1、主从复制原理
slave 开启IO/thread 请求master 的 log-bin 文件,写到自己的relay-log 文件中,然后在slave 重做relay log的sql语句,master IO/thread dump 传送bin log 给slave 。
2、如何配置主从复制
1、master 需要开启bin log
vi /etc/my.cnf
datadir=/var/lib/mysql
log-bin=/var/lib/mysql/mysql-bin
2、slave / master 的 serverid 不能相同
vi /etc/my.cnf
server-id=123456
mysql> create user 'bak'@'192.168.1.%' identified by 'bak123';
Query OK, 0 rows affected (0.38 sec)
mysql> grant replication slave on *.* to 'bak'@'192.168.1.%';
Query OK, 0 rows affected (0.34 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
3、备份数据
mysqldump --single-transaction -uroot -p123456 --master-data=2 -A >all.sql
# single-transaction 事务快照
# A 复制所有的数据库
# master-data=2 复制master 的position位置,用于主从架构搭建
查看all.sql 中master 的binlog 文件和position
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000017', MASTER_LOG_POS=1680;
slave 操作
# 把数据copy到slave 上
[root@nginx1 ~]# scp all.sql root@192.168.1.122:/opt/mysql/
1、导入数据到slave
[root@mysql1 mysql]# mysql -uroot -p123456 <all.sql
2、配置复制账号
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.120' ,
MASTER_USER='bak',
MASTER_PASSWORD='bak123',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000017',
MASTER_LOG_POS=1680;
Query OK, 0 rows affected, 2 warnings (0.52 sec)
3、开启主从复制命令
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.34 sec)
4、查看salve 状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.120
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000017
Read_Master_Log_Pos: 1680
Relay_Log_File: mysql1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000017
Slave_IO_Running: Connecting
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: 1680
Relay_Log_Space: 156
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'bak@192.168.1.120:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_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: 201121 02:54:10
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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
发现开启失败;
1、检查server-uuid
master:
[auto]
server-uuid=eaefcbe0-b781-11ea-b8fd-080027564b55
slave:
[auto]
server-uuid=eaefcbe0-b781-11ea-b8fd-080027564b55
发现文件一样,关闭master,删除auto.cnf 文件。重启master。
[root@nginx1 mysql]# rm -rf auto.cnf
[root@nginx1 mysql]# systemctl stop mysqld
[root@nginx1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 六 2020-11-21 02:58:54 EST; 5s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1672 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 1649 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1672 (code=exited, status=0/SUCCESS)
Status: "Server shutdown complete"
11月 21 02:30:25 nginx1.fandong.com systemd[1]: Starting MySQL Server...
11月 21 02:30:27 nginx1.fandong.com systemd[1]: Started MySQL Server.
11月 21 02:58:53 nginx1.fandong.com systemd[1]: Stopping MySQL Server...
11月 21 02:58:54 nginx1.fandong.com systemd[1]: Stopped MySQL Server.
[root@nginx1 mysql]# systemctl start mysqld
#查看
[auto]
server-uuid=7a6c803a-2bcf-11eb-ba5b-0800273e809e
2、重启slave
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
发现是由于slave-id 并没有设置,导致slave 和mster的id 一致,导致无法启动,修改slave 的
server-id=132456
重启mysql
3、查询状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.120
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000020
Read_Master_Log_Pos: 941
Relay_Log_File: mysql1-relay-bin.000006
Relay_Log_Pos: 1150
Relay_Master_Log_File: binlog.000020
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: 941
Relay_Log_Space: 1569
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: 1
Master_UUID: 7a6c803a-2bcf-11eb-ba5b-0800273e809e
Master_Info_File: mysql.slave_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
发现 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
状态正常,已将开启了主从复制。
测试:
master:
mysql> insert test1 values ('uu',1);
Query OK, 1 row affected (0.54 sec)
slave
mysql>
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1;
+------+------+
| name | age |
+------+------+
| uu | 1 |
+------+------+
1 row in set (0.00 sec)
发现已经同步过来了。
纠正问题:
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'bak@192.168.1.120:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection
是由于需要ssl验证,必须设置密钥串。
解决办法:更换验证字符。
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'bak123';
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.39 sec)