溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下面一起來了解下MySQL數據庫如何配置Keepalived雙主，相信大家看完肯定會受益匪淺，文字在精不在多，希望MySQL數據庫如何配置Keepalived雙主這篇短內容是你想要的。
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/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 server_id=1 log-bin=mysql-bin gtid-mode=on enforce-gtid-consistency=1 log_slave_updates = 1 binlog-format=mixed auto-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/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 server_id=2 log-bin=mysql-bin gtid-mode=on enforce-gtid-consistency=1 log_slave_updates = 1 binlog-format=mixed auto-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 event Master_Host: 10.0.0.134 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: master1-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 576 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: e59d0925-be6a-11e8-9cab-000c29b63bad Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_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 event Master_Host: 10.0.0.132 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: master2-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 576 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_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 BACKUP
interface ens33
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.0.100
}
}
virtual_server 192.168.1.100 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 10.0.0.132 3306 {
weight 1
notify_down /etc/keepalived/bin/mysql.sh
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
connect_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 ens33
chmod +x /etc/keepalived/bin/mysql.sh
master2上
vim /etc/keepalived/keepalived.conf
global_defs {
router_id master2
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 50
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.0.100
}
}
virtual_server 192.168.1.100 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 10.0.0.134 3306 {
weight 1
notify_down /etc/keepalived/bin/mysql.sh
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
connect_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 ens33
chmod +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。
看完MySQL數據庫如何配置Keepalived雙主這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
