1.事先环境准备
首先确保两台mysql服务器的防火墙都关闭,mysql的root密码最好保持一致
MASTER A 192.168.1.25
MASTER A 192.168.1.27
2.配置mysql配置文件
修改MASTER A的mysql配置文件,表示通过二进制日志同步,其中server-id两台机器必须设置不同的值
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin = mysql-bin
server-id = 2
expire_logs_days = 5
replicate-do-db = seeyon //记录那些库需要做同步操作
重启MASTER A数据库
service mysqld restart
修改MASTER B的mysql配置文件,表示通过二进制日志同步
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin = mysql-bin
server-id = 1
expire_logs_days = 5
replicate-do-db = seeyon //记录那些库需要做同步操作
重启MASTER B数据库
service mysqld restart
3.配置主从同步
MASTER A上登录mysql
mysql> grant replication slave on *.* to 'slave'@'192.168.1.27' identified by '123@qwe';
mysql> flush privileges;
查看MASTER A状态并记录相关数据,file以及position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 690 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
MASTER B上登录mysql
mysql> grant replication slave on *.* to 'slave'@'192.168.1.25' identified by '123@qwe';
mysql> flush privileges;
看MASTER B状态并记录相关数据,file以及position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 96588 | seeyon | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置MASTER A同步信息
mysql> change master to master_host='192.168.1.27',master_port=3306,
>master_user='slave',master_password='123@qwe',master_log_file='mysql-bin.000002',master_log_pos=690;
mysql> start slave;
mysql> show slave status\G;
显示有如下状态则正常:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置MASTER B同步信息
mysql> change master to master_host='192.168.1.25',master_port=3306,
>master_user='slave',master_password='123@qwe',master_log_file='mysql-bin.000006',master_log_pos=96588;
mysql> start slave;
mysql> show slave status\G;
显示有如下状态则正常:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.测试主从同步
在A数据库增加测试表,添加数据看在B数据库中是否有相关变动。
在B数据库增加测试表,添加数据看在A数据库中是否有相关变动。
4.相关异常问题:
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file’系列一:
主库添加log-bin-index 参数后,从库报这个错误:Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Got fatal error 1236 from master when reading data from binary log: ‘could not find next log’
可以
stop slave;
reset slave;
start slave;