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 login path for security and convenience. Each login 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 login path, do the following:

1. Use the MySQL Configuration Utility (mysql_config_editor) to define all the login paths that you need. For each login 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 login paths.

The login 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 login paths you have defined are valid.

2. Copy the .mylogin.cnf file to the DS-Client installation folder.

IMPORTANT: Always ensure that the .mylogin.cnf file in the DS-Client installation folder contains the login 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 login path and ensured that the appropriate .mylogin.cnf file is in the DS-Client installation folder, you can refer to the login path when specifying credentials for a MySQL database backup set in Linux DS-Client.

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

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

NOTE: You can access the Ask for database credentials screen in the New Backup Set Wizard when creating a new MySQL database backup set or in the Connection Options tab when modifying Backup Set Properties.

DS-Client will access the MySQL Server using the connection and authentication information of the login 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.

2. In the Select a MySQL Server to backup screen, you must specify the IP address of the server and the port number in the Path field, 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 will use “3306” as a default.

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

2. In the Select the source for this backup set screen, select Ask for database credentials.

When the credentials screen appears, you will be able to enter the port number that the MySQL Server is configured to use.

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

How to display the MySQL backup set type in the New Backup Set Wizard

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 may 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 mysql) 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 - 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: