主从复制实验

zhl52306

于 2022-11-24 15:30:08 发布

阅读量109

点赞数

本文链接：https://blog.csdn.net/zhl52306/article/details/128019693

版权

#用脚本安装方式，安装二进制数据库，准备主从初始化数据库
[root@rocky8 ~]#bash install_mysql5.7or8.0.sh 
开始安装MySQL数据库...
安装相关包完成!                                            [  OK  ]
数据库安装完成                                             [  OK  ]

主节点

#新增server-id配置
[root@master ~]#vim /etc/my.cnf 
[mysqld]
server-id=11

#调整二进制日志存放路径，新增如下配置
[root@master data]#vim /etc/my.cnf
[mysqld]
log-bin=/data/binlog/mysql-bin

#重启服务
[root@master data]#systemctl restart mysqld.service 

#查看二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#创建复制用户并授权允许复制权限
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

#由于主服务器已运行一段时间，则需导出数据至从服务器导入
[root@master data]#mysqldump -A -F --single-transaction --master-data=1 >/data/backup/fullbackup_`date +%F`.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.

[root@master backup]#ll
-rw-r--r-- 1 root root 1269798 Nov 12 20:33 fullbackup_2022-11-12.sql


#拷贝至远程从节点
[root@master backup]#scp fullbackup_2022-11-12.sql 10.0.0.7:/data
root@10.0.0.7's password: 
fullbackup_2022-11-12.sql                                  100% 1240KB 136.9MB/s   00:00

从节点

#新增server-id以及设置只读节点
[root@slave ~]#vim /etc/my.cnf
[mysqld]
server-id=7
read-only

#新增存放二进制日志目录，并更改属主
[root@slave data]#mkdir binlog
[root@slave data]#chown mysql.mysql binlog/

#修改二进制日志存放位置
[root@slave data]#vim /etc/my.cnf
[mysqld]
log-bin=/data/binlog/mysql_binlog

#重启mysql服务
[root@slave data]#systemctl restart mysqld.service 

#查看日志位置已更改
[root@slave data]#ll binlog/
total 8
-rw-r----- 1 mysql mysql 157 Nov 12 20:19 mysql_binlog.000001
-rw-r----- 1 mysql mysql  33 Nov 12 20:19 mysql_binlog.index

#配置从节点，从完全备份的位置之后开始复制，找到CHANGE MASTER字段插入如下MASTER_HOST='10.0.0.11',MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306

[root@slave data]#grep '^CHANGE MASTER' /data/fullbackup_2022-11-12.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=157;

[root@slave data]#vim /data/fullbackup_2022-11-12.sql 
CHANGE MASTER TO MASTER_HOST='10.0.0.11',MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=157;

#恢复数据
[root@slave data]#mysql -uroot -p123456< fullbackup_2022-11-12.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@slave data]#mysql -uroot -p123456
Server version: 8.0.30 MySQL Community Server - GPL

mysql> 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.0.0.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: No
            Slave_SQL_Running: No
...................

#开始从节点复制
mysql> start slave
    -> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

#提示有一个告警，查看为密码加密规则问题，但是主从复制正常
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 10.0.0.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Connecting    #IO线程运行正常，该线程为与主节点的slave线程通信
            Slave_SQL_Running: Yes           #sql中继线程运行正常，该线程为读取同步过来的二进制日志进行数据库重写；
              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: 157
              Relay_Log_Space: 157
              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 'repluser@10.0.0.11: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: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 221112 20:47:34
     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)

#主节点上修改同步账号密码插件
mysql> alter user repluser@'10.0.0.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

#再次启动从节点
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 450
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 619
        Relay_Master_Log_File: mysql-bin.000006
             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: 450
              Relay_Log_Space: 829
              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: 11
                  Master_UUID: 3798c7dc-6269-11ed-9c1b-000c29c614e5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

验证

#主节点插入数据，教师表插入2行数据，学生表删除1行数据
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Hong Qigong   |  70 | M      |
|   6 | Xiao Longnv   |  18 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
..............................................................
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

mysql> insert teachers(name,age,gender) values ('Zhou Botong',88,'M'),('Li Mochou',55,'F');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from students where stuid=25;
Query OK, 1 row affected (0.00 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Hong Qigong   |  70 | M      |
|   6 | Xiao Longnv   |  18 | F      |
|   7 | Zhou Botong   |  88 | M      |
|   8 | Li Mochou     |  55 | F      |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
..............................................................
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)


#从节点查看数据
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Hong Qigong   |  70 | M      |
|   6 | Xiao Longnv   |  18 | F      |
|   7 | Zhou Botong   |  88 | M      |
|   8 | Li Mochou     |  55 | F      |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
..............................................................
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)

#主节点删除toc表

实验问题点：

做此实验时出现一次误操作，在从节点上删除了一个toc表，为避免后续该问题，可在从节点新增如下配置

#从节点误操作删除了toc表
mysql> drop tables toc;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| courses           |
| scores            |
| students          |
| teachers          |
+-------------------+
5 rows in set (0.01 sec)

#从节点新增管理员只读配置
[root@rocky8 ~]#vim /etc/my.cnf
[mysqld]
super_read_only=ON 

#重启服务
[root@rocky8 ~]#systemctl restart mysqld.service

#验证已无法删除
mysql> drop tables scores;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

由于未设置从节点禁止操作数据库，导致删除库之后，主从节点数据不同步出现问题，这时候可通过重新指定二进制日志位置进行同步

#主从不同步报错
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.0.0.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1051
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000006, end_log_pos 1296. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1085
              Relay_Log_Space: 5906
              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: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 1051
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000006, end_log_pos 1296. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: 3798c7dc-6269-11ed-9c1b-000c29c614e5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 221112 21:36:46
     Last_SQL_Error_Timestamp: 221112 21:36:46
               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.01 sec)


#解决方式，先停止从节点同步，而后重新指定主节点二进制日志位置
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> change master to master_log_file='mysql-bin.000001',master_log_pos=157;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

#查看同步已恢复
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 767
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 630
        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: 767
              Relay_Log_Space: 1495
              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: 11
                  Master_UUID: 3798c7dc-6269-11ed-9c1b-000c29c614e5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)