mysql雙機互備方式配置

charsi發表於2017-10-23
MySql
A庫：192.168.190.130
B庫：192.168.190.131

A庫建使用者
grant FILE,SELECT, replication slave on *.* to 'gaok_bak'@'192.168.190.131' identified by 'gaok';
flush privileges;

show grants for 'gaok_bak'@'192.168.190.131';

B庫建使用者
grant FILE,SELECT, replication slave on *.* to 'gaok_bak'@'192.168.190.130' identified by 'gaok';
flush privileges;

show grants for 'gaok_bak'@'192.168.190.130';

A庫my.cnf配置(新增以下內容)：
[mysqld]
server-id = 1
log_bin = /usr/local/mysql/data/mysql-bin.log
relay_log = /usr/local/mysql/data/mysql-relay.log
log-slave-updates = ON
sync_binlog=1
auto-increment-increment=10
auto-increment-offset=1

B庫my.cnf配置(新增以下內容)：
[mysqld]
server-id = 2
log_bin = /usr/local/mysql/data/mysql-bin.log
relay_log = /usr/local/mysql/data/mysql-relay.log
log-slave-updates = ON
auto-increment-increment=10
auto-increment-offset=1
sync_binlog=1


#####兩個mysql重啟
A庫、B庫檢視master狀態

A庫:
change master to
master_host='192.168.190.131',
master_user='gaok_bak',
master_password='gaok',
master_log_file='mysql-bin.000002',
master_log_pos=154;

start slave;
show slave status\G

B庫：
change master to
master_host='192.168.190.130',
master_user='gaok_bak',
master_password='gaok',
master_log_file='mysql-bin.000002',
master_log_pos=154;

start slave;
show slave status\G

----------測試是否正確。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-2146232/，如需轉載，請註明出處，否則將追究法律責任。

相關文章