[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 ./mysql_install_db 初始化成功之後，執行： /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命令來處理。
