配置mysql數據庫主從復制

一、    原理說明

復制有以下三個步驟：

(a)主服務器把數據更改記錄到二進制日志中。（這叫做二進制日志事件（binary log events）。）

(b)從服務器把主服務器的二進制日志事件拷貝到自己的中繼日志（relay log）中。

(c)從服務器重放中繼日志中的事件，把更改用到自己的數據上。

第一步：在主服務器上記錄二進制日志。在每個更新數據的事務完成之前，主服務器都會把數據更改記錄到二進制日志中。即使事務在執行期間是交錯的，mysql也會串行地把事務寫入到二進制日志中。在把事件寫入到二進制日志之后，主服務器告訴存儲引擎提交事務。

第二步：從服務器把主服務器的二進制日志拷貝到自己硬盤上，進入所謂的“中繼日志（relay log）中。

它首先啟動一個工作線程，叫I/O線程。這個I/O線程開啟一個普通的客戶端連接，然后啟動一個特殊的二進制日志轉儲進程。這個轉儲進程從主服務器的二進制日志中讀取事件。它不會對事件進行輪詢。如果它跟上了主服務器，就會進入休眠狀態，并等待有新事件發生時主服務器發出的信號。I/O線程把事件寫入從服務器的中繼日志中。

第三步：SQL從線程處理了該過程的最后一部分。該線程讀取了中繼日志，并且重放其中的事件，然后更新從服務器的數據。由于這個線程能跟上I/O線程，所以，中繼日志一般都是在操作系統的緩存中，因此中繼日志的開銷很低。同時SQL線程執行的事件也可以被寫入從服務器自己的二進制日志中。

二、    結構說明

l  2服務器mysql版本一致，初始化表，并在后臺啟動mysql

l  前提：關閉防火墻和selinux

l  可以實現基于實例級，數據庫級，表級別的復制， 本實驗的是實例級主從復制

l  復制優勢：無需共享存儲，通過網絡傳輸變化的數據到從庫

三、    修改主服務器master:

   #vi /etc/my.cnf   //增加以下參數

       [mysqld]

       log-bin=mysql-bin   //[必須]啟用二進制日志

       server-id=103      //[必須]服務器唯一ID，默認是1，一般取IP最后一段

四、    修改從服務器slave:

   #vi /etc/my.cnf

       [mysqld]

       log-bin=mysql-bin   //[不是必須]啟用二進制日志

       server-id=104      //[必須]服務器唯一ID，默認是1，一般取IP最后一段

五、    重啟兩臺服務器的mysql

   /etc/init.d/mysql restart

六、    在主服務器上建立帳戶并授權slave:

 create user 'rep1'@'192.168.0.%' identified by 'rep1234';

GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.0.%';

//一般不用root帳號，“%”表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強安全

該用戶信息不會復制到從庫上

七、    登錄主服務器的mysql，查詢master的狀態

>show master status;

注：執行完此步驟后不要再對主服務器表進行修改，防止主服務器狀態值變化

八、    從服務器配置Slave：

>change master to master_host='192.168.0.103',master_port=3306,master_user='rep1',master_password='rep1234',master_log_file='mysql-bin.000001',master_log_pos=350;   //注意不要斷開，350數字前后無單引號。

   Mysql>start slave;    //啟動從服務器復制功能

九、    檢查從服務器復制功能狀態：

   mysql> show slave status\G

注：Slave_IO及Slave_SQL進程必須正常運行，即YES狀態，否則都是錯誤的狀態(如：其中一個NO均屬錯誤)。

Master端查看：

十、    主從服務器測試：

主服務器Mysql，建立數據庫，并在這個庫中建表插入一條數據：

  mysql> create database hi_db;

  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;

  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));

  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into hi_tb values(001,'bobu');

  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;

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

   | Database           |

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

   | information_schema |

   | hi_db                |

   | mysql                |

   | test                 |

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

   4 rows in set (0.00 sec)

從服務器Mysql查詢：

   mysql> show databases;

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

   | Database               |

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

   | information_schema |

   | hi_db                 |       //I'M here，大家看到了吧

   | mysql                 |

   | test          |

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

   4 rows in set (0.00 sec)

   mysql> use hi_db

   Database changed

   mysql> select * from hi_tb;           //查看主服務器上新增的具體數據

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

   | id   | name |

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

   |    1 | bobu |

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

   1 row in set (0.00 sec)

十一、      主從手動切換

1)         從庫 停止 IO_THREAD 線程

mysql> stop slave IO_THREAD;

2)         激活從庫

root@localhost:francs>stop slave;

root@localhost:francs>reset master;

root@localhost:francs>reset slave;

mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       106 |

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

1 row in set (0.00 sec)

3)         重啟從庫

[root@mysqlslave ~]# /etc/init.d/mysqld restart

mysql> show slave status\G

Empty set (0.00 sec)

mysql> show master status\G

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

            File: mysql-bin.000002

        Position: 360

    Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4)         從庫創建復制用戶

create user 'rep2'@'192.168.0.%' identified by 'rep1234';

GRANT REPLICATION SLAVE ON *.* TO 'rep2'@'192.168.0.%';

//一般不用root帳號，“%”表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強安全

5)         原主庫103上操作

將原來主庫變為從庫

  CHANGE MASTER TO

   MASTER_HOST='192.168.0.104',

   MASTER_PORT=3306,

   MASTER_USER='rep2',

   MASTER_PASSWORD='rep1234',

   MASTER_LOG_FILE='mysql-bin.000002',

   MASTER_LOG_POS=360;

mysql> start slave;

mysql> show slave status\G

6)         復制數據測試

在104原從庫現主庫上插入數據

在103原主庫現從庫上查詢

十二、      主從回切

目的：將之前的手動切換再回切

103回切回主

104回切回從

1)         103停止從庫IO線程

mysql> stop slave IO_THREAD;

mysql> show slave status\G

2)         激活103從庫

root@localhost:francs>stop slave;

root@localhost:francs>reset master;

root@localhost:francs>reset slave;

mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       106 |

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

1 row in set (0.00 sec)

3)         重啟103從庫和查詢

[root@mysqlserver ~]# /etc/init.d/mysqld restart

mysql> show slave status\G

Empty set (0.00 sec)

mysql> show master status\G

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

            File: mysql-bin.000002

        Position: 106

    Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4)         104上操作

將原來主庫變為從庫

  CHANGE MASTER TO

   MASTER_HOST='192.168.0.103',

   MASTER_PORT=3306,

   MASTER_USER='rep1',

   MASTER_PASSWORD='rep1234',

   MASTER_LOG_FILE='mysql-bin.000002',

   MASTER_LOG_POS=106;

mysql> start slave;

mysql> show slave status\G

5)         測試

在103上更新記錄

mysql> insert into hi_tb values ('5','eee');

Query OK, 1 row affected (0.00 sec)

104上查詢

十三、      主從宕機測試

1)         模擬主宕機

103宕機

104查看

2)         103恢復

復制恢復

3)         104宕機

103主庫不受影響

4)         104恢復

啟動104庫，復制恢復