MySql backup and restore

Prerequisites include a MySql server, probably Linux, shell access and MySql services installed and running.

 Backing up and restoring large MySql databases is more difficult than working with small databases. Small databases are easily dealt with using the ubiquitous phpMySqladm administration tool associated with most administrator situations but when the database exceeds a certain size between 2 and 95 Megabytes, using html to download and upload the backup files becomes less desirable than an SSL transport such as sFtp.

1. Determine your MySql database name, MySql user name and password, your domain for mysql (sometimes localhost suffices)

mysqldump -uUserName -p -h DatabaseName >file.sql

This command ends with >file.sql, which redirects the output to the file named file.sql in the current working directory.

2. Use the mysql command to restore the backup file. In this case, DatabaseName could be a different database within the same server, allowing you to make test database, or it could be the same database name on another MySql server.

mysql DatabaseName -uUserName -p <file.sql

Since some versions of html make have difficulty with the ‘less than’ sign, here is another way of doing the same thing.

cat file.sql | mysql DatabaseName -uUserName -p <file.sql

In both these cases, the command is prompting for the password with the -p option.