How to back up a MySQL database

The following articles mainly introduce the actual operation methods of MySQL database BACKUP. The MySQL BACKUP methods I have used include mysqldump, mysqlhotcopy, BACKUP TABLE, SELECT INTO, and OUTFILE, you can also directly copy data files and related configuration files.

MyISAM

Tables are saved as files, so they are relatively easy to back up. The methods mentioned above can be used. All Innodb tables are stored in the same data file ibdata1

It may also be multiple files or independent tablespace files), which is relatively difficult to back up. The free solution can be copying data files, backing up binlog, or using

Mysqldump

1. mysqldump backup

Mysqldump adopts the SQL-level MySQL backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method.

Example: mysqldump-uroot-p database table>/home/jobs/back. SQL

Mysqldump can also be used for Incremental backup. Many mysqldump-related parameters are available on the Internet, so I will not repeat them here.

2. mysqlhotcopy backup

Mysqlhotcopy is a PERL program. It uses lock tables and FLUSH

TABLES and cp or scp

To quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database files include data table definition files, data files, and index files.

Mysqlhotcopy can only be used to back up MyISAM and can only run on Unix-like and NetWare systems.

Mysqlhotcopy supports copying multiple databases at a time and regular expression.

Example: root #/usr/local/mysql/bin/mysqlhotcopy-h = localhost-u = root

-P = 123456 database/tmp (copy the database directory to/tmp

Under) root #/usr/local/mysql/bin/mysqlhotcopy-h = localhost-u = root-p = 123456

Db_name_1... db_name_n/tmproot #/usr/local/mysql/bin/mysqlhotcopy

-H = localhost-u = root-p = 123456 db_name./regex/

For more detailed usage of/tmp, see the manual or call the following command to view the help of mysqlhotcopy:

Perldoc/usr/local/mysql/bin/mysqlhotcopy Note: To use mysqlhotcopy, you must have

SELECT and RELOAD (to execute flush tables) permissions, and must be able to read the datadir/db_name directory.

Restoring mysqlhotcopy MySQL back up the entire database directory. You can directly copy it to mysqld during use.

The specified datadir directory (here it is/usr/local/mysql/data/). Pay attention to the permission issues, for example: root # cp

-Rf db_name/usr/local/mysql/data/root # chown-R nobody: nobody

/Usr/local/mysql/data/(change the owner of the db_name directory to the mysqld running user)

3. SQL syntax backup

3.1 backup table syntax and mysqlhotcopy

The working principle is almost the same. They all lock tables and then copy data files. It can achieve online backup, but the effect is not ideal, so it is not recommended. It only copies table structure files and data files, and does not copy index files at the same time.

So the recovery is slow. Example: back table tbl_name TO '/tmp/db_name/'; note that FILE is required

Permission to execute this SQL statement, and the directory/tmp/db_name/must be writable by mysqld users. exported files cannot overwrite existing files to avoid security issues.

Run the restore table statement to RESTORE the data TABLE. Example: restore table from '/tmp/db_name/'; the permission requirements are similar to described above.

3.2 select into outfile is to export data to a common text file. You can customize the field interval to process the data conveniently. Example:

Select into outfile '/tmp/db_name/tbl_name.txt' FROM tbl_name; note that

FILE Permission to execute this SQL statement, and the FILE/tmp/db_name/tbl_name.txt must be mysqld

Users can write and exported files cannot overwrite existing files to avoid security issues.

If you use the select into outfile method to back up a file, you can run the load data infile statement to restore the DATA table. Example: LOAD

Data infile '/tmp/db_name/tbl_name.txt' INTO TABLE

Tbl_name; permission requirements are similar to described above. Before you import data, the data table already exists. If you are worried that the data will be duplicated, you can add REPLACE

Keyword to replace existing records or IGNORE them with the IGNORE keyword.

4. Enable binlog)

The binlog method is more flexible and labor-saving, and supports incremental MySQL backup.

Mysqld must be restarted when binlog is enabled. First, close mysqld, open my. cnf, and add the following lines:

Server-id = 1

Log-bin = binlog

Log-bin-index = binlog. index

Start mysqld. Binlog.000001 and binlog. index are generated during running. The preceding file is mysqld.

Record all data update operations, and subsequent files are indexes of all binlogs, which cannot be easily deleted. For information about binlog, see the manual.

When a backup is required, run the SQL statement to terminate the current binlog.

In this way, you can directly back up the file, so that you can achieve the purpose of Incremental Backup: flush logs; if it is a backup copy system slave server, you should also back up

Master.info and relay-log.info files.

The binlog file backed up can be viewed using mysqlbinlog, a tool provided by MySQL, for example:

/Usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001 this tool allows you to display all

SQL statement, and time range can be limited, which is quite convenient. For details, please refer to the manual.

You can use a statement similar to the following for restoration:/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.20.01

| Mysql-uyejr-pyejr db_name runs the SQL statement output by mysqlbinlog directly as input.

If you have an idle machine, use this method for backup. Because the performance requirements of the Server Load balancer are not that high, the cost is low. with low costs, Incremental Backup can be achieved and the Data Query pressure can be shared. Why not?

5. Copy an object

Compared with the previous methods, the direct backup of data files in MySQL is the most direct, fast, and convenient. The disadvantage is that Incremental backup is basically not supported.

To ensure data consistency, run the following SQL statement: FLUSH TABLES WITH READ

LOCK, that is, refresh the data in the memory to the disk and LOCK the data table to ensure that no new data is written during the copy process. The data backed up in this way is also very simple, and is directly copied back.

In the original database directory.

Note: For an Innodb table, you also need to back up its log file, that is, the ib_logfile * file. Because when the Innodb table is corrupted, these log files can be recovered.

6. Backup Using rsync

Rsync can also be used as a synchronization tool for MySQL backup, but you need to configure the server and client

Example rsync-vzrtopg -- progress -- delete root@192.168.1.3: root/tmp/