25-mysql集群搭建【高可用将会在26章发出】 主从架构和原理 MySQL的主从复制 读写分离 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制 复制的作用 负载均衡读
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 -versionopenjdk 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/mycatmycat/bin/wrapper-linux-ppc-64mycat/bin/wrapper-linux-x86-32mycat/bin/wrapper-linux-x86-64...mycat/conf/wrapper.confmycat/conf/...mycat/conf/schema.xml...mycat/conf/server.xml...mycat/version.txtmycat/logs/mycat/catlet/[root@mycat-server ~]#ll /apps/mycat/total 12drwxr-xr-x 2 root root 190 Nov 16 21:50 bindrwxrwxrwx 2 root root 6 Apr 15 2020 catletdrwxrwxrwx 4 root root 4096 Nov 16 21:50 confdrwxr-xr-x 2 root root 4096 Nov 16 21:50 libdrwxrwxrwx 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/mymycat.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 PATHPATH[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 -ntlpState 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 DaemonSTATUS | 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.orgINFO | 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> <propertyname="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 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 testdbERROR 1049 (HY000): Unknown database 'testdb'mysql> use TESTDB;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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_passwordlog_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.sql9 directories, 212 filesmysql> 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-121 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-bindatadir=/data/mysqlsocket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.piddefault_authentication_plugin=mysql_native_password#default_authentication_plugin=caching_sha2_passwordplugin-load-add = "semisync_slave.so"rpl_semi_sync_slave_enabled=ONgtid_mode=ONenforce_gtid_consistency[client]socket=/data/mysql/mysql.sockmysql> 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: 0Master_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-bindatadir=/data/mysqlsocket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.piddefault_authentication_plugin=mysql_native_password#default_authentication_plugin=caching_sha2_passwordgtid_mode=ONenforce_gtid_consistency[client]socket=/data/mysql/mysql.sockmysql> 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: 0Master_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-bindatadir=/data/mysqlsocket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.piddefault_authentication_plugin=mysql_native_password#default_authentication_plugin=caching_sha2_passwordplugin-load-add = "semisync_slave.so"rpl_semi_sync_slave_enabled=ONgtid_mode=ONenforce_gtid_consistency[client]socket=/data/mysql/mysql.sockmysql> 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: 0Master_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 STATUSSHOW BINARY LOGSSHOW BINLOG EVENTSSHOW SLAVE STATUSSHOW PROCESSLIST
从服务器是否落后于主服务 ：观察 slave 线程字段Seconds_Behind_Master：0
如何确定主从节点数据是否一致：工具 percona-toolkit
数据不一致如何修复：删除从数据库，重新复制
复制的问题和解决方案
#数据损坏或丢失Master：MHA + semisync replicationSlave： 重新复制#不惟一的 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=04. 从库开启只读，不允许人为写入5. 定期进行主从一致性检验
