Backup: full backup incremental backup
Full backup: Back up the entire database completely
Incremental backup: Based on the full backup, backup of subsequent newly added content
Backup requirements
1. In a production environment, data security is crucial, and any loss of data may have very serious consequences.
2. Why data is lost, program operation, calculation error, disk failure, unpredictable time (earthquakes, etc.), human operation
Cold backup: shut down the backup, stop the mysql service, and then perform the backup
Hot backup: startup backup, no need to shut down the mysql service for 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). It backs up and saves the database, table structure, and table data in the form of SQL statements.
(Delete all files directly in the database system)
Physical backup: full backup, a complete packaged backup of the entire database
Advantages: simple operation
Disadvantages: The database file occupies a large amount of space, the backup and recovery time is very long, and a temporary database service is required.
It is best to turn off the service when packaging backup to avoid new data entering and being overwritten, which may also cause recovery failure.
Cold backup experiment
terminal
Close mysql service
systemctl stop mysqld.service
Install xz service
yum -y install xz
Enter the mysql directory
cd /usr/local/mysql/
Pack
tar Jcvf /opt/mysql_all_$(date + %F).tar.xz data
Unzip the file you just compressed
cd /opt/
tar Jxvf mysql_all_.tar.xz
Delete source file
Data recovery
Copy the unzipped file to the mysql directory
The table data has not changed and the original data is maintained.
Remote replication
Delete the mysql data file in terminal 2
Through remote Copy, copy the mysql data file of terminal 1 to terminal 2
Copy the mysql directory
Note: Check the file location of the data file copied to the mysql directory.
Logical backup in hot backup
mysqldump This is a tool that comes with mysql
mysqldump
Back up a single library
mysqldump -u root -p123456 –databases library name > /opt/filename.sql
Back up multiple libraries
mysqldump -u root -p123456 –databases library name 1 library name 2 > /opt/file name.sql
Back up all libraries
mysqldump -u root -p123456 –all-databases > /opt/filename.sql
Specify that after executing the command after connecting to mysql, it will automatically exit.
mysql -u root -p123456 -e ‘show databases;’
Example
Back up a single library
Delete this library
restore
Command line backup and restore
Create library create database xiaobu; Create table create table info ( id int(4) not null primary key, name char(5) default null, hobby varchar(10) default null ); Back up multiple libraries backup mysqldump -u root -p --databases library name 1 library name 2 > /opt file name.sql delete mysql -u root -p -e 'drop database library name 1;' mysql -u root -p -e 'drop database library name 2;' Restore multiple mysql -u root -p < /opt/library name.sql Back up all mysqldump -uroot -p --all-databases > /opt/ filename.sql delete mysql -u root -p -e 'drop database library name 1;' mysql -u root -p -e 'drop database library name 2;' Restore multiple mysql -u root -p < /opt/library name.sql
How to restore the specified library and table
Single table backup mysqldump -u root -p library name table name > /opt/file name.sql Delete without entering the database mysql -u root -p -e 'drop table library name.table name' recover mysql -u root -p library name < /opt/file name.sql multiple tables mysqldump -u root -p library name table name 1 table name 2 > /opt/file name.sql delete mysql -u root -p -e 'drop table library name.table name 1' mysql -u root -p -e 'drop table library name.table name 2' recover mysql -u root -p library name < /opt/filename.sql
Physical cold backup and physical hot backup
Features: Simple
The amount of data takes up a relatively large amount of backup space.
mysqldump This is the command that comes with mysql to back up files.
Features: Convenient and simple, but it can only restore 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 and takes up a lot of space. Compared with physical backup, it takes up much less space.
Incremental backup
mysqldump supports incremental backup
No duplicate data, small backup volume, time limit
During mysqldump incremental backup to restore table data, the table will be locked (advantages)
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.
Incremental backup process
1. Implement incremental backup of binary log files provided by mysql
Where do you get the binary file?
Modify the configuration file (vim /etc/my.conf)
log-bin=mysql-bin ? binlog_format=MIXED
There are three mysql binary record formats
1. STATENET: based on sql statement
Record modified sql statements. Under high concurrency conditions, the order when recording sql statements may be wrong, which may cause loss and error when restoring data. It is more efficient.
2. ROW: Based on rows
Accurately records the data of each row, the accuracy is high, but the recovery efficiency is low
3. MIXED: It can be based on SQL statements or rows.
Use STATEENT under normal circumstances. Once high concurrency occurs, it will intelligently and automatically switch to the row row.
Restart service
View the backup binary file (must be viewed in the data directory)
mysqlbinlog –no-defaults –base64-output=decode-rows -v mysql-bin.000001
mysqladmin -u root -p flush-logs refresh log files (print breakpoints)
A log file 00002 will be generated.
Delete the contents of the table (breakpoint before deleting)
How to restore
mysqlbinsql –no-defaults mysql-bin.000003 | mysql -u root -p
- Incremental backup, location node and time point, pay attention to breakpoints
Location-based node
Start from a certain point and resume to the end
mysqlbinlog –no-defaults –start-position=’position point’ file name | mysql -u root -p
From the beginning, all the way back to a certain position
mysqlbinlog –no-defaults –stop-position=’position point’ file name | mysql -u root -p
From specified point—specified end point
mysqlbinlog –no-defaults –start-position=’position point’ –stop-position=’position point’ file name | mysql -u root -p
Start from a certain point and resume to the end
From the beginning, all the way back to a certain position
From specified point—specified end point
Time point based
1. Start from a certain point in time and restore to the end
mysqlbinlog –no-defaults –start-datetime=’time point’ file | mysql -u root -p
2. From the beginning to the specified end time
mysqlbinlog –no-defaults –stop-datetime=’time point’ file | mysql -u root -p
3. Specify time range
mysqlbinlog –no-defaults –start-datetime=’point in time’ –stop-datetime=’point in time’ File | mysql -u root -p
Start from a certain point in time and resume to the end
From the beginning to the specified end time point
Specify time range
Summary
In production, incremental recovery through binlog is a very useful method.
As long as we need to back up the binlog file, we can back up and restore it at any time.
Additional questions
Write a script to set a breakpoint on the database on the 20th of every month.
Automatic incremental backup after breakpoint
How to record error logs in log files
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5