The importance of data backup
1. In a production environment, data security is crucial
Any data loss will have very serious consequences.
2. Why data is lost: program operations, calculation errors, disk failures, unpredictable events (earthquakes, tsunamis), human operations
Classification and backup strategies of database backup
1. Physical backup
Physical backup: Backup of physical files (such as data files, log files, etc.) of the database operating system.
Physical backup method:
Cold backup (offline backup): performed when the database is closed
Hot backup (online backup): the database is running and relies on the log file of the database
Warm backup: Backup operation is performed while the database table is locked (not writable but readable).
2) Logical backup
Logical backup: Backup of logical components of the database (such as tables and other database objects).
That is, the library, table structure, and table data are saved in the form of SQL statements.
Database Backup Strategy
- Full backup (full backup): Perform a complete backup of the database every time.
- Differential backup: Backs up files that have been modified since the last full backup.
- Incremental backup: Only files that have been modified since the last full backup or incremental backup will be backed up.
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 database 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
How to migrate local database to cloud
1.
Xshell is connected to the public IP
Then compile and install on the cloud service
Drag the files that need to be backed up from the local server into the cloud server through (sz rz)
Logical backup in hot backup
Use the tool mysqldump that comes with mysql
backup command
Back up a single: mysqldump -u root -p123456 --databases test > /opt/zzr.sql Back up multiple libraries: mysqldump -u root -p 123456 --databases test test1 > /opt/test1.sql Back up all libraries: mysqldump -u root -p123456 -all-databases > /opt/test3.sql
mysqldump -u root -p --databases kgc > /opt/kgc.sql cd /usr/local/mysql/data/ ls rm -rf kgc //delete library 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
mysql -u root -p123456 -e ‘show databases;’
Automatically execute the command after entering mysql, and automatically exit after completion
-e: Specifies to automatically exit after executing the command after connecting to mysql.
restore command
mysql -u root -p < /opt/test.sql
How to restore only the data table
Restore a single table Backup first mysqldump -u root -p kgc info1 > /opt/kgc_info1.sql cd /opt ls mysql -u root -p -e 'drop table kgc.info1;' Restore specified library name mysql -u -root -p kgc < /opt/kgc_info1.sql mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql //Redirect backup mysql -u root -p -e 'drop table kgc.info1;' mysql -u root -p -e 'drop table kgc.info2;' //delete table mysql -u root -p kgc < /opt/kgc_info1-2.sql //recover 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.
Physical cold backup and physical hot backup
: Features Simple
Data volume takes up a large amount of backup space
mysqldump This is the command that comes with MySQL to back up files.
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 for data migration, which will inevitably take up more space. Compared with physical backup, it takes up much less space.
Incremental backup
mysqldump supports incremental backup
There is no duplicate data, the backup volume is small and the time is short.
Between mysqldump incremental backup and restoration of table data, the table will be locked
Disadvantages: Locking the table during backup will inevitably affect the business. If it exceeds 10G, it will take a long time and cause the service to be unavailable.
1. The binary log provided by mysql indirectly implements incremental backup.
Where do you get the binary file?
vim /etc/my.cnf server-id=1 log-bin=mysql-bin binlog_formst=MIXED wq
There are three MySQL binary logging formats:
1. STATEMNET: based on sql statement. It is to record modified SQL statements. The order of 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. The data of each row will be recorded accurately. The accuracy is high but the reply efficiency is low.
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:
Recover from a certain point to the end
From the beginning, restore to a certain position
Start from the specified point——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-based recovery
1. Starting from a certain point in time:
mysqlbinlog –no-defaults –start-datetime=’point in time’ file | mysql -u root -p
2. From the beginning to the specified end time point
mysqlbinlog –no-defaults –stop-datetime=’point in time’ file | mysql -u root -p
3.Specify time range
mysqlbinlog –no-defaults –stop-datetime=’point in time’ file –stop-datetime=’point in time’ file |mysql -u root -p
Time format: 2023-11-06 11:44:32
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
Log storage
The default storage location of MySQL logs is /usr/local/mysql/data vim /etc/my.cnf [mysqld] ## Error log, used to record error information that occurs when MySQL starts, stops or runs. It is enabled by default. log-error=/usr/local/mysql/data/mysql_error.log #Specify the storage location and file name of the log ##General query log, used to record all MySQL connections and statements, is closed by default general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log ##Binary log (binlog) is used to record all statements that have updated data or potentially updated data. It records data changes and can be used for data recovery. It is enabled by default. log-bin=mysql-bin #Also log_bin=mysql-bin ##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. To facilitate optimization, it is turned off by default. slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5 #Set statements executed for more than 5 seconds to be recorded, the default is 10 seconds
Query shared log access status
mysql -u root -p show variables like 'general%'; #Check whether the general query log is turned on show variables like 'log_bin%'; #Check whether the binary log is turned on show variables like '%slow%'; #Check whether the slow query function is enabled show variables like 'long_query_time'; #View slow query time settings set global slow_query_log=ON; #Set the method to enable slow query in the database
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.