如何一步一步在CentOS7上配置MySQL 8.0.31 基于日志时间点儿的master-slave主从同步数据库
零背景说明
本文档记录如何在CentOS 7上，安装配置一主一从的MySQL 8.0数据库环境。
机器信息：
| IP | 系统版本 | 内核版本 | 角色 | MySQL版本 |
|---|---|---|---|---|
| 172.16.17.56 | CentOS Linux release 7.5.1804 (Core) | 3.10.0-862.el7.x86_64 x86_64 | master | 8.0.31 |
| 172.16.17.57 | CentOS Linux release 7.5.1804 (Core) | 3.10.0-862.el7.x86_64 x86_64 | slave | 8.0.31 |
一配置步骤
1 下载MySQL安装软件
为简便起见，我这里直接下载MySQL community server版的rpm格式的bundle包，即全家桶。
mkdir mysql8.0 cd mysql8.0/ wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar
2 解压软件
[root@host56 mysql8.0]# tar -xvf mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar mysql-community-client-8.0.31-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm mysql-community-common-8.0.31-1.el7.x86_64.rpm mysql-community-debuginfo-8.0.31-1.el7.x86_64.rpm mysql-community-devel-8.0.31-1.el7.x86_64.rpm mysql-community-embedded-compat-8.0.31-1.el7.x86_64.rpm mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm mysql-community-libs-8.0.31-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm mysql-community-server-8.0.31-1.el7.x86_64.rpm mysql-community-server-debug-8.0.31-1.el7.x86_64.rpm mysql-community-test-8.0.31-1.el7.x86_64.rpm [root@host56 mysql8.0]# ll mysql-*.rpm -rw-r--r--. 1 7155 31415 16916168 9月 14 16:28 mysql-community-client-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 2633904 9月 14 16:29 mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 662344 9月 14 16:29 mysql-community-common-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 507228688 9月 14 16:30 mysql-community-debuginfo-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 1961680 9月 14 16:30 mysql-community-devel-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 4216960 9月 14 16:31 mysql-community-embedded-compat-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 2218812 9月 14 16:31 mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 1582440 9月 14 16:31 mysql-community-libs-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 685968 9月 14 16:31 mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 67166828 9月 14 16:31 mysql-community-server-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 25248244 9月 14 16:32 mysql-community-server-debug-8.0.31-1.el7.x86_64.rpm -rw-r--r--. 1 7155 31415 275570172 9月 14 16:34 mysql-community-test-8.0.31-1.el7.x86_64.rpm [root@host56 mysql8.0]# ll mysql-*.rpm|wc -l 12 [root@host56 mysql8.0]#
解压之后一共12个rpm软件包文件。
3 安装MySQL
[root@host56 mysql8.0]# rpm -qa|grep mysql
[root@host56 mysql8.0]# pwd
/root/mysql8.0
[root@host56 mysql8.0]# for f in `ls *.rpm`;do rpm -ivh $f;done
警告：mysql-community-client-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-client-8.0.31-1.e################################# [100%]
警告：mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-client-plugins-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-common-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-common-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-debuginfo-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-debuginfo-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-devel-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-devel-8.0.31-1.el################################# [100%]
警告：mysql-community-embedded-compat-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-embedded-compat-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-icu-data-files-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-libs-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-libs-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
软件包 mysql-community-libs-compat-8.0.31-1.el7.x86_64 已经安装
警告：mysql-community-server-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-server-8.0.31-1.e################################# [100%]
警告：mysql-community-server-debug-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-server-debug-8.0.################################# [100%]
警告：mysql-community-test-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-test-8.0.31-1.el7################################# [100%]
[root@host56 mysql8.0]# rpm -qa|grep mysql
mysql-community-common-8.0.31-1.el7.x86_64
mysql-community-server-8.0.31-1.el7.x86_64
mysql-community-icu-data-files-8.0.31-1.el7.x86_64
mysql-community-embedded-compat-8.0.31-1.el7.x86_64
mysql-community-client-8.0.31-1.el7.x86_64
mysql-community-test-8.0.31-1.el7.x86_64
mysql-community-debuginfo-8.0.31-1.el7.x86_64
mysql-community-libs-compat-8.0.31-1.el7.x86_64
mysql-community-server-debug-8.0.31-1.el7.x86_64
mysql-community-libs-8.0.31-1.el7.x86_64
mysql-community-client-plugins-8.0.31-1.el7.x86_64
mysql-community-devel-8.0.31-1.el7.x86_64
[root@host56 mysql8.0]# rpm -qa|grep mysql|wc -l
12
[root@host56 mysql8.0]# 安装过程中，如果有提示缺失软件包导致安装失败的话，需要手工安装对应的软件包。然后再尝试重新安装，当然这些rpm安装包之间，也存在需要先后安装的关系，这样初次执行安装时，有可能因为依赖关系导致安装不成功，我们可以重复执行上面的shell for循环命令来安装。一句话小结：这个shell for loop命令反复执行几次，没事儿。
之前写的一篇安装参考文档:如何在CentOS7上安装MySQL 8数据库
http://www.knockatdatabase.com/2022/07/14/how-to-install-mysql-8-on-centos7-via-rpm-bundles/
注意1📢：
通常，我们只需要安装mysql-community-{server,client,common,libs}-* 这几个软件包即可，其它的可以不需要安装，尤其是debug包。
注意2📢：
我们也可以采取这样的for loop命令来安装：for f in
ls *.rpm;do yum install -y $f;done 即，通过yum install 来安装软件包。
官方文档参考：https://dev.mysql.com/doc/refman/8.0/en/linux-installation-rpm.html
4 启动MySQL数据库
systemctl start mysqld第一次执行会初始化并启动MySQL数据库。
[root@host56 mysql8.0]# systemctl start mysqld
[root@host56 mysql8.0]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2022-10-26 08:41:46 CST; 28s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 10087 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 10524 (mysqld)
Status: "Server is operational"
Tasks: 39
Memory: 443.1M
CGroup: /system.slice/mysqld.service
└─10524 /usr/sbin/mysqld
10月 26 08:41:18 host56 systemd[1]: Starting MySQL Server...
10月 26 08:41:46 host56 systemd[1]: Started MySQL Server.
[root@host56 mysql8.0]# 5 修改root口令
MySQL从5.7版本开始，初次启动时，会把root用户临时口令，写入/var/log/mysqld.log文件中，我们先获取其临时口令
[root@host56 mysql8.0]# grep 'temporary password' /var/log/mysqld.log |tail -1 2022-10-26T00:41:30.023554Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: :*do<vEcG6>l [root@host56 mysql8.0]#
然后执行mysql_secure_installtion命令，进入交互式命令，根据提示来修改root用户口令、禁用root远程登录、禁用匿名用户登录、删除测试数据库等。
[root@host56 mysql8.0]# mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: #输入root用户临时口令 The existing password for the user account root has expired. Please set a new password. New password: #输入root用户新口令 Re-enter new password: #再次确认输入root用户新口令 The 'validate_password' component is installed on the server. The subsequent steps will run with the existing configuration of the component. Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #需要再次修改root口令吗？No ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #需要删除匿名用户吗？yes Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n #需要禁用root用户远程登录吗？No,我这里是测试使用，先放开root远程登录。生产环境最好禁用。 ... skipping. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #是否删除测试数据库。yes - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #是否重新加载权限表。yes. Success. All done! [root@host56 mysql8.0]#
6 slave节点重复上述1-5步骤
7 master节点修改/etc/my.cnf配置文件，重启MySQL
[root@host56 mysql8.0]# vi /etc/my.cnf ... server-id=56
配置文件中添加server-id=56
重启MySQL：
[root@host56 mysql8.0]# systemctl restart mysqld
[root@host56 mysql8.0]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2022-10-26 09:49:08 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8178 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8212 (mysqld)
Status: "Server is operational"
Tasks: 39
Memory: 356.5M
CGroup: /system.slice/mysqld.service
└─8212 /usr/sbin/mysqld
10月 26 09:49:07 host56 systemd[1]: Starting MySQL Server...
10月 26 09:49:08 host56 systemd[1]: Started MySQL Server.
[root@host56 mysql8.0]# 8 master节点添加复制用户并授权、获取binary log信息
命令信息：
CREATE USER 'replica'@'172.16.17.57' identified WITH caching_sha2_password by 'Onlyou_168'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.17.57'; flush privileges; show master status\G;
执行命令：
[root@host56 mysql8.0]# mysql -uroot -p'Onlyou_168'
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 8
Server version: 8.0.31 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.
mysql> CREATE USER 'replica'@'172.16.17.57' identified WITH caching_sha2_password by 'Onlyou_168';
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.17.57';
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: binlog.000002
Position: 886
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> 9 slave节点修改配置文件/etc/my.cnf并重启MySQL
配置文件中添加server-id=57。
[root@localhost ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
...
server-id=57
"/etc/my.cnf" 32L, 1256C written
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2022-10-26 10:04:31 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 12614 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 12638 (mysqld)
Status: "Server is operational"
Tasks: 39
CGroup: /system.slice/mysqld.service
└─12638 /usr/sbin/mysqld
10月 26 10:04:29 localhost.localdomain systemd[1]: Starting MySQL Server...
10月 26 10:04:31 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# 这里在配置文件中之所以添加server-id并在主从上加以区分的目的：防止将来两个节点配置为互为主从时，binary log没区分开，主库上的binary log传递到slave节点，slave应用binary log时，重复产生binary log，又传递到master节点重复被apply导致的循环同步执行。
10 slave节点开启同步
同步命令：
stop slave; CHANGE MASTER TO MASTER_HOST='172.16.17.56' ,MASTER_USER='replica' ,MASTER_PASSWORD='Onlyou_168',GET_MASTER_PUBLIC_KEY=1,MASTER_LOG_FILE='binlog.000002',master_log_pos=719; start slave; show slave status\G;
执行命令：
[root@localhost ~]# mysql -uroot -p'Onlyou_168';
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 8
Server version: 8.0.31 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.
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='172.16.17.56' ,MASTER_USER='replica' ,MASTER_PASSWORD='Onlyou_168',GET_MASTER_PUBLIC_KEY=1,MASTER_LOG_FILE='binlog.000002',master_log_pos=886;
Query OK, 0 rows affected, 9 warnings (0.20 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.17.56
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 886
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 323
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: 886
Relay_Log_Space: 537
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: 56
Master_UUID: e7cd213a-54c6-11ed-9168-005056a59231
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> 11 验证同步
master节点创建测试库，写测试数据：
[root@host56 mysql8.0]# mysql -uroot -p'Onlyou_168' 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 11 Server version: 8.0.31 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql> create database testdb; Query OK, 1 row affected (0.08 sec) mysql> create table testdb.t1(id int); Query OK, 0 rows affected (0.24 sec) mysql> insert into testdb.t1 values(100); Query OK, 1 row affected (0.09 sec) mysql>
salve节点，验证库和表数据是否同步过来？
[root@localhost ~]# mysql -uroot -p'Onlyou_168'; 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 15 Server version: 8.0.31 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 100 | +------+ 1 row in set (0.00 sec) mysql>
二 注意事项
1 MySQL 从8版本开始，自动开启binary log，可以不需要再在配置文件里显示指定开启binlog。当然，再次手工添加修改配置文件关于binlog的配置也没问题。
2 创建同步用户时指定WITH caching_sha2_password选项的原因，从MySQL 8.0开始，默认身份验证plugin是：default_authentication_plugin是：caching_sha2_password，所以我们需要显示指定。否则的话，在备库开启同步时，可能会在备库/var/log/mysqld.log文件里，看到类似下述错误：
2022-10-24T01:22:24.346458Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2022-10-24T01:22:24.358753Z 9 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replica@172.16.17.56:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061 2022-10-24T01:23:24.360234Z 9 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replica@172.16.17.56:3306' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
当我们使用默认的caching_sha2_password plugin时，在备库开启同步时，我们需要指定GET_MASTER_PUBLIC_KEY=1选项。
当然，我们也可以在创建同步用户时，指定：WITH mysql_native_password来避免该错误。
CREATE USER 'replica'@'172.16.17.57' identified WITH mysql_native_password by 'Onlyou_168';
3 防火墙引起的错误，如果备库访问主库时，主库的3306端口被防火墙拦截了。会在备库/var/log/mysqld.log文件里，看到类似下述错误：
2022-10-26T02:32:52.549442Z 11 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=host56-relay-bin' to avoid this problem. 2022-10-26T02:32:52.598629Z 11 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='172.16.17.57', master_port= 3306, master_log_file='binlog.000003', master_log_pos= 1561, master_bind=''. 2022-10-26T02:32:57.069737Z 12 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2022-10-26T02:32:57.070532Z 12 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replica@172.16.17.57:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '172.16.17.57:3306' (113), Error_code: MY-002003 2022-10-26T02:33:57.071160Z 12 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replica@172.16.17.57:3306' - retry-time: 60 retries: 2 message: Can't connect to MySQL server on '172.16.17.57:3306' (113), Error_code: MY-002003
解决办法，是主库上放开对MySQL 3306端口的限制。
4 基于日志时间点儿的主从同步数据库有一个弊端，那就是同一个事务，在主库和备库上可能分别位于不同的binlog以及不同的偏移量。潜在问题就是，一旦当前主从同步中的主库出现故障，提升备库为新主库时，有可能出现不能确定使用哪个具体的日志文件和偏移量来作为基础点。所以，这个问题成为了基于日志时间点儿该模式下的主从同步的一个诟病。
三扩展补充，如何配置成互为master-slave架构的数据库
上述场景下，我们完成了配置172.16.17.56为master，172.16.17.57为slave的主从同步数据库。接下来，我们扩展一下，配置172.16.17.57为slave的同时，作为172.16.17.56的master库的架构。
即172.16.17.56上修改的操作，会同步到172.16.17.57数据库上。同时，172.16.17.57上修改的操作，也会反向同步到172.16.17.56数据库上。
1 172.16.17.57新master上创建同步用户
[root@localhost mysql]# mysql -uroot -p'Onlyou_168';
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 17
Server version: 8.0.31 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.
mysql> CREATE USER 'replica'@'172.16.17.56' identified WITH caching_sha2_password by 'Onlyou_168';
Query OK, 0 rows affected (0.10 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.17.56';
Query OK, 0 rows affected (0.06 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: binlog.000003
Position: 1561
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>2 172.16.17.56新slave上开启同步
[root@host56 mysql8.0]# mysql -uroot -p'Onlyou_168' 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 11 Server version: 8.0.31 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. mysql> show slave status\G; Empty set, 1 warning (0.00 sec) ERROR: No query specified mysql> stop slave; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='172.16.17.57' ,MASTER_USER='replica' ,MASTER_PASSWORD='Onlyou_168',GET_MASTER_PUBLIC_KEY=1,MASTER_LOG_FILE='binlog.000003',master_log_pos=1561; Query OK, 0 rows affected, 9 warnings (0.12 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.26 sec) mysql>
3 验证同步
172.16.17.57新master上创建测试数据：insert into testdb.t1 values(200);
[root@localhost mysql]# mysql -uroot -p'Onlyou_168'; 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 19 Server version: 8.0.31 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. mysql> select * from testdb.t1; +------+ | id | +------+ | 100 | +------+ 1 row in set (0.00 sec) mysql> insert into testdb.t1 values(200); Query OK, 1 row affected (0.05 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 100 | | 200 | +------+ 2 rows in set (0.00 sec) mysql>
172.16.17.56新slave上查看数据：可以看到新插入的记录。
[root@host56 mysql8.0]# mysql -uroot -p'Onlyou_168' 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 18 Server version: 8.0.31 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. mysql> select * from testdb.t1; +------+ | id | +------+ | 100 | | 200 | +------+ 2 rows in set (0.00 sec) mysql>
四参考链接
https://blog.csdn.net/haveqing/article/details/124758666
官方文档参考：https://dev.mysql.com/doc/refman/8.0/en/linux-installation-rpm.html
一条评论
Pingback: