MYSQL Backup in the command prompt


Sometimes, I have to find solutions for creating database-backups by MySQL prompt commands. This rarely happens, mainly due to the migration of my projects or hypotheses. I decided to make the short note about MySQL backup by prompt commands and post it here.

Create a backup

mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Create a Database Schema

mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

If we need to make the backup only for one or a few tables

mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Create a backup and archive it

mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Create a backup archive with a date-time name

mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Import backup data to the database

mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Import backup data from arc to the database

gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Additional options

  • --quote-names, -Q
    Quote identifiers (such as a database, table, and column names) within ` characters. If the ANSI_QUOTES SQL mode is enabled, identifiers are quoted within ” characters. This option is enabled by default
  • --extended-insert-e
    Write Insert statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
  • For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

find more options:

References materials


Please enter your comment!
Please enter your name here