Skip to content

Instantly share code, notes, and snippets.

@shyamvyas5

shyamvyas5/mysql-backup-restore-commands.md

Created April 21, 2022 08:23
Show Gist options
  • Save shyamvyas5/387400862f87ff21af91a5bbf4a44d52 to your computer and use it in GitHub Desktop.
Save shyamvyas5/387400862f87ff21af91a5bbf4a44d52 to your computer and use it in GitHub Desktop.
Download ZIP
Commands for backup and restore of mysql database
Raw
mysql-backup-restore-commands.md
For backup of all databases,
mysqldump -u <DB_USER> -p --all-databases --host <DB_HOST> -P <DB_PORT> > <PATH_FOR_FILE>.sql
For backup of a specific database,
mysqldump -u <DB_USER> -p <DB_NAME> --host <DB_HOST> -P <DB_PORT> > <PATH_FOR_FILE>.sql
For backup of a specific database in single transaction without locking the tables,
mysqldump -u <DB_USER> -p <DB_NAME> --host <DB_HOST> -P <DB_PORT> --single-transaction --quick --lock-tables=false > <PATH_FOR_FILE>.sql
For backup of a specific database and specific tables within that database,
mysqldump -u <DB_USER> -p <DB_NAME> <TABLE1_NAME> <TABLE2_NAME> --host <DB_HOST> -P <DB_PORT> > <PATH_FOR_FILE>.sql
For backup of a specific database with gzip compression,
mysqldump -u <DB_USER> -p <DB_NAME> <TABLE1_NAME> <TABLE2_NAME> --host <DB_HOST> -P <DB_PORT> | gzip > <PATH_FOR_FILE>.sql.gz
For backup of a specific database without the database structure,
mysqldump -u <DB_USER> -p <DB_NAME> --host <DB_HOST> -P <DB_PORT> --no-create-info > <PATH_FOR_FILE>.sql
For restore of the particular database,
mysql -u <DB_USER> -p <DB_NAME> --host <DB_HOST> -P <DB_PORT> < <PATH_FOR_FILE>.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment