How to Backup and Restore a MySQL Server

Online Backup, Data Recovery and Business Continuity Services

How to Backup and Restore MySQL Database Server

This page will describe in detail how to configure the online backup manager to backup and restore a MySQL database server.

Requirements

i. The online backup manager must be installed onto the computer running MySQL server.

ii. Data from MySQL server will be backed up to a temporary directory before they are sent to Offsite

Backup Server. Please make sure you have sufficient space on your computer to store these data when you run the backup job.

iii. There must be a MySQL account can be used to connect from localhost.

Add two new MySQL accounts for Backup Manager mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' -> IDENTIFIED BY 'some_pass'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost.localdomain' -> IDENTIFIED BY 'some_pass'; mysql> FLUSH PRIVILEGES;

They are superuser accounts with full privileges to do anything with a password of some_pass.

Overview

The online backup manager will backup your MySQL server by taking the following steps:

i. Run all Pre-Commands of this backup set

ii. All database(s) (either local or external) selected are backed up to a temporary directory specified in its backup set

iii. Run all Post-Commands of this backup set

iv. Upload all backup files from the temporary directory to an Offsite Backup Server

v. Remove temporary files from the temporary directory if [Setting] -> [Temporary Directory for storing backup files] is enabled

How to backup a My SQL Server

Please follow the instructions below to backup Windows System State using the online backup manager:

i. Open the online backup manager

Right click the online backup manager icon available in the system tray and choose [Open]

ii. From the Menu, Choose [Backup Set] -> [New] -> [MySQL Backup Set] Create a backup set

a. From the Menu, Choose [Backup Set] -> [New] -> [MySQL Backup Set]

b. Enter a name for your backup set

c. Enter the root password, the MySQL server TCP/IP port number and the path to MySQL backup utility (mysqldump)

d. Select the database(s) to be backup

e. Enter a temporary directory for storing the backup files before they are sent to an Offsite Backup Server, e.g. C:\Backup\MySQL

f. Set the backup schedule for Database Backup

g. Set the encryption algorithm, encryption mode and encrypting key for this backup set

(Hint: For maximum security, please select AES (Advanced Encryption Standard) Algorithm, CBC (Cipher Block Chaining) mode and use an encrypting key with more than 8 characters.)

a. Select the backup set you want to run on the left panel and press the [Start Backup] button (►)

b. Select [Off-site Backup Server] to start backing up your files to an Offsite Backup Server and press the [OK] button

c. You should get something similar to the screen shot below.

How to backup MySQL server on Linux (command line mode)

If you want to setup online backup manager to backup MySQL server running on Linux using command line mode, please do the followings:

i. Create a backup account on the online backup server

ii. Logon to the web interface of OBS using the backup account created in the previous step (doesn’t matter if it is not from the Linux server running the MySQL server to be backed up)

iii. Create a new backup set by pressing the [Backup Set] -> [Add] button

iv. Select the [Backup Set] -> [Type] -> [MySQL Database Server] radio button and press the

[Update] button (which can be found at the bottom of the page)

v. Setup all [Backup Set] -> [Database Backup Setting]

MySQL Username (e.g root)
A MySQL user account that has backup access to the databases to be backed up (e.g. root). Please refer to the [Requirements] section for details.

MySQL Password
Password of the MySQL user account being used Host IP address / Hostname of the MySQL Server MySQL Connection

TCP/IP Port
TCP/IP port used to access the MySQL Server (default:3306)

Path to MySQL backup utility (mysqldump)
Full path to where mysqldump can be found (e.g. /usr/bin/mysqldump)

Temporary Spooling Directory
A temporary directory to be used to store all MySQL database dump files before they are uploaded to the backup server

Enable Delete Temp. File
Whether to delete the temporary MySQL database dump files after they are uploaded to the backup server

vi. Setup the [Backup Set] -> [Backup Source] setting

􀂾 Add an “MySQL” entry to the [Backup Source] if you want to backup all databases under this MySQL Server

􀂾 Add two entries, “MySQL/database1” and “MySQL/database2”, to the [Backup Source] if you want to backup both “database1” and “database2” under this MySQL Server (Please use “\” instead of “/” if the MySQL server to be backed up is running on Windows instead of Linux)

vii. Setup the [Backup Set] -> [Backup Schedule] by pressing the [Add] link next the the “Backup Schedule” sub-title

viii. Install the online backup manager onto the Linux server running MySQL server (Please refer to the [Installation of the online backup manager] section for details. The command line mode installation instructions are available on the web interface)

ix. Completed

If you have started up the online backup manager backup scheduler in the previous step, selected databases will be backed up automatically at scheduled time.

How to restore MySQL server

Please follow the instructions below to restore MySQL server from an Offsite Backup Server.

i. Download the database backup files to be restored from the Offsite Backup Server.

ii. Restore the database named [db_name] from the database backup file [db_name.sql]:

a. Connect to the MySQL server (Windows) C:\> mysql (Linux) [root@server ~]# mysql

b. Create the database to be restored mysql> CREATE DATABASE IF NOT EXISTS db_name

c.Restore the database backup file back into the MySQL server mysql> use db_name ; mysql> source db_name.sql ; If db_name.sql is not located in the current directory, please specify the full path to the db_name.sql file in the command above.

iii. Repeat the same procedure for each database to be restored to the MySQL Server.

iv. Completed

Online Backup for: