2019獨角獸企業重金招聘Python工程師標準>>>

Master-Slave Replication原理圖：

主從復制的原理：

分為同步復制和異步復制，實際復制架構中大部分為異步復制。 復制的基本過程如下：

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的哪個位置開始往后的日志內容，請發給我”；

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

為什么是MariaDB Replication

As you may know,?MariaDB?is a drop in replacement for MySQL. It is a robust, scalable and reliable SQL server that comes rich set of enhancements.

MariaDB replication?is a method to store multiple copies of data on many systems, and the data will automatically be copied from one database (Master) to another database (Slave). If one server goes down, the clients still can access the data from another (Slave) server database.

In this article, let us see how to configure MariaDB Master-Slave replication in CentOS 7. This method will work on all linux distributions, including?RHEL, CentOS, Ubuntu, and openSUSE etc.?All you need to know is how to install MariaDB in the specific distribution you use.

環境：

• MariaDB Master:?CentOS 7 64bit Minimal Server

• Master IP Address:?192.168.15.134/24

• MariaDB Slave:?CentOS 7 64bit Minimal Server

• Slave IP Address:?192.168.15.138/24

• 不同Linux版本，MariaDB安裝略有不同（Installing LAMP Stack in Linux）

Master與Slave均操作

安裝MariaDB：

yum install mariadb-server mariadb

啟動MariaDB并開機啟動：

systemctl start mariadb

systemctl enable mariadb

設置MariaDB root密碼：

mysql_secure_installation

配置Master MariaDB:

firewall-cmd --permanent --add-port=3306/tcp ?#放行3306端口

firewall-cmd --reload

vi /etc/my.cnf ?#添加如下至[mysqld]部分

[mysqld]

server-id=1

log-basename=master

log-bin

binlog-format=row

binlog-do-db=games

#binlog-ignore-db=test

[...]

#binlog-format binlog三種格式：row/statement/mixed

#binlog-do-db 要同步的數據庫，同步多個，重復設置即可

#games 為要復制到Slave的數據庫名稱

systemctl restart mariadb ?#重啟MariaDB

mysql -u root -p ?#登錄Master MariaDB

創建Slave用戶并設置密碼，獲取相應的Binlog及Pos信息供后續使用：

MariaDB [(none)]> STOP SLAVE;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'sk'@'%' IDENTIFIED BY 'centos';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> ?SHOW MASTER STATUS;

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

| File ? ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mariadb-bin.000001 | ? ? ?460 | games ? ? ? ?| ? ? ? ? ? ? ? ? ?|

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

1 row in set (0.00 sec)

MariaDB [(none)]> exit

Bye

備份Master MariaDB，關閉鎖表，并上傳備份masterdatabase.sql?到Slave

mysqldump --all-databases --user=root --password --master-data > masterdatabase.sql

mysql -u root -p ?#登錄Master MariaDB

MariaDB [(none)]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit

Bye

拷貝masterdatabase.sql至Slave的/home目錄。

scp masterdatabase.sql root@192.168.14.138:/home

配置Slave MariaDB:

firewall-cmd --permanent --add-port=3306/tcp ?#放行3306端口

firewall-cmd --reload

vi /etc/my.cnf ?#添加如下至[mysqld]部分

[mysqld]

server-id = 2

replicate-do-db=games

#replicate-ignore-db=games

[...]

導入Master數據：

mysql -u root -p < /home/masterdatabase.sql?

systemctl restart mariadb ?#重啟MariaDB

mysql -u root -p #登錄Slave MariaDB

MariaDB [(none)]> STOP SLAVE;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.15.134', MASTER_USER='sk', MASTER_PASSWORD='centos', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=460;

Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> SLAVE START;

Query OK, 0 rows affected (0.01 sec)

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

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

? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event

? ? ? ? ? ? ? ? ? Master_Host: 192.168.15.134

? ? ? ? ? ? ? ? ? Master_User: sk

? ? ? ? ? ? ? ? ? Master_Port: 3306

? ? ? ? ? ? ? ? Connect_Retry: 60

? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000001

? ? ? ? ? Read_Master_Log_Pos: 460

? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000002

? ? ? ? ? ? ? ? Relay_Log_Pos: 531

? ? ? ? Relay_Master_Log_File: mariadb-bin.000001

? ? ? ? ? ? ?Slave_IO_Running: Yes

? ? ? ? ? ? Slave_SQL_Running: Yes

? ? ? ? ? ? ? Replicate_Do_DB: games

? ? ? ? ? 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: 460

? ? ? ? ? ? ? Relay_Log_Space: 827

? ? ? ? ? ? ? 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)

注：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

保證以上兩個參數為Yes,否則檢查master/slave配置步驟

測試MariaDB復制：

mysql -u root -p

MariaDB [(none)]> create database games;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use games;

Database changed

MariaDB [games]> create table sample(c int);

Query OK, 0 rows affected (0.01 sec)

MariaDB [games]> insert into sample (c) values (1);

Query OK, 1 row affected (0.01 sec)

MariaDB [games]> select * from sample;

+------+

| c ? ?|

+------+

| ? ?1 |

+------+

1 row in set (0.00 sec)

MariaDB [games]>?

Slave MariaDB端檢測

mysql -u root -p

Then, run the following commands to verify whether the entries have been replicated correctly.

MariaDB [(none)]> show databases;

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

| Database ? ? ? ? ? |

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

| information_schema |

| games ? ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| performance_schema |

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

MariaDB [(none)]> use games;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [games]> select * from sample;

+------+

| c ? ?|

+------+

| ? ?1 |

+------+

1 row in set (0.00 sec)

MariaDB [games]>

類似article

Setup Master-Slave Replication in MySQL Server

轉載于:https://my.oschina.net/HeAlvin/blog/663471

總結

以上是生活随笔為你收集整理的CentOS 7下的MariaDB Master-Slave Replication配置的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。