程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql互為主從復制詳解----mysql-mm

mysql互為主從復制詳解----mysql-mm

編輯：MySQL綜合教程

原理講述：
AB復制主要是通過兩個slave進程（Sql和I/O進程）和Master的I/O進程完成的
復制過程主要是Slave從Master端獲取該日志然後再在自己身上完全順序的執行日志中所記錄的各種操作
復制過程三部曲：
1）Slave啟動I/O進程連接Master，並請求從指定日志文件的指定位置（或者從最開始的日志）之後的日志內容
2）Master接到請求後通過負責復制的IO進程將Master端的bin-log文件的名稱 bin-log的位置以及日志信息返回給Slave
3）Slave收到信息後將接收到的日志內容依次添加到Slave端的relay-log文件的最末端，並將讀取到的Master端的
   bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從
   某個bin-log的哪個位置開始往後的日志內容，請發給我”
   Slave的Sql進程檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候
   的那些可執行的內容，並在自身執行

環境描述：最好兩台機器的mysql版本完全相同
A:211.100.97.246 Linux x86_64 mysql5.1.56
B:211.100.97.250 Linux x86_64 mysql5.1.56

啟動mysql進程
A 和 B均啟動mysql進程

修改安全級別
關閉selinux，iptables允許兩台機器之間的mysql端口互連
可以在/etc/sysconfig/selinux中設置參數selinux= disabled。
添加iptables -A INPUT -s SourceIP -p tcp --dport 3306 -j ACCEPT
修改完測試一下端口：
A： telnet B_IP 3306
B: telnet A_IP 3306

創建賬戶
A: useradd repl1
B: useradd repl2
添加完查看賬戶信息
A: id repl1
B: id repl2

A:mysql配置文件
user=mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

B：mysql配置文件
user=mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

說明：
server-id表示本機的序列號，如果為1的話一般代表master
binlog-do-db 表示需要備份哪個數據庫，如果要備份多個數據庫，應該添加多條記錄
replicate-do-db 表示要同步的那個數據庫
log-bin 表示開啟binlog日志功能，打開該選項才可以通過I/O進程將mater上的日志信息寫入到Slave的relay-log
auto_increment_increment定義下一次AUTO_INCREMENT的步長
auto_increment_offset 定義AUTO_INCREMENT的起點值

授權用戶【至少賦予FILE,SELECT,REPLICATION SLAVE權限】
A:允許B通過repl2賬戶與A同步數據
mysql> grant replication client on *.* to 'repl2'@'B_IP' identified by 'PASSWD';
mysql> flush privileges;
查看一下授權情況：
mysql> select * from mysql.user where host='repl1'@'B_IP'\G ;
*************************** 1. row ***************************
                 Host: 211.100.97.250
                 User: repl2
             Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)

B:允許A通過repl1賬戶與B同步數據
mysql> grant replication client on *.* to 'repl1'@'A_IP' identified by 'PASSWD';
mysql> flush privileges;
mysql> select * from mysql.user where host='repl1'@'A_IP'\G;
*************************** 1. row ***************************
                 Host: 211.100.97.246
                 User: repl1
             Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
授權以後需要測試
A: /usr/local/mysql/bin/mysql -h'B_IP' -urepl1 -p
B: /usr/local/mysql/bin/mysql -h'A_IP' -urepl2 -p

兩台機器上均重啟mysql
killall mysqld
ps aux |grep mysql
/usr/local/mysql/bin/mysqld_safe &
ps aux |grep mysql

進入MYSQL的SHELL
/usr/local/mysql/bin/mysql -uroot -p

A：
服務器鎖表（鎖表狀態下不能終止mysql進程，否則會失敗）
mysql> flush tables with read lock\G;
Query OK, 0 rows affected (0.01 sec)
----------------

查看 A 服務器主機狀態（記錄二進制開始文件，位置）
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000005
        Position: 106
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
----------------

修改A服務器配置
mysql> change master to
    -> master_host='211.100.97.250',
    -> master_user='repl2',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000014',
    -> master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)

說明：
master_host表示主機B(250)是A(246)的master
master_user表示允許A(246)上的賬戶repl1連接到master進行復制，建議兩台主機的授權用戶和密碼完全相同。
master_password 表示授權用戶repl1的密碼
master_log_file 表示master上日志文件的名稱
master_log_pos 表示日志文件的位置
----------------
mysql> slave stop;
mysql> change master to master_host='B_IP', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;

然後啟動slave
mysql> slave start;

啟動之後查看slave的狀態
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 211.100.97.250
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: XKWB5510-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 106
              Relay_Log_Space: 409
              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:
1 row in set (0.00 sec)

查看相關進程
mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 18
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 100
State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 19
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 100
State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 4. row ***************************
     Id: 21
   User: repl2
   Host: 211.100.97.250:34536
     db: NULL
Command: Binlog Dump
   Time: 19
State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
4 rows in set (0.00 sec)
----------------------
同步兩個數據庫的基礎庫

----------------
解鎖服務器
mysql> unlock tables;
----------------
mysql> use test;
mysql> show tables;
Empty set (0.00 sec)
----------------
mysql> create table t11_replicas
-> (id int not null auto_increment primary key,
-> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11_replicas(str) values
-> ('This is a master to master test table');
Query OK, 1 row affected (0.00 sec)
----------------
mysql> show tables;
----------------
mysql> select * from t11_replicas;

------------------------------------------------------------------------------------------------
B：
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000014
        Position: 98
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
----------------
mysql> stop slave;
mysql> change master to master_host='A_IP', master_user='repl2', master_password='123456', master_log_file='mysql-bin.000005',master_log_pos=106;
mysql> start slave;
----------------
mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 15
   User: repl1
   Host: 211.100.97.246:51840
     db: NULL
Command: Binlog Dump
   Time: 101
State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 3. row ***************************
     Id: 16
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 20
State: Waiting for master to send event
   Info: NULL
*************************** 4. row ***************************
     Id: 17
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 20
State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
4 rows in set (0.00 sec)
----------------
mysql> show slave status\G;
----------------
mysql> use test;
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

----------------
重置日志
mysql> reset master;
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 106
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
[root@XKWB5510 ~]# ls -l /var/mysql/database/data/
total 0

查看data目錄下是否有報錯文件：
[root@XKWB5510 data]# ls /var/mysql/database/data/

重新執行change master to 命令以後，再啟動slave，再看一下slave的狀態，I/O進程起來了

進程都起來之後，就實施監控
-------------------------------------------------------------------------------------------------------------
報錯：
1）
change master導致的：
Last_IO_Error: error connecting to master 'repl1@A_IP:3306' - retry-time: 60 retries

2）
在沒有解鎖的情況下停止slave進程：
mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

3）
change master語法錯誤，落下逗號
mysql> change master to
    -> master_host='211.100.97.250'
    -> master_user='repl2',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=106;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_user='repl2',
master_password='123456',
master_log_file='mysql-bin.000002' at line 3

4）
在沒有停止slave進程的情況下change master
mysql> change master to master_host='211.100.97.246', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=106;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

5）
A B的server-id相同：
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
mysql> show variables like 'server_id';
手動修改server-id
mysql> set global server_id=2; #此處的數值和my.cnf裡設置的一樣就行
mysql> slave start;

6）change master之後，查看slave的狀態，發現slave_IO_running 為NO
需要注意的是，做完上述操作之後最後重啟mysql進程
---------------------------------------
同步數據情況
A：在A上插入數據
mysql> create table aniya (id int not null auto_increment primary key, str varchar(255) not null);
mysql> insert into aniya(str) values
-> ('This is a master to master test table');

mysql> select * from aniya;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)

查看B的日志：
[root@XKWB5705 var]# ls -lrth XKWB5705-relay-bin.000003
-rw-rw---- 1 mysql mysql 576 Sep 26 12:29 XKWB5705-relay-bin.000003
[root@XKWB5705 var]# more XKWB5705-relay-bin.000003
.in.N

(id int not null auto_increment primary key,
str varchar(255) not null)3
('This is a master to master test table')

-----
B A主從同步測試

在B上創建表lian，並插入數據
mysql> create table lian (a int,b char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lian (a,b)values(22,hahah);
ERROR 1054 (42S22): Unknown column 'hahah' in 'field list'
mysql> insert into lian (a,b)values(22,'hahah');
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian |
+----------------+
1 row in set (0.00 sec)

mysql> select * from lian;
+------+-------+
| a | b |
+------+-------+
| 22 | hahah |
+------+-------+
1 row in set (0.00 sec)

查看一下B的master日志，證明以上操作成功：
cat mysql-bin.000002
.?Nh?@stdtestcreate table lian (a int,b char(10))??Nl>@stdtestinsert into lian (a,b)values(22,'hahah')

現在查看從服務器A的relay日志，發現日志已經同步了
[root@XKWB5510 var]# cat XKWB5510-relay-bin.000003
.?Nh?@stdtestcreate table lian (a int,b char(10))??Nl>@stdtestinsert into lian (a,b)values(22,'hahah')

現在說明數據B A 主從同步成功
---------------------------------------------------------------------------
測試A B主從

mysql> create table From246(Name varchar(255),Sex varchar(255),Age int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24);
Query OK, 1 row affected (0.00 sec)

mysql> select * from From246;
+--------+------+------+
| Name | Sex | Age |
+--------+------+------+
| Zhaoyj | Girl | 24 |
+--------+------+------+
1 row in set (0.00 sec)

查看A的master日志,證明上述操作成功
[root@XKWB5510 var]# tail -1 mysql-bin.000002
testcreate table From246(Name varchar(255),Sex varchar(255),Age int(10))?N?R@stdtestinsert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)

查看A的master日志狀態
[root@XKWB5510 var]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 |tail -15
/*!*/;
# at 702
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016911/*!*/;
create table From246(Name varchar(255),Sex varchar(255),Age int(10))
/*!*/;
# at 838
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016925/*!*/;
insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

查看B的relay日志，同步日志成功
testcreate table From246(Name varchar(255),Sex varchar(255),Age int(10))?N?R@stdtestinsert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)[root@XKWB5705 var]

查看B的relay日志狀態
[root@XKWB5705 var]# /usr/local/mysql/bin/mysqlbinlog XKWB5705-relay-bin.000005|tail -13
/usr/local/mysql/bin/mysqlbinlog: Character set '#28' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index.xml' file
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016911/*!*/;
create table From246(Name varchar(255),Sex varchar(255),Age int(10))
/*!*/;
# at 853
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016925/*!*/;
insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

但是數據卻沒有插入數據庫
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian |
+----------------+
1 row in set (0.00 sec)

當我刪除A上的表時，B的relay日志也同步了
[root@XKWB5705 var]# tail -4 XKWB5705-relay-bin.000005
??NS?@stdtestdrop table A246??NT@stdtestdrop table aniya??NSd@stdtestdrop table lian??NV?@stdtestdrop table From246

------------------------------------------------------------------------------
問題排查：

首先在Master上用

show processlist; 查看下進程是否Sleep太多。發現很正常。

show master status; 也正常。

再跑到Slave上查看也正常

show slave status;

發現一個問題：
當我手動從A導入B數據時
mysql> load table From246 from master;
ERROR 1115 (42000): Unknown character set: 'gbk'

那現在應該是在啟動mysql的時候統一他們的字符集
A :[root@XKWB5510 var]# /usr/local/mysql/bin/mysqld_safe --default-character-set=latin1 &
B :[root@XKWB5705 var]# /usr/local/mysql/bin/mysqld_safe --default-character-set=latin1 &

在B上從A導入數據：
mysql> show tables;
Empty set (0.00 sec)

mysql> load table From246 from master;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| From246 |
+----------------+
1 row in set (0.00 sec)

現在字符集的問題解決了

-----------------------------------
現在手動啟動一下“將日志應用於數據庫”的線程：SLAVE start SQL_THREAD
和“把master段的日志寫到本地”的線程：SLAVE start IO_THREAD

發現同步數據還是失敗，那說明不是線程的問題

如果發現 Seconds_Behind_Master 為 (null)
解決：
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之後Slave會和Master去同步主要看Seconds_Behind_Master是否為0，直到為0時就已經同步了。。

-----------------------------------
slave B機器上master.info信息，與master A上的信息是否是同步的

mater A：
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000004
        Position: 808
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

slave B：
[root@XKWB5705 var]# cat master.info
15
mysql-bin.000004
808
211.100.97.246
repl2
123456
3306
60
0

從以上可以看到是同步的

--------------------------------------------

flush master
flush slave

摘自：ANLJF的專欄