Centos7 Mysql 集群MHA--之一(MYSQL5.7雙主配置)

  1. 統(tǒng)一設(shè)置hosts
[root@elk-node2 ~]#vi /etc/hosts
---------------------------------------------------------------------------------------------------------
192.168.40.171  MYSQLM1  MYSQLM1.COM
192.168.40.172  MYSQLS1  MYSQLS1.COM
192.168.40.173  MYSQLS2  MYSQLS2.COM
192.168.40.174  MYSQLMHA MYSQLMHA.COM
---------------------------------------------------------------------------------------------------------
[root@elk-node2 ~]# vim /etc/sysconfig/network
[root@elk-node2 ~]# vim /etc/hostname
  1. 配置SSH無(wú)密碼登錄認(rèn)證
（1）、在MYSQLMHA（192.168.40.174）上
ssh-keygen -t rsa
#一路回車(chē)，直到完成
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.171
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.172
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.173

（2）、在MYSQLM1（192.168.40.171）上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.172
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.173

（3）、在MYSQLS1（192.168.40.172）上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.171
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.173

（4）、在MYSQLS2（192.168.40.173）上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.171
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.172

--test
ssh MYSQLM1
  1. 時(shí)間同步
查看時(shí)間是否同步：
shell> ssh MYSQLM1 date; ssh MYSQLS1 date;ssh MYSQLS2 date;ssh MYSQLMHA date;

發(fā)現(xiàn)時(shí)間不同步，進(jìn)行時(shí)間同步，依據(jù)當(dāng)前時(shí)間為準(zhǔn)
shell>ssh MYSQLM1 "date -s '2018-04-07 00:20:00'"; ssh MYSQLS1 "date -s '2018-04-07 00:20:00'"; ssh MYSQLS2 "date -s '2018-04-07 00:20:00'";
  1. 修改配置（4臺(tái)都要做）
shell> vim /etc/security/limits.conf
* soft nproc 65535 
* hard nproc 65535 
* soft nofile 65535 
* hard nofile 65535

shell> echo "fs.file-max=65535" >> /etc/sysctl.conf

[root@MYSQL src]# mkdir /usr/local/mysql/binlog
[root@MYSQL src]# mkdir /usr/local/mysql/iblog
[root@MYSQL src]# chown mysql:mysql /usr/local/mysql/binlog
[root@MYSQL src]# chown mysql:mysql /usr/local/mysql/iblog
[root@MYSQL src]# chmod -R 755 /usr/local/mysql
  1. 在各個(gè)節(jié)點(diǎn)安裝半同步插件
在192.168.40.171、172、173上：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=on;
  1. 修改 /etc/my.cnf 配置
#修改server_id=ip尾數(shù)，增加如下：
read_only=1                                 #S1、S2上配置
#binlog
log_bin=/usr/local/mysql/binlog/binlog      #開(kāi)啟二進(jìn)制日志
log-slave-updates=1                         # 做為從庫(kù)時(shí)，數(shù)據(jù)庫(kù)的修改也會(huì)寫(xiě)到bin-log里
#開(kāi)啟半同步復(fù)制  否則自動(dòng)切換主從的時(shí)候會(huì)報(bào)主鍵錯(cuò)誤
#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 = 500
relay-log = mysql-relay-bin
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%
replicate-do-db=test                #要同步的數(shù)據(jù)庫(kù)，默認(rèn)所有庫(kù)
  1. 搭建主從半同步復(fù)制
#說(shuō)明：下面的搭建方法是比較通用的方法（本例中三個(gè)節(jié)點(diǎn)都是新的數(shù)據(jù)庫(kù)，可以不用遷移數(shù)據(jù)）。
1、在192.168.40.171上創(chuàng)建復(fù)制用戶(hù)
grant replication slave on *.* to 'repl'@'192.168.40.%' identified by '123456';

2、在192.168.40.171上執(zhí)行備份
[root@MYSQL src]# mysqldump -uroot -p --master-data=2 --single-transaction --default-character-set=utf8 -R --triggers -A >all2.sql

3、查看上備份時(shí)刻Binlog的名稱(chēng)和位置，MASTER_LOG_FILE 和 MASTER_LOG_FILE
[root@panda001 opt]# head -n 30 all2.sql|grep -i "CHANGE MASTER TO"
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=716;

4、將all.sql拷貝到 192.168.40.172 和 192.168.40.173機(jī)器上
scp all2.sql root@192.168.40.172:/home
scp all2.sql root@192.168.40.173:/home

5、在192.168.40.172上搭建備庫(kù)
root@panda002 home]# mysql -uroot -p<all2.sql
[root@panda002 home]# mysql -uroot -p
mysql> change master to master_host='192.168.40.171',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=716;

6、在192.168.40.173上搭建備庫(kù)
root@panda002 home]# mysql -uroot -p<all2.sql
[root@panda002 home]# mysql -uroot -p
mysql> change master to master_host='192.168.40.171',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=716;
  1. 啟動(dòng)主從復(fù)制
#在192.168.40.172上：
mysql> start slave;
mysql> set global read_only=1;

#在192.168.40.173上：
mysql> start slave;
mysql> set global read_only=1;

#查看S1、S2
mysql> show slave status\G
----------------------------------------------------------------------------------------------
        Slave_IO_Running: No
        Slave_SQL_Running: Yes
        Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
----------------------------------------------------------------------------------------------
#在庫(kù)MYSQLS1及MYSQLS1上執(zhí)行（相同則則修改b庫(kù)id，找到data文件夾下的auto.cnf文件，修改里面的uuid值，保證各個(gè)db的uuid不一樣，重啟db即可）
mysql> show variables like '%server_uuid%';

#在各庫(kù)mysqla及mysqlb上執(zhí)行,都為yes則ok
mysql> show slave status\G
----------------------------------------------------------------------------------------------
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
----------------------------------------------------------------------------------------------
  1. 各庫(kù)my.cnf 參考171
[mysql]
default-character-set=utf8
prompt=\\u@\\h : \\d【\\r:\\m:\\s】\\c SQL->
[mysqld]
server_id=171
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
# 一般配置選項(xiàng)
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/usr/local/mysql/mysql.sock
default-storage-engine=INNODB
character_set_server=utf8
log_error=/usr/local/mysql/logs/error.log
#read_only=1                                 #S1、S2上配置
#binlog
log_bin=/usr/local/mysql/binlog/binlog      #開(kāi)啟二進(jìn)制日志
log-slave-updates=1                         # 做為從庫(kù)時(shí)，數(shù)據(jù)庫(kù)的修改也會(huì)寫(xiě)到bin-log里
#開(kāi)啟半同步復(fù)制  否則自動(dòng)切換主從的時(shí)候會(huì)報(bào)主鍵錯(cuò)誤
#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 = 500
relay-log = mysql-relay-bin
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%
replicate-do-db=test                #要同步的數(shù)據(jù)庫(kù)，默認(rèn)所有庫(kù)
  1. 各庫(kù)my.cnf 參考172
[mysql]
default-character-set=utf8
prompt=\\u@\\h : \\d【\\r:\\m:\\s】\\c SQL->
[mysqld]
server_id=172
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
# 一般配置選項(xiàng)
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/usr/local/mysql/mysql.sock
default-storage-engine=INNODB
character_set_server=utf8
log_error=/usr/local/mysql/logs/error.log
read_only=1                                 #S1、S2上配置
#binlog
log_bin=/usr/local/mysql/binlog/binlog
log-slave-updates=1
#開(kāi)啟半同步復(fù)制  否則自動(dòng)切換主從的時(shí)候會(huì)報(bào)主鍵錯(cuò)誤
#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 = 500
relay-log = mysql-relay-bin
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%
replicate-do-db=test                #要同步的數(shù)據(jù)庫(kù)，默認(rèn)所有庫(kù)
  1. 各庫(kù)my.cnf 參考173
[mysql]
default-character-set=utf8
prompt=\\u@\\h : \\d【\\r:\\m:\\s】\\c SQL->
[mysqld]
server_id=173
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
# 一般配置選項(xiàng)
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/usr/local/mysql/mysql.sock
default-storage-engine=INNODB
character_set_server=utf8
log_error=/usr/local/mysql/logs/error.log
read_only=1                                 #S1、S2上配置
#binlog
log_bin=/usr/local/mysql/binlog/binlog
log-slave-updates=1
#開(kāi)啟半同步復(fù)制  否則自動(dòng)切換主從的時(shí)候會(huì)報(bào)主鍵錯(cuò)誤
#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 = 500
relay-log = mysql-relay-bin
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%
replicate-do-db=test                #要同步的數(shù)據(jù)庫(kù)，默認(rèn)所有庫(kù)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容