Problem
Need to backup the MySQL database for safe keeping on backup server.
Solution
To backup Database
You can use MySQLdump in linux to dump the database into a SQL file for safe keeping in other backup server. The command is in the following structure:
mysqldump -u username -p password databasename > output_file_name
Example
mysqldump -u root -p rootpass sample_db>sample_db_backup.sql
With the above command, you can create a backup copy of the sample_db database. You can transfer it to other computer using any file transfer tool.
To Restore the Database in second Server
MySQL command line provides an easy interface to restore the database from the SQL file. However, you need to create the database before you can insert tables and data into it.
Here is the linux command to restore a database:
mysql –user=user –password=password database_name < SQL file
To restore the sample_db from the MySQLDump datafile earlier with the following line:
mysql –user=root –password=rootpass sample_db <sample_db_backup.sql
It takes a short moment to insert all tables and data from the sql file to the database. You can automate the task with cron job. That will be covered in other post.
Reference
- Manual for mysqldump and mysql. Accessable in Linux command line.
- More advance topics in DevShed.com