MySQL Console Database Backup/Dump and Restore via console
Backup a DB via console
The backup/dump is performed using mysqldump, which takes the contents of the specified database and creates a set of “CREATE TABLE” and “INSERT INTO” SQL commands that can be used to re-create the database again.
In short, the mysqldump tool is called like this:
mysqldump –user [user] –password=[pass] [db] > [file]
You can also use the short versions of the command-line arguments. When you do, however, you’ll need to omit the space and/or the equals sign (=) between the password option and the actual password, as shown below.
By way of example, the following command would create a dump file called “my_backup.sql” from database “mydb_db”, accessing the database with user “usersql” and password request:
mysqldump -u usersql -p mydb_db > my_backup.sql
(After the command insert the password for the usersql)
There’s lots of other options available (including SQL compatibility modes) but that’s well beyond the scope of this quick post – if you need further detail, perhaps you should head over to the MySQL reference manual entry on mysqldump
Restoring / Inserting a Dump into MySQL via console:
Instead of using mysqldump, we use the actual mysql. The command-line options and arguments are basically the same, but the redirection goes the other way:
mysql –user [user] –password=[pass] [db] < [file]
To restore the dump taken above, we’d use:
mysql -u usersql -p mydb_db < my_backup.sql
(After the command insert the password for the usersql)