當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL-主从架构的搭建

發(fā)布時間：2025/3/21 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL-主从架构的搭建小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

文章目錄

生猛干貨
一主一從的搭建
- 前置工作
- 操作步驟
- - 配置 master主服務(wù)器
  - Master的數(shù)據(jù)庫中建立一個復(fù)制帳戶并授權(quán)
  - 配置 slave從服務(wù)器
  - 啟動從服務(wù)器(slave)的復(fù)制線程
  - 查看從服務(wù)器狀態(tài)
- binlog相關(guān)的命令
- 問題記錄
- - File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory)
  - File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 13 - Permission denied)
  - [ERROR] Unable to setup unix socket lock file.
  - can't read dir of './artisan' (errcode 13 - permission denied)
  - ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
主從測試
- 測試主從同步
- 問題記錄
- - Slave_SQL_Running: No
搞定MySQL

生猛干貨

帶你搞定MySQL實(shí)戰(zhàn)，輕松對應(yīng)海量業(yè)務(wù)處理及高并發(fā)需求，從容應(yīng)對大場面試

一主一從的搭建

前置工作

OS: CentOS 7
DB：MySQL5.7

131和132已經(jīng)按照MySQL-CentOS7通過YUM安裝MySQL5.7.29完成了MYSQL的安裝，并成功啟動。

操作步驟

配置 master主服務(wù)器

核心配置：開啟binlog , 指定唯一的server ID

mysql的配置文件 /etc/my.cnf ,增加如下配置

#指定binlog的存儲位置，日志格式為二進(jìn)制 log-bin=/var/lib/mysql/data/binlog/mysql-bin #[必須]服務(wù)器唯一ID，默認(rèn)是1，多個mysql server，不重復(fù)即可 server-id=1 #每次執(zhí)行寫入就與硬盤同步 (如果不配置這個,會等到緩沖區(qū)滿了自動刷盤, 安全和性能的權(quán)衡,配置為1是每次都會刷盤，自然慢一些，自己權(quán)衡) sync-binlog=1 #需要同步的二進(jìn)制數(shù)據(jù)庫名 (有多個的話，配置多個binlog-do-db) binlog-do-db=artisan #只保留7天的二進(jìn)制日志，以防磁盤被日志占滿 expire-logs-days=7 #不備份的數(shù)據(jù)庫(有多個的話，配置多個binlog-ignore-db) binlog-ignore-db=information_schema binlog-ignore-db=performation_schema binlog-ignore-db=sys #關(guān)閉名稱解析(非必須) skip-name-resolve

全部的配置如下：

配置項(xiàng)解讀：

log-bin

log-bin=/var/lib/mysql/data/binlog/mysql-bin

mysql默然是不開啟bin-log的，show binary logs; 可查看

開啟bin-log，核心的配置
server-id = [序列號] log-bin = [文件名]
配置該項(xiàng)后，會自動生成 mysql-bin.index和mysql-bin.xxx的文件
server-id：在 MySQL 5.7.3 及以后版本,如果沒有設(shè)置server-id, 那么設(shè)置binlog后無法開啟MySQL服務(wù). (Bug #11763963, Bug #56739)
sync-binlog ：權(quán)衡一下刷盤的機(jī)制，性能和安全之間權(quán)衡一下。
binlog-do-db：需要同步的數(shù)據(jù)庫，多個庫，配置多個binlog-do-db
expire-logs-days： bin-log的保留時間
binlog-ignore-db：不同步的數(shù)據(jù)庫

查看 bin-log的配置信息

File: 當(dāng)前記錄bin-log的文件
Position: 從服務(wù)器讀取的位置
Binlog_Do_DB: 需要同步的數(shù)據(jù)庫
Binlog_Ignore_DB: 忽略的數(shù)據(jù)庫，不同步

Master的數(shù)據(jù)庫中建立一個復(fù)制帳戶并授權(quán)

在 Master 的數(shù)據(jù)庫中建立一個備份帳戶(user = artisan4syn,pwd=artisan)：每個 slave 使用標(biāo)準(zhǔn)的 MySQL 用戶名和密碼連接 master 。

進(jìn)行復(fù)制操作的用戶會授予 REPLICATION SLAVE 權(quán)限。

# @后面的IP段，建議僅對從服務(wù)的網(wǎng)段進(jìn)行開放 CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan'; grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan';FLUSH PRIVILEGES; [root@artisan ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.29-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> uninstall plugin validate_password; Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan'; # 用戶和密碼自定義 Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan'; # *.* 所有數(shù)據(jù)庫的所有表 Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)mysql>

查看用戶：

select * from mysql.user a where a.`User` = 'artisan4syn';

重點(diǎn)關(guān)注 host user ,

這是啥意思呢？

意思是 授權(quán)用戶user 從哪臺服務(wù)器host能夠登錄 ----------------> 主節(jié)點(diǎn) （主節(jié)點(diǎn)上創(chuàng)建的用戶嘛）允許 192.168網(wǎng)段的IP，通過 artisan4syn用戶訪問我主節(jié)點(diǎn)。

配置 slave從服務(wù)器

slave從節(jié)點(diǎn)主要的配置為：打開中繼日志，指定唯一的 servr ID，設(shè)置只讀權(quán)限

從服務(wù)器mysql的配置文件/etc/my.cnf 文件增加如下配置

#配置server-id，唯一ID號 server-id=2 #開啟從服務(wù)器二進(jìn)制日志 (從節(jié)點(diǎn)如果后面沒有級聯(lián)的從節(jié)點(diǎn)，binlog可以不打開，避免無謂的資源消耗) #log_bin = /var/lib/mysql/data/binlog/mysql-bin#[必須開啟]打開Mysql中繼日志，日志格式為二進(jìn)制 relay_log = /var/lib/mysql/data/binlog/mysql-relay-bin #如果salve庫名稱與master庫名相同，使用本配置 replicate-do-db = artisan #如果master庫名[artisan]與salve庫名[artisan01]不同，使用以下配置[需要做映射] #replicate-rewrite-db = artisan[主庫名] -> artisan01[從庫名] #如果不是要全部同步[默認(rèn)全部同步]，則指定需要同步的表 #replicate-wild-do-table=artisan01.t_order #replicate-wild-do-table=artisan01.t_order_item#設(shè)置只讀權(quán)限 read_only = 1 #使得更新的數(shù)據(jù)寫進(jìn)二進(jìn)制日志中 log_slave_updates = 1 （這個是不是應(yīng)該為 on 待確認(rèn) , 給GTID用的？ 5.7以下的版本使用GTID必須要開啟）

配置項(xiàng)解讀：

server-id ：唯一id ,必須設(shè)置
relay_log 中繼日志，必須設(shè)置。結(jié)合主從同步的架構(gòu)圖去理解就非常好理解了。
[root@artisan ~]# mkdir -p /var/lib/mysql/data/binlog/mysql-bin [root@artisan ~]# [root@artisan ~]# cd /var/lib/mysql [root@artisan mysql]# chown -R mysql:mysql data/ [root@artisan mysql]# ll data/ total 0 drwxr-xr-x. 3 mysql mysql 22 Jan 28 19:07 binlog
[root@artisan ~]# cd /var/lib/mysql [root@artisan mysql]# chown mysql:mysql mysql.sock.lock
log_bin ：從服務(wù)器可選 ,開啟從服務(wù)器二進(jìn)制日志 (從節(jié)點(diǎn)如果后面沒有級聯(lián)的從節(jié)點(diǎn)，binlog可以不打開，避免無謂的資源消耗，如果后面還有個從節(jié)點(diǎn)，那就必須要開啟了，根據(jù)實(shí)際情況而定)
replicate-do-db ：如果salve庫名稱與master庫名相同，使用本配置
replicate-rewrite-db ：master和slave數(shù)據(jù)庫不同時，需要做映射
replicate-rewrite-db = artisan -> artisan01 replicate-rewrite-db = artisan -> artisan02
主庫artisan 映射到兩個不同的數(shù)據(jù)庫，這種配置是不對的，因?yàn)橐粋€binlog只能播放一次 .
replicate-wild-do-table ：需要同步的表

注意事項(xiàng)：

如果也配置了同步的表，優(yōu)先使用這個配置。

如果master節(jié)點(diǎn)有個跨庫的查詢，比如一個查詢關(guān)聯(lián)了DB1和DB2 ,這個時候如果想要同步到從節(jié)點(diǎn)，必須要配置 replicate-wild-do-table，指定具體的表
read_only =1 : db只可以讀，不可寫，但對root超級用戶來講，是不受只讀設(shè)置影響的，所以，root還是可以往里面去插入數(shù)據(jù) . 如果我們換成其他的普通用戶就不會出現(xiàn)這樣的問題了。要防止root可寫，設(shè)置 flush tables with read lock; 但這樣的話，主從自動同步就掛逼了。。。因?yàn)橹鲝耐绞峭ㄟ^把主上的 sql 語句放在從上再執(zhí)行一遍來實(shí)現(xiàn)的，鎖表的話，sql 語句就不能執(zhí)行了。想要同步，需要把表解鎖（執(zhí)行 unlock tables;）就可以了
log_slave_updates ： https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_slave_updates

默認(rèn)的情況下log_slave_updates參數(shù)是關(guān)閉的，從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進(jìn)制日志。該選項(xiàng)告訴從服務(wù)器將其SQL線程執(zhí)行的更新記入到從服務(wù)器自己的二進(jìn)制日志。為了使該選項(xiàng)生效，還必須用–logs-bin選項(xiàng)啟動從服務(wù)器以啟用二進(jìn)制日志。如果想要應(yīng)用鏈?zhǔn)綇?fù)制服務(wù)器，應(yīng)使用–logs-slave-updates。例如，可能你想要這樣設(shè)置：

A -> B -> C

也就是說，A為從服務(wù)器B的主服務(wù)器，B為從服務(wù)器C的主服務(wù)器。為了能工作，B必須既為主服務(wù)器又為從服務(wù)器。你必須用–logs-bin啟動A和B以啟用二進(jìn)制日志，并且用–logs-slave-updates選項(xiàng)啟動B。

1、從庫只開啟log-bin功能，不添加log-slave-updates參數(shù)，從庫從主庫復(fù)制的數(shù)據(jù)不會寫入log-bin日志文件里。
2、直接向從庫寫入數(shù)據(jù)時，是會寫入log-bin日志的。
3、開啟log-slave-updates參數(shù)后，從庫從主庫復(fù)制的數(shù)據(jù)會寫入log-bin日志文件里。這也是該參數(shù)的功能。
開啟以后可以實(shí)現(xiàn)主主同步，切換。

啟動從服務(wù)器(slave)的復(fù)制線程

slave 連接 master ，并開始重做 master 二進(jìn)制日志中的事件

先到 master上執(zhí)行下

在slave節(jié)點(diǎn)上執(zhí)行如下命令：

[root@artisan mysql-bin]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.29 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.# Step1.設(shè)置同步關(guān)系mysql> change master to master_host='192.168.18.131', master_user='artisan4syn', master_password='artisan', master_log_file='mysql-bin.000045', master_log_pos=1308; Query OK, 0 rows affected, 2 warnings (0.41 sec)# Step2.啟動復(fù)制線程mysql> start slave; Query OK, 0 rows affected (0.04 sec)mysql>

start slave 啟動可能報錯： ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread;run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

----》原因：在設(shè)置同步關(guān)系時，沒有先停止slaves線程。
----》解決辦法：在設(shè)置同步關(guān)系之前，先執(zhí)行下“stop slave;”，以停止slave線程。然后啟動線程"start slave;"

參數(shù)說明：

mysql> change master to master_host='192.168.18.131', // master ip>master_user='artisan4syn', // 創(chuàng)建的復(fù)制用戶>master_password='artisan', // 創(chuàng)建的復(fù)制用戶的密碼>master_log_file='mysql-bin.000045', //master對應(yīng)的bin-log文件 >master_log_pos=1308; //master對應(yīng)的position

看看 mysql的log ,啟動了2個線程。

查看從服務(wù)器狀態(tài)

可使用SHOW SLAVE STATUS\G;查看從服務(wù)器狀態(tài),

也可用show processlist \G;查看當(dāng)前復(fù)制狀態(tài)

# 查看查看從節(jié)點(diǎn)狀態(tài) mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.18.131Master_User: artisan4synMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000045Read_Master_Log_Pos: 1308Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000045Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: artisanReplicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1308Relay_Log_Space: 527Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 2a9b3cc0-4140-11ea-b350-000c29f66452Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specifiedmysql>

如何確定slave節(jié)點(diǎn)的兩個線程工作正常，看紅框，需要關(guān)注的幾個點(diǎn)

Slave_IO_Running: Yes //IO線程正常運(yùn)行

Slave_SQL_Running: Yes //SQL線程正常運(yùn)行

mysql> show processlist \G; *************************** 1. row ***************************Id: 3User: rootHost: 192.168.18.1:61080db: NULL Command: SleepTime: 2549State: Info: NULL *************************** 2. row ***************************Id: 4User: rootHost: localhostdb: NULL Command: QueryTime: 0State: startingInfo: show processlist *************************** 3. row ***************************Id: 5User: system userHost: db: NULL Command: ConnectTime: 784State: Waiting for master to send eventInfo: NULL *************************** 4. row ***************************Id: 6User: system userHost: db: NULL Command: ConnectTime: 784State: Slave has read all relay log; waiting for more updatesInfo: NULL 4 rows in set (0.00 sec)ERROR: No query specifiedmysql>

binlog相關(guān)的命令

# 查看是否開啟binlog mysql>show binary logs;#查看binlog格式： mysql>show variables like 'binlog_format';#獲取binlog文件列表： mysql>show binary logs;#查看當(dāng)前正在寫入的binlog文件： mysql>show master status;#查看master上的binlog： mysql>show master logs;#只查看第一個binlog文件的內(nèi)容： mysql>show binlog events;#查看指定binlog文件的內(nèi)容： mysql>show binlog events in 'mysql-bin.000045';#清空所有的bin-log： mysql>reset master;#生成一個新的binlog： mysql>flush logs; #用bash查看binlog日志文件： [root@artisan binlog]# mysqlbinlog mysql-bin.000045

問題記錄

核心：根據(jù)配置文件中的配置項(xiàng)log-error，找到mysql的日志文件，從日志中發(fā)掘報錯信息，從而進(jìn)一步去解決問題。

linux的默認(rèn)配置文件位于 /etc/my.cnf

# 過濾空行和注釋行 grep -Ev "^$|[#;]" /etc/my.cnf

所以啟停mysql的時候，查看 /var/log/mysqld.log 即可

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)

開啟binlog后，重啟過程中碰到的問題一

配置開啟binlog的時候指定了 log-bin

開啟binlog后，重啟mysql報錯如下

[root@artisan ~]# tail -f /var/log/mysqld.log 2020-01-28T01:10:48.022822Z 0 [Note] /usr/sbin/mysqld: Shutdown complete2020-01-28T01:10:48.746119Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-01-28T01:10:48.748077Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 114966 ... mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory) 2020-01-28T01:10:48.750840Z 0 [ERROR] Aborting

沒有創(chuàng)建這個目錄，需要手工創(chuàng)建

mkdir -p /var/lib/mysql/data/binlog

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 13 - Permission denied)

開啟binlog后，重啟過程中碰到的問題二

2020-01-28T01:12:43.463345Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-01-28T01:12:43.467768Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 115213 ... mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 13 - Permission denied) 2020-01-28T01:12:43.472386Z 0 [ERROR] Aborting

上一步用root 建的 /var/lib/mysql/data/binlog 目錄，需要改成mysql用戶，修改用戶和用戶組

chown -R mysql /var/lib/mysql/data/ chgrp -R mysql /var/lib/mysql/data/

[ERROR] Unable to setup unix socket lock file.

開啟binlog后，重啟過程中碰到的問題三

2020-01-28T01:21:37.069836Z 0 [Note] Server socket created on IP: '::'. 2020-01-28T01:21:37.069929Z 0 [ERROR] Could not open unix socket lock file /var/lib/mysql/mysql.sock.lock. 2020-01-28T01:21:37.069944Z 0 [ERROR] Unable to setup unix socket lock file. 2020-01-28T01:21:37.069955Z 0 [ERROR] Aborting

mysql.sock.lock的用戶和用戶組改成mysql的

[root@artisan mysql]# chown mysql:mysql /var/lib/mysql/mysql.sock.lock

can’t read dir of ‘./artisan’ (errcode 13 - permission denied)

開啟binlog后，新建artisan數(shù)據(jù)庫，連接artisan,碰到的問題

調(diào)整用戶和用戶組權(quán)限

[root@artisan mysql]# chown -R mysql:mysql artisan/

重啟mysql

[root@artisan etc]# ps -ef|grep mysqld |grep -v grep root 7369 115008 0 14:30 pts/2 00:00:00 tail -f /var/log/mysqld.log mysql 7419 1 0 14:31 ? 00:00:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid [root@artisan etc]# [root@artisan etc]#

觀察日志 /var/log/mysqld.log 啟動正常。

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

創(chuàng)建復(fù)制賬戶的時候，密碼策略問題

參考：MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

更多見官方指導(dǎo)：6.4.3.2 Password Validation Plugin Options and Variables

主從測試

測試主從同步

主節(jié)點(diǎn) ，建表插入數(shù)據(jù)，測試自動同步

從節(jié)點(diǎn)，查看表和數(shù)據(jù)，

成功。

問題記錄

Slave_SQL_Running: No

瞎倒騰了一下，結(jié)果把SQL線程搞掛了。

看MYSQL的日志,從節(jié)點(diǎn)的表不存在

2020-01-28T13:18:21.874935Z 6 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'artisan.t_order' doesn't exist', Error_code: 1146 2020-01-28T13:18:21.875184Z 6 [Warning] Slave: Table 'artisan.t_order' doesn't exist Error_code: 1146 2020-01-28T13:18:21.875207Z 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000045' position 1308.

是這樣的，我同步的那個pos ,并不包含我建庫的腳本，所以報錯是正常的。

解決辦法：重新設(shè)置了同步關(guān)系，重啟線程

Master重新查看下

slave節(jié)點(diǎn)重新設(shè)置同步關(guān)系

重新查看

搞定MySQL

總結(jié)

以上是生活随笔為你收集整理的MySQL-主从架构的搭建的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MySQL-CentOS7通过YUM安装
下一篇： MySQL-性能优化_大表和大事务的常用