目錄

1、環境設置 2、配置my.cnf配置文件 3、授權允許同步 4、開啟slave同步 5、驗證互為主從 6、安裝keepalived 7、配置keepalived 8、驗證

1、環境設置

10.0.0.132?master1 10.0.0.134?master2 setenforce?0 systemctl?stop?firewalld

2、配置my.cnf配置文件

master1上

vim?/etc/my.cnf[client]socket=/usr/local/mysql/mysql.sock[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlpid-file=/usr/local/mysql/data/mysqld.pidlog-error=/usr/local/mysql/data/mysql.errsocket=/usr/local/mysql/mysql.sockport=3306server_id=1log-bin=mysql-bingtid-mode=onenforce-gtid-consistency=1log_slave_updates?=?1binlog-format=mixedauto-increment-increment=2??????????auto-increment-offset=1 systemctl?restart?mysqld

master2上

vim?/etc/my.cnf [client]socket=/usr/local/mysql/mysql.sock[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlpid-file=/usr/local/mysql/data/mysqld.pidlog-error=/usr/local/mysql/data/mysql.errsocket=/usr/local/mysql/mysql.sockport=3306server_id=2log-bin=mysql-bingtid-mode=onenforce-gtid-consistency=1log_slave_updates?=?1binlog-format=mixedauto-increment-increment=2??auto-increment-offset=2 systemctl?restart?mysqld

3、授權允許同步

master1上

mysql?-uroot?-p mysql>?grant?replication?slave?on?*.*?to?rep@'10.0.0.%'?identified?by?'123';

master2上

mysql?-uroot?-p mysql>?grant?replication?slave?on?*.*?to?rep@'10.0.0.%'?identified?by?'123';

4、開啟slave同步

master1上

mysql>?change?master?to?master_host='10.0.0.134',master_user='rep',master_password='123',master_port=3306,master_autoter_auto_position=1; mysql>?start?slave;

master2上

mysql>?change?master?to?master_host='10.0.0.132',master_user='rep',master_password='123',master_port=3306,master_autoter_auto_position=1; mysql>?start?slave;

5、驗證互為主從

master1上

mysql>?show?slave?status\G; ***************************?1.?row?***************************Slave_IO_State:?Waiting?for?master?to?send?eventMaster_Host:?10.0.0.134Master_User:?repMaster_Port:?3306Connect_Retry:?60Master_Log_File:?mysql-bin.000001Read_Master_Log_Pos:?154Relay_Log_File:?master1-relay-bin.000002Relay_Log_Pos:?367Relay_Master_Log_File:?mysql-bin.000001Slave_IO_Running:?YesSlave_SQL_Running:?YesReplicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0Last_Error:?Skip_Counter:?0Exec_Master_Log_Pos:?154Relay_Log_Space:?576Until_Condition:?NoneUntil_Log_File:?Until_Log_Pos:?0Master_SSL_Allowed:?NoMaster_SSL_CA_File:?Master_SSL_CA_Path:?Master_SSL_Cert:?Master_SSL_Cipher:?Master_SSL_Key:?Seconds_Behind_Master:?0 Master_SSL_Verify_Server_Cert:?NoLast_IO_Errno:?0Last_IO_Error:?Last_SQL_Errno:?0Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?2Master_UUID:?e59d0925-be6a-11e8-9cab-000c29b63badMaster_Info_File:?/usr/local/mysql/data/master.infoSQL_Delay:?0SQL_Remaining_Delay:?NULLSlave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?more?updatesMaster_Retry_Count:?86400Master_Bind:?Last_IO_Error_Timestamp:?Last_SQL_Error_Timestamp:?Master_SSL_Crl:?Master_SSL_Crlpath:?Retrieved_Gtid_Set:?Executed_Gtid_Set:?Auto_Position:?1Replicate_Rewrite_DB:?Channel_Name:?Master_TLS_Version:? 1?row?in?set?(0.00?sec)

master2上

mysql>?show?slave?status\G; ***************************?1.?row?***************************Slave_IO_State:?Waiting?for?master?to?send?eventMaster_Host:?10.0.0.132Master_User:?repMaster_Port:?3306Connect_Retry:?60Master_Log_File:?mysql-bin.000001Read_Master_Log_Pos:?154Relay_Log_File:?master2-relay-bin.000002Relay_Log_Pos:?367Relay_Master_Log_File:?mysql-bin.000001Slave_IO_Running:?YesSlave_SQL_Running:?YesReplicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0Last_Error:?Skip_Counter:?0Exec_Master_Log_Pos:?154Relay_Log_Space:?576Until_Condition:?NoneUntil_Log_File:?Until_Log_Pos:?0Master_SSL_Allowed:?NoMaster_SSL_CA_File:?Master_SSL_CA_Path:?Master_SSL_Cert:?Master_SSL_Cipher:?Master_SSL_Key:?Seconds_Behind_Master:?0 Master_SSL_Verify_Server_Cert:?NoLast_IO_Errno:?0Last_IO_Error:?Last_SQL_Errno:?0Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?1Master_UUID:?ceb0ca3d-8366-11e8-ad2b-000c298b7c9aMaster_Info_File:?/usr/local/mysql/data/master.infoSQL_Delay:?0SQL_Remaining_Delay:?NULLSlave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?more?updatesMaster_Retry_Count:?86400Master_Bind:?Last_IO_Error_Timestamp:?Last_SQL_Error_Timestamp:?Master_SSL_Crl:?Master_SSL_Crlpath:?Retrieved_Gtid_Set:?Executed_Gtid_Set:?Auto_Position:?1Replicate_Rewrite_DB:?Channel_Name:?Master_TLS_Version:? 1?row?in?set?(0.00?sec)

6、安裝keepalived

master1上

yum?-y?install?keepalived

master2上

yum?-y?install?keepalived

7、配置keepalived

master1上

vim /etc/keepalived/keepalived.conf

global_defs?{router_id?master1 }vrrp_instance?VI_1?{state?BACKUPinterface?ens33virtual_router_id?51priority?100advert_int?1nopreemptauthentication?{auth_type?PASSauth_pass?1111}???virtual_ipaddress?{10.0.0.100?}??? }???virtual_server?192.168.1.100?3306?{delay_loop?6lb_algo?rr?lb_kind?DR?persistence_timeout?50protocol?TCPreal_server?10.0.0.132?3306?{weight?1notify_down?/etc/keepalived/bin/mysql.sh??????????????????TCP_CHECK?{connect_timeout?3retry?3delay_before_retry?3connect_port?3306}} }systemctl?enable?keepalived systemctl?start?keepalived mkdir?/etc/keepalived/bin

vim /etc/keepalived/bin/mysql.sh

#!/bin/bash pkill?keepalived /sbin/ifdown?ens33?&&?/sbin/ifup?ens33chmod?+x?/etc/keepalived/bin/mysql.sh

master2上

vim /etc/keepalived/keepalived.conf

global_defs?{router_id?master2 }vrrp_instance?VI_1?{state?BACKUPinterface?ens33virtual_router_id?51priority?50advert_int?1nopreemptauthentication?{auth_type?PASSauth_pass?1111}???virtual_ipaddress?{10.0.0.100?}??? }???virtual_server?192.168.1.100?3306?{delay_loop?6lb_algo?rr?lb_kind?DR?persistence_timeout?50protocol?TCPreal_server?10.0.0.134?3306?{weight?1notify_down?/etc/keepalived/bin/mysql.sh??????????????????TCP_CHECK?{connect_timeout?3retry?3delay_before_retry?3connect_port?3306}} }systemctl?enable?keepalived systemctl?start?keepalived mkdir?/etc/keepalived/bin

vim /etc/keepalived/bin/mysql.sh

#!/bin/bash pkill?keepalived /sbin/ifdown?ens33?&&?/sbin/ifup?ens33chmod?+x?/etc/keepalived/bin/mysql.sh

8、最終驗證（keepalived雙主）

驗證一

在master1上

ip addr show ens33可以看到我們設置vip

在master2上

ip addr show ens33沒有vip出現

驗證二

在兩臺數據庫上授權允許remote用戶允許遠程登錄

master1上

mysql?-uroot?-p mysql>?grant?all?on?*.*?to?remote@'%'?identified?by?'123';

master2上

mysql?-uroot?-p mysql>?grant?all?on?*.*?to?remote@'%'?identified?by?'123';

另尋找一臺MySQL數據庫使用vip遠程訪問數據庫集群

查看server_id，圖示為1，所以說明我們當前登錄到了master1上；也證明了master1如今是active狀態的，而master2是備份狀態

我們創建創建一個數據庫試試

回到master1上

再到master2上查看

這就說明我們的主從復制也是沒有問題的

驗證三：驗證keepalived雙主集群的可用性

首先停掉master1上mysqld服務

systemctl?stop?mysqld

再次查看master1的ens33網卡，vip已經消失

查看master2的ens33網卡，出現vip

再次通過vip遠程登錄，依然可以登錄，而且可以看到server_id變成了2。

轉載于:https://blog.51cto.com/13434336/2178982

總結

以上是生活随笔為你收集整理的MySQL数据库Keepalived双主的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。