Exportación

To illustrate the basic steps in making and using a backup, the following examples show how to do a full backup, examine the data files in the backup directory, and then restore the backup to correct an issue with corruption or lost data.

3.2.1 Backing Up an Entire MySQL Instance

In this example, we specify all required options on the command line for illustration purposes. After testing and standardizing the backup procedure, we could move some options to the MySQL configuration file. The options specify connection information for the database and the location to store the backup data. The final option backup specifies the type of operation, because mysqlbackup can perform several kinds of backup, restore, and pack/unpack operations.

For this example, we specify the final option as backup-and-apply-log. This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored. For backups of huge or busy databases, you might split up these stages to minimize load on the database server. That is, run mysqlbackup first with the backup option, transfer the backup to another server, then run mysqlbackup with the apply-log option to perform the final processing.

The output echoes all the parameters used by the backup operation, including several that are retrieved automatically using the database connection. The unique ID for this backup job is recorded in special tables that mysqlbackup creates inside the instance, allowing you to monitor long-running backups and view the results of previous backups. The final output section repeats the location of the backup data and provides the LSN values that you might use when you perform an incremental backup next time over the full backup that is just made.

$ mysqlbackup --port=13000 --protocol=tcp --user=root --password \ --backup-dir=/home/admin/backups backup-and-apply-log

MySQL Enterprise Backup version 3.7.0

Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

INFO: Starting with following command line ...

mysqlbackup --port=13000 --protocol=tcp --user=root --password --backup-dir=/home/admin/backups

...informational messages...

--- Server Repository Options:

--- datadir = /home/mysql/data/ innodb_data_home_dir = /home/mysql/data innodb_data_file_path = ibdata1:20M;ibdata2:20M:autoextend innodb_log_group_home_dir = /home/mysql/data innodb_log_files_in_group = 4 innodb_log_file_size = 20971520 --- Backup Config Options:

--- datadir = /home/admin/backups/datadir innodb_data_home_dir = /home/admin/backups/datadir innodb_data_file_path = ibdata1:20M;ibdata2:20M:autoextend innodb_log_group_home_dir = /home/admin/backups/datadir innodb_log_files_in_group = 4 innodb_log_file_size = 20971520

mysqlbackup: INFO: Unique generated backup id for this is 13071379168342780 ...output showing backup progress...

110604 0:51:59 mysqlbackup: INFO: Full backup completed!

mysqlbackup: INFO: Backup created in directory '/home/admin/backups'

--- Parameters Summary --- Start LSN : 36864 End LSN : 50335 ---

mysqlbackup completed OK!

Now the backup subdirectory is created under the backup-dir we specified. The directory name for each new backup is formed from the date and the clock time when the backup run was started, in the local time zone. The backup directory contains the backed-up ibdata files and ibbackup_logfile. Each subdirectory corresponds to a MySQL database, and contains copies of .frm, .MYD, .MYI, and similar files. For an example of the layout of such a backup directory, see Section C.1, “Sample Directory Structure for Full Backup”.

3.2.2 Verifying a Backup

To verify the backup, restore the backup data on a different server and run the MySQL daemon (mysqld) on the new data directory. Then you can execute SHOW statements to verify the database and table structure, and execute queries to verify the number of rows, latest updates, and so on.

This is the same general technique to use when you intend to put the backup data to some other use. For example, you might set up a replication slave by making a backup of the master server, or turn a backup into a new MySQL instance for running report queries.

Note

Always do verification against restored data, rather than running mysqld with datadir pointing to the backup directory. The SQL statements you use to verify the data change the underlying logical sequence number, which would interfere with using the backup directory for subsequent incremental backups.

If you did the backup with the backup-and-apply-log option as in the previous example, the backup data is fully consistent and ready to verify. If you only ran the first stage by using the backup option, run

Restoring a Database at its Original Location

mysqlbackup a second time with the apply-log option before doing this verification. (Typically, you run this second phase on the other server after transferring the backup data there, to minimize the load on the original database server.)

See Chapter 4, Recovering or Restoring a Database for the procedure to restore the database files on a different server.

Running the mysqld daemon on the restored data requires a valid configuration file, which you specify with the --defaults-file option of the mysqld command. You can reuse most of the settings from the original my.cnf file, combined with the backup-my.cnf file in the backup directory, which contains only the small subset of parameters required by mysqlbackup. Create a new configuration file by concatenating those two files into a new one, and use that configuration file on the server where you do the verification. Edit the resulting file to make sure the datadir parameter points to the right location on the verification server. directory. Edit the values for port, socket, and so on if you need to use different connection settings on the verification server.

3.2.3 Restoring a Database at its Original Location

To restore a MySQL instance from a backup:

• Shut down the database server using your usual technique, such as the command mysqladmin shutdown.

• Make sure the backup data is fully consistent, by either using the backup-and-apply-log option to perform the backup, or running mysqlbackup with the apply-log option after the initial backup.

• Use the mysqlbackup command with the copy-back option. This operation copies tables, indexes, metadata, and any other required files back to their original locations as defined by the original MySQL configuration file. For the different combinations of options that you can specify as part of this operation, see Section 5.1.1.3, “Restore an Existing Backup”.

$ mysqlbackup --defaults-file=path_to_my.cnf \ --datadir=path_to_data_directory \

--backup-dir=path_to_backup_directory copy-back

...many lines of output...

mysqlbackup: Finished copying backup files.

101208 16:48:13 mysqlbackup: mysqlbackup completed OK!

Now the original database directory is restored from the backup, and you can restart the database server.