How to copy MySQL database from one server to another?

Question

I just finished making a website in Joomla! 1.6. Web sites in Joomla! consist of two basic parts. There are files which are used by HTTP server and there is the database. I have FTP access to the server and have copied the needed files, but I don't know how to copy the MySQL database.

I have access to MySQL terminal application on the remote computer and to MySQL on my computer. On the server, there is already a username which I can use and there is an empty database which I should use. The user has full control over the database.

I do not have physical access to the remote server.

This one should be very simple, but Google isn't with me today.

Answer 1

This link (taken right from the MySql docs...) shows you how to use the utils w/ MySql to move the database to a different location. I've personally never needed to do this but the detailed instructions should be sufficient.

Answer 2

There are two basic ways of achieving this.

1) Dump the database to a text file and import at the remote end

2) Copy the raw data files.

Option one is usually the most reliable, especially if the two systems are different.

First, create a dump of the database:

$ mysqldump -u <username> -p<password> <databasename> | bzip2 >mydatabase.sql.bz2

This will dump the entire database and compress it into a file. You don't have to do the compression if you don't want to, but it makes transferring the file through the internet an awful lot quicker. Obviously replace the username, password and databasename with your actual details.

Once you have the file mydatabase.sql.bz2, transfer it to the remote server with whatever tools you would normally use (scp for example);

One transferred you need to create the database on the remote server (or have it created for you by the system admins / control panel):

mysql> create database <databasename>;
mysql> grant all privileges on <databasename>.* to <username>@localhost identified by '<password>'

Obviously replace the details with your own details.

Now to restore the database:

$ bzcat mydatabase.sql.bz2 | mysql -u <username> -p<password> <databasename>

After a few moments you should have your database now installed in the remote server.

Answer 3

I have moved quite a few Joomla sites manually from development servers to their uitimate hosts, but now use Akeeba Backup as it creates a 'package' of the site which can be uploaded on a target server and then 'executed' (from a browser) to restore the site - it's bloomin marvellous, free and compatible with 1.6.

Worth a read: http://www.akeebabackup.com/software/akeeba-backup.html