mysql 互为主从配置

1、准备工作

1）机器A，安装mysql-server，设置root密码，删除匿名用户，创建需要同步的数据库，创建一个外部连接用户，创建一个同步用户

2）机器B，安装mysql-server，设置root密码，删除匿名用户，创建需要同步的数据库，创建一个外部连接用户，创建一个同步用户

注意若要导入数据，请保证机器A和机器B，需要同步的数据库中的数据一致才开始后续步骤。

命名如下：

yum install mysql-server
set password for root@localhost=password('密码');
delete from mysql.user where user='';
create database test;
grant all privileges on test.* to '用户名'@'%' identified by '密码';
grant replication slave on test.* to '用户名'@'192.168.18.%' IDENTIFIED BY '密码';

2、配置文件

机器A

[mysqld]
lower_case_table_names=1
default-character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=500

server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db=test
replicate-ignore-db=mysql,information_schema

[client]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

机器B

[mysqld]
lower_case_table_names=1
default-character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=500

server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db=mysql,information_schema
binlog-do-db=test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2

[client]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

3、操作

1）分别重启机器A和机器B

2）机器A和机器B分别执行如下命令，查看binlog和pos（第3小步需要），然后将表解锁

mysql>flush tables with read lock; 
mysql>show master status\G;
mysql>unlock tables;

3）机器A和机器B分别执行如下命令，

mysql>change master to master_host='192.168.18.*', master_user='用户名', master_password='密码', master_log_file='binlog.000005', master_log_pos=107;

4）机器A和机器B分别执行如下命令，

mysql>start slave;
mysql>show slave status\G;

显示如下内容说明配置成功，

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

4、测试

分别从机器A和机器B中添加数据，看是否都可以同步。