MySQL Backup

The MySQL software comes with additional tools for backing up and restoring databases. As of 2011-12, the administrative tool is called "MySQL Workbench," whereas the earlier one was called "MySQL Administrator." However, "MySQL Workbench" cannot schedule backups.

Batch file, for scheduling backups

Backing Up

• Create a batch file (like this one: mysql_batch.bat) that runs the backup-to-file command

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" --user=root --password= --result-file="C:\matlab\spm8\toolbox\CSPM\database\DBbackup_%DATE%.sql" --all-databases

• Run the batch file once to make sure it creates a backup file
• Schedule it using the Windows "at" program: open a Command Prompt ("cmd"), and enter time time of day, days to repeat, and full path to the batch file:

C:\>at 02:00 /every:T,W,Th,F,S "C:\matlab\spm8\CSPM\database\mysql-backup.bat"

• Type "at" at the command prompt to verify this is scheduled.

Restoring

• You can restore using MySQL Workbench
• From the command prompt, you can restore to a new database using the following (adjusting for the correct MySQL and backup file locations):

C:\>"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql" --user=root --password= < C:\matlab\spm8\toolbox\CSPM\database\DBbackup_14-Feb-12.sql

MySQL Administrator

Backing up (MySQL Administrator)

1) Start MySQL Administrator by clicking on Start Menu ->All Programs->MySQL->MySQL Administrator. MySQL Interface will open.

2) Enter a name for Stored Connection,  enter localhost for Server Host and 3306 for Port. Enter root for Username and password if there is any, click OK. MySQL Administrator window will open, which contains list of varius operation on the left side Server Information, Server Control, Startup Variables ...

3)Choose Backup from that list and create new backup project by clicking on New Project. Enter project name, choose databases from the Schemata by selecting database and clicking on the right arrow. Any database that is in the Backup Content list will be copied.

4) Click on Execute Backup Now and specify the directory to store te backup file. You can save the created project by clicking on Save Project, the saved project will appear in the Backup Projects list located at the left side of the main window.

Note: The Saved Backup file will have timestamp appended to its name therefore every time executing backup will create a new file.

Schedule Backup

1)To Schedule Backup we need to create a new project or use existing project. The program requirs to store the password. To do that go to Tools menu and choose Options. Select General Options category, check Stor password  and select from Password Storage Method to be Obsecured. Click Apply and Close the window.

2) Select a project from Backup Projects list and go to Schedule  tab and check the box Schedule this backup project.

3) Specify the Target folder to store the backup files, file name as well as days and time . Save the project.

Restoring (MySQL Administrator)

1)To restore the database chose Restore from the list.

2)Click on Open Backup file and select the backup file.

3)You may want to check the box Create Databases if they don't exist, and click Start Restore  to restore the data.

MySQL Workbench

Backing Up

• Start MySQL Workbench (Start Menu ->All Programs->MySQL->MySQL Workbench)
• Under Server Administration, select the server instance named something like "mysqld@localhost" (otherwise see Installing MySQL).
• Click "Manage Import/Export"

Restoring

• On a new computer, install MySQL (see Installing MySQL).
• Start MySQL Workbench (Start Menu ->All Programs->MySQL->MySQL Workbench)
• Under Server Administration, select the server instance named something like "mysqld@localhost" (otherwise see Installing MySQL).
• Click "Manage Import/Export"

• Click "Data Import/Restore" in the left plane
• Select file, then "Start Import" - there may be some messages that look like errors.

• To get back, go View => Home
• To verify import, from home page under "SQL Development," select "Edit Table Data"
• Select a stored connection (localhost is fine), click next
• Under Schema, select "sidata"
• Under Table, select a table you are familiar with (e.g., subject).

• Click Finish, and the table will open up for you to browse