linux下實現mysql日志管理及mysql備份還原方法

下面跟著筆者一起來了解下linux下實現mysql日志管理及mysql備份還原方法，相信大家看完肯定會受益匪淺，文字在精不在多，希望linux下實現mysql日志管理及mysql備份還原方法這篇短內容是你想要的。

linux下mysql日志管理及mysql備份還原

1.SHOW GLOBAL VARIABLES LIKE '%log%';  顯示所有日志相關的選項

2.錯誤日志：log_error和log_warnings

3.一般查詢日志：general_log general_log_file log log_output

4.慢查詢日志：long_query_time log_slow_queries={YES|NO} slow_query_log low_query_log_file 

5.二進制日志：任何引起或可能引起數據庫變化的操作；復制和即時點恢復；

二進制日志的格式：binlog_format

  基于語句: statement

  基于行: row

  混合方式: mixed

二進制日志事件：產生的時間 和 相對位置

二進制日志文件：索引文件 和 二進制日志文件

 mysql> SHOW MASTER STATUS;#查看當前正在使用的二進制日志文件

 mysql> SHOW BINARY LOGS; #查看所有日志文件:

 mysql> SHOW BINLOG EVENTS IN '二進制日志文件' [FROM 位置]；#查看事件:

    例如:SHOW BINLOG EVENTS IN 'mysql-bin.000004' FROM 107;

刪除二進制日志文件: 刪除指定二進制日志文件之前的所有日志文件

 mysql> PURGE BINARY LOGS TO '二進制日志文件'

查看日志信息:mysqlbinlog命令選項如下:

         --start-datetime  --stop-datetime

         --start-position  --stop-position

    舉例:mysqlbinlog --start-position 177 --stop-position 331 mysql-bin.000004;

       mysqlbinlog --start-datetime='16-07-07 13:32:07' mysql-bin.000004;

滾動二進制日志:FLUSH LOGS;

6.中繼日志：從主云服務器的二進制日志文件中復制而來的事件，并保存為的日志文件；

7.事務日志：事務性存儲引擎用于保證原子性、一致性、隔離性和持久性；

 innodb_flush_log_at_trx_commit:

  0: 每秒同步，并執行磁盤flush操作；

  1：每事務同步，并執行磁盤flush操作；

  2: 每事務同步，但不執行磁盤flush操作；

8.mysqldump備份和還原數據庫工具

8.1.備份單個數據庫，或庫中特定表

  格式:mysqldump 數據庫名 [表1] [表2]

   --master-data={0|1|2}

0: 不記錄二進制日志文件及路位置；

1：以CHNAGE MASTER TO的方式記錄位置，可用于恢復后直接啟動從云服務器；

2：以CHANGE MASTER TO的方式記錄位置，但默認為被注釋；

   --lock-all-tables：鎖定所有表

   --flush-logs: 執行日志flush；

   --single-transaction啟動熱備；如果指定庫中的表類型均為InnoDB

8.2.備份多個庫：

--all-databases: 備份所有庫

--databases DB_NAME,DB_NAME,...: 備份指定庫

--events 事件調度器

--routines 存儲過程和存儲函數

--triggers 觸發器

單個數據庫備份 方法一:

mysql>FLUSH TABLES WITH READ LOCK;

mysql>FLUSH LOGS;

mysql>SHOW BINARY LOGS;

#mysqldump -u root -p --master-data=2 jiaowu>/root/jiaowu.sql #備份并沒有創建數據庫的語句

mysql>UNLOCK TABLES;

mysql>CREATE DATABASE jiaowu;  #必須手動創建數據庫

#mysql jiaowu < /root/jiaowu.sql  #指定還原至指定數據庫

單個數據庫備份方法二:

#mysqldump -u root -p --lock-all-tables --flush-logs --masterdata=2 jiaowu>/root/jiaowu.sql

所有數據庫完全備份：

#mysqldump -u root -p --lock-all-tables --flush-logs --all-databases  --master-data=2 > /root/alldatabases.sql

9.實例:利用mysqldump命令實現對所有數據庫每周做完全備份和每天做增量備份,并且實現還原

建議:mysql數據目錄放置獨立硬盤或LVM中,二進制日志文件也要放置在其他目錄

以下利用實驗環境模擬實現：注意本實驗二進制日志文件名以自己實驗環境為準

9.1.周末做完整備份所有數據庫

#mysqldump -u root -p --lock-all-tables --flush-logs --all-databases  --masterdata=2  > /root/alldatabases.sql

9.2.清除滾動日志之前的所有二進制文件,但清除之前建議最好保存一下

mysql>PURGE BINARY LOGS TO 'mysql-bin.000010';

mysql>SHOW BINARY LOGS;

9.3.完整備份數據庫后，過了一天,模擬數據庫有刪除一個數據

mysql>USE jiaowu;

mysql>DELETE FROM tutors WHERE Age>80;

9.4.每天做增量備份

mysql>FLUSH LOGS;  #滾動日志

#cd /mydata/data

#cp mysql-bin.000010 /root  #直接復制增量二進制日志文件

#mysqlbinlog mysql-bin.0000010 > /root/increment_"date +%F-%H-%M-%S".sql #或保存至sql語句

9.5.模擬又過了一天,數據庫有插入一條數據

mysql>USE jiaowu;

mysql>INSERT INTO tutors (Tname) VALUES ("Tom");

9.6.模擬當天數據庫崩潰,手動刪除數據目錄所有文件導致數據庫無法使用

#cd /mydata/data

#cp mysql-bin.000011 /root/  #備份當前正使用的二進制日志文件

#rm -rf *

9.7.利用之前的備份,模擬實現還原數據庫

#killall mysqld

#cd /usr/local/mysql

#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ #初始化數據庫

#service mysqld start

#cd

#mysql -u root -p < alldatabases.sql #還原完整備份時的數據庫

#mysql -u root -p < increment_2016-07-08-13-30-00.sql #還原增量備份的數據

#mysqlbinlog mysql-bin.000011 | mysql -u root -p #還原當天數據庫崩潰時的數據

10.單表備份

備份格式：SELECT * INTO OUTFILE '表的備份路徑.txt' FROM 表名 [WHERE 條件];

還原格式：LOAD DATA INFILE '表的備份路徑.txt' INTO TABLE 表名;

mysql> CREATE TABLE students_new LIKE students; #復制一個與源表結構一樣的表

mysql> SELECT * INTO OUTFILE '/tmp/students_new.txt' FROM students;

mysql> DRIO TABLE students;

mysql> LOAD DATA INFILE '/tmp/students_new.txt' INTO TABLE students_new;

11.LVM快照備份

11.1.找開會話,模擬啟動事務

     mysql>START TRANSACTION;

11.2.打開另外一個會話，加讀鎖，鎖定所有表；

mysql> FLUSH TABLES WITH READ LOCK;

mysql> FLUSH LOGS;

     mysql> SHOW MASTER STATUS;

11.3.通過另一個終端，保存二進制日志文件及相關位置信息；

# mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /backup/master-`data +%F`.info

11.4.創建快照卷

# lvcreate -L 100M -s -p r -n mydata-snap /dev/myvg/mydata

11.5.釋放鎖

mysql> UNLOCK TABLES;

11.6.掛載快照卷并備份

mount -o ro  /dev/myvg/mydata /mnt 

cd /mnt/data

     cp -a /mnt/data/* /backup/bak-full-`data +%F`

11.7.刪除快照卷；

 cd 

 umount /mnt

 lvremove --froce /dev/myvg/mydata-snap 

 cp /backup/bak-full-2016-07-11

 rm -rf mysql-bin.*

11.8.模擬添加數據并滾動日志

   mysql> USE jiaowu;

   mysql> INSERT INTO tutors (Tname) VALUES ('stu200');

   mysql> FLUSH LOGS;

   mysql> INSERT INTO tutors (Tname) VALUES ('stu201');

   mysql> INSERT INTO tutors (Tname) VALUES ('stu202');

   mysql> FLUSH LOGS;

11.9.備份上次快照后增加的二進制日志；

 cp /mydata/data

 cat /backup/master-2016-07-11.info #查看備份時的二制日志文件,且備份它之后所有二制日志文件

 mysqlbinlog --start-datetime='16-07-11 15:30:08' mysql-bin.000005 mysql-bin.000006 > /backup/increment-`data +F-%H-%M-%S`.sql

11.10.模擬刪除mysql數據庫數據目錄,導致mysql數據崩潰

  service mysqld stop

  rm -rf /mydata/data/*

11.11.還原數據庫

 cp -a /backup/bak-full-2016-07-11/* /mydata/data/

  ls -l /mydata/data 

  service mysqld start

  mysql> SET sql_log_bin=0; #連上mysql并暫時關閉二進制日志功能

  mysql> source /backup/increment-2016-07-11-15-58-36.sql #導入增量二進制日志文件

  mysql> SET sql_log_bin=0; #連上mysql并開啟二進制日志功能

看完linux下實現mysql日志管理及mysql備份還原方法這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。