[转载] mysql数据库主主同步实施方案实践

笔者根据业务系统高可用的实际需要并结合企业硬件设施的实际情况，为保障业务系统为用户提供不间断服务。在业务应用交付层采用了数据库实时同步技术，在系统层采用了heartbeat技术。本博文记录了笔者一步步实现mysql数据同步的操作步骤及其中遇到的问题和解决方法。
设置主主同步具体步骤如下所示（每个服务器各做一边）:
1.首先创建一个数据库为dbname
2.授权数据库指向特定的用户
grant all on fzh.* to myapp@'%' identified by '123abc'; #把数据库fzh授权给myapp账户
a)select user,host,password from mysql.user; #检查用户是否有密码
b)select user,length(user),host,length(host) from mysql.user;  #检查用户名后是否有空格
c)select current_user();#查看当前登陆是谁
以上三步解决创建用户后使用口令无法登陆的问题，如果博友们没有遇到无法使用创建的用户登陆的问题可不操作a、b及c步骤。
mysql>use mysql;
mysql>update user set password=password('123456') where user='myapp';
mysql>flush privileges;
最好把匿名用户也删除了
mysql>delete from user where user='';
正式设置主主同步
1.登陆mysql1创建数据库 create database mms_sd
2. grant replication slave on mms_sd.* to myapp@'10.0.211.14' identified by '123' #允许mysql2数据库服务器拷贝数据
3.登陆mysql2服务器重复以上操作
4.配置mysql1数据库的主配置文件my.cnf
添加内容如下所示：
Ø  Mysql1：
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=itwhhsol
binlog-ignore-db=mysql
#different
replicate-do-db=itwhhsol
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
Ø  mysql2：
[mysqld]
log-bin=mysql-bin
server-id=2
binlog-do-db=itwhhsol
binlog-ignore-db=mysql
#different
replicate-do-db=itwhhsol
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
5.导入数据
mysql -uroot -p mydb < /tmp/mydb.sql
6.锁表备份数据
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump --user=root -p mydb > /tmp/mydb.sql
解表unlock tables;
7.数据库同步指令
停止主主复制stop slave
调整主mysql
Show master statuss;#确认mysql数据库的状态并根据返回的状态设置同步信息
mysql> change master to master_host='10.0.211.13',\
    -> master_user='myapp',\
    -> master_password='fzhftp100',\
    -> master_log_file=' mysql-bin.000015',\
-> master_log_pos= 2565;
调整副mysql：
mysql> change master to master_host='10.0.211.14',\
    -> master_user='myapp',\
    -> master_password='fzhftp100',\
    -> master_log_file=' mysql-bin.000009',\
    -> master_log_pos=98;  
Query OK, 0 rows affected (0.00 sec)

最后确认是否设置成功
show master status \G
回显如下信息
mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.202.73
                Master_User: myapp
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000002
        Read_Master_Log_Pos: 3591
             Relay_Log_File: mysqld-relay-bin.000018
              Relay_Log_Pos: 1111
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: itwhhsol
        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: 3591
            Relay_Log_Space: 1111
            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
1 row in set (0.00 sec)
则表示配置成功
测试
Grant select,update,insert,delete on db.*  to myapp@’%’
通过navicat或者客户端连接后进行修改添加数据进行测试
运维记录
mysqldump -h 127.0.0.1 -u root -p  mms_sdmtv > mms.sql
#backupdb
mysql -u root -p mms_sdmtv< mms.sql
#restoredb
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED
BY ’password’ WITH GRANT OPTION;
grant all privileges on *.* to root@'%' identified by '' with grant option
flush privileges
revoke all privileges on *.* from root@'%';
fzhftp100
#myapp的认证密码

change master to
master_host='10.0.211.14',master_user='myapp',master_password='fzhftp100',master_log_file='mysql-bin.000014',master_log_pos=98;
#记住不日志文件不要有空格否则会同步失败
change master to
master_host='10.0.211.13',master_user='myapp',master_password='fzhftp100',master_log_file='mysql-bin.000017',master_log_pos=98;
#记住不日志文件不要有空格否则会同步失败
不小心删除bin日志导致主主同步失败可以用以下方法再次
Slave：stop slave;
Master:flush logs
Master: show master status;
Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
Slave: start slave;
亦可以用SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;