Backup
Full backup and full backup
Full backup
Full backup: Back up the entire database completely
Incremental backup
Incremental backup: It is to back up subsequent newly added content based on the full backup.
Backup requirements
1. Data security is crucial in a production environment, and any data loss may have serious consequences.
2. Why data is lost, for example: it may be program operation, calculation error, disk failure, unpredictable events (earthquake, tsunami, etc.), human operation, etc.
Backup method
Cold backup: shutdown backup. Stop the MySQL service and then perform a backup
Hot backup: startup backup. No need to shut down MySQL services to perform backups
Physical backup: Back up the physical files (data files, log files) of the data system
Logical backup: Back up the components (table structure), back up and save the library, table structure, and table data in the form of SQL statements. (Delete all files directly in the database, logical backup cannot be restored)
Physical backup
Physical backup: Full backup, a complete packaged backup of the entire database.
Advantages: Simple operation Disadvantages: The database file takes up a lot of space. It takes up too much space, takes a long time to back up and restore, and requires website database services.
How to perform physical backup
systemctl stop mysqld #Close the MySQL1 server. Because shutting down the service can avoid new data insertion or data overwriting cd usr/local/mysql/ CD data cdkgc Install packages tar Jcvf /opt/mysql_all_$(data + %F).tar.xz /usr/local/mysql/data/ #Package the contents of data ls tar -Jxvf mysql_all_ #unzip files cd usr/local/mysql/ rm -rf data #Delete the library and run away cd /opt/ cd usr/local/mysql cp -a data/ /usr/local/mysql #Reserve permissions to copy systemctl restart mysqld #Restart mysql service to another library cd /usr/local/mysql/ cp -a data/ /opt/ #MySQL2 Make a file backup yourself first scp -r [email protected]:/opt/usr/local/mysql/data /opt/ #Remote copy to MySQL2 cp -a data/ /usr/local/mysql/data #Preserve permissions to copy to MySQL2 systemctl restart mysqld #Restart the service and log in chown -R mysel.mysql kgc kgc1 #Recursively change ownership of two libraries #If you see that it cannot be read, it means there is no permission issue
To another host MySQL2
It is best to shut down the service for packaged backup to prevent new data from entering and being overwritten. It may also cause recovery to fail.
Logical backup based on hot backup
MySQL’s own tools: MySQLdump: used to restore logical database and table structure table data.
Operation on the command line: mysqldump -u root -p --databases library name > /opt/filename.sql #Back up a single library mysqldump -u root -p --databases library 1 library 2 > /opt/filename2.sql #Multiple backups mysqldump -u root -p --all-databases > /opt/filename3.sql #Back up all libraries
Just a logical backup
systemctl restart mysqld netstart -antp | grep 3306 mysqldump -u root -p --databases kgc > /opt/kgc.sql #Back up the kgc library to the /opt, directory cd /usr/local/mysql/data rm -rf kgc #Physical deletion mysql -u root -p < /opt/kgc.sql # An error will be reported directly and the library will come back, but the table structure in the kgc library will disappear. Only tombstones can be used (deletion at the command line) mysql -u root -p123456 -e 'show databases;' #-e: Indicates that after connecting to the specified MySQL, it will automatically exit after executing the command. #Enter MySQL and execute the show databases command once and then exit. mysql -u root -p -e 'drop database kgc;' #Require manual data password mysql -u root -p < /opt/kgc.sql #Enter password manually Back up multiple libraries at once Back up multiple libraries first mysqldump -u root -p --databases kgc kgc1 > /opt/kgc_all.sql #Enter the password manually. Save multiple libraries mysql -u root -p -e 'drop database kgc;' mysql -u root -p -e 'drop database kgc1;' #Enter the password manually. Simulate deletion of library mysqldump -u root -p < /opt/kgc_all.sql #Enter the password manually. Restore multiple libraries at once Back up all libraries at once mysqldump -r root -p --all-databases > /opt/all_database.sql #Backup all
Backup a single library restore
Backup multiple libraries and restore
mysql -u root -p123456 -e 'show databases;' #-e: Indicates that after connecting to the specified MySQL, it will automatically exit after executing the command. ?#Enter MySQL and execute the show databases command once and then exit
How to restore data table
Restore only a single table or multiple tables: Prepare another table to insert data To restore a table: mysqldump -u root -p kgc info1 > opt/kgc_info1.sql msql -u root -p -e 'drop table kgc.info1;' #Specify the library to delete the table mysql -u root -p kgc < /opt/kgc_info1.sql #Specify the library name to restore #Refresh and check To restore multiple tables: mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql msql -u root -p -e 'drop table kgc.info1;' msql -u root -p -e 'drop table kgc.info2;' mysql -u root -p kgc < /opt/kgc_info1-2.sql #Specify the library name to restore #Refresh and check The logical backup files of all MySQL1 databases are restored to MySQL2. scp [email protected]:/opt/all_database.sql /opt/ mysql -u root -p < all_database.sql #Use sql statement to directly convert hot backup. #Will overwrite all data of MySQL2
Restore a single table
To restore multiple tables:
Physical cold backup and hot backup Features: Simple Data volume, large backup space occupied Mysqldump: This is the backup file command that comes with MySQL Features: Convenient and simple. But it can only be restored based on the logical table structure and table data. If you use logical recovery after physical deletion, an error will be reported. Can also be used as a data migration. Will take up a lot of space. Compared with physical backup, relatively speaking, it takes up much less space.
Use logical backup within 100G. It is recommended to use tools above 100G.
Incremental backup
Incremental backup: mysqldump: supports incremental backup. Features: no duplicate data, small backup volume and short time. During the MySQL incremental backup to restore table data, the table will be locked. Can check but cannot write. Disadvantage: locking the table during backup will inevitably affect the business. If it exceeds 10G, it will take a long time. causing the service to become unavailable.
Incremental backup process
1. MySQL provides binary logs to indirectly implement incremental backup. How do binary files come from? Answer: Modify the configuration file
vim /etc/my.conf #Insert content log-bin=mysql-bin binlog_format=MIXED # represents mixed mode systemctl restart mysqld
There are three MySQL binary logging formats:
1. STATEMNET: based on sql statement. It is a sql statement that records modifications. The order when recording sql statements may be wrong under high concurrency conditions. Data recovery may result in loss or errors. The efficiency is relatively high, but it is not suitable for high concurrency scenarios.
2. ROW: Based on rows. It will accurately record the data of each row, with high accuracy but low reply efficiency.
3. MIXED: Mixed mode. It can be based on both statements and lines. When STATEMNET is used under normal circumstances, once high concurrency occurs, it will intelligently switch to ROW mode. Although the efficiency is low, it will not go wrong.
More table data settings select * from info1; #Check whether the data is written cd /usr/local/mysql/data/ #Two files will be generated mysql-bin.index mysql-bin.000001 After writing the information into the table, check the log file mysql-bin.000001 mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 #View the log of the newly inserted table mysqladmin -u root -p flush-logs #Refresh log. #At this time, a new log file mysql-bin.000002 will be generated in the data directory. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 #After refreshing, the updated content will be updated in 2. This is the breakpoint. How to recover: mysqlbinlog --no-defaults mysql-bin00000.1 | mysql -u root -p #Incremental backup, restore the data previously inserted into the table. This is called breakpoint recovery. At this time, insert information into the table. At this time, the newly inserted data is inside 000002. As long as the log is not refreshed, there will be no breakpoint. Will be inserted first and then deleted. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 If you need to back up new data, you need to refresh it again. mysqladmin -u root -p flush-logs #Refresh log. Re-insert data into the table At this time, the data after the breakpoint is in the newly generated 000003 mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 mysqladmin -u root -p flush-logs #Refresh log breakpoint Then delete the data in the table. At this time, the deletion operation will be saved in 000004. mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000003
Location recovery
Three ways:
1. Recover from a certain point to the end
2. Restore from the beginning to a certain position
3. Recover from the specified point to the specified end point
Delete table data test Restoration based on location points: 1. Recover from a certain point to the end Format: mysqlbinlog --no-defaults --start-position='position point' file name | mysql -u root -p 2. Restore from the beginning to a certain position Format: mysqlbinlog --no-defaults --stop-position='position point' file name | mysql -u root -p 3. Recover from the specified point to the specified end point Format: mysqlbinlog --no-defaults --start-position='position point' --stop-position='position point' file name | mysql -u root -p mysqladmin -u root -p flush-logs #Refresh log breakpoint Delete the data in the table and the data will be at 000005. mysqlbinlog --no-defaults --start-position='6171' mysql-bin.000004 | mysql -u root -p View location points The number after #at is the location point. To select the position point behind commit mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000004 #View the location points in the 000004 log Restore from the beginning all the way to a certain position: mysqlbinlog --no-defaults --stop-position='6106' mysql-bin.000004 | mysql -u root -p #Recover from the beginning to the specified point Range recovery: mysqlbinlog --no-defaults --start-position='6106' --stop-position='6748' mysql-bin.000004 | mysql -u root -p #Restore from specified location to specified location
Time recovery
Point-in-time recovery
1. Starting from a certain point in time
2. From the beginning to the specified end time point
3. Specify time range
Restore based on point in time: YYY-MM-DD 11:11:11 The time format should be written according to the standard format 1. Starting from a certain point in time Format: mysqlbinlog --no-defaults --start-datettime='time point' file name | mysql -u root -p 2. From the beginning to the specified end time point Format: mysqlbinlog --no-defaults --stop-datettime='time point' file name | mysql -u root -p 3. Specify time range Format: mysqlbinlog --no-defaults --start-datettime='time point' --stop-datettime='time point' file name | mysql -u root -p starting from a certain point in time mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p #Start from a certain point in time From the beginning to the specified end time point mysqlbinlog --no-defaults --stop-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p #Start from the beginning and end at a certain time Specify time range mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:37' --stop-datetime='2023-11-06 11:49:05' mysql-bin. 000004 | mysql -u root -p #Start at a certain time and end at a certain time
Rules for separate log storage
Store error logs separately vim /etc/my.conf log-error=/usr/local/mysql/data/mysql_error.log #Store error logs separately Store general access logs separately general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log #Store the general access log separately Enable slow query log slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_show_query.log long_query_time=5 #Set the timeout to 5 seconds #Enable slow query log. All queries exceeding 5 seconds will be recorded. systemctl restart mysqld
Query general log start status
show variables like 'general%'; #Query whether to enable general log access records show variables like 'log_bin%'; #Query whether the binary log is enabled show variables like 'slow%'; #Query whether to enable slow query access log
Summary
In production, incremental recovery through binlog is a very useful method.
We only need to back up the binlog file, which can be backed up and restored at any time. (This operation can only be performed by the root user)
Unless there are special needs, additional breakpoints are generally not set. Depending on the needs, breakpoints are usually set monthly through scripts.