文章目錄
- 生猛幹貨
- 一主一從的搭建
-
- 前置工作
- 操作步驟
-
- 配置 master主伺服器
- Master的資料庫中建立一個複制帳戶并授權
- 配置 slave從伺服器
- 啟動從伺服器(slave)的複制線程
- 檢視從伺服器狀态
- binlog相關的指令
- 問題記錄
-
- 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實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試
- OS: CentOS 7
- DB：MySQL5.7
131和132已經按照MySQL-CentOS7通過YUM安裝MySQL5.7.29完成了MYSQL的安裝，并成功啟動。
核心配置： 開啟binlog , 指定唯一的server ID
mysql的配置檔案 /etc/my.cnf ,增加如下配置
#指定binlog的存儲位置，日志格式為二進制
log-bin=/var/lib/mysql/data/binlog/mysql-bin
#[必須]伺服器唯一ID，預設是1，多個mysql server，不重複即可
server-id=1
#每次執行寫入就與硬碟同步 (如果不配置這個,會等到緩沖區滿了自動刷盤, 安全和性能的權衡,配置為1是每次都會刷盤，自然慢一些，自己權衡)
sync-binlog=1
#需要同步的二進制資料庫名 (有多個的話，配置多個binlog-do-db)
binlog-do-db=artisan
#隻保留7天的二進制日志，以防磁盤被日志占滿
expire-logs-days=7
#不備份的資料庫(有多個的話，配置多個binlog-ignore-db)
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
#關閉名稱解析(非必須)
skip-name-resolve
全部的配置如下：
配置項解讀：
- log-bin
log-bin=/var/lib/mysql/data/binlog/mysql-bin
show binary logs;
可檢視
開啟bin-log，核心的配置
配置該項後，會自動生成 mysql-bin.index和mysql-bin.xxx的檔案server-id = [序列号] log-bin = [檔案名] MySQL-主從架構的搭建 - server-id： 在 MySQL 5.7.3 及以後版本,如果沒有設定server-id, 那麼設定binlog後無法開啟MySQL服務. (Bug #11763963, Bug #56739)
- sync-binlog ： 權衡一下刷盤的機制， 性能和安全之間權衡一下。
- binlog-do-db： 需要同步的資料庫 ，多個庫，配置多個binlog-do-db
- expire-logs-days： bin-log的保留時間
- binlog-ignore-db： 不同步的資料庫
檢視 bin-log的配置資訊
- File: 目前記錄bin-log的檔案
- Position: 從伺服器讀取的位置
- Binlog_Do_DB: 需要同步的資料庫
- Binlog_Ignore_DB: 忽略的資料庫，不同步
在 Master 的資料庫中建立一個備份帳戶(user = artisan4syn,pwd=artisan)：每個 slave 使用标準的 MySQL 使用者名和密碼連接配接 master 。
進行複制操作的使用者會授予 REPLICATION SLAVE 權限。
# @後面的IP段，建議僅對從服務的網段進行開放
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'; # *.* 所有資料庫的所有表
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';
重點關注 host user ,
這是啥意思呢？
意思是 授權使用者user 從哪台伺服器host能夠登入 ----------------> 主節點 （主節點上建立的使用者嘛） 允許 192.168網段的IP，通過 artisan4syn使用者通路 我主節點。
slave從節點主要的配置為：打開中繼日志，指定唯一的 servr ID，設定隻讀權限
從伺服器mysql的配置檔案/etc/my.cnf 檔案增加如下配置
#配置server-id，唯一ID号
server-id=2
#開啟從伺服器二進制日志 (從節點如果後面沒有級聯的從節點，binlog可以不打開，避免無謂的資源消耗)
#log_bin = /var/lib/mysql/data/binlog/mysql-bin
#[必須開啟]打開Mysql中繼日志，日志格式為二進制
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[從庫名]
#如果不是要全部同步[預設全部同步]，則指定需要同步的表
#replicate-wild-do-table=artisan01.t_order
#replicate-wild-do-table=artisan01.t_order_item
#設定隻讀權限
read_only = 1
#使得更新的資料寫進二進制日志中
log_slave_updates = 1 （這個是不是應該為 on 待确認 , 給GTID用的？ 5.7以下的版本 使用GTID必須要開啟 ）
- server-id ： 唯一id ,必須設定
- relay_log 中繼日志，必須設定。 結合主從同步的架構圖去了解就非常好了解了。
[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 MySQL-主從架構的搭建 [root@artisan ~]# cd /var/lib/mysql [root@artisan mysql]# chown mysql:mysql mysql.sock.lock MySQL-主從架構的搭建 - log_bin ： 從伺服器可選 ,開啟從伺服器二進制日志 (從節點如果後面沒有級聯的從節點，binlog可以不打開，避免無謂的資源消耗，如果後面還有個從節點，那就必須要開啟了，根據實際情況而定)
- replicate-do-db ： 如果salve庫名稱與master庫名相同，使用本配置
- replicate-rewrite-db ：master和slave資料庫不同時，需要做映射
主庫artisan 映射到兩個不同的資料庫，這種配置是不對的，因為一個binlog隻能播放一次 .replicate-rewrite-db = artisan -> artisan01 replicate-rewrite-db = artisan -> artisan02 -
replicate-wild-do-table ： 需要同步的表
注意事項：
如果也配置了同步的表， 優先使用這個配置。
如果master節點有個跨庫的查詢 ，比如一個查詢 關聯了DB1和DB2 ,這個時候如果想要同步到從節點，必須要配置 replicate-wild-do-table，指定具體的表
- read_only =1 : db隻可以讀，不可寫，但對root超級使用者來講，是不受隻讀設定影響的，是以，root還是可以往裡面去插入資料 . 如果我們換成其他的普通使用者就不會出現這樣的問題了。 要防止root可寫，設定
flush tables with read lock;
unlock tables;
-
log_slave_updates ： https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_slave_updates
預設的情況下log_slave_updates參數是關閉的，從伺服器從主伺服器接收到的更新不記入它的二進制日志。該選項告訴從伺服器将其SQL線程執行的更新記入到從伺服器自己的二進制日志。為了使該選項生效，還必須用–logs-bin選項啟動從伺服器以啟用二進制日志。如果想要應用鍊式複制伺服器，應使用–logs-slave-updates。例如，可能你想要這樣設定：
A -> B -> C
也就是說，A為從伺服器B的主伺服器，B為從伺服器C的主伺服器。為了能工作，B必須既為主伺服器又為從伺服器。你必須用–logs-bin啟動A和B以啟用二進制日志，并且用–logs-slave-updates選項啟動B。
1、從庫隻開啟log-bin功能，不添加log-slave-updates參數，從庫從主庫複制的資料不會寫入log-bin日志檔案裡。
2、直接向從庫寫入資料時，是會寫入log-bin日志的。
3、開啟log-slave-updates參數後，從庫從主庫複制的資料會寫入log-bin日志檔案裡。這也是該參數的功能。
開啟以後可以實作主主同步，切換。
slave 連接配接 master ，并開始重做 master 二進制日志中的事件
先到 master上執行下
在slave節點上執行如下指令：
[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.設定同步關系
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.啟動複制線程
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.
----》原因 ：在設定同步關系時，沒有先停止slaves線程。
----》解決辦法： 在設定同步關系之前， 先執行下“stop slave;”，以停止slave線程。然後啟動線程"start slave;"
參數說明：
mysql> change master to master_host='192.168.18.131', // master ip
>master_user='artisan4syn', // 建立的複制使用者
>master_password='artisan', // 建立的複制使用者的密碼
>master_log_file='mysql-bin.000045', //master對應的bin-log檔案
>master_log_pos=1308; //master對應的position
看看 mysql的log ,啟動了2個線程 。
可使用
SHOW SLAVE STATUS\G;
檢視從伺服器狀态,
也可用
show processlist \G;
檢視目前複制狀态
# 檢視檢視從節點狀态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: artisan4syn
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000045
Read_Master_Log_Pos: 1308
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000045
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: artisan
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1308
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 2a9b3cc0-4140-11ea-b350-000c29f66452
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
如何确定slave節點的兩個線程工作正常，看紅框，需要關注的幾個點
Slave_IO_Running: Yes //IO線程正常運作
Slave_SQL_Running: Yes //SQL線程正常運作
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 3
User: root
Host: 192.168.18.1:61080
db: NULL
Command: Sleep
Time: 2549
State:
Info: NULL
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 3. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 784
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 784
State: Slave has read all relay log; waiting for more updates
Info: NULL
4 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
# 檢視是否開啟binlog
mysql>show binary logs;
#檢視binlog格式：
mysql>show variables like 'binlog_format';
#擷取binlog檔案清單：
mysql>show binary logs;
#檢視目前正在寫入的binlog檔案：
mysql>show master status;
#檢視master上的binlog：
mysql>show master logs;
#隻檢視第一個binlog檔案的内容：
mysql>show binlog events;
#檢視指定binlog檔案的内容：
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
核心：根據配置檔案中的配置項log-error，找到mysql的日志檔案，從日志中發掘報錯資訊，進而進一步去解決問題。
linux的預設配置檔案位于 /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 complete
2020-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
沒有建立這個目錄， 需要手工建立
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/
開啟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資料庫，連接配接artisan,碰到的問題
調整使用者和使用者組權限
[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
啟動正常。
建立複制賬戶的時候，密碼政策問題
參考：MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
更多見官方指導：6.4.3.2 Password Validation Plugin Options and Variables
主節點 ，建表插入資料，測試自動同步
從節點，檢視表和資料，
成功。
瞎倒騰了一下，結果把SQL線程搞挂了。
看MYSQL的日志,從節點的表不存在
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 ,并不包含我建庫的腳本，是以報錯是正常的 。
解決辦法： 重新設定了同步關系，重新開機線程
Master重新檢視下
slave節點重新設定同步關系
重新檢視