Key points for performing non-hot standby operations on MySQL database

MySQL Backup recovery scenario One:

In case of small database data, use mysqldump for full backups and mysqlbinlog for incremental backups.

Full Backup, Example:

# mysqldump-a--lock-all-tables--routines--triggers--master-data=2 >/backup/all-' Date +%F '. sql

Incremental backup, Example:

# mysqlbinlog/mydata/data/mysql-bin.0000## >/backup/incre-' Date +%f '. sql

The process of recovery:

1. Close the database:

# Service Mysqld Stop

2, modify the configuration file, so that the startup database does not provide services:

# vim/etc/my.cnf #在mysqld字段添加如下内容: skip-networking

Also MySQL uses the location of the socket file to avoid other people using the database

Socket=/home/username/mysql.sock #在修改配置文件中生成mysql the location of the. sock file, both the client and server side configurations need to be changed to a consistent mysql.sock file path and name. In this way, other clients cannot connect to the database because of inconsistent configuration, while the administrator of the repair database is available.

3. Start the database and try to recover:

# service Mysqld Start

Be sure to remember this time. If you restore the database by typing # mysql </path/from/file.sql directly under the command line, there will be a lot more binary logging in the newly generated database. So this way of recovery is not very good, or the habit is bad, backup time using the command line, this habit is good, to restore the time to connect to the database, first set the environment variable to not log binary logs, and then restore, so as to avoid many unnecessary log records, eliminating the waste of disk space.

To connect to the database:

# MySQL > SET session sql_log_bin=0;> source/path/from/file.sql;> Source/path/from/incre.sql;

After all the data has been restored, turn on the logging function of the binary log.

> SET Session Sql_log_bin=1;>\q

Stop MySQL Service

# Service Mysqld Stop

Edit the configuration file, release the permissions of the service, remove the skip-networking option, and modify the directory location generated by the socket file to the original defined location. ：

# VIM/ETC/MY.CNF

Restart the server in full access mode.

# service Mysqld Start

MySQL Backup recovery Scenario Two

Backup based on LVM snapshots:

Connect to the MySQL database and apply a shared lock to all tables in the database:

# mysql> FLUSH TABLES with READ LOCK;

In the actual production environment, this command may request a long time to complete the lock on all tables,

After the lock is successfully applied, it is necessary to record the location information of the binary log file and the log that is used at this time, to save this information to record and back up.

> SHOW MASTER STATUS;

At this point, you can create a snapshot by starting a different terminal, with a simple example of the following:

# lvcreate-s-L size-p r-n name/dev/vg_name/lv_name

After creating the snapshot, perform the log scrolling function at the previous terminal:

> FLUSH LOGS The purpose of this operation is to generate a new binary logging file, after which the binary log will be stored in this file, the operation of the backup database is easier to understand, just a lot of time is more important, but also to avoid loss of data is an important guarantee, before the backup method , the mysqldump command can do a full backup with the--master-data option to record the name and location of the binary log file, and I personally feel that scrolling the log when using mysqldump as a backup is not conducive to viewing the location of the previous backup when you do the incremental backup later. In order to avoid the operation of the repeated view of the log content, but also to avoid the habit of omission, sometimes scrolling, sometimes forget to scroll the log, the data will affect the backup. When using snapshot-based backup, it is recommended that you use flush LOGS, which generates new log files to facilitate the comparison of the files in the snapshot backup with the files in the database, so it is best to use this command in this scenario. If database data is small, I think it's easy to do a full backup every time, and it's easy to recover the data. A script that uses a full backup can basically solve the problem.

Release the lock after scrolling the log:

> UNLOCK TABLES;

After that, the snapshot content is copied and archived for backup operations.

Database recovery operations backed up using LVM:

This process is actually more consistent than the mysqldump command, just copy the full backup database contents to the database data directory, and then if there are other incremental parts of the data recovery will need to use binary log file to do incremental part of the data recovery operation. However, the specific operation process is to follow the previous method of recovery, the recovery time to start the service also do not provide services, and to turn off the functionality of binary logging (when there is incremental data need to recover), to avoid unnecessary binary log records.

MySQL backup is the key to backup is the binary log file, binary log files and database data directory cannot be on a disk, this is the most basic requirements, in addition, to back up the configuration files, these are the database configuration work important basic work.

Key points for performing non-hot standby operations on MySQL database