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 觸發器

單個Database Backup 方法一:

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  #指定還原至指定資料庫

單個Database Backup方法二:

#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 #還原當天資料庫崩潰時的資料

本文出自 “夏維柳” 部落格，請務必保留此出處http://willow.blog.51cto.com/6574604/1812598

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