部署搭建MySQL雙向主從復(fù)制流程

下面一起來(lái)了解下部署搭建MySQL雙向主從復(fù)制流程，相信大家看完肯定會(huì)受益匪淺，文字在精不在多，希望部署搭建MySQL雙向主從復(fù)制流程這篇短內(nèi)容是你想要的。

作為Master云服務(wù)器apenglinux-001.cn的配置
/ -- 建庫(kù)，表，備份庫(kù)，將備份傳給另一臺(tái)機(jī)器-- /
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e "create database db1;use db1;create table t1(id int unsigned not null primary key auto_increment,name varchar(100));insert into t1(name)values('zhangsan'),('lisi'),('wangwu');select * from t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+

[root@apenglinux-001 ~]# mysqldump -uroot -p123456 -B db1 > db1_all.sql

[root@apenglinux-001 ~]# mysqldump -uroot -p123456 -B db1 > db1_all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@apenglinux-001 ~]# scp db1_all.sql 192.168.1.20:/root
The authenticity of host '192.168.1.20 (192.168.1.20)' can't be established.
ECDSA key fingerprint is SHA256:ENfUT65MBnG5u82/aeA84Wl7klhZZMS/MI1+36eGu8k.
ECDSA key fingerprint is MD5:bb:7a:dc:8b:d2:5b:99:54:9a:8d:f2:17:81:0a:5e:72.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.20' (ECDSA) to the list of known hosts.
root@192.168.1.20's password: 

db1_all.sql                                                      100% 2024   856.5KB/s   00:00

/ -- 配置my.cnf-- /
[root@apenglinux-001 ~]# vim /etc/my.cnf
log-bin=apenglinux-001.cn
server-id=100
binlog-do-db=db1
binlog-ignore-db=mysql

/ -- 開(kāi)啟MySQL服務(wù)-- /
[root@apenglinux-001 ~]# systemctl restart mysqld

/-- 作為master時(shí)，授權(quán)，將表鎖定--/
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e "grant replication slave on . to slave@192.168.1.20 identified by '123';flush tables with read lock;"

作為slave云服務(wù)器apenglinux-002的配置

/-- 還原從master上傳過(guò)來(lái)的數(shù)據(jù)庫(kù)-- /
[root@apenglinux-002 ~]# mysql -uroot -p123456 < db1_all.sql
/ -- 配置my.cnf-- /
validate_password=off
log-bin=apenglinux-002.cn
server-id=90
binlog-do-db=db1
binlog-ignore-db=mysql
/-- 重啟mysql--/
[root@apenglinux-002 ~]# systemctl restart mysqld
/ -- 停止slave,指定主云服務(wù)器的ip,user,password,log_file,log_pos,開(kāi)啟slave-- /
[root@apenglinux-002 ~]# mysql -uroot -p123456 -e "stop slave;change master to master_host='192.168.1.10',master_port=3306,master_user='slave',master_password='123',master_log_file='apenglinux-001.000001',master_log_pos=449;start slave;"
/ -- 去主云服務(wù)器上開(kāi)啟解表操作 -- /
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'unlock tables;'
/ -- 查看slave的狀態(tài) -- /
[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'show slave status\G'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_SQL_Errno: 0
作為master云服務(wù)器apenglinux-002.cn的配置
[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'grant replication slave on . to slave@192.168.1.10 identified by "123";flush tables with read lock;show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| apenglinux-002.000001 |      449 | db1          | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
作為slave云服務(wù)器apenglinux-001.cn的配置
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'stop slave;change master to master_host="192.168.1.20",master_port=3306,master_user="slave",master_password="123",master_log_file="apenglinux-002.000001",master_log_pos=449;start slave;'
去apenglinux-002.cn 上解鎖
[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'unlock tables;'
/ -- 查看slave的狀態(tài) --/
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'show slave status\G'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_SQL_Errno: 0
此時(shí) apenglinux-001與apenglinux-002兩臺(tái)機(jī)器互為主從了。
測(cè)試：
/ -- 在apenglinux-001.cn上增加一條記錄，在apenglinux-002 上查看-- /
[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'insert into db1.t1(name)values("aa");select * from db1.t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | aa       |
+----+----------+

[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'select * from db1.t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | aa       |
+----+----------+

/ -- 在apenglinux-002.cn上刪除兩條記錄，在apenglinux-001上查看-- /
[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'delete from db1.t1 limit 2;select * from db1.t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+
| id | name   |
+----+--------+
|  3 | wangwu |
|  4 | aa     |
+----+--------+

[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'select * from db1.t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+
| id | name   |
+----+--------+
|  3 | wangwu |
|  4 | aa     |
+----+--------+

看完部署搭建MySQL雙向主從復(fù)制流程這篇文章后，很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容，如需獲取更多的行業(yè)信息，可以關(guān)注我們的行業(yè)資訊欄目。