（十二）MySQL邏輯備份mysqldump

標籤：head   rom   creat   master   generated   檔案拷貝   value   一致性服務   無法啟動   

（1）簡介

• 文法
  mysqldump -h伺服器 -u使用者名稱 -p密碼 [-P連接埠號碼] [參數] 資料庫名 >備份檔案.sql

• 關於資料庫：

  -A,--all-databases 所有庫，會產生DDL語句（建立資料庫的語句和進入資料庫的語句，匯入的時候不需要指定資料）test    資料庫,沒有指定其它參數，匯入到資料的時候需要指定匯入到哪個資料庫，不會產生建立資料庫的DDL語句，mysql -uroot -predhat testdb <1.sqltest t1 t2  test資料庫的表t1和t2 ，匯入到資料的時候需要指定匯入到哪個資料庫，不會產生建立資料庫的DDL語句-B, --databases bbs test mysql 多個資料庫 ，會產生DDL語句（建立資料庫的語句和進入資料庫的語句，匯入的時候不需要指定資料）mysql -uroot -predhat <1.sql

• 其它參數說明

  --single-transaction  #InnoDB 一致性服務可用性-x  #MyISAM 一致性服務可用性-E   #備份時間調度器代碼-R   #備份預存程序和儲存函數-F    #備份之前重新整理日誌-d    #只備份表結構--triggers    #備份觸發器--master-data=1|2    #用於記錄binlog日誌位置和檔案名稱追加到檔案中，一般使用1

  （2）機器損壞備份恢複

  前提：需要有完全備份和增量備份（二進位記錄檔）

  1）備份

• 需要提前開啟二進位日誌

  #vim /etc/my.cnflog-bin=/data/mydata/mysql-bin/masterserver-id=1#mkdir /data/mydata/mysql-bin#chown -R mysql.mysql /data/mydata/mysql-bin#systemctl restart mysqld

• 準備資料

  mysql> create database testdb;mysql> create table testdb.test(id int);mysql> insert into testdb.test values(1);

• 完整備份

  mysqldump -uroot [email protected] -A -R --single-transaction --master-data=1 --flush-logs >/backup/$(date +%F-%H)-mysql-all.sql   \\需要提前建立好備份目錄

  組建目錄：ll /backup/2018-04-26-00-mysql-all.sql

• 產生一些資料，用於使用二進位日誌恢複

  mysql>  insert into testdb.test values(2);mysql> flush logs;mysql>  insert into testdb.test values(2);mysql>  insert into testdb.test values(3);mysql> flush logs;mysql>  insert into testdb.test values(4);mysql> flush logs;mysql> select * from testdb.test;+------+| id   |+------+|    1 ||    2 ||    2 ||    3 ||    4 |+------+

• 把二進位檔案拷貝了其它目錄，我這裡就先放在/root目錄了
  cp -ar /data/mydata/mysql-bin/ /root

• 類比資料庫故障

  rm -rf /var/lib/mysql/*systemctl stop mysqld chown -R mysql.mysql /var/lib/mysqlsystemctl start mysqld   \\無法啟動rm -rf /var/lib/mysql/*   \\再刪除資料systemctl start mysqld   \\再次可以啟動了grep "password" /var/log/mysqld.log 2018-04-25T10:01:13.953346Z 1 [Note] A temporary password is generated for [email protected]: 9Mf3.k)AOgEdmysqladmin -uroot -p‘9Mf3.k)AOgEd‘ password ‘[email protected]‘;

  2）恢複：注意恢複時關閉記錄二進位日誌，避免產生不必要的IO操作

• 關閉記錄二進位日誌，完整備份恢複

  #mysqladmin  -uroot -p‘[email protected]‘ flush-logsmysql> set sql_log_bin=0;mysql> system mysql -uroot -predhat </backup/2018-04-26-00-mysql-all.sql

• 擷取備份記錄的log_file檔案和pos位置點

  # grep -i change /backup/2018-04-26-00-mysql-all.sql | head -1CHANGE MASTER TO MASTER_LOG_FILE=‘master.000003‘, MASTER_LOG_POS=154;

• 增量備份恢複

  mysql> system mysqlbinlog --start-position=154 master.000003 master.000004 master.000005 master.000006 | mysql -uroot -p‘[email protected]‘

• 驗證資料

  mysql> select * from testdb.test;+------+| id   |+------+|    1 ||    2 ||    2 ||    3 ||    4 |+------+5 rows in set (0.01 sec)

  （3）類比刪除資料恢複1）準備資料

  mysql -uroot -predhat -e "create database testdb"mysql -uroot -predhat -e "create table testdb.test(id int)"mysql -uroot -predhat -e "insert into testdb.test values(1)"

  2）完整備份

  mysqldump -uroot -predhat -A -R --single-transaction --master-data=1 --flush-logs >/backup/$(date +%F-%H)-mysql-all.sql組建目錄：ll /backup/2018-04-25-10-mysql-all.sql 

  3）準備資料用於增量備份恢複

  mysql> insert into testdb.test values(2);mysql> flush logs;mysql> insert into testdb.test values(3);mysql> flush logs;mysql> insert into testdb.test values(4);mysql> flush logs;mysql> insert into testdb.test values(5);mysql> select * from testdb.test;+------+| id   |+------+|    1 ||    2 ||    3 ||    4 ||    5 |+------+

  4）刪除資料庫:注意刪除資料庫重新整理一下二進位日誌

  mysql> drop database testdb;mysql> flush logs;

  5）完全備份恢複

  mysql -uroot -predhat </backup/2018-04-25-10-mysql-all.sql

  6）擷取備份檔案的位置點

  # grep -i "change" /backup/2018-04-25-10-mysql-all.sql | head -1CHANGE MASTER TO MASTER_LOG_FILE=‘master.000002‘, MASTER_LOG_POS=154;

  7）擷取刪庫的pos位置點

  [[email protected] ~]# mysqlbinlog -v /data/mydata/mysql-bin/master.000005 | grep -C 3 "^\bdrop\b" # mysqlbinlog -v /data/mydata/mysql-bin/master.000005 | grep -C 3 "^\bdrop\b"# at 472#180425 10:34:23 server id 1  end_log_pos 570 CRC32 0x1d4bcda1  Query   thread_id=24    exec_time=0 error_code=0SET TIMESTAMP=1524666863/*!*/;drop database testdb/*!*/;# at 570#180425 10:35:14 server id 1  end_log_pos 635 CRC32 0x836dc275  Anonymous_GTID  last_committed=2    sequence_number=3   rbr_only=no

  8）增量備份恢複

  master.000005 是刪除資料庫的二進位檔案，上面把刪除庫語句上面at的pos位置找到，恢複這個檔案的時候使用stop-position=472

  cd /data/mydata/mysql-bin#mysqlbinlog --start-position=154 master.000002 master.000003 master.000004 | mysql -uroot -p‘redhat‘# mysqlbinlog --stop-position=472 master.000005 | mysql -uroot -p‘redhat‘

  9）驗證

  mysql> select * from testdb.test;+------+| id   |+------+|    1 ||    2 ||    3 ||    4 ||    5 |+------+

（十二）MySQL邏輯備份mysqldump