Using MySQLDump to Back up and Restore Databases

Recently needed to back up live MySQL databases and restore them to another location. This included ALL databases into one dump file that could be imported to another server. You will need to have the root username/password to mysql.

Exporting:

# mysqldump -u root -p $PASS --all-databases > MySQLDUMP.sql

Importing:

# mysql -u root -p $PASS < MySQLDUMP.sql

Alternatively, you can do this for a single database by specifying the database you wish to dump before sending the output to a file:

# mysqldump -uroot -p $PASS <database> > MySQLDUMP.sql

Q: But I don't know or have my root password for MySQL!
A: Easy, follow this one:

If you do not know the root password you can still obtain all databases and import them to the new host which I'm under the assumption you also do not have the password for. You can start MySQL without permissions to get this:

Exporting:

# service mysqld stop
Stopping mysqld:                                           [  OK  ]
# mysqld_safe --skip-grant-tables &
# mysqldump --all-databases > MySQLDUMP.sql
# service mysqld stop
Stopping mysqld:                                           [  OK  ]
# service mysqld start
Starting mysqld:                                           [  OK  ]

Importing:

# service mysqld stop
Stopping mysqld:                                           [  OK  ]
# mysqld_safe --skip-grant-tables &
# mysql -u root < MySQLDUMP.sql
# service mysqld stop
Stopping mysqld:                                           [  OK  ]
# service mysqld start
Starting mysqld:                                           [  OK  ]

Of course, if necessary depending on user that is connecting to MySQL to perform the dump/import, you will need to specify different credentials and replace the $USER or $PASS.