MySQL database backup (2)

mysql| Backup | data | database 1 using mysqldump to back up and copy databases

When you use the Mysqldumo program to generate a database backup file, the file contents contain the Create statement that creates the table being dumped and the INSERT statement that contains the row data in the table, by default. In other words, the output produced by Mysqldump can later be used as MySQL input to rebuild the database.

You can dump the entire database into a separate text file, as follows:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

The beginning of the output file looks like this:

# MySQL Dump 6.0
#
# Host:localhost database:samp_db
#---------------------------------------
# Server Version 3.23.2-alpha-log
#
# Table structure for table ' absence '
#
CREATE TABLE Absence (
student_id Int (a) unsigned DEFAULT ' 0 ' not NULL,
Date Date DEFAULT ' 0000-00-00 ' not NULL,
PRIMARY KEY (student_id,date)
);
#
# Dumping data for table ' absence '
#
INSERT into Absence VALUES (3, ' 1999-09-03 ');
INSERT into Absence VALUES (5, ' 1999-09-03 ');
INSERT into Absence VALUES (10, ' 1999-09-08 ');
......
The rest of the file consists of more insert and CREATE TABLE statements.

If you want to compress the backup, use a command similar to the following:

%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz

If you want a large database, the output file will be very large and may be difficult to manage. If you want, you can dump the file into smaller, more manageable files by listing individual table names after the mysqldump command line's database name. The following example shows how to dump some of the tables in the SAMP_DB database into separate files:

%mysqldump samp_db Student Score Event Absence >grapbook.sql
%mysqldump samp_db member President >hist-league.sql

You may want to use the--add-drop-table option if you generate backup files that are ready to be used to refresh the contents of another database on a regular basis. This tells the server to write the drop TABLE if exists statement to the backup file, and then when you take out the backup file and load it into the second database, you won't get an error if the table already exists.

If you dump a database so that you can transfer the database to another server, you don't even have to create a backup file. To ensure that the data inventory is another host, and then dump the database pipeline, so that MySQL can directly read the mysqldump output. For example: you want to copy the database from the host pit-viper.snake.net samp_db to Boa.snake.net, you can easily do this:

%mysqladmin-h boa.snake.net Create samp_db
%mysqldump samp_db | Mysql-h boa.snake.net samp_db

Later, if you want to refresh the database on Boa.snake.net again, skip the mysqladmin command, but add--add-drop-table to mysqldump to avoid the error that the table already exists:

%mysqldump--add-drop-table samp_db | mysql-h boa.snake.net samp_db

other useful options for mysqldump include:

The--flush-logs and--lock-tables combinations will help with your database checkpoint. --lock-tables Lock All the tables you're dumping, while--flush-logs closes and reopen the update log file, the new update log will include only queries that modify the database from the point of backup. This will set up your update log to check the bit backup time. (However, if you have customers who need to perform an update, locking all the tables for customer access during the backup is not a good thing.) ）

If you use--flush-logs to set up checkpoints to backup, it may be best to dump the entire database. If you dump a separate file, it's harder to sync the update log checkpoint with the backup file. During recovery, you typically extract the update log content on a database basis, and you have no option to extract updates for a single table, so you have to extract them yourself.

By default, mysqldump reads the entire contents of a table into memory before writing. This is usually really unnecessary, and actually if you have a big table, it's almost a failure. You can use the--quick option to tell Mysqldump to write each line as soon as it retrieves one line. To further optimize the dumping process, use--opt rather than--quick. The--OPT option turns on other options to accelerate the dumping of data and to read them back.

Implementing backups with--opt may be the most common method, because of the advantages of backup speed. However, to warn you that the--opt option does have a price,--opt optimizes your backup process, not other clients ' access to the database. The--opt option prevents anyone from updating any of the tables you are dumping by locking all tables at once. You can easily see the effect on general database access. When your database is used very frequently, it only adjusts backups once a day.

An option that has the opposite effect of--opt is--dedayed. This option causes Mysqldump to write the insert delayed statement instead of the INSERT statement. --delayed is helpful if you load a data file into another database and you want to minimize the impact of this operation on queries that might appear in the database.

The--compress option is helpful when you copy the database to another machine, because it reduces the number of bytes transmitted by the network. Here's an example of a program that--compress to communicate with a server on a remote host, rather than a program that connects to a local host:

%mysqldump--opt samp_db | mysql--compress-h boa.snake.net samp_db

Mysqldump has many options, as detailed in the MySQL reference manual.