🐬 MySQL Backup Cheat Sheet

Creating a Backup

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

Dumping Database Structure Without Data

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

Dumping Specific Tables

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

Creating and Compressing Backup

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

Backup With Date in Filename

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

Restoring a Backup

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

Restoring From a Compressed Backup

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

or

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

Creating a New Database

mysqladmin -u USER -pPASSWORD create NEWDATABASE

Viewing a List of Databases

mysqlshow -u USER -pPASSWORD

Viewing Tables in a Database

mysqlshow -u USER -pPASSWORD DATABASE

🐳 MySQL Backup from Docker

Create a Dump from a Container

docker exec -it CONTAINER_ID mysqldump -u root --password=pass DB > /dump.sql

Before running this command:

• Replace CONTAINER_ID with the actual container ID (docker ps -a to list containers).
• Replace /dump.sql with the desired path and file name on the host machine.
• Replace root, pass, and DB with your actual MySQL username, password, and database name.

Restore a Dump into a Container

cat /dump.sql | docker exec -i CONTAINER_ID /usr/bin/mysql -u root --password=pass DB

• Make sure to adjust CONTAINER_ID, username, password, and database name accordingly.

🚀 Explore more guides on our blog 👉 blog.1it.pro
📧 Contact us: admin@1it.pro for expert IT guidance.
🌐 Explore more: Visit 1it.pro for top-tier IT solutions.