記錄Mysql主從
MySQL8.x 主從數據庫搭建
1. 準備工作-安裝MySQL
確保手中有多臺機器【在同一個電腦里面，創建多個虛擬機也可以----本文linux系統是centos7】。同時，這些機器之間可以互相通信！
此外，需要在這些機器上面安裝相同版本的MySQL。
安裝注意點：
感謝這篇文章：https://blog.csdn.net/G502770782/article/details/131216466
由于Centos7不支持libncurses.so.6，因此無法安裝使用glic2.17以上的MySQL8，所以Centos7只能選擇中glibc2.12版本的MySQL8。
【安裝見這兩篇文章】--
http://www.rzrgm.cn/MrYoodb/p/15811199.html
https://blog.csdn.net/qq_36408717/article/details/126705287
linux安裝MySQL8的
環境清除：
【1】最開始，檢查系統是否安裝了mariadb數據庫， mariadb數據庫是mysql的分支，是免費開源的。 mariadb和msyql會有沖突。首先要檢查安裝了mariadb, 如果有，需要卸載掉。檢查命令：
使用：yum list installed | grep mariadb 或 rpm -qa | grep mariadb
如果有，會顯示名字的，刪掉：
執行命令：yum -y remove xxx[上面顯示的名字]
或者執行：rpm -e [xx上面顯示的名字] --nodeps
【2】最好刪除電腦已存在的mysql
安裝
在官網下載mysql的壓縮包：【例如】mysql-8.0.26-linux-glibc2.28-x86_64.tar.xz
然后上傳到你的機器上面去安裝MySQL。
tar xvJf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
解壓完成之后，重命名為mysql8。【純屬個人喜好】然后進去創建data目錄;
配置好mysql環境變量 /etc/profile;
export PATH=$PATH:/usr/local/mysql8/bin [這個是你mysql的位置，因人而異]
# 如果設置這個不管用，可以再設置下面的：
# /etc/bashrc 是系統級的 bash 交互配置文件，無論登錄 shell 還是交互式非登錄 shell 都會加載它（針對 bash），因此將 PATH 設置添加到這里可以確保每次打開終端自動生效。
# 在文件末尾添加你的 PATH 配置（和/etc/profile中一致）：
然后編寫mysql配置文件：/etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql8/data/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
# 這個切記！！ 每個MySQL實例一定要唯一！！！！！！
# 比如master是1， slave就不能是1
server-id=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
port = 3306
socket = /usr/local/mysql8/data/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir = /usr/local/mysql8
datadir = /usr/local/mysql8/data
# 錯誤日志文件
log-error=/usr/local/mysql8/data/mysqld.log
# 進程 ID 文件
pid-file=/usr/local/mysql8/data/mysqld.pid
# 自定義二進制日志文件存放路徑
log-bin=/usr/local/mysql8/data/mysql-bin
# 啟用二進制日志（已在 log-bin 中啟用，無需重復）
# log-bin=mysql-bin
# 設置二進制日志過期時間（秒）
binlog_expire_logs_seconds=2592000 # 30天
# 設置單個二進制日志文件的最大大小（例如100MB）
max_binlog_size=100M
#lower_case_table_names=1
#如果要設置lower_case_table_names可以在初始化里面設置 ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql8/data --basedir=/usr/local/mysql8 --lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
新建mysql用戶組和用戶：
groupadd mysql
useradd -r -g mysql mysql
# 授予權限
chown -R mysql:mysql /usr/local/mysql8
chmod -R 755 /usr/local/mysql8
用該配置文件初始化：[ 進入bin目錄 ] -- 記住初始化密碼
./mysqld --initialize --user=mysql --datadir=/usr/local/mysql8/data --basedir=/usr/local/mysql8
/*
如果記不住的話，可以暫時跳過權限認證，以root進入mysql
*/
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
然后去，mysql8里面的support-files目錄里面就可以啟動mysql了：
./mysql.server start
./mysql.server restart
將MySQL變為系統服務：
添加mysqld服務到系統中（注意在mysql8文件下執行）,是 將 MySQL 的服務控制腳本復制到系統的 init.d 目錄
cp -a ./support-files/mysql.server /etc/init.d/mysql
| 部分 | 說明 |
|---|---|
cp -a |
復制時保留文件的所有屬性（權限、所有者、時間戳等） |
./support-files/mysql.server |
源文件路徑：當前目錄下的 support-files/mysql.server（MySQL自帶的啟動腳本） |
/etc/init.d/mysql |
目標路徑：系統服務管理目錄，復制后改名為 mysql |
授權以及添加服務
chmod +x /etc/init.d/mysql
chkconfig --add mysql
檢查binlog是否開啟
mysql啟動好之后，查看binlog位置及其狀態
SHOW VARIABLES like '%log_bin%'
--
log_bin ON
log_bin_basename /usr/local/mysql8/data/mysql-bin
log_bin_index /usr/local/mysql8/data/mysql-bin.index
show variables like '%binlog%'
--
binlog_cache_size 32768
binlog_checksum CRC32
binlog_direct_non_transactional_updates OFF
binlog_encryption OFF
binlog_error_action ABORT_SERVER
binlog_expire_logs_seconds 2592000
binlog_format ROW # binlog日志格式
....
2. 開始搭建
在兩個電腦上安裝好mysql并且啟動完成之后，就可以著手搭建主從了。
對了，記得開放端口3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
首先來一個別人的圖片：【https://blog.51cto.com/lenglingx/13601901】
從上面的圖我們可以知道mysql主從的大致流程。
首先，在主節點上操作
要配置一下哪些同步哪些不同步呢，這步可以跳過的：[ 可以加上這些東西，動了my.cnf的話，記得|重啟或者啟動|一下Mysql服務喔]
[mysqld]
# 需要同步的數據庫
# binlog-do-db=your_database
# 不需要同步的數據庫
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
主節點創建用于主從復制的數據庫Mysql賬號【如下所示創建了 slave1, 密碼是123456】
mysql> create user 'slave1'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'slave1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'slave1'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看主節點的二進制日志情況：其中File和Position兩個參數需要在從庫配置中使用
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 27147 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 如果這個時候，往主庫里面插入一條數據position會變喔，重啟【都會變】
然后，配置從節點
從這一節最開始的那個圖中，我們可以知道，從節點是需要中繼日志的！！所以，我們要把從節點的配置文件修改一下：/etc/my.cnf
[mysqld]
#配置唯一的服務器ID
server-id=2
#加上這個 開啟中繼日志，從主服務器上同步日志文件記錄到本地
relay-log=relay-log-bin
重啟一下mysql服務。然后用數據庫的root登錄slave數據庫實例
mysql> change master to master_host='192.168.110.128',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=156;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
#語法
change master to
master_host='主節點IP',
master_user='主節點用戶名',
master_password='用戶密碼',
master_log_file='mysql-bin.具體數字',
master_log_pos=具體值;
在從節點開啟slave同步，查看同步狀態:
mysql> change master to master_host='192.168.110.128',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=156;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.110.128
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 156
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.............
Slave_IO_Running：IO線程，負責與主機的io通信Slave_SQL_Running：SQL線程，責自己的slave MySQL進程
#開啟slave同步
mysql> start slave;
#關閉slave同步
mysql> stop slave;
#重設slave同步
mysql> reset slave;
3. 主庫已經運行一段時間
第二步在主庫，從庫都是新的實例，然后數據庫沒有其他數據的情況下，一搭建就會ok。
但是如果是主庫運行了一段時間，中間需要弄主從，按照上面過程搭建完成之后，然后往主庫里面插入數據，從庫會報錯的，說：找不到該數據庫。
這種情況，就需要盡可能不停止主庫，然后將數據在從庫還原一份兒，這樣主從復制就會沒有問題了。
首先：使用mysqldump工具：【不是在mysql里面執行的，是在shell里面】
-- 所有數據庫
mysqldump -uroot -p --single-transaction --source-data=2 --all-databases > full_backup.sql
-- 部分
mysqldump -uroot -p --single-transaction --source-data=2 [tbname ...] > xx.sql
-- 示例
mysqldump -uroot -p --single-transaction --source-data=2 ytw_db > /usr/local/full_backup.sql
-- source-data=2 會在備份文件中注釋 CHANGE MASTER 所需的 binlog 文件名和位置
-- 避免鎖表：--single-transaction 適用 InnoDB 引擎，非 InnoDB 表需 --lock-all-tables
然后把這個sql導入從庫。
如果說數據庫有點兒大，優化mysqldump的可行方案：
mysqldump -u user -p --single-transaction --quick dbname table1 > table1.sql
-- quick：禁用緩存，減少內存占用
還可以考慮壓縮：
mysqldump -u user -p dbname | gzip > backup.sql.gz # 備份時壓縮
gunzip < backup.sql.gz | mysql -u user -p dbname # 恢復時解壓流式導入
還可以用mydumper（備份）/myloader（恢復）。
如果說，數據還是大，比如說達到了幾百gb，上tb了。。。【這個Xtrabackup】
這個不在本文討論范疇。。
end. 補充點
e.1 binlog的position
這個東西是干什么的呢？
Binlog Position（二進制日志位置） 是確保數據一致性和復制準確性的核心機制。他可以明確告知從庫應從主庫的哪個binlog文件及具體位置開始同步數據。在主庫執行 SHOW MASTER STATUS; 獲取當前binlog位置（如 mysql-bin.000003 和 154），然后在從庫配置時通過 CHANGE MASTER TO ... MASTER_LOG_FILE='...', MASTER_LOG_POS=... 指定該位置。
比如說，binlog保存7天。如果說有這樣一個場景，我得到了主庫2025-06-01的數據備份并且同時記錄了binlog的position=150，將其還原到了salve數據庫實例中。然后再2025-06-02搭建好了主從復制，position是主庫的150開始，然后在從庫里面start slave命令的時候，從庫會自動還原06-01到06-02中間的所有數據變更。
初始狀態（2025-06-01）：主庫：數據狀態A（position=150），從庫：通過備份還原到狀態A
啟動復制（2025-06-02）
CHANGE MASTER TO
MASTER_LOG_FILE='binlog.00000X', -- 備份時記錄的binlog文件名
MASTER_LOG_POS=150; -- 備份時的position
START SLAVE;
同步過程：從庫IO線程向主庫請求從position=150開始的binlog，主庫發送2025-06-01 15:00（position=150）→ 2025-06-02的所有binlog事件，從庫SQL線程按順序重放這些事件，最終從庫達到與主庫完全一致的狀態；
| 時間范圍 | 主庫binlog事件 | 從庫動作 |
|---|---|---|
| 2025-06-01 15:00 | INSERT/UPDATE/DELETE (pos=150) | 重放第一個事件 |
| 2025-06-01 15:01 | 新事件 (pos=151) | 重放第二個事件 |
| ... | ... | ... |
| 2025-06-02 10:00 | 最新事件 (pos=XXXX) | 持續重放直到追上主庫 |
主從復制其他重點參數（通過 SHOW SLAVE STATUS\G 查看）：
Read_Master_Log_Pos：從庫已讀取的主庫binlog位置（IO線程狀態）。Exec_Master_Log_Pos：從庫已執行的主庫binlog位置（SQL線程狀態）
延遲判斷：若 Exec_Master_Log_Pos 長期落后于 Read_Master_Log_Pos，表明SQL線程處理速度不足，存在復制延遲（Seconds_Behind_Master > 0）。
針對上面的問題，可以
MySQL配置優化
啟用并行復制：
# my.cnf (MySQL 5.7+)
slave_parallel_workers = 8 # 建議設置為CPU核數的2倍
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1 # 保持事務順序(8.0默認)
# MySQL 8.0 增強
binlog_transaction_dependency_tracking = WRITESET
增大復制緩沖區:
# 專用于復制線程的內存
slave_pending_jobs_size_max = 256M # 5.6+默認16M，建議提升
slave_preserve_commit_order = 1 # 并行復制保序
主庫大事務拆分、寫操作盡量改成批量的。
e.2 配置文件參數解釋
[client]
port=3306
# 設置 MySQL 客戶端默認字符集
default-character-set=utf8mb4
# 指定 MySQL 客戶端用來與 MySQL 服務器通信的本地 socket 文件的位置。
socket=/var/lib/mysql/mysql.sock
[mysql]
# 設置 MySQL 客戶端默認字符集
default-character-set=utf8mb4
# 禁用反向 DNS 解析，以提高連接性能。
skip-name-resolve
# 指定 MySQL 客戶端連接的端口號。
port=3306
# 指定 MySQL 客戶端使用的 UNIX 域套接字文件的位置。
socket=/var/lib/mysql/mysql.sock
[mysqld]
# 服務器端口
port=3306
# MySQL 的安裝目錄
basedir=/usr/local/mysql8
# MySQL 的數據目錄
datadir=/var/lib/mysql
# 錯誤日志文件
log-error=/var/log/mysql/mysqld.log
# 進程 ID 文件
pid-file=/var/run/mysqld/mysqld.pid
# 自定義二進制日志文件存放路徑
log-bin=/var/lib/mysql/mysql-bin
# 啟用二進制日志（已在 log-bin 中啟用，無需重復）
# log-bin=mysql-bin
# 設置二進制日志過期時間（秒）
binlog_expire_logs_seconds=2592000 # 30天
# 設置單個二進制日志文件的最大大小（例如100MB）
max_binlog_size=100M
# 最大連接數
max_connections=1000
# 允許的最大包大小
max_allowed_packet=512M
# 打開表緩存的大小
table_open_cache=256
# 排序緩沖區大小
sort_buffer_size=256K
# 讀取緩沖區大小
read_buffer_size=256K
# 讀取臨時表緩沖區大小
read_rnd_buffer_size=512K
# 連接緩沖區大小
join_buffer_size=256K
# 指定 MySQL 內部臨時表的最大大小
tmp_table_size=1G
max_heap_table_size=1G
# InnoDB 使用緩沖池來緩存數據和索引
innodb_buffer_pool_size=40G
# InnoDB 使用日志文件來記錄所有的修改操作
innodb_log_file_size=4G
# 每個表的數據和索引存儲在單獨的文件中
innodb_file_per_table=ON
# 索引緩存大小（適用于 MyISAM）
key_buffer_size=64M
# 默認存儲引擎
default-storage-engine=InnoDB
# 設置服務器ID（適用于主從復制）
server-id=1
# SQL 模式
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# 開啟慢查詢日志，慢查詢閾值設為 2秒
slow_query_log=1
long_query_time=5
# 慢查詢日志文件
slow_query_log_file=/var/log/mysql/slow.log
# 連接超時設置（秒）
wait_timeout=600
interactive_timeout=600
# 線程緩存大小
thread_cache_size=200
# 查詢緩存大小（MySQL 8.0 已移除查詢緩存，無需配置）
# 表定義緩存
table_definition_cache=400
# 表打開緩存
table_open_cache_instances=4
# InnoDB 相關優化
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_thread_concurrency=0
innodb_adaptive_hash_index=ON
innodb_buffer_pool_instances=8
# 日志刷新頻率
sync_binlog=1
innodb_doublewrite=ON
# 錯誤日志緩沖
log_error_verbosity=3
# 臨時表相關設置
tmp_table_size=1G
max_heap_table_size=1G
# 字符集和排序規則
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 安全相關設置
skip_symbolic_links
local-infile=0
# 資源限制
open_files_limit=65535
# 其他優化
innodb_strict_mode=ON
innodb_old_blocks_time=1000
浙公網安備 33010602011771號