How to backup a MySQL database

The basics of mysqldump to get the job done quickly

You don’t need to spend any money or use any third-party tools to backup you MySQL databases. Simply use the mysqldump utility to quickly get the job done.

Start by opening up a terminal window, then run the mysqldump command. The basic mysqldump command is:

mysqldump -u [user] –p [password] [database_name] > [backup_filename.sql]

If your user account has permission to access the database, this command can be simplified to:

mysqldump [database_name] > [backup_filename.sql]

If your MySQL installation requires you to use elevated credentials, you can run:

sudo mysqldump [database_name] > [backup_filename.sql]

NOTE: Be sure to replace [user] and [password] with your account credentials; replace [database_name] wit the name of the database you are backing up; and replace [backup_filename.sql] with the actual filename you would like to use for this backup.

More detailed information about mysqldump

mysqldump has many more options that you can use. I will cover the more common options, but the full documentation (for MySQL 8.0) can be found here.

The syntax for mysqldump is:

mysqldump -u [user] –p [options] [database_name] [table_names] > [backup_filename.sql]

The parameters are:

  • -u [user]: the user you are using to connect to the MySQL server
  • -p: tells mysqldump to prompt you for the password for the above user
  • [options]: you can customize the backup using one or more of the following parameters (this lists only some of the common ones)
    • –add-drop-database adds a DROP DATABASE statement before each CREATE DATABASE statement
    • –add-drop-table adds a DROP TABLE statement before each CREATE TABLE statement
    • –extended-insert uses multiple-row syntax for inserts
    • –extended-insert=FALSE outputs one row per INSERT statement (runs slower, but makes bulk-editing the output file somewhat easier)
    • –no-create-db do not write CREATE DATABASE statements
    • –no-create-info do not write CREATE TABLE statements
    • –replace write REPLACE statements instead of INSERT statements (REPLACE will delete an existing row before inserting it)
    • –where dumps only the rows that match the given query (–where=”[sql_where_clause]” OR -w”[sql_where_clause]”)
  • [database_name]: the name of the database you want to backup (if you omit this parameter, it will backup all databases
  • [table_names]: an optional parameter where you can specify one or more tables that you want to backup (only these tables will be included in the backup)
  • >: tells mysqldump that you want to create a backup 
  • [backup_filename.sql]: the path and name for the file that will contain the backup