MySQL-5.7-to-8.0的主从同步_mysql5.7迁移8.0_牛牛的笔记的博客

相关文章推荐

愉快的黄豆 · mysql-8.0.16-winx64.zi ...· 4 月前 ·

爱跑步的冰棍 · mysql重建索引· 4 月前 ·

天涯 · 【MySQL】用Sequel ...· 7 月前 ·

冲动的香槟 · MySQL分页到了后面越来越慢，有什么好的解 ...· 9 月前 ·

魁梧的饭盒 · mysql配置文件my.cnf详解[部分]- ...· 1 年前 ·

读研的橙子 · 庆余年2大结局范闲林婉儿插曲-西瓜视频· 5 天前 ·

爱热闹的葫芦 · 《纯白青梅》我们学校的校花18岁就被人整怀孕 ...· 4 月前 ·

气势凌人的红烧肉 · electron打包你的h5应用成桌面应用 ...· 1 年前 ·

坚强的伤痕 · 《卡门》txt全集下载_百度知道· 1 年前 ·

本文主要讲述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
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)

本文主要讲述MySQL5.7到MySQL8.0的主从同步配置，MySQL8.0从2016年发布到现在，越来越多的用户将MySQL升级到了8.0版本，升级的方式主要有in-place upgrade，mysqldump，数据迁移+upgrade，主从同步切换方式等。其中主从同步切换是一种非常不错的升级方式，数据迁移安全，停机窗口短，操作简单。...