5 Mysql集群Cluster
服务器性能扩展方式
Scale Up，向上扩展，垂直扩展
Scale Out，向外扩展，横向扩展
5.1Mysql主从复制
5.1.1主从复制架构和原理
5.1.1.1Mysql的主从复制
- 读写分离
- 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
5.1.1.2复制的功用
- 负载均衡读操作
- 备份（这里的备份如果删库备份也会删掉，只不过物理硬件损坏了有备份而已）
- 高可用和故障切换
- 数据分布
- MySQL升级
5.1.1.3 复制架构
一主一从复制架构
一主多从复制架构
5.1.1.4 主从复制原理
注意：依赖于二进制日志，必须开启二进制日志
主从复制相关线程
- 主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events - 从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
- master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
- relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
- mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
说明
MySQL8.0 取消 master.info 和 relay-log.info文件
5.1.1.5主从复制特点
- 异步复制: 客户端性能良好
- 主从数据不一致比较常见
5.1.1.6各种复制架构
- 一Master/一Slave
- 一主多从
- 从服务器还可以再有从服务器
- Master/Master
- 一从多主:适用于多个不同数据库
- 环状复制
复制需要考虑二进制日志事件记录格式
STATEMENT（5.0之前）, Mariadb5.5 默认使用此格式
ROW（5.1之后，推荐）,MySQL 8.0 默认使用此格式
MIXED: Mariadb10.3 默认使用此格式
5.1.2 ★★实现主从复制配置★★
5.1.2.1 主从复制说明
官网参考
https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/
主节点配置：
(1) 启用二进制日志
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld]datadir=/var/lib/mysqllog-bin=/data/mysql/mysql-bin
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]server-id=#log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
#如果不设置默认为1，如果主从都不设置，则两边都是1，那么会产生冲突，所以需要人为指定mysql> select @@server_id;+-------------+| @@server_id |+-------------+| 1 |+-------------+1 row in set (0.00 sec)
说明：
server-id的取值范围 #虽然范围很大但是为了方便使用一般都是用ip地址的主机位作为id使用1 to 4294967295 (>= MariaDB 10.2.2)，默认值为10 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
(3) 查看当前二进制日志文件的位置
SHOW MASTER STATUS;
(4) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';#MySQL8.0 分成两步实现mysql> create user repluser@'10.0.0.%' identified by '123456';mysql> grant replication slave on *.* to repluser@'10.0.0.%';
从节点配置：
(1) 启动中继日志
[mysqld]server_id=# #为当前节点设置一个全局惟的ID号log-bin #可开可不开，但是建议开启read_only=ON #设置数据库只读，针对supper user无效relay_log=relay-log #relay log的文件路径，默认值hostname-relay-binrelay_log_index=relay-log.index #默认值hostname-relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mariadb-bin.xxxxxx',MASTER_LOG_POS=#;START SLAVE [IO_THREAD|SQL_THREAD];SHOW SLAVE STATUS;
5.1.2.2实战案例：新建主从复制——主设备为新设备
##主节点#启用二进制日志[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld]datadir=/var/lib/mysqllog-bin=/data/mysql/mysql-bin #设置serverid[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld]server_id=11 #创建目录，修改属主属组[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf [root@localhost ~]#mkdir /data/mysql -pvmkdir: created directory '/data'mkdir: created directory '/data/mysql'[root@localhost ~]#chown -R mysql.mysql /data/mysql/#启动服务[root@localhost ~]#systemctl restart mysqld.service #确认是否正常开启二进制，serverid是否修改成功[root@localhost ~]#ll /data/mysql/total 32-rw-r----- 1 mysql mysql 179 Dec 13 09:33 mysql-bin.000001-rw-r----- 1 mysql mysql 179 Dec 13 09:36 mysql-bin.000002-rw-r----- 1 mysql mysql 179 Dec 13 09:39 mysql-bin.000003-rw-r----- 1 mysql mysql 10775 Dec 13 10:04 mysql-bin.000004-rw-r----- 1 mysql mysql 156 Dec 13 10:04 mysql-bin.000005-rw-r----- 1 mysql mysql 145 Dec 13 10:04 mysql-bin.indexmysql> select @@server_id;+-------------+| @@server_id |+-------------+| 11 |+-------------+1 row in set (0.00 sec)# 查看当前二进制日志文件的位置mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000005 | 156 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)#创建有复制权限的用户账号（帐号为haha）mysql> create user haha@'10.0.0.%' identified by 'sunxiang';Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to haha@'10.0.0.%';Query OK, 0 rows affected (0.00 sec)#查看线程mysql> show processlist;+----+-----------------+-----------+------+---------+------+------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+------+---------+------+------------------------+------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 563 | Waiting on empty queue | NULL || 8 | root | localhost | NULL | Query | 0 | init | show processlist |+----+-----------------+-----------+------+---------+------+------------------------+------------------+2 rows in set (0.00 sec)
思考题：先查看节点位置好，还是先创建用户授权好先查看节点位置，那么之后的创建用户并授权的操作也会进行同步，那么在从服务器上也有此用户的信息，如果主服务器出现问题，不至于没有可以连接数据库的用户可以用，所以建议先查看节点在创建用户
##从节点
#修改配置文件
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin #开启二进制日志
binlog_format=ROW
server_id=8 #设置服务id
read_only=on #对数据库只读
#创建目录，修改属主属组
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[root@localhost ~]#mkdir /data/mysql -pv
mkdir: created directory '/data'
mkdir: created directory '/data/mysql'
[root@localhost ~]#chown -R mysql.mysql /data/mysql/
#启动服务
[root@localhost ~]#systemctl restart mysqld.service
#查看线程
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 54 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
#使用有复制权限的用户账号连接至主服务器（这里一大段配置记不住，所以我通过help来进行修改）
mysql> help change master to #获取一下范例
CHANGE MASTER TO
MASTER_HOST='source2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='source2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
#修改范例
MASTER_HOST='10.0.0.11',
MASTER_USER='haha',
MASTER_PASSWORD='sunxiang',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.11',
-> MASTER_USER='haha',
-> MASTER_PASSWORD='sunxiang',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=156,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.01 sec)
#查看复制线程状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.11
Master_User: haha
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 156
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: No #线程状态为no
Slave_SQL_Running: No #线程状态为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: 156
Relay_Log_Space: 156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL #主从之间的数据差有多少秒
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
#启动复制线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
#查看线程 此时多出了两个线程
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1299 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
| 9 | system user | connecting host | NULL | Connect | 8 | Waiting for source to send event | NULL |
| 10 | system user | | NULL | Query | 559 | Replica has read all relay log; waiting for more updates | NULL |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
#再次查看复制线程状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.11
Master_User: haha
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1711
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1879
Relay_Master_Log_File: mysql-bin.000005
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: 1711
Relay_Log_Space: 2092
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 #主从之间的数据差0秒
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 80ffe18d-5bb5-11ec-8c4e-000c2995b7a2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
##主节点#再次查看线程 #多出了两个线程mysql> show processlist;+----+-----------------+----------------+------+-------------+------+-----------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+----------------+------+-------------+------+-----------------------------------------------------------------+------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 2364 | Waiting on empty queue | NULL || 8 | root | localhost | NULL | Query | 0 | init | show processlist || 9 | haha | 10.0.0.8:39780 | NULL | Sleep | 537 | | NULL || 10 | haha | 10.0.0.8:39782 | NULL | Binlog Dump | 215 | Source has sent all binlog to replica; waiting for more updates | NULL |+----+-----------------+----------------+------+-------------+------+-----------------------------------------------------------------+------------------+4 rows in set (0.00 sec)#查看网络连接状态[root@localhost ~]#ss -nt | grep 3306ESTAB 0 0 [::ffff:10.0.0.11]:3306 [::ffff:10.0.0.8]:39782
#主节点导入数据库
mysql> source /root/hellodb_innodbtest.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#从节点查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
5.1.2.3实战案例：新建主从复制——主设备已经有大量数据
##主节点（模拟为已经运行的数据库，二进制日志等都是开启的）
#查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#修改配置文件添加服务id
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
log-bin=/data/mysql/mysql-bin
server_id=11
#重启服务
[root@localhost ~]#systemctl restart mysqld.service
#备份数据库
[root@localhost ~]#mysqldump -A -F --master-data=1 --single-transaction | gzip > /data/all.sql.gz
[root@localhost ~]#ls /data/
all.sql.gz mysql
[root@localhost ~]#gzip -d /data/all.sql.gz
[root@localhost ~]#ls /data/
all.sql mysql
#将备份的数据库文件发送到从服务器上
[root@localhost ~]#scp /data/all.sql 10.0.0.8:/data/
#创建一个有复制权限的用户账号（帐号为haha）
mysql> create user haha@'10.0.0.%' identified by 'sunxiang';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to haha@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##从服务器
#修改配置文件
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin #开启二进制日志
binlog_format=ROW
server_id=8 #设置服务id
read_only=on #对数据库只读
#修改主服务发送的备份文件（使得其不仅具有数据库还原功能，还可以完成主从复制）
[root@localhost ~]#vim /data/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;
#将上面的一行换成下面的一段
[root@localhost ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.11',
MASTER_USER='haha',
MASTER_PASSWORD='sunxiang',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=156;
#启动服务，进入数据库
[root@localhost ~]#systemctl restart mysqld.service
[root@localhost ~]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#确认没有数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
#如果开启了二进制日志，恢复备份前需要关闭否则会生成大量的二进制日志文件
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
#导入修改过后的数据库文件
mysql> source /data/all.sql
#再次查看数据库 #数据库成功备份
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#查看主从复制状态 #主从服务也已经配置好，但是还没有开启线程
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.11
Master_User: haha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
#开启二进制日志
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
#手动开启线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#再次查看主从复制状态 #OK
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.11
Master_User: haha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 673
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 841
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从复制之前主数据的数据通过完全备份，在从服务器上备份，在备份二进制节点之后的内容通过主从复制在从服务器上也能够正常获取。达成主从的完全一致
5.1.3主从复制相关
5.1.3.1★★复制错误解决方案★★
可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突，建主键记录会产生冲突
#系统变量，指定跳过复制事件的个数SET GLOBAL sql_slave_skip_counter = N#服务器选项，只读系统变量，指定跳过事件的ID[mysqld]slave_skip_errors=1007|ALL
找茬环节
##从服务器上修改数据库内容mysql> insert teachers values(null,'zhangsan',33,'M');Query OK, 1 row affected (0.00 sec)mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | zhangsan | 33 | M |+-----+---------------+-----+--------+5 rows in set (0.00 sec)##主服务器长查看此表mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)##因为主从复制具有单向性，所以从服务器上的操作无法同步到主服务器上##问题来了主服务器上在此表中添加两条内容会怎样mysql> insert teachers values(null,'lisi',55,'M');Query OK, 1 row affected (0.00 sec)mysql> insert teachers values(null,'wangmazi',66,'M');Query OK, 1 row affected (0.00 sec)mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | lisi | 55 | M || 6 | wangmazi | 66 | M |+-----+---------------+-----+--------+6 rows in set (0.00 sec)#可以正常添加##查看从服务器mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | zhangsan | 33 | M |+-----+---------------+-----+--------+5 rows in set (0.01 sec)#查看不到主服务添加的信息##查看主从复制状态mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 10.0.0.11 Master_User: haha Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1185 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 1052 Relay_Master_Log_File: mysql-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 hellodb.teachers; Duplicate entry '5' for key 'teachers.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 1154 Skip_Counter: 0 Exec_Master_Log_Pos: 884 Relay_Log_Space: 1566 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: NULLMaster_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 hellodb.teachers; Duplicate entry '5' for key 'teachers.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 1154 Replicate_Ignore_Server_Ids: Master_Server_Id: 11 Master_UUID: 8410cce3-5bc7-11ec-9611-000c2995b7a2 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 211213 12:44:48 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
##解决方案#停止主从复制mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.01 sec)#跳过一个复制事件mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected, 1 warning (0.00 sec)#启动主从复制mysql> start slave;Query OK, 0 rows affected, 1 warning (0.01 sec)#查看数据库mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | zhangsan | 33 | M || 6 | wangmazi | 66 | M |+-----+---------------+-----+--------+6 rows in set (0.00 sec)#主服务器上的tid=5，为lisi老师，此条数据并没有正常同步，但是tid=6的数据从服务器上可以正常同步了，后续可以手动修改第五条数据，使得其与主服务一致即可mysql> update teachers set name='lisi',age=55 where tid=5;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select *from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | lisi | 55 | M || 6 | wangmazi | 66 | M |+-----+---------------+-----+--------+6 rows in set (0.00 sec)##至此成功解决！！
5.1.3.2 限制从服务器为只读
read_only=ON#注意：此限制对拥有SUPER权限的用户均无效
注意：以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;
5.1.3.3 在从节点清除信息
注意：以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay logRESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD等
5.1.3.4START SLAVE 语句
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =log_pos
START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS =log_pos
thread_types:
[thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD
5.1.3.5 保证主从复制的事务安全
参看https://mariadb.com/kb/en/library/server-system-variables/
在master节点启用参数：
sync_binlog=1 #每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
sync_master_info=# #次事件后master.info同步到磁盘
在slave节点启用服务器选项：
skip-slave-start=ON #不自动启动slave
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘