MySQL log management, backup and recovery of database


Study preparation: first create a table (I use the previous table here)

mysql -u root -p

create database school;
use school;
create table class(
id int(10) not null,
name varchar(20) not null,
sex char(2) not null,
cardid varchar(20) not null,
phone varchar(11),
address varchar(50));

desc class;

insert into class values ('1','zhangsan','male','123456','111111','Suzhou');
insert into class values ('2','lisi','female','123123','222222','Suzhou');
insert into class values ('3','wangchao','male','123412','333333','Yangzhou');
insert into class values ('4','zhanglong','male','112233','444444','Nanjing');
insert into class values ('5','zhaohu','male','111222','555555','Suzhou');
select * from class;


1, Log management of MySQL

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

(1) Log classification of MySQL

  • Error log
  • General query log
  • Binary log
  • Slow query log

(2) MySQL log on

  • By permanently modifying the MySQL configuration file
vim /etc/my.cnf
[mysqld]
##The error log is used to record the error messages that occur when MySQL starts, stops or runs. It is enabled by default
#Specify the location and file name of the error log
log-error=/usr/local/mysql/data/mysql_error.log

##The general query log 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 is used to record all statements that have updated data or have potentially updated data. It records the changes of data 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 is 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.service 

(3) Enter the database to check whether the corresponding log is enabled

mysql -u root -p

1. Check whether the general query log is enabled

show variables like 'general%';

2. Check whether binary log is enabled

show variables like 'log_bin%';

3. View slow query log related functions

(1) Check whether the slow query log function is enabled

show variables like '%slow%';

(2) View slow query time settings

show variables like 'long_query_time';

(3) Set the method of starting slow query in the database-

  • Our external configuration file has been opened, so we won't demonstrate it here
set global slow_query_log=ON;

Supplement:

2, MySQL full 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

(2) Causes of data loss

  • Program error
  • Human operation error
  • Arithmetic error
  • Disk failure
  • Disasters (e.g. fire, earthquake) and theft

(3) Classification of database backup

1. Classification from the perspective of physics and logic

(1) Physical backup

  • Backup of physical files (such as data files, log files, etc.) of database operating system
  • Methods of physical backup
    • 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 carried out in the state of database locking table (not writable but readable)

(2) Logical backup

  • Backup of database logical components (such as tables and other database objects)

2. Classification from the perspective of database backup strategy

(1) Full backup

  • Complete backup of database every time

(2) Differential backup

  • Backs 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

(4) 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

2. Special backup tools mydump or mysqlhotcopy

  • mysqldump is a common logical backup tool
  • mysqlhotcopy only has backup MyISAM and ARCHIVE tables

3. Enable binary logs for incremental backups

  • For incremental backup, you need to refresh the binary log

4. Third party tool backup

  • Free MySQL hot backup software Percona XtraBackup

(5) MySQL full backup

1. Concept

  • It 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

2. Advantages and disadvantages of full backup

  • advantage:
    • Backup and recovery operations are simple and convenient
  • Disadvantages:
    • There is a lot of duplicate data
    • Takes up a lot of backup space
    • Long backup and recovery time

3. Database full backup classification

(1) Physical cold backup and recovery

  • Close MySQL database
  • Use the tar command to package the database folder directly
  • Simply replace the existing MySQL directory

(2) mysqldump backup and recovery

  • MySQL has its own backup tool, which can facilitate the backup of MySQL
  • You can export the specified libraries and tables as SQL scripts
  • Use the command mysql to import the backed up data

3, Basic commands for database cold backup and recovery and full backup and recovery

(1) Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz					#xz is a compression tool
#Compressed backup
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#Decompression recovery
tar Jxvf /opt/mysql_all_2021-02-05.tar.xz -C /usr/local/mysql/data

systemctl start mysqld

(2) mysqldump backup and recovery

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

#The exported backup file is the database script file
mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql
 Example:
mysqldump -u root -p --databases school > /opt/school.sql
mysqldump -u root -p --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
 Example:
mysqldump -u root -p --all-databases > /opt/all.sql

3. Fully backs up some tables in the specified library

mysqldump -u root -p[password] [-d] 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
 Example:
mysqldump -u root -p school class > /opt/school_class.sql

4. View backup files

cat /opt/Backed up files |grep -v "^--" | grep -v "^/" | grep -v "^$"

Example:
cat /opt/school_class.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

(3) MySQL full recovery

1. Restore database

#"- 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 -p -e 'drop database school;'
mysql -u root -p -e 'show databases;'

mysql -u root -p < /opt/school.sql
mysql -u root -p -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 -u root -p -e 'drop table school.class;'
mysql -u root -p -e 'show tables from school;'

mysql -u root -p school < /opt/school_class.sql
mysql -u root -p -e 'show tables from school;'

4, Method of MySQL incremental backup and recovery

(1) MySQL incremental backup

1. Enable binary log function

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


#There are three different formats: SQL based (mixed) and STATEMENT based (temporary), and the default is based on the binary format of log

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

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

  • Such scheduled tasks can be executed in combination with crontab -e scheduled tasks
#Perform backup manually
mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql
mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql

#Use crontab -e to execute planned tasks; Backup the table class and all libraries at 2 a.m. every week
0 2 * * 1 mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql
0 2 * * 1 mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql

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

mysqladmin -u root -p flush-logs

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

mysql -u root -p
use school;
insert into class values ('6','zzz','male','897656','666666','Nanjing');
insert into class values ('7','aaa','female','098765','777777','Suzhou');

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

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

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

(2) MySQL incremental recovery

1. General recovery

(1) Simulate recovery steps for lost changed data

mysql -u root -p
use school;
delete from class where id=6;
delete from class where id=7;
select * from class;
quit

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
mysql -u root -p -e "select * from school.class;"

(2) Simulate recovery steps for all lost data

mysql -u root -p
use school;
drop table class;
show tables;
quit

mysql -uroot -p school < /opt/school_class_2021-02-06.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p
mysql -u root -p -e "select * from school.class;"

2. Breakpoint recovery

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

#Contents of some binary files
......
BEGIN
/*!*/;
##-------------Explanation: at xxx indicates the location point------------------------------------------------
# at 302
##--------------Explanation: the beginning 210206 15:45:53 indicates the time. Others are not used now-----------------------------------
#210206 15:45:53 server id 1  end_log_pos 449 CRC32 0xe972def7 	Query	thread_id=6	exec_time=0	error_code=0
##--------------Explanation: here is the executed operation statement---------------------
use `school`/*!*/;        <-------------use school;Use database
SET TIMESTAMP=1612597553/*!*/; <------------Establish timestamp
insert into class values ('6','zzz','male','897656','666666','Nanjing') <-------Insert data into a table
/*!*/;
##---------------------------------------------------------------
# at 449
#210206 15:45:53 server id 1  end_log_pos 480 CRC32 0x5efde826 	Xid = 446
COMMIT/*!*/;
# at 480
#210206 15:45:54 server id 1  end_log_pos 545 CRC32 0x11768895 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 545
#210206 15:45:54 server id 1  end_log_pos 628 CRC32 0x778ea5fa 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
##-------------------------------Insert second data--------------------------
BEGIN
/*!*/;
# at 628
#210206 15:45:54 server id 1  end_log_pos 775 CRC32 0x66e3bb53 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
insert into class values ('7','aaa','female','098765','777777','Suzhou')
/*!*/;
# at 775
#210206 15:45:54 server id 1  end_log_pos 806 CRC32 0x7b972395 	Xid = 447
COMMIT/*!*/;
# at 806
#210206 15:48:52 server id 1  end_log_pos 853 CRC32 0x0d77c456 	Rotate to mysql-bin.000003  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......

(1) Location based recovery

  • Only recover the data before the location point is "628", that is, do not recover the data with "id=7"
#Analog data loss
mysql -uroot -p123456 school < /opt/school_class_2021-02-06.sql
mysql -uroot -p123456 -e "select * from school.class;"
#Stop recovering data at location 628
mysqlbinlog --no-defaults --stop-position='628' /opt/mysql-bin.000002 | mysql -uroot -p123456
#View the data of class table
mysql -uroot -p123456 -e "select * from school.class;"

  • Only recover the data of "id=7" and skip the data of "id=6"
#Analog data loss
mysql -uroot -p123456 school < /opt/school_class_2021-02-06.sql
mysql -uroot -p123456 -e "select * from school.class;"
#Recover data from location point 628
mysqlbinlog --no-defaults --start-position='628' /opt/mysql-bin.000002 | mysql -uroot -p123456
#View the data of class table
mysql -uroot -p123456 -e "select * from school.class;"

(2) Point in time based recovery

  • Only the data before 210206 15:45:54 is recovered, that is, the data with "id=7" is not recovered
#Analog data loss
mysql -uroot -p123456 school < /opt/school_class_2021-02-06.sql
mysql -uroot -p123456 -e "select * from school.class;"
#By 2021-02-06 15:45:54, the data will be recovered
mysqlbinlog --no-defaults --stop-datetime='2021-02-06 15:45:54' /opt/mysql-bin.000002 | mysql -uroot -p123456
#View the data of class table
mysql -uroot -p123456 -e "select * from school.class;"

  • Only recover the data of "id=7" and skip the data recovery of "id=6"
#Analog data loss
mysql -uroot -p123456 school < /opt/school_class_2021-02-06.sql
mysql -uroot -p123456 -e "select * from school.class;"
#Recover data from 15:45:54 on February 6, 2021
mysqlbinlog --no-defaults--start-datetime='2021-02-06 15:45:54' /opt/mysql-bin.000002 | mysql -uroot -p123456
#View the data of class table
mysql -uroot -p123456 -e "select * from school.class;"
  • Error demonstration: there is a small problem here. The data time of id6 and 7 is too close. They are recovered during recovery, so I won't do it again. It's no big problem to follow the above command. Be careful with the same problems as me when pasting and copying.

Summary: breakpoint recovery
If you restore all the data before an SQL statement, stop at the location node or time point of the statement
If you restore an SQ statement and all subsequent data, start from the location node or time point of the statement

Tags: Database MySQL

Posted by omniuni on Mon, 25 Apr 2022 00:29:16 +0300