How to Move a mysqldatabase

Thinking about moving your web hosting mysql database? Not sure where to start? It’s easier than you think.

If you have a web hosting cPanel it’s even easier, if not we will show you how to do it without cPanel, see below. Firstly if you do have cPanel you can move a mysql database by logging into your cPanel account.

Now click on the backup button. You should now see a page contains the backup information.

On the third option down it should have

Download a MySQL Database Backup

Just select the database you want to transfer, by clicking on it, a download should now start with your database, please note this may take awhile to start downloading depending on the size of your database.

Once the download is complete, thats you, you have downloaded your database backup.

Now to go about restoring it all you have to do is login to cPanel, click the backup button. Now in the second menu on the right, click browse, select the .gz backup you downloaded earlier and hit upload. This will automatically restore your database.

Thats really how easy it is, to move a mySQL database with cPanel.

Please note if you have a large database I would recommend you use the steps below.

Dont have cPanel?

Dont panic, it is not as easy as it is with cPanel but it’s fairly easy. There are lots of ways to do this but we are going to use ssh so you will need to have ssh access to the server.

If you have ssh, firstly login to the server.

For this we are doing to use the mysqldump command. I suggest you read the man pages for this command by typing `man mysqldump` in ssh. Now for this all we have to do is type
mysqldump -u [username] -B [database] –password > dump.sql

Obviously replace [username] + [database] with your username and database name. Again this is only if you are moving one database, you are best to read the man pages for all the switches. IE if you want to move ALL databases associated with the user you would type

mysqldump -u [username] -A –password > dump.sql

Once this is done, again this may take awhile depending on the size of your database. You should now have a file called dump.sql in the current directory. You will have to transfer this to the server you would like to restore the database on. This can be done with ftp,wget and so on.

For our example we are going to use ftp, now say the server you want to move it too is 127.0.0.1 we would type ftp 127.0.0.1, you will be prompted for the username, after you input the username, you will be prompted with the password, after you have inputted it you should see a screen where you can execute ftp commands. You should see somthing similar to this when logging in.

# ftp 127.0.0.1
Name (127.0.0.1:[currentusername]): example
331 User example OK. Password required
Password:
Remote system type is UNIX.
Using binary mode to transfer files.
ftp>

Obviously this will be different from ftp server to ftp server, but the general layout and functionality should be roughly the same.

Now that you have logged into ftp, you should put the dump.sql file on the remote server by typing

put dump.sql

this may take awhile again depending on the size of your database and on the speed of the connection. Please note, if you are using cPanel (and are using this part anyway, for large databases) you may not be able to upload the database if it is 2+ GB, you will have to split it into different parts.

This can be done by using the split command, before continuing please read the man pages on split by typing `man split`

The split command when using the -b switch DOES support using m for MB instead of just using bytes. I would recommend splitting the files into 1GB portions. This can be done by typing

split -b 1024m dump.sql

You should now see different parts, you will have to use the put command with ftp for each one.

Skip the steps above if your database transferred fine. Now that the database is transferred just exit the ftp client by typing quit.

Now it’s time to work on the remote server. First login to the remote server via ssh. There are lots of ways to restore this database. We are going to use mysqldump and by using the myql command, you only need to use one of these.

1) Using mysqldump
This is fairly easy, you just do the exact same as you did to back it up except instead of > dump.sql you use < dump.sql

So you would type
mysqldump -u [username] -B [database] –password > dump.sql

Thats it, it may take time to restore depending on the size of your database.

2) Using the mysql command
Since we have not used this to restore the database, you may not know how to use this but it’s even easier to restore a database with.
Firstly you will have to login to the database, again read the man pages on mysql before continuing by typing `man mysql` in ssh.
You would login by typing
mysql -u [username] -D [database] –password

Once logged in all you type is

source dump.sql

This will now restore your database.

Thats it you have successfully transferred a database.

Random stuff

1) If you receive problems restoring the database, ie mysql errors. This could be because of when you output the database it does NOT quote everything properly. You can fix this issue by adding -Q to the mysqldump command.

2) If you receive memory problems, you can stop mysqldump writing to memory by adding the -q switch to the mysqldump command.

3) If you just want to output the mysql tables, without data all you would do is add the -d switch to the mysqldump command.