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

Today, the editor is going to talk about how to back up MySQL data.

one

The purpose of data backup is very direct and simple, which is to avoid heavy losses caused by unpredictable and accidental events, so the more important and frequent the data is, the more necessary it is to back up the data. This paper takes MySQL as an example to talk about how to do data backup and recovery in the face of different storage engines.

In order to deal with different data processing, MySQL provides more than a dozen different storage engines, but we do not need to understand them one by one, because people familiar with using MySQL know that there are two more commonly used storage engines, MyISAM and InnoDB.

MyISAM is the ISAM extension format of MySQL and the default database engine. It does not support transactions and foreign keys, but its advantage lies in its fast access speed and no requirement for transaction integrity. Select,insert-based applications can basically use this engine to create tables. Commonly used for high-read application scenario databases, it supports three different types of storage structures: static, dynamic and compressed.

InnoDB provides transaction security with commit, rollback, and crash resilience, supports automatic column growth, and supports foreign key constraints. Compared to the MyISAM engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes.

Now that you've learned about the two storage engines commonly used in MySQL, let's take a look at how data is backed up and restored in both engines.

two

1) MyISAM data backup:

Because MyISAM is saved as a file, there are many methods that can be used during backup, and most web hosting providers and INTERNET platform providers only allow the use of MyISAM format, so it is particularly important to master MyISAM data backup.

Method 1: file copy:

In order to maintain the consistency of the data backup, we can LOCK TABLES the related tables and FLUSH TABLES the tables. Of course, you only need to restrict write operations to ensure that other operations can still query the table when copying data, while FLUSH TABLES is used to ensure that all active index pages are written to the hard disk before starting the backup.

Standard process: lock the table, refresh the table to disk, copy files, unlock.

Method 2:SQL statement backup:

SELECT INTO... OUTFILE or BACKUP TABLE can back up tables at the SQL level. It is important to note that if there are duplicate files in these two methods, it is best to remove them first. In addition, BACKUP TABLE backup needs to pay attention to the permissions of the output directory, the change method is to back up MYD and frm files, not the index.

Method 3: mysqlhotcopy backup:

Mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to quickly back up the database, but it can only be run on the same machine as the database directory and is only used to back up MyISAM.

Shell > mysqlhotcopy db_name [/ path/to/new_directory]

Shell > mysqlhotcopy db_name_1... Db_name_n / path/to/new_directory

Method 4: mysqldump backup:

Mysqldump can back up both table structure and data, as well as a single table, a single library, or all libraries, and the output is in SQL statement files or other database-compatible formats. In the previous article, we introduced Mysqldump in more detail, so I won't repeat it in this article.

Shell > mysqldump [options] db_name [tables]

Shell > mysqldump [options]-database DB1 [DB2 DB3...]

Shell > mysqldump [options]-- all-database

Method 5: cold backup:

The method of cold backup is simple and rough, copying all table files when the MySQL server is out of service.

three

2) MyISAM data backup and recovery:

There are corresponding recovery methods for different backup methods:

If it is a mysqldump backup, the recovery method is mysql-u root < backup file name

If it is a mysqlhotcopy or file cold / hot copy backup, the recovery method is to stop the MySQL service and overwrite the existing file with the backup file

If it is a BACKUP TABLE backup, use restore table to restore

If it is a SELECT INTO... OUTFILE backup, use the load data restore data or mysqlimport command.

four

3) InnoDB data backup:

MyISAM does not support transactions and foreign keys, which makes MySQL users often face some challenges, so, of course, when InnoDB supports transactions and foreign keys, even if it is slow, it is still popular with MySQL users.

Method 1:mysqldump:

Does it look familiar? yes, one of the ways to back up MyISAM data above is it. Mysqldump can also provide non-physical online logical hot backup of InnoDB in a manner similar to MyISAM.

Method 2:copy file:

InnoDB stores data and meta-information in ibdata*, * .ibd, * .frm, * .ib_logfile* and other files, so backing up these files is equivalent to backing up InnoDB data.

Method 3:select into:

It is the same as MyISAM.

Method 4: business tools:

There are many commercial tools available for InnoDB data backup, such as InnoDB Hotbackup, an online backup tool that backs up the InnoDB database while the InnoDB database is running, and ibbackup, which backs up the data content pointed to by the online my.cnf to the data directory pointed to by the my.backup.cnf.

4) InnoDB data backup and recovery:

Before using specific recovery methods, there are two general methods for InnoDB data backup and recovery, namely, the automatic log recovery function of InnoDB, that is, restarting the mysql service and the "omnipotent law"-restarting the computer.

If it is a mysqldump full backup, restore the full backup first, and then restore the incremental log backup after the full backup.

If the table is backed up by select into, it is restored by load data or mysqlimport.

If it is copy file, stop the MySQL service, overwrite the current file from the backup file, and perform an incremental log backup since the last full backup.

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.