MYSQL Tutorial: back up database _ MySQL

MYSQL Tutorial: regular backup of the backup database can greatly reduce the loss caused by database crashes.

There are two ways to back up data in MySQL. one is to use the mysqldump program, and the other is to use the mysqlhotcopy, cp, tar, cpio, and other packaging programs to directly copy database files. The mysqldump program backs up the database slowly, but the text files it generates are easy to transplant. Using mysqlhotcopy and other programs to back up data is fast, because it directly performs operations on system files and requires manual coordination of database data backup consistency.

This article by web teaching Network (http://www.webjx.com) finishing release! Please indicate the source for reprinting. thank you!

Using mysqldump to back up a database is actually dumping the database into a series of create table and INSERT statements, through which we can regenerate the database. Use mysqldump as follows:

% Mysqldump -- opt testdb | gzip>/data/backup/testdb. bak
# -- Opt option will optimize the dump process, the generated backup file will be a little smaller, and the subsequent pipeline operations will compress the data
% Mysqldump -- opt testdb mytable1, mytable2 | gzip>/data/backup/testdb_mytable.bak
# Only the specified data table can be exported after the database name. Multiple Data tables can be separated by commas (,).

The -- opt option can also activate the -- add-drop-table option, which will add a drop table if exists statement before each create table in the backup file. This allows you to easily update data tables without the "data table already exists" error.

You can use the mysqldump command to directly transfer the database to another server without generating backup files. You can regularly update the remote database after repeated execution.

% Mysqladmin-h remote_host create testdb
% Mysqldump -- opt testdb | mysql-h remote_host testdb
In addition, you can remotely call programs on the server through ssh, such:
% Ssh remote_host mysqladmin create testdb
% Mysqldump -- opt testdb | ssh remote_host mysql testdb
You can directly copy system files to back up the database. During backup, make sure that no one modifies the database. To do this, it is best to shut down the server. If the data table cannot be closed, try to lock the data table with the read-only side. The following are some examples:

% Cp-r db/backup/db # back up the database to the/backup/db Directory
% Cp table_name. */backup/db # back up only the table_name data table
% Scp-r db remotehot:/usr/local/mysql/data # use scp to directly copy the database to a remote server

When directly copying a database to a remote host, note that the two machines must have the same hardware structure or copy all the copied data tables as portable data tables.

Mysqlhotcopy is a Perl DBI script that backs up a database without shutting down the server. it has the following advantages:

It directly copies files, so it is faster than mysqldump.

Data locking can be automatically completed without shutting down the server during backup.

Refresh the log so that the checkpoints of backup files and log files can be synchronized.

The following is an example of this tool:

% Mysqlhotcopy db/bakcup/# back up the db database to the backup/db Directory. a db directory is automatically created.
Use the backup table statement to back up data. this statement was first used in MySQL 3.23.25 and is only applicable to MyISAM data tables. The usage is as follows:

Mysql> backup table mytable TO '/backup/db'; # back up the mytable data table to the/backup/db directory.
To execute this statement, you must have the FILE and SELECT permissions for those tables, and the backup directory must also be writable by the server. When this statement is executed, the data in the memory is first written to the disk, and then the data of each data table is. frm (table structure definition file ),. copy the MYD (data) file from the data directory to the backup directory. It does not copy the. MYI (index) file because it can be rebuilt with the other two files. When the backup table statement is used to back up data tables, the data tables are locked in sequence. when multiple data tables are backed up at the same time, the data tables may be modified. Therefore, when BACKUP 0 is complete, the data in the backup file may be different from the data in the current data table. to eliminate this difference, we can lock the data table in read-only mode and unlock it after the backup is complete. For example:

Mysql> lock tables tb1 READ, tb2 READ;
Mysql> backup table tb1, tb2 TO 'backup/db ';
Mysql> unlock tables;
Data tables backed up using the backup table statement can be restore table to be reloaded to the server.

InnoDB and BDB databases can also be backed up using mysqldump and direct copy. When using the direct copy method, you must copy all the files that comprise the InnoDB and BDB databases, for example, InnoDB. frm files, log files, and tablespace configuration files; BDB data files and log files.

Using the image mechanism for backup, we can use the slave stop statement to suspend the image of the SLAVE server and create a backup using the copy method or other tools on the SLAVE server. After the backup is complete, use slave start to restart the image and synchronize the image from the server to the master server again to receive the modifications made by the master server during the backup.

There is no command to rename the database in MySQL, but we can use mysqldump to dump the database, create a new empty database, and load the dump file to the new database, in this way, the database is renamed. For example:

% Mysqldump old_db> db. SQL # dump db database data
% Mysqladmin create new_db # create an empty database
% Mysql new_db <db. SQL # load the database data to the new database
% Mysqladmin drop old_db # delete old database
A simpler way to rename a database is to directly modify the database directory name, but this method is not applicable to InnoDB and BDB databases. Note: After renaming, you must update the relevant data table information in the permission table. you must execute the following statement:

Mysql> UPDATE db SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE tables_priv SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE columns_priv SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE host SET db = 'new _ db' WHERE db = 'old _ db ';