Back up and copy MYSQL Databases

It is important to back up the database when the table is lost or destroyed. If the system crashes, you can restore the table to the State at the time of crash, and do not lose data as much as possible. Likewise, users who mistakenly send the drop database or drop table command may request data recovery from you. Sometimes this is caused by the MySQL administrator. The Administrator tries to use an editor like vi or emacs to directly edit the table files and destroy them. This is definitely a bad thing for the table.

Two main methods for backing up a database are to use the mysqldump program or directly copy database files, such as using c p, tar, or c p I o ). Each method has its own advantages and disadvantages:

The mysqldump and MySQL Server are used together for operations. The direct copy method is separated from the server. Therefore, measures must be taken to ensure that no client is modifying these tables during the copy process. This problem is the same as using file system backup to back up the database: if the database table is updated during file system backup, the table files to be backed up are in a different state, it is meaningless to restore the table in the future. The difference between file system backup and direct copy is that you have the right to control the backup progress for the latter, so you can take measures to ensure that the server keeps the table static.

Mysqldump is slower than direct copy.

Mysqldump generates text files that can be transplanted to other machines or even machines with different hardware structures. Directly copying files cannot be transplanted to other machines, unless the table to be copied uses the MyISAM storage format. An ISAM table can only be copied between machines with the same hardware structure. For example, copying files from s parc's Solaris machine to Intel's Solaris machine or vice versa. The MyISAM Table Storage Format introduced by MySQL3.23 solves this problem because it is independent from the machine. Therefore, if both of the following conditions are met, You can directly copy the file to a machine with different hardware structures: that is, the version of MySQL3.23 or later must be run on the other machine, and the file must be expressed as a MyISAM table instead of an ISAM table.

No matter which backup method you choose, there are some principles. You must stick to these principles to ensure the best results when you need to restore the database content:
Perform regular backup. Set a timetable and stick to it.

Tells the server to run the Update log. Update logs are helpful when you need to recover the database after the crash. After using the backup file to restore the database to the status at the time of backup, you can run the query in the Update log to re-run the backup and make changes. This operation restores the tables in the database to the state of crash. In the file system backup language, the database backup file indicates full dump), while the Update log indicates incremental dump.

Use consistent and understandable backup file naming patterns. Such names as B a c k up 1 and backup2 have no special meaning. When you need it to restore, You have to waste time viewing the content in the file. You will find that using the database name and taking the time to construct the backup file name is advantageous. For example:
% Mysqldump samp_db>/usr/archives/mysql/samp_db. 2017-10-02
% Mysqldump menagerie>/usr/archives/mysql/menagerie.1999-10-02

You may need to compress the backup files after they are generated. After all, the backup files are large, so you may need to terminate the backup files to avoid them filling up the disk, which is similar to the termination log file. You can use the same technology to terminate backup files:

Use File System Backup to back up your backup files. If you have suffered a complete crash, not only destroying the data directory but also destroying the disk drive that contains the database backup, it will cause real trouble. You should also back up the Update log.

Place the backup file on a different file system from your database. This will reduce the possibility that the file system containing the data dictionary will be filled with backup files generated.

The technology for creating backups is also helpful for copying databases 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. If you are running a server for a new version of MySQL and want to test it with some real data on the finished server, this may happen. Another possibility is that you get a new machine and want to move all the databases to the new machine.
Back up and copy databases with mysqldump
When the mysqldump program is used to generate a database backup file, the default setting is that the file content is composed of the c r e at e table statement, these statements create the dump table and the INSERT statement that contains the row data in the table. In other words, mysqldump is created in the future and can be used as an output for mysql input to recreate the database.

You can run the following command to dump the entire database to a separate text file:

The rest of the file consists of more INSERT and create table statements. To compress data when a backup is generated, replace it with a command similar to the following:
% Mysqldump samp_db | gzip>/usr/archives/mysql/samp_db.1999.10.02.gz

If you have a super large database, the output file will be extremely large and difficult to manage. If you like it, you can dump the contents of a single table after the Database Name of the mysqldump command. This operation divides the dump into smaller and more manageable files. The following example shows how to dump some tables in samp_db to a single file:

% Mysqldump samp_db student score event absence> gradebook. SQL
% Mysqldump samp_db member president> hist-league. SQL

If you are generating backup files and intend to use these backup files to regularly refresh the content of another database, you may need to use the -- add-drop-table option. This option tells mysqldump to write the drop table if exists statement to the backup file. Then, when you extract the backup file and load it to the second database, if the table already exists, no error message will appear. If you are running the second database, you can use this technology to regularly load it by copying data from the first database.

If you are dumping a database so that the database can be converted to another server, you do not need to create a backup file. Ensure that the database exists on another host, and then use a pipe to let mysql directly read the output result of mysqldump to dump the database. For example, if you want to copy the samp_db database from p I t _ v I per.snke.net to B o a. s n a k e. n e t, perform the following operations:

% Mysqladmin-h boa. snake. netcreate samp_db
% Mysqldump samp_db | mysql-h boa.snke.net samp_db

Later, if you want to refresh the database again in boa.snke.net, you can skip the mysqladmin command, but add -- add-drop-table to mysqldump, to avoid the error "table already exists:

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

Other options of mysqldump include:

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 re-opens the Update log file. If a subsequent update log is being generated, the new update log will only contain the database query modified from the backup point. Check the checkpoint of the update log for the backup time. However, locking all the tables is not good for client access during the backup, if you have a client to perform the update operation ).

If you use -- flush-logs to check the Update log checkpoint for the backup time, it is best to dump the entire database. If you dump a single file, it is difficult to synchronize the checkpoint of the update log with the backup file. In the restoration operation, you usually extract the updated log Content Based on the total database per-d a t a B a s e. There is no option for extracting update logs for a single table, so you must extract them yourself.

By default, mysqldump reads all the table content into the memory before writing. This is not actually necessary. In fact, if you have a large table, it 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, replace-o p t with-q ui c k. -- Opt options enable other options, which will speed up data dumping and reading back.

Due to the advantages of quick backup, using -- opt to perform backup becomes the most common method. However, be careful that the-o p t option has a price: -- opt optimizes your backup process, rather than accessing the database by other clients. The -- opt option prevents anyone from updating any table that is being dumped. You can easily find

The effort made in this regard during database access. Try to run a backup at the busiest time of the day. This does not take too much time.

The option opposite to -- opt is-d e l a y e d. This option causes mysqldump to write the insert d e l ayed statement instead of the INSERT statement. If you load a data file to another database and want to minimize the impact of this operation on other queries that may occur in the database, --d e l a y e d will help to achieve this goal.

The -- compress option helps copy the database to another machine because it reduces the number of bytes transmitted over the network. Here is an example. Note that the -- compress option is provided to allow the program to communicate with the server on the remote host instead of the local host:
% Mysqldump -- opt samp_db | mysql -- compress-h boa.snke.net samp_db

Mysqldump has many options. For more information, see Appendix E.
Using the Direct Copy database backup and copy method
Another way to back up databases or tables without using mysqldump is to directly copy table files. You can usually use utilities such as c p, tar, or cpio. The example in this section uses c p.

When using direct-copy backup), make sure that these tables are not used. If the server is modifying a table while copying it, the copy is invalid.

The best way to ensure copy integrity is to close the server, copy files, and restart the server. If you do not want to shut down the server, refer to Chapter 13th for details about locking the server when performing the table check. If the server is running, the same constraints apply to copying files. You should use the same locking protocol to keep the server static.

If the server is shut down or the table to be copied has been locked, the following example shows how to back up the entire samp_db database to the backup directory where DATADIR represents the data directory of the server ):
% 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 disabled), or release the lock applied on the table if the server remains running ).

To directly copy files to copy a database from one machine to another, you just need to copy these files to the corresponding database on another server host. Ensure that these files have the same hardware structure for the MyISAM table or the two machines. Otherwise, these tables seem strange on the second host. Make sure that the server on the second host does not access these tables when you install them.
Copy Database

The meaning of the term "copy" is simply like the meaning of "copying a database to another server", or updating live updating effectively in the database when the content of the primary database changes. If you want to simply copy the database to another server, you can use the commands discussed earlier. MySQL 3.23 and later versions have started to support replication based on effective updates. However, its functions are not yet mature, so I have nothing to discuss in this regard. If you are interested, you can pay attention to the current new version to see what new development functions are available.