MySQL Backup and Restore

Backup and Restore

# Dump entire server
mysqldump -u root -p --all-databases > all_dbs.sql

# Dump single database
mysqldump -u root -p mydb > mydb_backup.sql

# Dump specific tables
mysqldump -u root -p mydb users posts > tables.sql

# Dump with compression
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# Restore
mysql -u root -p mydb < mydb_backup.sql
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# Restore to different database
mysql -u root -p -e "CREATE DATABASE newdb"
mysql -u root -p newdb < mydb_backup.sql

# Binary log for point-in-time recovery
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p