Mysql主從複製

標籤：mysql主從

一、主從複製原理：

將Mysql的資料分布到多個系統上去，這種分布的機制，是通過將Mysql的某一台主機的資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。簡單的說：從伺服器的IO線程從主伺服器擷取二進位日誌，並在本地儲存為中繼日誌，然後通過SQL線程來在從上執行中繼日誌中的內容，從而使從庫和主庫保持一致。

A.master將改變記錄到二進位日誌(binary log)中（這些記錄叫做二進位日誌事件，binary log events）；

B.slave將master的binary log events拷貝到它的中繼日誌(relay log)；

C.slave重做中繼日誌中的事件，將改變反映它自己的資料。

1、Slave上面的IO進程串連上Master，並請求從指定記錄檔的指定位置（或者從最開始的日誌）之後的日誌內容； 

2、Master接收到來自Slave的IO進程的請求後，通過負責複製的IO進程根據請求資訊讀取制定日誌指定位置之後的日誌資訊，返回給Slave的IO進程。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置；

 

3、Slave的IO進程接收到資訊後，將接收到的日誌內容依次添加到Slave端的relay-log檔案的最末端，並將讀取到的Master端的bin-log的檔案名稱和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master“需要從bin-log的哪個位置開始往後的日誌內容，然後給Slave”；

4、Slave的Sql進程檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行檔內容，並在自身執行。

5、mysql的 replication 是一個非同步複製過程，從master複製到slave。在 master 與 slave之間的實現整個複製過程主要由三個線程來完成，其中兩個線程(Sql線程和IO線程)在 slave 端，另外一個線程(IO線程)在 master端。

6、關鍵點：負責在主、從伺服器傳輸各種修改動作的媒介是主伺服器的二進位變更日誌，這個日誌記載著需要傳輸給從伺服器的各種修改動作。因此，主伺服器必須啟用二進位日誌功能。從伺服器必須具備足以讓它串連主伺服器並請求主伺服器把二進位變更日誌傳輸給它的許可權。

二、環境描述：

Master的IP：192.168.44.151

Slave的IP：192.168.44.152

三、主伺服器配置：

1、修改設定檔，添加如下內容

vi /etc/my.cnf 

server-id=1

log-bin=mysql-bin

relay-log=mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2、重啟服務，使設定檔生效

/etc/init.d/mysqld restart 

3、登入主伺服器

mysql -uroot -p

4、建立同步處理的使用者bob，授權同步許可權給從伺服器192.168.44.152

mysql> create user ‘bob‘@‘192.168.44.152‘ identified by ‘123456‘; 

mysql>grant replication slave on *.* to ‘bob‘@‘192.168.44.152‘ identified by ‘123456‘;   

5、查看主伺服器狀態

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      106 |              | MySQL            | 

+------------------+----------+--------------+------------------+

四、從伺服器配置：

1、修改設定檔，添加如下內容

vi /etc/my.cnf

server-id=2

log-bin=mysql-bin

relay-log=mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

2、重啟服務，使設定檔生效

/etc/init.d/mysqld restart

3、配置從伺服器

mysql> change master to master_host=‘192.168.44.151‘,master_user=‘bob‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=106;

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first //報錯了

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> change master to master_host=‘192.168.44.151‘,master_user=‘bob‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=106;

Query OK, 0 rows affected (0.06 sec)

 

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: 

                  Master_Host: 192.168.44.151

                  Master_User: bob

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 106

               Relay_Log_File: B-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB: test_1

          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: 106

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1130

                Last_IO_Error: error connecting to master ‘[email protected]:3306‘ - retry-time: 60  retries: 86400

               Last_SQL_Errno: 0

               Last_SQL_Error: 

1 row in set (0.00 sec)

4、啟動從伺服器複製功能 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.44.151

                  Master_User: bob

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 106

               Relay_Log_File: B-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: test_1

          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: 402

              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)

五、主從同步檢驗：

主伺服器建立庫test_1，從伺服器也會建立test_1

mysql> create database test_1;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| mysql              | 

| test               | 

| test_1             | 

+--------------------+

4 rows in set (0.00 sec)

 

mysql> use test_1;

Database changed

mysql>  create table bob(id int(1),name char(5));

Query OK, 0 rows affected (0.10 sec)

 

mysql>  insert into bob values (1,‘bob‘);

Query OK, 1 row affected (0.05 sec)

 

mysql> select * from bob;

+------+------+

| id   | name |

+------+------+

|    1 | bob  | 

+------+------+

1 row in set (0.00 sec)

六、主從不同步解決辦法

先到Master上看： 

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

show master status; 正常。 

mysql> show master status; 

再到Slave上查看 ：

mysql> show slave status\G 

Slave_IO_Running: Yes 

Slave_SQL_Running: No 

可見是Slave不同步 

方法一：忽略錯誤後，繼續同步 

該方法適用於主從庫資料相差不大，或者要求資料可以不完全統一的情況，資料要求不嚴格的情況 

mysql>stop slave; 

skip表示跳過一步錯誤，後面的數字可變 

mysql>set global sql_slave_skip_counter =1; 

mysql>start slave; 

mysql> show slave status\G  

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes 

方法二：完全同步，該方法適用於主從庫資料相差較大，或者要求資料完全統一的情況 

1、先進入master進行鎖表，防止資料寫入 

mysql> flush tables with read lock; 

2、然後將資料匯出，進行資料備份 

[[email protected]]#mysqldump -uroot -p -hlocalhost > mysqlbak.sql 

這裡注意一點：Database Backup一定要定期進行，可以用shell指令碼或者python指令碼，都比較方便，確保資料萬無一失 

3、查看master 狀態 

mysql> show master status; 

+-------------------+----------+--------------+-------------------------------+ 

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

+-------------------+----------+--------------+-------------------------------+ 

| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 

+-------------------+----------+--------------+-------------------------------+ 

1 row in set (0.00 sec)

 

4、把mysql備份檔案傳到從庫機器，進行資料恢複 

[[email protected]]# scp mysqlbak.sql [email protected]:/tmp/ 

5、停止從庫的狀態 

mysql> stop slave; 

6、然後到slave執行mysql命令，匯入資料備份 

mysql> source /tmp/mysqlbak.sql 

7、設定從庫同步，注意該處的同步點，就是主庫show master status資訊裡的| File| Position兩項 

change master to master_host = ‘192.168.128.100‘, master_user = ‘rsync‘, master_port=3306, master_password=‘‘, master_log_file = ‘mysqld-bin.000001‘, master_log_pos=3260; 

8、重新開啟從同步 

mysql> start slave; 

9、查看同步狀態 

mysql> show slave status\G  

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes 

10、解鎖

mysql> unlock tables;

本文出自 “卡卡西” 部落格，請務必保留此出處http://whnba.blog.51cto.com/1215711/1605720

Mysql主從複製