理解MySQL主從復(fù)制原理
主服務(wù)器開啟binlog日志，從庫生成log dump線程，將binlog日志傳給從庫I/O線程，從庫生成倆個線程，一個是I/O線程，一個是SQL線程，I/O線程去請主庫的binlog日志，并將binlog日志中的文件寫入relay log中，sql線程會讀取relay log 中的內(nèi)容，并解析成具體的操作，來實(shí)現(xiàn)主從一致，達(dá)到最終數(shù)據(jù)一致的目的。
2.完成MySQL主從復(fù)制（一主兩從）
環(huán)境準(zhǔn)備：
主機(jī)名IP地址端口號node01192.168.11.1103306node02192.168.11.1113306node03192.168.11.1123306
數(shù)據(jù)庫準(zhǔn)備：
create database company;
use company
CREATE TABLE `emp` ?(
?`empno` int(4) NOT NULL,
?`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
?`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
?`mgr` int(4) NULL DEFAULT NULL,
?`hiredate` date NOT NULL,
?`sai` int(255) NOT NULL,
?`comm` int(255) NULL DEFAULT NULL,
?`deptno` int(2) NOT NULL,
?PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `emp` VALUES (1001, '甘寧', '文員', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛綺絲', '銷售員', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '銷售員', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '劉備', '經(jīng)理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '謝遜', '銷售員', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '關(guān)羽', '經(jīng)理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '張飛', '經(jīng)理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '諸葛亮', '分析師', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事長', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韋一笑', '銷售員', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文員', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文員', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '龐統(tǒng)', '分析師', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黃蓋', '文員', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '張三', '保潔員', 1001, '2013-05-01', 80000, 50000, 50);
方式一：基于三臺服務(wù)器實(shí)現(xiàn)主從復(fù)制
主庫配置：
1、在mysqld標(biāo)簽下添加server_id并開啟bin_log日志
[root@node01 ~]# cat /etc/my.cnf[mysqld]
log_bin=mysql_bin
server_id=1
2、重啟數(shù)據(jù)庫服務(wù)
[root@node01 ~]# systemctl restart mysqld.service
3、授權(quán)同步賬號和密碼
mysql> grant replication slave on *.* to 'rep'@'192.168.11.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
4、查看授權(quán)信息
mysql> show grants for 'rep'@'192.168.11.%';
+--------------------------------------------------------+
| Grants for rep@192.168.11.% ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.11.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
5、對表操作
# 鎖表設(shè)置為只讀# 為后邊備份準(zhǔn)備，注意生產(chǎn)環(huán)境要提前申請停機(jī)時間，停服mysql> flush tables with read lock;# 超過時間不操作會自動解鎖，查看超時時間mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name ? ? ? ? ? ? ? | Value ? ?|
+-----------------------------+----------+
| connect_timeout ? ? ? ? ? ? | 10 ? ? ? |
| delayed_insert_timeout ? ? ?| 300 ? ? ?|
| have_statement_timeout ? ? ?| YES ? ? ?|
| innodb_flush_log_at_timeout | 1 ? ? ? ?|
| innodb_lock_wait_timeout ? ?| 50 ? ? ? |
| innodb_rollback_on_timeout ?| OFF ? ? ?|
| interactive_timeout ? ? ? ? | 28800 ? ?|
| lock_wait_timeout ? ? ? ? ? | 31536000 |
| net_read_timeout ? ? ? ? ? ?| 30 ? ? ? |
| net_write_timeout ? ? ? ? ? | 60 ? ? ? |
| rpl_stop_slave_timeout ? ? ?| 31536000 |
| slave_net_timeout ? ? ? ? ? | 60 ? ? ? |
| wait_timeout ? ? ? ? ? ? ? ?| 28800 ? ?|
+-----------------------------+----------+
13 rows in set (0.01 sec)# 查看主庫狀態(tài)mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | ? ?11824 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6、備份數(shù)據(jù)庫數(shù)據(jù)
# 創(chuàng)建備份目錄[root@node01 ~]# mkdir /server/backup -p[root@node01 ~]# mysqldump -uroot -p -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gzEnter password:
7、解鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
8、主庫備份數(shù)據(jù)傳送到從庫
# 在從庫上常見備份目錄[root@node02 ~]# mkdir /server/backup -p# scp傳送[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz ?192.168.11.111:/server/backup/[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz ?192.168.11.112:/server/backup/
從庫配置：
1、關(guān)閉bin_log參數(shù)，設(shè)置server-id
[root@node02 ~]# cat /etc/my.cnf[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=2
2、重啟數(shù)據(jù)庫服務(wù)
[root@node02 ~]# systemctl restart mysqld.service
3、還原從主庫傳輸過來的數(shù)據(jù)文件
[root@node02 ~]# cd /server/backup/[root@node02 backup]# gzip -d mysql_bak.2023-03-25.sql.gz[root@node02 backup]# mysql -uroot -p < mysql_bak.2023-03-25.sqlEnter password:
4、檢查數(shù)據(jù)完整性
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| company ? ? ? ? ? ?|
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
+--------------------+
5 rows in set (0.00 sec)
mysql> use company;
mysql> select * from company;# 數(shù)據(jù)完整，恢復(fù)完成
5、配置主從同步
# 查看主庫的binlog和pos位置點(diǎn)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | ? ?11824 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 從庫上配置mysql> change master to
? ?-> master_host='192.168.11.110',
? ?-> master_user='rep',
? ?-> master_password='123456',
? ?-> master_log_file='mysql_bin.000001',
? ?-> master_log_pos=11824;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
6、啟動從庫同步并檢查狀態(tài)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ?Master_Host: 192.168.11.110
? ? ? ? ? ? ? ? ?Master_User: rep
? ? ? ? ? ? ? ? ?Master_Port: 3306
? ? ? ? ? ? ? ?Connect_Retry: 60
? ? ? ? ? ? ?Master_Log_File: mysql_bin.000001
? ? ? ? ?Read_Master_Log_Pos: 11824
? ? ? ? ? ? ? Relay_Log_File: node02-relay-bin.000002
? ? ? ? ? ? ? ?Relay_Log_Pos: 320
? ? ? ?Relay_Master_Log_File: mysql_bin.000001
? ? ? ? ? ? Slave_IO_Running: Yes
? ? ? ? ? ?Slave_SQL_Running: Yes# 看目前最后倆行是否為YES，倆個線程都為YES才OK
測試：
1、主庫創(chuàng)建一個數(shù)據(jù)庫
mysql> create database test_master;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| company ? ? ? ? ? ?|
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
| test_master ? ? ? ?|
+--------------------+
6 rows in set (0.00 sec)
2、從庫檢查
[root@node02 backup]# mysql -uroot ?-p -e 'show databases;'Enter password:
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| company ? ? ? ? ? ?|
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
| test_master ? ? ? ?|
+--------------------+
第二臺從庫一樣的配置，除了server_id不同
