MySQL實現實時備份的配置過程詳解

MySQL實現實時備份沒有做過做過最多的就是定時備份了，我們今天一起來看看關於MySQL實現實時備份的配置過程了，希望例子能夠幫助到各位。

前面筆者在其他隨筆中有說過，公司數據庫選用的是免安裝版並且是在Windows上，等過段時間我會將數據庫遷移至Linux

因公司開發操作數據庫之多，如不及時備份，萬一操作失誤，恢復可就成難題了

此篇隨筆將介紹如何進行 MySQL 的實時備份

 

環境介紹

 

兩台服務器進行主從同步復制（安裝可以通過google查詢相關資料或者看筆者的這篇免安裝版）

利用Windows腳本和Windows自帶的計劃任務，將主服務器進行實時備份（下面進行演示）

Maseter MySQL: 192.168.1.253

Slave    MySQL: 192.168.1.254

在主服務器上開啟二進制日志功能，設置唯一的服務器ID編號，這些設置需要重啟MySQL服務

在從服務器上設置唯一的服務器ID編號，這些設置需要重啟MySQL服務

在主服務器上為了不用從服務器創建可以讀取主服務器日志文件的用戶，或使用相同的統一用戶

在進行數據復制之前，需要記錄主服務器上二進制文件的位置的標記

在進行數據復制之前，保證從服務器和主服務器上的數據一致

回到頂部

 

主服務器的配置

在實際的環境中，可能在我們還沒有部署數據庫的同步前，數據庫中就已經存在大量的數據。所以，在操作數據庫的時候一定記住備份

mysqldump -u root -p --all-databases --lock-all-tables > G:/dbdump.sql
Enter password: ******

我們需要在主服務器上開啟二進制日志並設置服務器編號，服務器唯一編號是2的32次方減1之間的整數，根據自己的實際情況而設置

進行這些設置需要關閉MySQL數據庫並編輯my.ini或者my.cnf文件，然後在[mysqld]是指段添加相應的配置選項

C:\Users\Administrator>net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止。 
[mysqld]
log-bin=mysql-bin
server-id=1 
C:\Users\Administrator>net start mysql
MySQL 服務正在啟動 .
MySQL 服務已經啟動成功。
為了使從服務器能夠同步復制，我們需要創建一個同步復制的用戶

執行數據復制時，所有的從服務器都需要使用用戶與密碼連接MySQL主服務器，所以在主服務器上必須存在至少一個用戶及相應的密碼提供從服務器來連接

但是這個用戶必須擁有"REPLICATION SLAVE" 權限，當然你可以給不同的從服務器創建不用的用戶與密碼，也可以使用統一的用戶與密碼

如果該用戶僅為數據庫復制所使用，則該用戶僅需要"REPLICATION SLAVE"權限即可

C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, 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> CREATE USER 'slave'@'%' IDENTIFIED BY 'slaveAdmin';
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye
獲取主服務器二進制日志信息

 首先我們來了解一下二進制文件的基本信息，這些信息在對從服務器的設置需要用到，它包括服務器二進制文件名稱及當前日志記錄位置，這樣從服務器就可以知道哪裡開始進行復制操作

當我們得到二進制文件名以及二進制當前記錄的位置時請牢記，在從服務器上會用到

從服務器的配置

 和主服務器一樣，需要配置my.ini或者my.cnf 文件，注意這裡的從服務器ID編號

C:\Users\Administrator>net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止。 
[mysqld]
server-id=2 
C:\Users\Administrator>net start mysql
MySQL 服務正在啟動 .
MySQL 服務已經啟動成功。

對於復制而言，MySQL從服務器二進制功能是不需要開啟的，當然也可以開啟從服務器上的二進制功能來實現數據備份與恢復

•在介紹裡面我已經說到在進行數據復制之前，保證從服務器和主服務器上的數據一致，還記得我剛才將主服務器的MySQL進行備份嗎？！ 可以將這備份還原到從服務器上面，這樣主從的數據就是一樣的了

 

mysql -u root -p < D:/dbdump.sql

 

Enter password: ******

 

•配置從服務器連接主服務器進行數據復制

 

1.其實數據復制的關鍵操作是配置從服務器去連接主服務器進行數據復制，我們需要告訴從服務器建立網絡連接所有必要的信息

2.使用 CHANGE MASTER TO 語句完成與主服務器的連接工作

 

　　　† MASTER_HOST 指定主服務器主機名或者Ip地址

 

　　　† MASTER_USER 為剛才在主服務器上創建的擁有復制權限的用戶

 

　　　† MASTER_PASSWORD 為改用戶的秘密

 

　　　†  MASTER_LOG_FILE 指定主服務器二進制日志文件名稱

 

　　　† MASTER_LOG_POS 指定主服務器二進制日志文件當前的位置

1.START LSAVE 開啟從服務器功能進行主從連接

2.SHOW SLAVE STATUS 查看從服務器狀態

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.253',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='slaveAdmin',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=9876212;
Query OK, 0 rows affected (0.00 sec) 
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
查看狀態時，下面兩個都要為 YES 說明才是正常運行狀態，如果IO為No 則檢查密碼或者從新寫入

查看狀態時，下面兩個都要為 YES 說明才是正常運行狀態，如果IO為No 則檢查密碼或者從新寫入

Slave_IO_Running:

Slave_SQL_Running:

現在當我們在主服務器上創建一個數據庫時，我們再到從服務器上查看，這時就可以看到已經把主服務器上新創建數據庫的同步過來了

mysql> create database vforbox;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| vforbox            |
+--------------------+
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| vforbox            |
+--------------------+
Windows 定時備份數據庫腳本

上面介紹中已經提到用Windows腳本和Windows自帶的計劃任務，將主服務器進行實時備份

這裡筆者獻上代碼，如有更好的腳本推薦請發至筆者的郵箱

新建一個 "mysql_auto_bak.bat"

@echo off
cls
color 3E
title %date% %time:~,5% 備份MySQL數據庫 By:vforobx
::::::::::::::::::::::::以下是需要配置的參數::::::::::::::::::::::::::::::::::::::::
 
        rem 設置 MySQL服務器root賬號的密碼,特殊符號需要在其前添加兩個^
        SET MySQL_pw=123456
        rem 設置 數據庫備份目錄
        SET BAK_dir=D:\mysqlbak
        rem 設置 需要備份的myisam格式數據庫
        SET BAK_db_myisam=myisam_db
        rem 設置 需要備份的innodb格式數據庫
        SET BAK_db_innodb=innodb
        rem 設置 WinRAR壓縮軟件的路徑
        SET RAR_dir="C:\Program files (x86)\WinRAR\WinRAR.exe"
        rem 設置 以2015-10-01格式的日期為子目錄
        SET BAK_dir2=%date:~0,4%-%date:~5,2%-%date:~8,2%
        rem 設置 備份文件名
        SET BAK_file=%%i_%BAK_dir2%.sql
        rem 設置日志文件裡面指向的rar文件路徑
        SET BAK_file2=%%i_%BAK_dir2%
        rem 設置 日志文件名
        SET LOG_file=%BAK_dir%\%BAK_dir2%\Mysql_bak.log
 
::::::::::::::::::::::::以上是需要配置的參數::::::::::::::::::::::::::::::::::::::::
 
::::::::::::::::::::::::以下是判斷變量是否定義::::::::::::::::::::::::::::::::::::::
 
if not defined MySQL_pw (echo MySQL_pw 尚未定義！)
if not defined BAK_dir (echo BAK_dir 尚未定義！)
if not defined RAR_dir (RAR_dir 尚未定義！)
 
::::::::::::::::::::::::以下是記錄時間日志::::::::::::::::::::::::::::::::::::::::::
 
if not defined BAK_db_myisam (goto innodb)
echo. 開始以當前日期創建文件夾
if not exist %BAK_dir%\%BAK_dir2% md %BAK_dir%\%BAK_dir2%
cd /d %BAK_dir%\%BAK_dir2%
echo. 開始建立(%BAK_dir2%)的備份
::::::::::::::::::::::::以下是備份的核心代碼::::::::::::::::::::::::::::::::::::::::
 
echo 備份時間: %BAK_dir2% %time:~0,8%  >> %LOG_file%
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> %LOG_file%
SETLocal DisableDelayedExpansion
for %%i in (%BAK_db_myisam%) do (
mysqldump -h 192.168.1.253 -uroot -p%MySql_pw% --all-databases >%BAK_file%
%RAR_Dir% a %BAK_file:~0,-4%.rar %BAK_file%
DEL /F /A /Q %BAK_file%
echo 數據庫【%%i 格式】已經備份到: %BAK_dir%\%BAK_dir2%\%BAK_file2%.rar >> %LOG_file%)
::::::::::::::::::::::::以下是 innodb格式的備份代碼::::::::::::::::::::::::::::::::::
 
if not defined BAK_db_innodb (goto exitbat)
echo. 開始以當前日期創建文件夾
if not exist %BAK_dir%\%BAK_dir2% md %BAK_dir%\%BAK_dir2%
cd /d %BAK_dir%\%BAK_dir2%
echo. 開始建立今天(%BAK_dir2%)的備份
SETLocal DisableDelayedExpansion
for %%i in (%BAK_db_innodb%) do (
mysqldump -h 192.168.1.253 -uroot -p%MySQL_pw% --all-databases >%BAK_file% 
%RAR_dir% a %BAK_file:~0,-4%.rar %BAK_file%
DEL /F /A /Q %BAK_file%
echo 數據庫【%%i 格式】已經備份到：%BAK_dir%\%BAK_dir2%\%BAK_file2%.rar >> %LOG_file%)
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> %LOG_file%
echo. 所有備份建立完畢
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
rem 清除變量
        SET MySQL_pw=
        SET BAK_dir=
        SET RAR_dir=
        SET BAK_dir2=
        SET BAK_file=
        SET BAK_file2
        SET LOG_file=