3.1.1 Collect Database Information

Before backing up a particular database server for the first time, gather some information and decide on some directory names, as outlined in the following table.

Table 3.1 Information Needed to Back Up a Database

Information to Gather

Where to Find It

How Used

Path to MySQL configuration file

Default system locations, hardcoded application default locations, or from --defaults-file option in mysqld startup script.

This is the preferred way to convey database configuration information to the mysqlbackup command, using the --defaults-file option. When connection and data layout information is available from the configuration file, you can skip most of the other choices listed below.

MySQL port

MySQL configuration file or mysqld startup script.

Used to connect to the database instance during backup operations. Specified via the --port option of mysqlbackup. --port is not needed if available from MySQL configuration file. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions.

Path to MySQL data directory

MySQL configuration file or mysqld startup script.

Used to retrieve files from the database instance during backup operations, and to copy files back to the database instance during restore operations. Automatically retrieved from database connection for hot and warm backups. Taken from MySQL configuration file for cold backups.

ID and password of privileged MySQL user

You record this during installation of your own databases, or get it from the DBA when backing up databases you do not own. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions. For cold backups, you log in as an administrative user.

Specified via the --password option of the mysqlbackup. Prompted from the terminal if the --password option is present without the password argument.

Path under which to store backup data

You choose this. See Section 3.1.3, “Designate a Location for Backup Data” for details.

By default, this directory must be empty for mysqlbackup to write data into it, to avoid overwriting old backups or mixing up data from different backups. Use the --with-timestamp option to automatically create a subdirectory with a unique name, when storing multiple sets of backup data under the same main directory.

Owner and permission information for backed-up files (for Linux, Unix, and OS X systems)

In the MySQL data directory.

If you do the backup using a different OS user ID or a different umask setting than applies to the original files, you might need to run commands such as chown and chmod on the backup data. See Section A.1, “Limitations of MySQL Enterprise Backup” for details.

Size of InnoDB redo log files

Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the --incremental-with-redo-log-only option.

Only needed if you perform incremental backups using the --incremental-with-redo-log-only option rather than the --incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.

Rate at which redo data is generated

Calculated from the values of the InnoDB logical sequence number at different points in time. Use the technique explained for the --incremental-with-redo-log-only option.

Only needed if you perform incremental backups using the --incremental-with-redo-log-only option rather than the --incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.