Centos下MySQL数据库主从双向同步配置

时间:2024-07-29 23:06:50

MYSQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后*并等待主服务器通知下一次更新。

    在实际项目中,两台分布于异地的主机上安装有MYSQL数据库,两台服务器互为主备,客户要求当其中一台机器出现故障时,另外一台能够接管服务器上的应用,这就需要两台数据库的数据要实时保持一致,在这里使用MYSQL的同步功能实现双机的同步复制。

一、修改10.32.8.9的配置文件my.cnf如下(红色标注是必须配置的,其他参数可以自行配置)

[mysqld]
server-id=
auto_increment_offset=
auto_increment_increment=
log-bin=mysql-bin
log-error=/var/log/mysqld.log
#pid-file=/mysqldata/mysqld.pid
master-host=10.32.8.29
master-user=backup
master-password=
master-port=
replicate-do-db=common
replicate-ignore-db=mysql
master-connect-retry=

二、修改10.32.8.29的配置文件my.cnf如下(红色标注是必须配置的,其他参数可以自行配置)

[mysqld]
server-id=
auto_increment_offset=
auto_increment_increment=
log-bin=mysql-bin
log-error=/var/log/mysqld.log
#pid-file=/mysqldata/mysqld.pid
master-host=10.32.8.9
master-user=backup
master-password=
master-port=
replicate-do-db=common
replicate-ignore-db=mysql
master-connect-retry=

三、在两台mysql数据库服务器里面设置权限,分别执行如下命令:

grant replication slave on *.* to 'backup'@'10.32.8.%' identified by '123456';

四、在10.32.8.9上查看master运行状态,获取binlog日志信息:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+-----------+--------------+------------------+
row in set (0.00 sec)

在10.32.8.29上,先停止slave:slave stop;

然后再执行:

change master to master_host='10.32.8.9',master_user='backup',master_password='backup@nirec.net',master_log_file='mysql-bin.000052',master_log_pos=;

再启动slave:slave start;

五、在10.32.8.29上查看master运行状态,获取binlog日志信息:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec)

在10.32.8.9上,先停止slave:slave stop;

然后再执行:

change master to master_host='10.32.8.29',master_user='backup',master_password='backup@nirec.net',master_log_file='mysql-bin.000005',master_log_pos=;

再启动slave:slave start;

六、查看slave状态:

在10.32.8.9和10.32.8.29上分别查看slave状态,如果Slave_IO_Running,Slave_SQL_Running都显示为Yes即可。

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.32.8.29
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 244
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Yes #显示为YES即可
Slave_SQL_Running: Yes #显示为YES即可
Replicate_Do_DB: common
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 244
Relay_Log_Space: 693
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec) ERROR:
No query specified

注:如果master主机上的日志文件已经不存在,则需要首先从master主机上恢复全部数据,再开启同步机制。

在slave主机上运行:

mysql> stop slave;

在master主机上运行:

mysql> stop slave;

在slave主机上运行:

mysql> load data from master;

mysql> reset master;

mysql> start slave;

在master主机上运行:

mysql> reset slave;

mysql>start slave;

注意:LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的数据库上有效。