MySQL log management and MySQL backup restore under Linux

MySQL log management and MySQL backup restore under Linux

1.SHOW GLOBAL VARIABLES like '%log% '; Show all log-related options

2. Error log: Log_error and Log_warnings

3. General Enquiry log: General_log general_log_file log Log_output

4. Slow query log: Long_query_time log_slow_queries={yes|no} slow_query_log low_query_log_file

5. Binary log: Any operation that causes or may cause changes in the database; replication and instant point recovery;

Format of binary log: Binlog_format

Statement based: statement

Row-based: row

Mixing mode: Mixed

Binary Log events: the resulting time and relative position

Binary log files: index files and binary log files

mysql> SHOW MASTER STATUS; #查看当前正在使用的二进制日志文件

mysql> SHOW BINARY LOGS; #查看所有日志文件:

Mysql> SHOW BINLOG EVENTS in ' binary log file ' [from location]; #查看事件:

Example: SHOW BINLOG EVENTS in ' mysql-bin.000004 ' from 107;

Delete binary log files: Delete all log files before the specified binary log file

mysql> PURGE binary LOGS to ' binary log file '

To view the log information: The mysqlbinlog command option is as follows:

--start-datetime--stop-datetime

--start-position--stop-position

Example: Mysqlbinlog--start-position 177--stop-position 331 mysql-bin.000004;

Mysqlbinlog--start-datetime= ' 16-07-07 13:32:07 ' mysql-bin.000004;

Scrolling binary log: FLUSH LOGS;

6. Relay LOG: An event that is copied from the binary log file of the primary server and saved as a log file;

7. Transaction log: The transactional storage engine is used to ensure atomicity, consistency, isolation and persistence;

Innodb_flush_log_at_trx_commit:

0: Synchronize every second, and perform disk flush operation;

1: Synchronize each transaction and perform disk flush operation;

2: Synchronize per transaction, but do not perform disk flush operation;

8.mysqldump Backup and RESTORE Database Tools

8.1. Backing up a single database, or a specific table in a library

Format: mysqldump database name [table 1] [table 2]

--MASTER-DATA={0|1|2}

0: Do not log the binary log file and the location of the road;

1: Record location in Chnage MASTER to, can be used to start the server directly after recovery;

2: Record the position in the form of change MASTER to, but the default is to be commented;

--lock-all-tables: Lock All tables

--flush-logs: Execute log flush;

--single-transaction start hot standby; If the table type in the specified library is InnoDB

8.2. Back up multiple libraries:

--all-databases: Backing Up all libraries

--databases db_name,db_name,...: Backing up the specified library

--events Event Scheduler

--routines stored procedures and stored functions

--triggers Trigger

Single database backup method one:

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 #指定还原至指定数据库

Single database backup method two:

#mysqldump-u root-p--lock-all-tables--flush-logs--masterdata=2 Jiaowu>/root/jiaowu.sql

Full backup of all databases:

#mysqldump-u root-p--lock-all-tables--flush-logs --all-databases --master-data=2 >/root/ Alldatabases.sql

9. Example: Use the mysqldump command to make a full weekly backup of all databases and make incremental backups daily, and to implement a restore

Recommendation: MySQL data directory placed on a standalone hard disk or LVM, binary log files are also placed in other directories

The following simulation is implemented using the experimental environment: note The binary log file name of this experiment is subject to its own experimental environment

9.1. Do a full backup of all databases at weekends

#mysqldump-u root-p--lock-all-tables--flush-logs --all-databases --masterdata=2 >/root/ Alldatabases.sql

9.2. Clear all binaries before scrolling, but it is best to save before clearing

Mysql>purge BINARY LOGS to ' mysql-bin.000010 ';

Mysql>show BINARY LOGS;

9.3. After a full backup of the database, a day after the simulation database has deleted a data

Mysql>use Jiaowu;

Mysql>delete from Tutors WHERE age>80;

9.4. Make incremental backups Daily

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. Simulation Another day, the database has inserted a piece of data

Mysql>use Jiaowu;

Mysql>insert into Tutors (tname) VALUES ("Tom");

9.6. Simulate the day of the database crash, manually delete all files of the data directory to prevent the database to use

#cd/mydata/data

#cp mysql-bin.000011/root/#备份当前正使用的二进制日志文件

#rm-RF *

9.7. Using the previous backup, simulation to implement the RESTORE database

#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 #还原当天数据库崩溃时的数据

This article is from the "Xavier Willow" blog, please be sure to keep this source http://willow.blog.51cto.com/6574604/1812598

MySQL log management and MySQL backup restore under Linux