目錄
1 操作系統信息
2 MySql數據庫版本
3 主機列表
4 MySQL服務器都安裝依賴
5 主庫服務器安裝mysql軟件步驟：
6 從服務器安裝mysql軟件步驟
7 基于二進制日志文件位置的主從復制配置
8 使用全局事務標識符進行主從復制(GTID)
9 部署過程遇到問題
1 操作系統信息
root@u24-mysql-51:/mysql# cat /etc/issue
Ubuntu 24.04.2 LTS \n \l
2 MySql數據庫版本
mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz
3 主機列表
192.168.254.51 ?#主庫
192.168.254.52 ?#從庫
4 MySQL服務器都安裝依賴
apt-get install libaio1
5 主庫服務器安裝mysql軟件步驟：
tar -xf mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz -C /usr/local
cd /usr/local
mv mysql-8.0.42-linux-glibc2.28-x86_64 mysql-8.0.42
#創建用戶和組
groudadd mysql
useradd -g mysql -s /sbin/nologin mysql
#給MySql軟件目錄授權
chown -r mysql:mysql /usr/local/mysql-8.0.42
#編寫my.cnf配置文件
root@u24-mysql-51:~# cat /etc/my.cnf?
[client]
socket = /mysql/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql-8.0.42
datadir = /mysql/data
user = mysql
port = 3306
socket = /mysql/data/mysql.sock
log_error = /mysql/data/mysqld.err
log_timestamps = system
log-bin = mysql-bin
server-id = 51
#初始化數據庫
mkdir /mysql/data -p
chown -R mysql:mysql /mysql/data
/usr/local/mysql-8.0.42/bin/mysqld --defaults-file=/etc/my.cnf --initialize
#配置啟動文件systemd
root@u24-mysql-52:/mysql/data# cat /lib/systemd/system/mysql.service?
[Unit]
Description=MySQL server
Documentation=man:mysql(8)?
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/mysql/data/mysqld.pid
TimeoutSec=0
ExecStart=/usr/local/mysql-8.0.42/bin/mysqld --defaults-file=/etc/my.cnf --pid-file=/mysql/data/mysqld.pid --daemonize $MYSQLD_OTPS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE=65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=False
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
#啟動MySQL
systemctl start mysql
systemctl enable mysql
#登錄并修改密碼
root@u24-mysql-51:~# grep password /mysql/data/mysqld.err?
2025-05-28T23:13:58.538997+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: shAuAVU%f5a-
/usr/local/mysql-8.0.42/bin/mysql -uroot -p'shAuAVU%f5a-'
mysql>alter user user() identified by '123456';
#配置環境變量
echo 'export PATH=$PATH:/usr/local/mysql-8.0.42/bin' >>~/.bashrc
6 從服務器安裝mysql軟件步驟
#從主庫復制軟件和數據庫文件
rsync -r /mysql 192.168.254.52:/
rsync -r /usr/local/mysql-8.0.42 192.168.254.52:/usr/local/
rsync /lib/systemd/system/mysql.service 192.168.254.52:/lib/systemd/system/mysql.service
rsync /etc/my.cnf 192.168.254.52:/etc/my.cnf
#創建用戶和組
groupadd mysql?
useradd -g mysql -s /sbin/nologin mysql
#給軟件和數據庫文件授權
chown -R mysql:mysql /mysql/
chown -R mysql:mysql /usr/local/mysql-8.0.42
#配置環境變量
echo 'export PATH=$PATH:/usr/local/mysql-8.0.42/bin' >>~/.bashrc
source ~/.bashrc
#啟動MySQL
systemctl start mysql
systemctl enable mysql
7 基于二進制日志文件位置的主從復制配置
7.1 主庫配置
添加server-id和開啟binlog
root@u24-mysql-51:~# cat /etc/my.cnf?
[mysqld]
log-bin = mysql-bin
server-id = 51
systemctl restart mysql
#創建用于復制的用戶
mysql -uroot -p
mysql> CREATE USER 'repl'@'192.168.254.%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.254.%';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status \G;
*************************** 1. row ***************************
? ? ? ? ? ? ?File: mysql-bin.000007
? ? ? ? ?Position: 157
? ? ?Binlog_Do_DB:?
?Binlog_Ignore_DB:?
Executed_Gtid_Set:?
1 row in set (0.00 sec)
#使用 mysqldump 備份數據
root@u24-mysql-51:~# mysqldump -uroot -p --all-databases --source-data > data.sql
#解鎖
mysql -uroot -p
mysql> unlock tables;
#將data.sql備份文件傳送到從庫
root@u24-mysql-51:~#rsync data.sql 192.168.254.52:~
7.2 從庫配置(可以配置多臺從庫)
#添加server-id
root@u24-mysql-51:~# cat /etc/my.cnf?
[mysqld]
server-id = 52
#修改uuid,保證主從服務器上uuid是唯一，否則出現問題1報錯
root@u24-mysql-52:~# vi /mysql/data/auto.cnf?
[auto]
server-uuid=5d7fd409-3bd6-11f0-9d66-000c29704178
#重啟數據庫
systemctl restart mysql
#將data.sql數據導入數據庫
root@u24-mysql-52:~# mysql -uroot -p < data.sql?
#在從庫服務器上設置源配置
mysql>change replication source to source_host='192.168.254.51', source_user='repl', source_log_file='mysql-bin.000007', source_log_pos=157, SOURCE_PASSWORD='password',get_master_public_key=1;
mysql> start replica;
Query OK, 0 rows affected (0.41 sec)
mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for source to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.254.51
? ? ? ? ? ? ? ? ? Master_User: repl
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000007
? ? ? ? ? Read_Master_Log_Pos: 157
? ? ? ? ? ? ? ?Relay_Log_File: u24-mysql-52-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 326
? ? ? ? Relay_Master_Log_File: mysql-bin.000007
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
#出現以下兩個yes說明主從復制已經完成
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8 使用全局事務標識符進行主從復制(GTID)
8.1 主庫配置
#在my.cnf添加以下參數?
root@u24-mysql-51:~# cat /etc/my.cnf?
[mysqld]
log-bin = mysql-bin
server-id = 51
gtid_mode=ON
enforce-gtid-consistency=ON
#創建用于復制的用戶
mysql -uroot -p
mysql> CREATE USER 'repl'@'192.168.254.%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.254.%';
mysql> FLUSH TABLES WITH READ LOCK;
#使用 mysqldump 備份數據
root@u24-mysql-51:~# mysqldump -uroot -p --all-databases --source-data > data.sql
#解鎖
mysql -uroot -p
mysql> unlock tables;
#將data.sql備份文件傳送到從庫
root@u24-mysql-51:~#rsync data.sql 192.168.254.52:~
8.2 從庫配置
#在my.cnf添加以下參數?
root@u24-mysql-51:~# cat /etc/my.cnf?
[mysqld]
log-bin = mysql-bin
server-id = 52
gtid_mode=ON
enforce-gtid-consistency=ON
#修改uuid,保證主從服務器上uuid是唯一，否則出現問題1報錯
root@u24-mysql-52:~# vi /mysql/data/auto.cnf?
[auto]
server-uuid=5d7fd409-3bd6-11f0-9d66-000c29704178
#將data.sql數據導入數據庫
root@u24-mysql-52:~# mysql -uroot -p < data.sql?
#在從庫服務器上設置源配置
mysql>change replication source to source_host='192.168.254.51', source_user='repl', SOURCE_PASSWORD='password',SOURCE_AUTO_POSITION = 1,get_master_public_key=1;
mysql> start replica;
Query OK, 0 rows affected (0.41 sec)
mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for source to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.254.51
? ? ? ? ? ? ? ? ? Master_User: repl
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000007
? ? ? ? ? Read_Master_Log_Pos: 157
? ? ? ? ? ? ? ?Relay_Log_File: u24-mysql-52-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 326
? ? ? ? Relay_Master_Log_File: mysql-bin.000007
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
#出現以下兩個yes說明主從復制已經完成
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9 部署過程遇到問題
問題1：
mysql> show replica status \G
? ? ??
Source_SSL_Verify_Server_Cert: Yes
? ? ? ? ? ? ? ? Last_IO_Errno: 13117
? ? ? ? ? ? ? ? Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解決方法：
修改server-uuid的值，保證集群唯一
root@u24-mysql-52:/mysql/data# vi auto.cnf?
[auto]
server-uuid=5d7fd409-3bd6-11f0-9d66-000c29704178
問題2：
mysql> show replica status \G;
Source_SSL_Verify_Server_Cert: Yes
? ? ? ? ? ? ? ? Last_IO_Errno: 2061
? ? ? ? ? ? ? ? Last_IO_Error: Error connecting to source 'repl@192.168.254.51:3306'. This was attempt 11/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
解決方法：
添加：get_master_public_key=1 參數
mysql>change replication source to source_host='192.168.254.51', source_user='repl', source_log_file='mysql-bin.000007', source_log_pos=157, SOURCE_PASSWORD='password',，get_master_public_key=1;
)
