Mysql log management
1, MySQL log management
The default log storage location of MySQL is / usr/local/mysql/data
There are two ways to open logs: through configuration files or through commands
The log opened through command modification is temporary and will be closed after the service is shut down or restarted
- MySQL common log types and startup
vim /etc/my.cnf [mysqld] ......
- Error log
It is used to record error messages that occur when MySQL starts, stops or runs. It is enabled by default
Specify the save location and file name of the log log-error=/usr/local/mysql/data/mysql_error.log
- General query log
It 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
It is used to record all error messages sent when MySQL starts, stops or runs. It is off by default
log-bin=mysql-bin or log_bin=mysql-bin
- Slow query log
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 systemctl restart mysqld
- View log status
- Check whether the general query log is enabled
mysql -u root -p show variables like 'general%';
- Check whether binary log is enabled
show variables like 'log_bin%';
- Check whether the slow query function is enabled
show variables like '%slow%';
View slow query time settings
show variables like 'long_query_time';
Set the method of starting slow query in the database
set global slow_query_log=ON; This method fails to restart the service
2, Mysql backup and recovery
- 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
Causes of data loss
1. Program error
2. Human operation error
3. Arithmetic error
4. Disk failure
5. Disaster (fire, earthquake, theft, etc.)
- Classification of database backup
1. From a physical and logical point of view, backup can be divided into
- Physical backup: backup of physical files (such as data files, log files, etc.) of database operating system
Physical backup method:
1. Cold backup (offline backup): it is performed when the database is closed
2. Hot backup (online backup): the database is running and depends on the log file of the database
3. Warm backup: the backup operation is carried out in the state of database locking table (not writable but readable)
2. Backup of logical database components (such as logical database)
3. From the perspective of database backup strategy, backup can be divided into:
- Full backup: make a full backup of the database every time
Full backup is the backup of the whole 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.
Equivalent to the cornerstone.
- Differential backup: backs up files that have been modified since the last full backup
- Incremental backup: only files modified after the last full backup or incremental backup will be backed up
- Common backup methods
1. Physical cold standby
The database is closed during backup, and the database files are packaged directly
Backup is fast and recovery is the simplest
Close MySQL database
Use the tar command to package the database folder directly
Simply replace the existing MySQL directory
2. Special backup tools mydump or mysqlhotcopy
mysqldump is a common logical backup tool
MySQL has its own backup tool, which can backup mysql
You can export the specified libraries and tables as SQL scripts
Use the command mysql to import the backed up data
mysqlhotcopy only has backup myisam and archive tables
3. Start binary log for incremental backup
For incremental backup, you need to refresh the binary log
4. Third party tool backup
Free MySQL hot backup software Percona XtraBackup
- MySQL full backup and recovery
Experimental environment
| host | operating system | IP address | Required tools / software / installation package |
|---|---|---|---|
| MySQL | CentOS7 | 192.168.86.10 | mysql-boost-5.7.20.tar.gz |
mysql -u root -p
create database SCHOOL;
use SCHOOL;
create table if not exists CLASS1 (
id int(4) not null auto_increment,
name varchar(10) not null,
sex char(10) not null,
hobby varchar(50),
primary key (id));
insert into CLASS1 values(1,'user1','male','running');
insert into CLASS1 values(2,'user2','female','singing');
set password = password('123123');
MySQL full backup
The database of InnoDB storage engine is stored in three files on disk: dB Opt (table attribute file), table name Frm (table structure file), table name IBD (table data file).
1. Physical cold backup and recovery
systemctl stop mysqld yum -y install xz
Compressed backup
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
Decompression recovery
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data systemctl restart mysql
2. mysqldump backup and recovery
(1) Full backup of one or more complete libraries (including all tables therein)
mysqldump -u root -p [password] - databases database name 1 [database name 2]... > / backup path / backup file name sql # exports database script files
Example:
mysqldump -uroot -p123123 --databases SCHOOL > /opt/SCHOOL.sql mysqldump -uroot -p123123 --databases mysql SCHOOL > /opt/mysql-SCHOOL.sql
(2) Fully back up all libraries in the MySQL server
mysqldump -u root -p [password] - All Databases > / backup path / backup file name sql
Example:
mysqldump -u root -p123123 --all-databases > /opt/all.sql
(3) Fully backs up some tables in the specified library
mysqldump -u root -p [password] database name [table name 1] [table name 2]... > / backup path / backup file name sql
Example:
mysqldump -uroot -p123123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1.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
(4) View backup files
grep -v "^--" /opt/SCHOOL_CLASS1.sql | grep -v "^/" | grep -v "^$"
Full backup recovery
1. Restore database
mysql -uroot -p123123 -e 'drop database SCHOOL;'
#"- 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 -uroot -p123123 -e 'SHOW DATABASES;' mysql -uroot -p123123 < /opt/SCHOOL.sql mysql -uroot -p123123 -e 'SHOW DATABASES;'
2. Restore data table
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 -uroot -p123123 -e 'drop table SCHOOL.CLASS1;' mysql -uroot -p123123 -e 'show tables from SCHOOL;' mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1.sql mysql -uroot -p123123 -e 'show tables from SCHOOL;'
- MySQL incremental backup and recovery
MySQL incremental backup
1. Enable binary log function
vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format = MIXED server-id = 1
#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 ls -l /usr/local/mysql/data/mysql-bin.*
2. The database or table can be fully backed up every week
mysqldump -uroot -p123123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql
3. Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 2)
mysqladmin -uroot -p123123 flush-logs
4. Insert new data to simulate the addition or change of data
mysql -uroot -p123123 use SCHOOL; insert into CLASS1 values(3,'user3','male','game'); insert into CLASS1 values(4,'user4','female','reading');
5. Generate a new binary log file again (for example, MySQL bin. 00000 3)
mysqladmin -uroot -p123123 flush-logs
6. View the contents of binary log files
cp /usr/local/mysql/data/mysql-bin.000002 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#-- Base64 output = decode rows: use the 64 bit encoding mechanism to decode and read by line
#-v: Show details
MySQL incremental backup and recovery
1. General recovery
(1) Simulate recovery steps for lost changed data
mysql -uroot -p123123 use SCHOOL; delete from CLASS1 where id=3; delete from CLASS1 where id=4; select * from CLASS1; quit mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
2. Simulate the recovery steps of all lost data (basically the same, pay attention to the log date)
mysql -uroot -p123123 use SCHOOL; drop table CLASS1; quit mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1_2021-01-29.sql mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
- checkpoint recovery
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 #As just now, use the 64 bit encoding mechanism to decode and read the details of the binary file 00000 2 by line
(1) Location based recovery
#Only recover the data before the operation ID is "609", that is, do not recover the data of "user4"
mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p
Example:
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
#Only recover the data of "user4". Skip the data recovery of "user3". After 609, there is only the fourth record
Example:
mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --start-position='609' /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
(2) Point in time based recovery
#Only recover the data before 0:39:13, that is, do not recover the data of "user4"
Example: clear the table first CLASS1,Convenient experiment mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"
#Only recover the data of "user4" and skip the data recovery of "user3" (basically consistent)
mysqlbinlog --no-defaults --start-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p