概念
什么是·Mysql/Mariadb主從復制？
Mysql/Mariadb主從復制：當Master（主）資料庫發生變化的時候，變化實時會同步到slave（從）資料庫中； 類似于：Samba共享檔案（C/S）、NFS網路檔案共享（C/S），當服務端（Server）發生變化時，客戶端（client）資料內容會根據服務端進行改變；
好處
- 水平擴展資料庫的負載能力，后備資料庫，主資料庫服務器故障后，可切換到從資料庫繼續作業；
- 容錯、高可用，從資料庫可用來做備份、資料統計等作業，這樣不影響主資料庫的性能；
- 資料分布；
- 資料備份；
實作原理
在master機器上，主從同步事件會被寫到特殊的log檔案中(binary-log);
主從同步事件有3種形式:statement、row、mixed，
statement：會將對資料庫操作的sql陳述句寫入到binlog中， row：會將每一條資料的變化寫入到binlog中， mixed：statement與row的混合，Mysql決定什么時候寫statement格式的，什么時候寫row格式的binlog，
整體上來說，復制有3個步驟：
- master將改變記錄到二進制日志(binary log)中（這些記錄叫做二進制日志事件，binary log events)；
- slave將master的binary log events拷貝到它的中繼日志(relay log)；
- slave重做中繼日志中的事件，將改變反映它自己的資料，
下面這章圖可以詳細解釋其原理：
說的簡單一些就是：
當對Master資料庫不管做了增、刪、改還是創建了資料庫、表等操作時，Slave就會快速的接受這些資料以及物件的操作，從而實作主從資料復制，保證資料的一致性，
讀寫分離操作請查看：https://www.cnblogs.com/kuiyajia/p/11909757.html
實戰
我記得我學PHP開發的時候，教員經常說的一句話就是：學習半小時，實戰一分鐘；
好了，接下來到我們實戰的時刻了，認真聽講喲！！！
環境介紹
系統環境：系統基本上都差不多，一般多數都是Linux平臺和Windows平臺比較多，不管什么樣的系統環境對這次實戰的操作都影響不大，我在這里使用的是Docker虛擬出來的CentOS作業系統，當然您可以選用Ubuntu、RedHat以及Windows系統，這些都不會影響到大的操作；
我這里使用的系統版本：
[root@master /]# cat /etc/redhat-release CentOS Linux release 8.0.1905 (Core)
這里會用到兩臺服務器：其中一臺MasterIP172.18.0.2，另外一個slaveIP172.18.0.3
資料庫版本：（我這里使用的Mariadb，你可以使用mysql資料庫）
[root@master /]# mysql --version mysql Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1
配置Master資料庫
1.更改Master組態檔
找到下面檔案：
mysql資料庫：/etc/mysql/mysql,conf.d/mysqld.cnf mariadb資料庫：/etc/my.cnf.d/mariadb.cnf
注意：我這里是使用yum進行安裝的所以默認組態檔是在/etc下面，建議在修改上面兩個檔案時要先將組態檔進行備份
修改以下配置：
bind-address=172.18.0.2 \\指定Master地址 server-id = 1 \\指定唯一的serverid 部分版本沒有需手動寫入 log_bin = /var/log/mariadb/mariadb-bin.log \\開啟binlog 部分版本沒有需手動寫入
注意：log_bin這個欄位需根據實際情況來定，需找到資料庫的日志檔案，默認實在 /var/log
2.重新啟動資料庫
[root@master my.cnf.d]# systemctl restart mariadb \\centos7、centos8、ubuntu重新啟動方式 [root@master my.cnf.d]# server mariadb restart \\centos6及以下版本使用這個重新啟動方式
mysql重新啟動：
[root@master my.cnf.d]# systemctl restart mysqld \\centos7、centos8、ubuntu重新啟動方式 [root@master my.cnf.d]# server mysqld restart \\centos6及以下版本使用這個重新啟動方式
3.初始化資料庫
[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 //這里是設定root密碼，可不進行設定
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.創建主從同步的用戶
[root@master ~]# mysql -u root -p \\登陸資料庫 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'; \\創建用戶，并設定相應的權限 \\此處%表示允許從任何地方（除本地外）使用此賬號進行登陸使用，在正式環境建議具體到某臺主機IP Query OK, 0 rows affected (0.000 sec) \\表示sql陳述句執行成功
5.更新Slave用戶權限
MariaDB [(none)]> flush privileges; \\每次修改用戶權限，都要使用這個sql陳述句進行更新 Query OK, 0 rows affected (0.000 sec)
6.匯出資料庫中所有資料（此步驟取決于slave的權限）
[root@master ~]# mysqldump -u root -p --all-databases --master-data > mariadb.bat.sql --all-databases \\此引數表示備份所有資料庫 --master-data \\此引數表示將二進制的資訊寫入到輸出檔案中，在這里是寫入到備份的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資料庫
1.更改Slave組態檔
檔案位置與Master位置一致
mysql資料庫：/etc/mysql/mysql,conf.d/mysqld.cnf mariadb資料庫：/etc/my.cnf.d/mariadb.cnf
注意：我這里是使用yum進行安裝的所以默認組態檔是在/etc下面，建議在修改上面兩個檔案時要先將組態檔進行備份
修改以下配置：
bind-address=172.18.0.3 \\指定Master地址 server-id = 2 \\指定唯一的serverid 部分版本沒有需手動寫入 log_bin = /var/log/mariadb/mariadb-bin.log \\開啟binlog 部分版本沒有需手動寫入
注意：log_bin這個欄位需根據實際情況來定，需找到資料庫的日志檔案，默認實在 /var/log
2.重新啟動資料庫
[root@master my.cnf.d]# systemctl restart mariadb \\centos7、centos8、ubuntu重新啟動方式 [root@master my.cnf.d]# server mariadb restart \\centos6及以下版本使用這個重新啟動方式
mysql重新啟動：
[root@master my.cnf.d]# systemctl restart mysqld \\centos7、centos8、ubuntu重新啟動方式 [root@master my.cnf.d]# server mysqld restart \\centos6及以下版本使用這個重新啟動方式
3.初始化資料庫
[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 //這里是設定root密碼，可不進行設定
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.從Master將資料庫備份復制到slave服務器
[root@slave my.cnf.d]# scp [email protected]:/opt/mariadb.bat.sql /opt/ [email protected]'s password: mariadb.bat.sql
5.將備份資料恢復到slave資料庫
[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資料庫地址
-> MASTER_USER = 'slave', \\指定主從復制用戶名
-> MASTER_PASSWORD = 'redhat', \\指定主從復制用戶密碼
-> MASTER_LOG_FILE = 'mariadb-bin.000002', \\指定Master資料庫的binlog檔案名
-> MASTER_LOG_POS=1974;
Query OK, 0 rows affected (0.290 sec)
MariaDB [(none)]> start slave; \\開啟復制功能
Query OK, 0 rows affected (0.019 sec)
MariaDB [(none)]>
注意：lMASTER_LOG_FILE='mariadb-bin.000002與MASTER_LOG_POS=1974的值，是從上面的 SHOW MASTER STATUS 得到的，
到這里已經可以做到主從復制了下面讓我們測驗一下吧
驗證資料庫是否同步
測驗方法很簡單，只需要在主資料庫上面創建資料庫或者增加一條記錄就可以測驗是否主從復制配置成功
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.018 sec) MariaDB [(none)]> create database a; \\在主資料庫創建a資料庫 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)
下面我們來看看從資料庫上面有沒有a這個資料庫吧
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | a | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.075 sec)
我們會發現已經有了a這個資料庫
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109168.html
標籤：MySQL
上一篇：索引介紹