MySQL 5.7 是流行的开源数据库的最新版本，它提供了新的可扩展性功能，应该及时更新，以发挥更好的性能。 为了突出其中一个变化，可扩展性已经大大提高。下面介绍如何从更低版本的数据同步到MySQL5.7或更高版本。
1、环境：
主库：10.1.1.1 mysql 5.5.27
从库：10.1.1.2 mysql 5.7.14-7
2、原理：由于复制的原理没有改变，在没有开启GTID的时候Master的版本可以是MySQL5.5、5.6、5.7。并且从库需要master-info-repository、relay-log-info-repository设置为table，否则会报错：
ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.
需要注意：从的版本若是5.7.x～5.7.13，主的版本不能是MySQL5.5，因为MySQL5.5没有server_uuid函数。该问题在MySQL5.7.13里修复(Bug #22748612)
set global gtid_mode= OFF
set global master_info_repository= TABLE
set global relay_log_info_repository= TABLE
3、主库建立复制账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.%' IDENTIFIED BY 'abc123';
4、从库配置访问主库
stop slave;
CHANGE MASTER TO MASTER_HOST=' 10.1.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='abc123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
start slave;
SHOW SLAVE STATUS\G
5、查看主从是否同步
ysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.1
Master_User: repl
Master_Port: 3309
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 261
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 304
Relay_Master_Log_File: mysql-bin.000001
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: 261
Relay_Log_Space: 505
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: 1111693309
Master_UUID:
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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
6、在线调整Replication Filter
这里只同步业务库，屏蔽其他数据库。
在从库先关闭sql线程，要是在多源复制中，是关闭所有channel的sql thread。
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
##过滤1个库
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(test);
Query OK, 0 rows affected (0.00 sec)
##过滤2个库
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(information_schema,dbmon,mysql,performance_schema,test);
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.1
Master_User: repl
Master_Port: 3309
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 296
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,mysql
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: 107
Relay_Log_Space: 532
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: 1111693309
Master_UUID:
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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
7、MySQL 5.7开启多并发复制
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-type=DATABASE #兼容MySQL 5.6基于schema级别的并发复制
slave-parallel-workers=16 ＃开启多线程复制
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
8、对于mysql系统表升级
方案1：在5.5主库上upgrap升级成5.7系统表
方案2：导出5.5权限IP，在5.7数据库中建立。简单
使用pt-show-grants工具导出。 #pt-show-grants --host='127.0.0.1' --user='root' -S /tmp/mysql3309.sock > /tmp/grants.sql
[root@sjs_111_169 ~]# more /tmp/grants.sql
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 5.5.27-rel29.0-log at 2016-09-26 12:02:43
-- Grants for 'admin'@'%'
GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*F9C16226630835340B31B7B5AE9828A2EE4BD501';
GRANT ALL PRIVILEGES ON `admin`.* TO 'admin'@'%';
-- Grants for 'cm'@'10.%'
GRANT USAGE ON *.* TO 'cm'@'10.%' IDENTIFIED BY PASSWORD '*F9C16226630835340B31B7B5AE9828A2EE4BD501';
GRANT ALL PRIVILEGES ON `cm`.* TO 'cm'@'10.%';
-- Grants for 'cpc'@'%'
GRANT USAGE ON *.* TO 'cpc'@'%' IDENTIFIED BY PASSWORD '*F9C16226630835340B31B7B5AE9828A2EE4BD501';
GRANT ALL PRIVILEGES ON `cpc`.* TO 'cpc'@'%';
-- Grants for 'dba'@'10.134.52.20'
查看指定mysql的所有用户权限： pt-show-grants --host='localhost' --user='root' --password='lin@10.1.xxx.xxx'
查看执行数据库的权限： pt-show-grants --host='localhost' --user='root' --password='lin@10.1.xxx.xxx' --database='hostsops'
查看每个用户权限生成 revoke收回权限的语句： pt-show-grants --host='localhost' --user='root' --password='lin@10.1.xxx.xxx' --revoke
长按二维码关注我们
