MySQL5.7主從複製教程

超人那個超~發表於2023-03-06
MySql

​ 簡述：主從複製，是用來建立一個和主資料庫完全一樣的資料庫環境，稱為從資料庫，主資料庫一般是準實時的 業務資料庫、事務處理庫，從庫做查詢庫。

​ 複製過程簡單的說就是 master 將資料庫的改變寫入二進位制日誌，slave同步這些二進位制日誌，並根據這些二進位制日誌行資料操作

1、什麼是主從複製

​ 主從複製，是用來建立一個和主資料庫完全一樣的資料庫環境，稱為從資料庫，主資料庫一般是準實時的業務資料庫。在最常用的mysql資料庫中，支援單向、非同步複製。在複製過程中，一個伺服器充當主伺服器，而另外一臺伺服器充當從伺服器；此時主伺服器會將更新資訊寫入到一個特定的二進位制檔案中。

​ 並會維護檔案的一個索引用來跟蹤日誌迴圈。這個日誌可以記錄併傳送到從伺服器的更新中去。當一臺從伺服器連線到主伺服器時，從伺服器會通知主伺服器從伺服器的日誌檔案中讀取最後一次成功更新的位置。然後從伺服器會接收從哪個時刻起發生的任何更新，然後鎖住並等到主伺服器通知新的更新。

2、主從複製的作用

  1. 是確保資料安全；做資料的熱備，作為後備資料庫，主資料庫伺服器故障後，可切換到從資料庫繼續工作，避免資料的丟失。

  2. 是提升I/O效能；隨著日常生產中業務量越來越大,I/O訪問頻率越來越高，單機無法滿足，此時做多庫的儲存，有效降低磁碟I/O訪問的頻率，提高了單個裝置的I/O效能。

  3. 是讀寫分離，使資料庫能支援更大的併發；在報表中尤其重要。由於部分報表sql語句非常的慢，導致鎖表，影響前臺服務。如果前臺使用master，報表使用slave，那麼報表sql將不會造成前臺鎖，保證了前臺速度。

3、主從複製的原理三步曲進行：

主資料庫有個bin log二進位制檔案，記錄了所有增刪改SQL語句。（binlog執行緒）

從資料庫把主資料庫的bin log檔案的SQL語句複製到自己的中繼日誌relay log（io執行緒）

從資料庫的relay log重做日誌檔案，再執行一次這些sql語句。（sql執行執行緒）

一、環境描述

伺服器最佳化注意事項

Ⅰ、伺服器的大磁碟必須掛載到/opt目錄下，服務的目錄以及資料都儲存在/opt/software下

Ⅱ、磁碟掛載必須預設寫入/etc/fstab

首先在兩個伺服器上建立兩個資料庫

作業系統 IP地址 資料庫 資料庫版本
CentOS 7.9 1.0.0.11 MySQL主 5.7.38
CentOS 7.9 1.0.0.12 MySQL從 5.7.38
CentOS 7.9 1.0.0.13 MySQL從 5.7.38

二、Linux基礎配置

# 檢視伺服器版本
cat /etc/redhat-release
# 檢視伺服器磁碟空間使用情況
df -h
# 新建資料夾package存放壓縮包
mkdir /opt/package
# 新建資料夾software存放解壓後的軟體
mkdir /opt/software
# 新建資料夾存放專案
mkdir /opt/project
# 新建資料夾apr存放檔案
mkdir /opt/software/apr
# 新建資料夾apr-util存放檔案
mkdir /opt/software/apr-util

關閉防火牆

# 檢視防火牆狀態
firewall-cmd --state
# 關閉防火牆（重啟伺服器會開啟）
systemctl stop firewalld.service
# 永久關閉防火牆
systemctl disable firewalld.service

關閉SELinux

# 關閉SELinux修改配置檔案需要重啟機器：
# 修改/etc/selinux/config 檔案
vim /etc/selinux/config
將SELINUX=enforcing 改為：SELINUX=disabled
# 重啟機器生效
reboot

配置IP、主機名對映

vim /etc/hosts
1.0.0.11 nwtest1
1.0.0.12 nwtest2
1.0.0.13 nwtest3

設定時間同步

# 聯網
# 設定東八區時區為當前時區
rm -rf /etc/localtime
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# 手動同步下網路時間
ntpdate -u cn.pool.ntp.org
# 內網狀態時間設定
# 檢視當前系統時間
date
# 檢視硬體時間
hwclock
# 修改當前系統時間
date -s "2023-1-1 8:08:08"
# 修改硬體時間
hwclock --set --date "2023-1-1 8:08:08"
# 同步系統時間和硬體時間
hwclock --hctosys
# 儲存時鐘
clock -w
# 重啟機器生效
reboot

三、安裝MySQL5.7.38

Master/Slave部署?

主從伺服器均需要進行本MySQL安裝步驟！！！

部署路徑：/opt/software/mysql

1、安裝MySQL所需的yum源

yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libgcrypt libtool bison
# 因為CentOS7作業系統預設安裝mariadb-libs庫，它與MySQL衝突，影響初始化
yum -y remove mariadb-libs

2、建立MySQL組以及MySQL使用者

# 建立MySQL組
groupadd mysql
# 建立MySQL使用者
useradd -g mysql mysql
# 設定密碼
passwd mysql
# 本機設定為:
123456

3、解壓MySQL檔案包

# 將MySQL安裝包放在opt目錄下
cd /opt/package
# 在當前目錄解壓mysql安裝包
tar -zxvf mysql-5.7.38.tar.gz -C /opt/software/
# 修改MySQL目錄名稱
mv /opt/software/mysql-5.7.38-linux-glibc2.12-x86_64 /opt/software/mysql

4、安裝MySQL資料庫

# 進行mysql安裝目錄下
cd /opt/software/mysql
# 新建資料夾並授權
mkdir -p /opt/software/mysql/data
mkdir -p /opt/software/mysql/logs
echo "" > /opt/software/mysql/logs/mysql_err.log
mkdir -p /opt/software/mysql/logbin
mkdir -p /opt/software/mysql/tmp
chown -R mysql:mysql /opt/software/mysql
chmod +x /opt/software/mysql

修改配置檔案

1>vim /etc/my.cnf 或 cat > /etc/my.cnf <<EOF

cat > /etc/my.cnf <<EOF
# 客戶端設定,即客戶端預設的連線引數
[client]
# 預設連線埠
port = 6603
# 程式與mysqlserver處於同一臺機器,發起本地連線時可用
socket = /opt/software/mysql/mysql.sock
# 資料庫字符集
default_character_set = utf8mb4
# 服務端基本設定
#主從同步配置
[mysqld]
# 本機資料庫 ID 標示，主從配置中ID要唯一
server_id = 1
# MySQL監聽埠
port = 6603
# 為MySQL客戶端程式和伺服器之間的本地通訊指定一個套接字檔案
socket = /opt/software/mysql/mysql.sock
# pid檔案所在目錄
pid_file = /opt/software/mysql/mysql.pid
# 使用該目錄作為根目錄（安裝目錄）
basedir = /opt/software/mysql
# 資料檔案存放的目錄
datadir = /opt/software/mysql/data
# MySQL存放臨時檔案的目錄
tmpdir = /opt/software/mysql/tmp
# 服務端預設編碼（資料庫級別）
character_set_server = utf8mb4
# 服務端預設的比對規則,排序規則
collation_server = utf8mb4_general_ci
# 錯誤日誌路徑
log_error = /opt/software/mysql/logs/mysql_err.log
# 開啟二進位制日誌功能,binlog資料位置
log_bin = /opt/software/mysql/logbin/mysql_bin
# 混合模式複製
binlog_format = mixed
# 超過7天的binlog刪除
expire_logs_days = 7
# binlog每個日誌檔案大小
max_binlog_size = 1G
# 這個表示只同步某個庫, 二進位制需要同步的資料庫名 （如果沒有此項，表示同步所有的庫）
# binlog-do-db = xxxx
# 不可以被從伺服器複製的庫
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schem
### 主從複製的格式（mixed,statement,row，預設格式是statement）
binlog_format=mixed
### 如：1062錯誤是指一些主鍵重複，1032錯誤是因為主從資料庫資料不一致
slave_skip_errors=1062
### 控制binlog的寫入頻率。每執行多少次事務寫入一次
### 這個引數效能消耗很大，但可減小MySQL崩潰造成的損失，為0表示不控制
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# 指定是否開啟慢查詢日誌
slow_query_log = 1
# 指定慢日誌檔案存放位置,可以為空,系統會給一個預設的檔案host_name_slow.log
slow_query_log_file = /opt/software/mysql/logs/slow.log
# 設定慢查詢的閥值,超出次設定值的SQL即被記錄到慢查詢日誌
long_query_time = 8
# 引數 log_output 指定了慢查詢輸出的格式,預設為 FILE,你可以將它設為 TABLE,然後就可以查詢 mysql 架構下的 slow_log表了
log_output = TABLE
# 如果執行的SQL語句沒有使用索引,則mysql資料庫同樣會將這條SQL語句記錄到慢查詢日誌檔案中
log_queries_not_using_indexes
# 跳過外部鎖定,防止檔案目錄不可用,該選項預設開啟
skip_external_locking
# 禁用域名DNS查詢,不能在mysql的授權表中使用主機名或域名,只能使用IP或localhost
skip_name_resolve
# 表名的大小寫敏感選項,預設為0,即開啟大小寫敏感,1為大小寫不敏感,看需求設定
lower_case_table_names = 1
# MySQL的最大連線數,如果伺服器的併發連線請求量比較大,建議調高此值
max_connections = 3000
# 指定MySQL可能的連線數量。當MySQL主執行緒在很短的時間內得到非常多的連線請求,該引數就起作用
back_log = 1024
# 對於同一主機,如果有超出該引數值個數的中斷錯誤連線,則該主機將被禁止連線。如需對該主機進行解禁,執行：FLUSH HOST
max_connect_errors = 6000
# mysql支援的基本語法及校驗規則
sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 指定用於索引的緩衝區大小,增加它可得到更好的索引處理效能
key_buffer_size = 256M
# 表描述符快取大小,可減少檔案開啟/關閉次數
table_open_cache = 1024
# MySQL執行排序使用的緩衝大小。如果想要增加ORDER BY的速度,首先看是否可以讓MySQL使用索引而不是額外的排序階段
sort_buffer_size = 256M
# 讀查詢操作所能使用的緩衝區大小。和 sort_buffer_size 一樣,該引數對應的分配記憶體也是每連線獨享！
read_buffer_size = 32M
# 執行緒池快取大小
thread_cache_size = 64
# MySQL的隨機讀緩衝區大小
read_rnd_buffer_size = 32M
# 指定MySQL 查詢緩衝區的大小
query_cache_size = 64M
# 明確時間戳預設null方式
explicit_defaults_for_timestamp = true
# 指定單個查詢能夠使用的緩衝區大小,預設1M
query_cache_limit = 5M
# 預設儲存引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:4000M;ibdata2:2000M:autoextend
# InnoDB 用來高速緩衝資料和索引記憶體緩衝大小。 更大的設定可以使訪問資料時減少磁碟 I/O。
innodb_buffer_pool_size=2G
# 此引數確定些日誌檔案所用的記憶體大小,以M為單位。緩衝區更大能提高效能,但意外的故障將會丟失資料。MySQL開發人員建議設定為1－8M之間
innodb_log_buffer_size = 4M
# 此引數確定資料日誌檔案的大小,更大的設定可以提高效能,但也會增加恢復故障資料庫所需的時間
innodb_log_file_size = 32M
# 為提高效能,MySQL可以以迴圈方式將日誌檔案寫到多個檔案。推薦設定為3
innodb_log_files_in_group = 3
# innodb主執行緒重新整理快取池中的資料,使髒資料比例小於90%
innodb_max_dirty_pages_pct = 90
# InnoDB事務在被回滾之前可以等待一個鎖定的超時秒數。
innodb_lock_wait_timeout = 120 
# 批次插入快取大小, 這個引數是針對MyISAM儲存引擎來說的。適用於在一次性插入100_1000+條記錄時, 提高效率。預設值是8M。可以針對資料量的大小,翻倍增加。
bulk_insert_buffer_size = 8M
# MyISAM設定恢復表之時使用的緩衝區的尺寸,當在REPAIR TABLE或用CREATE INDEX建立索引或ALTER TABLE過程中排序 MyISAM索引分配的緩衝區
myisam_sort_buffer_size = 16M
# 如果臨時檔案會變得超過索引,不要使用快速排序索引方法來建立一個索引。註釋：這個引數以位元組的形式給出
myisam_max_sort_file_size = 10G
# 如果該值大於1,在Repair by sorting過程中並行建立MyISAM表索引(每個索引在自己的執行緒內)
myisam_repair_threads = 1
# 伺服器關閉互動式連線前等待活動的秒數。預設值：28800秒（8小時）
interactive_timeout = 28800
# 限制server接受的資料包大小;指代mysql伺服器端和客戶端在一次傳送資料包的過程當中資料包的大小
max_allowed_packet = 256M

[mysqldump]
# 支援較大資料庫的轉儲,在匯出非常巨大的表時需要此項。增加該變數的值十分安全,這是因為僅當需要時才會分配額外記憶體。
# 例如,僅當你發出長查詢或mysqld必須返回大的結果行時mysqld才會分配更多記憶體。
# 該變數之所以取較小預設值是一種預防措施,以捕獲客戶端和伺服器之間的錯誤資訊包,並確保不會因偶然使用大的資訊包而導致記憶體溢位。
# 如果你正是用大的BLOB值,而且未為mysqld授予為處理查詢而訪問足夠記憶體的許可權,也會遇到與大資訊包有關的奇怪問題。
# 如果懷疑出現了該情況,請嘗試在mysqld_safe指令碼開始增加ulimit _d 256000,並重啟mysqld。
quick
# 限制server接受的資料包大小;指代mysql伺服器端和客戶端在一次傳送資料包的過程當中資料包的大小
max_allowed_packet = 256M
# TCP/IP和套接字通訊緩衝區大小,建立長度達net_buffer_length的行
net_buffer_length = 16384

[mysql]
# auto_rehash是自動補全的意思
auto_rehash

# isamchk資料檢測恢復工具
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

EOF

2>初始化資料庫

cd /opt/software/mysql/
# 初始化資料庫
bin/mysqld --initialize --user=mysql --basedir=/opt/software/mysql --datadir=/opt/software/mysql/data
# 檢視MySQL初始密碼
grep 'temporary password' /opt/software/mysql/logs/mysql_err.log

5、啟動MySQL服務

cd /opt/software/mysql/support-files/
# 然後啟動MySQL
./mysql.server start
# 登入MySQL
cd /opt/software/mysql/bin/
./mysql -uroot -p
# 輸入密碼，初始化產生的密碼。進入介面
# 修改root密碼
mysql> alter user 'root'@'localhost' identified by 'root';
# 允許遠端機器連線
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
# 立即生效
mysql> flush privileges;
# 退出
mysql> quit;

6、配置環境變數

# 修改配置檔案
vim /etc/profile
# 在最後面加入
export MYSQL_HOME=/opt/software/mysql
export PATH=$PATH:$MYSQL_HOME/bin
# 使配置檔案立即生效
source /etc/profile

7、將MySQL設定為開機自啟

# 將 mysql.server 檔案複製到 /etc/rc.d/init.d/ 目錄下
cp /opt/software/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# 賦予可執行許可權
chmod +x /etc/init.d/mysqld
# 新增為服務
chkconfig --add mysqld
# 檢視服務列表
chkconfig --list
# 看到3、4、5狀態為開或者為 on 則表示成功,如果是 關或者 off 則執行一下
chkconfig --level 345 mysqld on
# 重啟伺服器
reboot
# 再次檢視服務列表或者檢視6603埠號
netstat -na | grep 6603


MySQL常用命令

# 啟動MySQL服務
service mysqld start
systemctl start mysqld
# 關閉MySQL服務
service mysqld stop
systemctl stop mysqld
# 檢視MySQL狀態
service mysqld status
systemctl status mysqld

四、配置MySQL主從

1、Master配置

1、修改主伺服器my.cnf

修改/etc/my.cnf,在[mysqld]模組下新增以下內容

# 主從同步配置
[mysqld]
# 本機資料庫 ID 標示，主從配置中ID要唯一
server_id = 1
# MySQL監聽埠
port = 6603
# 為MySQL客戶端程式和伺服器之間的本地通訊指定一個套接字檔案
socket = /opt/software/mysql/mysql.sock
# pid檔案所在目錄
pid_file = /opt/software/mysql/mysql.pid
# 使用該目錄作為根目錄（安裝目錄）
basedir = /opt/software/mysql
# 資料檔案存放的目錄
datadir = /opt/software/mysql/data
# MySQL存放臨時檔案的目錄
tmpdir = /opt/software/mysql/tmp
# 服務端預設編碼（資料庫級別）
character_set_server = utf8mb4
# 服務端預設的比對規則,排序規則
collation_server = utf8mb4_general_ci
# 錯誤日誌路徑
log_error = /opt/software/mysql/logs/mysql_err.log
# 開啟二進位制日誌功能,binlog資料位置
log_bin = /opt/software/mysql/logbin/mysql_bin
# 混合模式複製
binlog_format = mixed
# 超過7天的binlog刪除
expire_logs_days = 7
# binlog每個日誌檔案大小
max_binlog_size = 1G
# 這個表示只同步某個庫, 二進位制需要同步的資料庫名 （如果沒有此項，表示同步所有的庫）
# binlog-do-db = xxxx
# 不可以被從伺服器複製的庫
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schem
### 主從複製的格式（mixed,statement,row，預設格式是statement）
binlog_format=mixed
### 如：1062錯誤是指一些主鍵重複，1032錯誤是因為主從資料庫資料不一致
slave_skip_errors=1062
### 控制binlog的寫入頻率。每執行多少次事務寫入一次
### 這個引數效能消耗很大，但可減小MySQL崩潰造成的損失，為0表示不控制
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

2、修改完配置，重啟主庫的MySQL

service mysqld restart

3、登入MySQL

mysql -uroot -proot

4、主資料庫授權從資料庫，授權從資料庫能夠讀取主資料庫二進位制檔案

#建立同步使用者slave，使用者名稱slave，密碼slave@2023
mysql> create user 'slave'@'%' identified by 'slave@2023';
#給同步賬戶賦予複製許可權
mysql> grant replication slave on *.* to 'slave'@'%' identified by 'slave@2023';
#重新整理許可權
mysql> flush privileges;
# 檢視主庫的狀態
mysql> show master status;

說明：?

replication 複製許可權，也可以是all；

slave 表示從庫；

*. 表示同步所有資料庫，也可以指定某一個庫；

slave 使用者僅是mysql資料庫的一個認證使用者；

% 全網都能登陸訪問，可指定也可以設定成某一個網段都可以訪問。

2、Slave配置

1、修改從伺服器my.cnf

修改/etc/my.cnf,在[mysqld]模組下新增以下內容

# 修改/etc/my.cnf,在[mysqld]模組下新增以下內容
# 主從同步配置
#這裡server-id的值可以是除1之外的任意數字，切記不能為1
server-id=2
# 其他內容和主庫保持一致即可

2、修改完配置，重啟主庫的MySQL

service mysqld restart

3、執行主從初始化

# 登入MySQL
mysql -uroot -proot
# 在mysql命令列輸入指令，執行初始化
change master to master_host='1.0.0.11',master_user='slave',master_password='slave@2023',master_port=6603;
# 開啟從庫(stop slave：關閉從庫）
mysql> start slave;
# 檢視狀態
mysql> show slave status\G;

說明：?

master_host='1.0.0.11' # 指定主資料庫地址

master_user='slave' # 指定連線使用者

master_password='slave@2023' # 指定連線密碼

master_port=6603 # 指定連線埠（埠切記不可加引號）

4、都為Yes的時候表示配置成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO：複製master主機binlog日誌檔案裡的SQL到本機的relay-log檔案裡

Slave_SQL：執行本機relay_log檔案裡的SQL語句重現Master的資料操作

還可透過 netstat -nltpa 檢視從庫與主庫通訊埠。

千萬注意：?

由於這裡我們同步的是最新的二進位制檔案，所以在該二進位制檔案之前生成的資料庫是無法進行同步的；若要將全部資料庫同步，需先將所有資料庫匯出。

五、驗證MySQL主從

1、在Master主庫進行資料庫建立，執行建表語句

# 新增資料庫datadb
mysql> create database datadb;
# 進入資料庫datadb
mysql> use datadb;
# 資料庫datadb新增test表結構
mysql> create table test(id int not null,username varchar(32));
# 在test表插入一條資料
mysql> insert into test(id,username) values(1,'root');
mysql> insert into test(id,username) values(2,'從庫看得見嘛');
# 重新整理許可權
mysql> flush privileges;
mysql> quit;

2、在Slave從庫上檢視是否會自動同步該庫，表結構以及資料

# 檢視資料庫
mysql> show databases;
# 進入資料庫datadb
mysql> use datadb;
# 資料庫datadb新增test表結構
mysql> create table test(id int not null,username varchar(32));
# 檢視錶test
mysql> show tables;
# 查詢表test內容
mysql> select * from test \G
mysql> quit;

可以看到，slave完成了對master的複製。

--------------------------------------至此MySQL主從複製部署完成_--------------------------------------

六、MySQL主從部署錯誤記錄

錯誤一、show slave status\G;查詢後報錯：ERROR: No query specified

解決辦法：

在\G後面加入；分號雖然查詢出來了但是會報錯

ERROR:

No query specified

去掉\G後面加；分號就不會報錯了。

錯誤二、MySQL主從同步故障：Slave_SQL_Running:No

解決辦法：

Slave_SQL_Running: No

1.程式可能在slave上進行了寫操作；

2.也可能是slave機器重起後，事務回滾造成的；

一般是事務回滾造成的。

解決辦法：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

相關文章