發(fā)布時(shí)間：2016-01-13 15:00:40 來(lái)源： 作者：
master：192.168.1.210（CentOS6.5）
slave：192.168.1.211（CentOS6.5）
VIP：192.168.1.208
MySQL主主互備模式配置
step1：Master服務(wù)的/etc/my.cnf配置
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[mysqld] basedir = /usr/local/mysql datadir = /var/lib/mysql port = 3306 socket = /var/lib/mysql/mysql.sock
server_id = 1 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% #指定不需要復(fù)制的庫(kù)，mysql.%表示mysql庫(kù)下的所有對(duì)象 replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% |
step2：Slave服務(wù)的/etc/my.cnf配置
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[mysqld] basedir = /usr/local/mysql datadir = /var/lib/mysql port = 3306 socket = /var/lib/mysql/mysql.sock
server_id = 2 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% |
step3：重啟兩臺(tái)主從mysql服務(wù)
|
1 2 3 4 5 6 |
[root@master ~]# service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] [root@slave ~]# service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] |
step4：查看主從的log-bin日志狀態(tài)
記錄File和Position的值
|
1 2 3 4 5 6 7 |
[root@master ~]# mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+ |
|
1 2 3 4 5 6 7 |
[root@slave ~]# mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+ |
step5：創(chuàng)建主從同步replication用戶
1、master
|
1 2 3 4 5 6 7 8 9 10 |
mysql> grant replication slave on *.* to 'replication'@'192.168.1.%' identified by 'replication'; mysql> flush privileges; mysql> change master to -> master_host='192.168.1.211', -> master_user='replication', -> master_password='replication', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=414; mysql> start slave; |
2、slave
|
1 2 3 4 5 6 7 8 9 10 |
mysql> grant replication slave on *.* to 'replication'@'192.168.1.%' identified by 'replication';<
公司簡(jiǎn)介 解決方案 客戶案例 官方微信 |
