MySQL log management, backup and recovery

Posted by kitaeshi on Sun, 23 Jan 2022 09:12:24 +0100

MySQL common log types and startup

The default location of MySQL logs is / usr/local/mysql/data

vim /etc/my.cnf
[mysqld]
......

Error log

It is used to record error messages that occur when MySQL is started, stopped or running. It is enabled by default
Specify the save location and file name of the log

log-error=/usr/local/mysql/data/mysql_error.log

General query log

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

general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

Binary log

It is used to record all updated or potentially updated data statements, record data changes, and can be used for data recovery. It is enabled by default

log-bin=mysql-bin            #You can also log_bin=mysql-bin

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

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5                #Set the statements executed for more than 5 seconds to be recorded. The default is 10 seconds


systemctl restart mysqld

mysql -u root -p

show variables like ‘general%’; # Check whether the general query log is enabled

show variables like ‘log_bin%’; # Check whether binary log is enabled

show variables like ‘%slow%’; # Check whether the slow query day function is enabled
show variables like ‘long_query_time’; # View slow query time settings

set global slow_query_log=ON; # Set the method of starting slow query in the database




MySQL full backup and recovery

The purpose of data backup is mainly for disaster recovery. Data security is very important. Any data loss may bring huge losses to the company and customers

Causes of data loss:

  • Program error
  • Human operation error
  • Arithmetic error
  • Disk failure
  • Disaster (fire, earthquake, theft, etc.)

Classification of database backup

From the perspective of physics and logic

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

Physical backup method:
Cold backup (offline backup): it is performed when the database is closed
Hot backup (online backup): the database is running and depends on the log file of the database
Warm backup: the backup operation is performed when the database is locked in a table (not writable but readable)

  • Logical backup: backup of database logical components (such as tables and other database objects)

From the perspective of database backup strategy

  • Full backup: perform a full backup of the database each time

A full backup is a backup of the entire 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 a 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

Common backup methods

  • 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

  • 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 mysql command to import the backed up data

mysqlhotcopy only has backup myisam and archive tables

  • Start binary log for incremental backup

For incremental backup, you need to refresh the binary log

  • Third party tool backup

Free MySQL hot backup software Percona XtraBackup

Experimental preparation

use SCHOOL;
create table if not exists info1 (
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 info1 values(1,'user1','male','running');
insert into info1 values(2,'user2','female','singing');

MySQL full backup

The database of InnoDB storage engine is stored into three files on disk; db. Opt (table property file), table name Frm (table structure file), table name IBD (table data file).

Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz
#Compressed backup
tar Jcvf /opt/myscl_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

mysqldump backup and recovery

1. Fully back up one or more complete libraries (including all tables therein)

mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql  #The exported is the database script file



2. Fully back up all databases in the MySQL server

mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql


3. Fully back up some tables in the specified library

mysqldump -u root -p[password] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.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/info1.sql | grep -v "^/" | grep -v "^$"

MysQL full backup recovery

Restore database

mysql -u root -p123456 -e 'drop database SCHOOL;'
#The "- 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 -u root -p123456 -e 'SHOW DATABASES;'

mysql -u root -p123456 < /opt/SCHOOL.sql
mysql -u root -p123456 -e 'SHOW DATABASES;'


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.

mysqldump -u root -p123456 SCHOOL info1 > /opt/info1.sql

mysql -u root -p123456 -e 'drop table SCHOOL.info1;'
mysql -u root -p123456 -e 'show tables from SCHOOL;'

mysql -uroot -p123456 SCHOOL < /opt/info1.sql
mysql -u root -p123456 -e 'show tables from SCHOOL;'


MySQL incremental backup and recovery

MysQL incremental backup

① Enable binary log function

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED          #Optional. Specifies that the binary log record format is MIXED 
server-id = 1

There are three different recording formats for binary log: constant (based on SQI STATEMENT), ROV (based on row) and Mtxed (mixed mode). The default format is constant

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


② The database or table can be fully backed up every week

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

③ Incremental backup can be performed every day to generate new binary log files (such as MySQL bin. 00000 2)

mysqladmin -uroot -p123456 flush-logs

④ Insert new data to simulate the addition or change of data

use SCHOOL;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

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

mysqladmin -uroot -p123456 flush-logs
#The database operation in step 4 above will be saved to MySQL bin In the 00000 3 file, if the database data changes again, it is saved in MySQL bin 00000 4 document

⑥ View the contents of the binary log file

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

#– Base64 output = decode rows: decode and read by line using 64 bit encoding mechanism
#-v: Show details


MysQL incremental recovery

General recovery

① Simulate recovery steps for lost changed data

use SCHOOL;
delete from info1 where id=3;
delete from info1 where id=4;

mysqlbinlog --no-defaults /opt/mysql-bin.000001 | mysql -u root -p123456


Simulate recovery steps for all lost data (note log date)

use SCHOOL;
drop table info1;

mysql -uroot -p123456 SCHOOK </opt/SCHOOL_info1_2020-2-13.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123456

checkpoint recovery 

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

Location based recovery

#Only recover the data before operation ID "608", that is, do not recover the data of "user4"
mysqlbinlog --no-defaults --stop-position='608' /opt/mysql-bin.000001 | mysql -uroot -p123456

#Only recover the data of "user4" and skip the data recovery of "user3"
mysqlbinlog --no-defaults --start-position='608' /opt/mysql-bin.000001 | mysql -uroot -p123456

Point in time based recovery
 

#Only recover the data before 15:59:24, that is, do not recover the data of "user4"
mysqlbinlog --no-defaults --stop-datetime='2021-02-13 15.59.24'/opt/mysql-bin.00001 | mysql -uroot -p123456


#Only recover the data of "user4" and skip the data recovery of "user3"
mysqlbinlog --no-defaults--start-datetime='2021-02-13 15.59.24'/opt/mysql-bin.000001 | mysql -uroot -p123456


    
 
    
If you restore a SQL All data before the statement stop At the location node or point in time of this statement
 If you restore a SQL All data after the statement start At the location node or point in time of this statement

Hot Topics

©2025 Programmer Think