Mysql雙主雙從(實(shí)戰(zhàn)干貨,好文章!?。?/h1>
1.機(jī)器說明


因?yàn)橹?,從2,都是通過同一個(gè)鏡像生成的系統(tǒng),所以mysql的 server-uuid 是一樣的,在配置前,需要做以下操作
重命名 /var/lib/mysql/auto.cnf 文件
mv auto.cnf auto.cnf.bak
重啟mysql 重新生成 auto.cnf 文件




因?yàn)橹芭渲眠^ ali-shuidi01,ali-shuidi02的主從關(guān)系,所以先將兩者之前的主從關(guān)系取消,在master1上執(zhí)行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
2. master1 配置文件修改
vim /etc/my.cnf
#主服務(wù)器唯一ID
server-id=1
#啟用二進(jìn)制日志
log-bin=mysql-bin
# 設(shè)置不要復(fù)制的數(shù)據(jù)庫(可設(shè)置多個(gè))
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#設(shè)置需要復(fù)制的數(shù)據(jù)庫
binlog-do-db=copydb2
#設(shè)置logbin格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時(shí)候,有寫入操作也要更新二進(jìn)制日志文件
log-slave-updates
#表示自增長字段每次遞增的量,指自增字段的起始值,其默認(rèn)值是1,取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個(gè)數(shù)開始,指字段一次遞增多少,他的取值范圍是1 .. 65535
auto-increment-offset=1
3. master2 配置文件修改
vim /etc/my.cnf
#主服務(wù)器唯一ID
server-id=3
#啟用二進(jìn)制日志
log-bin=mysql-bin
# 設(shè)置不要復(fù)制的數(shù)據(jù)庫(可設(shè)置多個(gè))
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#設(shè)置需要復(fù)制的數(shù)據(jù)庫
binlog-do-db=copydb2
#設(shè)置logbin格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時(shí)候,有寫入操作也要更新二進(jìn)制日志文件
log-slave-updates
#表示自增長字段每次遞增的量,指自增字段的起始值,其默認(rèn)值是1,取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個(gè)數(shù)開始,指字段一次遞增多少,他的取值范圍是1 .. 65535
auto-increment-offset=2
4. slave1 配置文件修改
vim /etc/my.cnf
#從服務(wù)器唯一ID
server-id=2
#啟用中繼日志
relay-log=mysql-relay
5. slave2配置文件修改
vim /etc/my.cnf
#從服務(wù)器唯一ID
server-id=4 #啟用中繼日志
relay-log=mysql-relay
6. 雙主機(jī)、雙從機(jī)重啟 mysql 服務(wù), 主機(jī)從機(jī)都關(guān)閉防火墻
[root@shuidi01 etc]# systemctl restart mysqld
[root@shuidi01 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:38:23 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9985 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 9946 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 9990 (mysqld)
CGroup: /system.slice/mysqld.service
└─9990 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:38:22 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 20:38:23 shuidi01 systemd[1]: Started MySQL Server.
[root@shuidi02 etc]# systemctl restart mysqld
[root@shuidi02 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:39:10 CST; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 868 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 845 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 873 (mysqld)
CGroup: /system.slice/mysqld.service
└─873 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:39:09 shuidi02 systemd[1]: Starting MySQL Server...
3月 30 20:39:10 shuidi02 systemd[1]: Started MySQL Server.
[root@shuidi03 etc]# systemctl restart mysqld
[root@shuidi03 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:39:42 CST; 5s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8507 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8485 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8512 (mysqld)
CGroup: /system.slice/mysqld.service
└─8512 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:39:41 shuidi03 systemd[1]: Starting MySQL Server...
3月 30 20:39:42 shuidi03 systemd[1]: Started MySQL Server.
[root@shuidi04 etc]# systemctl restart mysqld
[root@shuidi04 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:40:13 CST; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8861 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8839 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8866 (mysqld)
CGroup: /system.slice/mysqld.service
└─8866 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:40:12 shuidi04 systemd[1]: Starting MySQL Server...
3月 30 20:40:13 shuidi04 systemd[1]: Started MySQL Server.
7. 在兩臺主機(jī)上建立賬戶并授權(quán) slave
master1:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Zan_666888';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查詢Master1的狀態(tài)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 435 | copydb2 | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
master2:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Zan_666888';
Query OK, 0 rows affected, 1 warning (0.01 sec)
#查詢Master2的狀態(tài)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 | 435 | copydb2 | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
#分別記錄下File和Position的值
#執(zhí)行完此步驟后不要再操作主服務(wù)器MYSQL,防止主服務(wù)器狀態(tài)值變化
8. 在從機(jī)上配置需要復(fù)制的主機(jī)
Slava1 復(fù)制 Master1,Slava2 復(fù)制 Master2
#復(fù)制主機(jī)的命令
CHANGE MASTER TO MASTER_HOST='主機(jī)的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具體數(shù)字',MASTER_LOG_POS=具體值;
slave1:
CHANGE MASTER TO MASTER_HOST='172.16.94.131',
MASTER_USER='slave',
MASTER_PASSWORD='Zan_666888',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=435;
slave2:
CHANGE MASTER TO MASTER_HOST='172.16.94.133',
MASTER_USER='slave',
MASTER_PASSWORD='Zan_666888',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;
#啟動兩臺從服務(wù)器復(fù)制功能
slave1:
mysql> start slave;
#查看從服務(wù)器狀態(tài)
show slave status\G;

slave2:
mysql> start slave; #查看從服務(wù)器狀態(tài) show slave status\G;

#下面兩個(gè)參數(shù)都是Yes,則說明主從配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
9. 兩個(gè)主機(jī)互相復(fù)制
Master2 復(fù)制 Master1,Master1 復(fù)制 Master2
master2:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.94.131',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='Zan_666888',
-> MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=435;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

master1:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.94.133',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='Zan_666888',
-> MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

#下面兩個(gè)參數(shù)都是Yes,則說明主從配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
10. Master1 主機(jī)新建庫、新建表、insert 記錄,Master2 和從機(jī)復(fù)制
mysql> CREATE DATABASE copydb2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| copydb |
| copydb2 |
| mysql |
| nacos_config |
| performance_schema |
| seata |
| seata_account |
| seata_order |
| seata_storage |
| sys |
+--------------------+
11 rows in set (0.00 sec)
mysql> use copydb2;
Database changed
mysql> CREATE TABLE mytbl(id INT,NAME VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mytbl VALUES(1,'zhang3');
Query OK, 1 row affected (0.01 sec)
mysql> select * from mytbl;
+------+--------+
| id | NAME |
+------+--------+
| 1 | zhang3 |
+------+--------+
1 row in set (0.00 sec)

四個(gè)節(jié)點(diǎn)數(shù)據(jù)均實(shí)現(xiàn)了同步
11.如何停止從服務(wù)復(fù)制功能
stop slave;
如何重新配置主從
stop slave; reset master;
12.修改 Mycat 的配置文件
/usr/local/mycat/conf/schema.xml
修改<dataHost>的balance屬性,通過此屬性配置讀寫分離的類型
負(fù)載均衡類型,目前的取值有4 種:
(1)balance="0", 不開啟讀寫分離機(jī)制,所有讀操作都發(fā)送到當(dāng)前可用的 writeHost 上。
(2)balance="1",全部的 readHost 與 stand by writeHost 參與 select 語句的負(fù)載均衡,簡單地說,當(dāng)雙主雙從
模式(M1->S1,M2->S2,并且 M1 與 M2 互為主備),正常情況下,M2,S1,S2 都參與 select 語句的負(fù)載均衡。
(3)balance="2",所有讀操作都隨機(jī)的在 writeHost、readhost 上分發(fā)。
(4)balance="3",所有讀請求隨機(jī)的分發(fā)到 readhost 執(zhí)行,writerHost 不負(fù)擔(dān)讀壓力
為了雙主雙從讀寫分離balance設(shè)置為1
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="copydb2" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="172.16.94.131:3306" user="root"
password="Zan_123456">
<readHost host="hostS1" url="172.16.94.132:3306" user="root" password="Zan_123456" />
</writeHost>
<writeHost host="hostM2" url="172.16.94.133:3306" user="root"
password="Zan_123456">
<readHost host="hostS2" url="172.16.94.134:3306" user="root" password="Zan_123456" />
</writeHost>
</dataHost>
</mycat:schema>
#balance="1": 全部的readHost與stand by writeHost參與select語句的負(fù)載均衡。
#writeType="0": 所有寫操作發(fā)送到配置的第一個(gè)writeHost,第一個(gè)掛了切到還生存的第二個(gè)
#writeType="1",所有寫操作都隨機(jī)的發(fā)送到配置的 writeHost,1.5 以后廢棄不推薦
#writeHost,重新啟動后以切換后的為準(zhǔn),切換記錄在配置文件中:dnindex.properties 。
#switchType="1": 1 默認(rèn)值,自動切換。
# -1 表示不自動切換
# 2 基于 MySQL 主從同步的狀態(tài)決定是否切換。
13.啟動 Mycat,驗(yàn)證讀寫分離
[root@shuidi01 bin]# ./mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@shuidi01 bin]# ./mycat start
Starting Mycat-server...
#登錄mycat
mysql -umycat -pzan123456 -P 8066 -h 127.0.0.1
#插入數(shù)據(jù)
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_copydb2 |
+-------------------+
| mytbl |
+-------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mytbl VALUES(2,@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi03 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi02 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
+------+----------+
2 rows in set (0.00 sec)
#在Mycat里查詢mytbl表,可以看到查詢語句在Master2(shuidi03)、Slava1(shuidi02)、Slava2(shuidi04)主從三個(gè)主機(jī)間切換
14.抗風(fēng)險(xiǎn)能力
#停止數(shù)據(jù)庫Master1
[root@shuidi01 logs]# systemctl stop mysqld
[root@shuidi01 logs]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 一 2020-03-30 21:31:28 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9985 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 9946 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 9990 (code=exited, status=0/SUCCESS)
3月 30 20:38:22 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 20:38:23 shuidi01 systemd[1]: Started MySQL Server.
3月 30 21:31:16 shuidi01 systemd[1]: Stopping MySQL Server...
3月 30 21:31:28 shuidi01 systemd[1]: Stopped MySQL Server.
#在Mycat里插入數(shù)據(jù)依然成功,Master2自動切換為寫主機(jī)
mysql> INSERT INTO mytbl VALUES(3,@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
| 3 | shuidi04 |
+------+----------+
3 rows in set (0.00 sec)
#啟動數(shù)據(jù)庫Master1
[root@shuidi01 logs]# systemctl start mysqld
[root@shuidi01 logs]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 21:33:39 CST; 2s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3527 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 3473 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3531 (mysqld)
CGroup: /system.slice/mysqld.service
└─3531 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 21:33:38 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 21:33:39 shuidi01 systemd[1]: Started MySQL Server.
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi01 |
| 3 | shuidi01 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi02 |
| 3 | shuidi02 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
| 3 | shuidi04 |
+------+----------+
3 rows in set (0.00 sec)
#在Mycat里查詢mytbl表,可以看到查詢語句在Master1(shuidi01)、Slava1(shuidi02)、Slava2(shuidi04)
主從三個(gè)主機(jī)間切換
Master1、Master2 互做備機(jī),負(fù)責(zé)寫的主機(jī)宕機(jī),備機(jī)切換負(fù)責(zé)寫操作,保證數(shù)據(jù)庫讀寫分離高
可用性。
瀏覽
31
1.機(jī)器說明


因?yàn)橹?,從2,都是通過同一個(gè)鏡像生成的系統(tǒng),所以mysql的 server-uuid 是一樣的,在配置前,需要做以下操作
重命名 /var/lib/mysql/auto.cnf 文件
mv auto.cnf auto.cnf.bak
重啟mysql 重新生成 auto.cnf 文件




因?yàn)橹芭渲眠^ ali-shuidi01,ali-shuidi02的主從關(guān)系,所以先將兩者之前的主從關(guān)系取消,在master1上執(zhí)行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
2. master1 配置文件修改
vim /etc/my.cnf
#主服務(wù)器唯一ID
server-id=1
#啟用二進(jìn)制日志
log-bin=mysql-bin
# 設(shè)置不要復(fù)制的數(shù)據(jù)庫(可設(shè)置多個(gè))
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#設(shè)置需要復(fù)制的數(shù)據(jù)庫
binlog-do-db=copydb2
#設(shè)置logbin格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時(shí)候,有寫入操作也要更新二進(jìn)制日志文件
log-slave-updates
#表示自增長字段每次遞增的量,指自增字段的起始值,其默認(rèn)值是1,取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個(gè)數(shù)開始,指字段一次遞增多少,他的取值范圍是1 .. 65535
auto-increment-offset=1
3. master2 配置文件修改
vim /etc/my.cnf
#主服務(wù)器唯一ID
server-id=3
#啟用二進(jìn)制日志
log-bin=mysql-bin
# 設(shè)置不要復(fù)制的數(shù)據(jù)庫(可設(shè)置多個(gè))
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#設(shè)置需要復(fù)制的數(shù)據(jù)庫
binlog-do-db=copydb2
#設(shè)置logbin格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時(shí)候,有寫入操作也要更新二進(jìn)制日志文件
log-slave-updates
#表示自增長字段每次遞增的量,指自增字段的起始值,其默認(rèn)值是1,取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個(gè)數(shù)開始,指字段一次遞增多少,他的取值范圍是1 .. 65535
auto-increment-offset=2
4. slave1 配置文件修改
vim /etc/my.cnf
#從服務(wù)器唯一ID
server-id=2
#啟用中繼日志
relay-log=mysql-relay
5. slave2配置文件修改
vim /etc/my.cnf
#從服務(wù)器唯一ID
server-id=4 #啟用中繼日志
relay-log=mysql-relay
6. 雙主機(jī)、雙從機(jī)重啟 mysql 服務(wù), 主機(jī)從機(jī)都關(guān)閉防火墻
[root@shuidi01 etc]# systemctl restart mysqld
[root@shuidi01 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:38:23 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9985 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 9946 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 9990 (mysqld)
CGroup: /system.slice/mysqld.service
└─9990 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:38:22 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 20:38:23 shuidi01 systemd[1]: Started MySQL Server.
[root@shuidi02 etc]# systemctl restart mysqld
[root@shuidi02 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:39:10 CST; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 868 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 845 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 873 (mysqld)
CGroup: /system.slice/mysqld.service
└─873 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:39:09 shuidi02 systemd[1]: Starting MySQL Server...
3月 30 20:39:10 shuidi02 systemd[1]: Started MySQL Server.
[root@shuidi03 etc]# systemctl restart mysqld
[root@shuidi03 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:39:42 CST; 5s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8507 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8485 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8512 (mysqld)
CGroup: /system.slice/mysqld.service
└─8512 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:39:41 shuidi03 systemd[1]: Starting MySQL Server...
3月 30 20:39:42 shuidi03 systemd[1]: Started MySQL Server.
[root@shuidi04 etc]# systemctl restart mysqld
[root@shuidi04 etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 20:40:13 CST; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8861 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8839 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8866 (mysqld)
CGroup: /system.slice/mysqld.service
└─8866 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 20:40:12 shuidi04 systemd[1]: Starting MySQL Server...
3月 30 20:40:13 shuidi04 systemd[1]: Started MySQL Server.
7. 在兩臺主機(jī)上建立賬戶并授權(quán) slave
master1:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Zan_666888';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查詢Master1的狀態(tài)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 435 | copydb2 | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
master2:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Zan_666888';
Query OK, 0 rows affected, 1 warning (0.01 sec)
#查詢Master2的狀態(tài)
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 | 435 | copydb2 | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
#分別記錄下File和Position的值
#執(zhí)行完此步驟后不要再操作主服務(wù)器MYSQL,防止主服務(wù)器狀態(tài)值變化
8. 在從機(jī)上配置需要復(fù)制的主機(jī)
Slava1 復(fù)制 Master1,Slava2 復(fù)制 Master2
#復(fù)制主機(jī)的命令
CHANGE MASTER TO MASTER_HOST='主機(jī)的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具體數(shù)字',MASTER_LOG_POS=具體值;
slave1:
CHANGE MASTER TO MASTER_HOST='172.16.94.131',
MASTER_USER='slave',
MASTER_PASSWORD='Zan_666888',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=435;
slave2:
CHANGE MASTER TO MASTER_HOST='172.16.94.133',
MASTER_USER='slave',
MASTER_PASSWORD='Zan_666888',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;
#啟動兩臺從服務(wù)器復(fù)制功能
slave1:
mysql> start slave;
#查看從服務(wù)器狀態(tài)
show slave status\G;

slave2:
mysql> start slave; #查看從服務(wù)器狀態(tài) show slave status\G;

#下面兩個(gè)參數(shù)都是Yes,則說明主從配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
9. 兩個(gè)主機(jī)互相復(fù)制
Master2 復(fù)制 Master1,Master1 復(fù)制 Master2
master2:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.94.131',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='Zan_666888',
-> MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=435;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

master1:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.94.133',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='Zan_666888',
-> MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

#下面兩個(gè)參數(shù)都是Yes,則說明主從配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
10. Master1 主機(jī)新建庫、新建表、insert 記錄,Master2 和從機(jī)復(fù)制
mysql> CREATE DATABASE copydb2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| copydb |
| copydb2 |
| mysql |
| nacos_config |
| performance_schema |
| seata |
| seata_account |
| seata_order |
| seata_storage |
| sys |
+--------------------+
11 rows in set (0.00 sec)
mysql> use copydb2;
Database changed
mysql> CREATE TABLE mytbl(id INT,NAME VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mytbl VALUES(1,'zhang3');
Query OK, 1 row affected (0.01 sec)
mysql> select * from mytbl;
+------+--------+
| id | NAME |
+------+--------+
| 1 | zhang3 |
+------+--------+
1 row in set (0.00 sec)

四個(gè)節(jié)點(diǎn)數(shù)據(jù)均實(shí)現(xiàn)了同步
11.如何停止從服務(wù)復(fù)制功能
stop slave;
如何重新配置主從
stop slave; reset master;
12.修改 Mycat 的配置文件
/usr/local/mycat/conf/schema.xml
修改<dataHost>的balance屬性,通過此屬性配置讀寫分離的類型
負(fù)載均衡類型,目前的取值有4 種:
(1)balance="0", 不開啟讀寫分離機(jī)制,所有讀操作都發(fā)送到當(dāng)前可用的 writeHost 上。
(2)balance="1",全部的 readHost 與 stand by writeHost 參與 select 語句的負(fù)載均衡,簡單地說,當(dāng)雙主雙從
模式(M1->S1,M2->S2,并且 M1 與 M2 互為主備),正常情況下,M2,S1,S2 都參與 select 語句的負(fù)載均衡。
(3)balance="2",所有讀操作都隨機(jī)的在 writeHost、readhost 上分發(fā)。
(4)balance="3",所有讀請求隨機(jī)的分發(fā)到 readhost 執(zhí)行,writerHost 不負(fù)擔(dān)讀壓力
為了雙主雙從讀寫分離balance設(shè)置為1
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="copydb2" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="172.16.94.131:3306" user="root"
password="Zan_123456">
<readHost host="hostS1" url="172.16.94.132:3306" user="root" password="Zan_123456" />
</writeHost>
<writeHost host="hostM2" url="172.16.94.133:3306" user="root"
password="Zan_123456">
<readHost host="hostS2" url="172.16.94.134:3306" user="root" password="Zan_123456" />
</writeHost>
</dataHost>
</mycat:schema>
#balance="1": 全部的readHost與stand by writeHost參與select語句的負(fù)載均衡。
#writeType="0": 所有寫操作發(fā)送到配置的第一個(gè)writeHost,第一個(gè)掛了切到還生存的第二個(gè)
#writeType="1",所有寫操作都隨機(jī)的發(fā)送到配置的 writeHost,1.5 以后廢棄不推薦
#writeHost,重新啟動后以切換后的為準(zhǔn),切換記錄在配置文件中:dnindex.properties 。
#switchType="1": 1 默認(rèn)值,自動切換。
# -1 表示不自動切換
# 2 基于 MySQL 主從同步的狀態(tài)決定是否切換。
13.啟動 Mycat,驗(yàn)證讀寫分離
[root@shuidi01 bin]# ./mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@shuidi01 bin]# ./mycat start
Starting Mycat-server...
#登錄mycat
mysql -umycat -pzan123456 -P 8066 -h 127.0.0.1
#插入數(shù)據(jù)
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_copydb2 |
+-------------------+
| mytbl |
+-------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mytbl VALUES(2,@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi03 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi02 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
+------+----------+
2 rows in set (0.00 sec)
#在Mycat里查詢mytbl表,可以看到查詢語句在Master2(shuidi03)、Slava1(shuidi02)、Slava2(shuidi04)主從三個(gè)主機(jī)間切換
14.抗風(fēng)險(xiǎn)能力
#停止數(shù)據(jù)庫Master1
[root@shuidi01 logs]# systemctl stop mysqld
[root@shuidi01 logs]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 一 2020-03-30 21:31:28 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9985 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 9946 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 9990 (code=exited, status=0/SUCCESS)
3月 30 20:38:22 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 20:38:23 shuidi01 systemd[1]: Started MySQL Server.
3月 30 21:31:16 shuidi01 systemd[1]: Stopping MySQL Server...
3月 30 21:31:28 shuidi01 systemd[1]: Stopped MySQL Server.
#在Mycat里插入數(shù)據(jù)依然成功,Master2自動切換為寫主機(jī)
mysql> INSERT INTO mytbl VALUES(3,@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
| 3 | shuidi04 |
+------+----------+
3 rows in set (0.00 sec)
#啟動數(shù)據(jù)庫Master1
[root@shuidi01 logs]# systemctl start mysqld
[root@shuidi01 logs]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2020-03-30 21:33:39 CST; 2s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3527 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 3473 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3531 (mysqld)
CGroup: /system.slice/mysqld.service
└─3531 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3月 30 21:33:38 shuidi01 systemd[1]: Starting MySQL Server...
3月 30 21:33:39 shuidi01 systemd[1]: Started MySQL Server.
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi01 |
| 3 | shuidi01 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi02 |
| 3 | shuidi02 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from mytbl;
+------+----------+
| id | NAME |
+------+----------+
| 1 | zhang3 |
| 2 | shuidi04 |
| 3 | shuidi04 |
+------+----------+
3 rows in set (0.00 sec)
#在Mycat里查詢mytbl表,可以看到查詢語句在Master1(shuidi01)、Slava1(shuidi02)、Slava2(shuidi04)
主從三個(gè)主機(jī)間切換
Master1、Master2 互做備機(jī),負(fù)責(zé)寫的主機(jī)宕機(jī),備機(jī)切換負(fù)責(zé)寫操作,保證數(shù)據(jù)庫讀寫分離高
可用性。
