MySQL主從配置及mysqldump備份的步驟

本篇內(nèi)容主要講解“MySQL主從配置及mysqldump備份的步驟”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL主從配置及mysqldump備份的步驟”吧!

1. MySQL主從概念

MySQL的主從復(fù)制，是用來建立一個(gè)和主數(shù)據(jù)庫完全一樣的數(shù)據(jù)庫環(huán)境，從庫會(huì)同步主庫的所有數(shù)據(jù)，可輕松實(shí)現(xiàn)故障轉(zhuǎn)移。

1.1 MySQL主從主要作用

• 在業(yè)務(wù)復(fù)雜的系統(tǒng)中，有這么一個(gè)情景，有一句sql語句需要鎖表，導(dǎo)致暫時(shí)不能使用讀的服務(wù)，那么就很影響運(yùn)行中的業(yè)務(wù)，使用主從復(fù)制，讓主庫負(fù)責(zé)寫，從庫負(fù)責(zé)讀，這樣，即使主庫出現(xiàn)了鎖表的情景，通過讀從庫也可以保證業(yè)務(wù)的正常運(yùn)行。

• 做數(shù)據(jù)的熱備，主庫宕機(jī)后能夠及時(shí)替換主庫，保證業(yè)務(wù)可用性。

• 架構(gòu)的擴(kuò)展。業(yè)務(wù)量越來越大，I/O訪問頻率過高，單機(jī)無法滿足，此時(shí)做多庫的存儲(chǔ)，降低磁盤I/O訪問的頻率，提高單個(gè)機(jī)器的I/O性能。

1.2 常見MySQL主從架構(gòu)

1.2.1MySQL主從復(fù)制的流程

1. 主庫的更新事件(update、insert、delete)被寫到binlog；

2. 從庫啟動(dòng)并發(fā)起連接，連接到主庫；

3. 主庫創(chuàng)建一個(gè)binlog dump thread，把binlog的內(nèi)容發(fā)送到從庫；

4. 從庫啟動(dòng)之后，創(chuàng)建一個(gè)I/O線程，讀取主庫傳過來的binlog內(nèi)容并寫入到relay log；

5. 從庫啟動(dòng)之后，創(chuàng)建一個(gè)SQL線程，從relay log里面讀取內(nèi)容，從Exec_Master_Log_Pos位置開始執(zhí)行讀取到的更新事件，將更新內(nèi)容寫入到slave的數(shù)據(jù)庫。

1.2.2 MySQL主從復(fù)制的原理

MySQL主從復(fù)制是一個(gè)異步的復(fù)制過程，主庫發(fā)送更新事件到從庫，從庫讀取更新記錄，并執(zhí)行更新記錄，使得從庫的內(nèi)容與主庫保持一致。

binlog：binary log，主庫中保存所有更新事件日志的二進(jìn)制文件。binary log是從數(shù)據(jù)庫服務(wù)啟動(dòng)的一刻起，保存數(shù)據(jù)庫所有變更記錄（數(shù)據(jù)庫結(jié)構(gòu)和內(nèi)容）的文件。在主庫中，只要有更新事件出現(xiàn)，就會(huì)被依次地寫入到binary log中，之后會(huì)推送到從庫中作為從庫進(jìn)行復(fù)制的數(shù)據(jù)源。

binlog輸出線程：每當(dāng)有從庫連接到主庫的時(shí)候，主庫都會(huì)創(chuàng)建一個(gè)線程然后發(fā)送binlog內(nèi)容到從庫。 對(duì)于每一個(gè)即將發(fā)送給從庫的sql事件，binlog輸出線程會(huì)將其鎖住。一旦該事件被線程讀取完之后，該鎖會(huì)被釋放，即使在該事件完全發(fā)送到從庫的時(shí)候，該鎖也會(huì)被釋放。

在從庫中，當(dāng)復(fù)制開始時(shí)，從庫就會(huì)創(chuàng)建從庫I/O線程和從庫的SQL線程進(jìn)行復(fù)制處理。

從庫I/O線程：當(dāng)start slave語句在從庫開始執(zhí)行之后，從庫創(chuàng)建一個(gè)I/O線程，該線程連接到主庫并請(qǐng)求主庫發(fā)送binlog里面的更新記錄到從庫上。 從庫I/O線程讀取主庫的binlog輸出線程發(fā)送的更新并拷貝這些更新到本地文件，其中包括relay log文件。

從庫的SQL線程：從庫創(chuàng)建一個(gè)SQL線程，這個(gè)線程讀取從庫I/O線程寫到relay log的更新事件并執(zhí)行。

綜上所述，可知：

對(duì)于每一個(gè)主從復(fù)制的連接，都有三個(gè)線程。擁有多個(gè)從庫的主庫為每一個(gè)連接到主庫的從庫創(chuàng)建一個(gè)binlog輸出線程，每一個(gè)從庫都有它自己的I/O線程和SQL線程。

從庫通過創(chuàng)建兩個(gè)獨(dú)立的線程，使得在進(jìn)行復(fù)制時(shí)，從庫的讀和寫進(jìn)行了分離。因此，即使負(fù)責(zé)執(zhí)行的線程運(yùn)行較慢，負(fù)責(zé)讀取更新語句的線程并不會(huì)因此變得緩慢。比如說，如果從庫有一段時(shí)間沒運(yùn)行了，當(dāng)它在此啟動(dòng)的時(shí)候，盡管它的SQL線程執(zhí)行比較慢，它的I/O線程可以快速地從主庫里讀取所有的binlog內(nèi)容。這樣一來，即使從庫在SQL線程執(zhí)行完所有讀取到的語句前停止運(yùn)行了，I/O線程也至少完全讀取了所有的內(nèi)容，并將其安全地備份在從庫本地的relay log，隨時(shí)準(zhǔn)備在從庫下一次啟動(dòng)的時(shí)候執(zhí)行語句。

1.3 MySQL主從部署

node3：master，192.168.48.183
node4：slave， 192.168.48.184

1.3.1 master端配置

# 安裝好MySQL/mariadb數(shù)據(jù)庫：
[root@node03 ~]# yum install mariadb mariadb-server -y
# 修改/etc/my.cnf配置文件，在[MySQLd]指令段添加以下行：
log-bin=node3-bin
server-id=1

# 啟動(dòng)數(shù)據(jù)庫服務(wù)：
[root@node03 ~]# systemctl start mariadb
[root@node03 ~]# 
# 查看MySQL進(jìn)程：
[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直接進(jìn)入數(shù)據(jù)庫：
[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 授權(quán)從庫

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狀態(tài)

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 啟動(dòng)slave數(shù)據(jù)庫服務(wù)

[root@node04 ~]# systemctl start mariadb

1.3.7 在slave數(shù)據(jù)庫上指定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狀態(tài)

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 驗(yàn)證數(shù)據(jù)同步

# 在主庫創(chuàng)建一個(gè)數(shù)據(jù)庫：
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 同步錯(cuò)誤分析

xxxxxxxxxx
Slave_IO_Running: Connecting
# 第一種：主庫宕機(jī)
# 第二種：從庫指定的用戶名與密碼錯(cuò)誤（與主庫授權(quán)的用戶名和密碼不一致）
# 第三種：關(guān)閉防火墻
Slave_IO_Running: No
# 從庫指定的二進(jìn)制文件有誤
Slave_SQL_Running: No
# pos點(diǎn)問題

1.4 主從復(fù)制延遲問題及解決方法

1.4.1 從庫過多

建議從庫數(shù)量3-5 為宜，要復(fù)制的從節(jié)點(diǎn)數(shù)量過多，會(huì)導(dǎo)致復(fù)制延遲。

1.4.2 從庫硬件差

從庫硬件比主庫差，導(dǎo)致復(fù)制延遲，查看master和slave的系統(tǒng)配置，可能會(huì)因?yàn)闄C(jī)器配置的問題，包括磁盤IO、CPU、內(nèi)存等各方面因素造成復(fù)制的延遲，一般發(fā)生在高并發(fā)大數(shù)據(jù)量寫入場景。

1.4.3 網(wǎng)絡(luò)問題

主從庫之間的網(wǎng)絡(luò)延遲，主庫的網(wǎng)卡、網(wǎng)線、連接的交換機(jī)等網(wǎng)絡(luò)設(shè)備都可能成為復(fù)制的瓶頸，導(dǎo)致復(fù)制延遲。

1.5  MySQLdump備份

1.5.1 只備份表，不備份數(shù)據(jù)本身

# 備份zabbix數(shù)據(jù)庫中的所有表，但是不會(huì)自動(dòng)生成創(chuàng)建zabbix數(shù)據(jù)庫的語句：
[root@node03 ~]# MySQLdump -uroot -p*** zabbix > zabbix_tables.sql
[root@node03 ~]#

1.5.2 備份zabbix數(shù)據(jù)庫與表

備份zabbix數(shù)據(jù)庫中的所有表，并且會(huì)生成創(chuàng)建zabbix數(shù)據(jù)庫的SQL語句，也就是導(dǎo)入時(shí)不需要先創(chuàng)建數(shù)據(jù)庫：
[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix > zabbix_database.sql
[root@node03 ~]#

1.5.3 備份多個(gè)數(shù)據(jù)庫

[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix MySQL > zabbix_MySQL_database.sql
[root@node03 ~]#

1.5.4 備份所有數(shù)據(jù)庫

[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數(shù)據(jù)庫，并且記錄pos點(diǎn)

[root@node03 ~]# MySQLdump -uroot -p*** --master-data zabbix > zabbix_pos.sql
[root@node03 ~]#

1.5.6  備份數(shù)據(jù)庫，并刷新日志

[root@node03 ~]# MySQLdump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql
[root@node03 ~]#

到此，相信大家對(duì)“MySQL主從配置及mysqldump備份的步驟”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！