master：主服务器
slave：从服务器
1、开启master的binarylog
打开MySQL的配置文件
# vim /etc/my.cnf
添加以下代码
log-bin=mysql-bin
binlog_format=mixed
server-id=1
read-only=0
binlog-do-db=<dbname>
重启master数据库
# /etc/inint.d/mysqld restart
2、导出master数据库
锁定master数据库
mysql> FLUSH TABLES WITH READ LOCK;
导出master数据库
# mysqldump --master-data -uroot -p <dbname> > <path>+<dbname>.sql
记录master的日志信息
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: <dbname>
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
解除锁定
mysql> UNLOCK TABLES;
3、开启slave的binarylog
打开MySQL的配置文件
# vim /etc/my.cnf
添加以下代码
log-bin=mysql-bin
binlog_format=mixed
server-id=2
replicate-do-db=<dbname>
relay_log=mysql-relay-bin
log-slave-updates=OFF
重启slave数据库
# /etc/inint.d/mysqld restart
4、导入master的数据到slave中
在slave中创建相应的数据库
CREATE DATABASE `<dbname>` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
导入数据库
mysql> source <path>+<dbname>.sql
5、在master中创建同步账户
mysql> grant replication slave on *.* to '<master_user>'@'<slave_host>' identified by '<master_password>';
如：允许所有ip使用该用户名和密码登陆
mysql> grant replication slave on *.* to 'db_backup'@'%' identified by 'root';
6、在slave中开启同步
mysql> CHANGE MASTER TO
MASTER_HOST='<master_host>',
MASTER_USER='<master_user>',
MASTER_PASSWORD='<master_password>',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
此处的MASTER_LOG_FILE、MASTER_LOG_POS的值参考2中的master日志信息
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: < dbname >
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
重启slave数据库
# /etc/inint.d/mysqld restart
查看slave的slave线程是否开启
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <master_host>
Master_User: <master_user>
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 639
Relay_Log_File: mysqld-relay-bin.001075
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: <dbname>
Replicate_Ignore_DB:
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: 233
Relay_Log_Space: 15668
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Slave_IO_State: Waiting for master to send event
表示线程已开启，等待master的数据
7、遇到的问题
1、在my.cnf中加入log-bin=mysql-bin后仍无法开启binarylog
- 添加后需重启MySQL
- 存在多个my.cnf，用find / -name "my.cnf" 找到其他的my.cnf，一般来说，其中有socket=...的一般都是有效的配置文件
- 在my.cnf中添加的代码位置不对，尽量上移，在[mysqld]的最后面即可
2、slave的状态中有Slave_IO_Running: Connecting 错误
- 检查< master_host > 、< master_user >、< master_password >是否手误填写错误
- master服务器的3306端口没有允许外部访问，在master中添加防火墙例外。然后用
telnet <master_host> 3306测试是否连通 - 检查master中创建的同步账户的< slave_host >
