Ubuntu India Design and Management Wiki

Mysql DB Backup and Restore How to

 Database Backup

If you're storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. It is also a good idea to create a backup before making any major changes, incase something goes wrong and you need to revert to the unmodified version. Database backups can also be used to transfer your database from one server to another if you change web hosts. Command Line (using mysqldump)

# mysqldump -u user_name -p your_password database_name > File_name.sql

Eg: Let's assume that: Username: ubuntu Password: jaunty Database Name: ubuntulinux

  1. mysqldump -u ubuntu -p jaunty ubuntulinx> ubuntulinux.sql

This will backup the database to a file called ubuntulinux.sql

Back up more than one database

In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

  1. mysqldump -u root -p --databases ubuntu ubuntulinux > data_backup.sql

Backup all Database

Back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

  1. mysqldump -u root -p --all-databases > all_backup.sql

Database Backup From the phpMyAdmin

  1. Login to phpMyAdmin
  2. Click on your database name
  3. Click on the tab labeled EXPORT
  4. Select all tables you want to backup (usually all)
  5. Default settings usually work, just make sure SQL is checked
  6. Check the SAVE FILE AS box
  7. Hit GO

Database Restore Restore Database From the Command Prompt

For more info http://ubuntulinux.co.in/mysql-backup-methods.php