在上一篇文章 MySQL 命令行工具之 mysqldump 深入研究 中，我們搞定了mysqldump的參數和基本原理。那麼我們該怎麼樣最好的使用它的？它有哪些坑呢？
1. 利用mysqldump進行邏輯備份
1）全邏輯備份：
mysqldump -uxxx -p --flush-logs --delete-master-logs --all-databases > alldb.sql (每天晚上進行一次全備)
2）增量備份：
mysqladmin flush-logs (每小時刷一下，保存起來，進行了一次增量備份)
3）缺點：
1> --all-databases 包含了 mysql 數據庫，其中包含了權限的數據，所以我們應該加上 --flush-privileges，在恢復時，權限才能生效；
注意 --all-databases 包括了mysql數據庫，但是不會包含 information_schema和performance_schema兩個數據庫。
2> 因為 mysqldump 默認啟用了 --lock-tables，所以會導致在備份期間對所有表持有讀鎖： lock table tb read local，所以所有的update，delete語句
會被阻塞。但是select語句和insert語句不會被阻塞。
3> --delete-master-logs 備份之後，會執行 purge logs to 語句。刪除了備份之後的master上的binary log. 一般而言，我們不建議隨便刪除binary log.
我們應該將它們保存起來，而不是直接刪除。以防萬一，要留條退路。
4> 該備份方式，雖然在整個備份過程中持有了 lock table tb read local，但是還是可以執行 insert 語句的。所以得到的不是一致性的備份。雖然得到的不是
一致性的備份，但是因為flush log之後，所有的操作 也會記入新的binary log，所以如果使用了所有新的binary log來進行完全恢復的話，最後恢復的數據
也是一致性的。當然不一致性的備份無法用於搭建slave。
如果要得到一致性的備份的話，需要使用 --lock-all-tables 或者使用 --single-transaction 選項。前者使用了全局讀鎖，不允許任何修改操作。後者使用
了事務的特性來得到一致性備份。
所以我們應該對上面的備份方式進行改良。
2. 使用mysqldump備份的最佳姿勢
1）優化鎖 和 得到一致性備份：
我們可以使用結合使用 --single-transaction 、--master-data=2 、--flush-logs 來達到將鎖定時間大大減少的目的。同時有得到了一致性的備份，而且該一致性備份和 flush 的日志也是一致的；
2）去掉 --delete-master-logs 選項，改為在備份之後，將所有被刷新的 binary log 移到一個地方保存起來；
3）因為使用了 --single-transaction 選項，針對的只能是 innodb 數據，但是mysql數據是Myisam引擎的，所以我們最好將mysql數據庫的備份分開來，
另外專門針對 mysql 數據庫進行一次操作。當然不分開來備份，可能也沒有問題。
4）還要加上 --routines 來備份存儲過程和函數，觸發器默認會備份。
優化之後，我們得到：
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;
如果將mysql也一起備份的話：
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;
3. 使用mysqldump來搭建slave環境
搭建slave環境，一般有兩種方法，對於規模不大的庫，可以采用mysqldump來搭建；對於規模很大的庫，最好采用xtrabackup來搭建，速度要快很多。
1）首先 分別在master和slave上設置不同的server_id=1/101，啟用master上的log-bin=1，啟用slave上的relog-log=relay-bin; 在master上設置：
binlog_format=row；二進制日志的格式。maser上最好還設置sync_binlog=1 和 innodb_flush_log_at_trx_commit=1防止發生服務器崩潰時
導致復制破壞。在slave上最好還配置：read-only=1 和 skip-slave-start=1 前者可以防止沒有super權限的用戶在slave上進行寫，後者防止在啟動
slave數據庫時，自動啟動復制線程。以後需要手動start slave來啟動復制線程。注意slave沒有必要啟用 log-bin=1，除非需要搭建二級slave。
2）在master上建立一個具有復制權限的用戶：
grant replication slave, replication client on *.* to repl@’192.168.%.%’ identified by ‘123456’;
3）備份master上的數據庫，遷移到slave上：
[root@localhost ~]# mysqldump -uroot -p --routines --flush-logs --master-data=2 --databases db2 db1>/root/backup.sql Enter password: [root@localhost ~]# scp /root/backup.sql 192.168.137.9:/tmp/backup.sql The authenticity of host '192.168.137.9 (192.168.137.9)' can't be established. RSA key fingerprint is a4:cd:c0:13:d1:8c:c0:a5:e7:c4:43:b5:95:17:af:d3. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.137.9' (RSA) to the list of known hosts. [email protected]'s password: backup.sql
因為slave的搭建需要一致性的備份，所以需要啟用 --lock-all-tables(master-data=1/2會自動啟用--lock-all-tables)或者--single-transaction；
另外還需要知道該一致性備份的數據，對應的master上的binary log的文件名，以及在該文件中的position，所以必須啟用 master-data選項。
因為--master-data會啟用--lock-all-tables 所以數據才是一致性的；但是導致了全局鎖，不能進行任何修改操作；下面我們使用--single-transaction進行優化：
mysqldump -uroot -p --routines --flush-logs --single-transaction --master-data=2 --databases db1 db2 > /root/backup.sql; (--flush-logs非必須)
這樣全局鎖僅僅在備份的開始短暫的持有。不會再備份的整個過程中持有全局鎖。
4）在slave上執行備份的腳本，然後連上master，開啟復制線程：
執行sql腳本：
mysql> source /tmp/backup.sql
找到 --master-data 輸出的 binary log 的文件名和postion:
[root@localhost ~]# head -50 /tmp/backup.sql
......
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;
執行 change master to, start slave:
在salve上執行命令開始復制：
mysql> change master to master_host='192.168.137.8', master_user='repl', master_password='123456',
-> master_log_file='mysql-bin.000010', master_log_pos=809;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
最後在slave上查看復制線程的狀態：
mysql> show slave status\G
... ...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
slave_IO_Runing 和 slave_sql_runing 狀態都是yes表示搭建成功。
5）replication涉及到的三個線程：
1> master上的 binlog dump(dump線程)，即讀取master上的binlog，發送到slave上的線程。
2> slave上的IO線程：讀取slave上的relay log。
3> slave上的sql線程：執行IO線程讀取的relay log的線程。
4. 使用mysqldump的備份進行 還原
下面使用 mysqldump 進行一個備份，然後刪除 datadir, 然後使用備份sql腳本和binary log進行還原的過程。
1）首先進行一個全備：
mysqldump -uroot -p --single-transaction --master-data=2 --routines --flush-logs --databases gs ngx_lua > gs_ngx_lua_backup.sql;
數據庫有兩個庫： gs , ngx_lua.
2）將 備份時刷新之後的 binary log 利用 mv 命令移動到安全的位置，也就是--master-data=2輸出的日志文件，它之前的日志文件都存儲到安全的位置：
[root@localhost ~]# head -n 50 gs_ngx_lua_backup.sql -- MySQL dump 10.13 Distrib 5.6.26, for linux-glibc2.5 (i686) -- -- Host: localhost Database: gs ... ...-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;
也就是將 MASTER_LOG_FILE='mysql-bin.000027' 之前的日志都存儲到其它位置。
然後執行：purge binary logs to 'mysql-bin.000027' 更新了 mysql-bin.index 中的索引信息，這裡並沒有刪除binary log，因為它們已經被mv走了。
3）下面模擬一個 增量備份：
mysql> delete from user where id=5; Query OK, 1 row affected (0.02 sec) mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | +----+----------+------+-------------+----------+ 4 rows in set (0.00 sec)
mysql> flush logs; Query OK, 0 rows affected (0.11 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000027 | 370 | | mysql-bin.000028 | 120 | +------------------+-----------+ 2 rows in set (0.00 sec)
這裡 flush logs 進行增量備份，然後將增量備份的 bianry log 文件 mysql-bin.000027 也存儲起來。
然後在進行一條 delete 語句：
mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | +----+----------+------+-------------+----------+ 3 rows in set (0.00 sec)
到這裡數據庫的最新狀態是：user 表只有3條記錄。
然後我們同樣將 mysql-bin.000028 也存儲起來。
4）然後我們刪除 data-dir 目錄中的所有文件，然後開始還原：
[root@localhost mysql]# pwd /var/lib/mysql [root@localhost mysql]# mv ./* /backup/before_delete/
此時 data-dir 目錄是空的。然後我們試圖使用 mysqladmin -uroot -p shutdown 來關閉mysqld，發現早不到 mysql.sock 無法關閉，我們只好使用
killall mysqld
來關閉。
5）然後進行數據庫的初始化，然後開始還原：
進入到 /usr/local/mysql/script 目錄，然後執行初始化：
./mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql
初始化成功之後，執行：
/usr/local/mysql/bin/mysql_secure_installation
來進行密碼設置。這一步可能會報錯：找不到 /tmp/mysql.sock 文，解決方法，在/etc/my.cnf 中的[client], [mysql], [mysqld] 都進行下面的設置：
socket=/tmp/mysql.sock
然後重新執行：/usr/local/mysql/bin/mysql_secure_installation 就行了。
初始化完成之後，我們使用備份的sql腳本來進行恢復：
[root@localhost ~]# mysql -uroot -p < gs_ngx_lua_backup.sql
執行完成之後，發現 gs 和 ngx_lua 數據庫都還原出來了，但是他們的數據不是最新的數據：
mysql> use gs; 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> select * from user; +----+----------+------+----------------+----------+ | id | name | sex | phone | addr | +----+----------+------+----------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | | 5 | lexin2 | 1 | 43563436565634 | aomao | +----+----------+------+----------------+----------+ 5 rows in set (0.01 sec)
這是因為，我們還沒有使用 mysql-bin.000027 和 mysql-bin.000028 兩個binary log。mysql-bin.000027 是我們前面模擬的增量備份，而mysql-bin.000028 是 刪除data-dir目錄時，最新的binary log。依次應用了這兩個binary log之後，數據庫才能恢復到最新的狀態。
6）應用 binary log:
[root@localhost backup]# pwd /backup [root@localhost backup]# mysqlbinlog mysql-bin.000027 > 27.sql
[root@localhost backup]# mysqlbinlog mysql-bin.000028 > 29.sql
mysqlbinlog mysql-bin.000027 > 27.sql 得到了 27.sql 和 28.sql 之後，使用 mysql 來執行：
mysql -uroot -p < 27.sql; 應用了增量備份的binary log.
然後查看數據：
mysql> mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | +----+----------+------+-------------+----------+ 4 rows in set (0.00 sec)
然應用 28.sql ：
mysql -uroot -p < 28.sql;
得到最新的狀態：
mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | +----+----------+------+-------------+----------+ 3 rows in set (0.00 sec)
可以看到，成功的還原到了刪除 data-dir 目錄之前的狀態了。
5. mysqldump的 坑
如果對 mysqldump 的默認啟用的選項不熟悉的話，可能會被默認啟用的選項 --add-drop-table 給坑了。因為默認會生成 drop table if exist 語句。可能會導致數據的丟失。 --add-drop-database 默認沒有啟用。如果不想他生成 drop table 語句，可以加入 --skip-add-drop-table選項，或者 --add-ropt-table=0也可以。
6. 總結：
1）邏輯備份的最佳方法：
全備：
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;
如果將mysql也一起備份的話：
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;
有時，還需要加入：--default-character-set=utf8/utf8mb4 ，該選項一般也可以配置在/etc/my.cnf中。
增量備份：flush logs; 然後將binary log存儲起來即可。
2）搭建slave時的最佳選項：
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --databases db1 db2 db3 > alldb.sql;
搭建slave，沒有必要 --flush-logs。當然搭建slave的最佳方式是使用 xtrabackup，物理備份。
3）使用mysqldump備份的sql腳本還原的方法：
先還原數據庫，然後應用增量日志和最新日志，binary log在應用之前需要使用mysqlbinlog命令來處理。
