Mysql/Mariadb主從復(fù)制

頭號(hào)碼甲 2020-12-10

展開全文

概念

什么是·Mysql/Mariadb主從復(fù)制？　　　　

Mysql/Mariadb主從復(fù)制：當(dāng)Master（主）數(shù)據(jù)庫(kù)發(fā)生變化的時(shí)候，變化實(shí)時(shí)會(huì)同步到slave（從）數(shù)據(jù)庫(kù)中；
類似于：Samba共享文件（C/S）、NFS網(wǎng)絡(luò)文件共享（C/S），當(dāng)服務(wù)端（Server）發(fā)生變化時(shí)，客戶端（client）數(shù)據(jù)內(nèi)容會(huì)根據(jù)服務(wù)端進(jìn)行改變；

好處

水平擴(kuò)展數(shù)據(jù)庫(kù)的負(fù)載能力，后備數(shù)據(jù)庫(kù)，主數(shù)據(jù)庫(kù)服務(wù)器故障后，可切換到從數(shù)據(jù)庫(kù)繼續(xù)工作；
容錯(cuò)、高可用，從數(shù)據(jù)庫(kù)可用來做備份、數(shù)據(jù)統(tǒng)計(jì)等工作，這樣不影響主數(shù)據(jù)庫(kù)的性能；
數(shù)據(jù)分布；
數(shù)據(jù)備份；

實(shí)現(xiàn)原理

在master機(jī)器上，主從同步事件會(huì)被寫到特殊的log文件中(binary-log);

主從同步事件有3種形式:statement、row、mixed。

 statement：會(huì)將對(duì)數(shù)據(jù)庫(kù)操作的sql語句寫入到binlog中。
 row：會(huì)將每一條數(shù)據(jù)的變化寫入到binlog中。
 mixed：statement與row的混合。Mysql決定什么時(shí)候?qū)憇tatement格式的，什么時(shí)候?qū)憆ow格式的binlog。

整體上來說，復(fù)制有3個(gè)步驟：

master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events)；
slave將master的binary log events拷貝到它的中繼日志(relay log)；
slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。

下面這章圖可以詳細(xì)解釋其原理：

主從復(fù)制過程

說的簡(jiǎn)單一些就是：

當(dāng)對(duì)Master數(shù)據(jù)庫(kù)不管做了增、刪、改還是創(chuàng)建了數(shù)據(jù)庫(kù)、表等操作時(shí)，Slave就會(huì)快速的接受這些數(shù)據(jù)以及對(duì)象的操作，從而實(shí)現(xiàn)主從數(shù)據(jù)復(fù)制，保證數(shù)據(jù)的一致性?！　?/pre>

讀寫分離操作請(qǐng)查看：https://www.cnblogs.com/kuiyajia/p/11909757.html

實(shí)戰(zhàn)

我記得我學(xué)PHP開發(fā)的時(shí)候，教員經(jīng)常說的一句話就是：學(xué)習(xí)半小時(shí)，實(shí)戰(zhàn)一分鐘；
好了，接下來到我們實(shí)戰(zhàn)的時(shí)刻了，認(rèn)真聽講喲！??！

環(huán)境介紹

系統(tǒng)環(huán)境：系統(tǒng)基本上都差不多，一般多數(shù)都是Linux平臺(tái)和Windows平臺(tái)比較多，不管什么樣的系統(tǒng)環(huán)境對(duì)這次實(shí)戰(zhàn)的操作都影響不大，我在這里使用的是Docker虛擬出來的CentOS操作系統(tǒng)，當(dāng)然您可以選用Ubuntu、RedHat以及Windows系統(tǒng)，這些都不會(huì)影響到大的操作；

我這里使用的系統(tǒng)版本：

[root@master /]# cat /etc/redhat-release
CentOS Linux release 8.0.1905 (Core)

這里會(huì)用到兩臺(tái)服務(wù)器：其中一臺(tái)MasterIP172.18.0.2，另外一個(gè)slaveIP172.18.0.3
數(shù)據(jù)庫(kù)版本：（我這里使用的Mariadb，你可以使用mysql數(shù)據(jù)庫(kù)）

[root@master /]# mysql --version
mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1

配置Master數(shù)據(jù)庫(kù)

　　1.更改Master配置文件
　　　　找到下面文件：

mysql數(shù)據(jù)庫(kù)：/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb數(shù)據(jù)庫(kù)：/etc/my.cnf.d/mariadb.cnf

注意：我這里是使用yum進(jìn)行安裝的所以默認(rèn)配置文件是在/etc下面，建議在修改上面兩個(gè)文件時(shí)要先將配置文件進(jìn)行備份　

修改以下配置：　

bind-address=172.18.0.2\\指定Master地址
server-id = 1\\指定唯一的serverid部分版本沒有需手動(dòng)寫入
log_bin = /var/log/mariadb/mariadb-bin.log\\開啟binlog部分版本沒有需手動(dòng)寫入

注意：log_bin這個(gè)字段需根據(jù)實(shí)際情況來定，需找到數(shù)據(jù)庫(kù)的日志文件，默認(rèn)實(shí)在 /var/log

　　2.重新啟動(dòng)數(shù)據(jù)庫(kù)

[root@master my.cnf.d]# systemctl restart mariadb\\centos7、centos8、ubuntu重新啟動(dòng)方式
[root@master my.cnf.d]# server  mariadb  restart\\centos6及以下版本使用這個(gè)重新啟動(dòng)方式

　　mysql重新啟動(dòng)：

[root@master my.cnf.d]# systemctl restart mysqld\\centos7、centos8、ubuntu重新啟動(dòng)方式
[root@master my.cnf.d]# server  mysqld  restart\\centos6及以下版本使用這個(gè)重新啟動(dòng)方式

　　3.初始化數(shù)據(jù)庫(kù)

[root@master my.cnf.d]# 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//這里是設(shè)置root密碼，可不進(jìn)行設(shè)置
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] y
... Success!

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] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

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!
[root@master my.cnf.d]#

　　4.創(chuàng)建主從同步的用戶

 [root@master ~]# mysql -u root -p\\登陸數(shù)據(jù)庫(kù)
Enter password: \\輸入root密碼
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.11-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT REPLICATION SLAVE on *.* to 'slave'@'%' IDENTIFIED BY 'redhat';
\\創(chuàng)建用戶，并設(shè)置相應(yīng)的權(quán)限
\\此處%表示允許從任何地方（除本地外）使用此賬號(hào)進(jìn)行登陸使用，在正式環(huán)境建議具體到某臺(tái)主機(jī)IP
Query OK, 0 rows affected (0.000 sec)\\表示sql語句執(zhí)行成功

　　5.更新Slave用戶權(quán)限

 MariaDB [(none)]> flush privileges;\\每次修改用戶權(quán)限，都要使用這個(gè)sql語句進(jìn)行更新
Query OK, 0 rows affected (0.000 sec)

　　6.導(dǎo)出數(shù)據(jù)庫(kù)中所有數(shù)據(jù)（此步驟取決于slave的權(quán)限）

[root@master ~]# mysqldump -u root -p --all-databases --master-data > mariadb.bat.sql
--all-databases\\此參數(shù)表示備份所有數(shù)據(jù)庫(kù)
--master-data\\此參數(shù)表示將二進(jìn)制的信息寫入到輸出文件中，在這里是寫入到備份的sql文件中
Enter password:

　　7.查看MASTERr REPLICATION LOG位置

 MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     1974 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置Slave數(shù)據(jù)庫(kù)

　　1.更改Slave配置文件

　　　文件位置與Master位置一致

mysql數(shù)據(jù)庫(kù)：/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb數(shù)據(jù)庫(kù)：/etc/my.cnf.d/mariadb.cnf

　　　注意：我這里是使用yum進(jìn)行安裝的所以默認(rèn)配置文件是在/etc下面，建議在修改上面兩個(gè)文件時(shí)要先將配置文件進(jìn)行備份

　　修改以下配置：

bind-address=172.18.0.3\\指定Master地址
server-id = 2\\指定唯一的serverid部分版本沒有需手動(dòng)寫入
log_bin = /var/log/mariadb/mariadb-bin.log\\開啟binlog部分版本沒有需手動(dòng)寫入

　　注意：log_bin這個(gè)字段需根據(jù)實(shí)際情況來定，需找到數(shù)據(jù)庫(kù)的日志文件，默認(rèn)實(shí)在 /var/log

　　2.重新啟動(dòng)數(shù)據(jù)庫(kù)

[root@master my.cnf.d]# systemctl restart mariadb\\centos7、centos8、ubuntu重新啟動(dòng)方式
[root@master my.cnf.d]# server  mariadb  restart\\centos6及以下版本使用這個(gè)重新啟動(dòng)方式

　　mysql重新啟動(dòng)：

[root@master my.cnf.d]# systemctl restart mysqld\\centos7、centos8、ubuntu重新啟動(dòng)方式
[root@master my.cnf.d]# server  mysqld  restart\\centos6及以下版本使用這個(gè)重新啟動(dòng)方式

　　3.初始化數(shù)據(jù)庫(kù)

[root@master my.cnf.d]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

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.

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] y
... Success!

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] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

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!
[root@master my.cnf.d]#

　　4.從Master將數(shù)據(jù)庫(kù)備份復(fù)制到slave服務(wù)器

[root@slave my.cnf.d]# scp jia@172.18.0.2:/opt/mariadb.bat.sql /opt/
jia@172.18.0.2's password: 
mariadb.bat.sql

　　5.將備份數(shù)據(jù)恢復(fù)到slave數(shù)據(jù)庫(kù)

[root@slave my.cnf.d]# mysql -u root -p < /opt/mariadb.bat.sql 
 Enter password:

　　6.使slave與master建立連接

[root@slave my.cnf.d]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@slave my.cnf.d]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.11-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST = '172.18.0.2',\\指定Master數(shù)據(jù)庫(kù)地址
    -> MASTER_USER = 'slave',\\指定主從復(fù)制用戶名
    -> MASTER_PASSWORD = 'redhat',\\指定主從復(fù)制用戶密碼
    -> MASTER_LOG_FILE = 'mariadb-bin.000002',\\指定Master數(shù)據(jù)庫(kù)的binlog文件名
    -> MASTER_LOG_POS=1974;
Query OK, 0 rows affected (0.290 sec)

MariaDB [(none)]> start slave;\\開啟復(fù)制功能
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]>

　　注意：lMASTER_LOG_FILE='mariadb-bin.000002與MASTER_LOG_POS=1974的值，是從上面的 SHOW MASTER STATUS 得到的。

　　到這里已經(jīng)可以做到主從復(fù)制了下面讓我們測(cè)試一下吧

驗(yàn)證數(shù)據(jù)庫(kù)是否同步

測(cè)試方法很簡(jiǎn)單，只需要在主數(shù)據(jù)庫(kù)上面創(chuàng)建數(shù)據(jù)庫(kù)或者增加一條記錄就可以測(cè)試是否主從復(fù)制配置成功

MariaDB [(none)]> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.018 sec)

MariaDB [(none)]> create database a;\\在主數(shù)據(jù)庫(kù)創(chuàng)建a數(shù)據(jù)庫(kù)
Query OK, 1 row affected (0.063 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| a                  |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

　　下面我們來看看從數(shù)據(jù)庫(kù)上面有沒有a這個(gè)數(shù)據(jù)庫(kù)吧

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| a                  |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.075 sec)

　　我們會(huì)發(fā)現(xiàn)已經(jīng)有了a這個(gè)數(shù)據(jù)庫(kù)

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：頭號(hào)碼甲 > 《待分類》

舉報(bào)/認(rèn)領(lǐng)