MySQL主从复制实例环境搭建与MySQL数据库迁移完整案例分享
参考背景介绍
阿里云ECS自建MySQL-8.0.21，因公司策略调整规划转移，考虑数据量级目前大约是100Gb+、以及降低对线上业务影响。因此，引入主从模式迁移，此手稿是在生产迁移之前，在VMware上测试记录，仅供参考练手；
mysql> select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema;
+--------------------+----------+--------------+--------------+
| 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) |
+--------------------+----------+--------------+--------------+
| bh | 348439560| 100703.68 | 53370.93 |
| information_schema | 0 | 0.00 | 0.00 |
| mysql | 3376 | 2.10 | 0.27 |
| performance_schema | 2781780 | 0.00 | 0.00 |
| sys | 6 | 0.01 | 0.00 |
+--------------------+----------+--------------+--------------+
5 rows in set (0.14 sec)
一、MySQL主从/主主模式环境搭建
这里注意几点：
- master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog）;
- slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了;
- mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务;
- mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）;
- master和slave两节点间时间需同步;
Mysql复制逻辑图
1、MySQL主从复制条件
- 开启Binlog功能
- 主库要建立账号
- 从库要配置master.info (CHANGE MASTER to...相当于配置密码文件和Master的相关信息)
- start slave 开启复制功能
2、在MySQL主Master数据库上的操作，设置Master数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）
server-id=129 #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=huanqiu #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1 #确保binlog日志写入后与硬盘同步
binlog_checksum = none #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed #bin-log日志文件格式，设置为MIXED可以防止主键重复。3、在MySQL主Master数据库上的操作，导出master数据库的数据，上传至MySQL从机/opt/目录下
导出数据库之前先锁定数据库，再执行导出命令
mysql> flush tables with read lock; #数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定[root@master ~]# mysqldump -uroot 10691 -p123456 >/opt/10691.sql
[root@master ~]# rsync -e "ssh -p22" -avpgolr /opt/10691.sql 172.19.19.128:/opt/ ＃将导出的sql文件上传到slave机器上4、在MySQL主Master数据库上的操作，创建Master数据库访问账户
mysql> create user 'master'@'172.19.19.128' identified WITH mysql_native_password by 'Bhyl@master123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* to 'master'@'172.19.19.128';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'master'@'172.19.19.128' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)验证账户权限，'master'@'172.19.19.128'，“IS_GRANTABLE”列全为YES，完整权限（此处可适当降权）；
mysql> select * from information_schema.user_privileges;
+--------------------------+---------------+----------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------------+---------------+----------------------------+--------------+
| 'master'@'172.19.19.128' | def | SELECT | YES |
| 'master'@'172.19.19.128' | def | INSERT | YES |
| 'master'@'172.19.19.128' | def | UPDATE | YES |
| 'master'@'172.19.19.128' | def | DELETE | YES |
| 'master'@'172.19.19.128' | def | CREATE | YES |
| 'master'@'172.19.19.128' | def | DROP | YES |
| 'master'@'172.19.19.128' | def | RELOAD | YES |
| 'master'@'172.19.19.128' | def | SHUTDOWN | YES |
| 'master'@'172.19.19.128' | def | PROCESS | YES |
| 'master'@'172.19.19.128' | def | FILE | YES |
| 'master'@'172.19.19.128' | def | REFERENCES | YES |
| 'master'@'172.19.19.128' | def | INDEX | YES |
| 'master'@'172.19.19.128' | def | ALTER | YES |
| 'master'@'172.19.19.128' | def | SHOW DATABASES | YES |
| 'master'@'172.19.19.128' | def | SUPER | YES |
| 'master'@'172.19.19.128' | def | CREATE TEMPORARY TABLES | YES |
| 'master'@'172.19.19.128' | def | LOCK TABLES | YES |
| 'master'@'172.19.19.128' | def | EXECUTE | YES |
| 'master'@'172.19.19.128' | def | REPLICATION SLAVE | YES |
| 'master'@'172.19.19.128' | def | REPLICATION CLIENT | YES |
| 'master'@'172.19.19.128' | def | CREATE VIEW | YES |
| 'master'@'172.19.19.128' | def | SHOW VIEW | YES |
| 'master'@'172.19.19.128' | def | CREATE ROUTINE | YES |
| 'master'@'172.19.19.128' | def | ALTER ROUTINE | YES |
| 'master'@'172.19.19.128' | def | CREATE USER | YES |
| 'master'@'172.19.19.128' | def | EVENT | YES |
| 'master'@'172.19.19.128' | def | TRIGGER | YES |
| 'master'@'172.19.19.128' | def | CREATE TABLESPACE | YES |
| 'master'@'172.19.19.128' | def | CREATE ROLE | YES |
| 'master'@'172.19.19.128' | def | DROP ROLE | YES |
| 'master'@'172.19.19.128' | def | XA_RECOVER_ADMIN | YES |
| 'master'@'172.19.19.128' | def | TABLE_ENCRYPTION_ADMIN | YES |
| 'master'@'172.19.19.128' | def | SYSTEM_VARIABLES_ADMIN | YES |
| 'master'@'172.19.19.128' | def | SYSTEM_USER | YES |
| 'master'@'172.19.19.128' | def | SET_USER_ID | YES |
| 'master'@'172.19.19.128' | def | SESSION_VARIABLES_ADMIN | YES |
| 'master'@'172.19.19.128' | def | SERVICE_CONNECTION_ADMIN | YES |
| 'master'@'172.19.19.128' | def | ROLE_ADMIN | YES |
| 'master'@'172.19.19.128' | def | RESOURCE_GROUP_USER | YES |
| 'master'@'172.19.19.128' | def | RESOURCE_GROUP_ADMIN | YES |
| 'master'@'172.19.19.128' | def | REPLICATION_SLAVE_ADMIN | YES |
| 'master'@'172.19.19.128' | def | REPLICATION_APPLIER | YES |
| 'master'@'172.19.19.128' | def | PERSIST_RO_VARIABLES_ADMIN | YES |
| 'master'@'172.19.19.128' | def | INNODB_REDO_LOG_ARCHIVE | YES |
| 'master'@'172.19.19.128' | def | GROUP_REPLICATION_ADMIN | YES |
| 'master'@'172.19.19.128' | def | ENCRYPTION_KEY_ADMIN | YES |
| 'master'@'172.19.19.128' | def | CONNECTION_ADMIN | YES |
| 'master'@'172.19.19.128' | def | CLONE_ADMIN | YES |
| 'master'@'172.19.19.128' | def | BINLOG_ENCRYPTION_ADMIN | YES |
| 'master'@'172.19.19.128' | def | BINLOG_ADMIN | YES |
| 'master'@'172.19.19.128' | def | BACKUP_ADMIN | YES |
| 'master'@'172.19.19.128' | def | AUDIT_ADMIN | YES |
| 'master'@'172.19.19.128' | def | APPLICATION_PASSWORD_ADMIN | YES |
| 'mysql.infoschema'@'%' | def | SELECT | NO |
| 'mysql.session'@'%' | def | SHUTDOWN | NO |
| 'mysql.session'@'%' | def | SUPER | NO |
| 'mysql.sys'@'%' | def | USAGE | NO |
| 'root'@'%' | def | SELECT | YES |
| 'root'@'%' | def | INSERT | YES |
| 'root'@'%' | def | UPDATE | YES |
| 'root'@'%' | def | DELETE | YES |
| 'root'@'%' | def | CREATE | YES |
| 'root'@'%' | def | DROP | YES |
| 'root'@'%' | def | RELOAD | YES |
| 'root'@'%' | def | SHUTDOWN | YES |
| 'root'@'%' | def | PROCESS | YES |
| 'root'@'%' | def | FILE | YES |
| 'root'@'%' | def | REFERENCES | YES |
| 'root'@'%' | def | INDEX | YES |
| 'root'@'%' | def | ALTER | YES |
| 'root'@'%' | def | SHOW DATABASES | YES |
| 'root'@'%' | def | SUPER | YES |
| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | LOCK TABLES | YES |
| 'root'@'%' | def | EXECUTE | YES |
| 'root'@'%' | def | REPLICATION SLAVE | YES |
| 'root'@'%' | def | REPLICATION CLIENT | YES |
| 'root'@'%' | def | CREATE VIEW | YES |
| 'root'@'%' | def | SHOW VIEW | YES |
| 'root'@'%' | def | CREATE ROUTINE | YES |
| 'root'@'%' | def | ALTER ROUTINE | YES |
| 'root'@'%' | def | CREATE USER | YES |
| 'root'@'%' | def | EVENT | YES |
| 'root'@'%' | def | TRIGGER | YES |
| 'root'@'%' | def | CREATE TABLESPACE | YES |
| 'root'@'%' | def | CREATE ROLE | YES |
| 'root'@'%' | def | DROP ROLE | YES |
+--------------------------+---------------+----------------------------+--------------+
87 rows in set (0.08 sec)5、在MySQL主Master数据库上的操作，查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 151 | 10691 | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.05 sec)6、在MySQL主Slave数据库上的操作，设置Slave数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）
server-id=128 #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin #启动MySQ二进制日志系统
replicate-do-db=10691 #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql #不同步mysql系统数据库
slave-skip-errors = all #跳过所有的错误错误，继续执行复制操作7、在MySQL主Slave数据库上的操作，创建并导入同步Master数据，即/opt/10691.sql文件
mysql> create database zabbix character set utf8mb4 collate utf8mb4_general_ci;
mysql> use 10691;
mysql> source /opt/10691.sql; ＃导入master中的原始数据8、在MySQL主Slave数据库上的操作，MySQL主从参数配置
#主从配置
mysql> change master to master_host='172.19.19.129',master_user='master',master_password='Bhyl@master123',master_log_file='mysql-bin.000001',master_log_pos=151;
#停止链路
mysql> stop slave;
#启动链路
mysql> start slave;
#查看链路
mysql> show slave status \G;
重置绑定
mysql> reset master;
重置同步日志
mysql> reset slave;9、校验同步状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.19.129
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 4974
Relay_Log_File: s-relay-bin.000002
Relay_Log_Pos: 5145
Relay_Master_Log_File: mysql-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: 4974
Relay_Log_Space: 5349
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: 129
Master_UUID: da0d7492-4bfb-11eb-8e7c-000c290b2ad9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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 (0.00 sec)两项显示Yes表示同步正常，说明实现了slave－>master的同步环境
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MySQL主Master增、删、改，查看MySQL从Slave库中是否一致为考虑新手操作方便，本次使用Navicat来操作增、删、改（图一为主库要删部分、图二为从库未同步之前与主库一致、图三为同步之后状态）
完结！
MySQL主从复制实例环境搭建与MySQL数据库迁移完整案例分享
https://cn.10691.cn//archives/10057