Mysql（Mariadb）資料庫主從複製

Mysql 主從複製的實現原理圖大致如下：

 

MySQL 之間資料複製的基礎是 以 二進位制日誌檔案（binary log file） 來實現的 ，一臺MySQL資料庫一旦啟用二進位制日誌後，其作為master，它資料庫中所有操作都會以“事件”的方式記錄在二進位制日誌中，其他資料庫作為slave透過一個I/O執行緒與主伺服器保持通訊，並監控master的二進位制日誌檔案的變化，如果發現master二進位制日誌檔案發生變化，則會把變化複製到自己的中繼日誌中，然後slave的一個SQL執行緒會把相關的“事件”執行到自己的資料庫中，以此實現從資料庫和主資料庫的一致性，也就實現了主從複製。MySQL(MariaDB)具體詳細的安裝可以參考 教程的第十八章節，裡面內容寫的非常詳細，適合初學者，本文也比較適合企業應用。

 

實現MySQL主從複製配置 要求 ：

主伺服器： 1、 開啟 資料庫 二進位制日誌 功能；2、 配置 資料庫認證 唯一 服務id ；3、 獲得 主庫的 二進位制日誌檔名及位置 ； 4、在主庫上面建立一個用於主庫和從庫通訊的使用者賬號 ，安全管理。

從伺服器： 1、在從庫中配置唯一 服務id；2、使用主庫建立分配的使用者賬號讀取主庫的二進位制日誌 ；3、啟用slave功能，用於主從通訊。

 

一、準備工作：

1. 主從資料庫版本最好一致 ；

2. 主從資料庫內資料保持一致 ；

主資料庫（master）：192.168.3.91    /CentOS Linux release 7.5.1804 (Core)

從資料庫（ slave ） ：192.168.3.218   /CentOS release 7.5.1804 (Core)

 

注意： 這裡的主從都是透過yum源安裝的mariadb 5.5.56；

# yum install mariadb-server.x86_64 mariadb.x86_64  -y

 

//設定mariadb服務

# systemctl start mariadb.service &&  systemctl enable mariadb.service

 

//設定mariadb資料庫root賬號的密碼，預設root使用者是沒有密碼；

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

 

In order to log into MariaDB to secure it, we'll need the current

password for the root user.  If you've just installed MariaDB, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] y

 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n

 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] n

 ... skipping.

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

 

Reload privilege tables now? [Y/n] y

 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

 

二、主資料庫master修改：

1. 修改mysql配置

找到主資料庫的配置檔案 my.cnf (或者my.ini)，我的在/etc/my.cnf,在[mysqld]部分插入如下兩行：

# find / -name my.cnf

預設配置

[mysqld] log - bin = mysql - bin # 開啟二進位制日誌 server - id = 1 #設定server - id

  log-bin="/var/lib/mysql/"  #設定生成的log檔名； 

修改後：

# systemctl restart mariadb.service

 

2. 重啟mysql，建立用於同步的使用者賬號

# mysql -hlocalhost -uroot -ppassword

建立使用者並授權：使用者：wxp,密碼：password

MariaDB [(none)] > CREATE USER 'wxp' @ '192.168.3.218' IDENTIFIED BY 'password' ;# 建立使用者

MariaDB [(none)] > GRANT REPLICATION SLAVE ON * . * TO 'wxp' @ '192.168.3.218' ;# 分配許可權

 MariaDB [(none)] > flush privileges ;    # 重新整理許可權

 

3. 檢視master狀態，記錄二進位制檔名(mysql-bin.000001)和位置(492)：

MariaDB [(none)]> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      492 |              |                  |

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

1 row in set (0.00 sec)

 

二、從伺服器slave修改：

1. 修改mysql配置

同樣找到my.cnf配置檔案，新增server-id

# find / -name my.cnf

my.cnf預設配置

[mysqld] server - id = 2            # 設定server - id，必須唯一

  log-bin="/var/lib/mysql/"  #設定生成的log檔名； 

修改後：

# systemctl restart mariadb.service

 

2. 重啟mysql，開啟mysql會話，執行同步SQL語句 ( 需要主伺服器主機名，登陸憑據，二進位制檔案的名稱和位置)：

# mysql -hlocalhost -uroot -ppassword

MariaDB [(none)] > CHANGE MASTER TO       -> MASTER_HOST = '192.168.3.91' ,     -> MASTER_USER = 'wxp' ,     -> MASTER_PASSWORD = 'password' ,     -> MASTER_LOG_FILE = 'mysql-bin.000001 ' ,     -> MASTER_LOG_POS = 492 ;

這裡是直接把資訊寫入到資料庫裡面，

 

mysql>  select  *  from  mysql.slave_master_info \G

 

3. 啟動slave同步程式：

MariaDB [(none)] > start slave;

 

4. 檢視slave狀態：

MariaDB [(none)] > show slave status\G;

MariaDB [(none)]> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.3.91

                  Master_User: wxp

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 492

               Relay_Log_File: mariadb-relay-bin.000002

                Relay_Log_Pos: 529

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

              Relay_Log_Space: 825

              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

1 row in set (0.00 sec)

ERROR: No query specified

 

當 Slave_IO_Running 和 Slave_SQL_Running 都為YES的時候就表示主從同步設定成功了。接下來就可以進行一些驗證了，比如在主master資料庫的test資料庫的一張表中插入一條資料，在slave的test庫的相同資料表中檢視是否有新增的資料即可驗證主從複製功能是否有效，還可以關閉slave（ MariaDB [(none)] >stop slave; ）,然後再修改master，看slave是否也相應修改（停止slave後，master的修改不會同步到slave），就可以完成主從複製功能的驗證了。

 

5、測試，操作Master資料庫

MariaDB [(none)]> use test;

Database changed

MariaDB [test]> create table t1(Name varchar(18));

Query OK, 0 rows affected (0.03 sec)

 

MariaDB [test]> insert into t1(Name) values('wxp');

Query OK, 1 row affected (0.01 sec)

 

MariaDB [test]> select * from t1;

+------+

| Name |

+------+

| wxp  |

+------+

1 row in set (0.00 sec)

 

在 slave 上面檢視test庫是否有資料同步過來；

[root@backup-3-218 ~]# mysql -hlocalhost -uroot -ppassword

MariaDB [(none)]> use test;

MariaDB [test]> show tables;

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

| Tables_in_test |

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

| t1             |

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

1 row in set (0.00 sec)

 

MariaDB [test]> select * from t1;

+------+

| Name |

+------+

| wxp  |

+------+

1 row in set (0.00 sec)

 

6 、還可以用到的其他相關引數：

master 開啟二進位制日誌後預設記錄所有庫所有表的操作，可以透過配置來指定只記錄指定的資料庫甚至指定的表的操作，具體在mysql配置檔案的[mysqld]可新增修改如下選項：

# 不同步哪些資料庫  

# vim /etc/my.cnf binlog - ignore - db = mysql   binlog - ignore - db = test   binlog - ignore - db = information_schema  

 

# systemctl restart mariadb.service

   # 只同步哪些資料庫，除此之外，其他不同步  binlog - do - db = wxp

 

# 日誌保留時間

expire_logs_days = 10

 

# 控制binlog的寫入頻率。每執行多少次事務寫入一次

# 這個引數效能消耗很大，但可減小MySQL崩潰造成的損失

sync_binlog = 5

 

# 日誌格式，建議mixed

# statement 儲存SQL語句

# row 儲存影響記錄資料

# mixed 前面兩種的結合

binlog_format = mixed

 

在slave資料庫上面操作，設定重新連線超時時間

# 停止主從同步

mysql> stop slave;

 

# 連線斷開時，重新連線超時時間

mysql> change master to master_connect_retry=50;

 

# 開啟主從同步

mysql> start slave;