Shulou(Shulou.com)06/01 Report--

-- Journal--

There are several log types for MySQL:

1. Error log (error), MySQL service instance start, run or stop and other related information. two。 Normal query log (general), all SQL statements or MySQL commands that the MySQL service instance runs. 3. Binary log (binary), all update statements executed against the database, excluding select and show statements. 4. Slow query log (slow), a SQL statement that takes longer to execute than the value set by long_query_time, or a SQL statement that does not use an index.

By default, all MySQL logs are stored as files in the database root directory:

[root@localhost data] # pwd/usr/local/mysql/data [root@localhost data] # lsauto.cnf ibdata1 ib_logfile1 mysql sysib_buffer_pool ib_logfile0 ibtmp1 performance_schema

Here we mainly talk about two kinds of logs.

1. Error log

The error log records information about mysqld starts and stops, as well as errors that occur while the server is running. By default, the system's ability to log errors is turned off and error messages are output to standard error output. You need to turn on the log-error option at startup

If you do not specify a file name default hostname.err, the default path is the datadir directory

To open the error log:

[root@localhost data] # vim / etc/my.cnf / / Edit the configuration file under [mysqld]: log-error=/usr/local/mysql/data/mysql_ error.log [root @ localhost data] # systemctl restart mysqld.service / / restart the service [root@localhost data] # ls / / check whether the document generates a log file auto.cnf ibdata1 ib_logfile1 mysql performance_schemaib_buffer _ pool ib_logfile0 ibtmp1 mysql_error.log sysmysql > show variables like'% err%' / / View error log status +-+-+ | 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)

Clean up the error log: [root@localhost data] # lsauto.cnf ibdata1 ib_logfile1 mysql performance_schemaib_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-logsmysqladmin: [Warning] Using a password on the command line interface can be insecure.

two。 Binary log (key!)

Binary logs do not record statements such as select, show, and so on that do not modify data. Opening binary logs consumes some system power, but for replication and system recovery, the benefits outweigh the reduced performance, which is used to implement the basic credentials of replication. In other words, you can run the MySQL binaries in the build environment on an offline server, and theoretically you will get the same data as in the build environment, so binary logs are also called replication logs. Binary log files are in the data directory by default, usually mysql-bin# (for example: mysql-bin.000001,mysql-bin000002). Binary logging is binlog logging data definition language (DDL) and data Control language (DML), but does not include data query language.

The main functions of binary logs

1. Restore (recovery)

2. Copy (replication)

Binary log file content format

1. Date and time when the event occurred (in the keyword "at")

two。 Server ID (server id)

3. Event end location (end_log_pos)

4. Type of event (e. G. Query,Stop, etc.)

5. The thread ID number when the original server generated this event (thead_id, which can be queried through "show processlist;")

6. The difference between the statement timestamp and the time it takes to write to the binary file, in seconds (exec_time), which represents the timestamp used to record the log, and when it equals 0 indicates that no 1 second has been used. )

7. Error code, 0 indicates normal execution (error_code, troubleshooting method has to check the official documentation. )

8. Event content (modified SQL statement)

9. Event location (equivalent to the start of the next event, or marked with the "at" keyword)

Open binary log

[root@localhost data] # vim / etc/my.cnf / / Edit the configuration file and then add: log_bin=mysql-bin [root@localhost data] # systemctl restart mysqld.service / / restart the service under [mysqld]

Binary log status view

The value of the system variable log_bin, OFF, indicates that binary logging (binary log) is not turned on. ON indicates that binary logging is enabled (binary log)

Mysql > show variables like 'log_bin';+-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec)

View all the binary log files on the current server

Mysql > show binary logs;+-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 154,154 | +-+-+ 1 row in set (0.00 sec)

View the current binary log file status

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)

In order to avoid a file being too large, we can properly store the contents of the file separately, which is called log scrolling. For example, when the log exceeds 1G, the log will scroll. Of course, you can also customize the time definition according to the file size. To scroll the log manually, execute "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)

View the contents of the first binlog file

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 | Rotate | 1 | 201 | mysql-bin.000002 Pos=4 | +-+-- + 3 rows in set (0.00 sec)

View the contents of a specific binglog file

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 changedmysql > 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 | 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)

How to view the contents of a binary log file using the mysqlbinlog command

[root@localhost ~] # cd / usr/local/mysql/data/ want to enter [root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-vmysql-bin.000003 / / 64-bit decode to view binary logs / *! 50530 SET @ @ SESSION.PSEUDOSLAVESLAVEMODEBUTlGRAM 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 Flux 35 this binlog is either in use or was not closed properly.# at 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 banks # at 219 session 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 pick up set @ @ session.pseudothread readreadable cards 3pxt set @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.unique_checks=1, @ @ session.session .sqlcards models 1437073414 SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement incrementalization offsetbacks 1 racemaker races pinch packs!\ C utf8 * / / *! * /; SET @ @ session.sessions setsetclientholders 331 minutes session .session. Collationalization contacts connections 3 copies session .session. Collationalization timetables namespaces 33 canvasseset @ @ session.lcbooks timestamps namespaces 0 minutes session holders set @ session .collection databases database sets @ session .collection databases databases DEULTGULTGULTGULT examples # 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= 'ANONYMOUSAccording to the SET" 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 TIMESTAMPay 1535981846 Universe 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 593mm CRC32 0xfb0a8540 Anonymous_GTID last_committed=2 sequence_number=3SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to server id # at 593" 180903 21:37:51 server id 1 end_log_pos 665 timespace 1535981871 # at 665 mapped to number 180903 21:37:51 server id 1 end_log_pos 715 CRC32 0x7bc30dc3 Table_map: `mood`.`info` mapped to number 2 "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 end_log_pos 791 CRC32 0x2b8944b4 Xid @ 2='lisi'# at 760 "180903 21:37:51 server id 1 end_log_pos 791 CRC32 0x2b8944b4 Xid = 23com SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filebank / 50003 SET completion incremental backup / 50530 incremental backup

Incremental backup means that after a full backup or the last incremental backup, each subsequent backup only needs to back up the files that have been added or modified compared with the previous one. This means that the objects of the first incremental backup are the files added and modified after the full backup, the objects of the second incremental backup are the files added and modified after the first incremental backup, and so on. The most obvious advantage of this backup method is that there is no duplicate backup data, so the amount of backup data is small, and the backup time is very short. But the data recovery of incremental backup is troublesome. You must have the last full backup and all incremental backup tapes (if one of the increments is lost or damaged, it will cause the restore to fail), and they must be restored one by one in the chronological order from the full backup to the incremental backup, so this greatly increases the recovery time.

In the process of using MYSQL database, it is generally necessary to back up the database used. For a small amount of data, you can use the mysqldump command for full backup of the database, but when the amount of database data reaches a certain extent, it is obvious that incremental backup is more suitable. If we have a database with 20 gigabytes of data, 10 megabytes of data will be added every day, and the database will be fully backed up once a day, so the pressure on the server will be greater, so we only need to back up this part of the data. This reduces the burden on the server.

2. Introduction to binlog

Binlog logging is enabled by the log-bin option of the configuration file. The MySQL server will create two new text XXX-bin.001 and xxx-bin.index in the data root directory. If the configuration option does not give a file name, Mysql will name the two files with the host name, where the .index file contains a list of all log files.

Mysql records the changes made by users to the content and structure of all databases in the XXX-bin.n file, instead of recording SELECT and UPDATE statements that are not actually updated.

When the MySQL database is stopped or restarted, the server will record the log file in the next log file, and Mysql will generate a new binlog log file on restart, with an increment of the file sequence number. In addition, if the log file exceeds the upper limit of the max_binlog_size system variable configuration, a new log file will be generated.

Mysqlbinlog's commonly used [options]: 1--start-time # start time 2--stop-time # end time 3--start-position # display information based on the start position 4--stop-position # specify the end position to display

Here is a simple breakpoint recovery incremental backup instance:

Take a look at the basic environment first.

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mood | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec) mysql > use moodDatabase changedmysql > 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) first make a full backup [root@localhost data] # mysqldump-uroot-p mood > / opt/mood.sqlEnter password:

Refresh the log to generate a new binary log with the tail number 000004

[root@localhost data] # mysqladmin-uroot-p flush-logsEnter password: [root@localhost data] # lsauto.cnf ib_logfile1 mysql-bin.000001 mysql-bin.index sysib_buffer_pool ibtmp1 mysql-bin.000002 mysql_error.logibdata1 mood mysql-bin.000003 mysql_olderror.logib_logfile0 mysql mysql-bin.000004 performance_schema

Enter the database to simulate a misoperation, which is to delete the entry lisi.

Mysql > use moodDatabase changedmysql > 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)

Refresh the log again

[root@localhost data] # mysqladmin-u root-p flush-logsEnter password:

We can see that the new 000005 log has been produced under data, but what we just did was write to 00004 log. We decoded 00004 log with 64 bits and saved a txt file for us to view.

[root@localhost data] # lsauto.cnf ibtmp1 mysql-bin.000003 mysql_olderror.logib_buffer_pool mood mysql-bin.000004 performance_schemaibdata1 mysql mysql-bin.000005 sysib_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

Then vim under opt to view the generated txt file, that is, the 000004 log just now.

Find the misoperated command and record the time and location data of it and its front and back position.

Operation time and position type 2018-09-03 22 15purl 31341 correct operation-2018-09-03 22 purge 18purl 03606 misoperation 2018-09-03 22purl 18purl 09869 correct operation II

Find the location as shown in the figure:

Here are two ways to skip

1. By time identification

First restore to the state of the data table at the time of the full backup.

Mysql > use moodDatabase changedmysql > drop table info;Query OK, 0 rows affected (0.01 sec) mysql > show tables;Empty set (0.00 sec) mysql > source / opt/mood.sqlQuery OK, 0 rows affected (0.00 sec) mysql > select * from info;+----+-+ | id | name | +-- +-+ | 1 | lisi | +-+-+ 1 row in set (0.00 sec)

Skip the erroneous operation of deleting lisi for incremental backup restore.

The first command: restore to 22.18.03 (point in time when the error occurred)! Commands written at the wrong point in time are not executed, and all subsequent commands are not executed.

The second command: start at 22.18.09 (the next correct command point in time)! All previous orders will not be carried out.

So which delete command did we skip?

[root@localhost opt] # mysqlbinlog-- no-defaults-- stop-datetime='2018-09-03 22 no-defaults 18purl 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 2222 18Using a password on the command line interface can be insecure.+----+-+ 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 | +-+-+

two。 Operate by location

Or restore to the state of the data table at the time of the full backup.

Mysql > use moodDatabase changedmysql > drop table info;Query OK, 0 rows affected (0.01 sec) mysql > show tables;Empty set (0.00 sec) mysql > source / opt/mood.sqlQuery OK, 0 rows affected (0.00 sec) mysql > select * from info;+----+-+ | id | name | +-- +-+ | 1 | lisi | +-+-+ 1 row in set (0.00 sec)

Looking back at the table above, we know that:

The location number of the error operation is 606, its last operation position number is 556, and the next operation position number is 651

That is:

556-606-651

Then we just have to skip 606!

Do the following:

[root@localhost opt] # mysqlbinlog-no-defaults-stop-position='556' / usr/local/mysql/data/mysql-bin.000004 | mysql- uroot-pabc123mysql: [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-pabc123mysql: [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 | +-+-+

Done!

Ps:warning prompt is that it is not safe to write the password in the command, here the simulation test diagram is easy, we should not write the actual operation in the command!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.