Mysql 入门篇之二进制安装

Mysql 5.7 入门安装

环境说明：CentOS Linux release 7.6.1810 (Core) 4核4G

卸载自带组件

• 卸载自带的mysql相关组件

rpm -qa | grep mysql
rpm -e --nodeps *mysql*
rpm -qa | grep mariadb
rpm -e --nodeps mariadbxxxx

下载二进制包

官方下载地址：https://dev.mysql.com/downloads/mysql/

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.43-linux-glibc2.12-x86_64.tar

安装

1. 创建相关目录

mkdir -p /usr/local/mysql/app
mkdir -p /usr/local/mysql/data/3306
mkdir -p /var/log/mysql/3306

1. 解压二进制包

tar -xf mysql-5.7.43-linux-glibc2.12-x86_64.tar
tar -xf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.43-linux-glibc2.12-x86_64 /usr/local/mysql/app
cd /usr/local/mysql/app
mv mysql-5.7.43-linux-glibc2.12-x86_64 mysql

1. 增加环境变量

# vi ~/.bash_profile
PATH=$PATH:/usr/local/mysql/app/mysql/bin

1. 配置用户组信息

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /var/log/mysql

1. 配置启动文件

# /usr/local/mysql/data/3306/my.cnf
[mysql] 
default-character-set=utf8 
socket=/usr/local/mysql/data/3306/mysql.sock 
[mysqld] 
port = 3306 
socket=/usr/local/mysql/data/3306/mysql.sock 
basedir=/usr/local/mysql/app/mysql
datadir=/usr/local/mysql/data/3306/data
character-set-server=utf8
default-storage-engine=INNODB 
innodb_buffer_pool_size = 200M
max_allowed_packet=16M 
explicit_defaults_for_timestamp=1
log-output=FILE
general_log = 0 
general_log_file=/var/log/mysql/general.err
slow_query_log = ON
slow_query_log_file=/var/log/mysql/query.err
long_query_time=10
log-error=/var/log/mysql/error.err

1. 初始化mysql

ln -sf /usr/local/mysql/data/3306/my.cnf /etc/my.cnf
/usr/local/mysql/app/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/app/mysql/ --datadir=/usr/local/mysql/data/3306/data/

1. 配置服务启动脚本

# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/app/mysql/bin/mysqld --defaults-file=/usr/local/mysql/data/3306/my.cnf
LimitNOFILE = 65536
LimitNPROC = 65536

1. 启动生效

systemctl daemon-reload
systemctl stop mysqld
systemctl start mysqld
systemctl enable mysqld

配置

1. 修改 root 密码

# 初始密码可以通过 cat /var/log/mysql/error.err  | grep pass 获取
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin@mysql';
exit；

1. 允许 root 远程登陆

mysql -u root -p
use mysql;
select host,user from user where user='root';
# 注意 root 是设置的密码
create USER 'root'@'%' IDENTIFIED BY 'root';
grant all privileges on *.* to 'root'@'%' with grant option;
exit;
# 验证一下即可
mysql -uroot -proot -h 10.0.16.4

Mysql 8.0 入门安装

环境说明：CentOS Linux release 7.6.1810 (Core) 4核4G

卸载自带组件

• 卸载自带的mysql相关组件

rpm -qa | grep mysql
rpm -e --nodeps *mysql*
rpm -qa | grep mariadb
rpm -e --nodeps mariadbxxxx

下载二进制包

官方下载地址：https://dev.mysql.com/downloads/mysql/

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.34-linux-glibc2.12-x86_64.tar

安装

1. 解压

tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar
rm -f mysql-8.0.34-linux-glibc2.12-x86_64.tar
xz -d mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar

1. 创建目录

mv mysql-8.0.34-linux-glibc2.12-x86_64 /usr/local/
cd /usr/local/
mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql
mkdir /var/lib/mysql
mkdir -p /usr/local/mysql/log
mkdir /usr/local/mysql/data

1. 配置环境变量

# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

1. 创建用户组权限

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /var/lib/mysql

1. 生成配置文件

# vi /etc/my.cnf
[mysql] 
default-character-set=utf8mb4 
socket=/var/lib/mysql/mysql.sock 
[mysqld] 
port = 3306 
socket=/var/lib/mysql/mysql.sock 
basedir=/usr/local/mysql 
character-set-server=utf8mb4 
default-storage-engine=INNODB
innodb_buffer_pool_size = 200M
max_allowed_packet=16M 
explicit_defaults_for_timestamp=1
log-output=FILE
general_log = 0 
general_log_file=/usr/local/mysql/log/general.err
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/log/query.err
long_query_time=10
log-error=/usr/local/mysql/log/error.err
default-authentication-plugin=mysql_native_password

1. 初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

1. 配置启停文件

# vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65536
LimitNPROC = 65536

1. 启停服务

systemctl daemon-reload
systemctl stop mysqld 
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

配置

1. 修改 root 密码

mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin@mysql';
exit;

1. 允许 root 远程登陆

mysql -u root -p
use mysql;
select host,user from user where user='root';
create USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin@mysql';
grant all privileges on *.* to 'root'@'%' with grant option;
exit;

1. 远程登陆验证

mysql -uroot -p -h 10.0.16.4