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中添加数据,看是否都可以同步。