MySQL backup


The Backup Manager offers backup and recovery service for MySQL databases on all of the supported operating systems.


The Backup Manager handles all MySQL versions starting from 5.0 on Windows, Mac and Linux (glibc 2.5 or higher required).


You can add multiple MySQL instances to your backup selection (if they are installed on the same machine).



Backup requirements



  • The Backup Manager must be installed on the MySQL server that you want to back up.

  • The MySQL server must be running at the time a backup session starts.

  • On Linux, glibc 2.5 or higher are required.


Types of backups


There are 2 types of MySQL backups in the Backup Manager. They are version-dependent.



  1. Cold backups (the MySQL server is stopped for some 30-60 seconds before a backup session starts; it's necessary that no third-party applications should be keeping the database forcibly open at that time).

  2. Hot backups (the MySQL server keeps functioning while a backup session is running).


Hot backups are handled with the help of Percona XtraBackup (an open-source backup utility for MySQL). Cold backups are performed using the native Volume Shadow Copy Service from Microsoft.































MySQL version Windows Mac Linux
MySQL 5.0 Hot backups Hot backups Hot backups
MySQL 5.1 Hot backups
MySQL 5.5 Cold backups
MySQL 5.6 Cold backups

MySQL backup and recovery through the command line


You can back up and restore data not just using the web interface but through the command line as well. This is done using the Client Tool, an executable file included into all Backup Manager installations (view primary Client Tool instruction).


Here is a sample instruction for MySQL. The current instruction is based on a Linux case. The same workflow can be used on Windows.


Backup instructions


Step 1


Start your terminal emulator and move to the Backup Manager installation directory (this is where ClientTool is located).


cd /opt/MXB/bin

Step 2


Configure access to the MySQL server that you want to back up.


./ClientTool control.mysqldb.add -name mysqldb -user root -password Aiuq78552v -server-port 3306

Here are the parameters to submit:



  • -name – assign a name to the MySQL server as it will appear in the Client Tool and Backup Manager. Latin letters and most punctuation marks are supported. No slashes.

  • -user – your username for access to the MySQL server (we recommend using the root)

  • -password – your password for access to the MySQL server

  • -server-port – the port number of the MySQL server. If in doubt, check the option file on your machine (my.cnf).


Step 3


Add the MySQL server to your backup selection.


./ClientTool control.selection.modify -datasource MySql -include mysqldb

You can make sure the MySQL server has been included into the backup selection using the control.selection.list command.


Backup selection modified (Client Tool response)


Step 4


Start the backup of the MySQL data source.


./ClientTool control.backup.start -datasource MySql

You can check the current status of the backup session using the control.session.list command.


Backup in progress (Client Tool response)


Recovery instructions


Step 1


Start your terminal emulator and move to the Backup Manager installation directory.


cd /opt/MXB/bin

Step 2


Initiate the recovery of your MySQL server.


./ClientTool control.restore.start -datasource MySql -selection mysqldb

Here are some options:



  • By default, the databases are restored to the original directory (in-place restore). To perform a restore to an intermediate directory, specify the -restore-to parameter, for example -restore-to /tmp.

  • You can specify the backup session that you want to restore (by default the most recent session is selected). This is done using the -time parameter – for example -time "2016-09-02 14:44:39".


You can check the current status of the restore session using the control.session.list command.


Restore completed (ClientTool response)


Step 3 (if applicable)


If you recovered MySQL to an intermediate directory, you'll need to copy the recovered files to the appropriate data directory for the recovered instance, for example /var/lib/mysql on Linux or C:\ProgramData\MySQL\MySQL Server 5.5\data on Windows.


Overwrite original files


Step 4 (if applicable)


When MySQL recovery is performed under the root user account, all of the files are normally recovered with their original permissions. If another account is used, the original permissions can get overwritten. In that case you'll need to set the user which is used for MySQL database management as the owner of the copied folder.


chown -R mysql:mysql /var/lib/mysql

Step 5


Restart the MySQL server to apply the changes.


/etc/init.d/mysqld restart

Important


To get details on a particular command (output structure, required arguments and optional arguments), enter the following string to your terminal emulator:


./ClientTool help -c <command>