Two Commands To Move Your MySQL Database To A Different Computer

Many popular web applications are written in PHP and use MySQL database. In this post I will describe, how you can move your database to a different computer. Almost all web applications store database and files system information in configuration files. You can actually move your web application to a different server without much hassle by editing few lines in these configuration files. I will write more about the configuration files of web applications in a different post. In this post I will walk you through the steps to move your MySQL database to a different server. The commands will help you move both data and schema.

What do you need to know before moving a database? On both the computers you will require the

• MySQL database name
• MySQL username that has access to the database
• Password for the username that has access to database

You can take a backup of your database in the form of SQL dump and restore it on another server quickly from the Shell. Later in the post we will discuss how you can import and export MySQL databases using a graphical tool.

Launch the terminal. If the server is accessible remotely using SSH you can execute these commands on the secure shell.

Command 1 to generate the backup

Execute the above command in the terminal to generate the backup file of your database. When you enter the command you will be prompted to enter the password for the MySQL database user. Type the password and hit enter. In the above example mydatabaseuser is the MySQL database user having access to the database mydatabase. mydatabase is the name of the MySQL database. mysqldump is the command we execute in the shell. When you execute the above command MySQL generates the backup and stores it in the file backup.sql.

Checking if the command was executed successfully. Once you type and password and hit enter, the shell returns with no message. If there was any problem while executing the command you would receive the error message. You can also see the backup file contents by typing

The above command will print the contents of the backup file. If your database is large you can use the less command:

You can scroll down the screen by pressing the enter key. Type q and hit enter to exit.

Transfer the backup.sql file to the server where you want to import your database. Before you import the database you have to create a database. The database can have a different name on the second server. You will also have to create a database user and grant access to the database on the second server. Let us assume mydatabase2 is the name of the database where we import the backup on the second server. mydatabaseuser2 is the MySQL user that has access to mydatabase2. Let us execute the command on the second computer to import the database.

Command 2 to restore the backup

If the shell returns with no error message, the operation was successful. One common mistake people do is execute these commands in the MySQL command line interface. You have to execute these commands on the Linux shell/terminal. Another gotcha worth mentioning here is with the correct usage of the < and > operators. Compare the two commands and notice the difference. Yet another mistake people often make is replace mysql with mysqldump in the second command. We use mysqldump to generate the backup and mysql command to restore the database.

Using phpMyAdmin to export and import MySQL database.
In the last post we learned how to install LAMP. That post also descries how to install phpMyAdmin. You can also refer to package management on Fedora for more details.

Here are the steps to generate a backup of your MySQL database using phpMyAdmin

1. Launch phpMyAdmin from your browser by visiting http://localhost/phpMyAdmin/. The URL to access phpMyAdmin will be different if the server is other than your local computer. Check with your server administrator on how to access phpMyAdmin
2. phpMyAdmin will prompt you to type your database username and password. Type them and hit enter.
3. On the left hand side of the webpage select the database by clicking on it
4. On the navigation menu at the top click Export
5. In the "Save as file" legend put a check mark next to Save as file and type the name of the backup file in the "File name" template" field
6. Click Go. phpMyAdmin will offer the backup file for download.
7. Save the file on your computer.
8. Access phpMyAdmin on the second server using the appropriate URL
9. Repeat step 2 and 3
10. Click Import on the top navigation menu
11. Click the Browse button in the "Location of the text file" field. Select the backupfile stored on your computer.
12. Click Go

We are done. You just learned two methods of importing and exporting MySQL database.