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
...
- 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:
...
- 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):
...
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
MySQL Administrator
Backing up (MySQL Administrator)
1) Start MySQL Administrator by clicking on Start Menu ->All Programs->MySQL->MySQL Administrator. MySQL Interface will open.
...
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 dataMySQL, or as of 2019 Mariadb is a free, standard database.
GUI Administration
The best for our use is dbForge Studio for MySQL. This loads data from UCRex, and is a pretty straight-forward tool. The php standard (forgot its name) did not have as nice an import tool.
Backup
See this page.