Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about how mysql backs up and recovers data. have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading how mysql backs up and recovers data.

Mysql physical backup and logical backup

Mysql physical backup:

Mysql physical backup refers to the directory file that copies the entire database data (default is in the / data/db_data directory)

Features:

Simple and fast, but not suitable for memory storage engine (data is not saved to hard disk). It is mainly aimed at MyISAM storage engine, which can be implemented through file backup tools such as scp,cp,tar, or mysqlhotcopy included with mysql.

Disadvantages:

The storage engine corresponding to innodb requires the backup tool mysqlbackup of mysql Enterprise Edition.

Mysql logical backup:

Save data by getting database structure and content

Advantages:

Can be used for any storage engine, online backup, mysql comes with a tool mysqldump

Disadvantages:

Slow speed (get data and convert it to the specified format), large volume

MySQL online backup and offline backup

MySQL online backup

There is no need to shut down the mysql CVM. Proper locking tables can effectively ensure the integrity of the backup.

Offline backup:

You need to shut down the mysql CVM, which usually occurs in the slave database to prevent the normal operation of the master database.

Remote backup and local backup

Remote backup tool: mysqldump

Local backup tool: mysqldump,mysqlhotcopy

Complete backup and incremental backup:

Full backup: back up all databases

Incremental backup: backup of database occurrence variables within a point in time (depending on mysql binary log) needs to be enabled-log-bin (a new binary file is generated each time you restart

Mysql client: the flush logs (linux command line mysqladmin flush-logs) command can manually generate a new)

Backup Strategy Crossing:

Shell > mysqldump-uroot-p-all-databases > backup_date.sql (lock table)

If the database is full of innodb engines, you can add-- single-transaction to ensure that the transaction can also be backed up. If you need to generate a new binary log as follows:

Mysqldump-uroot-p-single-transaction-flush-logs-master-data=2-all-databases > backup_date.sql

To reduce space waste, you can delete useless binary log files by following:

Mysqldump-uroot-p-single-transaction-flush-logs-master-data=2-all-databases-delete-master-logs > backup_date.sql

When there is a master and slave, you need to be careful. It may not have been updated from the CVM.

Restore by executing the following statement:

Mysql-uroot-p

< backup_date.sql 或者binary log恢复 mysqlbinlog mysql-bin.000007 mysql-bin.000008 | mysql -uroot -p mysqlbinlog 还可以设置起止时间以及位置，具体请参考Mysql手册 或者　man mysqlbinlog mysqldump使用说明 mysqldump [arguments] >

File_name

Back up all databases used-- all-databases such as

Mysqldump-- all-databases > dump.sql

Specify database

Mysqldump-- databases db1 db2... > dump.sql (if not added-- create databases and use database statements will not be generated in the databases file)

Mysql > CREATE DATABASE IF NOT EXISTS db1

Mysql > USE db1

Mysql > source dump.sql

There is another way to use mysqldump-- tab. Please refer to the manual for details.

If your storage engine is innodb, consider the parameters

-events (event)

-- routines (stored procedures and functions)

-- triggers (trigger, included by default)

You can also specify to skip through the following parameters

-skipevents,-skip-routines, or-skip-triggers

Mysqldump can also back up the table structure and data separately as follows:

-- no-data does not back up data (only backup table structure)

-- no-create-info does not back up table structure (only data is backed up)

Shell > mysqldump-- no-data test > dump-defs.sql

Shell > mysqldump-- no-create-info test > dump-data.sql

What do you think of the article on how mysql backs up and recovers data? does it yield anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.