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.

ADVERTISEMENT


MySQL Backup using mysqldump

The basic command is like this:

mysqldump -h [hostname] -u [dbuser] -p[dbpass] --quick --max_allowed_packet=512M --routines --single-transaction [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)
  • –quick – Don’t buffer query, dump directly to stdout.
  • –max_allowed_packet=512M – The maximum packet length to send to or receive from server (to avoid the following error: mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table)
  • –routines – Dump stored routines (functions and procedures)
  • –single-transaction – Creates a consistent snapshot by dumping all tables in a single transaction. (currently only InnoDB tables support multiversioning)
  • [dbname] – The name of your database you want to backup
  • [backupfile.sql] – The filename of the backed-up database

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

mysqldump -u mydbuser -p --quick --max_allowed_packet=512M --routines --single-transaction 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 --quick --max_allowed_packet=512M --routines --single-transaction 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.


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

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 --quick --max_allowed_packet=512M --routines 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.

MySQLhttp://www.mysql.com/

ADVERTISEMENT