您好,登錄后才能下訂單哦!
-----------------------------日志-----------------------------------
MySQL的日志類型有以下幾種:
1. 錯誤日志(error),MySQL服務(wù)實例啟動、運行或者停止等相關(guān)信息。 2. 普通查詢?nèi)罩荆╣eneral),MySQL服務(wù)實例運行的所有SQL語句或者M(jìn)ySQL命令。 3. 二進(jìn)制日志(binary),對數(shù)據(jù)庫執(zhí)行的所有更新語句,不包括select 和show語句。 4. 慢查詢?nèi)罩荆╯low),執(zhí)行時間超過long_query_time 設(shè)置值的SQL語句,或者沒有使用索引的SQL語句。
默認(rèn)情況下,所有的MySQL日志以文件的方式存放在數(shù)據(jù)庫根目錄下:
[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啟動和停止,以及服務(wù)器在運行過程中發(fā)生的錯誤的相關(guān)信息。在默認(rèn)情況下,系統(tǒng)記錄錯誤日志的功能是關(guān)閉的,錯誤信息被輸出到標(biāo)準(zhǔn)錯誤輸出。需要在啟動的時候開啟 log-error選項
如果沒有指定文件名 默認(rèn)hostname.err,默認(rèn)路徑為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 //重啟服務(wù) [root@localhost data]# ls //查看文件是否產(chǎn)生日志文件 auto.cnf ibdata1 ib_logfile1 mysql performance_schema ib_buffer_pool ib_logfile0 ibtmp1 mysql_error.log sys mysql> show variables like '%err%'; //查看錯誤日志狀態(tài) +---------------------+---------------------------------------+ | 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.二進(jìn)制日志(重點!)
二進(jìn)制日志不會記錄select ,show 等不修改數(shù)據(jù)的語句。打開二進(jìn)制日志會消耗一些系統(tǒng)系能,但是對于復(fù)制和系統(tǒng)恢復(fù),所帶來的好處大于減少的性能,它用來實現(xiàn)復(fù)制的基本憑據(jù)。也就是說,你可以將生成環(huán)境中的MySQL的二進(jìn)制文件拿到線下的服務(wù)器上運行一下,理論上你會拿到和生成環(huán)境中一樣的數(shù)據(jù),因此,二進(jìn)制日志也叫復(fù)制日志。二進(jìn)制日志文件默認(rèn)在數(shù)據(jù)目錄下,通常情況下為mysql-bin#(例如:mysql-bin.000001,mysql-bin000002)。二進(jìn)制日志即binlog日志 記錄數(shù)據(jù)定義語言(DDL)和數(shù)據(jù)控制語言(DML) 但不包括數(shù)據(jù)查詢語言。
二進(jìn)制日志的主要功能
1、恢復(fù)(recovery)
2、復(fù)制(replication)
二進(jìn)制日志文件內(nèi)容格式
1.事件發(fā)生的日期和時間(會在關(guān)鍵字“at”)
2.服務(wù)器ID(server
id)
3.事件結(jié)束位置(end_log_pos)
4.事件的類型(如:Query,Stop等等)
5.原服務(wù)器生成此事件時的線程ID號(thead_id,可以通過“show
processlist;”進(jìn)行查詢)
6.語句時間戳和寫入二進(jìn)制文件的時間差,單位為秒(exec_time,表示記錄日志所用的時間戳,當(dāng)他等于0時表示沒有用到1秒鐘。)
7.錯誤代碼,0表示正常執(zhí)行(error_code,排查方法就得查看官方文檔。)
8.事件內(nèi)容(修改的SQL語句)
9.事件位置(相當(dāng)于下一事件的開始位置,還是用“at”關(guān)鍵字標(biāo)志)
開啟二進(jìn)制日志
[root@localhost data]# vim /etc/my.cnf //編輯配置文件 再[mysqld]下添加: log_bin=mysql-bin [root@localhost data]# systemctl restart mysqld.service //重啟服務(wù)
二進(jìn)制日志狀態(tài)查看
系統(tǒng)變量log_bin的值為OFF表示沒有開啟二進(jìn)制日志(binary log)。ON表示開啟了二進(jìn)制日志(binary log)
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
查看當(dāng)前服務(wù)器所有的二進(jìn)制日志文件
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec)
查看當(dāng)前二進(jìn)制日志文件狀態(tài)
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)
為了避免一個文件過大,我們可以適當(dāng)?shù)膶⑽募膬?nèi)容分開存儲,這就是日志滾動,比如:當(dāng)超過1G,日志會滾動。當(dāng)然,你也可以按照文件大小自定義, 時間定義。想要手動滾動日志,執(zhí)行“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文件的內(nèi)容
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文件的內(nèi)容
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命令如何查看二進(jìn)制日志文件中的的內(nèi)容
[root@localhost ~]# cd /usr/local/mysql/data/ //想進(jìn)入data目錄下 [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows –vmysql-bin.000003 //64位解碼查看二進(jìn)制日志 /*!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*/;
增量備份 是指在一次全備份或上一次增量備份后,以后每次的備份只需備份與前一次相比增加或者被修改的文件。這就意味著,第一次增量備份的對象是進(jìn)行全備后所產(chǎn)生的增加和修改的文件;第二次增量備份的對象是進(jìn)行第一次增量備份后所產(chǎn)生的增加和修改的文件,如此類推。這種備份方式最顯著的優(yōu)點就是:沒有重復(fù)的備份數(shù)據(jù),因此備份的數(shù)據(jù)量不大,備份所需的時間很短。但增量備份的數(shù)據(jù)恢復(fù)是比較麻煩的。您必須具有上一次全備份和所有增量備份磁帶(一旦丟失或損壞其中的一個增量,就會造成恢復(fù)的失敗),并且它們必須沿著從全備份到依次增量備份的時間順序逐個反推恢復(fù),因此這就極大地延長了恢復(fù)時間。
MYSQL數(shù)據(jù)庫在使用過程中,一般都需要對使用的數(shù)據(jù)庫進(jìn)行備份處理,對于數(shù)據(jù)量較小時可以通過mysqldump命令進(jìn)行數(shù)據(jù)庫全備份,但是當(dāng)數(shù)據(jù)庫數(shù)據(jù)量達(dá)到一定程度之后,顯然增量備份更加適合。 假如我們有一個數(shù)據(jù)庫,有20G的數(shù)據(jù),每天會增加10M的數(shù)據(jù),數(shù)據(jù)庫每天都要全量備份一次,這樣的話服務(wù)器的壓力比較大,因此我們只需要備份增加的這部分?jǐn)?shù)據(jù),這樣減少服務(wù)器的負(fù)擔(dān)。
2、binlog簡介
binlog日志由配置文件的 log-bin 選項負(fù)責(zé)啟用,MySQL服務(wù)器將在數(shù)據(jù)根目錄創(chuàng)建兩個新文 件XXX-bin.001和xxx-bin.index,若配置選項沒有給出文件名,Mysql將使用主機(jī)名稱命名這兩個文件,其中.index文件包含一份全體日志文件的清單。
Mysql會把用戶對所有數(shù)據(jù)庫的內(nèi)容和結(jié)構(gòu)的修改情況記入XXX-bin.n文件,而不會記錄
SELECT和沒有實際更新的UPDATE語句。
當(dāng)MySQL數(shù)據(jù)庫停止或重啟時,服務(wù)器會把日志文件記入下一個日志文件,Mysql會在重啟時生成一個新的binlog日志文件,文件序號遞增,此外,如果日志文件超過max_binlog_size系統(tǒng)變量配置的上限時,也會生成新的日志文件。
mysqlbinlog的常用的[options]: 1--start-time #起始時間 2--stop-time #結(jié)束時間 3--start-position #基于起始位置來顯示信息 4--stop-position #指定結(jié)束位置來顯示
下面做一個簡單的斷點恢復(fù)增量備份實例:
先看下基本環(huán)境
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)
首先進(jìn)行一個完全備份 [root@localhost data]# mysqldump -uroot -p mood > /opt/mood.sql Enter password:
刷新日志生成尾號為000004的新二進(jìn)制日志
[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
進(jìn)入數(shù)據(jù)庫模擬一個誤操作,這里誤操作為刪除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下生產(chǎn)了新的000005日志,但是我們剛剛的操作是寫入了000004日志中,我們對00004日志進(jìn)行64位解碼并轉(zhuǎn)存位一個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日志。
找到那條誤操作的命令,記錄下它以及前后位置的時間和位置數(shù)據(jù)。
操作時間 | 位置 | 類型 |
2018-09-03 22:15:31 | 341 | 正確操作一 |
2018-09-03 22:18:03 | 606 | 誤操作 |
2018-09-03 22:18:09 | 869 | 正確操作二 |
尋找位置如圖:
下面示范兩種跳過方式
1.通過時間識別
首先還原到完全備份時的數(shù)據(jù)表狀態(tài)。
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那一步誤操作進(jìn)行增量備份還原。
第一條命令:恢復(fù)到22.18.03(錯誤發(fā)生時間點)為止!不執(zhí)行錯誤時間點寫入的命令,后面一切命令也不執(zhí)行。
第二條命令:從22.18.09 (下一條正確命令時間點)開始!前面一切命令不執(zhí)行。
這樣我們就跳過了哪一條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.通過位置操作
還是還原到完全備份時的數(shù)據(jù)表狀態(tài)。
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提示的是把密碼寫在命令中并不安全,這里模擬試驗圖省事,大家實際操作還是不要寫在命令中哦!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。