发布时间：2016-01-13 15:00:40 来源： 作者：
master：192.168.1.210（CentOS6.5）
slave：192.168.1.211（CentOS6.5）
VIP：192.168.1.208
MySQL主主互备模式配置
step1：Master服务的/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.% #指定不需要复制的库，mysql.%表示mysql库下的所有对象 replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% |
step2：Slave服务的/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：重启两台主从mysql服务
|
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日志状态
记录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：创建主从同步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';<
公司简介 解决方案 客户案例 官方微信 |
