溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
-----------------------------日志-----------------------------------
MySQL的日志類型有以下幾種：
1. 錯誤日志（error）,MySQL服務實例啟動、運行或者停止等相關信息。 2. 普通查詢日志（general）,MySQL服務實例運行的所有SQL語句或者MySQL命令。 3. 二進制日志（binary）,對數據庫執行的所有更新語句，不包括select 和show語句。 4. 慢查詢日志（slow），執行時間超過long_query_time 設置值的SQL語句，或者沒有使用索引的SQL語句。
默認情況下，所有的MySQL日志以文件的方式存放在數據庫根目錄下：
[root@localhost data]# pwd/usr/local/mysql/data [root@localhost data]# ls auto.cnf ibdata1 ib_logfile1 mysql sys ib_buffer_pool ib_logfile0 ibtmp1 performance_schema
這里主要講到兩種日志
1. 錯誤日志
錯誤日志記錄著mysqld啟動和停止,以及服務器在運行過程中發生的錯誤的相關信息。在默認情況下，系統記錄錯誤日志的功能是關閉的，錯誤信息被輸出到標準錯誤輸出。需要在啟動的時候開啟 log-error選項
如果沒有指定文件名 默認hostname.err，默認路徑為datadir目錄
開啟錯誤日志的操作：
[root@localhost data]# vim /etc/my.cnf //編輯配置文件 在[mysqld]下添加：log-error=/usr/local/mysql/data/mysql_error.log [root@localhost data]# systemctl restart mysqld.service //重啟服務 [root@localhost data]# ls //查看文件是否產生日志文件 auto.cnf ibdata1 ib_logfile1 mysql performance_schema ib_buffer_pool ib_logfile0 ibtmp1 mysql_error.log sys mysql> show variables like '%err%'; //查看錯誤日志狀態 +---------------------+---------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------+ | binlog_error_action | ABORT_SERVER | | error_count | 0 | | log_error | /usr/local/mysql/data/mysql_error.log | | log_error_verbosity | 3 | | max_connect_errors | 100 | | max_error_count | 64 | | slave_skip_errors | OFF | +---------------------+---------------------------------------+ 7 rows in set (0.01 sec)
錯誤日志的清理： [root@localhost data]# ls auto.cnf ibdata1 ib_logfile1 mysql performance_schema ib_buffer_pool ib_logfile0 ibtmp1 mysql_error.log sys[root@localhost data]# mv mysql_error.log mysql_olderror.log[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2.二進制日志(重點!)
二進制日志不會記錄select ，show 等不修改數據的語句。打開二進制日志會消耗一些系統系能，但是對于復制和系統恢復，所帶來的好處大于減少的性能，它用來實現復制的基本憑據。也就是說，你可以將生成環境中的MySQL的二進制文件拿到線下的服務器上運行一下，理論上你會拿到和生成環境中一樣的數據，因此，二進制日志也叫復制日志。二進制日志文件默認在數據目錄下，通常情況下為mysql-bin#（例如：mysql-bin.000001，mysql-bin000002）。二進制日志即binlog日志 記錄數據定義語言(DDL)和數據控制語言(DML) 但不包括數據查詢語言。
二進制日志的主要功能
1、恢復（recovery）
2、復制（replication）
二進制日志文件內容格式
1.事件發生的日期和時間（會在關鍵字“at”）
2.服務器ID（server
id）
3.事件結束位置（end_log_pos）
4.事件的類型（如：Query，Stop等等）
5.原服務器生成此事件時的線程ID號（thead_id，可以通過“show
processlist;”進行查詢）
6.語句時間戳和寫入二進制文件的時間差，單位為秒（exec_time，表示記錄日志所用的時間戳，當他等于0時表示沒有用到1秒鐘。）
7.錯誤代碼，0表示正常執行（error_code,排查方法就得查看官方文檔。）
8.事件內容（修改的SQL語句）
9.事件位置（相當于下一事件的開始位置，還是用“at”關鍵字標志）
開啟二進制日志
[root@localhost data]# vim /etc/my.cnf //編輯配置文件 再[mysqld]下添加： log_bin=mysql-bin [root@localhost data]# systemctl restart mysqld.service //重啟服務
二進制日志狀態查看
系統變量log_bin的值為OFF表示沒有開啟二進制日志（binary log）。ON表示開啟了二進制日志（binary log）
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
查看當前服務器所有的二進制日志文件
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec)
查看當前二進制日志文件狀態
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
為了避免一個文件過大，我們可以適當的將文件的內容分開存儲，這就是日志滾動，比如：當超過1G，日志會滾動。當然，你也可以按照文件大小自定義， 時間定義。想要手動滾動日志，執行“flush logs;”即可。
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看第一個binlog文件的內容
mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Rotate | 1 | 201 | mysql-bin.000002;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)
查看某個特定binglog文件的內容
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> create database mood;
Query OK, 1 row affected (0.00 sec)
mysql> use mood;Database changed
mysql> create table info (id int primary key auto_increment,name char(10) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name)values('lisi');
Query OK, 1 row affected (0.01 sec)
mysql> select *from info;
+----+------+
| id | name |
+----+------+
| 1 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 791 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 1 | 313 | create database mood |
| mysql-bin.000003 | 313 | Anonymous_Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 378 | Query | 1 | 528 | use `mood`; create table info (id int primary key auto_increment,name char(10) not null) |
| mysql-bin.000003 | 528 | Anonymous_Gtid | 1 | 593 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 593 | Query | 1 | 665 | BEGIN |
| mysql-bin.000003 | 665 | Table_map | 1 | 715 | table_id: 219 (mood.info) |
| mysql-bin.000003 | 715 | Write_rows | 1 | 760 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000003 | 760 | Xid | 1 | 791 | COMMIT /* xid=23 */ |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)使用mysqlbinlog命令如何查看二進制日志文件中的的內容
[root@localhost ~]# cd /usr/local/mysql/data/ //想進入data目錄下 [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows –vmysql-bin.000003 //64位解碼查看二進制日志 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4#180903 21:35:36 server id 1 end_log_pos 123 CRC32 0x42461cf1 Start: binlog v 4, server v 5.7.17-log created 180903 21:35:36# Warning: this binlog is either in use or was not closed properly. # at 123#180903 21:35:36 server id 1 end_log_pos 154 CRC32 0xec3d8563 Previous-GTIDs # [empty]# at 154#180903 21:37:18 server id 1 end_log_pos 219 CRC32 0xbc79d089 Anonymous_GTID last_committed=0 sequence_number=1SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219#180903 21:37:18 server id 1 end_log_pos 313 CRC32 0xf7bec480 Query thread_id=3 exec_time=0 error_code=0SET TIMESTAMP=1535981838/*!*/;SET @@session.pseudo_thread_id=3/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1437073414/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database mood/*!*/; # at 313#180903 21:37:26 server id 1 end_log_pos 378 CRC32 0x23338ada Anonymous_GTID last_committed=1 sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 378#180903 21:37:26 server id 1 end_log_pos 528 CRC32 0x2abda936 Query thread_id=3 exec_time=0 error_code=0use `mood`/*!*/;SET TIMESTAMP=1535981846/*!*/;create table info (id int primary key auto_increment,name char(10) not null)/*!*/; # at 528#180903 21:37:51 server id 1 end_log_pos 593 CRC32 0xfb0a8540 Anonymous_GTID last_committed=2 sequence_number=3SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 593#180903 21:37:51 server id 1 end_log_pos 665 CRC32 0x092ccb69 Query thread_id=3 exec_time=0 error_code=0SET TIMESTAMP=1535981871/*!*/;BEGIN/*!*/; # at 665#180903 21:37:51 server id 1 end_log_pos 715 CRC32 0x7bc30dc3 Table_map: `mood`.`info` mapped to number 219# at 715#180903 21:37:51 server id 1 end_log_pos 760 CRC32 0x634de617 Write_rows: table id 219 flags: STMT_END_F ### INSERT INTO `mood`.`info` ### SET### @1=1### @2='lisi'# at 760#180903 21:37:51 server id 1 end_log_pos 791 CRC32 0x2b8944b4 Xid = 23COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
增量備份 是指在一次全備份或上一次增量備份后，以后每次的備份只需備份與前一次相比增加或者被修改的文件。這就意味著，第一次增量備份的對象是進行全備后所產生的增加和修改的文件；第二次增量備份的對象是進行第一次增量備份后所產生的增加和修改的文件，如此類推。這種備份方式最顯著的優點就是：沒有重復的備份數據，因此備份的數據量不大，備份所需的時間很短。但增量備份的數據恢復是比較麻煩的。您必須具有上一次全備份和所有增量備份磁帶（一旦丟失或損壞其中的一個增量，就會造成恢復的失敗），并且它們必須沿著從全備份到依次增量備份的時間順序逐個反推恢復，因此這就極大地延長了恢復時間。
MYSQL數據庫在使用過程中，一般都需要對使用的數據庫進行備份處理，對于數據量較小時可以通過mysqldump命令進行數據庫全備份，但是當數據庫數據量達到一定程度之后，顯然增量備份更加適合。 假如我們有一個數據庫，有20G的數據，每天會增加10M的數據，數據庫每天都要全量備份一次，這樣的話服務器的壓力比較大，因此我們只需要備份增加的這部分數據，這樣減少服務器的負擔。
2、binlog簡介
binlog日志由配置文件的 log-bin 選項負責啟用，MySQL服務器將在數據根目錄創建兩個新文 件XXX-bin.001和xxx-bin.index，若配置選項沒有給出文件名，Mysql將使用主機名稱命名這兩個文件，其中.index文件包含一份全體日志文件的清單。
Mysql會把用戶對所有數據庫的內容和結構的修改情況記入XXX-bin.n文件，而不會記錄
SELECT和沒有實際更新的UPDATE語句。
當MySQL數據庫停止或重啟時，服務器會把日志文件記入下一個日志文件，Mysql會在重啟時生成一個新的binlog日志文件，文件序號遞增，此外，如果日志文件超過max_binlog_size系統變量配置的上限時，也會生成新的日志文件。
mysqlbinlog的常用的[options]： 1--start-time #起始時間 2--stop-time #結束時間 3--start-position #基于起始位置來顯示信息 4--stop-position #指定結束位置來顯示
下面做一個簡單的斷點恢復增量備份實例：
先看下基本環境
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mood | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use moodDatabase changed mysql> show tables; +----------------+ | Tables_in_mood | +----------------+ | info | +----------------+ 1 row in set (0.00 sec) mysql> select *from info; +----+------+ | id | name | +----+------+ | 1 | lisi | +----+------+ 1 row in set (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 791 | +------------------+-----------+ 3 rows in set (0.00 sec)
首先進行一個完全備份 [root@localhost data]# mysqldump -uroot -p mood > /opt/mood.sql Enter password:
刷新日志生成尾號為000004的新二進制日志
[root@localhost data]# mysqladmin -uroot -p flush-logs Enter password: [root@localhost data]# ls auto.cnf ib_logfile1 mysql-bin.000001 mysql-bin.index sys ib_buffer_pool ibtmp1 mysql-bin.000002 mysql_error.logibdata1 mood mysql-bin.000003 mysql_olderror.logib_logfile0 mysql mysql-bin.000004 performance_schema
進入數據庫模擬一個誤操作，這里誤操作為刪除lisi這個條目。
mysql> use moodDatabase changed
mysql> insert into info (name)values('test01');
Query OK, 1 row affected (0.00 sec)
mysql> delete from info where name='lisi';
Query OK, 1 row affected (0.01 sec)
mysql> insert into info (name)values('test02');
Query OK, 1 row affected (0.00 sec)
mysql> select *from info;
+----+--------+
| id | name |
+----+--------+
| 2 | test01 |
| 3 | test02 |
+----+--------+
2 rows in set (0.00 sec)再次刷新日志
[root@localhost data]# mysqladmin -u root -p flush-logs Enter password:
看到data下生產了新的000005日志，但是我們剛剛的操作是寫入了000004日志中，我們對00004日志進行64位解碼并轉存位一個txt文件便于我們查看。
[root@localhost data]# ls auto.cnf ibtmp1 mysql-bin.000003 mysql_olderror.logib_buffer_pool mood mysql-bin.000004 performance_schema ibdata1 mysql mysql-bin.000005 sys ib_logfile0 mysql-bin.000001 mysql-bin.indexib_logfile1 mysql-bin.000002 mysql_error.log [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /opt/info.txt
再opt下vim查看生成出來的txt文件，也就是剛剛的000004日志。
找到那條誤操作的命令，記錄下它以及前后位置的時間和位置數據。
| 操作時間 | 位置 | 類型 |
| 2018-09-03 22:15:31 | 341 | 正確操作一 |
| 2018-09-03 22:18:03 | 606 | 誤操作 |
| 2018-09-03 22:18:09 | 869 | 正確操作二 |
尋找位置如圖：
下面示范兩種跳過方式
1.通過時間識別
首先還原到完全備份時的數據表狀態。
mysql> use mood Database changed mysql> drop table info; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql> source /opt/mood.sql Query OK, 0 rows affected (0.00 sec) mysql> select *from info; +----+------+ | id | name | +----+------+ | 1 | lisi | +----+------+ 1 row in set (0.00 sec)
跳過刪除lisi那一步誤操作進行增量備份還原。
第一條命令：恢復到22.18.03（錯誤發生時間點）為止！不執行錯誤時間點寫入的命令，后面一切命令也不執行。
第二條命令：從22.18.09 （下一條正確命令時間點）開始！前面一切命令不執行。
這樣我們就跳過了哪一條delete命令
[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2018-09-03 22:18:03' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pEnter password: [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+--------+ | id | name | +----+--------+ | 1 | lisi | | 2 | test01 | +----+--------+ [root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2018-09-03 22:18:09' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pEnter password: [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+--------+ | id | name | +----+--------+ | 1 | lisi | | 2 | test01 | | 3 | test02 | +----+--------+
2.通過位置操作
還是還原到完全備份時的數據表狀態。
mysql> use moodDatabase changed mysql> drop table info; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql> source /opt/mood.sql Query OK, 0 rows affected (0.00 sec) mysql> select *from info; +----+------+ | id | name | +----+------+ | 1 | lisi | +----+------+ 1 row in set (0.00 sec)
回頭看一下上面的表格，我們得知：
錯誤操作的位置號是606，它的上一個操作位置號是556，下一個操作位置號是651
即：
556-606-651
那我們只要跳過606即可！
操作如下：
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='556' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pabc123 mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# mysqlbinlog --no-defaults --start-position='651' /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pabc123 mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt]# mysql -uroot -pabc123 -e 'use mood;select *from info;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+--------+ | id | name | +----+--------+ | 1 | lisi | | 2 | test01 | | 3 | test02 | +----+--------+
完成！
ps:warning提示的是把密碼寫在命令中并不安全，這里模擬試驗圖省事，大家實際操作還是不要寫在命令中哦！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
