Monday, February 8, 2010

How to Import / Export OR Backup / Restore MySQL Database in Ubuntu Linux with mysqldump

Backup of database is very important  in Database Projects. You can recover your data when  problems occurs. A variety of  backup strategies are used in  MySQL.  You can choose any one of them. This can also use for Installing your Software from One system to Another. This Method is not Distribution Specific, You can Use same command in Fedora, Mandriva, Debian, SUSE etc.. My Heading is for helping Ubuntu users in  Search.

Export / Backup MySQL database:

Utility named mysqldump  can be used to dump a database or a collection of databases for backup or for transferring the data to another MySQL server. The dump file is a text file which contains SQL statements to create the table and/or populate the table.

How to  export / Backup a  Mysql database to a .sql file

# mysqldump -u USERNAME  -p DATABASENAME > FILENAME.sql

USERNAME is the MySQL admin user
DATABASENAME  is the name of the database that need to be exported /Backup
FILENAME.sql is the name of the file where your data will be exported

Now It will ask for password,Enter  MySQL admin password. 

You can dump all databases by doing:

# mysqldump -u root -p --all-databases > all_my_data.sql

Now We can See How to Import/Restore MySQL database:
Below is the simple command through which you can restore / import the already exported MySQL database file (.sql)

# mysql -u USERNAME -p DATABASENAME < FILENAME.sql


You will be prompted for the MySQL administrator password.