MySQL log management, backup and recovery

_ Rui_ ao 2022-02-13 07:07:06 阅读数:620

mysql log management backup recovery

MySQL Log management 、 Backup and recovery

preparation

install MySQL database

MySQL Log management

MySQL The default log save location is /usr/local/mysql/data

There are two ways to open logs : Through the configuration file or through the command
The log opened by command modification is temporary , When the service is shut down or restarted, it will shut down

One 、MySQL Common log types and opening

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

1、 Error log
Used to record when MySQL start-up 、 Error message when stopping or running , The default is on

 Specify the save location and file name of the log
log-error=/usr/local/mysql/data/mysql_error.log

2、 General query log
Used to record MySQL All connections and statements , The default is off

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

3、 Binary log (binlog)
Used to record all when MySQL start-up 、 Error messages sent when stopping or running , The default is off

log-bin=mysql-bin
or
log_bin=mysql-bin

4、 Slow query log
Used to record all execution times exceeding long_query_time Second statement , You can find which query statements take a long time to execute , In order to optimize , The default is off

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
systemctl restart mysqld

Two 、 Check log status

1、 Check whether the general query log is enabled

mysql -u root -p
show variables like 'general%';

2、 Check whether the binary log is enabled

show variables like 'log_bin%';

3、 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 restarts the service and fails

MySQL Backup and recovery

One 、 The importance of data backup

The main purpose of backup is disaster recovery
In the production environment , Data security is crucial
Any loss of data can have serious consequences


Cause of data loss
1、 Program error
2、 Human error
3、 Arithmetic error
4、 Disk failure
5、 disaster ( fire 、 The earthquake 、 Theft, etc )


Two 、 Classification of database backup

1、 From the point of view of physics and logic , Backup can be divided into

1、 The physical backup : Physical files for database operating system ( Such as data files 、 Log files, etc ) Backup of

Physical backup method :
1. Cold backup ( Offline backup ): It was done when the database was shut down
2. Hot backup ( Online backup ): The database is running , Database dependent log files
3. Warm backup : Database lock table ( Not writable but readable ) Backup operation in the state of

2、 Logical backup : For database logic components ( Such as : Database objects such as tables ) Backup of


2、 From the perspective of database backup strategy , Backup can be divided into

1、 Full backup : Every time you make a full backup of the database

A full backup is a backup of the entire database 、 Backup of database structure and file structure .
What you save is the database at the time of backup completion .
The difference between backup and incremental backup is .
Equivalent to cornerstone .

2、 Differential backup : Back up files that have been modified since the last full backup

3、 Incremental backup : Only files modified after the last full backup or incremental backup will be backed up


3、 ... and 、 Common backup methods

1、 Physical cold standby

The database is closed at the time of backup , Directly package database files
Fast backup , Recovery is also the simplest
close MySQL database
Use tar Command to package the database folder directly
Replace existing directly MySQL directory

2、 Special backup tool mydump perhaps mysqlhotcopy

mysqldump Common logical backup tools
MySQL Backup tools included , It can be realized to MySQL Backup of
You can add the specified library 、 The table is exported as SQL Script
Use command mysql Import backup data

mysqlhotcopy Only have backup myisam and archive surface

3、 Start binary log for incremental backup

Incremental backup , Need to refresh binary log

4、 Third party tools backup

Free of charge MySQL Hot backup software Percona XtraBackup


Four 、MySQL Full backup and recovery

Experimental environment

host operating system IP Address The tools needed / Software / Installation package
MySQL CentOS7 192.168.254.10 mysql-boost-5.7.20.tar.gz
mysql -u root -p
create database SCHOOL;
use 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

InnoDB The database of the storage engine is stored in three files on disk :db.opt( Table properties 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 the tables )

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

example :

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

(2) Full backup MySQL All libraries in the server

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

example :

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

(3) Full backup of some tables in the specified library

mysqldump -u root -p[ password ] Library name [ Table name 1] [ Table name 2] … > / Backup path / Backup filename .sql

example :

mysqldump -uroot -p123123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1.sql
# Use “-d” Options , Note: only the table structure of the database is saved
# Don't use “-d” Options , Note that table data is also backed up

(4) View backup files

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

Full backup recovery

1、 Recover database

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

#“-e” Options , Used to specify the connection MySQL Command executed after , Automatically exit after the command is executed

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 tables , Without 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;'

5、 ... and 、MySQL Incremental backup and recovery

MySQL Incremental backup

1、 Turn on binary log function

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
server-id = 1

# Binary log (binlog) Yes 3 Two different record formats :STATEMENT( be based on SQL sentence )、ROW( Based on line )、MIXED( Mixed mode ), The default format is STATEMENT

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 operations can be performed every day , Generate a new binary log file

( for example mysql-bin.000002)

mysqladmin -uroot -p123123 flush-logs

4、 Insert new data , To simulate the addition or change of data

mysql -uroot -p123123
use SCHOOL;
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.000003)

mysqladmin -uroot -p123123 flush-logs

# Previous steps 4 The database operation will be saved to mysql-bin.000002 In file , After that, if the database data changes again, it is saved in mysql-bin.000003 In file

6、 View the contents of the binary log file

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 64 Bit encoding mechanism to decode and read by line
#-v: Show details

MySQL Incremental backup recovery

1、 General recovery

(1) Simulate recovery steps for lost changed data

mysql -uroot -p123123
use SCHOOL;
delete from CLASS1 where id=3;
delete from CLASS1 where id=4;
select * from CLASS1;
quit
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

2、 Simulate recovery steps for all lost data ( Is essentially the same , Note the log date )

mysql -uroot -p123123
use SCHOOL;
drop table CLASS1;
quit
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;"

3. Breakpoint recovery

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

Use the same as just 64 Bit encoding mechanism to decode and read binary files by line 000002 Details of

BEGIN
/!/;

at 302

#210129 0:39:12 server id 1 end_log_pos 430 CRC32 0x2c164d0a Query thread_id=10 time=0 error_code=0
use SCHOOL/!/;
SET TIMESTAMP=1611851952/!/;
insert into CLASS1 values(3,‘user3’,‘male’,‘game’)
/!/;

at 430

#210129 0:39:12 server id 1 end_log_pos 461 CRC32 0x225bb461 Xid = 76
COMMIT/!/;

at 461

#210129 0:39:13 server id 1 end_log_pos 526 CRC32 0xe5abe22c Anonymous_GTID last_comd=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 526

#210129 0:39:13 server id 1 end_log_pos 609 CRC32 0x2cfb793b Query thread_id=10 time=0 error_code=0
SET TIMESTAMP=1611851953/!/;
BEGIN
/!/;

at 609

#210129 0:39:13 server id 1 end_log_pos 742 CRC32 0x7ea13a1a Query thread_id=10 time=0 error_code=0
SET TIMESTAMP=1611851953/!/;
insert into CLASS1 values(4,‘user4’,‘female’,‘reading’)
/!/;

at 742

#210129 0:39:13 server id 1 end_log_pos 773 CRC32 0x11b21cd0 Xid = 77
COMMIT/!/;

(1) Based on location recovery
# Restore to operation only ID by “609” Previous data , I.e. no recovery “user4” The data of

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

example :

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;"

# Restore only “user4” The data of , skip “user3” Data recovery ,609 Only after , Only the fourth record

example :

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) Based on point in time recovery
# Only restore to 0:39:13 Previous data , I.e. no recovery “user4” The data of

 example : Clear the watch first CLASS1, Convenient experimental
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;"

# Restore only “user4” The data of , skip “user3” Data recovery ( Almost the same )

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

ysql
example : Clear the watch first CLASS1, Convenient experimental

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;”


\# Restore only “user4” The data of , skip “user3” Data recovery ( Almost the same )
```mysql
mysqlbinlog --no-defaults --start-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p
copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707048175.html