Backup / restore of MySQL database servers

This article covers various backup/restore issues for MySQL database backup sets.

The following requirements must be met to perform backup / restore of MySQL database servers.

The user who performs MySQL backup / restore should have knowledge about the MySQL database structure.

• DS-Client backs up MySQL Server using the MySQL utilities ‘mysqldump’ and ‘mysql’. DS-Client’s mysqldump and mysql utilities must be compatible with the MySQL version running on the source machine (MySQL server).

The following requirements depend on the type of DS-Client you are using:

• Windows DS-Client: The mysql utility must be listed in the PATH environment variable of the DS-Client computer. (If you must edit this variable, stop and restart the DS-Client service after making the change.)

• Linux DS-Client: The mysql utility must be listed in the PATH environment variable of user ’root’ on the DS-Client computer.

• Mac DS-Client: The path to the mysql utility must be added to the following file on the DS-Client computer:

When using Linux DS-Client, you can specify the server credentials for a MySQL database backup set by referring to a logon path for security and convenience. Each logon path represents specific connection and authentication details, such as the host name, user name, and password.

Before specifying the server credentials for a MySQL database backup set in Linux DS-Client using a logon path, do the following:

1. Use the MySQL Configuration Utility (mysql_config_editor) to define all the logon paths that you need. For each logon path, you must define the following:

IMPORTANT: To avoid conflict with the ports that you specify in DS-Client, do not define ports for the logon paths.

The logon path information is stored in the encrypted .mylogin.cnf file. For more information, refer to MySQL documentation.

IMPORTANT: To enable DS-Client to access the MySQL Server successfully, you must ensure that the logon paths you have defined are valid.

IMPORTANT: Always ensure that the .mylogin.cnf file in the DS-Client installation folder contains the logon paths that are required by your MySQL database backup sets. Updates to the .mylogin.cnf file are not accessible to DS-Client unless the updated file is in the DS-Client installation folder.

After you have defined the logon path and ensured that the appropriate .mylogin.cnf file is in the DS-Client installation folder, you can refer to the logon path when specifying credentials for a MySQL database backup set in Linux DS-Client.

• To refer to a logon path, type the following text in the User Name box in the Ask for database credentials dialog box:

where <login path name> is the name that is specified for the logon path.

NOTE: You can access the Ask for database credentials dialog box in the New Backup Set Wizard when creating a new MySQL database backup set or on the Connection Options tab when modifying a backup set’s properties.

DS-Client will access the MySQL Server using the connection and authentication information of the logon path that you refer to.

To back up a MySQL Server that is using non-standard ports, you must specify the port number when creating the MySQL backup set.

• You must specify the port numbers when creating the MySQL backup set.

3. In the Select a MySQL Server to backup dialog box, you must specify the IP address of the server and the port number in the Path box, in the following format:

For example, if you want to connect to a MySQL instance at IP address 10.20.30.111 using the port number 3310, you would type the following line into the Path field:

NOTE: If you do not specify a port number, Windows DS-Client uses “3306” as a default.

• You can specify the port numbers when creating the MySQL backup set:

3. In the Select the source for this backup set dialog box, select Ask for database credentials.

4. Click Next. In the Ask for database credentials dialog box, you can configure the port number that the MySQL server uses.

• You can also change the port number by editing the backup set properties (Backup Set Properties > Connection Options > Ask for database credentials: Port Number).

If you do not see the MySQL backup set type option when you open the New Backup Set Wizard (Sets > New Backup Set), your DS-Client might not be configured to support it. Make sure the following requirements are met:

• The folder that contains the MySQL utilities (such as: mysqldump.exe and mysql.exe) is in the PATH environment variable of the DS-Client computer. To verify if the path is included, launch a command window on DS-Client computer, and in the command prompt, run the command ‘path’.

• The folder that contains the MySQL utilities (such as: mysqldump and mysql) is in the search PATH of the DS-Client. To verify, edit the /etc/init.d/dsclient and in the line PATH, make sure the folder is listed.

• The folder that contains the MySQL utilities (such as: mysqldump and mysqll) is in the search PATH of the DS-Client. The path to the mysql utility should be added to the following file on the Mac DS-Client computer:
/etc/rc.common.

You have the option to specify the path of the MySQL database dump app that you want to use (either when creating a new backup set or editing an existing one). If you do not specify a path when creating the backup set, DS-Client will pick one on its own and this will be displayed in the Backup Set Properties dialog box, on the Connection Options tab.

Backing up and restoring a MySQL cluster database is the same as standalone MySQL database backup and restore.

MySQL added the capability to backup stored routines from its release version 5.0.13.

To restore backed up stored routines, the user who performs the restore (MySQL database user) must have the following privileges for the MySQL database: