35.10. Backing Up and Restoring a Database
If one of your databases contains important information, it should be backed up regularly in case of a disk failure or SQL mistake, which could cause data loss. It is also a good idea to create a backup before performing some potentially risky operation, such as changing the type of a field or running a complex SQL statement that modifies lots of records.
To use the module to make a backup, follow these steps:
1. | On the main page, click on the icon for the database that you want to back up. |
2. | |
3. | |
4. | To restrict the backup to only some records, deselect the All rows option for the Only backup rows matching where clause field and enter an SQL WHERE clause into the adjacent field, for example foo = “bar”. This only works if the clause is valid for all tables in the database, so in the example all tables would need to have a foo field. |
5. | If the Add drop table statements to backup? field is set to Yes, the backup will include SQL statements to delete existing tables of the same name when restoring. This means that if you restore it on another system, data in those tables will be replaced with the new data from the backup. If No is selected, the restored data will be added to any that already exists. The best choice really depends on what you are trying to do. For a normal backup, you should select Yes so that any corrupt or conflicting data is removed when the backup is restored. If you are transferring records to another system or database, however, No should be selected instead so that existing records in the target table are not lost. |
6. |
MySQL backup files are, in fact, just lists of SQL CREATE TABLE and INSERT statements that, when run, restore the database to the state it was in when the backup was made. Although this uses more disk space than a more compressed binary format would, it allows you to easily view and modify the file if you wish. It also means that a backup file can be used on a system with a different architecture, as the file contains only ASCII text.
If you have a database that is being used for an important production purpose, it should be backed up regularly, such as once per day. Instead of following the preceding instructions every day, you can use the Scheduled Cron Jobs module (covered in Chapter 10) to create a job that does the backup for you. To find out what command to run, use the preceding instructions to make a backup first and then visit the Webmin Actions Log module (covered in Chapter 54) to see the command that it used.
Once a backup file has been created, it can be restored on the same system or on another server running MySQL. Depending on what the Add drop table statements to backup? field was set to at backup time, the contents of any existing tables with the same names as those in the backup may be deleted. Therefore, you should generally only restore if the tables do not exist or contain outdated or invalid data that you want to overwrite.
Because a backup file is just a list of SQL statements, the restoration process just involves running all the commands in the file. This means that you can use the following same steps to execute a file of your own commands as well: