MySQL Backup and Restore


Tutorial for MySQL backup database 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.


MySQL Backup database 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 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.


MySQL Restore from dump

Once you have your MySQL 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 database 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/



Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Donatello Tuttle
Donatello Tuttle
6 years ago

Nice article mate.

I just saved my organisation while reading this… we had a db go offline and I had to restore… thank God for this article.

I need to go and clean out my pants though, they are bulging with the brown stuff… was so scared

Advertisement