一、需求背景

MySQL 8.4.2版本数据库安装后，需要搭建主从同步。

二、Master配置

配置文件内容如下：

[root@wqcycdldp1 yum.repos.d]# cat /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port = 3306

character-set-server=utf8

interactive_timeout = 120

wait_timeout = 120

key_buffer_size = 16M

bind-address=0.0.0.0

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

max_connections = 1000

server-id = 6

log-bin = /var/log/mysql/binlog/mysql-bin

[root@localhost ~]# mkdir -p /var/log/mysql/binlog

[root@localhost ~]# chmod 755 /var/log/mysql/binlog

[root@localhost ~]# chown -R mysq.mysql /var/log/mysql/binlog

[root@localhost ~]# systemctl restart mysqld

三、Master创建主从同步账户

# mysql -uroot -p

mysql> CREATE USER 'sync'@'%' IDENTIFIED BY 'WQcldp@2025';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';

mysql> flush privileges;

四、Master查询binlog文件位置

mysql> show binary log status;

mysql> show binary log status;

+------------------+-----------+--------------+------------------+-------------------+

File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+-----------+--------------+------------------+-------------------+

mysql-bin.000002 | 158 |              |                  |                   |

+------------------+-----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

五、slave节点配置更改

[root@localhost ~]# vim /etc/my.cnf

[mysqld]    # [mysqld]在下面添加

server-id = 7

重启MySQL服务

# mysql -uroot -p

change replication source to

source_host='10.18.11.6',

source_user='sync',

source_password='WQcldp@2024',

source_port=3306,

source_log_file='mysql-bin.000002',

source_log_pos=158,

get_source_public_key=1;  # 从服务器获取主服务器的公钥，以便进行安全连接。(不加会有问题)

启动同步机制

mysql>start replica;

查看slave同步状态：show replica status \G

六、主从同步验证

1、Master建库并导入数据：

mysql> CREATE DATABASE vehicle_s1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

mysql> use vehicle_s1;

mysql> source /app/mysql-vehicle_s1-20241212003001.sql

从库验证发现已经成功同步。

2、停止从库同步

mysql> stop replica;  #再观察主从同步：从库停止同步后，主库创建库，无法同步。从库启动同步进程后，未同步的库会自动同步

mysql> start replica;

mysql> show databases;

参考资料链接：

https://blog.csdn.net/weixin_60156113/article/details/144292232

原谅来源：https://mp.weixin.qq.com/s/x8iEwOAYx8rCwqiReWqP6A