Log management

MySQL log default save location

/usr/local/mysql/data

Error log

It is used to record the error messages that occur when MySQL is started, stopped or running. It is enabled by default

log-error=/usr/local/mysql/data/mysql_error.log     #Specify the save location and file name of the log

General query log

It is used to record all continuous and statements of MySQL. It is off by default

##The general query log is used to record all MySQL connections and statements. 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 updated or potentially updated data statements, record data changes, 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

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

s1ow_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

Query whether each log is enabled

systemctl restart mysqld
mysql -u root -P
show variables like 'general%';       #Check whether the general query log is enabled

show variables like 'log_bin%';       #Check whether binary log is enabled

show variables like '%slow%';         #Check whether the slow query day function is enabled
show variables like 'long_query_time';    #View slow query time settings

set global slow_query_log=ON;         #Set the method of starting slow query in the database

Database backup

Importance of data backup

• 1. The primary purpose of backup is disaster recovery

• 2. In a production environment, data security is critical

• 3. Any loss of data can have serious consequences

• 4. Causes of data loss

  Program error
  Human operation error
  Arithmetic error
  Disk failure
  Disasters (e.g. fire, earthquake) and theft

Classification of database backup

From the perspective of physical logic, backup can be divided into

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 performed when the database is locked in a table (not writable but readable)

Logical backup: backup of database logical components (such as tables and other database objects)

From the perspective of database backup strategy, backup can be divided into

• Full backup: perform a full backup of the database each time
• 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 of MySQL

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 mysqlhostcopy

• 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

• Free MySQL hot backup software Percona XtraBackup

MySQL full backup

• 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

advantage:

• Backup and recovery 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 mysq| to import the backed up data

MySQL full backup and recovery

Environmental preparation

use ku;
create table if not exists info1 (
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 info1 values(1,'user1','male','running');
insert into info1 values(2,'user2','female','singing');

MySQL full backup

The database of InnoDB storage engine is stored in three files on disk:

• db. Opt (table properties 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/

2. mysqldump Backup and recovery
(1),Full backup of one or more complete libraries (Include all tables)
mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql    #The exported is the database script file
 example:
mysqldump -u root -p --databases ku> /opt/gcc.sql       #Back up a gcc Library
mysqldump -u root -p --databases mysql ku > /opt/mysql-ku.sql    #Backup mysql and gcc Libraries

(2),Full backup MySQL All libraries in the server
mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql
 example:
mysqldump -u root -p --all-databases > /opt/all.sql

(3),Fully backs up some tables in the specified library
mysqldump -u root -p[password] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.sql
 example:
mysqldump -u root -p [-d] ku info1 info2 > /opt/ku_info1.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/ku_info1.sql | grep -v "^/" | grep -v "^$"

MySQL full recovery

systenctl start mysqld

(1)Restore database
mysql -u root -pabc123 -e 'drop database ku;'
#The "- 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 'SHOW DATABASES;'

mysql -u root -p < /opt/ku.sql         #Restore previously backed up ku database
mysql -u root -p -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.
mysqldump -u root -p ku info1 > /opt/ku_info1.sql    #Backup table info1

mysql -u root -p -e 'drop table ku.info1;'      #Simulate deletion of info1 table in ku Library
mysql -u root -p -e 'show tables from ku;'

mysql -u root -p ku < /opt/ku_info1.sql        #Restore the info1 table in the ku library
mysql -u root -p -e 'show tables from ku;'

MySQL incremental backup and recovery

1.Enable binary log function
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED      #Optional. Specifies that the binary log record format is MIXED
server-id = 1              #This command can be added or not

#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 start mysqld 
ls -l /usr/local/mysql/data/mysql-bin. *

2.The database or table can be fully backed up every week
mysqldump -u root -p ku info1 > /opt/ku_info1_$(date +%F).sql
mysqldump -u root -P --all-databases ku > /opt/ku_$(date +%F).sql

3.Incremental backup can be performed every day to generate new binary log files(for example:mysql-bin.000002)
mysqladmin -u root -p flush-logs

4.Insert new data to simulate the addition or change of data
use ku;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

5.Generate a new binary log file again(for example:mysql-bin.000003)
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

6.View the internal secret of binary log file
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 64 bit encoding mechanism to decode and read by line
#-v: Show details