Mysql MHA部署中什么是主從復制

這篇文章給大家介紹Mysql MHA部署中什么是主從復制，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

Mysql MHA部署-主從復制

架構說明:

參考：http://www.zhaibibei.cn/mysql/mha/

搭建主從復制

1 Mysql安裝

2 rac1(187 主),rac3(223 從)配置異步復制

3 rac1(187 主),rac2(188 從)配置半同步復制

我們根據上面的拓撲建立主從關系，192.168.2.223采用半同步，192.168.2.223采用異步

采用基于GTID的復制，否則建議關閉GTID功能

這里就不多做介紹了，具體見上一個專題

注意在做主從同步的時候建議清理下從庫相關信息

reset master ;reset slave all;

時間同步：

[root@rac1 ~]# ntpdate cn.pool.ntp.org

14 Mar 15:37:01 ntpdate[31863]: step time server 203.107.6.88 offset 2.987670 sec

[root@rac1 ~]# date

Sat Mar 14 15:37:17 CST 2020

1 Mysql安裝

---rac1(主187),rac2(從188),rac3(從223)分別安裝Mysql 

1.1 配置Limits

[root@rac1 package]# cat>>/etc/security/limits.conf<<EOF

mysql   soft   nofile    1024

mysql   hard   nofile    65536

mysql   soft   nproc     4095

mysql   hard   nproc     16384

mysql   soft   stack     10240

mysql   hard   stack     32768

EOF

1.2 系統內核參數

shmmax和shmall的設置 shmmax指的是單個內存段的最大值，單位為bytes shmall指的是能使用的最大內存大小，

單位為pages, pages大小可通過 getconf PAGE_SIZE 命令查詢，一般操作系統page大小為4096 bytes 如操作系統內存為8G，給80%給Oracle使用，

則 kernel.shmmax=(8 * 0.8 * 1024 * 1024 * 1024 )=6871947673 kernel.shmall=kernel.shmmax/4096=1677721

如默認值比較大 請保持默認值

cat>>/etc/sysctl.conf<<EOF

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

fs.aio-max-nr = 1048576

# vm.min_free_kbytes = 524288

vm.swappiness= 5

# vm.nr_hugepages =1024

# vm.hugetlb_shm_group = 2000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.ip_local_port_range = 1024 65500

1.3 目錄規劃

目錄名稱 參數名稱 路徑地址

安裝目錄 basedir /usr/local/mysql

數據文件目錄 datadir /data/mysql/data

臨時文件目錄 tmpdir /data/mysql/tmp

socket文件目錄 socket /data/mysql/data/mysql.sock

bin日志文件目錄 log_bin /datalog/mysql/binlog

relay日志文件目錄 relay_log /datalog/mysql/relaylog

1.4  MySQL5.7下載

下載地址：

dev.mysql.com/downloads/mysql

這里統一使用5.7.28的版本

[root@rac1 mysql]# pwd

/package/mysql

[root@rac1 mysql]# ll -rth

total 692M

-rw-r--r-- 1 root root 692M Mar 14 11:49 mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

[root@rac1 mysql]# 

1.5 新建用戶及目錄

[root@rac1 ~]# /usr/sbin/groupadd -g 105 mysql

[root@rac1 ~]# /usr/sbin/useradd -u 105 -g mysql mysql

[root@rac1 ~]# echo "123456" |passwd mysql --stdin

[root@rac1 ~]# mkdir -p /data/mysql/software

[root@rac1 ~]# mkdir -p /usr/local/mysql

[root@rac1 ~]# mkdir -p /data/mysql/data

[root@rac1 ~]# mkdir -p /datalog/mysql/binlog

[root@rac1 ~]# mkdir -p /datalog/mysql/relaylog

[root@rac1 ~]# chown -R mysql:mysql /usr/local/mysql

[root@rac1 ~]# mkdir -p /data/mysql/tmp

[root@rac1 ~]# chown -R mysql:mysql /data/mysql

[root@rac1 ~]# chown -R mysql:mysql /datalog/mysql/

1.6 配置環境變量

[root@rac1 ~]# su - mysql

Attempting to create directory /home/mysql/perl5

[mysql@rac1 ~]$ vim .bash_profile 

...

export MYSQL_HOME=/usr/local/mysql

export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH

export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH

[mysql@rac1 ~]$ source .bash_profile 

1.7 建立配置文件

[root@rac1 ~]# cp /etc/my.cnf /etc/my.cnf.bak

[root@rac1 ~]# vim /etc/my.cnf

[mysql]

user =

password =

[mysqld]

#-----------------MySQL Basic Setting-----------------#

server-id = 1723161113

port = 3306

user = mysql

pid-file = mysql.pid

character_set_server = utf8mb4

default_storage_engine = InnoDB

skip_name_resolve = 1

lower_case_table_names = 1

explicit_defaults_for_timestamp = 1

open_files_limit = 65535

max_connections = 1000

max_connect_errors = 100000

basedir = /usr/local/mysql

datadir = /data/mysql/data

tmpdir = /data/mysql/tmp

socket = /data/mysql/data/mysql.sock

query_cache_type = 0

query_cache_size = 0

join_buffer_size = 64M

tmp_table_size = 64M

max_allowed_packet = 32M

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 32M

log_error_verbosity=2

log_timestamps=SYSTEM

#-----------------MySQL Log Setting-----------------#

log_error = mysql-error.log

log_bin = /datalog/mysql/binlog/mysql-bin.log

slow_query_log_file = mysql-slow.log

relay_log = /datalog/mysql/relaylog/mysql-relay.log

log_slave_updates = 1

sync_binlog = 1

relay_log_recovery = 1

binlog_format = row

expire_logs_days = 14

slow_query_log = 1

long_query_time = 2

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes = 10

log_slow_admin_statements = 1

log_slow_slave_statements = 1

min_examined_row_limit = 1000

#-----------------MySQL Replication Setting-----------------#

slave_skip_errors = ddl_exist_errors

master_info_repository = TABLE

relay_log_info_repository = TABLE

#gtid_mode = on

#enforce_gtid_consistency = 1

binlog_rows_query_log_events = 1

#-----------------MySQL InnoDB Setting-----------------#

innodb_page_size = 16384

innodb_buffer_pool_size = 25600M

innodb_data_file_path = ibdata1:1G:autoextend

innodb_buffer_pool_instances = 8

innodb_file_per_table = 1

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 5

innodb_io_capacity = 800

innodb_io_capacity_max = 2000

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

innodb_flush_neighbors = 1

innodb_log_file_size = 2G

innodb_log_buffer_size = 16777216

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

#-----------------MySQL semi Replication Setting-----------------#

#plugin_dir = /usr/local/mysql/lib/plugin

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#loose_rpl_semi_sync_master_enabled = 1

#loose_rpl_semi_sync_slave_enabled = 1

#loose_rpl_semi_sync_master_timeout = 5000

修改my.cnf權限

[root@rac1 ~]# chown mysql.mysql /etc/my.cnf

1.8 依賴包檢查

[root@rac1 ~]# rpm -qa libaio*

libaio-0.3.109-13.el7.x86_64

libaio-devel-0.3.109-13.el7.x86_64

[root@rac1 ~]# rpm -qa lvm2-*

lvm2-libs-2.02.177-4.el7.x86_64

lvm2-python-libs-2.02.177-4.el7.x86_64

1.9 解壓文件

[root@rac1 ~]# chown mysql.mysql /package/mysql -R

[mysql@rac1 ~]$ cd /package/mysql/

[mysql@rac1 mysql]$ ls

mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

[mysql@rac1 mysql]$ tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql/ --strip-components=1

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisam_ftdump

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamchk

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamlog

......

1.10 配置服務文件

這里將mysql.server文件拷貝值init.d目錄使其可以當作服務啟停

[root@rac1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

這里修改下面幾處

[root@rac1 ~]# vim /etc/init.d/mysqld 

basedir=/usr/local/mysql

datadir=/data/mysql/data

lockdir='/data/mysql/data'

mysqld_pid_file_path=/data/mysql/data/mysql.pid

1.11 初始化數據庫

[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

[root@rac1 ~]# tail -f /data/mysql/data/mysql-error.log 

......

2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM

1.12 啟動和關閉數據庫

[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

[1] 18642

2020-03-14T05:24:05.258268Z mysqld_safe Logging to '/data/mysql/data/mysql-error.log'.

2020-03-14T05:24:05.320993Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

[mysql@rac1 mysql]$ netstat -lntp|grep mysqld

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp6       0      0 :::3306                 :::*                    LISTEN      19567/mysqld  

后續可以使用如下命令啟停數據庫

[mysql@rac1 mysql]$ service mysqld stop

Shutting down MySQL....2020-03-14T05:25:16.603331Z mysqld_safe mysqld from pid file /data/mysql/data/mysql.pid ended

[  OK  ]

[1]+  Done                    /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql

[mysql@rac1 mysql]$ service mysqld start

Starting MySQL.........[  OK  ]

設置MySQL自啟動

chkconfig mysqld on

1.13 連接數據庫

默認密碼在error文件中有

[mysql@rac1 mysql]$ cat /data/mysql/data/mysql-error.log |grep password

2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM

使用如下命令連接

[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

Enter password: 7DO4gs27;YOM

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.28-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user 'root'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

2 rac1(187 主),rac3(223 從)配置異步復制

這節我們的內容為MySQL的復制，MySQL復制有兩種形式

基于二進制日志文件位置

基于GTID

這節為第一種基于二進制日志文件位置

2.1 開啟二進制日志功能

無論是使用哪種方式我們都需要啟用二進制日志功能

如果未開啟則需要在my.cnf文件中加入如下參數，需要重啟數據庫生效

---主庫187 rac1

[mysqld] 

server-id = 1

binlog_format = row

log_bin = /datalog/mysql/binlog/mysql-bin.log

expire_logs_days = 14

log-slave-updates=ON

---從庫223 rac3  

[mysqld] 

server-id = 3

binlog_format = row

log_bin = /datalog/mysql/binlog/mysql-bin.log

expire_logs_days = 14

log-slave-updates=ON

read_only=1

---重啟mysql

[mysql@rac1 ~]$ service mysqld stop

Shutting down MySQL.....[  OK  ]

[mysql@rac1 ~]$ service mysqld start

Starting MySQL..............[  OK  ]

2.2 查看UUID是否一致

需要注意的是如果從庫是由主庫克隆而來，這時的uuid是一樣的，這樣也會報錯

該文件位于daadir的auto.cnf文件中

vim /data/mysql/data/auto.cnf

如果一樣可刪除該文件后重新啟動數據庫即可，這時會生成一個新的文件

2.3 建立復制賬號

接下來我們建立一個獨立的用于復制的賬號

主庫和從庫

[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl'; 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';

mysql> CREATE USER 'repl'@'192.168.2.223' IDENTIFIED BY 'rpl'; 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.223';

mysql> flush privileges;

mysql> select host,user from user;

+---------------+---------------+

| host          | user          |

+---------------+---------------+

| 192.168.2.187 | repl          |

| 192.168.2.223 | repl          |

| localhost     | mysql.session |

| localhost     | mysql.sys     |

| localhost     | root          |

+---------------+---------------+

5 rows in set (0.00 sec)

這里我們限制該賬號只能從同步的兩臺服務器上連接

2.4 備份主庫

---創建測試數據(模擬生產數據)

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> use jumptest

Database changed

mysql> source /package/mysql/jumpserver_bak_2020_03_13_22_00_01.sql

---備份 

[mysql@rac1 ~]$ mysqldump -S /data/mysql/data/mysql.sock -uroot -p --databases jumptest  --single-transaction --master-data=2 --set-gtid-purged=off   --triggers --events --routines> /tmp/dumpmaster.sql

Enter password: 

[mysql@rac1 ~]$ ll -rth /tmp/dumpmaster.sql 

-rw-r--r-- 1 mysql mysql 19M Mar 14 17:04 /tmp/dumpmaster.sql

2.5 文件傳輸

接下來將主庫的dump文件傳到備份，之后更改備庫的文件權限

主庫

[mysql@rac1 ~]$ scp /tmp/dumpmaster.sql root@192.168.2.223:/tmp

從庫

[root@rac3 ~]# chown mysql:mysql /tmp/dumpmaster.sql

2.6 備庫導入數據

接下來我們將備份的數據導入到備份

[mysql@rac3 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

mysql> use jumptest

Database changed

mysql> source /tmp/dumpmaster.sql

2.7 開始同步

接下來我們開啟同步

首先我們查看dumpmaster.sql文件中master的信息

mysql> show master status;

+------------------+----------+--------------+------------------+--------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |

+------------------+----------+--------------+------------------+--------------------------------------------+

| mysql-bin.000004 | 34751569 |              |                  | c1227971-65b3-11ea-bf67-080027839e5c:1-297 |

+------------------+----------+--------------+------------------+--------------------------------------------+

1 row in set (0.00 sec)

---從庫開啟同步命令2.223

mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34751569;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

2.8 從庫查看同步狀態

使用如下命令查看同步是否正常

主要關注如下幾點

Slave_IO_Running需要為YES

Slave_SQL_Running需要為YES

Seconds_Behind_Master需要為0

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.2.187

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 34751569

               Relay_Log_File: mysql-relay.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

......

        Seconds_Behind_Master: 0

......

2.9 重啟和重置復制

使用如下命令關閉重啟

mysql>stop slave;

mysql>start slave;

我們可以獨立的重啟IO進程或者SQL進程

mysql>stop slave sql_thread;

mysql>stop slave io_thread;

mysql>start slave io_thread;

mysql>start slave sql_thread;

使用如下命令重置復制

mysql>reset slave all;

3 rac1(187 主),rac3(188 從)配置半同步復制

3.1 半同步介紹

異步的復制，主庫將二進制日志發送到從庫后并不需要確認從庫是否接受并應用，這時就可能會造成數據丟失。

MySQL 從5.5版本后推出了半同步的功能，相當于Oracle DG的最大保護模式，它要求從庫在接收并應用日志后，主庫才提交完成，保證了數據。

開啟半同步需要如下要求：

MySQL 5.5及以上版本

變量have_dynamic_loading為YES

3.2 188從庫初始化主庫數據

188：

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

mysql> use jumptest

Database changed

mysql> source /tmp/dumpmaster.sql

3.3 創建復制賬號

[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl'; 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';

mysql> CREATE USER 'repl'@'192.168.2.188' IDENTIFIED BY 'rpl'; 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.188';

mysql> flush privileges;

mysql> use mysql 

---從庫188

mysql> select host,user from user;

+---------------+---------------+

| host          | user          |

+---------------+---------------+

| 192.168.2.187 | repl          |

| 192.168.2.188 | repl          |

| localhost     | mysql.session |

| localhost     | mysql.sys     |

| localhost     | root          |

+---------------+---------------+

5 rows in set (0.00 sec)

---主庫187

mysql> select host,user from user;

+---------------+---------------+

| host          | user          |

+---------------+---------------+

| 192.168.2.187 | repl          |

| 192.168.2.188 | repl          |

| 192.168.2.223 | repl          |

| localhost     | mysql.session |

| localhost     | mysql.sys     |

| localhost     | root          |

+---------------+---------------+

6 rows in set (0.00 sec)

3.4 加載半同步插件

因需執行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作，所以用戶需有SUPER權限

主庫和從庫

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

這里安裝半同步的master和slave插件

考慮到后面主從可能需要切換，這里在主從庫上都安裝

確認是否加載成功

mysql> show plugins;

......

| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |

| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |

+----------------------------+----------+--------------------+--------------------+---------+

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

+----------------------+---------------+

| PLUGIN_NAME          | PLUGIN_STATUS |

+----------------------+---------------+

| rpl_semi_sync_master | ACTIVE        |

| rpl_semi_sync_slave  | ACTIVE        |

+----------------------+---------------+

2 rows in set (0.00 sec)

3.5 啟用半同步

首先我們啟用半同步插件

主庫 187

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

從庫 188

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34752186;

mysql> start slave;

mysql> show slave status \G;

之后需要重啟IO線程才能使半同步生效，也可直接重啟復制

從庫

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

如果從庫超過一定時間不能和主庫進行通信，則會自動降為異步模式

該時間由rpl_semi_sync_master_timeout參數控制，單位為毫秒

3.6 查看半同步狀態

我們使用如下命令查看半同步是否正常工作

主庫的master和從庫的slave都需要為ON

主庫

mysql> show status like 'Rpl_semi_sync_master_status';

+-----------------------------+-------+

| Variable_name               | Value |

+-----------------------------+-------+

| Rpl_semi_sync_master_status | ON    |

+-----------------------------+-------+

1 row in set (0.02 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | OFF   |

+----------------------------+-------+

1 row in set (0.00 sec)

從庫

mysql> show status like 'Rpl_semi_sync_slave_status';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON    |

+----------------------------+-------+

1 row in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_master_status';

+-----------------------------+-------+

| Variable_name               | Value |

+-----------------------------+-------+

| Rpl_semi_sync_master_status | OFF   |

+-----------------------------+-------+

1 row in set (0.01 sec)

3.7 寫入配置文件

接下來我們將命令寫在配置文件中以使重啟后自動啟動

考慮到后面主從可能需要切換，這里在主從庫上半同步的master和slave都設置為啟動

主庫187和從庫188

[mysql@rac1 ~]$ vim /etc/my.cnf

#-----------------MySQL semi Replication Setting-----------------#

plugin_dir = /usr/local/mysql/lib/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_timeout = 5000

--從庫 188,223

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

主187、從庫188 重啟生效：

[mysql@rac1 ~]$ service mysqld restart

Shutting down MySQL....[  OK  ]

Starting MySQL.........[  OK  ]

測試數據同步

主庫187 

mysql> create table cjc01(id int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into cjc01 values(1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into cjc01 values(2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cjc01 values(3);

Query OK, 1 row affected (0.01 sec)

mysql> update cjc01 set id=100 where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from cjc01 where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from cjc01;

+------+

| id   |

+------+

|  100 |

|    3 |

+------+

2 rows in set (0.00 sec)

從庫188

mysql> select * from cjc01;

+------+

| id   |

+------+

|  100 |

|    3 |

+------+

2 rows in set (0.00 sec)

從庫223 

mysql> select * from cjc01;

+------+

| id   |

+------+

|  100 |

|    3 |

+------+

2 rows in set (0.00 sec)

關于Mysql MHA部署中什么是主從復制就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。