Directory
1. mysql log
log type
Transaction log performance optimization
Enable slow query log
Enable general logging
Turn on flushing the binary log
MySQL’s double “1” tuning
2. Database backup and recovery
backup type
Backup and restore of mysqldump tool
Binary log backup and recovery
1. mysql log
log type
①Transaction log: used to store and record related content of database transactions, which is enabled by default.
②Error log: used to store and record errors in the database. The default location of yum installation: /var/log/mysql.log, which is enabled by default.
③Slow query log: It is used to store records in the database. Some specific statements are executed slowly and are closed by default.
④ General log: record general operations on the database, including wrong sql statements, the default is off.
⑤Binary log: Binary mode to record all operations of the database can be used to backup and restore data, and it is disabled by default.
⑥Relay log: In the master-slave replication architecture, the slave server is used to save the binary log files transmitted by the master.
Transaction log performance optimization
Transaction log tuning is in the mysql configuration file of /etc/my.cnf
①innodb_log_file_size 50331648 #Adjust the size of each log file in bytes, the default is about 50M
②innodb_log_files_in_group 2 #The number of log partitions, the default is 2
③innodb_log_group_home_dir ./ #Transaction file path
④innodb_flush_log_at_trx_commit=0|1|2#There are 0, 1, and 2 storage methods for adjusting the transaction log
0: The transaction is not saved to the disk when it is committed, but is automatically committed and saved to the disk every three seconds in a period such as 3s, which is generally not used.
1: Every time a transaction is committed, it must be written to the disk. The speed is relatively slow, but it is safe. The default is 1.
2: Every time a transaction is submitted, it will go to the cache area, and when it reaches a certain number, it will be submitted and saved to the disk together. The speed is fast but the security is low.
⑤ Transaction log location: /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1
Enable slow query log
The slow query log is enabled in the mysql configuration file of /etc/my.cnf
①slow_query_log=on|off; #Open or close slow query log
②long_query_time=N # Configure how many seconds to judge as a slow record
③ Slow query log location: /var/lib/mysql/hostname-slow.log
Open general log
The general log is enabled in the mysql configuration file of /etc/my.cnf
①SET GLOBAL general_log=1; #Open general log
②General log location: /var/lib/mysql will generate a hostname.log in this directory;
Enable Refresh Binary Log
The binary log is enabled in the mysql configuration file of /etc/my.cnf
①server_id=1 # Binary log serverid is recommended to be configured as the last three digits of the local ip, serverid cannot be repeated
②log_bin=mysql-bin #Save the binary log file path, under /var/lib/mysql/ by default, you can configure the path yourself but remember to create the corresponding directory and give the permission to the mysql account.
③binlog_format = MIXED #Optional, specify the record format of the binary log as MIXED, generally do not write unless special requirements
④ Binary log file location: /var/lib/mysql/mysql-bin.00001
⑤ Refresh the binary log file, that is, generate a new binary log file.
Command: mysqladmin -u root -p flush-logs #It is recommended to refresh once a day
mysql double “1” tuning
① Adjust the transaction log storage mode to 1, innodb_flush_log_at_trx_commit=1. Indicates that every time a transaction is committed, it will be saved to disk immediately.
② After each transaction is committed, write the content in the binlog_cache read cache to the binary log on the disk. sync_binlog=1.
2. Database backup and recovery
Backup Type
①Incremental backup: only back up the content added every day relative to the previous ones, and restore it from scratch every day
②Full backup: complete backup of the database each time
③Differential backup: only back up the content that is different from the previous one, and restore it from scratch again and again
④Physical cold backup: when the database is closed, fully back up cp or scp to copy the packaged file of /var/libg/mysql to other servers. Yum installation path, pay attention to permission issues after decompression
⑤ Hot backup: the database is running
⑥ Warm backup: backup operation is performed when the database locked table is readable but not writable. myisam only supports warm backup because it does not support transactions
Backup and recovery of mysqldump tool
①Backup tools:
mysqldump belongs to hot standby, and the database service needs to be started
-A backs up all databases
-B backs up the specified database
-d only backs up the table structure, that is, the fields
–all–databases backup all databases
② All operations of backing up the test library include creating the library: mysqldump -u’account’ -p’password’ –databases test(library name) >/opt/test.mysql(save path)
Note: If the –databases parameter is not added during backup, the operation of creating a database will not be performed when restoring the backup, and the name of the database to be restored exists.
③Back up the test1 table in the test library: mysqldump -user -p’password’ test library name test1 (table name) >/opt/test.mysql (save path)
④ Recovery:
Restore backup database data: mysql -u user -p’password’
Restore backup table data: mysql -u user -p’password’ test(library name) 
Backup and recovery of binary logs
① Turn on the separated binary log
vim /etc/my.cnf Insert under [mysqld] server_id=11 #id is suggested to be the last three digits of the local ip bin-log=mysql-bin # Binary log storage location, do not write the directory yum installation default is /var/lib/mysql/mysql-bin.000001 # separate binary logs mysqladmin -u user -p 'password' flush-logs #A new binary log file will be generated, and the content of subsequent database operations will be saved in the new binary log file
② View the binary log
The binary log format is binary, you need to use mysqlbinlog –no-defaults –base64-output=decode-rows -v log name to view.
③When the binary log is turned on, it means backup. You can decide what period of time to restore data or where to start to restore data according to the time or location in the binary log, that is, the at node.
④ Binary log recovery backup
#General recovery mysqlbinlog --no-defaults binary log location | mysql -u user -p 'password' #Indicates to restore all operations in the log #Restore the backup according to the location breakpoint mysqlbinlog --no-defaults --start-position=node binary log position | mysql -u user -p 'password' #Indicates to restore the backup from the node position to the end mysqlbinlog --no-defaults --start-position=node binary log position | mysql -u user -p 'password' # means to restore the backup to the node from scratch before stopping #Avoid the pit: If the data is deleted, it is recommended to use --stop-position to restore, because if the last command to delete the library is recorded from a certain node, all the deleted commands will be restored once restored, which is equivalent to no restoration #Restore the backup according to the time breakpoint mysqlbinlog --no-defaults --start-datatime=2023-03-16 18:17:24 (year-month-day hour:minute:second) Binary log location | mysql -u user -p 'password' #Indicates to restore the backup from this time to the end mysqlbinlog --no-defaults --stop-datatime=2023-03-16 18:17:24 (year-month-day hour:minute:second) Binary log position | mysql -u user -p 'password' #Indicates to restore the backup from this time to this time and stop