一、需求背景

MySQL 8.4.2版本數(shù)據(jù)庫安裝后，需要搭建主從同步。

二、Master配置

配置文件內(nèi)容如下：

[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創(chuàng)建主從同步賬戶

# 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節(jié)點配置更改

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

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

server-id = 7

重啟MySQL服務(wù)

# 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;  # 從服務(wù)器獲取主服務(wù)器的公鑰，以便進行安全連接。(不加會有問題)

啟動同步機制

mysql>start replica;

查看slave同步狀態(tài)：show replica status \G

六、主從同步驗證

1、Master建庫并導(dǎo)入數(shù)據(jù)：

mysql> CREATE DATABASE vehicle_s1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

mysql> use vehicle_s1;

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

從庫驗證發(fā)現(xiàn)已經(jīng)成功同步。

2、停止從庫同步

mysql> stop replica;  #再觀察主從同步：從庫停止同步后，主庫創(chuàng)建庫，無法同步。從庫啟動同步進程后，未同步的庫會自動同步

mysql> start replica;

mysql> show databases;

參考資料鏈接：

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

原諒來源：https://mp.weixin.qq.com/s/x8iEwOAYx8rCwqiReWqP6A