MySQL management-data backup and recovery

MySQL management-backup and recovery

You must be aware of the importance of data. Here we have to briefly introduce the importance of backup:

Backup is a means to ensure availability, but it cannot be guaranteed. For scenarios with high data availability, backup is required.

 

Backup and considerations:

Backup is nothing more than saving the dataset as a replica set. Generally, the company's backup and recovery operations cannot exceed half an hour, which requires high recovery efficiency.

Significance of Data Backup:

· Disaster recovery

· Demand changes

· Test

 

In order to ensure normal backup without affecting online services, we need to select backup methods, backup types, backup media, and so on, considering that online services are generally carried out when the business volume is minimal, if it is a cross-region business, it is another code. Therefore, all kinds of factors must be taken into consideration. Therefore, backup is automatically performed and usually relies on the script method.

However, the following issues need to be considered in advance no matter how backup is performed:

1. How long data is lost with tolerance

If the data can be tolerated for a few days, Backup recovery is particularly easy.

2. How long must the restoration be completed?

Different backup methods have different backup efficiency. Simple backup tools may be executed slowly, but complex backup tools may be more efficient, therefore, you must select a backup tool suitable for the current environment.

3. Whether continuous service provision is required

 

4. What needs to be restored?

The whole database server, a single database, one or more tables, or a statement

# The above points must be investigated in advance. Otherwise, they may not be able to meet the recovery requirements.

 

Common MySQL backup tools

Mysqldump logical Backup Tool

Classic backup tools support hot backup for innodb tables, while MyISAM tables support Hot Backup (readable but not writable) at most, and the backup and recovery speed is relatively slow;

 

The full version of mysqldumper mysqldump is also a logical backup tool.

Supports parallel backup, improves the speed, and provides a simple mechanism for output.

 

Lvm-snapshot lvm snapshot is based on snapshot Backup. the table must be locked from the moment the snapshot is created, but the speed is very fast. It can be completed instantly as long as it can request to apply a read lock, close to Hot Standby tools

The backup data is consistent in time and is a physical backup.

Because global read locks are required, it may take a long time for a very busy online transaction processing system.

 

Select built-in backup tool: You can select data using select foreign OUTFILE and save it to a file. Then, you can use the load data command to load the data.

Syntax:

Select into outfile '/path/to/xx. SQL' # You can use the select clause to filter out the required results and back up the results with the where clause.

Load data infile '/path/to/xx. SQL'

This backup method does not support SQL statements. You must use load data for Backup recovery.

Its performance is faster than mysqldump and supports backing up data under a certain condition. At the same time, the files backed up are relatively small.

 

Ibbackup, xtrabackup:

· Follow the gpl Protocol

· Physical backup tools

· Support innodb hot backup in the true sense, unlike mysqldump

· For Myisam, only warm backup is supported

· Fast speed

 

Mysqlhotcopy: almost a cold Backup Tool

· Slow speed

· Poor use

 

Steps required for restoring a backup

(1) Stop mysql Service (cold backup and snapshot recovery must be stopped

(2) Record service configuration and File Permissions

(3) copy the backup file to the data directory

(4) Adjust configurations as needed

(5) change the File Permission as needed

(6) try to start the service, but restrict access permissions.

 

Restore a binary file

(1) Mount logical backup

(2) check or replay binary logs

(3) Check that data restoration is normal

(4) restart the service with full permissions.

In fact, the loading process of different backup methods is only part of the above, not the whole process.

 

Use of mysqldump Backup Tool

If you want to back up data from a table to a data file, you can use mysqldump, mysqldumper, and so on.

Show users older than 30

Mysql> select * from students where age> 30;

+ ------- + ------ + --------- +

| Name | id | Age | class |

+ ------- + ------ + --------- +

| Bob | 1 | 40 | 1 class |

| Jerry | 3 | 33 | 2 class |

+ ------- + ------ + --------- +

2 rows in set (0.00sec)

Back up the data using the select command

The directory path must be accessible to the user currently running the mysql service. Currently, only tmp is available except the data directory.

Mysql> select * from students where age> 30 into outfile '/tmp/students. SQL ';

View the configuration file, as shown below, we have exported the information of users older than 30

[Root @ test ~] # Cat/tmp/students. SQL

Bob 1 40 1 class

Jerry 3 33 2 class

However, the content is plain text information, so mysql cannot be used to import it directly. You must use the load data in file method to achieve this.

Delete and restore user information older than 30 in the table

Mysql> deletefrom students where age> 30;

Query OK, 2 rowsaffected (0.07 sec)

 

Mysql> select * from students where age> 30;

Empty set (0.00sec) explicitly states that the file will be restored to the students table

Mysql> load data infile '/tmp/students. SQL' into tablestudents;

Query OK, 2 rowsaffected (0.11 sec)

Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

 

Back up a mysql database using mysqldump

Mysqldump features:

· One of the most classic and traditional mysql backup tools

· Backing up the entire server

· Backing up a single database or some databases

· One or more tables can be backed up

· Some rows in the table can be backed up.

· Storage functions and triggers can be backed up

· Automatically record the binary file name and location during Backup

· Innodb supports single-transaction Hot Backup

 

Mysql parameter description:

1 2 3 Next Page