重做MySQL主從同步

利用xtrabackup備份恢復(fù)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
創(chuàng)建同步用戶并授權(quán)
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)
創(chuàng)建備份目錄
# 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 
// 打包壓縮備份文件，并傳給從服務(wù)器
# tar zcf sql_backup.tar.gz 20170414/
# scp sql_backup.tar.gz 192.168.1.59:~
查看二進(jìn)制日志文件及位置
# cd /root/mysql_backup/20170414
# vim xtrabackup_info
11 binlog_pos = filename 'mysql-bin.000001', position 120

二、在從庫上操作

1、先停止數(shù)據(jù)庫
[root@web2 ~]# service mysqld stop
Shutting down MySQL. SUCCESS!
2、配置my.cnf
log-bin = mysql-bin
server-id   = 2
3、恢復(fù)數(shù)據(jù)
# 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數(shù)據(jù)目錄下的文件權(quán)限，否則數(shù)據(jù)庫啟動不了
# chown -R mysql.mysql /usr/local/mysql/var
// 啟動數(shù)據(jù)庫
# 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狀態(tài)
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)

注意：重點關(guān)注 Slave_IO_Running: Yes和Slave_SQL_Running: Yes狀態(tài)是否為yes.都為yes表明主從同步正常；關(guān)注Seconds_Behind_Master: 0這個參數(shù)的值，這個值為0表示主從同步?jīng)]有延遲。關(guān)于Seconds_Behind_Master:參數(shù)的詳細(xì)解釋,請點擊查看。

  • 至此，主從同步已經(jīng)搭建完成，可以創(chuàng)建一些庫，然后再刪除之，來驗證主從同步情況。

三、用mysqldump備份恢復(fù)，做主從復(fù)制

1、登錄主庫鎖表

mysql> flush tables with read lock；
// 查看主庫狀態(tài)
mysql> show master status;
+------------------+-----------+
| File             | Position  | 
+------------------+-----------+
| mysql-bin.000106 | 452283357 |
+------------------+-----------+

2、備份全庫

# mysqldump --all-databases > full_20170415.sql
// 登入主將表解鎖
mysql> unlock tables;
// 將備份的數(shù)據(jù)傳給從庫服務(wù)器
# scp full_20170415.sql dh-db-r:/root

3、從庫有數(shù)據(jù)時，先刪除mysql數(shù)據(jù)目錄下所有文件刪除，然后初始化從庫

# 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、導(dǎo)入數(shù)據(jù)

# 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狀態(tài)

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數(shù)據(jù)庫數(shù)據(jù)同步完成，查看user表里所有的用戶都是從master同步過來的，雖然看起來這些用戶都存在，但是這些用戶都無法登錄slave數(shù)據(jù)庫
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數(shù)據(jù)庫是明明存在dh_application@10.0.1.%這個用戶，但是用這個用戶登錄，卻出現(xiàn)如下錯誤：
[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上重新做授權(quán)：
mysql> grant all privileges on *.* to 'dh_application'@'10.0.1.%' identified by 'Eqx0Ls75RBN9jbfi';
Query OK, 0 rows affected (0.01 sec)

重新授權(quán)后，就可以正常登錄了。

  • 通過這個現(xiàn)象，最終發(fā)現(xiàn)，在slave上修改授權(quán)表，不會影響主從同步的進(jìn)行。

四、MySQL同步故障：" Slave_SQL_Running:No" 兩種解決辦法

進(jìn)入slave服務(wù)器，運行：

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上進(jìn)行了寫操作
  • 2.也可能是slave機(jī)器重起后，事務(wù)回滾造成的.

一般是事務(wù)回滾造成的：
解決辦法：

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

解決辦法二、

首先停掉Slave服務(wù)：mysql> slave stop

到主服務(wù)器上查看主機(jī)狀態(tài)：
記錄File和Position對應(yīng)的值

進(jìn)入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服務(wù)器上執(zhí)行手動同步：

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的寫操作！可以登入主數(shù)據(jù)庫，用以下命令操作：
mysql> flush tables with read lock；
  • 解鎖
mysql> unlock tables;

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

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容