Backup and copy MySQL database

mysql| Backup | data | Database It is important to back up the database when the table is lost and destroyed. If the system crashes, you can restore the table to the state of the crash and try not to lose data. Similarly, users who mistakenly send a drop DATABASE or drop TABLE command may request data recovery from you. Sometimes this is a breach caused by a MySQL administrator who tries to destroy a table file directly by using an editor such as VI or Emacs. It must have been a bad thing for the table.
The two main ways to back up a database are to use the Mysqldump program or directly copy the database files (such as C p, tar, or C P i o). Each method has its own advantages and disadvantages:
The mysqldump operates in conjunction with the MySQL server. The direct copy method is detached from the server, so steps must be taken to ensure that no client is modifying the tables while copying. This problem is the same as using a file system backup to back up a database: If a database table is updated at a file system backup, the table files that are backed up are in an inconsistent state and have no meaning to restore the table in the future. The difference between a file system backup and a direct copy file is that for the latter, you have the right to control the progress of the backup, so you can take steps to ensure that the server keeps the table in a static state.
Mysqldump is slower than direct copy technology.
Mysqldump produces text files on machines that can be ported to other machines, even with different hardware structures. Direct copy files cannot be ported to other machines unless the tables to be copied use the MyISAM storage format. ISAM tables can only be copied between machines that have the same hardware structure. For example, copying a file from an S PARC Solaris machine to an Intel Solaris machine (or vice versa) is not feasible. The MyISAM table storage format introduced by MySQL3.23 can solve this problem because the format is independent of the machine. Therefore, if the following two conditions are met, the direct copy file can be ported to a machine with a different hardware structure: The MySQL3.23 version must also be run on another machine, and the file must be represented as a MyISAM table, not a ISAM table.
Regardless of which backup method you choose, there are certain principles that you must adhere to to ensure that you get the best results when you need to restore your database content:
Perform backups on a regular basis. Set a schedule and stick with it.
Tell the server to run the update log. The update log is helpful when you need to restore a collapsed database. After you use the backup file to restore the database to the backup time, you can rerun the changes after the backup by running the query in the update log. This operation will restore the table in the database and suck it up. J Stupid phlegm Nao Thorn t 谖 raise  low Tide Fu Ying-ying-ying-ying-A--Jue--Fu Xing on the Jain ⅲ?full dump), while the update log represents an incremental dump.
Use consistent and understandable naming patterns for backup files. Like b a C K up 1, backup2 and other names have no special meaning. When you need it to perform a recovery, you still have to waste time looking at the contents of the file. You'll find it helpful to use database names and take the time to construct a backup file name. For example:
% mysqldump samp_db>/usr/archives/mysql/samp_db. 1999-10-02
% mysqldump menagerie>/usr/archives/mysql/menagerie.1999-10-02
You may need to compress them after generating the backup files. After all, backup files are relatively large, so you may also need to terminate the backup files to prevent them from filling up the disk, similar to the terminating log file. You can terminate a backup file with the same technology:
Use a file system backup to back up your backup files. If you suffer a complete crash, not only destroys the data directory but also destroys the disk drive that contains the database backup, that will cause real trouble. You should also back up the update log.
Place the backup file on a different file system than your database. This reduces the likelihood that the file system containing the data dictionary will be filled up with the resulting backup file.
The technology to create a backup is also helpful for copying the database to another server. It is common to transfer a database to a server running on another host, but you can also transfer data to another server running on the same host. You might do this if you are running a server for a new version of MySQL and you want to test it with some real data on the production server. Another possibility is that you get a new machine and move all the databases to the new machine.

Backing up and copying databases with mysqldump

When you use the Mysqldump program to generate a database backup file, the default setting is that the contents of the file consist of the C R E at E TABLE statement, which creates the tables that are dumped and insert statements that contain the row data in the table. In other words, Mysqldump creates the output that can be used as input to MySQL in the future to rebuild the database.
You can dump the entire database into a separate text file by following the command:

The remainder of the file consists of more insert and create TABLE statements.
If you want to compress when you build a backup, you can substitute a command similar to the following:
% mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999.10.02.gz
If you have a very large database, the output file will also be extremely difficult to manage. If you like, you can dump the contents of these tables by naming a single table after the database name of the mysqldump command. This operation divides the dump file into smaller, more manageable files. The following example shows how to samp_db the
Some of the tables are dumped into a single file:
% mysqldump samp_db Student Score Event absence > Gradebook.sql
% mysqldump samp_db Member President > Hist-league.sql
You may want to use the--add-drop-table option if you are building a backup file and intend to use these backup files to periodically refresh the contents of another database. This option tells Mysqldump to write the drop TABLE IF EXISTS statement to the backup file. Then, when you remove the backup file and load it into the second database, the error message will not appear if the table already exists. If you are running a second database, you can use this technique to load it periodically with a copy of the data from the first database.
If you are dumping the database so that the database can be converted to another server, you do not need to create a backup file. You should ensure that the database exists on another host, and then use a pipe to allow MySQL to directly read the mysqldump output to dump the database. For example, if you want to copy the samp_db database from P i t _ v i per.snake.net to B o A. s n A k E. N e T, the operation is as follows:
% mysqladmin-h boa.snake.netcreate samp_db
% mysqldump samp_db | Mysql-h boa.snake.net samp_db
Later, if you want to refresh the database again in Boa.snake.net, you can skip the mysqladmin command, but add--add-drop-table to mysqldump to avoid errors about "table already exists":
% mysqldump--add-drop-table samp_db | Mysql-h boa-snake.net samp_db
Other options for mysqldump include several of the following:
The combination of--flush-log and--lock-tables helps to check the database. --lock-table locks All tables that are being dumped, and--flush-log closes and opens the update log file again. If a subsequent update log is being generated, the new update log will contain only queries that modify the database from the point of the backup. Then check for the backup
Checkpoint of the update log for the time (however, locking all tables is not good for client access during backup, if you have a client that needs to perform an update operation).
If you use--flush-logs to check for update log checkpoints for backup times, it is a good idea to dump the entire database. If you dump a single file, it is more difficult to synchronize the checkpoint of the update log with the backup file. In a recovery operation, you typically extract the contents of the update log on the basis of the total database (per-d a t a B a s e). There are no options for an update log that extracts a single table, so you must extract them yourself.
The default setting, Mysqldump reads the entire contents of the table into memory before writing. This is actually not necessary, in fact, if you really have a large table, this is almost a failure method. You can use the--quick option to tell Mysqldump to write each row (as long as it is retrieved). To further optimize the dump process, you can use--o p t
To replace the--Q UI C K. The OPT option opens other options that will speed up the dumping of data and the reading of data.
Because of the benefits of fast backups, making backups with--opt is the most common approach. Beware, however, that the O-P option has a price:--opt is optimized for your backup process, not for access by other clients to the database. The--opt option prevents anyone from updating any tables that are locked for dumping. You will easily find that in a regular
Regulatory database access to this point in the effort. Try to run a backup during the day when the database is usually busiest. It's not going to take too much time.
The opposite option to the--opt function is-d e l a y e D. This option causes Mysqldump to write the insert D E L ayed statement rather than the INSERT statement. If you load a data file into another database and want the action to have minimal impact on other queries that might be occurring in the database, then--
D e l A Y e D will help to achieve this goal.
The--compress option helps you copy the database to another machine because it reduces the number of bytes in the network transport. As an example, note that in order for the program to communicate with the server on the remote host (rather than with the local host), the--compress option is given:
% mysqldump--opt samp_db | Mysql--compress-h boa.snake.net samp_db
Mysqldump has many options, please refer to Appendix E for more information.

Using direct Copy database backup and copy methods

Another way to back up a database or table without mysqldump is to copy the table files directly. Typically, utilities such as C P, tar, or cpio can be used. The example in this section uses the C p.
When using the direct copy backup (direct-copy Backup) method, you must make sure that the tables are not used. If the server is modifying a table while it is being copied, the copy is invalid.
The best way to ensure copy integrity is to shut down the server, copy the files, and then restart the server. If you do not want to shut down the server, refer to chapter 13th for an introduction to locking the server when performing a table checkpoint. If the server is running, the same constraints apply to the copy file, and you should use the same locking protocol to keep the server in a static state.
Assuming the server is down or the table you want to copy is locked, the following example shows how to back up the entire samp_db database to a backup directory (DataDir represents the server's data directory):
% CD DataDir
% cp-r samp_db/usr/archive/mysql A single table can be copied as follows:
% CD datadir/samp_db
% CD member.*/usr/archive/mysql/samp_db
% CD score.*/usr/archive/mysql/samp_db
...
When the backup is complete, you can restart the server (if it has been shut down) or release the lock imposed on the table (if the server is running).
To copy a database from one machine to another using a direct copy file, simply copy the files to the appropriate database on the other server host computer. Make sure that these files have the same hardware structure for the MyISAM table or both machines. Otherwise, the tables would appear to have strange content on the second host. You should also ensure that paragraph
The servers for the two hosts do not access them when you install the tables.

Copy Database

The term "copy" simply means something like "copy a database to another server," or the meaning of the effective update of the secondary database (live updating) that contains changes in the contents of the primary database. If you want to simply copy the database to another server, you can use the commands that you discussed earlier. Since the MySQL3.23 version, support for replication based on active updates has begun to appear. But its function is still immature, therefore, in this aspect I have nothing to discuss, if interested, you can pay attention to the current new version, to see what new development features.