How to Copy backup a MySQL Database

Posted on Updated on

The mysqldump Command

The mysqldump command creates a text version of the database. Specifically, it creates a list of SQL statements which can be used to restore/recreate the original database.

# mysqldump -uusername -ppasswd dbname > backupfile.sql

or

# mysqldump -B openfire > /home/db.sql

or

# mysqldump –all-databases -r dbdump.sql -uroot-p

[username] Your database username
[passwd] The password for your database
[dbname] The name of your database
[backupfile.sql] The filename for your database backup

You can dump a table, a database, or all databases.

To dump all MySQL databases on the system, use the –all-databases shortcut:

# mysqldump -u root -p –all-databases > backupfile.sql

Restoring a MySQL Database

Use this method to rebuild a database from scratch

# mysql -u username -p passwd database_to_restore < /path/to/file.sql

Use this method to import into an existing database (i.e. to restore a database that already exists)

# mysqlimport -u sadmin -p pass21 db_name DBback.sql

or

# mysqlimport options database textfile

or

mysql>use dbname; <select the existing DB >
mysql>source /home/db.sql; <restore backup to the db>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s