MARIADB binary log and backup recovery

I. Logs and their descriptions

1. Error log

Primary record server startup and shutdown information during server run error message, the transaction scheduler runs an event that is generated by the log information that starts from the server from the server on the startup process that is generated by the log information.

2. General Inquiry Log

General_log, General_log_file, log, Log_output

3. Slow query Log

The query execution time exceeds the specified query length, which is a slow query. The main configuration files are: Slow_query_log,slow_query_log_file/mydata/data/hostname.log

4. Binary Log

Any operations that cause or may cause changes to the database, such as replication, instant point recovery, and so on. The binary log records data for each DDL,DML,DCL command and its restart, which causes the database to change.

5. Relay Log

relay_log_purge={on| OFF} #是否自动清理不再需要中继日志

6. Transaction log

The random IO is switched to the CIS IO to ensure the consistency, atomicity and completeness of the data.

Two, binary log detailed

1. The format of the binary log

Statement based: statement; row: row; Mixed mode: Mixed

2. Binary log files

Index file (mysql-bin.index), binary log file (mysql-bin.00000x)

3, binary log function

Instant recovery, replication

4. Common Binary Log commands

All binary log files are displayed: Mysql>show binary logs;

Perform scrolling, that is, the binary log will automatically scroll once: Mysql>flush logs;

Displays the currently used binary log file: Mysql>show master status;

Command line view binary log file contents: #mysqlbinlog mysql-bin.000001

View binary log file contents: Mysql>show binlog events in ' mysql-bin.000001 ';

Mysqlbinlog Common options:--start-time--stop-time--start-position--stop-position

Icon:

Show binary log files

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/88/91/wKiom1f7od3BxS-7AAA21Ze0JOY973.png "title=" Bin.png "alt=" Wkiom1f7od3bxs-7aaa21ze0joy973.png "/>

The binary file format is as follows:

[[email protected] data]# mysqlbinlog --stop-position=1451  '/mydata/binlog/ master-bin.000001 '/*!50530 set @ @SESSION. pseudo_slave_mode=1*/;/*!40019 set @ @session. max_insert_delayed_threads=0*/;/*!50003 set @ [email protected] @COMPLETION_TYPE, completion_type=0*/;D elimiter /*!*/;# at 4#160929  11:41:17 server id 1  end_log_pos 245 start: binlog v 4,  server v 5.5.44-MariaDB-log created 160929 11:41:17 at  startuprollback/*!*/; binlog  ' Xy3svw8baaaa8qaaapuaaaaaaaqans41ljq0lu1hcmlhreitbg9naaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabdjexxezgnaagaegaebaqeegaa2 Qaegggaaaaicagcaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaazwk5ww== '/*!*/;# at 245# 160929 13:39:36 server id 1  end_log_pos 332 querythread_id=4exec_time=0error_code=0set timestamp=1475127576/ *!*/; set @ @session. pseudo_thread_id=4/*!*/; set @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1,  @ @session. autocommit=1/*!*/; set @ @session. sql_mode=0/*!*/; 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 testdb/*!*/;# at 332# 160929 13:40:40 server id 1  end_log_pos 454 querythread_id=5exec_time =0error_code=0use  ' TestDB '/*!*/; set timestamp=1475127640/*!*/; set @ @session. foreign_key_checks=0, @ @session. unique_checks=0/*!*/; set @ @session. Sql_mode=524288/*!*/;D rop table if exists  ' students '  /* generated by server  *//*!*/;# at 454#160929 13:40:40 server id 1  end_log_pos  752 querythread_id=5exec_time=0error_code=0set timestamp=1475127640/*!*/; create table  ' Students '   (   ' id '  int (TEN)  unsigned NOT NULL,    ' name '  varchar  DEFAULT NULL,   ' age '  tinyint (3)  unsigned  default null,   ' Gender '  enum (' f ', ' m ')  default null,  primary key   (' id '))  engine=innodb default charset=utf8/*!*/;# at 752#160929 13:40:40  server id 1  end_log_pos 864 querythread_id=5exec_time=0error_code=0set  TIMESTAMP=1475127640/*!*/;/*!40000 ALTER TABLE  ' Students '  disable keys *// *!*/;# at 864#160929 13:40:40 server id 1  end_log_pos 975 querythread_id=5exec_time=0error_code=0set  timestamp=1475127640/*!*/;/*!40000 alter table  ' Students '  enable keys *//*!*/;#  at 975#160929 13:40:40 server id 1  end_log_pos 1091  querythread_id=5exec_time=0error_code=0set timestamp=1475127640/*!*/;D rop table if exists   ' T2 '  /* generated by server *//*!*/;# at 1091#160929 13:40:40  server id 1  end_log_pos 1240 querythread_id=5exec_time=0error_code=0set  TIMESTAMP=1475127640/*!*/; create table  ' T2 '   (   ' id '  int (one)  default null)  engine=innodb  DEFAULT CHARSET=utf8/*!*/;# at 1240#160929 13:40:40 server id 1   end_log_pos 1346 querythread_id=5exec_time=0error_code=0set timestamp=1475127640/*!*/;/*!40000 alter table  ' T2 '  DISABLE KEYS *//*!*/;# at 1346#160929  13:40:40 server id 1  end_log_pos 1451 querythread_id=5exec_time=0error_ code=0set timestamp=1475127640/*!*/;/*!40000 alter table  ' T2 '  ENABLE KEYS * *!*/;D elimiter ;# end of log filerollback /* added by  mysqlbinlog */;/*!50003 set [email protected]_completion_type*/;/*!50530 set @@ SESSION. pseudo_slave_mode=0*/; [[email protected] data]#

The above format description:

Date and time when the event occurred

Server ID

Where the time ends

Type of Event

The thread ID of the original server that generated this event

Timestamp of the statement and time difference between writing to the binary log file

Error code

Event Content

Where to start the next event

Common Server Parameters:

Log_bin = {on| OFF}, this can be a file path

Log_bin_trust_funcition_creators

Sql_log_bin = {on| OFF}

Sync_binlog

Binlog_format = mixed {statement|row|mixed}

Max_binlog_cache_size = #二进制日志的缓冲区大小, only for the statement that buffers the transaction class

Max_binlog_stmt_cache_size = #状态缓冲区大小

Max_binlog_size = #二进制日志文件的上限, the excess will automatically scroll

Set session sql_log_bin=0; #可使得不记录二进制日志文件

Note: The binary log file and the data file are stored in the same file

Icon:

All log-related variables

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/88/8E/wKioL1f7oE7BnHVhAABhB7xrKHo993.png "style=" float: none; "title=" Log1.png "alt=" Wkiol1f7oe7bnhvhaabhb7xrkho993.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/88/91/wKiom1f7qTqj-x_MAABN483Z0Dc954.png "title=" 2L "[ KG2 ({6HDCAE (u6eu~2i.png "alt=" Wkiom1f7qtqj-x_maabn483z0dc954.png "/>

Migrating binary files to other directories

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/88/91/wKiom1f7qO_Bp1n2AAA-V4q2NoY841.png "title=" Qianyi.png "alt=" Wkiom1f7qo_bp1n2aaa-v4q2noy841.png "/>

Third, data backup and its recovery

The primary purpose of data backup is to perform recovery testing, auditing, and testing of backup data for data recovery.

1. Type of Backup

Cold: Cold Backup Win Bei: Warm backup hot standby: Hot backup

2, according to the backup data set is divided into

Full backup: Partial backup

3, according to the interface when the backup (directly backup data files or through the MySQL server export data) is divided into

Physical Backup: Direct copy (archive) data file backup mode, physical backup (applicable when data volume is large)

Logical backup: Extract data from the database to save as a file, logical backup (file size is larger than 10G), the main Backup tool is (mysqldump). The MyISAM only supports Win Bei and does not support incremental backups, while InnoDB is hot standby for incremental backups.

4. Backup tool (Mysqldump)

Myslqdump is a logical backup tool, backup and recovery is slow, only suitable for the data volume is not very large database.

mysqldump command:

mysqldump [Options] [db_name [Tbl_name ...]

Backing up a single database: Mysqldump db_name

Example: Mysqldump-uroot-hlocalhost-p testdb >/tmp/testdb.sql

To restore data, you need to create a library before performing data recovery: Mysql-uroot-p Testdb</tmp/testdb.sql

Back up all databases: mysqldump--all-databases >/tmp/all.sql

Backing up multiple databases: mysqldump-utestuser-h10.1.10.1-p--databases testdb Test >/tmp/testdbs.sql

Restore without first creating a library: Mysql-utestuser-h10.1.10.1-p </tmp/testdbs.sql

Icon:

A single-table backup can be restored to a database by preparing the library beforehand

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/88/93/wKiom1f8P7HjnD5bAABMyDCjtLE856.png "title=" Dump.png "alt=" Wkiom1f8p7hjnd5baabmydcjtle856.png "/>

Backing up multiple databases and their recovery

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/88/8F/wKioL1f8QtKgor1iAABPxNx7ahc062.png "title=" 2.png " alt= "Wkiol1f8qtkgor1iaabpxnx7ahc062.png"/>

Note: The appeal backup scheme has some drawbacks, such as: When the user is performing data operation, the data will be lost and so on.

Solution: The backup is to add locks to the library table to ensure the integrity of the data.

--lock-all-tables: Request Lock All tables

Example: mysqldump--databases testdb--lock-all-tables >/tmp/testdb.sql

--single-transaction: Single transaction, capable of hot provisioning of the InnoDB storage engine

--events: Backup Event Scheduler Code--routines: Backup stored procedure and storage function--triggers: Backup trigger

Scroll log After request lock on backup:--flush-logs

Sync location Tag When copying:--master-data=[0|1|2]

Note: You need to close the binaries when recovering, then turn on

Turn off binary logging: Set session sql_log_bin=0

Open Binary log: Set session Sql_log_bin=1

Lock table Backup steps:

1) lock table and give read-only permission

Flush tables with read lock;

2) Scrolling Log

Flush logs;

3) View the binary log information

Show master status;

4) Use mysqldump for data backup

Mysqldump--databases TestDB >/tmp/testdb.sql

5) After the backup is complete, you need to unlock it.

Unlock tables;

The command-line backup instance is as follows:

Mysqldump-p--databases testdb--lock-all-tables--flush-logs >/tmp/testdb2.sql

Mysqldump-p--databases testdb--single-trasaction--flush-logs >/tmp/testdb3.sql

Mysqldump-p--databases testdb--lock-all-tables--flush-logs--master-data=2 >/tmp/testdb4.sql

Icon:

Lock table and implement backup here, unlock after backup

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/88/93/wKiom1f8S2OBF-NvAAAkQmCh4mo358.png "style=" float: none; "title=" Suobiao.png "alt=" Wkiom1f8s2obf-nvaaakqmch4mo358.png "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/88/90/wKioL1f8S2ThhkydAABrEdLlN5M190.png "style=" float: none; "title=" status. png "alt=" Wkiol1f8s2thhkydaabredlln5m190.png "/>

Small ears of original works, a little bit more progress every day.

This article is from the "Small Ears" blog, please be sure to keep this source http://purify.blog.51cto.com/10572011/1860516

MARIADB binary log and backup recovery