Mysql MHA部署中什么是主從復(fù)制

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

Mysql MHA部署-主從復(fù)制

架構(gòu)說明:

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

搭建主從復(fù)制

1 Mysql安裝

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

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

我們根據(jù)上面的拓?fù)浣⒅鲝年P(guān)系，192.168.2.223采用半同步，192.168.2.223采用異步

采用基于GTID的復(fù)制，否則建議關(guān)閉GTID功能

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

注意在做主從同步的時(shí)候建議清理下從庫相關(guān)信息

reset master ;reset slave all;

時(shí)間同步：

[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 系統(tǒng)內(nèi)核參數(shù)

shmmax和shmall的設(shè)置 shmmax指的是單個(gè)內(nèi)存段的最大值，單位為bytes shmall指的是能使用的最大內(nèi)存大小，

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

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

如默認(rèn)值比較大 請保持默認(rèn)值

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 目錄規(guī)劃

目錄名稱 參數(shù)名稱 路徑地址

安裝目錄 basedir /usr/local/mysql

數(shù)據(jù)文件目錄 datadir /data/mysql/data

臨時(shí)文件目錄 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

這里統(tǒng)一使用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 配置環(huán)境變量

[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權(quán)限

[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 配置服務(wù)文件

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

[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 初始化數(shù)據(jù)庫

[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 啟動(dòng)和關(guān)閉數(shù)據(jù)庫

[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  

后續(xù)可以使用如下命令啟停數(shù)據(jù)庫

[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  ]

設(shè)置MySQL自啟動(dòng)

chkconfig mysqld on

1.13 連接數(shù)據(jù)庫

默認(rèn)密碼在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 從)配置異步復(fù)制

這節(jié)我們的內(nèi)容為MySQL的復(fù)制，MySQL復(fù)制有兩種形式

基于二進(jìn)制日志文件位置

基于GTID

這節(jié)為第一種基于二進(jìn)制日志文件位置

2.1 開啟二進(jìn)制日志功能

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

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

---主庫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是否一致

需要注意的是如果從庫是由主庫克隆而來，這時(shí)的uuid是一樣的，這樣也會(huì)報(bào)錯(cuò)

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

vim /data/mysql/data/auto.cnf

如果一樣可刪除該文件后重新啟動(dòng)數(shù)據(jù)庫即可，這時(shí)會(huì)生成一個(gè)新的文件

2.3 建立復(fù)制賬號

接下來我們建立一個(gè)獨(dú)立的用于復(fù)制的賬號

主庫和從庫

[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)

這里我們限制該賬號只能從同步的兩臺(tái)服務(wù)器上連接

2.4 備份主庫

---創(chuàng)建測試數(shù)據(jù)(模擬生產(chǎn)數(shù)據(jù))

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文件傳到備份，之后更改備庫的文件權(quán)限

主庫

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

從庫

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

2.6 備庫導(dǎo)入數(shù)據(jù)

接下來我們將備份的數(shù)據(jù)導(dǎo)入到備份

[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 從庫查看同步狀態(tài)

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

主要關(guān)注如下幾點(diǎn)

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 重啟和重置復(fù)制

使用如下命令關(guān)閉重啟

mysql>stop slave;

mysql>start slave;

我們可以獨(dú)立的重啟IO進(jìn)程或者SQL進(jìn)程

mysql>stop slave sql_thread;

mysql>stop slave io_thread;

mysql>start slave io_thread;

mysql>start slave sql_thread;

使用如下命令重置復(fù)制

mysql>reset slave all;

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

3.1 半同步介紹

異步的復(fù)制，主庫將二進(jìn)制日志發(fā)送到從庫后并不需要確認(rèn)從庫是否接受并應(yīng)用，這時(shí)就可能會(huì)造成數(shù)據(jù)丟失。

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

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

MySQL 5.5及以上版本

變量have_dynamic_loading為YES

3.2 188從庫初始化主庫數(shù)據(jù)

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 創(chuàng)建復(fù)制賬號

[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 加載半同步插件

因需執(zhí)行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作，所以用戶需有SUPER權(quán)限

主庫和從庫

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

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

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

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

確認(rèn)是否加載成功

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線程才能使半同步生效，也可直接重啟復(fù)制

從庫

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

如果從庫超過一定時(shí)間不能和主庫進(jìn)行通信，則會(huì)自動(dòng)降為異步模式

該時(shí)間由rpl_semi_sync_master_timeout參數(shù)控制，單位為毫秒

3.6 查看半同步狀態(tài)

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

主庫的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 寫入配置文件

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

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

主庫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  ]

測試數(shù)據(jù)同步

主庫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)

關(guān)于Mysql MHA部署中什么是主從復(fù)制就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。