MySQL主從復制

一、MySQL主從復制原理
- 1.1、MySQL 支持的復制型別
- 1.2、復制的作業程序
二、需要主從復制原因與改造方法
- 2.1、原因
- 2.2、改造辦法
- 2.3、更高級解決辦法
三、實驗程序
- 3.1、實驗環境
- 3.2、建立時間同步環境
- 3.3、主服務器、從服務器1、從服務器2均安裝MySQL服務
- 3.4、主服務器配置
- 3.5、從服務器 1 配置
- 3.6、從服務器 2 配置
- 3.7、驗證：MySQL主從服務器
- - 主服務器
  - 從服務器 1
  - 從服務器 2
- 3.8、實驗小問題

一、MySQL主從復制原理

1.1、MySQL 支持的復制型別

基于陳述句的復制（默認）
在主服務器上執行的陳述句，從服務器執行同樣的陳述句
基于行的復制
把改變的內容復制到從服務器
混合型別的復制
一旦發現基于陳述句無法精確復制時，就會采用基于行的復制

1.2、復制的作業程序

① 在每個事務更新資料完成之前，Master在二進制日志記錄這些改變，寫入二進制日志完成后，Master通知存盤引擎提交事務
②Slave將Master的Binary log復制到其中繼日志，首先，Slave開始一個作業執行緒——I/O執行緒，I/O執行緒在Master上打開一個普通的連接，然后開始Binlog dump process，Binlog dump process從Master的二進制日志中讀取事件，如果已經跟上Master，他會睡眠并等待Master產生新的事件，I/O執行緒將這些事件寫入中繼日志，
③SQL slave thread（SQL從執行緒）處理該程序的最后一步，SQL執行緒從中繼日志讀取事件，并重放其中的事件而更新Slave的資料，使其與Master中的資料一致，只要該執行緒與I/O執行緒保持一致，中繼日志通常會位于OS的快取中，所以中繼日志的開銷很小，
復制程序有一個很重要的限制，即復制在Slave上是串行化的，也就是說Master上的并行更新操作不能再Slave上并行操作，

在這里插入圖片描述

二、需要主從復制原因與改造方法

2.1、原因

■在企業網站中，后端MySQL資料庫只有一臺時，會有以下問題

單點故障，服務不可用
無法處理大量的并發資料請求
資料丟失——大災難

2.2、改造辦法

■改造辦法

增加MySQL資料庫服務器，對資料進行備份，形成主備
確保主備MySQL資料庫服務器資料是一樣的
主服務器宕機了，備份服務器繼續作業，資料有保障

在這里插入圖片描述

2.3、更高級解決辦法

■更高級解決辦法

通過主從復制的方式來同步資料，再通過讀寫分離來提升資料庫的并發負載能力

在這里插入圖片描述

三、實驗程序

3.1、實驗環境

服務器配置

IP地址主機作業系統
20.0.0.25 master centos-7.6-x86_64
20.0.0.24 Slave1 centos-7.6-x86_64
20.0.0.23 Slave2 centos-7.6-x86_64

三臺MySQL服務器關閉防火墻，關閉核心防護

3.2、建立時間同步環境

在主服務器上安裝配置NTP時間同步服務器（20.0.0.25）

[root@localhost~]# yum -y install ntp    ###如果不是最小化安裝，就不用yum安裝ntp

[root@localhost~]# vi /etc/ntp.conf  
###洗掉下面四行###
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
###添加這兩行###
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost~]# systemctl restart ntpd
[root@localhost~]# systemctl enable ntpd
[root@localhost ~]# service ntpd restart
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd

在從服務器1上配置NTP同步（20.0.0.24）

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.24 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log 
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log  ###動態查看更新日志檔案

在從服務器2上配置NTP同步（20.0.0.23）

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.23 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log 
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log  ###動態查看更新日志檔案

3.3、主服務器、從服務器1、從服務器2均安裝MySQL服務

[root@localhost~]# yum -y install \
ncurses \
ncurses-devel \
bison \
cmake \
gcc \
gcc-c++
[root@localhost~]# useradd -s /sbin/nologin  mysql
[root@localhost~]# cd /opt
[root@localhost opt]# tar xf mysql-boost-5.7.20.tar.gz
[root@localhost opt]# cd /opt/mysql-5.7.20/
[root@localhost mysql-5.7.20]# 
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make -j4
[root@localhost mysql-5.7.20]# make install

[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/  
 '//資料庫目錄進行權限調整'

[root@localhost mysql-5.7.20]# vi /etc/my.cnf     
 '//建立調整組態檔'
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile  
     '//設定環境變數'
[root@localhost mysql-5.7.20]# source /etc/profile    
'//生效'

[root@localhost mysql-5.7.20]# cd /usr/local/mysql/

[root@localhost mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
[root@localhost mysql]# netstat -anpt | grep 3306
[root@localhost mysql]# mysqladmin -u root -p password
'//設定密碼'
[root@localhost ~]# mysql -u root -p   ## 驗證是否可以登錄資料庫
Enter password: 
mysql>

3.4、主服務器配置

[root@localhost ~]# vi /etc/my.cnf ###在原來server-id=1的地方修改成11后面新增log_bin=master-bin log-slave-updates=true###
server-id = 11         ## 修改為 11，下面的從服務器也會改，3個server-id 不可相同
log_bin = master-bin   ##設定二進制日志名
log-slave-updates = true   ## 從服務器更新二進制日志

[root@localhost ~]# systemctl restart mysqld ###重啟資料庫

登錄Master資料庫給從服務器授權
[root@localhost ~]# mysql -uroot -p
## 讓20.0.0.0端的從服務器擁有復制權限，可以使用myslave身份12345密碼復制所有的庫和表
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '12345';
mysql> flush privileges;   ## 重繪
mysql> show master status;  
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.5、從服務器 1 配置

[root@localhost ~]# vi /etc/my.cnf  ###在原來server-id=1的地方修改成22 后面新增relay-log=relay-log-bin relay-log-index=slave-relay-bin.index###
server-id = 22     ## 修改為22，另一臺從服務器的id設為33，三個id不可相同
relay-log = relay-log-bin          ## 設定二進制日志名
relay-log-index = slave-relay-bin.index      ## 服務器更新二進制日志
[root@localhost ~]# systemctl restart mysqld  ###重啟資料庫
登錄slave資料庫配置同步 ###注意下這邊的master_log_file='master-bin.000001'，master_log_pos=601;要和Master資料庫資訊不一致，不一致的話要更改先stop slave;然后更改同步資訊
[root@localhost ~]# mysql -uroot -p12345
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.25
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   ## 查看IO是正常的，這里顯示 Yes 就可以了
            Slave_SQL_Running: Yes   ## 查看SQL是正常的，這里顯示 Yes 就可以了
            ....................略........................
            1 row in set (0.00 sec)

3.6、從服務器 2 配置

[root@localhost ~]# vi /etc/my.cnf
server-id = 33
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p12345
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;

mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.25
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   ## 這里顯示 Yes 就可以了
            Slave_SQL_Running: Yes   ## 這里顯示 Yes 就可以了
            ....................略........................
            1 row in set (0.00 sec)

3.7、驗證：MySQL主從服務器

主服務器

mysql> create database School;  ## 創建 School庫
Query OK, 1 row affected (0.00 sec)

mysql> show databases;   ## 查看主服務器有 School庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

從服務器 1

mysql> show databases;   ## 查看從服務器 1 有 School庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |   ## 同步成功！
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

從服務器 2

mysql> show databases;   ## 查看從服務器 2 有 School庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |   ## 同步成功！
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.8、實驗小問題

主從復制沒有成功，顯示的引數是這樣 Slave_IO_Running: Connecting ，
解決辦法：

## 當我們輸入這句代碼完之后
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_Running: Connecting  ## 這里并沒有改成 Yes
            Slave_SQL_Running: Yes

1、可能是因為 master_log_pos=599 這個值輸入錯了導致沒有同步，
2、也可能是因為主服務器 IP 輸入錯了，或者是密碼錯了，

這時候我們需要 stop slave

mysql> stop slave;  ## 先關閉
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;  ## 然后再給一次復制權限，把里面的引數改成正確的引數
mysql> start slave;  ## 在開啟
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
## 再查看就已經設定成功了！
## 排障完成

實驗做到這里就結束了！我們最后驗證了是可以進行主從復制的，
1、做資料的熱備，作為后備資料庫，主資料庫服務器故障后，可切換到從資料庫繼續作業，避免資料丟失，
2、架構的擴展，業務量越來越大，I/O訪問頻率過高，單機無法滿足，此時做多庫的存盤，降低磁盤I/O訪問的頻率，提高單個機器的I/O性能，
3、讀寫分離，使資料庫能支撐更大的并發，在報表中尤其重要，由于部分報表sql陳述句非常的慢，導致鎖表，影響前臺服務，如果前臺使用master，報表使用slave，那么報表sql將不會造成前臺鎖，保證了前臺速度，

下一篇博客我將介紹主從復制的讀寫分離！歡迎收看！

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/48168.html

標籤：其他

上一篇：8月一次阿里云的Java面試涼經（止步三面）

下一篇：Spark 出現的問題及其解決方案

MySQL服務器主從復制同步——主從復制的方式同步資料，企業必備操作！我會主從復制了，你呢？

MySQL主從復制

一、MySQL主從復制原理

1.1、MySQL 支持的復制型別

1.2、復制的作業程序

二、需要主從復制原因與改造方法

2.1、原因

2.2、改造辦法

2.3、更高級解決辦法

三、實驗程序

3.1、實驗環境

3.2、建立時間同步環境

3.3、主服務器、從服務器1、從服務器2均安裝MySQL服務

3.4、主服務器配置

3.5、從服務器 1 配置

3.6、從服務器 2 配置

3.7、驗證：MySQL主從服務器

主服務器

從服務器 1

從服務器 2

3.8、實驗小問題