联系：QQ(5163721)
标题：MYSQL小白的FAQ系列—-7—-如何配置mysql主从同步（Master-Slave）
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
======================================
主数据库master修改
======================================
# 是master的日志文件，存放地址和名称
log-bin=/u01/mysql/data/binlog/mysql-bin.index
log-bin=/u01/mysql/data/binlog/mysql-bin
# 日志格式，建议mixed
binlog_format = mixed
# 主数据库端ID号
server-id = 1
#不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
#只同步哪些数据库，除此之外，其他不同步
binlog-do-db = lunar
#日志保留时间
expire_logs_days = 10
#控制binlog文件的更新频率。每执行n次事务保存一次
#这个参数性能消耗很大，但可减小MySQL崩溃造成的损失
sync_binlog = 1
#查看log位置：
mysqlbinlog log-file | mysql -h server_name
#创建用于同步的账户：
#创建slave帐号slave，密码lunar
mysql>grant replication slave,select,reload,super on *.* to ‘slave’@’%’ identified by ‘lunar’;
#更新数据库权限
mysql>flush privileges;
重启mysql（使上面更改的参数生效）
[root@ebsdba2 u01]# service mysql stop
Shutting down MySQL..[ OK ]
[root@ebsdba2 u01]# service mysql start
Starting MySQL..[ OK ]
[root@ebsdba2 u01]#
启动后在主库执行：
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
记录下这个位置。
把主库的lunar数据库导出：
mysqldump -u root -p lunar > lunar.sql
然后在mysql解锁： UNLOCK TABLES;
把lunar.sql文件scp到从库上。
======================================
从数据库slave修改
======================================
mkdir -p /u01/mysql/data/binlog mkdir -p /u01/mysql/data/relaylog mkdir -p /u01/mysql/mysqldata chown mysql:mysql /u01/mysql grant all privileges on *.* to root@'%' identified by "lunar" with grant option; grant replication slave,select,reload,super on *.* to 'slave'@'%' identified by 'lunar'; grant all privileges on *.* to lunar@'%' identified by "lunar"; flush privileges; 从库连接主库进行测试： /opt/mysql/bin/mysql -u slave -p -h 10.45.10.141 停止从库，修改从库参数 [mysqld] server-id=2 #basedir = /u01/mysql datadir = /u01/mysql/mysqldata port = 3306 replicate-do-db=lunar log-bin=/u01/mysql/data/binlog/mysql-bin.index log-bin=/u01/mysql/data/binlog/mysql-bin slave-skip-errors=1022,1032,1062 log_slave_updates=1 log_bin_trust_function_creators=1 auto_increment_increment=2 auto_increment_offset=1 #bind-address=10.0.2.31 #hostname= relay-log=/u01/mysql/data/relaylog/mysql-relay-bin.index relay-log=/u01/mysql/data/relaylog/mysql-relay-bin
[root@ebsdba1 u01]# service mysql stop
Shutting down MySQL..[ OK ]
[root@ebsdba1 u01]# service mysql start
Starting MySQL..[ OK ]
[root@ebsdba1 u01]#
在从库上导入lunar.sql:
[root@ebsdba1 mysql]# mysql -ulunar -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.21-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> create database lunar; Query OK, 1 row affected (0.00 sec) mysql> use lunar; Database changed mysql> source lunar.sql Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_lunar | +-----------------+ | lunar | | new | +-----------------+ 2 rows in set (0.00 sec) mysql>
#执行同步命令，设置主数据库ip，同步帐号密码
change master to master_host='10.45.10.141' , master_user='slave', master_password='lunar' , master_log_file='mysql-bin.000003', master_log_pos=120;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.45.10.141
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 353
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 516
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lunar
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: 353
Relay_Log_Space: 852
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: 9e56df6a-642f-11e4-957d-005056ad43d4
Master_Info_File: /u01/mysql/mysqldata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
在主库插入数据：
[root@ebsdba2 ~]# mysql -ulunar -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.21-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use lunar;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into lunar value('lunar','hao baobao');
Query OK, 1 row affected (0.01 sec)
mysql> select * from lunar;
+-------+------------+
| name | phone |
+-------+------------+
| lunar | 123456 |
| lunar | hao baobao |
+-------+------------+
2 rows in set (0.00 sec)
mysql>
再从库检查数据：
[root@ebsdba1 mysqldata]# mysql -ulunar -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.6.21-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use lunar; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from lunar; +-------+------------+ | name | phone | +-------+------------+ | lunar | 123456 | | lunar | hao baobao | +-------+------------+ 2 rows in set (0.00 sec) mysql>
上述表明数据已经自动同步了，且查看的slave status也正常。
检查主库进程：
mysql> show processlist; +----+-------+---------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+---------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------+ | 2 | slave | ebsdba1.800best.com:31106 | NULL | Binlog Dump | 1763 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 3 | lunar | localhost | lunar | Sleep | 1595 | | NULL | | 4 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-------+---------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) mysql>
检查备库的进程：
mysql> show processlist; +----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+ | 1 | slave | localhost | NULL | Sleep | 9 | | NULL | | 11 | system user | | NULL | Connect | 1753 | Waiting for master to send event | NULL | | 12 | system user | | NULL | Connect | 1590 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 13 | lunar | localhost | lunar | Query | 0 | init | show processlist | +----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------------+------------------+ 4 rows in set (0.00 sec) mysql>
常见问题1：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
解决：从库和主库的server_id是相同的值，需要修改为不同值：show variables like ‘server_id’;
修改从库的/usr/my.cnf中的server_id，不要和主库重复
然后重启从库。使用slave登陆后，执行：
change master to master_log_file='mysql-bin.000003', master_log_pos=120; start slave; show slave status \G; 如果在主库和从库的任何一个中，没有显示的指定server_id，那么也可能会报这类错误，通过指定set global server_id也不能排除问题： mysql> set global server_id=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'server%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 2 | | server_id_bits | 32 | | server_uuid | 8abc63a1-64cd-11e4-9983-005056ad27e5 | +----------------+--------------------------------------+ 3 rows in set (0.00 sec) mysql>
这时候，检查主库和备库的参数，显示指定主库和备库的server_id为不同的值，然后重启服务器，问题就解决了。
查了一下，说是mysql的bug。
常见问题2： 有时候会遇到类似下面的错误：
2014-11-05 21:40:46 25430 [ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Error_code: 1872 2014-11-05 21:40:59 25430 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='10.45.10.141', master_port= 3306, master_log_file='mysql-bin.000001', master_log_pos= 120, master_bind=''. New state master_host='10.45.10.141', master_port= 3306, master_log_file='mysql-bin.000003', master_log_pos= 120, master_bind=''.
可以使用mysqlbinlog来查看:
[root@ebsdba1 mysqldata]# mysqlbinlog --start-position=120 /u01/mysql/data/binlog/mysql-bin.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #141105 22:12:09 server id 1 end_log_pos 120 CRC32 0xaf2e2d80 Start: binlog v 4, server v 5.6.21-enterprise-commercial-advanced-log created 141105 22:12:09 at startup ROLLBACK/*!*/; BINLOG ' OTBaVA8BAAAAdAAAAHgAAAAAAAQANS42LjIxLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl ZC1sb2cAAAAAAAAAAAA5MFpUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYAt Lq8= '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@ebsdba1 mysqldata]#
然后根据错误提示，调整master_log_file和master_log_pos。
例如：
change master to master_log_file=’mysql-bin.000003′, master_log_pos=120;
Pingback 引用通告： max