Dumping and restoring are fairly common task of managing database. Dumping is a process of making a file containing SQL queries that can be used to construct the whole database. Dumping is commonly used to download your database for backup or other purposes. Restoring is the reverse process of dumping.
To dump MySQL database please do the following.
Log on to SiteManager if you haven't already logged on.
Go to menu and then click on for the database you want to dump. From there click on .
Inside the menu, there are several options you can choose to determine how you want the database to be dumped. Choose accordingly and click .
The available options are described below.
What to dump. You can choose to dump both structure and data, structure only or data only.
Add DROP TABLE. If this option is selected, a DROP TABLE command will be created before creating tables. Useful if you want to replace existing database on the target database.
Add LOCKs. If this option is selected, a LOCK TABLE command will be added before creating tables.
Complete INSERTs. Use complete INSERT command.
Include all MySQL specific create options. This will include MySQL specific create options such as specifying the type of table.
Compress result. You can choose whether to compress the result or not. You can choose gzip or bzip2 compression.
Note
Sometimes the dump result is not compatible with different MySQL version. Please consult MySQL documentation for more information.
The dump file created above can be used for restoring database. You can also use dump file created elsewhere, and optionally compressed with gzip or bzip2. To restore database please do the following.
Log on to SiteManager if you haven't already logged on.
Go to menu and then click on for the database you want to restore into. From there click on .
Inside the menu, you need to choose an MySQL dump file on your system and then click on to continue. Optionally you can choose if you want MySQL to ignore when it encounter an error when restoring database.
You can also manually dump database using your shell account. You can also make SQL dump files on other system using this method. To make an SQL dump you need to log on to your shell account. Refer to the section called “Logging in to UNIX Shell Account” for more information. Issue the following command to dump your database. The following assumes your database name is u777_database with the password PASSWORD.
mysqldump -uu777_database -pPASSWORD \
u777_database > u777_database.sql
That command should create a file named u777_database.sql containing the SQL dump. You can make a gzip compressed SQL dump by issuing the following command instead of the above, useful when your database is large in size.
mysqldump -uu777_database -pPASSWORD \
u777_database | gzip > u777_database.sql.gz
Similarly, to restore a database from a dump file, please do the following command.
mysql -uu777_database -pPASSWORD \
u777_database < u777_database.sql
To do the same but with a gzip compressed dump file, do the following instead of the above.
gunzip < u777_database.sql.gz | \
mysql -uu777_database -pPASSWORD \
u777_database
Tip
On the above commands, you can substitute gzip with bzip2 to use bzip2 instead of gzip compression.