How do I backup and restore my MySQL database?  Print Introduction: This manual describes the backup and restore processes for MySQL databases. You are assumed to have: MySQL Administrator client application. The configuration details for your MySQL database. How to backup your MySQL database Keeping a back up of your database is one of the most important tasks you should be performing. Data loss to anyone can be catastrophic and the utmost care should be taken to ensure you have an up-to-date backup. There are various methods of backing up data - this manual describes only one such method. If you are not familiar with MySQL, or performing backups, please ensure you are using data that is not important to you, until you are comfortable about performing these backup and restore operations. First, you need to have a copy of MySQL Administrator – this is an application developed by the producers of MySQL. You can find more information about it and a download link at www.mysql.com/products/administrator/ Once you have downloaded, installed and started the application, the following screen will be displayed, seen in Fig. A. Fig. A Now you need to define your connection to the database server. Click the little icon marked with a red dot to define a new connection and to get to the next screen (Fig. B). Fig. B - Defining the database connection The important details you need to fill in here are as follows: Connection – A friendly name for the connection. Username – The username of your database owner as chosen during your MySQL setup. Password – The password of your DBO as chosen during your MySQL setup. Hostname – The IP address of the MySQL server you have been allocated. Once you have completed this information, click Apply changes, then Close to save these changes. Returning to your original screen you now see everything except the password has been filled in, type your password again and click Ok. Fig. C – Our defined connection Now you’re all set up to prepare your backup. Select the option on the left entitled Backup. This will open up the Backup Project configuration screen, which allows us to define the properties for our backup. Select the option at the bottom of the screen to create a New Project. Fig. D – Creating a new Backup Project Now you need to do the following: In the project name field put a friendly name to remind you of the purpose of this backup. On the left hand pane, select your database and click the little arrow to add it to the backup task. This will default to including all the tables in your database. Select the Advanced Options tab. Select the option to perform a normal backup. You will not be able to perform a lock all tables backup due to restrictions in place on the MySQL servers. Select the Backup Project tab again (at the top) and choose the option to Save Project. What you’ve done is to define a backup project for MySQL Administrator. This backup project will backup all the tables contained within your database to an SQL file, on your own local pc. If you so wish, you can also schedule this backup to take place automatically. That however, is outside the scope of this document. Look at the Schedule Task tab whilst defining your connection for further information on this. To recap your screen should now look similar to this: Fig. E - Our saved backup project Now test your backup procedure. Click the Execute Backup Now button to put your project to work. A save dialog will popup asking you where on your computer, you would like to save the SQL backup file. Keeping regular backups of your SQL database will help protect you against the worst when it comes to data loss! How to restore your MySQL database Step through the following simple restoration of the data you’ve just backed up. Note: You should be working with data that is not important, until you are comfortable about doing backups and restores. Select the option on the left of the MySQL Administrator entitled Restore (see Fig. F) Fig. F - Restoring a backup Now you should follow these steps: Select the option to Open Backup File. Browse to the previously saved SQL file of your database. Click the option to Start Restore. Note: For the purposes of this manual, we dropped our test tables between performing the backup and restore to demonstrate how a full restore works. If you still have your tables, the restore will complain about the tables already existing. This behaviour can be changed by choosing the option to Add Drop Statements when configuring your backup project.