MySql主從部署

1、操作環境

硬件環境：香橙派5 aarch64架構
軟件環境：Ubuntu 22.04.3 LTS
軟件版本：mysql-8.0.42
操作方式：mysql_1,mysql_2容器
主節點：mysql_1
啟動命令：docker run --name mysql_master \-p 3308:3306 \-v /data/Test/mysql_master/data:/var/lib/mysql \-v /data/Test/mysql_master/conf:/etc/mysql/conf.d \-v /data/Test/mysql_master/logs:/var/log/mysql \-v /etc/localtime:/etc/localtime:ro \-e MYSQL_ROOT_PASSWORD=test0123 \-e MYSQL_DATABASE=myappdb \-e MYSQL_USER=myuser \-e MYSQL_PASSWORD=test0123 \--restart=always \-d mysql:8.0.42 \--default-authentication-plugin=mysql_native_password \--character-set-server=utf8mb4 \--collation-server=utf8mb4_unicode_ci從節點：mysql_2
啟動命令：docker run --name mysql_slave \-p 3309:3306 \-v /data/Test/mysql_slave/data:/var/lib/mysql \-v /data/Test/mysql_slave/conf:/etc/mysql/conf.d \-v /data/Test/mysql_slave/logs:/var/log/mysql \-v /etc/localtime:/etc/localtime:ro \-e MYSQL_ROOT_PASSWORD=test0123 \-e MYSQL_DATABASE=myappdb \-e MYSQL_USER=myuser \-e MYSQL_PASSWORD=test0123 \--restart=always \-d mysql:8.0.42 \--default-authentication-plugin=mysql_native_password \--character-set-server=utf8mb4 \--collation-server=utf8mb4_unicode_ci

2、配置操作

2.1、主節點配置

（1）、my.cnf配置（容器/etc/mysql/conf.d/my.cnf，宿主機/data/Test/mysql_master/conf/master.cnf）

[client]
default-character-set=utf8mb4[mysql]
default-character-set=utf8mb4[mysqld]
# 設置時區
default-time_zone = '+8:00'
#設置密碼驗證規則
authentication_policy=mysql_native_password
# 限制導入和導出的數據目錄
# 為空，不限制導入到處的數據目錄；
secure_file_priv=
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve# 開啟logbin
log_bin=binlog
# binlog日志格式
binlog_format=ROW
# mysql主從備份serverId
server_id=1#參數優化，避免mysql占用太多內存
# 減少緩沖池大小
innodb_buffer_pool_size = 64M
# 降低臨時表大小
tmp_table_size = 16M
# 設置最大連接數
max_connections = 10

可選參數

# 0表示讀寫 (主機)，1表示只讀(從機)
read-only=0#設置日志文件保留的時長，單位是秒
binlog_expire_logs_seconds=6000#控制單個二進制日志大小。此參數的最大和默認值是1GB
max_binlog_size=20#設置不要復制的數據庫
binlog-ignore-db=test#設置需要復制的數據庫,不寫參數則默認全部記錄，可以填寫多個
binlog-do-db=需要復制的主數據庫名字
例如:
binlog-do-db=dbtest01
binlog-do-db=dbtest02#設置binlog格式
binlog_format=STATEMENT

（2）、主節點數據庫創建用戶

#進入容器內部
docker exec -it mysql_master /bin/bash
#登錄mysql
mysql -u root -p
#查看數據庫
show databases;
#切換到mysql庫
use mysql;
#創建master用戶
create USER 'master'@'%' IDENTIFIED BY 'root';
grant all on *.* to 'master'@'%';
FLUSH PRIVILEGES;

2.2、從節點配置

（1）、server.cnf配置（容器/etc/mysql/conf.d/server.cnf，宿主機的/data/Test/mysql_2/conf/server.cnf）

[client]
default-character-set=utf8mb4[mysql]
default-character-set=utf8mb4[mysqld]
# 設置時區
default-time_zone = '+8:00'
#設置密碼驗證規則
authentication_policy=mysql_native_password
# 限制導入和導出的數據目錄
# 為空，不限制導入到處的數據目錄；
secure_file_priv=
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve#server_id不要和主庫的server_id相同就行
server_id=2
#一般從數據庫作為讀數據庫
default-storage-engine=MyISAM

（2）、從節點數據庫創建用戶

#進入容器內部
docker exec -it mysql_slave /bin/bash
#登錄mysql
mysql -u root -p
#查看數據庫
show databases;
#切換到mysql庫
use mysql;
#創建slave用戶
create USER 'slave'@'%' IDENTIFIED BY 'root';
grant all on *.* to 'slave'@'%';
FLUSH PRIVILEGES;

2.3、配置數據同步

（1）、登錄master節點數據庫

#查看master節點binlog日志狀態
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      841 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

（1）、登錄slave節點數據庫

#停止slave
stop slave;#查看master節點binlog日志狀態，MASTER_LOG_FILE、MASTER_LOG_POS根據master數據庫查詢信息配置CHANGE MASTER TO MASTER_HOST = '172.17.0.5', #hostMASTER_PORT = 3306,#MASTER_USER = 'master',#binlogMASTER_PASSWORD = 'passwd',#binlogMASTER_LOG_FILE = 'binlog.000003',#binlog1MASTER_LOG_POS = 841;#啟動slave
start slave;#查看slave庫狀態
mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.17.0.5Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000003Read_Master_Log_Pos: 841Relay_Log_File: 4b87ab620b91-relay-bin.000002Relay_Log_Pos: 323Relay_Master_Log_File: binlog.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 841Relay_Log_Space: 540Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 61357c39-2e76-11f0-9f6f-0242ac110005Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

2.4、測試

（1）、master節點創建測試表及插入數據

mysql -u root -h 127.0.0.1 -p -P3308#登錄master節點創建簡單數據表
CREATE TABLE `test` (`id` int NOT NULL AUTO_INCREMENT,`Test_str` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;mysql> show tables;
+-------------------+
| Tables_in_myappdb |
+-------------------+
| test              |
+-------------------+
1 row in set (0.00 sec)mysql> select * from test;
Empty set (0.00 sec)mysql> INSERT INTO test (Test_str) VALUES( '1');
Query OK, 1 row affected (0.02 sec)mysql> select * from test;
+----+----------+
| id | Test_str |
+----+----------+
|  1 | 1        |
+----+----------+
1 row in set (0.00 sec)

（2）、slave節點查看數據同步

mysql -u root -h 127.0.0.1 -p -P3309mysql> use myappdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+-------------------+
| Tables_in_myappdb |
+-------------------+
| test              |
+-------------------+
1 row in set (0.01 sec)mysql> select * from test;
+----+----------+
| id | Test_str |
+----+----------+
|  1 | 1        |
+----+----------+
1 row in set (0.01 sec)