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

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

公司主營(yíng)業(yè)務(wù)：網(wǎng)站制作、網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)推出濮陽(yáng)縣免費(fèi)做網(wǎng)站回饋大家。

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

架構(gòu)說(shuō)明:

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

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

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

注意在做主從同步的時(shí)候建議清理下從庫(kù)相關(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大小可通過(guò) 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)值比較大請(qǐng)保持默認(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ù)庫(kù)

[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ù)庫(kù)

[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ù)庫(kù)

[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ù)庫(kù)

默認(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

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 從)配置異步復(fù)制

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

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

基于GTID

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

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

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

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

---主庫(kù)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

---從庫(kù)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是否一致

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

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

vim /data/mysql/data/auto.cnf

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

2.3 建立復(fù)制賬號(hào)

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

主庫(kù)和從庫(kù)

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

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

2.4 備份主庫(kù)

---創(chuàng)建測(cè)試數(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 文件傳輸

接下來(lái)將主庫(kù)的dump文件傳到備份，之后更改備庫(kù)的文件權(quán)限

主庫(kù)

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

從庫(kù)

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

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

接下來(lái)我們將備份的數(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)

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 開(kāi)始同步

接下來(lái)我們開(kāi)啟同步

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

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

---從庫(kù)開(kāi)啟同步命令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 從庫(kù)查看同步狀態(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ù)制，主庫(kù)將二進(jìn)制日志發(fā)送到從庫(kù)后并不需要確認(rèn)從庫(kù)是否接受并應(yīng)用，這時(shí)就可能會(huì)造成數(shù)據(jù)丟失。

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

開(kāi)啟半同步需要如下要求：

MySQL 5.5及以上版本

變量have_dynamic_loading為YES

3.2 188從庫(kù)初始化主庫(kù)數(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ù)制賬號(hào)

[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

---從庫(kù)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)

---主庫(kù)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)限

主庫(kù)和從庫(kù)

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

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

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

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

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

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 啟用半同步

首先我們啟用半同步插件

主庫(kù) 187

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

從庫(kù) 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ù)制

從庫(kù)

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

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

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

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

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

主庫(kù)的master和從庫(kù)的slave都需要為ON

主庫(kù)

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)

從庫(kù)

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

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

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

主庫(kù)187和從庫(kù)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

--從庫(kù) 188,223

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

主187、從庫(kù)188 重啟生效：

[mysql@rac1 ~]$ service mysqld restart

Shutting down MySQL....[ OK ]

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

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

主庫(kù)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)

從庫(kù)188

mysql> select * from cjc01;

+------+

| id |

+------+

| 100 |

| 3 |

+------+

2 rows in set (0.00 sec)

從庫(kù)223

mysql> select * from cjc01;

+------+

| id |

+------+

| 100 |

| 3 |

+------+

2 rows in set (0.00 sec)

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

當(dāng)前名稱：MysqlMHA部署中什么是主從復(fù)制
文章位置：http://muchs.cn/article14/ppppge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、微信公眾號(hào)、網(wǎng)站策劃、小程序開(kāi)發(fā)、建站公司、外貿(mào)建站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容