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