MySQL主主同步详细操作过程

此处是在公司线上部署MySQL双主时所记录的文档，为安全，IP都改为内网IP。版本信息：#mysql -Vmysql Ver 14.14 Distrib 5.5.37, fordebian-linux-gnu （x86_64） using readline 6.2 1、主库开启bin-log功能，配置server-id修改my.cf配置文件，开启bin-log功能，配置server-id。#cat /etc/mysql/my.cnf[myqld]server-id = 1log_bin =/var/log/mysql/slave-net-timeout = 60
#salve-net-timeout默认是3600秒，缩短时间是为了防止双YES的假象
#（事实上我已遇到，参考地址：http://www.cnblogs.com/billyxp/p/3470376.html）如果要指定同步或不同步哪些库，可使用如下参数#binlog-do-db=osyunweidb #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
#binlog-ignore-db=mysql #不同步mysql系统数据库 2、确认bin-log与server-id是否开启：查看命令 show variables like "log_bin"; show variables like "server_id";mysql> show variables like "log_bin";+---------------+-------+|Variable_name | Value |+---------------+-------+|log_bin | ON |+---------------+-------+1 rowin set （0.00 sec）mysql>show variables like "server_id";+---------------+-------+|Variable_name | Value |+---------------+-------+|server_id | 1 |+---------------+-------+1 rowin set （0.00 sec） 3、创建复制授权用户mysql> grant replication slave on *.* to replication@"%"identified by "123456"; #授权该用户对所有表都能进行复制mysql>flush privileges; #刷新权限 4、锁表，记录log-bin文件名和位置mysql>flush tables with read lock; #锁定所有表，此时数据库不能写入数据QueryOK, 0 rows affected （0.05 sec） mysql>show master status; #查看最新bin-log文件及位置+------------------------+------------+-------------------+-------------------------+|File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------------+------------+-------------------+-------------------------+|mysql-bin.000001 | 26314 | | |+------------------------+------------+-------------------+-------------------------+1row in set （0.00 sec） 5、锁表状态全备mysql数据由于退出当前mysql登陆窗口，锁表功能就失效，需克隆一个会话进行全备。#mysqldump-uroot -p -A -B >/tmp/mysql_bak_2014_10_30.sql.gz 看下备份数据大小，确认备份成功。#ls -l /tmp/mysql_bak_2014_10_30.sql.gz-rw-r--r--1 root root 339222 10月 3011:01 /tmp/mysql_bak_2014_10_30.sql.gz 6、解除锁表mysql>unlock tables;或直接quit退出即可。 7、从库开启bin-log功能，配置server-id从库开启bin-log功能后，待会在主上在配置同步，互为主从就完成了。#cat /etc/mysql/my.cnf[myqld]server-id = 2log_bin =/var/log/mysql/mysql-bin.logslave-net-timeout = 60
#salve-net-timeout默认是3600秒，缩短时间是为了防止双YES的假象
#（参考地址：http://www.cnblogs.com/billyxp/p/3470376.html） #/etc/init.d/mysqlrestart 8、确认从库bin-log与server-id是否开启查看命令 show variables like "log_bin"; show variables like "server_id";mysql>show variables like "log_bin";+---------------+-------+|Variable_name | Value |+---------------+-------+|log_bin | ON |+---------------+-------+1row in set （0.00 sec）mysql>show variables like "server_id";+---------------+-------+|Variable_name | Value |+---------------+-------+|server_id | 2 |+---------------+-------+1row in set （0.00 sec） 9、从库导入主库的全备数据在从库上解压数据。#gzip-d mysql_bak_2014_10_30.sql.gz登陆mysql导入数据mysql>source /root/mysql_bak_2014_10_30.sql 10、记录从库bin-log信息因为在从库导入全备数据时，此时主库与从库的内容是一致的，但是bin-log位置不一定一致。mysql>show master status; #查看最新bin-log文件及位置+------------------------+------------+-------------------+-------------------------+|File |Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------------+------------+-------------------+-------------------------+|mysql-bin.000003 | 2328055 | | |+------------------------+------------+-------------------+-------------------------+1row in set （0.00 sec） 11、从库设置同步主库此处binlog文件与位置状态，是主库在步骤4锁表时show master status查看的位置状态。 CHANGE MASTER TO MASTER_HOST="10.0.0.2",MASTER_PORT=8306,MASTER_USER="replication",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=26314; 12、开启从库同步并确认同步是否成功使用start slave开启同步功能，使用show slave statusG查看同步是否成功mysql>start slave;QueryOK, 0 rows affected （0.00 sec）mysql>show slave statusG #G不按表格输出***************************1. row *************************** Slave_IO_State: Waiting formaster to send event Master_Host: 10.0.0.2 Master_User: replication Master_Port: 8306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 136270 Relay_Log_File:mysqld-relay-bin.000002 Relay_Log_Pos: 72697 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB: 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: 98758 Relay_Log_Space: 110366 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: 622 #查看主从同步延迟，延迟大则可能需要优化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: 11row in set （0.00 sec） #sql线程与IO线程都是YES，slave配置成功。 13、主库设置同步从库由于从库是全备导入，原先在主库上配置的复制帐户也同样导入，所以这里不用在从库上新授权复制用户。从库上的binlog文件与位置状态，是从库在刚导入时show master status查看到的位置状态。 CHANGEMASTER TO MASTER_HOST="172.16.0.2",MASTER_PORT=3306,MASTER_USER="replication",MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000003",MASTER_LOG_POS=2328055;#修改相应信息，直接把这些配置在mysql中粘贴即可。 14、开启同步并确认同步是否成功使用start slave开启同步功能，使用show slave statusG查看同步是否成功mysql>start slave;QueryOK, 0 rows affected （0.00 sec） mysql>show slave statusG***************************1. row *************************** Slave_IO_State: Waiting formaster to send event Master_Host: 172.16.0.2 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 107 Relay_Log_File:mysqld-relay-bin.000006 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running:Yes Replicate_Do_DB: 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: 107 Relay_Log_Space: 556 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: 0Master_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: 21row in set （0.00 sec） #IO线程与sql线程都是正常。 15、互为主从测试在两台mysql各创建一个库，看两边是否都能进行同步。分别在主库上执行 create database test01;从库上执行create database test02;看两台数据库上执行show databases;，看是否都有test01表和test02表。我的经过测试，双主测试成功。生产环境MySQL主主同步主键冲突处理 http://www.linuxidc.com/Linux/2013-07/86890.htmUbuntu下Nginx做负载实现高性能WEB服务器5---MySQL主主同步 http://www.linuxidc.com/Linux/2012-06/61687p5.htmMySQL 主主同步配置 http://www.linuxidc.com/Linux/2013-05/83815.htm本文永久更新链接地址