howto: backup and restore a mysql database
long time, no post… i am back and decided to start off with a small howto. since i am always moving websites and web based programs from server to server, i thought others might benefit from this.
following these steps, you’ll be able to backup a database on one server and restore it to (the same server if upgrading an app or) a different server.
first thing you need to do is know what username/password you are using for the database and what the name of the database is. you can do this by logging into mysql with the root user and displaying a list of the databases that are available.
mysql -u root -p
enter password
SHOW DATABASES;
you should get something that looks like this (but prettier)
+——————–+
| Database |
+——————–+
| information_schema |
| db1 |
| db2 |
| mysql |
| ocdb |
| ocswebdb |
+——————–+
6 rows in set (0.01 sec)
now we need to do the actual backup by using the following command (replace username, database, and /path/to/database.sql with your information).
mysqldump -u username -p database > /path/to/database.sql
when you’re ready to restore the database, use this command
mysql -u username -p database < /path/from/database.sql
its that easy. there is another way to directly duplicate a database from one server to another, but that will be covered later on. enjoy!