25-mysql集群搭建【高可用将会在26章发出】
主从架构和原理
MySQL的主从复制
- 读写分离
- 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
- 复制的作用
- 负载均衡读操作
- 备份
- 高可用和故障切换
- 数据分布
- MySQL升级
- 一主多从复制架构
- 复制原理
-
描述
主节点：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#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
- 各种复制架构
一Master/一Slave
一主多从
从服务器还可以再有从服务器
Master/Master
一从多主:适用于多个不同数据库
环状复制
*#复制需要考虑二进制日志事件记录格式
STATEMENT --> 基于"语句"记录，记录执行语句，执行结果依赖sql_mode，日志量较少
ROW --> 基于"行"记录，记录执行语句和元数据，日志量较大，更加安全，建议使用的格式
MIXED --> 混合模式，让系统自行判定该基于哪种方式进行
关于集群的内容 这里做一个企业常用搭配方案的实验，请参考
#10.0.0.200 -客户端
[root@ubuntu2204 ~]#apt update
[root@ubuntu2204 ~]#apt install -y mysql-client
----------------------------------------------------------------------------------------
#10.0.0.29 --> 代理服务器,Mycat
[root@mycat-server ~]#yum install -y java
[root@mycat-server ~]#java -version
openjdk version "1.8.0_352"
OpenJDK Runtime Environment (build 1.8.0_352-b08)
OpenJDK 64-Bit Server VM (build 25.352-b08, mixed mode)
[root@mycat-server ~]#mkdir /apps
[root@mycat-server ~]#tar xvf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps/
mycat/bin/mycat
mycat/bin/wrapper-linux-ppc-64
mycat/bin/wrapper-linux-x86-32
mycat/bin/wrapper-linux-x86-64
...
mycat/conf/wrapper.conf
mycat/conf/
...
mycat/conf/schema.xml
...
mycat/conf/server.xml
...
mycat/version.txt
mycat/logs/
mycat/catlet/
[root@mycat-server ~]#ll /apps/mycat/
total 12
drwxr-xr-x 2 root root 190 Nov 16 21:50 bin
drwxrwxrwx 2 root root 6 Apr 15 2020 catlet
drwxrwxrwx 4 root root 4096 Nov 16 21:50 conf
drwxr-xr-x 2 root root 4096 Nov 16 21:50 lib
drwxrwxrwx 2 root root 6 May 23 20:48 logs
-rwxrwxrwx 1 root root 227 May 24 17:38 version.txt
[root@mycat-server ~]#cd /apps/
[root@mycat-server apps]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat-server apps]#cat /etc/profile.d/my
mycat.sh mysql.sh
[root@mycat-server apps]#cat /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
[root@mycat-server apps]#. /etc/profile.d/mycat.sh
[root@mycat-server apps]#echo PATH
PATH
[root@mycat-server apps]#echo $PATH
/apps/mycat/bin:/usr/local/mysql/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mycat-server apps]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=1031,fd=4))
LISTEN 0 100 127.0.0.1:25 0.0.0.0:* users:(("master",pid=1624,fd=16))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=8204,fd=4))
LISTEN 0 50 *:40021 *:* users:(("java",pid=8204,fd=80))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1031,fd=6))
LISTEN 0 100 [::1]:25 [::]:* users:(("master",pid=1624,fd=17))
LISTEN 0 50 *:36703 *:* users:(("java",pid=8204,fd=78))
LISTEN 0 50 *:1984 *:* users:(("java",pid=8204,fd=79))
LISTEN 0 128 *:8066 --> 默认 *:* users:(("java",pid=8204,fd=100))
LISTEN 0 128 *:9066 *:* users:(("java",pid=8204,fd=84))
LISTEN 0 128 *:80 *:* users:(("httpd",pid=1747,fd=4),("httpd",pid=1746,fd=4),("httpd",pid=1745,fd=4),("httpd",pid=1024,fd=4))
[root@mycat-server apps]#cat /apps/mycat/logs/wrapper.log --> successfully表示运行成功
STATUS | wrapper | 2022/11/16 22:07:21 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/11/16 22:07:22 | Launching a JVM...
INFO | jvm 1 | 2022/11/16 22:07:22 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/11/16 22:07:22 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/11/16 22:07:22 |
INFO | jvm 1 | 2022/11/16 22:07:23 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO | jvm 1 | 2022/11/16 22:07:27 | MyCAT Server startup successfully. see logs in logs/mycat.log
#修改server.xml文件配置Mycat的连接信息
[root@centos8 ~]#vim /apps/mycat/conf/server.xml
...省略...
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连
接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property
name="processors">32</property> #--> 删除#后面此部分
.....
<user name="root"> #连接Mycat的用户名
<property name="password">123456</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相
对应
</user>
</mycat:server>
*这里使用的是root，密码为123456,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都
有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。
#修改schema.xml实现读写分离策略
[root@mycat-server conf]#cat schema.xml
<?xml versinotallow="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCnotallow="1000" minCnotallow="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.8:3306" user="root" password="123456">
<readHost host="host2" url="10.0.0.7:3306" user="root" password="123456" />
<readHost host="host3" url="10.0.0.10:3306" user="root" password="123456" />
<readHost host="host4" url="10.0.0.28:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
*上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.8为主库，10.0.0.7、10、28为从库
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授
权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
#在后端10.0.0.8主服务器创建用户并对mycat授权、开启日志功能(确认实现读写分离)
mysql> create user 'root'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.25 sec)
mysql> grant all on *.* to 'root'@'10.0.0.%';
Query OK, 0 rows affected (0.23 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
#在客户端上连接并测试 --> 10.0.0.200连接10.0.0.29
[root@ubuntu2204 ~]#mysql -uroot -p123456 -h10.0.0.29
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20220524173810 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2022, 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 |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
mysql> use testdb
ERROR 1049 (HY000): Unknown database 'testdb'
mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.07 sec)
#通过监控主从服务器通用日志变化观察到Mycat服务器上读写分别作用在主和从服务器上
tail /data/mysql/xxxx.log
----------------------------------------------------------------------------------------
#10.0.0.8 -主服务器
[root@mysql-master ~]#cat /etc/my.cnf
[mysqld] --> 服务端配置
server-id=8 --> 唯一
log-bin --> 开启二进制日志
datadir=/data/mysql --> 指定日志存放路径
socket=/data/mysql/mysql.sock --> 指定套接字文件存档路径
log-error=/data/mysql/mysql.log --> 指定错误日志存放路径
pid-file=/data/mysql/mysql.pid --> 指定进程ID文件存档路径
default_authentication_plugin=mysql_native_password --> 登录认证方式指定，主从不同无法互相登录mysql服务
#default_authentication_plugin=caching_sha2_password
log_bin=/data/mysql-binarylog/mysql-binlog --> 二进制日志存放及生成
plugin-load-add = "semisync_master.so" --> 使用半同步插件
rpl_semi_sync_master_enabled=ON --> 开启半同步
rpl_semi_sync_master_timeout=3000 --> 3秒内返回请求
gtid_mode=ON --> 开启全局事务 并发读写
enforce_gtid_consistency
[client] --> 客户端配置
socket=/data/mysql/mysql.sock --> mysql的主机和客户机在同一host（物理服务器）上的时候，使用unix domain socket做为通讯协议的载体，它比tcp快
[root@mysql-master data]#tree
.
├── mysql
│ ├── auto.cnf
│ ├── ca-key.pem
│ ├── ca.pem
│ ├── client-cert.pem
│ ├── client-key.pem
│ ├── hellodb
│ │ ├── classes.ibd
│ │ ├── coc.ibd
│ │ ├── courses.ibd
│ │ ├── scores.ibd
│ │ ├── students.ibd
│ │ ├── teachers.ibd
│ │ ├── testlog.ibd
│ │ └── toc.ibd
│ ├── #ib_16384_0.dblwr
│ ├── #ib_16384_1.dblwr
│ ├── ib_buffer_pool
│ ├── ibdata1
│ ├── ibtmp1
│ ├── #innodb_redo
│ │ ├── #ib_redo31
...
│ │ └── #ib_redo62_tmp
│ ├── #innodb_temp
│ │ ├── temp_10.ibt
...
│ ├── mycat
│ ├── mysql
│ │ ├── general_log_213.sdi
│ │ ├── general_log.CSM
│ │ ├── general_log.CSV
│ │ ├── slow_log_214.sdi
│ │ ├── slow_log.CSM
│ │ └── slow_log.CSV
│ ├── mysql.ibd
│ ├── mysql.log
│ ├── mysql.pid
│ ├── mysql.sock
│ ├── mysql.sock.lock
│ ├── performance_schema
│ │ ├── accounts_145.sdi
│ │ ├── binary_log_trans_189.sdi
│ │ ├── cond_instances_82.sdi
│ │ ├── data_locks_160.sdi
│ │ ├── data_lock_waits_161.sdi
│ │ ├── error_log_83.sdi
...
│ │ ├── variables_by_thr_183.sdi
│ │ └── variables_info_186.sdi
│ ├── private_key.pem
│ ├── public_key.pem
│ ├── rocky8-bin.000001
│ ├── rocky8-bin.000002
│ ├── rocky8-bin.index
│ ├── rocky8.log
│ ├── server-cert.pem
│ ├── server-key.pem
│ ├── sys
│ │ └── sys_config.ibd
│ ├── undo_001
│ └── undo_002
├── mysql-binarylog
│ ├── mysql-binlog.000001
│ ├── mysql-binlog.000002
...
│ └── mysql-binlog.index
└── table_rbk.sql
9 directories, 212 files
mysql> show processlist;
+----+-----------------+-----------------+---------+------------------+--------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+---------+------------------+--------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 119759 | Waiting on empty queue | NULL |
| 16 | repluser | 10.0.0.7:37400 | NULL | Binlog Dump | 115425 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 39 | repluser | 10.0.0.28:60260 | NULL | Binlog Dump GTID | 17867 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 41 | root | localhost | mysql | Query | 0 | init | show processlist |
| 43 | root | 10.0.0.29:47982 | hellodb | Sleep | 76 | | NULL |
| 44 | root | 10.0.0.29:47992 | hellodb | Sleep | 26 | | NULL |
| 45 | root | 10.0.0.29:48008 | hellodb | Sleep | 66 | | NULL |
| 46 | root | 10.0.0.29:48030 | hellodb | Sleep | 96 | | NULL |
| 47 | root | 10.0.0.29:48038 | hellodb | Sleep | 36 | | NULL |
| 48 | root | 10.0.0.29:48042 | hellodb | Sleep | 6 | | NULL |
| 49 | root | 10.0.0.29:48004 | hellodb | Sleep | 46 | | NULL |
| 50 | root | 10.0.0.29:48060 | hellodb | Sleep | 16 | | NULL |
| 51 | root | 10.0.0.29:48054 | hellodb | Sleep | 56 | | NULL |
| 52 | root | 10.0.0.29:48020 | hellodb | Sleep | 86 | | NULL |
+----+-----------------+-----------------+---------+------------------+--------+-----------------------------------------------------------------+------------------+
14 rows in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000018
Position: 2403
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
1 row in set (0.00 sec)
ERROR:
No query specified
#全备数据到从服务器
239 2022-11-16 14:23:43 mysqldump -uroot -p123456 -A -F --single-transaction --source-data=1 --flush-privileges --set-gtid-purged=off > all.sql
--------------------------------------------------------------------------
##10.0.0.7 -从服务器
[root@mysql-slave01 ~]#cat /etc/my.cnf
[mysqld]
server-id=7
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password
plugin-load-add = "semisync_slave.so"
rpl_semi_sync_slave_enabled=ON
gtid_mode=ON
enforce_gtid_consistency
[client]
socket=/data/mysql/mysql.sock
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000018
Read_Master_Log_Pos: 2403
Relay_Log_File: rocky8-relay-bin.000022
Relay_Log_Pos: 2625
Relay_Master_Log_File: mysql-binlog.000018
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: 2403
Relay_Log_Space: 2926
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: 8
Master_UUID: d07146ee-630f-11ed-a2db-000c2942b4d3
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: d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
Executed_Gtid_Set: 2991515b-6310-11ed-9152-000c29f1fa20:1-3,
d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
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.02 sec)
ERROR:
No query specified
--------------------------------------------------------------------------
##10.0.0.10 -级联从服务器，认10.0.0.7为主
[root@mysql-slave07-01 ~]#cat /etc/my.cnf
[mysqld]
server-id=10
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password
gtid_mode=ON
enforce_gtid_consistency
[client]
socket=/data/mysql/mysql.sock
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rocky8-bin.000003
Read_Master_Log_Pos: 4395
Relay_Log_File: rocky8-relay-bin.000007
Relay_Log_Pos: 4613
Relay_Master_Log_File: rocky8-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: 4395
Relay_Log_Space: 4994
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: 7
Master_UUID: 2991515b-6310-11ed-9152-000c29f1fa20
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: 2991515b-6310-11ed-9152-000c29f1fa20:1-3,
d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
Executed_Gtid_Set: 2991515b-6310-11ed-9152-000c29f1fa20:1-3,
d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
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.02 sec)
ERROR:
No query specified
----------------------------------------------------------------------------------------
##10.0.0.28 -从服务器
[root@mysql-slave02 ~]#cat /etc/my.cnf
[mysqld]
server-id=28
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password
plugin-load-add = "semisync_slave.so"
rpl_semi_sync_slave_enabled=ON
gtid_mode=ON
enforce_gtid_consistency
[client]
socket=/data/mysql/mysql.sock
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000018
Read_Master_Log_Pos: 2403
Relay_Log_File: slavedns-relay-bin.000009
Relay_Log_Pos: 2625
Relay_Master_Log_File: mysql-binlog.000018
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: 2403
Relay_Log_Space: 3100
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: 8
Master_UUID: d07146ee-630f-11ed-a2db-000c2942b4d3
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: d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
Executed_Gtid_Set: d07146ee-630f-11ed-a2db-000c2942b4d3:1-12
Auto_Position: 1
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.02 sec)
ERROR:
No query specified
复制的监控和维护
清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST
从服务器是否落后于主服务 ：观察 slave 线程字段Seconds_Behind_Master：0
如何确定主从节点数据是否一致：工具 percona-toolkit
数据不一致如何修复：删除从数据库，重新复制
复制的问题和解决方案
#数据损坏或丢失
Master：MHA + semisync replication
Slave： 重新复制
#不惟一的 server id
解决方法: 重新复制 --> 配置文件/mysql/auto.cnf
#复制延迟
1. 升级到MySQL5.7以上版本(5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行)利用GTID(MySQL5.6需要手动开启,MySQL5.7以上默认开启)支持并发传输binlog及并行多个SQL线程
2. 减少大事务,将大事务拆分成小事务
3. 减少锁
4. sync_binlog=1 加快binlog更新时间,从而加快日志复制 --> 实时写入磁盘，而不是mysql缓存
5. 需要额外的监控工具的辅助
6. 多线程复制：对多个数据库复制 GTID
#MySQL 主从数据不一致
1. 主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
2. 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
3. 从节点未设置只读，误操作写入数据
4. 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
5. 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
6. 主从sql_mode 不一致
7. MySQL自身bug导致 --> orcale的锅
#主从不一致修复方法
1. 将从库重新实现（虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。）
2. 使用percona-toolkit工具辅助
3. 手动重建不一致的表
*在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致，这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
案例：A,B,C这三张表主从数据不一致
Ⅰ、从库停止Slave复制
mysql>stop slave;
Ⅱ、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -p123456 -q --single-transaction --master-data=2 testdb A B
C >/backup/A_B_C.sql
Ⅲ、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;
#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已
经生成了一份快照，只需要导入进入，然后开启同步即可
Ⅳ、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888',
MASTERLOGPOS=666666;
Ⅴ、在Slave机器上导入A_B_C.sql
mysql -uroot -p123456 testdb
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;
Ⅵ、导入完毕后，从库开启同步即可。
mysql>start slave;
#如何避免主从不一致（非常重要）
1. 主库binlog采用ROW格式
2. 主从实例数据库版本保持一致
3. 主库做好账号权限把控，不可以执行set sql_log_bin=0
4. 从库开启只读，不允许人为写入
5. 定期进行主从一致性检验