前言:
本文主要讲述MySQL5.7到MySQL8.0的主从同步配置，MySQL8.0从2016年发布到现在，越来越多的用户将MySQL升级到了8.0版本，升级的方式主要有in-place upgrade，mysqldump，数据迁移+upgrade，主从同步切换方式等。
其中主从同步切换是一种非常不错的升级方式，数据迁移安全，停机窗口短，操作简单。
不同版本主从配置条件:
从MySQL官方上看，MySQL是支持低版本主库到高版本从库的同步，如5.7到8.0的同步，但原则上不支持高版本主库到低版本从库的同步。
主从不同版本的兼容性，需要确认5.7版本与8.0版本之间的兼容性，包括数据字典，SQL语法，功能，参数，密码算法等
具体可以从官方文档去确认https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
测试环境:
| 主库(5.7.32) | 192.168.2.243 |
| 备库(8.0.29) | 192.168.2.242 |
使用mysqldump方式部署5.7-to-8.0的同步
说明:mysqldump方式部署主从，适合数据量小的环境，这里搭建的场景是在已有主库5.7.32环境下，部署一套8.0.29的从库
初始一套8.0的环境，作为从库环境，这里安装软件步骤省略，直接从初始化开始
my.cnf参数文件
[mysqld]
user = mysql
port = 3306
server_id = 242
basedir = /usr/local/mysql
datadir = /opt/mysql/data
log-error=/opt/mysql/log/error.log
socket = /opt/mysql/data/mysql.sock
pid-file = /opt/mysql/data/mysqld.pid
character_set_server = utf8mb4
log_timestamps = SYSTEM
lower_case_table_names = 1
max_allowed_packet = 32M
skip_name_resolve = 1
max_connections = 2000
max_connect_errors=1000000
interactive_timeout =1800
wait_timeout =1800
explicit_defaults_for_timestamp = 1
transaction_isolation =READ-COMMITTED
# BINLOG #
log_bin =/opt/mysql/log/binlog
log_bin_index=/opt/mysql/log/binlog.index
binlog_format = row
binlog_expire_logs_seconds=86400
binlog_rows_query_log_events=1
max_binlog_size = 1024M
relay_log = mysql-relay-bin
relay_log_recovery = 1
binlog_gtid_simple_recovery=1
sync_binlog = 1
table_open_cache = 4096
open_files_limit = 65535
thread_cache_size = 768
# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1
# INNODB #
default_storage_engine = InnoDB
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct=40
innodb_lru_scan_depth=2048
innodb_lock_wait_timeout=5
innodb_io_capacity=1000
innodb_io_capacity_max=2000
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
innodb_log_file_size=2048M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_max_dirty_pages_pct = 50
innodb_thread_concurrency = 0
innodb_print_all_deadlocks = 1
innodb_stats_on_metadata = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_sort_buffer_size = 16M
innodb_strict_mode = 1
innodb_page_cleaners = 8
innodb_undo_directory=/opt/mysql/data
innodb_undo_log_truncate=1
innodb_rollback_segments=128
innodb_max_undo_log_size=2GB
innodb_purge_rseg_truncate_frequency=128
innodb_open_files=65535
# CACHE #
key_buffer_size = 32M
tmp_table_size = 64M
max_heap_table_size = 32M
join_buffer_size = 64M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sort_buffer_size = 32M
binlog_cache_size = 4M
max_binlog_cache_size=2g
# SLOW LOG #
slow_query_log = 1
slow_query_log_file =/opt/mysql/log/slow-query.log
log_slow_admin_statements = 1
log_queries_not_using_indexes=1
long_query_time = 1
[mysql]
socket = /opt/mysql/data/mysql.sock
prompt="\u@\p \R:\m:\s [\d]>"
[client]
socket = /opt/mysql/data/mysql.sock
[mysqldump]
max_allowed_packet = 128M初始化从库
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot123 --master-data=2 --set-gtid-purged=on --all-databases --triggers --routines --events --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > /tmp/mysql_all_backup.sql启动从库
service mysqld start
mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.29 MySQL Community Server - GPL
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.
root@mysql.sock 17:44: [(none)]>设置root密码
/usr/local/mysql/bin/mysqladmin -u root password 'root123'使用mysqldump导出主库备份,包括mysql库以及其他应用的全部数据库
注意:导出的时候，记得忽略mysql.innodb_table_stats，mysql.innodb_table_stats因为两张表在导入的时候，会提示Access to system table，是bug92675导致
(bug链接:https://bugs.mysql.com/bug.php?id=92675)
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot123 --master-data=2 --set-gtid-purged=on --all-databases --triggers --routines --events --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > /tmp/mysql_all_backup.sql导入之后，要更新一下数据字典mysql库，因为8.0不在使用mysqld_upgrade进行更新，而是在数据库启动的时候进行更新，所以要关闭数据库，使用mysqld --upgrade=FORCE方式进行更新
mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/data --upgrade=FORCE &更新之后，再重新启库去掉--upgrade=FORCE，并检查是否已经更新到最新版本
---正常启动
service mysqld start
---检查
[root@ol7db1 subsys]# mysqlcheck -uroot -proot123 --all-databases --check-upgrade
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
mysql.columns_priv Table is already up to date
mysql.component Table is already up to date
mysql.db Table is already up to date
mysql.default_roles Table is already up to date
mysql.engine_cost Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.general_log Table is already up to date
mysql.global_grants Table is already up to date
mysql.gtid_executed Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.innodb_index_stats Table is already up to date
mysql.innodb_table_stats Table is already up to date
mysql.ndb_binlog_index Table is already up to date
mysql.password_history Table is already up to date
mysql.plugin Table is already up to date
mysql.proc Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.replication_asynchronous_connection_failover Table is already up to date
mysql.replication_asynchronous_connection_failover_managed Table is already up to date
mysql.replication_group_configuration_version Table is already up to date
mysql.replication_group_member_actions Table is already up to date
mysql.role_edges Table is already up to date
mysql.server_cost Table is already up to date
mysql.servers Table is already up to date
mysql.slave_master_info Table is already up to date
mysql.slave_relay_log_info Table is already up to date
mysql.slave_worker_info Table is already up to date
mysql.slow_log Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
sys.sys_config Table is already up to date
test.test Table is already up to date
test1.test1 Table is already up to date配置主5.7到从8.0的同步
主库创建同步用户repl
create user 'repl'@'%' identified by 'repl#123';
grant replication slave on *.* to 'repl'@'%' ;从库配置同步channel
注意:8.0不再使用change master,change master已经废弃
CHANGE REPLICATION SOURCE to SOURCE_HOST='192.168.2.243',SOURCE_USER='repl',SOURCE_PORT=3306,SOURCE_PASSWORD='repl#123',SOURCE_AUTO_POSITION=1;查看主从同步状态
root@mysql.sock 19:26: [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.2.243
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 56878549
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 14217438
Relay_Master_Log_File: binlog.000002
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: 28441994
Relay_Log_Space: 42654203
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: 2
Master_UUID: 45b42ee1-0b33-11ed-8902-000c29119c98
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: 45b42ee1-0b33-11ed-8902-000c29119c98:31-34
Executed_Gtid_Set: 45b42ee1-0b33-11ed-8902-000c29119c98:1-33
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.00 sec)使用xtrabackup方式部署5.7-to-8.0的同步
说明:mysqldump方式部署主从，适合数据量大的环境，这里搭建的场景是在已有主库5.7.32环境下，部署一套8.0.29的从库
在主库通过xtrabackup执行一个全备
xtrabackup -uroot -proot123 -P3306 -S /opt/mysql/data/mysql.sock --backup --target-dir=/tmp/xtrabackup注意全备的prepare要在主库先进行，因为从库环境是8.0的版本，没法正常apply log
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/tmp/xtrabackup/在从库环境恢复全备数据文件到datadir目录
注意:对于8.0.14之后的版本，由于innodb_undo_tablespaces参数已经废弃（使用create or drop undo tablespace 命令替代），my.cnf不在需要设置指定undo数量，但这可能导致从5.7版本的备份里面恢复时，不copy-backup undo表空间，所以在执行copy-back之前，先需要临时在/etc/my.cnf里面添加innodb_undo_tablespaces参数，才可以确保undo表空间正常copy-back
---copy-back 恢复备份到指定datadir
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/tmp/xtrabackup/
---创建log_error
touch /opt/mysql/log/error.log
---修改权限
chown -R mysql.mysql /opt/mysql/启动数据库，使用mysqld --upgrade=FORCE方式进行更新
mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/data --upgrade=FORCE &从后台日志可以观察到upgrade的操作
2022-07-24T21:31:01.399989+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-07-24T21:31:01.400080+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-24T21:31:06.733879+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-07-24T21:31:08.960499+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2022-07-24T21:31:09.826143+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80029' started.
2022-07-24T21:31:16.972410+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80029' completed.重新正常启动mysql，去掉--upgrade=FORCE，并检查是否已经更新到最新版本
---正常启动
service mysqld start
---检查
[root@ol7db1 local]# mysqlcheck -uroot -proot123 --all-databases --check-upgrade
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
mysql.columns_priv Table is already up to date
mysql.component Table is already up to date
mysql.db Table is already up to date
mysql.default_roles Table is already up to date
mysql.engine_cost Table is already up to date
mysql.func Table is already up to date
mysql.general_log Table is already up to date
mysql.global_grants Table is already up to date
mysql.gtid_executed Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.innodb_index_stats Table is already up to date
mysql.innodb_table_stats Table is already up to date
mysql.ndb_binlog_index Table is already up to date
mysql.password_history Table is already up to date
mysql.plugin Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.replication_asynchronous_connection_failover Table is already up to date
mysql.replication_asynchronous_connection_failover_managed Table is already up to date
mysql.replication_group_configuration_version Table is already up to date
mysql.replication_group_member_actions Table is already up to date
mysql.role_edges Table is already up to date
mysql.server_cost Table is already up to date
mysql.servers Table is already up to date
mysql.slave_master_info Table is already up to date
mysql.slave_relay_log_info Table is already up to date
mysql.slave_worker_info Table is already up to date
mysql.slow_log Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
sys.sys_config Table is already up to date
test.test Table is already up to date
test1.test1 Table is already up to date
test1.test2 Table is already up to date配置主5.7到从8.0的同步
主库创建同步用户repl
create user 'repl'@'%' identified by 'repl#123';
grant replication slave on *.* to 'repl'@'%' ;从库设置gtid_purged
---从备份的脚本里面查看备份的时点gtid
cat xtrabackup_binlog_info
binlog.000002 66742930 45b42ee1-0b33-11ed-8902-000c29119c98:1-30861
---从库设置gtid_purged
SET @@GLOBAL.GTID_PURGED='45b42ee1-0b33-11ed-8902-000c29119c98:1-30861';从库配置同步channel
注意:8.0不再使用change master,change master已经废弃
CHANGE REPLICATION SOURCE to SOURCE_HOST='192.168.2.243',SOURCE_USER='repl',SOURCE_PORT=3306,SOURCE_PASSWORD='repl#123',SOURCE_AUTO_POSITION=1;查看主从同步状态
root@mysql.sock 22:01: [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.2.243
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 94205812
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 448
Relay_Master_Log_File: binlog.000002
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: 94205812
Relay_Log_Space: 94206523
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: 2
Master_UUID: 45b42ee1-0b33-11ed-8902-000c29119c98
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: 45b42ee1-0b33-11ed-8902-000c29119c98:1-116683
Executed_Gtid_Set: 45b42ee1-0b33-11ed-8902-000c29119c98:1-116683
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.00 sec)
扫一扫
3349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
