【第八章】MySQL資料庫備份—邏輯備份

一、資料庫備份

1.命令簡介：

# mysqldump -h 伺服器 -u用戶名 -p密碼 資料庫名 > 備份文件.sql
1）關於資料庫名：
　　-A, --all-databases 　       所有庫
　　school 　　　　　　        資料庫名
　　school stu_info t1 school  資料庫的表stu_info、t1
　　-B, --databases bbs test mysql 多個資料庫
2）關於其它參數說明：
　　--single-transaction         #InnoDB 一致性 服務可用性
　　-x, --lock-all-tables          #MyISAM 一致性 服務可用性
　　-E, --events                    #備份事件調度器代碼
　　--opt                               #同時啟動各種高級選項
　　-R, --routines                 #備份存儲過程和存儲函數
　　-F, --flush-logs               #備份之前刷新日誌
　　--triggers                       #備份觸發器
　　--master-data=1|2        #該選項將會記錄binlog的日誌位置與文件名並追加到文件中

2、操作過程：

1）創建庫表：

mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;
Database changed

mysql> select * from school.t1;
Empty set (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> 

2）邏輯備份：

[root@localhost ~]# mysqldump -uroot -p'Yanglt123.' --all-databases \
> --single-transaction \
> --routines \
> --triggers \
> --master-data=1 \
> --flush-logs > /tmp/`date +%F`-mysql-all.sql`

mysqldump: [Warning] Using a password on the command line interface can be insecure.    #此提示是密碼明文顯示的願意
[root@localhost tmp]# 

 註意事項：

--master-data=1    #該選項將會記錄binlog的日誌位置與文件名並追加到文件中
參數為1和2的時候，都是把position日誌截斷，如果為2的話第22行為註釋狀態，為1的時候沒有註釋，建議選擇1：

 [root@localhost tmp]# vim 2018-09-19-mysql-all.sql 可以
 19 -- Position to start replication or point-in-time recovery from
 20 --
 21 
 22 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
 23 
 24 --
:set nu                                                        

 二、資料庫恢復

1. 停止資料庫 　　【systemtl stop mysqld 】
2. 清理環境 　　   【rm -rf /var/lib/mysql/*；】
3. 啟動資料庫 　   【初始密碼 /var/log/mysqld.log】
4. 重置密碼 　　   【新密碼 】
5. mysql恢複數據  【新密碼 】
6. 刷新授權 　　   【備份時密碼 】

註：如果不是一個新的資料庫環境，我們需要從第一步開始，如果已經是一個新的數據環境，我們可以直接從第5步執行。

先創建一個表，等一下驗證恢復情況：
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(1),(2)
    -> ;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| t1               |
| t2               |
+------------------+
2 rows in set (0.00 sec)

mysql> Bye
[root@localhost ~]# 

1）停止資料庫
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# 
2）清理環境
此處暫時不刪除bin-log日誌
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*

3）啟動資料庫
[root@localhost ~]# systemctl start mysqld

4）重置密碼       
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log|tail -n 1
2018-09-19T09:48:39.418109Z 1 [Note] A temporary password is generated for root@localhost: aBm<-wrj4NSV
[root@localhost ~]# mysqladmin -uroot -p'aBm<-wrj4NSV' password "Yanglt123."
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 

5）恢複數據
[root@localhost ~]# mysql -uroot -p'Yanglt123.' < /tmp/2018-09-19-mysql-all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 
可以看到它恢復到了備份點，剛纔創建的表t2是在備份點之後生成的，可以看到表中沒有t2：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use school;
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_school |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> 

6） 刷新授權 
改完密碼後與備份點的密碼可能不一致，所有我們要執行此步驟，來實現與備份點密碼一致。
[root@localhost ~]# mysql -p'Yanglt123.' -e 'flush privileges'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 

7）建議在邏輯備份恢復時，暫停BINLOG
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.02 sec)
mysql> source /tmp/2018-09-19-mysql-all.sql;

三、