重做MySQL主从同步

利用xtrabackup备份恢复MySQL

[toc]

一、在主库上操作

安装xtrabackup

# yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
# yum -y install perl-DBD-mysql perl-DBI percona-xtrabackup-22 qpress

创建同步用户并授权

mysql> GRANT SELECT, RELOAD, FILE, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, PROCESS,SHOW VIEW ON *.* TO 'cgy'@'192.168.1.59' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

创建备份目录

# mkdir mysql_backup

开始全库备份

# innobackupex --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=root --password='123456' --port=3300 --slave-info --no-timestamp ~/mysql_backup/20170414 
// 打包压缩备份文件，并传给从服务器
# tar zcf sql_backup.tar.gz 20170414/
# scp sql_backup.tar.gz 192.168.1.59:~

查看二进制日志文件及位置

# cd /root/mysql_backup/20170414
# vim xtrabackup_info
11 binlog_pos = filename 'mysql-bin.000001', position 120

二、在从库上操作

1、先停止数据库

[root@web2 ~]# service mysqld stop
Shutting down MySQL. SUCCESS!

2、配置my.cnf

log-bin = mysql-bin
server-id   = 2

3、恢复数据

# tar xf ecshop_sql.tar.gz
# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log ~/20170414/
# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back ~/20170414/

// 修改mysql数据目录下的文件权限，否则数据库启动不了
# chown -R mysql.mysql /usr/local/mysql/var
// 启动数据库
# service mysqld start

4、登录从mysql，指定同步信息

# mysql -uroot -p123456
...
mysql> change master to
    -> master_host='192.168.1.16',
    -> master_user='cgy',
    -> master_password='123456',
    -> master_port=3300,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=120;
// 打开slave功能
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

// 查看slave状态
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.16
                  Master_User: cgy
                  Master_Port: 3300
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 630
               Relay_Log_File: web2-relay-bin.000002
                Relay_Log_Pos: 793
        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: 630
              Relay_Log_Space: 965
              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: 5328b9ac-0bf5-11e7-a62b-000c293d97b8
             Master_Info_File: /usr/local/mysql/var/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)

注意：重点关注 Slave_IO_Running: Yes和Slave_SQL_Running: Yes状态是否为yes.都为yes表明主从同步正常；关注Seconds_Behind_Master: 0这个参数的值，这个值为0表示主从同步没有延迟。关于Seconds_Behind_Master:参数的详细解释,请点击查看。

• 至此，主从同步已经搭建完成，可以创建一些库，然后再删除之，来验证主从同步情况。

三、用mysqldump备份恢复，做主从复制

1、登录主库锁表

mysql> flush tables with read lock；
// 查看主库状态
mysql> show master status;
+------------------+-----------+
| File             | Position  | 
+------------------+-----------+
| mysql-bin.000106 | 452283357 |
+------------------+-----------+

2、备份全库

# mysqldump --all-databases > full_20170415.sql
// 登入主将表解锁
mysql> unlock tables;
// 将备份的数据传给从库服务器
# scp full_20170415.sql dh-db-r:/root

• mysqldump的ERROR 1449的解决方法
  请点击此处查看解决方法

3、从库有数据时，先删除mysql数据目录下所有文件删除，然后初始化从库

# rm -rf /mysql_data
# /mysql_base/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/mysql_base/ --datadir=/mysql_data/ --user=mysql

注意：

• my.cnf文件中加入

log-bin = mysql-bin
server-id   = 2

4、导入数据

# mysqld_safe --user=mysql &
# mysql < full_20170415.sql

5、登录入从库，指定主库信息

mysql> 
mysql> change master to 
    -> master_host='10.0.1.90',
    -> master_port=6606,
    -> master_user='dh_backup',
    -> master_password='lYRpNiZQ5eTUeS2g',
    -> master_log_file='mysql-bin.000106',
    -> master_log_pos=452283357;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

开启slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看slave状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.90
                  Master_User: dh_backup
                  Master_Port: 6606
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000106
          Read_Master_Log_Pos: 494159826
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 4534696
        Relay_Master_Log_File: mysql-bin.000106
             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: 456817770
              Relay_Log_Space: 41876919
              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: 7256
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: 149
                  Master_UUID: f2cb2b4e-dbab-11e5-a5f7-c81f66de505f
             Master_Info_File: /mysql_data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: updating
           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)

6、注意事项

• slave数据库数据同步完成，查看user表里所有的用户都是从master同步过来的，虽然看起来这些用户都存在，但是这些用户都无法登录slave数据库

mysql> select user,password,host from user;
+----------------+-------------------------------------------+----------------+
| user           | password                                  | host           |
+----------------+-------------------------------------------+----------------+
| root           | *1CCC8892FF6EFB87CFC902BC52126A099580F5D3 | localhost      |
| user153        | *1CCC8892FF6EFB87CFC902BC52126A099580F5D3 | 192.168.66.53  |
| dh_backup      | *D87F998D5C584C9CAEB1E977418D35F7E828B910 | 10.0.1.91      |
| dh_check       | *5F354F59BAD68E2B9A520066F42BD27FC18A76FD | localhost      |
| dh_application | *C050D94BAFB51A83A4A7249FC7B244ACDA305DCA | 10.0.1.%       |
+----------------+-------------------------------------------+----------------+
8 rows in set (0.00 sec)

• 通过表中可以看出，slave数据库是明明存在dh_application@10.0.1.%这个用户，但是用这个用户登录，却出现如下错误：

[caigy@DH-A01-PHP01 ~]$ mysql -udh_application -pEqx0Ls75RBN9jbfi -h10.0.1.91 -P6607
ERROR 1130 (HY000): Host 'DH-PHP01' is not allowed to connect to this MySQL server
//注：DH-PHP01的IP是10.0.1.40

• 原因就是因为这个用户是从master同步过来的，是虚假的。要想使用这个用户登录，只能在slave上重新做授权：

mysql> grant all privileges on *.* to 'dh_application'@'10.0.1.%' identified by 'Eqx0Ls75RBN9jbfi';
Query OK, 0 rows affected (0.01 sec)

重新授权后，就可以正常登录了。

• 通过这个现象，最终发现，在slave上修改授权表，不会影响主从同步的进行。

四、MySQL同步故障：" Slave_SQL_Running:No" 两种解决办法

进入slave服务器，运行：

MySQL> show slave status\G
             ...
             Relay_Log_File: localhost-relay-bin.000535
              Relay_Log_Pos: 21795072
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB:
              ...

解决办法一、

Slave_SQL_Running: No

• 1.程序可能在slave上进行了写操作
• 2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二、

首先停掉Slave服务：mysql> slave stop

到主服务器上查看主机状态：
记录File和Position对应的值

进入master

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

然后到slave服务器上执行手动同步：

mysql> change master to 
    > master_host='master_ip',
    > master_user='user', 
    > master_password='pwd', 
    > master_port=3306, 
    > master_log_file=localhost-bin.000094', 
    > master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

• 注意：手动同步需要停止master的写操作！可以登入主数据库，用以下命令操作：

mysql> flush tables with read lock；

• 解锁

mysql> unlock tables;

http://blog.csdn.net/heng_ji/article/details/51013710