MySQL 即時備份

標籤：

閱讀目錄

• 環境介紹 
• 主伺服器的配置
• 從伺服器的配置
• Windows 定時備份資料庫指令碼

 

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

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

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

環境介紹

1. 兩台伺服器進行主從同步複製（安裝可以通過google查詢相關資料或者看筆者的這篇免安裝版）
2. 利用Windows指令碼和Windows內建的計劃任務，將主伺服器進行即時備份（下面進行示範）

• Maseter MySQL: 192.168.1.253
• Slave    MySQL: 192.168.1.254

1. 在主伺服器上開啟二進位日誌功能，設定唯一的伺服器ID編號，這些設定需要重啟MySQL服務
2. 在從伺服器上設定唯一的伺服器ID編號，這些設定需要重啟MySQL服務
3. 在主伺服器上為了不用從伺服器建立可以讀取主伺服器記錄檔的使用者，或使用相同的統一使用者
4. 在進行資料複製之前，需要記錄主伺服器上二進位檔案的位置的標記
5. 在進行資料複製之前，保證從伺服器和主伺服器上的資料一致

回到頂部主伺服器的配置

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

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

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

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

C:\Users\Administrator>net stop mysqlMySQL 服務正在停止.MySQL 服務已成功停止。

[mysqld]log-bin=mysql-binserver-id=1

C:\Users\Administrator>net start mysqlMySQL 服務正在啟動 .MySQL 服務已經啟動成功。

• 為了使從伺服器能夠同步複製，我們需要建立一個同步複製的使用者

1. 執行資料複製時，所有的從伺服器都需要使用使用者與密碼串連MySQL主伺服器，所以在主伺服器上必須存在至少一個使用者及相應的密碼提供從伺服器來串連
2. 但是這個使用者必須擁有"REPLICATION SLAVE" 許可權，當然你可以給不同的從伺服器建立不用的使用者與密碼，也可以使用統一的使用者與密碼
3. 如果該使用者僅為資料庫複寫所使用，則該使用者僅需要"REPLICATION SLAVE"許可權即可

C:\Users\Administrator>mysql -u root -pEnter password: ******Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye

• 擷取主伺服器二進位日誌資訊

1.  首先我們來瞭解一下二進位檔案的基本資料，這些資訊在對從伺服器的設定需要用到，它包括伺服器二進位檔案名稱及當前日誌記錄位置，這樣從伺服器就可以知道哪裡開始進行複製操作
2. 當我們得到二進位檔案名以及二進位目前記錄的位置時請牢記，在從伺服器上會用到

mysql> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.00 sec)mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |  9876212 |              |                  |    |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

1. File列顯示的是二進位記錄檔名，Position為當前日誌記錄位置
2. FLUSH TABLES WITH READ LOCK 命令的作用是對所有資料庫的表執行唯讀鎖定，當唯讀鎖定後所有資料庫寫操作都將被拒絕，但讀操作可以繼續
3. 執行鎖定可以防止在查看二進位日誌資訊的同時有人對操作進行修改操作，最後使用 UNLOCK TABLES 命令對全域鎖執行結束操作

回到頂部從伺服器的配置

•  和主伺服器一樣，需要配置my.ini或者my.cnf 檔案，注意這裡的從伺服器ID編號

C:\Users\Administrator>net stop mysqlMySQL 服務正在停止.MySQL 服務已成功停止。

[mysqld]server-id=2

C:\Users\Administrator>net start mysqlMySQL 服務正在啟動 .MySQL 服務已經啟動成功。

對於複製而言，MySQL從伺服器二進位功能是不需要開啟的，當然也可以開啟從伺服器上的二進位功能來實現資料備份與恢複

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

mysql -u root -p < D:/dbdump.sqlEnter 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 則檢查密碼或者從新寫入

1. Slave_IO_Running: 
2. 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 offclscolor 3Etitle %date% %time:~,5% 備份MySQL資料庫 By:vforobx::::::::::::::::::::::::以下是需要配置的參數::::::::::::::::::::::::::::::::::::::::        rem 設定 MySQL伺服器root帳號的密碼,特殊符號需要在其前添加兩個^        SET MySQL_pw=123456        rem 設定 Database Backup目錄        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 DisableDelayedExpansionfor %%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 DisableDelayedExpansionfor %%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=

• 如果覺得複製麻煩-點擊下載

然後將指令碼加入Windwos內建計劃任務，這裡筆者將不示範如何建立計劃任務了，如需協助請自行google　　

 

MySQL 即時備份