MySQL Backup and Restore

Tutorial for MySQL backup and restore using command line for those of you who are doing it for the first time or have forgotten some parameter.

First of all don’t forget that you need to have permissions to connect with the user you are going to use from the host you are executing this command. If it is from the same machine that MySQL server is on, than you probably have access, but if you are connecting from a remote machine you need to make sure you have access from that host.

[divider]

MySQL Backup using mysqldump

The basic command is like this:

mysqldump -h [hostname] -u [dbuser] -p[dbpass] [dbname] > [backupfile.sql]
  • [hostname] MySQL server hostname (if localhost don’t use this parameter)
  • [dbuser] Your MySQL username
  • [dbpass] Your MySQL password (there is no space between -p and the password, if you leave just -p it will ask you to enter the password)
  • [dbname] The name of your database you want to backup
  • [backupfile.sql] The filename of the backedup database

If you want to backup your WordPress database named wpblog from you localhost MySQL server use this command:

mysqldump -u mydbuser -p wpblog > wpblog.sql

Or from a MySQL server that is on another server named db1.mysqlserver.com use this command:

mysqldump -h db1.mysqlsqlserver.com -u mydbuser -p wpblog > wpblog.sql

Note: I didn’t specify password with the -p parameter, but -p is in the command, this way it will ask me for the password for my mydbuser account. If you don’t add -p it will assume that the account is without specified password.

[divider]

Restore your MySQL database

Once you have backup of your MySQL database you can restore it using the mysql command:

mysql -h [hostname] -u [dbuser] -p[dbpass] [dbname_to_restore] < [backupfile.sql]
  • [hostname] MySQL server hostname (if localhost don’t use this parameter)
  • [dbuser] Your MySQL username
  • [dbpass] Your MySQL password (there is no space between -p and the password, if you leave just -p it will ask you to enter the password)
  • [dbname_to_restore] The name of your database you want to restore
  • [backupfile.sql] The filename you want to restore from

Now lets restore our wpblog.sql file into a localhost MySQL server into a database named newwpblog:

mysql -u mydbuser -p newwpblog < wpblog.sql

Or intoa MySQL server that is on another server named db1.mysqlserver.com:

mysql -h db1.mysqlserver.com -u mydbuser -p newwpblog < wpblog.sql

[divider]

MySQL Backup and Restore in one line

If you want to make a fresh newest copy of your database wpblog into newwpblog database on a MySQL server on a the same localhost machine use this command:

mysqldump -u mydbuser -pmydbpass wpblog | mysql -u mydbuser -pmydbpass newwpblog

Or copy wpblog from localhost to remote MySQL server db1.mysqlserver.com into databse newwpblog:

mysqldump -u mydbuser -pmydbpass wpblog | mysql -h db1.myhostname.com -u mydbuser -pmydbpass newwpblog

Note: I’ve added mydbpass as password next to -p parameter (there should be no space between -p and the password) and don’t forget if restoring to remote MySQL server you need to have permissions to connect from the host you are executing the command.

MySQL: http://www.mysql.com/

Nikola Stojanoski

System Administrator and Developer. Giving back to the community by blogging about my problems, solutions and practical howto's.