MySQL主從配置及mysqldump備份
MySQL的主從複製，是用來建立一個和主資料庫完全一樣的資料庫環境，從庫會同步主庫的所有資料，可輕鬆實現故障轉移。
1.1 MySQL主從主要作用
-
在業務複雜的系統中，有這麼一個情景，有一句sql語句需要鎖表，導致暫時不能使用讀的服務，那麼就很影響執行中的業務，使用主從複製，讓主庫負責寫，從庫負責讀，這樣，即使主庫出現了鎖表的情景，透過讀從庫也可以保證業務的正常執行。
-
做資料的熱備，主庫當機後能夠及時替換主庫，保證業務可用性。
-
1.2 常見MySQL主從架構
1.2.1MySQL主從複製的流程
-
主庫的更新事件(update、insert、delete)被寫到binlog；
-
從庫啟動併發起連線，連線到主庫；
-
主庫建立一個binlog dump thread，把binlog的內容傳送到從庫；
-
從庫啟動之後，建立一個I/O執行緒，讀取主庫傳過來的binlog內容並寫入到relay log；
-
從庫啟動之後，建立一個SQL執行緒，從relay log裡面讀取內容，從Exec_Master_Log_Pos位置開始執行讀取到的更新事件，將更新內容寫入到slave的資料庫。
1.2.2 MySQL主從複製的原理
MySQL主從複製是一個非同步的複製過程，主庫傳送更新事件到從庫，從庫讀取更新記錄，並執行更新記錄，使得從庫的內容與主庫保持一致。
binlog：binary log，主庫中儲存所有更新事件日誌的二進位制檔案。binary log是從資料庫服務啟動的一刻起，儲存資料庫所有變更記錄（資料庫結構和內容）的檔案。在主庫中，只要有更新事件出現，就會被依次地寫入到binary log中，之後會推送到從庫中作為從庫進行復制的資料來源。
binlog輸出執行緒：每當有從庫連線到主庫的時候，主庫都會建立一個執行緒然後傳送binlog內容到從庫。 對於每一個即將傳送給從庫的sql事件，binlog輸出執行緒會將其鎖住。一旦該事件被執行緒讀取完之後，該鎖會被釋放，即使在該事件完全傳送到從庫的時候，該鎖也會被釋放。
在從庫中，當複製開始時，從庫就會建立從庫I/O執行緒和從庫的SQL執行緒進行復制處理。
從庫I/O執行緒：當start slave語句在從庫開始執行之後，從庫建立一個I/O執行緒，該執行緒連線到主庫並請求主庫傳送binlog裡面的更新記錄到從庫上。 從庫I/O執行緒讀取主庫的binlog輸出執行緒傳送的更新並複製這些更新到本地檔案，其中包括relay log檔案。
從庫的SQL執行緒：從庫建立一個SQL執行緒，這個執行緒讀取從庫I/O執行緒寫到relay log的更新事件並執行。
綜上所述，可知：
對於每一個主從複製的連線，都有三個執行緒。擁有多個從庫的主庫為每一個連線到主庫的從庫建立一個binlog輸出執行緒，每一個從庫都有它自己的I/O執行緒和SQL執行緒。
從庫透過建立兩個獨立的執行緒，使得在進行復制時，從庫的讀和寫進行了分離。因此，即使負責執行的執行緒執行較慢，負責讀取更新語句的執行緒並不會因此變得緩慢。比如說，如果從庫有一段時間沒執行了，當它在此啟動的時候，儘管它的SQL執行緒執行比較慢，它的I/O執行緒可以快速地從主庫裡讀取所有的binlog內容。這樣一來，即使從庫在SQL執行緒執行完所有讀取到的語句前停止執行了，I/O執行緒也至少完全讀取了所有的內容，並將其安全地備份在從庫本地的relay log，隨時準備在從庫下一次啟動的時候執行語句。
1.3 MySQL主從部署
node3：master，192.168.48.183
node4：slave， 192.168.48.184
1.3.1 master端配置
# 安裝好MySQL/mariadb資料庫：
[root@node03 ~]# yum install mariadb mariadb-server -y
# 修改/etc/my.cnf配置檔案，在[MySQLd]指令段新增以下行：
log-bin=node3-bin
server-id=1
# 啟動資料庫服務：
[root@node03 ~]# systemctl start mariadb
[root@node03 ~]#
# 檢視MySQL程式：
[root@node03 ~]# ps -ef|grep MySQLd
MySQL 6130 1 0 20:37 ? 00:00:00 /bin/sh /usr/bin/MySQLd_safe --basedir=/usr
MySQL 6316 6130 0 20:37 ? 00:00:00 /usr/libexec/MySQLd --basedir=/usr --datadir=/var/lib/MySQL --plugin-dir=/usr/lib64/MySQL/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/MySQL/MySQL.sock
root 6365 5819 0 20:38 pts/0 00:00:00 grep --color=auto MySQLd
[root@node03 ~]#
# 檢視MySQL埠：
[root@node03 ~]# netstat -ntlp|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6316/MySQLd
[root@node03 ~]#
1.3.2 檢視配置是否生效
# 透過MySQL直接進入資料庫：
[root@node03 ~]# MySQL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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)]>
# 檢視log_bin和sql_log_bin是否均為on;
MariaDB [(none)]> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
| sql_log_bin | ON |
+---------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
1.3.3 授權從庫
MariaDB [(none)]> grant replication slave on *.* to "superman"@"192.168.48.184" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
1.3.4 檢視master狀態
xxxxxxxxxx
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| node3-bin.000004 | 479 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
1.3.5 slave端配置
# 修改/etc/my.cnf配置檔案，在[MySQLd]指令塊下新增如下行：
server-id=2
1.3.6 啟動slave資料庫服務
[root@node04 ~]# systemctl start mariadb
1.3.7 在slave資料庫上指定master
[root@node04 ~]# MySQL
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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)]>
MariaDB [(none)]> change master to
-> master_host="192.168.48.183",
-> master_user="superman",
-> master_password="123456",
-> master_log_file="node3-bin.000004",
-> master_log_pos=479;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]>
1.3.8 檢視slave狀態
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.48.183
Master_User: superman
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: node3-bin.000004
Read_Master_Log_Pos: 479
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: node3-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 479
Relay_Log_Space: 825
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
1 row in set (0.00 sec)
MariaDB [(none)]>
1.3.9 驗證資料同步
# 在主庫建立一個資料庫：
MariaDB [(none)]> create database zabbix charset=utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MySQL |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
# 在從庫檢視：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MySQL |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
1.3.10 同步錯誤分析
xxxxxxxxxx
Slave_IO_Running: Connecting
# 第一種：主庫當機
# 第二種：從庫指定的使用者名稱與密碼錯誤（與主庫授權的使用者名稱和密碼不一致）
# 第三種：關閉防火牆
Slave_IO_Running: No
# 從庫指定的二進位制檔案有誤
Slave_SQL_Running: No
# pos點問題
1.4 主從複製延遲問題及解決方法
1.4.1 從庫過多
建議從庫數量3-5 為宜，要複製的從節點數量過多，會導致複製延遲。
1.4.2 從庫硬體差
從庫硬體比主庫差，導致複製延遲，檢視master和slave的系統配置，可能會因為機器配置的問題，包括磁碟IO、CPU、記憶體等各方面因素造成複製的延遲，一般發生在高併發大資料量寫入場景。
1.4.3 網路問題
主從庫之間的網路延遲，主庫的網路卡、網線、連線的交換機等網路裝置都可能成為複製的瓶頸，導致複製延遲。
1.5 MySQLdump備份
1.5.1 只備份表，不備份資料本身
# 備份zabbix資料庫中的所有表，但是不會自動生成建立zabbix資料庫的語句：
[root@node03 ~]# MySQLdump -uroot -p*** zabbix > zabbix_tables.sql
[root@node03 ~]#
1.5.2 備份zabbix資料庫與表
備份zabbix資料庫中的所有表，並且會生成建立zabbix資料庫的SQL語句，也就是匯入時不需要先建立資料庫：
[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix > zabbix_database.sql
[root@node03 ~]#
1.5.3 備份多個資料庫
[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix MySQL > zabbix_MySQL_database.sql
[root@node03 ~]#
1.5.4 備份所有資料庫
[root@node03 ~]# MySQLdump -uroot -p*** --all-databases > all_databases.sql
[root@node03 ~]#
或者：
[root@node03 ~]# MySQLdump -uroot -p*** -A > all.sql
[root@node03 ~]#
1.5.5 備份zabbix資料庫，並且記錄pos點
[root@node03 ~]# MySQLdump -uroot -p*** --master-data zabbix > zabbix_pos.sql
[root@node03 ~]#
1.5.6 備份資料庫，並重新整理日誌
[root@node03 ~]# MySQLdump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql
[root@node03 ~]#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/291614/viewspace-2698245/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL備份與主備配置MySql
- 【mysqldump】mysqldump及備份恢復示例MySql
- Centos Mysql 主從備份CentOSMySql
- mysql5.6 mysqldump備份報錯MySql
- MySQL 高可用架構：主從備份及讀寫分離MySql架構
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- mysqldump備份技巧分享MySql
- Mysqldump的備份流程MySql
- MySQLDump的備份方法MySql
- redis主從備份Redis
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- MySQL主從配置及haproxy和keepalived搭建MySql
- mysql主從配置MySql
- windows mysqldump備份指令碼WindowsMySql指令碼
- 詳解MySQL資料備份之mysqldump使用方法MySql
- Mysqldump備份說明及資料庫備份指令碼分享-運維筆記MySql資料庫指令碼運維筆記
- 初探MySQL資料備份及備份原理MySql
- mysql 5.7主從配置MySql
- MySQL主從同步配置MySql主從同步
- docker mysql 主從配置DockerMySql
- MySQL 主從配置-之-一主一從MySql
- 簡單的mysqldump備份（windows）MySqlWindows
- mysqldump 資料庫備份程式MySql資料庫
- Mysqldump實現mysql的master-slave主從複製MySqlAST
- mysqldump備份單庫、部分庫、全庫、及排除部分庫MySql
- mysqldump使用方法(MySQL資料庫的備份與恢復)MySql資料庫
- mysql主從和主備的區別MySql
- Mysql 一主一從配置MySql
- MySQL雙主雙從配置MySql
- MySQL主從複製原理及必備知識總結MySql
- docker 配置 Mysql主從叢集DockerMySql
- mysql主從資料庫配置MySql資料庫
- 使用laradock配置mysql主從同步MySql主從同步
- mysql雙主雙從 搭建配置MySql
- MySQL常用操作和主從配置MySql
- mysql主從配置（清晰的思路）MySql
- MYSQL主從複製配置（整理）MySql
- mysqldump全量備份+mysqlbinlog二進位制日誌增量備份MySql