18. 主从复制搭建¶
1. 前提¶
1.准备2个以上的MySQL实例
2.每台实例server_id不同
3.主库创建专用的复制用户
4.主库必须开启二进制日志
5.从库需要初始化数据，保证和主库数据在一个时间点上一致
2. 搭建主从复制¶
2.1 准备多个MySQL实例(3307,3308)¶
2.1.1 创建多个专用目录¶
mkdir /data/330{7..9}/data -p
2.1.2 准备多个配置文件¶
vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log_error=/data/3307/mysql.log
skip-name-resolve
server_id=7
log_bin=/data/3307/data/mysql-bin
binlog_format=row
[mysql]
socket=/data/3307/mysql.sock
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
log_error=/data/3308/mysql.log
skip-name-resolve
server_id=8
[mysql]
socket=/data/3307/mysql.sock
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log_error=/data/3309/mysql.log
skip-name-resolve
server_id=9
[mysql]
socket=/data/3309/mysql.sock
2.1.3 初始化多套数据¶
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3309/data
2.1.4 修改权限¶
touch /data/330{7..9}/mysql.log
chown -R mysql.mysql /data/330*
2.1.5 启动数据库¶
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf &
/application/mysql/bin/mysqld_safe --defaults-file=/data/3309/my.cnf &
2.1.6 验证¶
netstat -tunlp|grep 330
2.2 主库创建复制用户¶
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'172.19.23.%' identified by '123';
2.3 全备主库数据，恢复到从库¶
mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/full.sql
set sql_log_bin=1;
2.4 开启主从复制¶
主库查看状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 447 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
从库开启主从复制
mysql -S /data/3308/mysql.sock
help change master to
change master to
master_host='172.19.23.181',
master_user='repl',
master_password='123',
master_port=3307,
master_log_file='mysql-bin.000001',
master_log_pos=0,
master_connect_retry=10;
2.5 启动复制线程¶
start slave;
2.6 查看主从状态¶
show slave status\G
#查看如下2个参数,为yes则说明开启成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
结果如下
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.23.181
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 447
Relay_Log_File: k8s-master-relay-bin.000002
Relay_Log_Pos: 660
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 447
Relay_Log_Space: 872
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: f3a11cfa-a690-11eb-bf79-00163e139de1
Master_Info_File: /data/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
最后更新:
2022-02-20 08:44:07