1. Data backup overview
1.1 Classification of backups
Full backup: The entire database is backed up completely
Incremental backup: Based on the full backup, backup of subsequent newly added content
Cold backup: shut down the backup, stop the mysql service, and then perform the backup
Hot backup: Start backup without shutting down the mysql service and then perform backup
Physical backup: Back up the physical files (data files, log files) of the database system
Logical backup: only backs up the logical components of the database (table structure), and backs up and saves the library, table structure, and table data in the form of SQL statements (all files are deleted directly in the database system, and the logical backup cannot be restored)
1.2 Reasons why data needs to be backed up
1. In a production environment, data security is crucial, and any loss of data will have very serious consequences.
2. Why is the data lost?
Program operation, calculation errors, disk failure, unpredictable time, human operation
2. Data backup operation
2.1 Physical cold backup
Physical backup: Generally, full backup is used to perform a complete package backup of the entire database
Advantages: simple operation
Disadvantages: takes up too much space, takes a long time to backup and restore, needs to pause the database service to prevent new data from being overwritten, and may also cause recovery failure.
Create two new libraries pup and pup1: create database pup; create database pup1; pup and pup1 create the same table info1 and insert the same data: create table if not exists info1 ( id int(4) primary key, namechar(5), hobby char(5) );
[root@mysql1 ~]# systemctl stop mysqld [root@mysql1 ~]# cd /usr/local/mysql/ [root@mysql1 mysql]# yum -y install xz [root@mysql1 mysql]# tar Jcvf /opt/mysql_all_$(date + %F).tar.xz /usr/local/mysql/data [root@mysql1 mysql]# cd /opt [root@mysql1 opt]# tar Jxvf mysql_all_2023-11-06.tar.xz [root@mysql1 opt]# cd /usr/local/mysql/ [root@mysql1 mysql]# rm -rf data [root@mysql1 mysql]# cd /opt/usr/local/mysql/ [root@mysql1 mysql]# cp -a data/ /usr/local/mysql/ [root@mysql1 mysql]# systemctl restart mysqld.service
2.2 Data migration between hosts
mysql2 virtual machine: [root@mysql2 mysql]# cd /usr/local/mysql/ [root@mysql2 mysql]# rm -rf data/ [root@mysql2 mysql]# scp -r [email protected]:/opt/usr/local/mysql/data /opt [root@mysql2 mysql]# cd /opt [root@mysql2 opt]# cp -a data/ /usr/local/mysql/ [root@mysql2 opt]# systemctl restart mysqld.service [root@mysql2 opt]# cd /usr/local/mysql/data/ [root@mysql2 data]# chown -R mysql.mysql pup pup1
2.3 Logical hot backup
mysqldump: Mysql’s own backup file command
Features: Convenient and simple, but can only be restored based on logical table structure and table data
If you use logical recovery after physical deletion, an error will be reported. It can also be used as data migration.
Occupies a large space, compared to physical backup, it occupies much less space.
Mysql comes with a backup tool: mysqldump Back up a library: mysqldump -u root -p --databases library name > /opt/backup name.sql (customize the name and end with .sql) Back up multiple libraries: mysqldump -u root -p --databases library name 1 library name 2 > /opt/backup name.sql Back up all databases: mysqldump -u root -p --all-databases > /opt/backupname.sql (Only in a logical sense)
Backup and restore a library
[root@mysql1 ~]# systemctl restart mysqld.service [root@mysql1 ~]# netstat -antp | grep 3306 [root@mysql1 ~]# mysqldump -u root -p --databases pup > /opt/pup.sql [root@mysql1 ~]# mysql -u root -p -e 'drop database pup;' #-e: Specify the connection library and exit immediately after executing the command. #Only logical deletion is possible. Physical deletion of library files in the /usr/local/mysql/data directory cannot be restored. [root@mysql1 ~]# mysql -u root -p < /opt/pup.sql
Backup and restore multiple libraries
[root@mysql1 ~]# systemctl restart mysqld.service [root@mysql1 ~]# netstat -antp | grep 3306 [root@mysql1 ~]# mysqldump -u root -p --databases pup pup1 > /opt/pup + pup1.sql [root@mysql1 ~]# mysql -u root -p -e 'drop database pup;' [root@mysql1 ~]# mysql -u root -p -e 'drop database pup1;' [root@mysql1 ~]# mysql -u root -p < /opt/pup + pup1.sql
Backup and restore the specified table in the specified library
Backup table info1: [root@mysql1 data]# mysqldump -u root -p pup info1 > /opt/pup_info1.sql [root@mysql1 data]# mysql -u root -p -e 'drop table pup.info1;'
Restore: [root@mysql1 data]# mysql -u root -p pup < /opt/pup_info1.sql
When restoring, different libraries can also read the backup content: [root@mysql1 data]# mysql -u root -p pup1 < /opt/pup_info1.sql
Backup and restore multiple tables in the specified database
New table info2 in pup library
Back up tables info1 and info2 in a file: [root@mysql1 data]# mysqldump -u root -p pup info1 info2 > /opt/pup_info1-2.sql [root@mysql1 data]# mysql -u root -p -e 'drop table pup.info1;' [root@mysql1 data]# mysql -u root -p -e 'drop table pup.info2;'
Restore: [root@mysql1 opt]# mysql -u root -p pup < /opt/pup_info1-2.sql
Data migration between hosts
[root@mysql1 opt]# mysqldump -u root -p --all-databases > /opt/all_databases.sql Host 2 creates libraries pup and pup2. pup has the same name as the library in host 1 for comparison
Host 2 uses all library backup information of host 1 to restore: [root@mysql2 ~]# scp [email protected]:/opt/all_databases.sql /opt [root@mysql2 ~]# mysql -u root -p < /opt/all_databases.sql
Results:
Conclusion: When data is migrated between hosts, the data of libraries with the same name will not be overwritten, and the data of libraries that do not have the same name will be migrated in
2.4 Incremental backup
mysqldump also supports incremental backup
Advantages: no duplicate data, small backup volume, short time
Disadvantages: locking the table during backup and recovery will inevitably affect the business. If it exceeds 10G, it will take a long time, resulting in service unavailability.
There are three Mysql binary logging formats:
- 1. STATEMENT: Based on sql statements, record modified sql statements. Under high concurrency conditions, the order of recording sql statements may be wrong, which may lead to loss and errors when recovering data. It is more efficient.
- 2. ROW: based on rows, accurately records the data of each row, with high accuracy, but low recovery efficiency.
- 3. MIXED: STATEMENT can be used under normal circumstances either based on sql statements or rows. Once high concurrency occurs, it will intelligently and automatically switch to ROW mode.
Practice:
The binary log provided by Mysql indirectly implements incremental backup How can I get this binary? [root@mysql1 opt]# vim /etc/my.cnf --insert-- log-bin=mysql-bin binlog_format=MIXED [root@mysql1 opt]# systemctl restart mysqld.service
View the backup binary log file
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
Refresh and create new breakpoints
mysqladmin -u root -p flush-logs
Recover data
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
Recover data based on location points
Restore based on location points, three ways: ?Start from a certain point and restore to the end: mysqlbinlog --no-defaults --start-position='position point' file name | mysql -u root -p Recover from the beginning to a certain position: mysqlbinlog --no-defaults --stop-position='position point' file name | mysql -u root -p Restore from the specified point to the specified end point: mysqlbinlog --no-defaults --start-position='position point' --stop-position='position point' file name | mysql -u root -p
Restore from starting point
Starting before inserting data 4, restore 4, 5, and 6
mysqlbinlog --no-defaults --start-position='296' mysql-bin.000001 | mysql -u root -p
Resume from beginning to pointing end point
Delete 4, 5, 6, end with data 6 inserted, and restore 4, 5, 6
mysqlbinlog --no-defaults --stop-position='1096' mysql-bin.000001 | mysql -u root -p
Restore from the specified starting point to the specified end point
Delete 4, 5, 6, starting from before inserting data 5, ending with inserting data 6, and restore 5 and 6.
mysqlbinlog --no-defaults --start-position='610' --stop-position='1096' mysql-bin.000001 | mysql -u root -p
Recover data based on time points
Resume from start time
mysqlbinlog --no-defaults --start-datetime='time point' file | mysql -u root -p
Restore from the beginning to the specified end time
mysqlbinlog --no-defaults --stop-datetime='time point' file | mysql -u root -p
Resume from the specified start time to the specified end time
mysqlbinlog --no-defaults --start-datetime='time point' --stop-datetime='time point' file name | mysql -u root -p
3. Mysql log management
3.1 Error log
is used to record error messages that occur when MySQL starts, stops or runs. It is enabled by default. vim /etc/my.cnf --Add to-- log-error=/usr/local/mysql/data/mysql_error.log
3.2 General Log
is used to record all connections and statements of MySQL. It is closed by default. vim /etc/my.cnf --Add to-- general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log Check whether the general log is turned on: show variables like 'general%'
3.3 Binary log (binlog)
is used to record all error messages sent when MySQL starts, stops or runs. It is turned off by default. vim /etc/my.cnf --Add to-- log-bin=mysql-bin Check whether the binary log is enabled: show variables like 'log_bin%';
3.4 Slow query log
is used to record all statements whose execution time exceeds long_query_time seconds. You can find which query statements take a long time to execute for optimization. It is turned off by default. The slow query log can also be used to query which search fields have timed out, and you can choose whether to increase the index to speed up the query. vim /etc/my.cnf --Add to-- slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5 #Modify slow query time Check whether slow query is enabled: show variables like '%slow%'; View slow query times: show variables like 'long_query_time';