【MySQL】MySQL備份和恢復

一、為什麼要備份資料

在生產環境中我們資料庫可能會遭遇各種各樣的不測從而導致資料丟失, 大概分為以下幾種.

• 硬體故障

• 軟體故障

• 自然災害

• 黑客攻擊

• 誤操作 (佔比最大)

所以, 為了在資料丟失之後能夠恢復資料, 我們就需要定期的備份資料, 備份資料的策略要根據不同的應用場景進行定製, 大致有幾個參考數值, 我們可以根據這些數值從而定製符合特定環境中的資料備份策略

• 能夠容忍丟失多少資料

• 恢復資料需要多長時間

• 需要恢復哪一些資料

 

二、資料的備份型別

資料的備份型別根據其自身的特性主要分為以下幾組

• 完全備份

• 部分備份

  完全備份指的是備份整個資料集( 即整個資料庫 )、部分備份指的是備份部分資料集(例如: 只備份一個表)

而部分備份又分為以下兩種

• 增量備份

• 差異備份

  增量備份指的是備份自上一次備份以來(增量或完全)以來變化的資料; 特點: 節約空間、還原麻煩 
  差異備份指的是備份自上一次完全備份以來變化的資料 特點: 浪費空間、還原比增量備份簡單

示意圖如下：

 

三、MySQL備份資料的方式

在MySQl中我們備份資料一般有幾種方式

• 熱備份

• 溫備份

• 冷備份

  熱備份指的是當資料庫進行備份時, 資料庫的讀寫操作均不是受影響 
  溫備份指的是當資料庫進行備份時, 資料庫的讀操作可以執行, 但是不能執行寫操作 
  冷備份指的是當資料庫進行備份時, 資料庫不能進行讀寫操作, 即資料庫要下線

MySQL中進行不同方式的備份還要考慮儲存引擎是否支援

• MyISAM 

   熱備 ×

   溫備 √

   冷備 √

• InnoDB

   熱備 √

   溫備 √

   冷備 √

  我們在考慮完資料在備份時, 資料庫的執行狀態之後還需要考慮對於MySQL資料庫中資料的備份方式

  物理備份一般就是通過tar,cp等命令直接打包複製資料庫的資料檔案達到備份的效果 
  邏輯備份一般就是通過特定工具從資料庫中匯出資料並另存備份(邏輯備份會丟失資料精度)

• 物理備份

• 邏輯備份

 

四、備份需要考慮的問題

定製備份策略前, 我們還需要考慮一些問題

我們要備份什麼?

一般情況下, 我們需要備份的資料分為以下幾種

• 資料

• 二進位制日誌, InnoDB事務日誌

• 程式碼(儲存過程、儲存函式、觸發器、事件排程器)

• 伺服器配置檔案

備份工具

這裡我們列舉出常用的幾種備份工具 
mysqldump : 邏輯備份工具, 適用於所有的儲存引擎, 支援溫備、完全備份、部分備份、對於InnoDB儲存引擎支援熱備 
cp, tar 等歸檔複製工具: 物理備份工具, 適用於所有的儲存引擎, 冷備、完全備份、部分備份 
lvm2 snapshot: 幾乎熱備, 藉助檔案系統管理工具進行備份 
mysqlhotcopy: 名不副實的的一個工具, 幾乎冷備, 僅支援MyISAM儲存引擎 
xtrabackup: 一款非常強大的InnoDB/XtraDB熱備工具, 支援完全備份、增量備份, 由percona提供

 

五、設計合適的備份策略

針對不同的場景下, 我們應該制定不同的備份策略對資料庫進行備份, 一般情況下, 備份策略一般為以下三種

• 直接cp,tar複製資料庫檔案

• mysqldump+複製BIN LOGS

• lvm2快照+複製BIN LOGS

• xtrabackup

以上的幾種解決方案分別針對於不同的場景

1. 如果資料量較小, 可以使用第一種方式, 直接複製資料庫檔案

2. 如果資料量還行, 可以使用第二種方式, 先使用mysqldump對資料庫進行完全備份, 然後定期備份BINARY LOG達到增量備份的效果

3. 如果資料量一般, 而又不過分影響業務執行, 可以使用第三種方式, 使用lvm2的快照對資料檔案進行備份, 而後定期備份BINARY LOG達到增量備份的效果

4. 如果資料量很大, 而又不過分影響業務執行, 可以使用第四種方式, 使用xtrabackup進行完全備份後, 定期使用xtrabackup進行增量備份或差異備份

 

六、實戰演練

1.使用cp進行備份

 #檢視資料庫資訊

mysql> SHOW DATABASES;    #檢視當前的資料庫, 我們的資料庫為employees
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monkey             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use monkey
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_monkey |
+------------------+
| locations |
| pseudohash |
| t1 |
| t2 |
| unsent_emails |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from t1;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | Jelly | 28 |
| 2 | Jerry | 30 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql>

#向資料庫施加讀鎖

mysql> FLUSH TABLES WITH READ LOCK;    #向所有表施加讀鎖
Query OK, 0 rows affected (0.00 sec)

#備份資料檔案

[root@A04-Test-172 ~]# mkdir /root/backup                         #建立資料夾存放備份資料庫檔案
[root@A04-Test-172 ~]# cp -a /usr/local/mysql/* /root/backup/     #保留許可權的拷貝源資料檔案
[root@A04-Test-172 ~]# ls /root/backup                            #檢視目錄下的檔案
total 122916
-rw-r----- 1 mysql mysql 56 Jan 5 17:03 auto.cnf
-rw-r----- 1 mysql mysql 373 Jan 24 14:15 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 5 11:08 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 5 11:08 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 5 17:03 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 5 11:08 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Feb 5 11:08 monkey
drwxr-x--- 2 mysql mysql 4096 Jan 5 17:03 mysql
-rw-r----- 1 mysql mysql 6 Jan 24 14:15 mysqld.pid
drwxr-x--- 2 mysql mysql 4096 Jan 5 17:03 performance_schema
drwxr-x--- 2 mysql mysql 12288 Jan 5 17:03 sys

#模擬資料丟失並恢復

[root@A04-Test-172 ~]# rm -rf /usr/local/mysql/data/*    #刪除資料庫的所有檔案
[root@A04-Test-172 ~]# service mysqld restart            #重啟MySQL, 如果是編譯安裝的應該不能啟動, 如果rpm安裝則會重新初始化資料庫
mysql> show databases;                                   #發現資料丟失了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
[root@A04-Test-172 backup]# cp -a /root/mysqldumpbackup/backup.sql  /usr/local/mysql/data/    #將備份資料拷貝回去
[root@A04-Test-172 ~]# service mysqld restart            #重啟MySQL，
mysql> show databases;                         #檢視資料是否恢復
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

mysql> use monkey
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_monkey |
+------------------+
| locations |
| pseudohash |
| t1 |
| t2 |
| unsent_emails |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from t1;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | Jelly | 28 |
| 2 | Jerry | 30 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql>

 

2.使用mysqldump+複製BINARY LOG備份

我們通過mysqldump進行一次完全備份, 再修改表中的資料, 然後再通過binary log進行恢復。

二進位制日誌需要在mysql配置檔案中新增 log_bin=on 開啟。

[root@A04-Test-172 backup]# more /etc/my.cnf
[client]  
port = 3306  
socket = /tmp/mysql.sock  
  
[mysqld]  
user=mysql  
port = 3306  
server_id = 1  
socket=/tmp/mysql.sock  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data  
pid-file=/usr/local/mysql/data/mysqld.pid  
log-error=/usr/local/mysql/log/mysql-error.log
log-bin=on                  #開啟二進位制日誌

mysqldump命令介紹

mysqldump是一個客戶端的邏輯備份工具, 可以生成一個重現建立原始資料庫和表的SQL語句, 可以支援所有的儲存引擎, 對於InnoDB支援熱備。

#基本語法格式

shell> mysqldump [options] db_name [tbl_name ...]    恢復需要手動CRATE DATABASES
shell> mysqldump [options] --databases db_name ...   恢復不需要手動建立資料庫
shell> mysqldump [options] --all-databases           恢復不需要手動建立資料庫


其他選項:
     -E, --events: 備份事件排程器
     -R, --routines: 備份儲存過程和儲存函式
     --triggers: 備份表的觸發器; --skip-triggers 
     --master-date[=value]  
         1: 記錄為CHANGE MASTER TO 語句、語句不被註釋
         2: 記錄為註釋的CHANGE MASTER TO語句
         基於二進位制還原只能全庫還原

     --flush-logs: 日誌滾動
         鎖定表完成後執行日誌滾動

檢視資料庫資訊

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use monkey;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_monkey |
+------------------+
| locations        |
| pseudohash       |
| t1               |
| t2               |
| unsent_emails    |
+------------------+
5 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

使用mysqldump備份資料庫

[root@A04-Test-172 data]# mysql -uroot -p -e 'SHOW MASTER STATUS'               #檢視二進位制檔案的狀態，並記錄Position中的數字
Enter password: 
+-----------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| on.000001 |      154 |              |                  |                   |
+-----------+----------+--------------+------------------+-------------------+

 

[root@A04-Test-172 mysqldumpbackup]# mysqldump -uroot -p --all-databases --lock-all-tables  > backup.sql  #備份資料庫到backup.sql檔案中
[root@A04-Test-172 mysqldumpbackup]# ll
total 768
-rw-r--r-- 1 root root 785316 Mar 13 14:13 backup.sql

 

[root@A04-Test-172 mysqldumpbackup]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 DATABASE TEST1;                 #建立一個資料庫
Query OK, 1 row affected (0.02 sec)

mysql> SHOW MASTER STATUS;                    #記錄下現在的Position
+-----------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| on.000001 |      456 |              |                  |                   |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

 

[root@A04-Test-172 mysql-bin]# cp -a /usr/local/mysql/log/mysql-bin.000001  /root/mysqldumpbackup/mysql-bin  #複製二進位制日誌檔案到備份目錄下

[root@A04-Test-172]# service mysql.server stop                            #停止MySQL
[root@A04-Test-172 data]# rm -rf /usr/local/mysql/data/*                  #刪除所有資料檔案

#啟動MySQL, 如果是編譯安裝的應該不能啟動(需重新初始化), 如果rpm安裝則會重新初始化資料庫

#再次清除/usr/local/mysql/data目錄下的檔案，執行資料庫初始化，初始化的密碼在日誌檔案中（/usr/local/mysql/log/mysql-error.log）
[root@A04-Test-172 mysql]# bin/mysqld --initialize --user=mysql                     #初始化MySQL

#啟動mysql
[root@A04-Test-172 tmp]# service mysql.server start

#登入MySQL
[root@A04-Test-172 tmp]# mysql -uroot -p

#重置MySQL密碼

  mysql> set password for root@localhost = password('mysql');
  Query OK, 0 rows affected, 1 warning (0.02 sec)

  mysql> flush privileges;
  Query OK, 0 rows affected (0.03 sec)

mysql> SHOW DATABASES;            #檢視資料是否丟失
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> SET sql_log_bin=OFF;        #暫時關閉二進位制
Query OK, 0 rows affected (0.00 sec)

mysql>source /root/mysqldumpbackup/backup.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SET sql_log_bin=ON;                 #開啟二進位制日誌
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;                     #檢視資料是否恢復，可以看到mysql已經恢復了，但缺少TEST1
+--------------------+
| Database |
+--------------------+
| information_schema |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

 

 

#通過二進位制日誌增量恢復資料

[root@A04-Test-172 mysqldumpbackup]# mysqlbinlog -uroot -p --start-position=154 --stop-position=456 mysql-bin.000009
Enter password: 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180313 14:11:07 server id 1  end_log_pos 123 CRC32 0x7bbbba85  Start: binlog v 4, server v 5.7.18-log created 180313 14:11:07 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
e2unWg8BAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB7a6daEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYW6u3s=
'/*!*/;
# at 154
#180313 14:13:23 server id 1  end_log_pos 219 CRC32 0xb59c4546  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180313 14:13:23 server id 1  end_log_pos 294 CRC32 0xe7b92856  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1520921603/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
FLUSH TABLES
/*!*/;
# at 294
#180313 14:18:05 server id 1  end_log_pos 359 CRC32 0xa7afc716  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 359
#180313 14:18:05 server id 1  end_log_pos 456 CRC32 0x6a6a4762  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1520921885/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
CREATE DATABASE TEST1
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


另一種方法，將解析後的二進位制日誌檔案儲存到一個txt檔案中，然後進行匯入操作！

[root@A04-Test-172 mysqldumpbackup]# mysqlbinlog --start-position=154 --stop-position=456 mysql-bin.000009 > mysqlbinlog.txt
[root@A04-Test-172 mysqldumpbackup]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> source /root/mysqldumpbackup/mysqlbinlog.txt
Query OK, 0 rows affected (0.00 sec)

 #檢視是否恢復

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

mysql>

 

3.使用lvm2快照備份資料

LVM快照簡單來說就是將所快照源分割槽一個時間點所有檔案的後設資料進行儲存，如果原始檔沒有改變，那麼訪問快照卷的相應檔案則直接指向源分割槽的原始檔，如果原始檔發生改變，則快照卷中與之對應的檔案不會發生改變。快照卷主要用於輔助備份檔案。 這裡只簡單介紹。

具體可參見：http://www.360doc.com/content/13/0522/16/11801283_287305129.shtml

 

4. Xtrabackup

使用xtrabackup使用InnoDB能夠發揮其最大功效, 並且InnoDB的每一張表必須使用單獨的表空間, 我們需要在配置檔案中新增 innodb_file_per_table = ON 來開啟.

（1）安裝Xtrabackup

yum -y install  perl-DBD-MySQL 
rpm -ivh libev-4.03-3.el6.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

依賴包下載地址：https://pan.baidu.com/s/1HF_FyrZAv4PZio8mJiOXpQ

 

Xtrabackup是由percona提供的mysql資料庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具。特點：

1. 備份過程快速、可靠；

2. 備份過程不會打斷正在執行的事務；

3. 能夠基於壓縮等功能節約磁碟空間和流量；

4. 自動實現備份檢驗；

5. 還原速度快；

 

我們這裡使用xtrabackup的前端配置工具innobackupex來實現對資料庫的完全備份

使用innobackupex備份時, 會呼叫xtrabackup備份所有的InnoDB表, 複製所有關於表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案, 同時還會備份觸發器和資料庫配置檔案資訊相關的檔案, 這些檔案會被儲存至一個以時間命名的目錄.。

mkdir /root/xtrabackup           #新建備份目錄
innobackupex --user=root --password=mysql xtrabackup/       #開始備份

#備份時顯示的資訊
180319 11:18:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3910261'
xtrabackup: Stopping log copying thread.
.180319 11:18:50 >> log scanned up to (3910270)

180319 11:18:50 Executing UNLOCK TABLES
180319 11:18:50 All tables unlocked
180319 11:18:50 [00] Copying ib_buffer_pool to /root/xtrabackup/2018-03-19_11-18-43/ib_buffer_pool
180319 11:18:50 [00]        ...done
180319 11:18:50 Backup created in directory '/root/xtrabackup/2018-03-19_11-18-43/'
MySQL binlog position: filename 'mysql-bin.000014', position '456'
180319 11:18:50 [00] Writing /root/xtrabackup/2018-03-19_11-18-43/backup-my.cnf
180319 11:18:50 [00]        ...done
180319 11:18:50 [00] Writing /root/xtrabackup/2018-03-19_11-18-43/xtrabackup_info
180319 11:18:50 [00]        ...done
xtrabackup: Transaction log of lsn (3910261) to (3910270) was copied.
180319 11:18:50 completed OK!

[root@A04-Test-172 xtrabackup]# ll
total 4
drwxr-x--- 7 root root 4096 Mar 19 11:18 2018-03-19_11-18-43

[root@A04-Test-172 xtrabackup]# pwd
/root/xtrabackup

 一般情況, 備份完成後, 資料不能用於恢復操作, 因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務。因此, 此時的資料檔案仍不一致, 所以我們需要”準備”一個完全備份。

 

#開始完全備份
[root@A04-Test-172 ~]# innobackupex --apply-log --user=root --password=mysql /root/xtrabackup/2018-03-19_11-18-43/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910289
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=3910289
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 3910668
InnoDB: Doing recovery: scanned up to log sequence number 3910677 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 3910677 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 3910677
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910696
180319 12:18:51 completed OK!

[root@A04-Test-172 2018-03-19_11-18-43]# ll          #檢視備份檔案
total 131120
-rw-r----- 1 root root      424 Mar 19 11:18 backup-my.cnf
-rw-r----- 1 root root      759 Mar 19 11:18 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 19 12:18 ibdata1
-rw-r----- 1 root root 50331648 Mar 19 12:18 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 19 12:18 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 19 12:18 ibtmp1
drwxr-x--- 2 root root     4096 Mar 19 11:18 monkey
drwxr-x--- 2 root root     4096 Mar 19 11:18 mysql
drwxr-x--- 2 root root     4096 Mar 19 11:18 performance_schema
drwxr-x--- 2 root root    12288 Mar 19 11:18 sys
drwxr-x--- 2 root root     4096 Mar 19 11:18 TEST1
-rw-r----- 1 root root       21 Mar 19 11:18 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Mar 19 12:18 xtrabackup_checkpoints
-rw-r----- 1 root root      467 Mar 19 11:18 xtrabackup_info
-rw-r----- 1 root root  8388608 Mar 19 12:18 xtrabackup_logfile

 

恢復資料：

#刪除MySQL資料目錄下的檔案
[root@A04-Test-172 2018-03-19_11-18-43]# rm -rf /usr/local/mysql/data/*

#開始恢復資料，不用啟動該資料庫也可以還原
[root@A04-Test-172 2018-03-19_11-18-43]# innobackupex --copy-back --user=root --password=mysql /root/xtrabackup/2018-03-19_11-18-43/

180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/global_status.frm to /usr/local/mysql/data/performance_schema/global_status.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/replication_applier_configuration.frm to /usr/local/mysql/data/performance_schema/replication_applier_configuration.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_thread_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_waits_summary_by_user_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_history.frm to /usr/local/mysql/data/performance_schema/events_transactions_history.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_stages_current.frm to /usr/local/mysql/data/performance_schema/events_stages_current.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_user_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/memory_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/memory_summary_by_host_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_waits_history.frm to /usr/local/mysql/data/performance_schema/events_waits_history.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /usr/local/mysql/data/performance_schema/socket_summary_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/session_variables.frm to /usr/local/mysql/data/performance_schema/session_variables.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_history_long.frm to /usr/local/mysql/data/performance_schema/events_statements_history_long.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./TEST1/db.opt to /usr/local/mysql/data/TEST1/db.opt
180319 12:23:15 [01]        ...done
180319 12:23:15 completed OK!

[root@A04-Test-172 data]# ll       #檢視MySQL資料目錄下是否資料已恢復
total 122916
-rw-r----- 1 root root      759 Mar 19 12:23 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 19 12:23 ibdata1
-rw-r----- 1 root root 50331648 Mar 19 12:23 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 19 12:23 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 19 12:23 ibtmp1
drwxr-x--- 2 root root     4096 Mar 19 12:23 monkey
drwxr-x--- 2 root root     4096 Mar 19 12:23 mysql
drwxr-x--- 2 root root     4096 Mar 19 12:23 performance_schema
drwxr-x--- 2 root root    12288 Mar 19 12:23 sys
drwxr-x--- 2 root root     4096 Mar 19 12:23 TEST1
-rw-r----- 1 root root      467 Mar 19 12:23 xtrabackup_info
[root@A04-Test-172 data]# pwd
/usr/local/mysql/data

#修改data目錄所屬使用者和組
[root@A04-Test-172 data]# chown -R mysql:mysql /usr/local/mysql/data

[root@A04-Test-172 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show databases;            #檢視資料是否恢復
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

 增量備份

#新建兩個資料庫TEST1和TEST2

mysql> CREATE DATABASE TEST2;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| TEST2              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> 

 

#開始增量備份
[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --incremental /root/xtrabackup/  --incremental-basedir=/root/xtrabackup/2018-03-19_11-18-43/

 

180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/session_variables.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/session_variables.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_history_long.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_history_long.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_global_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./TEST1/db.opt to /root/xtrabackup/2018-03-19_13-29-21/TEST1/db.opt
180319 13:29:28 [01]        ...done
180319 13:29:28 Finished backing up non-InnoDB tables and files
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/xtrabackup_binlog_info
180319 13:29:28 [00]        ...done
180319 13:29:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3910715'
xtrabackup: Stopping log copying thread.
.180319 13:29:28 >> log scanned up to (3910724)

180319 13:29:28 Executing UNLOCK TABLES
180319 13:29:28 All tables unlocked
180319 13:29:28 [00] Copying ib_buffer_pool to /root/xtrabackup/2018-03-19_13-29-21/ib_buffer_pool
180319 13:29:28 [00]        ...done
180319 13:29:28 Backup created in directory '/root/xtrabackup/2018-03-19_13-29-21/'
MySQL binlog position: filename 'mysql-bin.000015', position '154'
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/backup-my.cnf
180319 13:29:28 [00]        ...done
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/xtrabackup_info
180319 13:29:28 [00]        ...done
xtrabackup: Transaction log of lsn (3910715) to (3910724) was copied.
180319 13:29:29 completed OK!

#檢視備份檔案

[root@A04-Test-172 xtrabackup]# ll
total 8
drwxr-x--- 8 root root 4096 Mar 19 13:03 2018-03-19_11-18-43         #全量備份
drwxr-x--- 8 root root 4096 Mar 19 13:29 2018-03-19_13-29-21         #第一次增量備份

[root@A04-Test-172 2018-03-19_13-29-21]# ll
total 140
-rw-r----- 1 root root   424 Mar 19 13:29 backup-my.cnf
-rw-r----- 1 root root   759 Mar 19 13:29 ib_buffer_pool
-rw-r----- 1 root root 81920 Mar 19 13:29 ibdata1.delta
-rw-r----- 1 root root    44 Mar 19 13:29 ibdata1.meta
drwxr-x--- 2 root root  4096 Mar 19 13:29 monkey
drwxr-x--- 2 root root  4096 Mar 19 13:29 mysql
drwxr-x--- 2 root root  4096 Mar 19 13:29 performance_schema
drwxr-x--- 2 root root 12288 Mar 19 13:29 sys
drwxr-x--- 2 root root  4096 Mar 19 13:29 TEST1
drwxr-x--- 2 root root  4096 Mar 19 13:29 TEST2
-rw-r----- 1 root root    21 Mar 19 13:29 xtrabackup_binlog_info
-rw-r----- 1 root root   117 Mar 19 13:29 xtrabackup_checkpoints
-rw-r----- 1 root root   553 Mar 19 13:29 xtrabackup_info
-rw-r----- 1 root root  2560 Mar 19 13:29 xtrabackup_logfile

BASEDIR指的是完全備份所在的目錄，此命令執行結束後，innobackupex命令會在/root/xtrabackup目錄中建立一個新的以時間命名的目錄以存放所有的增量備份資料。

另外，在執行過增量備份之後再一次進行增量備份時，其--incremental-basedir應該指向上一次的增量備份所在的目錄。

需要注意的是，增量備份僅能應用於InnoDB或XtraDB表，對於MyISAM表而言，執行增量備份時其實進行的是完全備份。

 

整理增量備份：

 

#  2018-03-19_11-18-43為全量備份目錄

[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --apply-log --redo-only /root/xtrabackup/2018-03-19_11-18-43   
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910705
InnoDB: Number of pools: 1
180319 14:57:16 completed OK!

#  2018-03-19_13-29-21為增量備份目錄

[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --apply-log --redo-only /root/xtrabackup/2018-03-19_11-18-43 --incremental-dir=/root/xtrabackup/2018-03-19_13-29-21

180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_history.frm to ./performance_schema/events_transactions_history.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_stages_current.frm to ./performance_schema/events_stages_current.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_user_by_event_name.frm to ./performance_schema/events_statements_summary_by_user_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/memory_summary_by_host_by_event_name.frm to ./performance_schema/memory_summary_by_host_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_waits_history.frm to ./performance_schema/events_waits_history.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/socket_summary_by_event_name.frm to ./performance_schema/socket_summary_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/session_variables.frm to ./performance_schema/session_variables.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_thread_by_event_name.frm to ./performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_summary_by_host_by_event_name.frm to ./performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_history_long.frm to ./performance_schema/events_statements_history_long.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_global_by_event_name.frm to ./performance_schema/events_statements_summary_global_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/TEST1/db.opt to ./TEST1/db.opt
180319 14:58:24 [01]        ...done
180319 14:58:24 [00] Copying /root/xtrabackup/2018-03-19_13-29-21//xtrabackup_binlog_info to ./xtrabackup_binlog_info
180319 14:58:24 [00]        ...done
180319 14:58:24 [00] Copying /root/xtrabackup/2018-03-19_13-29-21//xtrabackup_info to ./xtrabackup_info
180319 14:58:24 [00]        ...done
180319 14:58:24 completed OK

[root@A04-Test-172 ~]# rm -rf  /usr/local/mysql/data/*            #刪除資料目錄下檔案


#開始恢復

#2018-03-19_11-18-43為全量備份目錄

[root@A04-Test-172 ~]# innobackupex --copy-back /root/xtrabackup/2018-03-19_11-18-43 
name.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./performance_schema/events_waits_history.frm to /usr/local/mysql/data/performance_schema/events_waits_history.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /usr/local/mysql/data/performance_schema/socket_summary_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/session_variables.frm to /usr/local/mysql/data/performance_schema/session_variables.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_host_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_statements_history_long.frm to /usr/local/mysql/data/performance_schema/events_statements_history_long.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./TEST1/db.opt to /usr/local/mysql/data/TEST1/db.opt 180319 15:00:30 [01] ...done 
180319 15:00:30 completed OK!

#修改data資料目錄所屬使用者和組 
[root@A04-Test-172 mysql]# chown -R mysql:mysql /usr/local/mysql/data

#殺掉殭屍程式
[root@A04-Test-172 data]# ps -ef|grep mysql
root      4246  3167  0 13:12 pts/3    00:00:00 tail -f /usr/local/mysql/log/mysql-error.log
root      4575     1  0 13:19 pts/4    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid
mysql     4778  4575  0 13:19 pts/4    00:00:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/mysql-error.log --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root      5723  5061  0 15:12 pts/2    00:00:00 tail -f mysql-error.log
root      5725  5033  0 15:12 pts/0    00:00:00 grep mysql
[root@A04-Test-172 data]# kill -9 4575 4778
[root@A04-Test-172 data]# ps -ef|grep mysql
root      4246  3167  0 13:12 pts/3    00:00:00 tail -f /usr/local/mysql/log/mysql-error.log
root      5723  5061  0 15:12 pts/2    00:00:00 tail -f mysql-error.log
root      5729  5033  0 15:14 pts/0    00:00:00 grep mysql

#啟動資料庫
[root@A04-Test-172 data]# service mysql.server start
Starting MySQL.[  OK  ]

 

#檢視資料是否已恢復
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| TEST2              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> exit

 

 

七、總結

備份方法	備份速度	恢復速度	便捷性	功能	一般用於
cp	快	快	一般、靈活性低	很弱	少量資料備份
mysqldump	慢	慢	一般、可無視儲存引擎的差異	一般	中小型資料量的備份
lvm2快照	快	快	一般、支援幾乎熱備、速度快	一般	中小型資料量的備份
xtrabackup	較快	較快	實現innodb熱備、對儲存引擎有要求	強大	較大規模的備份

八、常見問題解答

1.啟動mysql時報錯：

[root@A04-Test-172 ~]# service mysql.server start
Starting MySQL..The server quit without updating PID file (/usr/local/mysql/data/mysqld.pid).[FAILED]

【解決方法】

（1）檢視MySQL日誌，報錯如下：

.
2018-03-19T05:12:30.839470Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-03-19T05:12:30.839551Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-03-19T05:12:30.839569Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-03-19T05:12:31.440444Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-03-19T05:12:31.440568Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-03-19T05:12:31.440587Z 0 [ERROR] Failed to initialize plugins.
2018-03-19T05:12:31.440596Z 0 [ERROR] Aborting

2018-03-19T05:12:31.440618Z 0 [Note] Binlog end
2018-03-19T05:12:31.440745Z 0 [Note] Shutting down plugin 'CSV'
2018-03-19T05:12:31.441182Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

（2）根據上述錯誤資訊，加上百度之。判斷是/usr/local/mysql/data目錄沒有寫許可權。經核查,/usr/local/mysql/data目錄的所屬使用者和組均為root.故授權之。

chown -R mysql:mysql  /usr/local/mysql/data

（3）檢視是否有殭屍程式，有則kill -9 

ps -ef|grep mysql

（4）重新啟動資料庫，完美啟動

[root@A04-Test-172 backup]# service mysql.server start
Starting MySQL..[  OK  ]