MySQL – How to Back Up and Restore a Database
Declarations
Database: [dbname]
User: [uname]
Password: [pass]
Back up
Back up a dedicated database from the Command Line:
1 |
mysqldump --opt -u [uname] -p[pass] [dbname] > [dbname].sql |
Back up all the databases:
1 |
mysqldump -u root -p[pass] --all-databases > alldb_backup.sql |
Back up with Compress:
1 |
mysqldump -u [uname] -p[pass] [dbname] | gzip -9 >[dbname].sql.gz |
Restore
1. Create an appropriately named database on the target machine
2. Create an appropriately named user on the target machine
1 2 3 4 5 |
mysql -u [uname] -p[pass] CREATE DATABASE [dbname]; CREATE USER [uname]@localhost IDENTIFIED BY '[pass]'; GRANT ALL PRIVILEGES ON [dbname].* TO [uname]@localhost; FLUSH PRIVILEGES; |
3a. Import an uncompressed SQL dump
1 |
mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql] |
3b. Import a compressed SQL dump
1 |
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname] |