MySQL/Backup-Restore

Backup and restore in MySQL

PHPMyAdmin can backup small DBs for you, or individual tables, But there is a size limit on what PHPMyAdmin Can backup for you

For larger DBs you will need to use the mysqldump program as follows (Comes with MySQL)

1- To backup all databases

mysqldump --opt -u root --password="YourRootPass323" --all-databases > filedump.sql

2- To backup a certain database

mysqldump --opt -u root --password="YourRootPass323" databasename > filedump.sql

To restore a dump

Of all databases

mysql -u root --password="YourRootPass323" < dbdumpfilename.sql

Or of a specific database

mysql -u root --password="YourRootPass323" dbname < dbdumpfilename.sql

Relevant notes you probably don't need

The database INFORMATION_SCHEMA is ignored

--opt means optimize the sql file, there is also --compact, and the reverse of the two is --skip-opt or --skip-compact

--opt is on by default

--skip-quick (Quick is part of --opt so you need to explicitly put this in) saves you memory while dumping for systems that are already low on memory and that are expected to crash or swap if you use memory buffering (Quick)

Enjoy