MySQL单机主从离线部署
作者:鱼仔
博客首页: codeease.top (opens new window)
公众号:Java鱼仔
# 下载安装文件
首先下载MySQL的安装文件，本次教程使用的是 mysql-8.0.28-el7-x86_64.tar.gz
# 单机版安装
首先将文件放到安装目录下，本次安装目录为 /usr/local
cd /usr/local
tar -zxvf mysql-8.0.28-el7-x86_64.tar.gz
mv mysql-8.0.28-el7-x86_64 mysql
1
2
3
2
3
接着创建数据文件夹和日志文件夹
mkdir -p /usr/local/mysql/data
mkdir -p /usr/local/mysql/logs
1
2
2
创建一个MySQL用户和组，并授权MySQL目录
groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql
1
2
3
2
3
修改MySQL的配置文件
vi /etc/my.cnf
1
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/logs/mysql.log
socket=/usr/local/mysql/mysql.sock
event_scheduler=ON
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
long_query_time = 1
default-authentication-plugin=mysql_native_password
server-id=1
log-bin=mysql-bin
max_connect_errors=1000
binlog_format=STATEMENT
log-error=/usr/local/mysql/logs/mysql.log
pid-file=/usr/local/mysql/data/mysql.pid
character-set-server=utf8mb4
lower_case_table_names=1
max_connections=5000
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8mb4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
修改完成后通过 !wq 保存。
最后进行安装
/usr/local/mysql/bin/mysqld --initialize --user=mysql --lower_case_table_names=1
1
查看密码并记录
cat /usr/local/mysql/logs/mysql.log | grep password
1
创建软链接
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
ln -s /usr/local/mysql/bin/mysql /usr/bin/
1
2
2
启动MySQL
service mysqld start
1
当出现SUCCESS表示安装完成。
接着进入MySQL进行配置
mysql -uroot -p
1
提示输入密码则将上面保存下来的初始化密码输入。
进入后设置root账号
alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by 'mysql.123456';
update mysql.user set host='%' where user='root';
flush privileges;
1
2
3
2
3
至此，单机版就安装完成了。
# 主从搭建
首先按照单机版的部署教程在另外一台服务器部署MySQL
需要注意的是，从库的配置文件和主库略有不同
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/logs/mysql.log
socket=/usr/local/mysql/mysql.sock
event_scheduler=ON
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
long_query_time = 1
default-authentication-plugin=mysql_native_password
server-id=2
relay-log=mysql-relay
log-error=/usr/local/mysql/logs/mysql.log
pid-file=/usr/local/mysql/data/mysql.pid
character-set-server=utf8mb4
lower_case_table_names=1
max_connections=5000
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8mb4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
主库创建同步账号
create user 'slave'@'%' identified with mysql_native_password by 'mysql.123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;
1
2
3
2
3
在主库上获取二进制日志文件名和位置，记录File和Position
show master status;
1
在从库上配置主从同步
change master to master_host='主服务器 IP 或域名',
master_port=主库端口,
master_user='用户名',
master_password='密码',
master_log_file='主服务器上的二进制日志文件名',
master_log_pos=主服务器上的二进制日志文件位置;
1
2
3
4
5
6
2
3
4
5
6
如下面这样配置
change master to master_host='192.168.78.131',
master_port=3306,
master_user='slave',
master_password='mysql.123456',
master_log_file='mysql-bin.000003',
master_log_pos=1607;
1
2
3
4
5
6
2
3
4
5
6
在从库开启主从同步
start slave;
1
接着在主库新建库、表、插入数据的操作，从库都能实现数据的同步，说明主从搭建完成。
主从一般搭配着读写分离一起使用，另外一种方案是主备方案，读写是在主库中实现，备库负责备份数据以及应对主库发生异常。
上次更新: 2025/02/18, 11:30:08
