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 = #为当前节点设置一个全局惟一的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= > /root/all_bak.sql
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll -h
total 20M
-rw-r--r-- root root 20M Nov : 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 % 20MB .4MB/s :
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# scp all_bak.sql node103.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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | :: | *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, 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 | :: | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+--------------+-------------------------------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';
5>.级联节点（node103.yinzhengjie.org.cn）的配置文件(my.cnf)
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server_id = #为当前节点设置一个全局惟的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
-rw-r--r-- root root Nov : all_bak.sql
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# head - 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=; --
-- 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 - all_bak.sql | tail CHANGE MASTER TO
MASTER_HOST='172.30.1.102',
MASTER_USER='copy',
MASTER_PASSWORD='yinzhengjie',
MASTER_PORT=,
MASTER_LOG_FILE='master-bin.000003',
MASTER_LOG_POS=,
MASTER_CONNECT_RETRY=; [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# vim all_bak.sql #修改备份文件主要是配置使用有复制权限的用户账号连接至master
[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 root root Nov : /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 mysql mysql Nov : /data/mysql/logbin/
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[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
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb #启动mariadb数据库
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+---------------+-------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET sql_log_bin = OFF; #在导入master数据之前，应该临时关闭二进制日志功能
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SET sql_log_bin = OFF; #在导入master数据之前，应该临时关闭二进制日志功能
MariaDB [(none)]> SYSTEM ls -l /var/lib/mysql/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
MariaDB [(none)]>
MariaDB [(none)]> SOURCE /root/all_bak.sql #将咱们修改后的master备份数据导入进来
...... Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SYSTEM ls -l /var/lib/mysql/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
drwx------ mysql mysql Nov : devops
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
-rw-rw---- mysql mysql Nov : master.info
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
-rw-rw---- mysql mysql Nov : relay-log.
-rw-rw---- mysql mysql Nov : relay-log.index
-rw-rw---- mysql mysql Nov : relay-log.info
drwx------ mysql mysql Nov : test
drwx------ mysql mysql Nov : yinzhengjie2019
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [(none)]> SOURCE /root/all_bak.sql #将咱们修改后的master备份数据导入进来
MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SET sql_log_bin = ON; #导入成功后别忘记开启二进制日志功能哟
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SET sql_log_bin = ON; #导入成功后别忘记开启二进制日志功能哟
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> START SLAVE; #别忘记手动开启复制线程哟
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> START SLAVE; #别忘记手动开启复制线程哟
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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(*) |
+----------+
| |
+----------+
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, row affected (57.94 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT COUNT(*) FROM yinzhengjie2019.testlog;
+----------+
| COUNT(*) |
+----------+
| |
+----------+
row in set (0.16 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW PROCESSLIST\G
*************************** . row ***************************
Id:
User: root
Host: localhost
db: yinzhengjie2019
Command: Query
Time:
State: NULL
Info: SHOW PROCESSLIST
Progress: 0.000
*************************** . row ***************************
Id:
User: copy
Host: node103.yinzhengjie.org.cn:
db: NULL
Command: Binlog Dump
Time:
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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(*) |
+----------+
| |
+----------+
row in set (0.31 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW PROCESSLIST\G
*************************** . row ***************************
Id:
User: system user
Host:
db: NULL
Command: Connect
Time:
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** . row ***************************
Id:
User: system user
Host:
db: NULL
Command: Connect
Time:
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
*************************** . row ***************************
Id:
User: root
Host: localhost
db: NULL
Command: Query
Time:
State: NULL
Info: SHOW PROCESSLIST
Progress: 0.000
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= > /root/all_bak103.sql
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll -h
total 23M
-rw-r--r-- root root 23M Nov : 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::5d:d8:0a:4a:b4::0f::be:2c:::db::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 % 23MB .5MB/s :
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
8>.slave节点的配置文件
[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server_id = #为当前节点设置一个全局惟的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-- root root 23M Nov : all_bak103.sql
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# head - 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=; --
-- 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 - all_bak103.sql | tail CHANGE MASTER TO
MASTER_HOST='172.30.1.103', #注意此处主机应该指定的是级联节点的IP
MASTER_USER='copy',
MASTER_PASSWORD='yinzhengjie',
MASTER_PORT=,
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=,
MASTER_CONNECT_RETRY=; [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# vim all_bak103.sql #修改配置使用有复制权限的用户账号连接至级联服务器
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[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
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb #启动mariadb数据库
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ll -h
total 23M
-rw-r--r-- root root 23M Nov : 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
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
drwx------ mysql mysql Nov : devops
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
-rw-rw---- mysql mysql Nov : master.info
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
-rw-rw---- mysql mysql Nov : relay-log.
-rw-rw---- mysql mysql Nov : relay-log.index
-rw-rw---- mysql mysql Nov : relay-log.info
drwx------ mysql mysql Nov : test
drwx------ mysql mysql Nov : yinzhengjie2019
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# mysql < all_bak103.sql #将级联节点备份数据导入进来当前实例中
[root@node104.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.103
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> START SLAVE; #别忘记手动开启复制线程哟
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.103
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> START SLAVE; #别忘记手动开启复制线程哟
9>.验证级联复制是否生效
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+---------------------------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR() NOT NULL,sex
ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(),address VARCHAR());Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie2019]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',,,'beijing'),('Jay',
'',,'*');Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: MariaDB [yinzhengjie2019]> INSERT INTO students SET name='yinzhengjie',age=,address='shanxi';
Query OK, row affected (0.01 sec) MariaDB [yinzhengjie2019]> INSERT students (age,sex,name,mobile,address) VALUES (,'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 |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
master节点(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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
级联节点(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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]#
slave节点(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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+-------------+
| |
+-------------+
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 | :: | *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, 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 | :: | *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 ~]#
master节点为slave节点(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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | :: | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| test | node104.yinzhengjie.org.cn | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+-------------+
| |
+-------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [devops]> SELECT USER();
+---------------------------------+
| USER() |
+---------------------------------+
| test@node104.yinzhengjie.org.cn |
+---------------------------------+
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 |
+------------------+
rows in set (0.00 sec) MariaDB [devops]>
MariaDB [devops]> DELETE FROM students; #虽说我们当前用户有insert,delete,select,update权限，但当前数据库是只读的。因此会抛出如下异常。
ERROR (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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+---------------------------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT @@read_only;
+-------------+
| @@read_only |
+-------------+
| |
+-------------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> DELETE FROM students; #虽然当前数据库实例是只读的，单对于超级用户root来说，说删就直接删了，read_only参数对超级用户无效!
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
限制从服务器为只读,此限制对拥有SUPER权限的用户均无效
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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, rows affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| devops |
| mysql |
| performance_schema |
| test |
| yinzhengjie2019 |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> DROP DATABASE devops;
ERROR (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE db1;
ERROR (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]>
MariaDB [(none)]> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> UNLOCK TABLES; #解锁
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #添加全局锁,阻止所有用户, 包括主服务器复制的更新
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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, 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() 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',,,'beijing'),('Jay','',
,'*');Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
使用超级用户在master节点(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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+--------------------+
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 |
+-----------------+
row in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> INSERT INTO students SET name='yinzhengjie',age=,address='shanxi';
Query OK, row affected (0.01 sec) MariaDB [linux]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]>
使用超级用户在级联节点(node103.yinzhengjie.org.cn)上修改从master节点同步过来的数据
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (,'girl','Gloria Tang Tsz-Kei',null,'*');
Query OK, row affected (0.00 sec)
MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [linux]>
此时再用使用超级用户在master节点(node102.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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (,'girl','Gloria Tang Tsz-Kei',null,'*');
Query OK, row affected (0.00 sec)
MariaDB [linux]>
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (,'girl','张娜拉',null,'韩国');
Query OK, row affected (0.00 sec) MariaDB [linux]>
MariaDB [linux]> INSERT students (id,age,sex,name,mobile,address) VALUES (,,'girl','胡歌',null,'上海');
Query OK, row affected (0.01 sec) MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
| | 张娜拉 | girl | | NULL | 韩国 |
| | 胡歌 | girl | | NULL | 上海 |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [linux]>
此时再用使用超级用户在master节点(node102.yinzhengjie.org.cn)上同一张表插入数据
MariaDB [linux]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Replicate_Ignore_Server_Ids:
Master_Server_Id:
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM linux.students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | 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, rows affected (0.01 sec) MariaDB [linux]>
MariaDB [linux]> SELECT @@sql_slave_skip_counter;
+--------------------------+
| @@sql_slave_skip_counter |
+--------------------------+
| |
+--------------------------+
row in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SET GLOBAL sql_slave_skip_counter = ; #我这里设置从服务器忽略1个主服务器的复制事件，注意它是global变量。
Query OK, rows affected (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SELECT @@sql_slave_skip_counter;
+--------------------------+
| @@sql_slave_skip_counter |
+--------------------------+
| |
+--------------------------+
row in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]>
MariaDB [linux]> START SLAVE;
Query OK, rows affected (0.00 sec) MariaDB [linux]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [linux]> SELECT * FROM students; #此时数据同步啦
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | 张娜拉 | girl | | NULL | 韩国 |
| | 胡歌 | girl | | NULL | 上海 |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
MariaDB [linux]> SET GLOBAL sql_slave_skip_counter = 1; #我设置从服务器忽略1个主服务器的复制事件
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | 张娜拉 | girl | | NULL | 韩国 |
| | 胡歌 | girl | | NULL | 上海 |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> UPDATE students SET name='Gloria Tang Tsz-Kei',sex='girl',age=,address='*' WHERE id = ;
Query OK, row affected (0.01 sec)
Rows matched: Changed: Warnings: MariaDB [linux]>
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
| | 张娜拉 | girl | | NULL | 韩国 |
| | 胡歌 | girl | | 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= 每次写后立即同步二进制日志到磁盘，性能差
如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit= 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务MariaDB10..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的报错提示信息
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
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:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
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:
row in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G #仔细查看Last_Error和Last_SQL_Error的报错提示信息
报错分析:
由于主从复制使用的二进制格式不一致导致的报错。 解决方案:
可以在配置文件中2个数据库实例显示指定"binlog_format=row"，当二进制日志格式相同时这个报错就自然解决了，下面时查看二进制日志格式的命令。 MariaDB [(none)]> SHOW VARIABLES LIKE 'BINLOG_FORMAT'; #推荐将"binlog_format=row"写入"my.cnf"文件并重启实例可永久修改，并不推荐临时使用SET命令设置
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
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:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
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:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> STOP SLAVE;
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW';
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET binlog_format = 'ROW';
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> START SLAVE;
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW'; #戳这里查看详细过程
2>.主从二进制日志文件名称配置不一致(Could not find first log file name in binary log index file)
MariaDB [(none)]> SHOW SLAVE STATUS\G #仔细查看Last_IO_Error错误提示信息
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.103
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error: Got fatal error from master when reading data from binary log: 'Could not find first
log file name in binary log index file'
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G #仔细查看Last_IO_Error错误提示信息
报错分析:
报这个错一般是由于从节点通过在配置CHANGE MASTER TO时，将MASTER_LOG_FILE的所对应的文件名称配置错误导致的。
解决方案:
要解决这个问题首先得确认主从复制关系，然后找到master节点的二进制日志文件名称，然后在slave节点指向master的真正存在的名称即可。
即手动修改对应的master的二进制日志位置参数:"CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=;"
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的报错信息
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-log.
Relay_Log_Pos:
Relay_Master_Log_File: master-bin.
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:
Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin., end_log_pos Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [linux]>
MariaDB [linux]> SHOW SLAVE STATUS\G #仔细观察Last_SQL_Error和Last_Error的报错信息
报错分析:
这种错误一般是由于从库和主库数据不一致到的报错信息，我上面就通过模拟了主键冲突导致MySQL主从同步失败。 解决办法:
可以使用sql_slave_skip_counter参数来忽略一些复制时间，从而让从库可以进行复制，剩下的数据不同步的问题在另行解决方案。