Mysql8主從復(fù)制解讀(兼容低高版本)
Mysql主從復(fù)制
理論知識
主從復(fù)制必要前提
- 主從復(fù)制必要的條件:
- 主庫開啟binlog日志(設(shè)置log-bin參數(shù))
- 主從server-id不同
- 從庫服務(wù)器能連同主庫
實(shí)現(xiàn)原理
原理:實(shí)現(xiàn)整個主從復(fù)制,需要由slave服務(wù)器上的IO進(jìn)程和Sql進(jìn)程共同完成;要實(shí)現(xiàn)主從復(fù)制,首先必須打開Master端的binary log(bin-log)功能,因?yàn)檎麄€MySQL 復(fù)制過程實(shí)際上就是Slave從Master端獲取相應(yīng)的二進(jìn)制日志,然后再在自己本地(slave端)按照執(zhí)行日志中所記錄的順序,全部操作一遍。
- ---在主庫上把有數(shù)據(jù)更改的(DDL DML DCL)sql語句都記錄到二進(jìn)制日志(Binary Log)中。
- ---備庫的I/O線程將主庫上的日志復(fù)制到自己的中繼日志(Relay Log)中。
- ---備庫的SQL線程讀取中繼日志中的事件,將其重放到備庫數(shù)據(jù)庫之上。
binlog的三種類型
binlog有三種模式:statement模式、mixed模式和row模式。
- statement模式記錄的是SQL語句
- row模式記錄的是每一行數(shù)據(jù)的變化
- mixed模式是自動組合 STATEMENT 和 ROW 模式,按照最優(yōu)方式來記錄日志。
- Binlog日志的開啟和關(guān)閉可以通過設(shè)置MySQL的配置文件實(shí)現(xiàn)。
配置步驟
- 兩臺機(jī)器時間一致
- 對主庫已有的數(shù)據(jù)庫不會進(jìn)行自動同步。
- 主從同步之前,主庫上已有數(shù)據(jù)庫備份,需要在從庫上手動導(dǎo)入同步
操作系統(tǒng) | IP | mysql版本 | 主從類型 |
---|---|---|---|
Anolis OS8.9 | 192.168.200.81 | mysql8.4 | 主 |
Anolis OS8.9 | 192.168.200.83 | mysql8.4 | 從 |
需要注意:mysql 版本不同,有些命令是不同的
show master status; 不能用了
查看主節(jié)點(diǎn)binlog的命令
# mysql 8.4版本前使用這條命令查看 show master status; # MySQL 8.4版本后使用這條命令查看 SHOW BINARY LOG STATUS;
change master to不能用了
從節(jié)點(diǎn)配置主節(jié)點(diǎn)信息的命令
# MSQL 8.23前 CHANGE MASTER TO MASTER_HOST='192.168.200.81', MASTER_USER='nomax', MASTER_PASSWORD='nomax', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=158; # MSQL 8.23后 CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158;
start slave不能用了
# 開啟同步 start replica ; #8.0.22之后 start slave ; #8.0.22之前 #停止同步 stop replica ; #8.0.22之后 stop slave ; #8.0.22之前 #清空之前的主從復(fù)制配置信息 reset replica ; #8.0.22之后 reset slave ; #8.0.22之前
show slave status不能用了
查看從節(jié)點(diǎn)狀態(tài)的
# 查看狀態(tài),\G表示行轉(zhuǎn)列,便于查看 show replica status\G ; #8.0.22之后 show slave status\G ; #8.0.22之前
一、主節(jié)點(diǎn)配置(在192.168.200.81操作)
主從節(jié)點(diǎn)配置的差異:由于后續(xù)需要演示主從切換,所以無論是主從節(jié)點(diǎn),都需要提前開啟binlog和relaylog。故而這里主從配置基本一致,具體配置選項(xiàng)差異只有:server_id、read-only選項(xiàng)
編輯/etc/my.cnf
#==================== 主從同步配置========================= #節(jié)點(diǎn)id編號,各個mysql的server_id需要唯一 server_id=1 #指定binlog和binglog index的文件名 log_bin=/data/log/mysql/mysql-bin log_bin_index=/data/log/mysql/mysql-bin.index #[可選]0(默認(rèn))表示讀寫(主機(jī)),1表示只讀(從機(jī)) read-only=0 #[可選]啟用中繼日志 relay-log=/data/log/mysql/mysql-relay #[可選] 單個binlog最大的文件大小,默認(rèn)是1G max_binlog_size=500M #[可選]設(shè)置binlog格式.STATEMENT,row,mixed binlog_format=row #[可選]設(shè)置日志文件保留的時長,單位是秒(默認(rèn)不刪除文件) #binlog_expire_logs_seconds=6000 #[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫 #binlog-ignore-db=test #[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫,默認(rèn)全部記錄。比如:binlog-do-db=atguigu_master_slave #binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字
- relay-log中繼日志可以看主從復(fù)制之間的細(xì)節(jié)
- log_bin_index,用來索引,主從復(fù)制時提供日志文件順序
- binlog_format=row
- row哪里更改都會記錄下來,不會出現(xiàn)無法正確復(fù)制的問題。很詳細(xì),缺點(diǎn)是日志量大
- read-only=0 主的設(shè)置0讀寫,從的設(shè)置1只讀
修改配置后重啟數(shù)據(jù)庫:
systemctl restart mysql or service mysql restart
二、從節(jié)點(diǎn)配置(在192.168.200.83操作)
##節(jié)點(diǎn)id編號,各個mysql的server_id需要唯一 server_id=2 #指定binlog和binglog index的文件名 log_bin=/data/mysql/binlog log_bin_index=/data/mysql/binlog.index #[可選]啟用中繼日志 relay-log=/data/mysql/mysql-relay #[可選] 單個binlog最大的文件大小,默認(rèn)是1G max_binlog_size=500M #[可選]設(shè)置binlog格式.STATEMENT,row,mixed binlog_format=row #[可選]0(默認(rèn))表示讀寫(主機(jī)),1表示只讀(從機(jī)) read-only=1 # #[可選]設(shè)置日志文件保留的時長,單位是秒(默認(rèn)不刪除文件) # #binlog_expire_logs_seconds=6000 # #[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫 # #binlog-ignore-db=test # #[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫,默認(rèn)全部記錄。比如:binlog-do-db=atguigu_master_slave # #binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字
主從節(jié)點(diǎn)配置的差異:開啟binlog和relaylog,具體配置選項(xiàng)差異只有:server_id、read-only選項(xiàng)
在從節(jié)點(diǎn)登錄主節(jié)點(diǎn)服務(wù)器獲取服務(wù)器公鑰
防止后續(xù)出現(xiàn)從節(jié)點(diǎn)連接主節(jié)點(diǎn)服務(wù)器報無公鑰的錯誤
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
- 修改配置后重啟數(shù)據(jù)庫:
systemctl restart mysql or service mysql restart
三、創(chuàng)建用于主從同步的用戶(主從都要創(chuàng)建)
root
用戶也可以但是不安全
MySQL8
新版本
#創(chuàng)建nomax用戶 CREATE USER'nomax'@'%' IDENTIFIED BY'nomax'; #給nomax用戶授予數(shù)據(jù)同步的權(quán)限 GRANT replication slave on *.* to 'nomax'@'%'; #刷新權(quán)限 flush privileges;
mysql5
老版本
GRANT REPLICATION SLAVE ON *.* TO 'nomax'@'%' identified by 'nomax'; flush privileges;
參數(shù)解析
新版本需要先創(chuàng)建用戶
老版本權(quán)限用戶可一起生成
GRANT replication slave
:授予改用戶從庫中進(jìn)行復(fù)制的權(quán)限ON *.*
:第一個 * 代表數(shù)據(jù)庫名,第二個 * 代表表名,*.*
意味著所有數(shù)據(jù)庫中的所有表。TO 'nomax'
:用戶名字'@'%'
:表示用戶可以在任何主機(jī)連接數(shù)據(jù)庫identified by 'nomax'
:為用戶設(shè)置nomax的密碼
- 查詢確認(rèn)創(chuàng)建的用戶
SELECT User FROM mysql.user;
四、開啟主從復(fù)制
- 查看主節(jié)點(diǎn)binlog執(zhí)行位置(主節(jié)點(diǎn)192.168.200.81來執(zhí)行以下命令)
# mysql8.4的 SHOW BINARY LOG STATUS; # mysql8及其以前的 SHOW BINARY STATUS;
mysql> SHOW BINARY LOG STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000013 | 158 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
- 從節(jié)點(diǎn)開啟主節(jié)點(diǎn)同步操作(從節(jié)點(diǎn)192.168.200.83來執(zhí)行以下命令):
#從節(jié)點(diǎn)配置連接主的信息 CHANGE REPLICATION SOURCE TO #主節(jié)點(diǎn)的IP SOURCE_HOST='192.168.200.81', #主節(jié)點(diǎn)的端口號 SOURCE_PORT=3306, #主節(jié)點(diǎn)的用戶 SOURCE_USER='nomax', #主節(jié)點(diǎn)的密碼 SOURCE_PASSWORD='nomax', #通過 SHOW BINARY LOG STATUS;查看 SOURCE_LOG_FILE='mysql-bin.000013', SOURCE_LOG_POS=158; #開啟從節(jié)點(diǎn)備份 start replica; #查看從節(jié)點(diǎn)的狀態(tài) show replica status \G;
方便復(fù)制版本 CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_PORT=3306, SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='mysql-bin.000013', SOURCE_LOG_POS=158; start replica; show replica status \G;
CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘192.168.200.81’, SOURCE_LOG_FILE=‘mysql-bin.000013’, SOURCE_LOG_POS=158, SOURCE_PORT=3306, SOURCE_USER=‘nomax’, SOURCE_PASSWORD=‘nomax’;
確保下面四項(xiàng)參數(shù)正確
Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Error:Last_SQL_Error:
報錯:
Last_IO_Error: Error connecting to source 'nomax@192.168.200.81:3306'. This was attempt 10/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
- 原因:根據(jù)上一張密碼傳輸方式的第3條,該插件發(fā)現(xiàn)連接未加密,因此需要使用RSA加密來傳輸密碼。但是,服務(wù)器不會將公用密鑰發(fā)送給客戶端,并且客戶端未提供公用密鑰,因此它無法加密密碼并且連接失敗
解決方法:
- 使用復(fù)制用戶請求服務(wù)器公鑰
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
在這種情況下,服務(wù)器將RSA公鑰發(fā)送給客戶端,后者使用它來加密密碼并將結(jié)果返回給服務(wù)器。插件使用服務(wù)器端的RSA私鑰解密密碼,并根據(jù)密碼是否正確來接受或拒絕連接。
重新在從庫配置change masrer to并且start slave,復(fù)制可以正常啟動:
#停止主從復(fù)制 #清空之前的主從復(fù)制配置信息 stop replica; reset replica; #從新配置主從復(fù)制 CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_PORT=3306, SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='mysql-bin.000013', SOURCE_LOG_POS=158; start replica; show replica status \G;
五、主從同步驗(yàn)證
- 也可以用navicat去試試,倆IP的數(shù)據(jù)庫都連上進(jìn)行創(chuàng)建庫表啥的操作
- 主節(jié)點(diǎn)上進(jìn)行建庫、建表、插入表數(shù)據(jù)操作
#創(chuàng)建數(shù)據(jù)庫 mysql> create database test_db; Query OK, 1 row affected (0.03 sec) #查看數(shù)據(jù)庫 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test_db | +--------------------+ 5 rows in set (0.06 sec) #切換數(shù)據(jù)庫 mysql> use test_db; Database changed #創(chuàng)建表 mysql> CREATE TABLE `t_test` ( -> `id` int(11) NOT NULL, -> `age` int(11) DEFAULT NULL, -> `score` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected, 3 warnings (0.10 sec) #插入表數(shù)據(jù) mysql> INSERT INTO `t_test` VALUES (1, 2, 1); Query OK, 1 row affected (0.01 sec) #插入表數(shù)據(jù) mysql> INSERT INTO `t_test` VALUES (222, 22, 19); Query OK, 1 row affected (0.01 sec) #查看表 mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | t_test | +-------------------+ 1 row in set (0.00 sec) #查看表數(shù)據(jù) mysql> select * from t_test; +-----+------+-------+ | id | age | score | +-----+------+-------+ | 1 | 2 | 1 | | 222 | 22 | 19 | +-----+------+-------+ 2 rows in set (0.00 sec)
檢查從節(jié)點(diǎn)192.168.200.83是否也都同步成功:
如下所示,從節(jié)點(diǎn)也都自動完成了主節(jié)點(diǎn)上所進(jìn)行的相關(guān)操作~
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test_db | +--------------------+ 5 rows in set (0.00 sec) #切換數(shù)據(jù)庫 mysql> use test_db; Database changed #查看表 mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | t_test | +-------------------+ 1 row in set (0.00 sec) #查詢表數(shù)據(jù) mysql> select * from t_test; +-----+------+-------+ | id | age | score | +-----+------+-------+ | 1 | 2 | 1 | | 222 | 22 | 19 | +-----+------+-------+ 2 rows in set (0.00 sec)
至此,主從同步部署完成
六、故障切換
mysql主從,主節(jié)點(diǎn)宕機(jī),如何進(jìn)行切換
- 在從節(jié)點(diǎn)執(zhí)行(版本以8.4為例,之前的命令不同參考本文理論知識模塊命令注意)
mysql> stop replica; mysql> reset replica;
- 查看是否是只讀模式
mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec)
只讀模式需要修改my.cnf文件,注釋read-only=1并重啟mysql服務(wù)。
或者不重啟使用命令臨時關(guān)閉只讀,但下次重啟后失效:set global read_only=off;
- 查看
mysql> show replica status\G
- 在程序中將原來主庫IP地址改為現(xiàn)在的從庫IP地址,測試應(yīng)用連接是否正常
stop replica;mysql> reset replica;
查看是否是只讀模式
mysql> show variables like ‘read_only'; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | read_only | ON | ±--------------±------+ 1 row in set (0.00 sec)
**只讀模式需要修改my.cnf文件,注釋read-only=1并重啟mysql服務(wù)。**
**或者不重啟使用命令臨時關(guān)閉只讀,但下次重啟后失效:`set global read_only=off;`**
查看
mysql> show replica status\G
**在程序中將原來主庫IP地址改為現(xiàn)在的從庫IP地址,測試應(yīng)用連接是否正常**
總結(jié)
以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
使用pt-kill根據(jù)一定的規(guī)則來kill連接的方法
pt-kill 是一個優(yōu)秀的kill MySQL連接的一個工具,是percona toolkit的一部分,在因?yàn)榭臻e連接較多導(dǎo)致超過最大連接數(shù)、某個有問題的sql導(dǎo)致mysql負(fù)載很高時,都需要將一些連接kill掉,這個工具主要就是這個用途2016-04-04MySQL中的SHOW FULL PROCESSLIST命令實(shí)現(xiàn)
SHOW FULL PROCESSLIST命令是MySQL中一個非常有用的工具,可以幫助我們理解和監(jiān)控MySQL服務(wù)器的狀態(tài),本文主要介紹了MySQL中的SHOW FULL PROCESSLIST命令,感興趣的可以了解一下2023-11-11