How to Transfer MySQL Database from One Computer to Another

MySQL is one of the most popular database systems used by millions of developers and organizations. When you migrate your database from one computer to another, you will need to transfer one or more databases during this process. In this article, we will learn how to transfer database from one computer to another.

How to Transfer MySQL Database from One Computer to Another

Here are the steps to transfer MySQL database from one computer to another. Basically, you need to dump your databases into a .sql file on your source computer. Then you need to manually transfer this .sql file to your destination computer. Finally, you need to load from this .sql file to a new empty database, on your destination computer. You can use these steps for MariaDB also.

1. Export Database to Dump File

Open terminal and run the following command to stop your MySQL/MariaDB server on this system.

# systemctl stop mysql
OR
# systemctl stop mariadb

Next, run the following command to export database into a dump file. Replace [user] with your database username.

# mysqldump -u [user] -p --all-databases > all_databases.sql

Please note, the above command will dump definition and data of all databases accessible to [user] to all_databases.sql file.

If you want to transfer only a single database, then run the following command. Replace [database name] with the name of database that you want to export.

# mysqldump -u [user] -p --opt [database name] > database_name.sql

2. Transfer MySQL Database Dump File to New Server

Next, you can use a file transfer utility like scp to transfer the file to destination server using the following command. In the command below, replace user with scp username, not database username, and example.com with the domain name or host ip address of destination system.

# scp all_databases.sql user@example.com:~/       [All Databases]
# scp database_name.sql user@example.com:~/       [Singe Database]

Please note, you need to have scp user access in destination server to be able to transfer file to them.

3. Import MySQL Databases Dump File to New Server

After you have transferred the database dump file to new server, run the following command on the new server to import them into MySQL running on that system. We have provided commands to import both one or all databases.

# mysql -u [user] -p --all-databases < all_databases.sql   [All Databases]
# mysql -u [user] -p newdatabase < database_name.sql      [Singe Database]

Once you have imported your databases you can log into your database system on your destination machine with the following command and list the databases.

# mysql -u user -p
# show databases;

4. Transfer Users & Permissions

The above steps only allow you to transfer one or more databases from one computer to another. If you also want to transfer permissions and users, you can use rsync command to copy all content from MySQL/MariaDB data directory to new server as shown. Replace user with username and example.com with domain name or IP address of remote host.

# rsync -avz /var/lib/mysql/* user@example.com:/var/lib/mysql/

Once the transfer is complete, you can change ownership of files that you have copied to mysql:mysql. You need to run the following commands in your destination system.

# chown mysql:mysql -R /var/lib/mysql/
# ls  -l /var/lib/mysql/

In this article, we have learnt how to transfer MySQL database from one computer to another. It is useful if you want to migrate your data to a new system.

How to Transfer MySQL Database from One Computer to Another