22. June 2010 · 79 comments · Categories: Linux

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

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 file to which the backup should be written.

The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database ‘Data’ with the username ‘pensacola’ and password ‘password’ to a file customdata.sql, you would issue the command:

[pensacola@pensacola-tech ~]#mysqldump -u pensacola -p password Data > customdata.sql

Import your data into your database:

[pensacola@pensacola-tech ~]# mysqldump -u pensacola -p password Data < customdata.sql

Backup of Mysql database using phpMyAdmin:


It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

  • Open phpMyAdmin.
  • Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
  • Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
  • Select”SQL”-> for output format, Check “Structure” and “Add AUTO_INCREMENT” value. Check “Enclose table and field name with backquotes”. Check “DATA”, check use “hexadecimal for binary field”. Export type set to “INSERT”.
  • Check “Save as file”, do not change the file name, use compression if you want. Then click “GO” to download the backup file.


Restoring a backup of a MySql database with phpMyAdmin:

  • To restore a database, you click the SQL tab.
  • On the “SQL”-page , unclick the show query here again.
  • Browse to your backup of the database.
  • Click Go.

AutoMySQLBackup

There is a script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. You can download this script from here. This can be run using the following:

[pensacola@pensacola-tech ~]#sh automysqlbackup.sh.2.5

This is a very simple way for server administrator …

If you find AutoMySQLBackup valuable please make a donation at:

css.php