catalogue
(1) Log classification of MySQL
(3) enter the database to check whether the corresponding log is enabled
II MySQL full backup and recovery
(3) Classification of database backup
② Special backup tools mydump or mysqlhotcopy
③ Enable binary logs for incremental backups
② Advantages and disadvantages of full backup
③ Database full backup classification
III Basic commands for database cold backup and recovery and full backup and recovery
(1) Physical cold backup and recovery
(2)mysqldump backup and recovery
① Full backup of one or more complete libraries (including all tables therein)
② fully back up all databases in MySQL server
③ fully back up some tables in the specified library
IV Method of MySQL incremental backup and recovery
② the database or table can be fully backed up every week
④ insert new data to simulate the increase or change of data
⑤ generate a new binary log file again (e.g. MySQL bin. 000006)
⑥ check the contents of binary log files
Prepare a learning table first
mysql -u root -p
create database school;
use school;
create table class(
id int(10) not null,
name varchar(20) not null,
sex char(2) not null,
cardid varchar(20) not null,
phone varchar(11),
address varchar(50));
desc class;
insert into class values ('1','zhangsan','male','123456','111111','Suzhou');
insert into class values ('2','lisi','female','123123','222222','Suzhou');
insert into class values ('3','wangchao','male','123412','333333','Yangzhou');
insert into class values ('4','zhanglong','male','112233','444444','Nanjing');
insert into class values ('5','zhaohu','male','111222','555555','Suzhou');
select * from class;
I Log management of MySQL
The default location of MySQL logs is / usr/local/mysql/data
(1) Log classification of MySQL
- Error log
- General query log
- Binary log
- Slow query log
(2)MySQL log on
- By permanently modifying the MySQL configuration file
vim /etc/my.cnf [mysqld] ##The error log is used to record the error messages that occur when MySQL starts, stops or runs. It is enabled by default #Specify the location and file name of the error log log-error=/usr/local/mysql/data/mysql_error.log ##The general query log is used to record all connections and statements of MySQL. It is closed by default general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log ##Binary log is used to record all statements that have updated data or have potentially updated data. It records the changes of data and can be used for data recovery. It is enabled by default log-bin=mysql-bin #You can also log_bin=mysql-bin ##Slow query log is 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 for optimization. It is closed by default slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log long_query_time=5 #Set the statements executed for more than 5 seconds to be recorded. The default is 10 seconds systemctl restart mysqld.service
(3) enter the database to check whether the corresponding log is enabled
mysql -u root -p
① Check whether the general query log is enabled
show variables like 'general%';
② Check whether binary log is enabled
show variables like 'log_bin%';
③ Set the method of starting slow query in the database
- Our external configuration file has been opened, so we won't demonstrate it here
set global slow_query_log=ON;
Supplement:
II MySQL full backup and recovery
(1) Importance of data backup
- The primary purpose of backup is disaster recovery
- In the production environment, the security of data is very important
- Any loss of data can have serious consequences
(2) Causes of data loss
- Program error
- Human operation error
- Arithmetic error
- Disk failure
- Disasters (e.g. fire, earthquake) and theft
(3) Classification of database backup
① Classification from the perspective of physics and logic
(1) Physical backup
- Backup of physical files (such as data files, log files, etc.) of database operating system
- Methods of physical backup
- Cold backup (offline backup): it is performed when the database is closed
- Hot backup (online backup): the database is running and depends on the log file of the database
- Warm backup: the backup operation is carried out in the state of database locking table (not writable but readable)
(2) Logical backup
- Backup of database logical components (such as tables and other database objects)
② Classification from the perspective of database backup strategy
(1) Full backup
- Complete backup of database every time
(2) Differential backup
- Backs up files that have been modified since the last full backup
(3) Incremental backup
- Only files modified after the last full backup or incremental backup will be backed up
(4) Common backup methods
① Physical cold standby
- The database is closed during backup, and the database files are packaged directly
- Backup is fast and recovery is the simplest
② Special backup tools mydump or mysqlhotcopy
- mysqldump is a common logical backup tool
- mysqlhotcopy only has backup MyISAM and ARCHIVE tables
③ Enable binary logs for incremental backups
- For incremental backup, you need to refresh the binary log
④ Third party tool backup
- MySQL hot backup software for free
(5)MySQL full backup
① Concept
- It is a backup of the entire database, database structure and file structure
- Save the database at the completion time of backup
- It is the basis of differential backup and incremental backup
② Advantages and disadvantages of full backup
- advantage:
- Backup and recovery operations are simple and convenient
- Disadvantages:
- There is a lot of duplication in the data
- Takes up a lot of backup space
- Long backup and recovery time
③ Database full backup classification
(1) Physical cold backup and recovery
- Close MySQL database
- Use the tar command to package the database folder directly
- Simply replace the existing MySQL directory
(2) mysqldump backup and recovery
- MySQL has its own backup tool, which can facilitate the backup of MySQL
- You can export the specified libraries and tables as SQL scripts
- Use the command mysql to import the backed up data
III Basic commands for database cold backup and recovery and full backup and recovery
(1) Physical cold backup and recovery
systemctl stop mysqld yum -y install xz #xz is a compression tool #Compressed backup tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/ #Decompression recovery tar Jxvf /opt/mysql_all_2021-02-05.tar.xz -C /usr/local/mysql/data systemctl start mysqld
(2)mysqldump backup and recovery
① Full backup of one or more complete libraries (including all tables therein)
#The exported backup file is the database script file mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql Example: mysqldump -u root -p --databases school > /opt/school.sql mysqldump -u root -p --databases mysql school > /opt/mysql-school.sql
② fully back up all databases in MySQL server
mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql Example: mysqldump -u root -p --all-databases > /opt/all.sql
③ fully back up some tables in the specified library
mysqldump -u root -p[password] [-d] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.sql #Use the "- d" option to explain that only the table structure of the database is saved #If the "- d" option is not used, the table data will also be backed up Example: mysqldump -u root -p school class > /opt/school_class.sql
④ view backup files
cat /opt/Backed up files |grep -v "^--" | grep -v "^/" | grep -v "^$" Example: cat /opt/school_class.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"
(3)MySQL full recovery
① Restore database
#"- e" option is used to specify the command to be executed after connecting to MySQL. After the command is executed, it will exit automatically mysql -u root -p -e 'drop database school;' mysql -u root -p -e 'show databases;' mysql -u root -p < /opt/school.sql mysql -u root -p -e 'show databases;'
② restore data sheet
- When the backup file contains only the backup of the table, but not the statement of the created library, the library name must be specified when performing the import operation, and the target library must exist.
mysql -u root -p -e 'drop table school.class;' mysql -u root -p -e 'show tables from school;' mysql -u root -p school < /opt/school_class.sql mysql -u root -p -e 'show tables from school;'
IV Method of MySQL incremental backup and recovery
(1)MySQL incremental backup
① Enable binary log function
vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id = 1 binlog_format = MIXED #Specifies that the recording format of binary log is MIXED #There are three different recording formats for binary log: state (based on SQL STATEMENT), row (based on row) and mixed (mixed mode). The default format is state systemctl restart mysqld.service ls -l /usr/local/mysql/data/mysql-bin.*
② the database or table can be fully backed up every week
- Such scheduled tasks can be executed in combination with crontab -e scheduled tasks
#Perform backup manually mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql #Use crontab -e to execute planned tasks; Backup the table class and all libraries at 2 a.m. every week 0 2 * * 1 mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql 0 2 * * 1 mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql
③ Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 5)
mysqladmin -u root -p flush-logs
④ insert new data to simulate the increase or change of data
mysql -u root -p
use school;
insert into class values ('6','zzz','male','897656','666666','Nanjing');
insert into class values ('7','aaa','female','098765','777777','Suzhou');
⑤ generate a new binary log file again (e.g. MySQL bin. 000006)
mysqladmin -u root -p flush-logs #The database operation in step 4 above will be saved to MySQL bin In the 00000 2 file, if the database data changes again, it is saved in MySQL bin 00000 3 document
⑥ check the contents of binary log files
cp /usr/local/mysql/data/mysql-bin.000005 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000005 #--Base64 output = decode rows: use 64 bit encoding mechanism to decode and read by line #-v: Show details
(2)MySQL incremental recovery
① General recovery
(1) Simulate recovery steps for lost changed data
mysql -u root -p use school; delete from class where id=6; delete from class where id=7; select * from class; quit mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p mysql -u root -p -e "select * from school.class;"
(2) simulate the recovery steps of all lost data
mysql -u root -p use school; drop table class; show tables; quit mysql -uroot -p school < /opt/school_class_2021-02-14.sql mysqlbinlog --no-defaults /opt/mysql-bin.000005 | mysql -uroot -p mysql -u root -p -e "select * from school.class;"
② breakpoint recovery
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#Contents of some binary files
......
BEGIN
/*!*/;
##-------------Explanation: at xxx indicates the location point------------------------------------------------
# at 302
##--------------Explanation: the beginning 210206 15:45:53 indicates the time. Others are not used now-----------------------------------
#210206 15:45:53 server id 1 end_log_pos 449 CRC32 0xe972def7 Query thread_id=6 exec_time=0 error_code=0
##--------------Explanation: here is the executed operation statement---------------------
use `school`/*!*/; <-------------use school;Use database
SET TIMESTAMP=1612597553/*!*/; <------------Establish timestamp
insert into class values ('6','zzz','male','897656','666666','Nanjing') <-------Insert data into a table
/*!*/;
##---------------------------------------------------------------
# at 449
#210206 15:45:53 server id 1 end_log_pos 480 CRC32 0x5efde826 Xid = 446
COMMIT/*!*/;
# at 480
#210206 15:45:54 server id 1 end_log_pos 545 CRC32 0x11768895 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 545
#210206 15:45:54 server id 1 end_log_pos 628 CRC32 0x778ea5fa Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1612597554/*!*/;
##-------------------------------Insert second data--------------------------
BEGIN
/*!*/;
# at 628
#210206 15:45:54 server id 1 end_log_pos 775 CRC32 0x66e3bb53 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1612597554/*!*/;
insert into class values ('7','aaa','female','098765','777777','Suzhou')
/*!*/;
# at 775
#210206 15:45:54 server id 1 end_log_pos 806 CRC32 0x7b972395 Xid = 447
COMMIT/*!*/;
# at 806
#210206 15:48:52 server id 1 end_log_pos 853 CRC32 0x0d77c456 Rotate to mysql-bin.000003 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......
(1) Location based recovery
- Only recover the data before the location point is "628", that is, do not recover the data with "id=7"
#Analog data loss mysql -uroot -pabc123 school < /opt/school_class_2021-02-06.sql mysql -uroot -pabc123 -e "select * from school.class;" #Stop recovering data at location 628 mysqlbinlog --no-defaults --stop-position='628' /opt/mysql-bin.000005 | mysql -uroot -pabc123 #View the data of class table mysql -uroot -pabc123 -e "select * from school.class;"
- Only recover the data of "id=7" and skip the data of "id=6"
#Analog data loss mysql -uroot -pabc123 school < /opt/school_class_2022-02-14.sql mysql -uroot -pabc123 -e "select * from school.class;" #Recover data from location point 628 mysqlbinlog --no-defaults --start-position='628' /opt/mysql-bin.000005 | mysql -uroot -pabc123 #View the data of class table mysql -uroot -pabc123 -e "select * from school.class;"
