mysql 主从复制搭建

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)