MySQL/MariaDB数据库的主从级联复制
MySQL/MariaDB数据库的主从级联复制
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.主从复制类型概述
1>.主从复制
博主推荐阅读：
https://www.cnblogs.com/yinzhengjie/p/11816066.html
2>.一主多从
其实就是重复主从复制的步骤即可。一个master节点多个slave节点，此时多个slave节点同时同步master节点。
3>.级联复制
在一主多从的架构上稍作改进，依旧是一个master节点，指定一个slave节点从master节点拉取数据，其它slave节点从该slave节点拉取数据的架构。
二.级联复制案例实战
1>.试验架构说明
node102.yinzhengjie.org.cn(172.30.1.102):
master节点 node103.yinzhengjie.org.cn(172.30.1.103): slave节点，级联节点,该节点直接通过master节点复制数据
node104.yinzhengjie.org.cn(172.30.1.104):
slave节点，该节点并不直接通过从master节点复制数据，而是通过级联节点复制数据
2>.master节点配置文件("/etc/my.cnf")
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] binlog_format = row #推荐使用基于行数据的复制 log_bin = /data/logbin/mysql-bin #需要启用二进制日志,建议和数据文件分开放存放 server-id = 102 #为当前节点设置一个全局惟一的ID号,用于标识当前MySQL实例 log-basename = master #可选项，设置datadir中日志名称，确保不依赖主机名 character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]#
3>.备份master节点数据并拷贝备份数据级联节点(模拟对生产环境再用的数据库进行级联复制)
[root@node102.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A -F --single-transaction --master-data=1 > /root/all_bak.sql
[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll -h total 20M -rw-r--r-- 1 root root 20M Nov 8 14:26 all_bak.sql [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# scp all_bak.sql node103.yinzhengjie.org.cn:~ root@node103.yinzhengjie.org.cn's password: all_bak.sql 100% 20MB 91.4MB/s 00:00 [root@node102.yinzhengjie.org.cn ~]#
4>.master节点(node101.yinzhengjie.org.cn)创建有复制权限的用户账号
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +------+-----------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------+--------------+-------------------------------------------+ | user | host | password | +------+--------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +------+--------------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [(none)]>
5>.级联节点（node103.yinzhengjie.org.cn）的配置文件(my.cnf)
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server_id = 103 #为当前节点设置一个全局惟的ID号 read_only = ON #设置数据库只读 binlog_format = row #注意这个二进制日志格式要和master配置一致哟,否则会同步失败。 relay_log = relay-log #relay log的文件路径，默认值hostname-relay-bin relay_log_index = relay-log.index #默认值hostname-relay-bin.index log_bin = /data/mysql/logbin/mysql-bin #由于其它slave节点需要从该节点拷贝数据，因此咱们也得开启二进制日志功能，建议二进制日志和数据文件分开存放 log_slave_updates #启用级联复制功能 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid [root@node103.yinzhengjie.org.cn ~]#
6>.配置级联节点与master节点的主从复制
[root@node103.yinzhengjie.org.cn ~]# ll total 20192 -rw-r--r-- 1 root root 20674386 Nov 8 14:28 all_bak.sql [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# head -25 all_bak.sql | tail /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=245; -- -- Current Database: `devops` [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# vim all_bak.sql #需要传过来的备份文件稍作修改，主要是配置使用有复制权限的用户账号连接至主服务器 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# head -30 all_bak.sql | tail CHANGE MASTER TO MASTER_HOST='172.30.1.102', MASTER_USER='copy', MASTER_PASSWORD='yinzhengjie', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10; [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mkdir -pv /data/mysql/logbin mkdir: created directory ‘/data’ mkdir: created directory ‘/data/mysql’ mkdir: created directory ‘/data/mysql/logbin’ [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ -d drwxr-xr-x 2 root root 6 Nov 8 14:50 /data/mysql/logbin/ [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# chown -R mysql:mysql /data/mysql/logbin/ [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ -d drwxr-xr-x 2 mysql mysql 6 Nov 8 14:50 /data/mysql/logbin/ [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ total 0 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 0 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb #启动mariadb数据库 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 37852 -rw-rw---- 1 mysql mysql 16384 Nov 8 14:52 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 14:52 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 8 14:52 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:52 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:52 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 8 14:52 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 14:52 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 14:52 performance_schema drwx------ 2 mysql mysql 6 Nov 8 14:52 test [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ total 1056 -rw-rw---- 1 mysql mysql 30394 Nov 8 14:52 mysql-bin.000001 -rw-rw---- 1 mysql mysql 1038814 Nov 8 14:52 mysql-bin.000002 -rw-rw---- 1 mysql mysql 245 Nov 8 14:52 mysql-bin.000003 -rw-rw---- 1 mysql mysql 108 Nov 8 14:52 mysql-bin.index [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> SHOW VARIABLES LIKE '%sql_log_bin%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SET sql_log_bin = OFF; #在导入master数据之前，应该临时关闭二进制日志功能 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW VARIABLES LIKE '%sql_log_bin%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]>
MariaDB [(none)]> SYSTEM ls -l /var/lib/mysql/ total 28700 -rw-rw---- 1 mysql mysql 16384 Nov 8 14:52 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 14:52 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 8 14:52 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:52 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:52 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 8 14:52 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 14:52 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 14:52 performance_schema drwx------ 2 mysql mysql 6 Nov 8 14:52 test MariaDB [(none)]> MariaDB [(none)]> SOURCE /root/all_bak.sql #将咱们修改后的master备份数据导入进来 ...... Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SYSTEM ls -l /var/lib/mysql/ total 78828 -rw-rw---- 1 mysql mysql 16384 Nov 8 14:52 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 14:52 aria_log_control drwx------ 2 mysql mysql 60 Nov 8 14:57 devops -rw-rw---- 1 mysql mysql 69206016 Nov 8 14:58 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:58 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 14:57 ib_logfile1 -rw-rw---- 1 mysql mysql 84 Nov 8 14:57 master.info drwx------ 2 mysql mysql 4096 Nov 8 14:57 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 14:52 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 14:52 performance_schema -rw-rw---- 1 mysql mysql 245 Nov 8 14:57 relay-log.000001 -rw-rw---- 1 mysql mysql 19 Nov 8 14:57 relay-log.index -rw-rw---- 1 mysql mysql 43 Nov 8 14:57 relay-log.info drwx------ 2 mysql mysql 6 Nov 8 14:52 test drwx------ 2 mysql mysql 39 Nov 8 14:57 yinzhengjie2019 MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SET sql_log_bin = ON; #导入成功后别忘记开启二进制日志功能哟 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000003 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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 245 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: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> START SLAVE; #别忘记手动开启复制线程哟 Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000003 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: 245 Relay_Log_Space: 818 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: 102 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]>
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT COUNT(*) FROM yinzhengjie2019.testlog; +----------+ | COUNT(*) | +----------+ | 599994 | +----------+ 1 row in set (0.14 sec) MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjie2019 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 MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> CALL pro_testlog; Query OK, 1 row affected (57.94 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SELECT COUNT(*) FROM yinzhengjie2019.testlog; +----------+ | COUNT(*) | +----------+ | 699993 | +----------+ 1 row in set (0.16 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 15 User: root Host: localhost db: yinzhengjie2019 Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST Progress: 0.000 *************************** 2. row *************************** Id: 17 User: copy Host: node103.yinzhengjie.org.cn:45496 db: NULL Command: Binlog Dump Time: 546 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL Progress: 0.000 2 rows in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]>
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT COUNT(*) FROM yinzhengjie2019.testlog; +----------+ | COUNT(*) | +----------+ | 699993 | +----------+ 1 row in set (0.31 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 537 State: Waiting for master to send event Info: NULL Progress: 0.000 *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 837 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL Progress: 0.000 *************************** 3. row *************************** Id: 7 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST Progress: 0.000 3 rows in set (0.00 sec) MariaDB [(none)]>
7>.备份级联节点(node103.yinzhengjie.org.cn)的数据并发送到slave节点中(node104.yinzhengjie.org.cn)
[root@node103.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A -F --single-transaction --master-data=1 > /root/all_bak103.sql
[root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll -h total 23M -rw-r--r-- 1 root root 23M Nov 8 15:31 all_bak103.sql [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# scp all_bak103.sql node104.yinzhengjie.org.cn:~ The authenticity of host 'node104.yinzhengjie.org.cn (172.30.1.104)' can't be established. ECDSA key fingerprint is SHA256:F3IVf82keybIystuO6PYRfwr0o5dTftrmAHJWzqO4IA. ECDSA key fingerprint is MD5:02:5d:d8:0a:4a:b4:70:0f:61:be:2c:97:56:db:24:e7. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'node104.yinzhengjie.org.cn,172.30.1.104' (ECDSA) to the list of known hosts. root@node104.yinzhengjie.org.cn's password: all_bak103.sql 100% 23MB 93.5MB/s 00:00 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
8>.slave节点的配置文件
[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server_id = 104 #为当前节点设置一个全局惟的ID号 read_only = ON #设置数据库只读 relay_log = relay-log #relay log的文件路径，默认值hostname-relay-bin relay_log_index = relay-log.index #默认值hostname-relay-bin.index datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid [root@node104.yinzhengjie.org.cn ~]#
8>.配置slave节点与级联节点主从复制
[root@node104.yinzhengjie.org.cn ~]# ll -h total 23M -rw-r--r-- 1 root root 23M Nov 8 15:33 all_bak103.sql [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# head -25 all_bak103.sql | tail /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=245; -- -- Current Database: `devops` [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# vim all_bak103.sql #需要传过来的备份文件稍作修改，主要是配置使用有复制权限的用户账号连接至级联服务器 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# head -30 all_bak103.sql | tail CHANGE MASTER TO MASTER_HOST='172.30.1.103', #注意此处主机应该指定的是级联节点的IP MASTER_USER='copy', MASTER_PASSWORD='yinzhengjie', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10; [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 0 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb #启动mariadb数据库 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 37852 -rw-rw---- 1 mysql mysql 16384 Nov 8 15:45 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 15:45 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 8 15:45 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:45 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:45 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 8 15:45 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 15:45 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 15:45 performance_schema drwx------ 2 mysql mysql 6 Nov 8 15:45 test [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 37852 -rw-rw---- 1 mysql mysql 16384 Nov 8 15:45 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 15:45 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 8 15:45 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:45 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:45 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 8 15:45 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 15:45 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 15:45 performance_schema drwx------ 2 mysql mysql 6 Nov 8 15:45 test [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ll -h total 23M -rw-r--r-- 1 root root 23M Nov 8 15:36 all_bak103.sql [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# mysql < all_bak103.sql #将级联节点备份数据导入进来当前实例中 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 136172 -rw-rw---- 1 mysql mysql 16384 Nov 8 15:45 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 8 15:45 aria_log_control drwx------ 2 mysql mysql 60 Nov 8 15:46 devops -rw-rw---- 1 mysql mysql 77594624 Nov 8 15:46 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:46 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 8 15:46 ib_logfile1 -rw-rw---- 1 mysql mysql 84 Nov 8 15:46 master.info drwx------ 2 mysql mysql 4096 Nov 8 15:46 mysql srwxrwxrwx 1 mysql mysql 0 Nov 8 15:45 mysql.sock drwx------ 2 mysql mysql 4096 Nov 8 15:45 performance_schema -rw-rw---- 1 mysql mysql 245 Nov 8 15:46 relay-log.000001 -rw-rw---- 1 mysql mysql 19 Nov 8 15:46 relay-log.index -rw-rw---- 1 mysql mysql 43 Nov 8 15:46 relay-log.info drwx------ 2 mysql mysql 6 Nov 8 15:45 test drwx------ 2 mysql mysql 39 Nov 8 15:46 yinzhengjie2019 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.1.103 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000007 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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 245 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: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> START SLAVE; #别忘记手动开启复制线程哟 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.103 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000007 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: 245 Relay_Log_Space: 817 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: 103 1 row in set (0.00 sec) MariaDB [(none)]>
9>.验证级联复制是否生效
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE yinzhengjie2019; 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 MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SHOW TABLES; +---------------------------+ | Tables_in_yinzhengjie2019 | +---------------------------+ | testlog | +---------------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie2019]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay', '40',10086,'Taiwan');Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie2019]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi'; Query OK, 1 row affected (0.01 sec) MariaDB [yinzhengjie2019]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Ho ng Kong');Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie2019]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [(none)]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [(none)]> QUIT Bye [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
三.复制架构中应该注意的问题
1>.限制从服务器为只读
在从服务器上设置read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新
mysql> FLUSH TABLES WITH READ LOCK;
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT @@read_only; #Mysql节点不要开启只读属性 +-------------+ | @@read_only | +-------------+ | 0 | +-------------+ row in set (0.00 sec) MariaDB [(none)]> MariaDB [mysql]> SELECT user,host,password FROM mysql.user; +------+--------------+-------------------------------------------+ | user | host | password | +------+--------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +------+--------------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [mysql]> MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT ON devops.* TO test@'node104.yinzhengjie.org.cn' IDENTIFIED BY 'yinzhengjie'; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> MariaDB [mysql]> SELECT user,host,password FROM mysql.user; +------+----------------------------+-------------------------------------------+ | user | host | password | +------+----------------------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | test | node104.yinzhengjie.org.cn | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +------+----------------------------+-------------------------------------------+ rows in set (0.00 sec) MariaDB [mysql]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------+----------------------------+-------------------------------------------+ | user | host | password | +------+----------------------------+-------------------------------------------+ | root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | test | node104.yinzhengjie.org.cn | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +------+----------------------------+-------------------------------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> QUIT Bye [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# mysql -utest -pyinzhengjie -h node104.yinzhengjie.org.cn #连接当前节点 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT @@READ_ONLY; #当前节点为只读节点 +-------------+ | @@READ_ONLY | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [devops]> SELECT USER(); +---------------------------------+ | USER() | +---------------------------------+ | test@node104.yinzhengjie.org.cn | +---------------------------------+ 1 row in set (0.00 sec) MariaDB [devops]> MariaDB [(none)]> USE devops; 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 MariaDB [devops]> SHOW TABLES; +------------------+ | Tables_in_devops | +------------------+ | employee | | students | +------------------+ 2 rows in set (0.00 sec) MariaDB [devops]> MariaDB [devops]> DELETE FROM students; #虽说我们当前用户有insert,delete,select,update权限，但当前数据库是只读的。因此会抛出如下异常。 ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement MariaDB [devops]> MariaDB [devops]>
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjie2019 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 MariaDB [yinzhengjie2019]> SHOW TABLES; +---------------------------+ | Tables_in_yinzhengjie2019 | +---------------------------+ | students | | testlog | +---------------------------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SELECT @@read_only; +-------------+ | @@read_only | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> DELETE FROM students; #虽然当前数据库实例是只读的，单对于超级用户root来说，说删就直接删了，read_only参数对超级用户无效! Query OK, 4 rows affected (0.01 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> MariaDB [yinzhengjie2019]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 18 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #添加全局锁,阻止所有用户, 包括主服务器复制的更新 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | devops | | mysql | | performance_schema | | test | | yinzhengjie2019 | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> DROP DATABASE devops; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> CREATE DATABASE db1; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock MariaDB [(none)]> MariaDB [(none)]> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> UNLOCK TABLES; #解锁 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
2>.RESET SLAVE
在从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log ,注意：需要先STOP SLAVE RESET SLAVE ALL 清除所有从服务器上设置的主服务器同步信息如：PORT, HOST, USER和 PASSWORD 等
3>.sql_slave_skip_counter = N
从服务器忽略几个主服务器的复制事件，global变量
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> CREATE DATABASE linux; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE linux Database changed MariaDB [linux]> MariaDB [linux]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('bo y','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));Query OK, 0 rows affected (0.01 sec) MariaDB [linux]> MariaDB [linux]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086 ,'Taiwan');Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [linux]>
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 23 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | devops | | linux | | mysql | | performance_schema | | test | | yinzhengjie2019 | +--------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE linux 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 MariaDB [linux]> MariaDB [linux]> SHOW TABLES; +-----------------+ | Tables_in_linux | +-----------------+ | students | +-----------------+ 1 row in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi'; Query OK, 1 row affected (0.01 sec) MariaDB [linux]> SELECT * FROM students; +----+-------------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-------------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | +----+-------------+------+------+--------+---------+ 3 rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]>
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 33 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE linux 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 MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong'); Query OK, 1 row affected (0.00 sec) MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 3 rows in set (0.00 sec) MariaDB [linux]>
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 33 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE linux 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 MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong'); Query OK, 1 row affected (0.00 sec) MariaDB [linux]> MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (38,'girl','张娜拉',null,'韩国'); Query OK, 1 row affected (0.00 sec) MariaDB [linux]> MariaDB [linux]> INSERT students (id,age,sex,name,mobile,address) VALUES (5,37,'girl','胡歌',null,'上海'); Query OK, 1 row affected (0.01 sec) MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | | 4 | 张娜拉 | girl | 38 | NULL | 韩国 | | 5 | 胡歌 | girl | 37 | NULL | 上海 | +----+---------------------+------+------+--------+-----------+ 5 rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students; +----+-------------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-------------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | +----+-------------+------+------+--------+---------+ 3 rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22191700 Relay_Log_File: relay-log.000007 Relay_Log_Pos: 3046 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes 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: 1062 Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Skip_Counter: 0 Exec_Master_Log_Pos: 22191477 Relay_Log_Space: 3798 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: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Replicate_Ignore_Server_Ids: Master_Server_Id: 102 1 row in set (0.00 sec) MariaDB [linux]>
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22192537 Relay_Log_File: relay-log.000007 Relay_Log_Pos: 3046 Relay_Master_Log_File: master-bin.000003 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: 1062 Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Skip_Counter: 1 Exec_Master_Log_Pos: 22191477 Relay_Log_Space: 4635 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: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Replicate_Ignore_Server_Ids: Master_Server_Id: 102 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT * FROM linux.students; +----+-------------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-------------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | +----+-------------+------+------+--------+---------+ rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE linux 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 MariaDB [linux]> MariaDB [linux]> STOP SLAVE; Query OK, 0 rows affected (0.01 sec) MariaDB [linux]> MariaDB [linux]> SELECT @@sql_slave_skip_counter; +--------------------------+ | @@sql_slave_skip_counter | +--------------------------+ | 0 | +--------------------------+ row in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> SET GLOBAL sql_slave_skip_counter = 1; #我这里设置从服务器忽略1个主服务器的复制事件，注意它是global变量。 Query OK, 0 rows affected (0.00 sec) MariaDB [linux]> MariaDB [linux]> SELECT @@sql_slave_skip_counter; +--------------------------+ | @@sql_slave_skip_counter | +--------------------------+ | 1 | +--------------------------+ row in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> MariaDB [linux]> START SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [linux]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22192537 Relay_Log_File: relay-log.000009 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000003 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: 22192537 Relay_Log_Space: 1103 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: 102 row in set (0.00 sec) MariaDB [(none)]> MariaDB [linux]> SELECT * FROM students; #此时数据同步啦 +----+-------------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-------------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | 张娜拉 | girl | 38 | NULL | 韩国 | | 5 | 胡歌 | girl | 37 | NULL | 上海 | +----+-------------+------+------+--------+---------+ rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 31 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE linux 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 MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+-------------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-------------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | 张娜拉 | girl | 38 | NULL | 韩国 | | 5 | 胡歌 | girl | 37 | NULL | 上海 | +----+-------------+------+------+--------+---------+ rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> UPDATE students SET name='Gloria Tang Tsz-Kei',sex='girl',age=28,address='Hong Kong' WHERE id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [linux]> MariaDB [linux]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | | 4 | 张娜拉 | girl | 38 | NULL | 韩国 | | 5 | 胡歌 | girl | 37 | NULL | 上海 | +----+---------------------+------+------+--------+-----------+ rows in set (0.00 sec) MariaDB [linux]> MariaDB [linux]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
4>.如何保证主从复制的事务安全
在master节点启用参数： sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差 如果用到的为InnoDB存储引擎： innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘 innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除 sync_master_info=# #次事件后master.info同步到磁盘 在slave节点启用服务器选项： skip_slave_start=ON 不自动启动slave，服务器选项，默认为OFF即自动启动slave 在slave节点启用参数： sync_relay_log=# #次写后同步relay log到磁盘 sync_relay_log_info=# #次事务后同步relay-log.info到磁盘 博主推荐阅读: https://mariadb.com/kb/en/library/server-system-variables/
四.实战部署时遇到的问题及解决方案
1>.主从的BINLOG_FORMAT不一致，主为ROW,从为STATEMENT
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G #仔细查看Last_Error和Last_SQL_Error的报错提示信息 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 15836165 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes 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: 1666 Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si nce statement is in row format and BINLOG_FORMAT = STATEMENT.' Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 15836738 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: 0 Last_IO_Error: Last_SQL_Errno: 1666 Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si nce statement is in row format and BINLOG_FORMAT = STATEMENT.' Replicate_Ignore_Server_Ids: Master_Server_Id: 102 1 row in set (0.00 sec) MariaDB [yinzhengjie2019]>
报错分析:
由于主从复制使用的二进制格式不一致导致的报错。
解决方案:
可以在配置文件中2个数据库实例显示指定"binlog_format=row"，当二进制日志格式相同时这个报错就自然解决了，下面时查看二进制日志格式的命令。
MariaDB [(none)]> SHOW VARIABLES LIKE 'BINLOG_FORMAT'; #推荐将"binlog_format=row"写入"my.cnf"文件并重启实例可永久修改，并不推荐临时使用SET命令设置 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]>
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 27 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22192537 Relay_Log_File: relay-log.000007 Relay_Log_Pos: 3269 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes 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: 1666 Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si nce statement is in row format and BINLOG_FORMAT = STATEMENT.' Skip_Counter: 0 Exec_Master_Log_Pos: 22191700 Relay_Log_Space: 5208 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: 0 Last_IO_Error: Last_SQL_Errno: 1666 Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si nce statement is in row format and BINLOG_FORMAT = STATEMENT.' Replicate_Ignore_Server_Ids: Master_Server_Id: 102 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | STATEMENT | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | STATEMENT | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SET binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22192537 Relay_Log_File: relay-log.000009 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000003 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: 22192537 Relay_Log_Space: 1103 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: 102 1 row in set (0.00 sec) MariaDB [(none)]>
2>.主从二进制日志文件名称配置不一致(Could not find first log file name in binary log index file)
MariaDB [(none)]> SHOW SLAVE STATUS\G #仔细查看Last_IO_Error错误提示信息 *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.1.103 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000007 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000007 Slave_IO_Running: No 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: 245 Relay_Log_Space: 245 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: 1236 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: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 103 1 row in set (0.00 sec) MariaDB [(none)]>
报错分析:
报这个错一般是由于从节点通过在配置CHANGE MASTER TO时，将MASTER_LOG_FILE的所对应的文件名称配置错误导致的。
解决方案:
要解决这个问题首先得确认主从复制关系，然后找到master节点的二进制日志文件名称，然后在slave节点指向master的真正存在的名称即可。 即手动修改对应的master的二进制日志位置参数:"CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=107;"
3>.主键冲突类错误(Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;)
MariaDB [linux]> SHOW SLAVE STATUS\G #仔细观察Last_SQL_Error和Last_Error的报错信息 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: copy Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 22191700 Relay_Log_File: relay-log.000007 Relay_Log_Pos: 3046 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes 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: 1062 Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Skip_Counter: 0 Exec_Master_Log_Pos: 22191477 Relay_Log_Space: 3798 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: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673 Replicate_Ignore_Server_Ids: Master_Server_Id: 102 1 row in set (0.00 sec) MariaDB [linux]>
报错分析:
这种错误一般是由于从库和主库数据不一致到的报错信息，我上面就通过模拟了主键冲突导致MySQL主从同步失败。
解决办法:
可以使用sql_slave_skip_counter参数来忽略一些复制时间，从而让从库可以进行复制，剩下的数据不同步的问题在另行解决方案。
当你的才华还撑不起你的野心的时候，你就应该静下心来学习。当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练。问问自己，想要怎样的人生。
欢迎加入基础架构自动化运维：598432640，大数据SRE进阶之路：959042252，DevOps进阶之路：526991186
