MySQL database backup

• 1, MySQL common logs
• • 1.1 overview
  • 1.2 error log
  • 1.3 binary files
  • 1.4 relay log
  • 1.5 slow query log
  • 1.6 query log status in the database
  • • 1.6.1 check binary log status on
    • 1.6.2 check whether the slow query log function is enabled
    • 1.6.3 query slow time setting
    • 1.6.4 set the method of starting slow query in the database (temporary)
• 2, Backup
• • 2.1 overview
  • 2.2 importance of backup
  • 2.3 backup type
  • 2.4 backup methods
  • • 2.4.1 cold backup
    • 2.4.2 incremental backup by enabling binary logs
    • 2.4.3 backup through third-party tools
  • 2.5 backup command
  • 2.6 incremental backup and recovery
  • • 2.6.1 the binary log function needs to be enabled for incremental backup
    • 2.6.2 incremental backup operations can be carried out every day to generate new binary files
    • 2.6.3 view the newly generated log content
  • 2.7 recovery method
  • • 2.7.1 restore by position
    • 2.7.2 recovery by time
    • • Summary:

preface:
Database backup beware of data loss

1, MySQL common logs

1.1 overview

Log files play an important role in database backup and recovery
Common log files are saved in /usr/local/mysql/data directory by default
It can be found in /etc/my In [mysqld] in the CNF configuration file, you can modify, open, and close the log path

1.2 error log

It is used to record the error information generated when mysql starts, stops or runs
You can update through the following fields

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

1.3 binary files

Binary log is used to record all updated data or statements that have potentially updated data, record changes in data, and can be used for data recovery
Opening mode:

log-bin=mysql-bin perhaps log_bin=mysql-bin

1.4 relay log

Generally, it is only enabled on the slave nodes of mysql master-slave synchronization (replication) and read-write separation clusters.
The primary node generally does not need this log.

1.5 slow query log

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, so as to facilitate optimization
Opening mode:

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log (Specify file path and name)
long_query_time=5 (Set that statements executed for more than 5 seconds will be recorded, and the default is 10 seconds.)

1.6 query log status in the database

1.6.1 check binary log status on

show variables like '%log_bin%';

1.6.2 check whether the slow query log function is enabled

show variables like '%slow%';

1.6.3 query slow time setting

show variables like 'long_query_time';

1.6.4 set the method of starting slow query in the database (temporary)

set global slow_query_log=ON;

see
show variables like 'long_query_time';

Log timeout

2, Backup

2.1 overview

The main purpose of backup is disaster recovery

It can also be used to test applications, rollback data modification, query historical data, audit, etc

In the production environment, the security of data is very important

Any data loss can have serious consequences

2.2 importance of backup

In enterprises, the value of data is crucial, and data ensures the normal operation of enterprise business.
Therefore, the safety and reliability of data is the top priority of operation and maintenance. Any suspension of data may have serious consequences for enterprises.

==Generally, there are several reasons for data loss:==

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

2.3 backup type

From the physical and logical point of view, it can be divided into logical backup and physical backup

From the perspective of database backup strategy, it can be divided into full backup, differential backup and incremental backup

==Full backup: = = complete backup of data every time, that is, the backup of the entire database, database structure and file structure. What is saved is the database at the time of backup completion, which is the basis of differential backup and incremental backup. The backup and recovery operations of full backup are very simple and convenient, but there are a lot of duplication of data, and it will occupy a lot of disk space, and the backup time is also very long.

==Differential backup: = = backup all files that have been modified since the last full backup. The backup time node is from the last full backup, and the amount of backup data will be larger and larger. When restoring data, you only need to restore the last full backup and the latest differential backup.

==Incremental backup: = = only those files modified after the last full backup or incremental backup will be backed up. Taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the amount of data backed up is small, the occupied space is small, and the backup speed is fast. However, during recovery, all increments from the last full backup to the last incremental backup need to be recovered in turn. If the backup data in the middle is damaged, it will lead to data loss.

2.4 backup methods

There are many ways to backup databases, such as directly packaging database files (physical cold backup), special backup tools (mysqldump), binary log incremental backup, third-party tool backup, etc

2.4.1 cold backup

During cold backup, it is necessary to ensure the integrity of the database when the database is closed
Cold backup is characterized by fast speed and the simplest recovery.
Backup is achieved by directly packaging the database folder (/usr/loc.al/mysql/data)

2.4.2 incremental backup by enabling binary logs

Incremental backup is supported, and binary log must be enabled during incremental backup.
Binary log files provide users with replication and restore the information required for database changes after the backup point.
In case of incremental backup (including data modification since the last full backup or incremental backup), the binary log needs to be refreshed

2.4.3 backup through third-party tools

The third-party tool Percona xtraBackup is a free MySQL hot backup software, which supports online hot backup of Innodb and xtraDB, as well as MySQL table backup. However, the backup of MyISAM table should be carried out under the condition of table lock.

2.5 backup command

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). 

Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz
 
#Compressed backup
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#Simulate failure and delete database
drop database HUISUO;
 
#Decompression recovery
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

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   
#What is exported is the database script file
 
example:
 
mysqldump -u root -p --databases liu > /opt/kgc.sql       #Back up a kgc Library
mysqldump -u root -p --databases mysql li > /opt/mysql-kgc.sql    #Backup mysql and kgc Libraries

Back up all libraries

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

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 file name.sql
 
For example:
mysqldump -uroot -p[password] [-d] HUISUO member1 > /opt/member1.sql
#Use the "-d" option to explain that only the table structure of the database is saved
#Do not use "-d" option, indicating that the table data is also backed up

View backup files

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

2.6 incremental backup and recovery

2.6.1 the binary log function needs to be enabled for incremental backup

vim /etc/my.cnf
#Error log
log-error=/usr/local/mysql/data/mysql_error.log	 
#General query log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#Binary log
log-bin=mysql-bin	
#Slow query log
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#You need to restart MySQL after adding the configuration file
systemctl restart mysql

2.6.2 incremental backup operations can be carried out every day to generate new binary files

Complete the full backup first (based on the creation of tables and libraries)

systemctl restart mysqld.service
mysqldump -uroot -p meeting working > /mnt/meeting_working_$(date +%F).sql
mysqldump -uroot -p meeting > /mnt/meeting_$(date +%F).sql
 Generate a new binary file(Incremental backup operations can be performed every day)
mysqladmin -uroot -p flush-logs

2.6.3 view the newly generated log content

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

2.7 recovery method

2.7.1 restore by position

Delete table first
drop table working;
 
Empty table contents
truncate table meeting.working;
 
The recovery end point is before the delete command and after the insert command
mysqlbinlog --no-defaults --stop-position='902' usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p

2.7.2 recovery by time

Empty the table first CLASS1,Convenient experiment
mysql -uroot -p -e "truncate table meeting.working;"
mysql -uroot -p -e "select * from meeting.woring;"
 
mysqlbinlog --no-defaults --stop-datetime='2021-04-15 15:39:23' /opt/mysql-bin.000003 |mysql -uroot -p
mysql -uroot -p -e "select * from meeting.woring;"

Summary:

mysql does not directly provide a tool for incremental backup, which requires binary log files for operation

Incremental backup by separating logs with logs

Incremental recovery needs to be performed one by one according to the time of the log file

Data can be recovered more accurately by using time and location-based methods

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials