Mysql log management

Mysql log management

1, MySQL log management

The default log storage location of MySQL is / usr/local/mysql/data

There are two ways to open logs: through configuration files or through commands
The log opened through command modification is temporary and will be closed after the service is shut down or restarted

  1. MySQL common log types and startup
vim /etc/my.cnf
  • Error log

It is used to record error messages that occur when MySQL starts, stops or runs. It is enabled by default

Specify the save location and file name of the log
  • General query log

It is used to record all connections and statements of MySQL. It is closed by default

  • Binary log

It is used to record all error messages sent when MySQL starts, stops or runs. It is off by default

  • Slow query log

Used to record all execution times exceeding long_ query_ For the statement of time seconds, you can find which query statements take a long time to execute for optimization. It is closed by default


systemctl restart mysqld    
  1. View log status
  • Check whether the general query log is enabled
mysql -u root -p
show variables like 'general%';		
  • Check whether binary log is enabled
show variables like 'log_bin%';
  • Check whether the slow query function is enabled
show variables like '%slow%';	

View slow query time settings

show variables like 'long_query_time';

Set the method of starting slow query in the database

set global slow_query_log=ON;
This method fails to restart the service

2, Mysql backup and recovery

  1. Importance of data backup

The primary purpose of backup is disaster recovery
In the production environment, the security of data is very important
Any loss of data can have serious consequences

Causes of data loss
1. Program error
2. Human operation error
3. Arithmetic error
4. Disk failure
5. Disaster (fire, earthquake, theft, etc.)

  1. Classification of database backup

1. From a physical and logical point of view, backup can be divided into

  • Physical backup: backup of physical files (such as data files, log files, etc.) of database operating system

Physical backup method:
1. Cold backup (offline backup): it is performed when the database is closed
2. Hot backup (online backup): the database is running and depends on the log file of the database
3. Warm backup: the backup operation is carried out in the state of database locking table (not writable but readable)

2. Backup of logical database components (such as logical database)

3. From the perspective of database backup strategy, backup can be divided into:

  • Full backup: make a full backup of the database every time

Full backup is the backup of the whole database, database structure and file structure.
Save the database at the completion time of backup.
It is the basis of differential backup and incremental backup.
Equivalent to the cornerstone.

  • Differential backup: backs up files that have been modified since the last full backup
  • Incremental backup: only files modified after the last full backup or incremental backup will be backed up
  1. Common backup methods

1. Physical cold standby

The database is closed during backup, and the database files are packaged directly
Backup is fast and recovery is the simplest
Close MySQL database
Use the tar command to package the database folder directly
Simply replace the existing MySQL directory

2. Special backup tools mydump or mysqlhotcopy

mysqldump is a common logical backup tool
MySQL has its own backup tool, which can backup mysql
You can export the specified libraries and tables as SQL scripts
Use the command mysql to import the backed up data

mysqlhotcopy only has backup myisam and archive tables

3. Start binary log for incremental backup

For incremental backup, you need to refresh the binary log

4. Third party tool backup

Free MySQL hot backup software Percona XtraBackup

  1. MySQL full backup and recovery

Experimental environment

hostoperating systemIP addressRequired tools / software / installation package
mysql -u root -p
create database SCHOOL;
create table if not exists CLASS1 (
id int(4) not null auto_increment,
name varchar(10) not null,
sex char(10) not null,
hobby varchar(50),
primary key (id));

insert into CLASS1 values(1,'user1','male','running');
insert into CLASS1 values(2,'user2','female','singing');

set password = password('123123');

MySQL full backup

The database of InnoDB storage engine is stored in three files on disk: dB Opt (table attribute file), table name Frm (table structure file), table name IBD (table data file).

1. Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz

Compressed backup

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

Decompression recovery

tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data

systemctl restart mysql

2. mysqldump backup and recovery

(1) Full backup of one or more complete libraries (including all tables therein)

mysqldump -u root -p [password] - databases database name 1 [database name 2]... > / backup path / backup file name sql # exports database script files


mysqldump -uroot -p123123 --databases SCHOOL > /opt/SCHOOL.sql
mysqldump -uroot -p123123 --databases mysql SCHOOL > /opt/mysql-SCHOOL.sql

(2) Fully back up all libraries in the MySQL server

mysqldump -u root -p [password] - All Databases > / backup path / backup file name sql


mysqldump -u root -p123123 --all-databases > /opt/all.sql

(3) Fully backs up some tables in the specified library

mysqldump -u root -p [password] database name [table name 1] [table name 2]... > / backup path / backup file name sql


mysqldump -uroot -p123123 SCHOOL CLASS1 > 	/opt/SCHOOL_CLASS1.sql
#Use the "- d" option to explain that only the table structure of the database is saved
#If the "- d" option is not used, the table data will also be backed up

(4) View backup files

grep -v "^--" /opt/SCHOOL_CLASS1.sql | grep -v "^/" | grep -v "^$"

Full backup recovery

1. Restore database

mysql -uroot -p123123 -e 'drop database SCHOOL;'

#"- e" option is used to specify the command to be executed after connecting to MySQL. After the command is executed, it will exit automatically

mysql -uroot -p123123 -e 'SHOW DATABASES;'

mysql -uroot -p123123 < /opt/SCHOOL.sql
mysql -uroot -p123123 -e 'SHOW DATABASES;'

2. Restore data table

When the backup file contains only the backup of the table, but not the statement of the created library, the library name must be specified when performing the import operation, and the target library must exist.

mysql -uroot -p123123 -e 'drop table SCHOOL.CLASS1;'
mysql -uroot -p123123 -e 'show tables from SCHOOL;'

mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1.sql
mysql -uroot -p123123 -e 'show tables from SCHOOL;'
  1. MySQL incremental backup and recovery

MySQL incremental backup

1. Enable binary log function

vim /etc/my.cnf
binlog_format = MIXED				
server-id = 1

#There are three different recording formats for binary log: state (based on SQL STATEMENT), row (based on row) and mixed (mixed mode). The default format is state

systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

2. The database or table can be fully backed up every week

mysqldump -uroot -p123123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql
mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql

3. Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 2)

mysqladmin -uroot -p123123 flush-logs

4. Insert new data to simulate the addition or change of data

mysql -uroot -p123123
insert into CLASS1 values(3,'user3','male','game');
insert into CLASS1 values(4,'user4','female','reading');

5. Generate a new binary log file again (for example, MySQL bin. 00000 3)

mysqladmin -uroot -p123123 flush-logs

6. View the contents of binary log files

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

#-- Base64 output = decode rows: use the 64 bit encoding mechanism to decode and read by line
#-v: Show details

MySQL incremental backup and recovery

1. General recovery

(1) Simulate recovery steps for lost changed data

mysql -uroot -p123123
delete from CLASS1 where id=3;
delete from CLASS1 where id=4;
select * from CLASS1;

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

2. Simulate the recovery steps of all lost data (basically the same, pay attention to the log date)

mysql -uroot -p123123
drop table CLASS1;

mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1_2021-01-29.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
  1. checkpoint recovery
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#As just now, use the 64 bit encoding mechanism to decode and read the details of the binary file 00000 2 by line

(1) Location based recovery

#Only recover the data before the operation ID is "609", that is, do not recover the data of "user4"

mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p


mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;"
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

#Only recover the data of "user4". Skip the data recovery of "user3". After 609, there is only the fourth record


mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
mysqlbinlog --no-defaults --start-position='609' /opt/mysql-bin.000002 | mysql -uroot -p123123
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

(2) Point in time based recovery

#Only recover the data before 0:39:13, that is, do not recover the data of "user4"

Example: clear the table first CLASS1,Convenient experiment

mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;"
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

mysqlbinlog --no-defaults --stop-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p123123
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

#Only recover the data of "user4" and skip the data recovery of "user3" (basically consistent)

mysqlbinlog --no-defaults --start-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p

Keywords: Database MySQL server

Added by MikeUK on Mon, 21 Feb 2022 10:16:57 +0200