Backing Up MySQL Database
MySQL database backup can be accomplished in two ways:
a) Copying the raw mysql database files &
b) Exporting tables to text files
b) Exporting tables to text files
Copying the MySQL database files
MySQL uses the same table format on different platforms, so it's possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).
Exporting tables to text files
The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. To use the MySQLDump utility it is required to logon to the System running the MySQL Databse . You can use Telnet to remotely logon to the system if you don't have the physical access to the machine.
The syntax for the command is as follows.
mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup
[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup
Let's discuss the example of backing up MySQL Database named "accounts" into text file accounts.sql. Here are the scenarios of taking the backup assuming that both user name and password of the database is "admin".
a) Taking the full backup of all the tables including the data.
Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql
mysqldump -u admin -p admin accounts > accounts.sql
b) Taking the backup of table structures only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-data accounts > accounts.sql
mysqldump -u admin -p admin --no-data accounts > accounts.sql
c) Taking the backup data only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql
Restoring MySQL Database
Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.
mysql - u admin -p admin accounts < accounts.sql
In this tutorial you learned how to take the backup of your MySQL Database and restore the same in the event of some database crash or on some other machine.
