Mysql MHA部署-02主從複製

Mysql MHA部署-02主從複製

一 Mysql MHA部署-01介紹

二 Mysql MHA部署-02主從複製

三 Mysql MHA部署-03MHA軟體安裝

四 Mysql MHA部署-04MHA配置

五 Mysql MHA部署-05故障轉移

六 Mysql MHA部署-06手動切換

七 Mysql MHA部署-07常見問題

架構說明:

參考： 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

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)

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)

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;

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

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

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

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;

......

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

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)

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2680376/，如需轉載，請註明出處，否則將追究法律責任。

Mysql MHA部署-02主從複製

相關文章