Chances are, your network applications depend upon databases. In fact, much of what you work with probably depends upon a database or two. Because of this, it’s important to not only have backups of those databases, but to also be able to export and import them from either machine-to-machine or database-server-to-database- server. Say, for instance, you migrate from MySQL to MariaDB. How do you move those databases from one server to the next?
I’m going to show you.
I will be presenting this in the shape of exporting a database from one MySQL server, copying it to another server, and importing it into the new MySQL server. I’ll demonstrate this on identical Ubuntu 18.04 Server installs, but the process should be the same, regardless of your Linux distribution. I will also employ the scp command to move the database from one server to the other. You will need to have access to a user with sudo privileges on the initial server and MySQL admin user credentials on both.
With all of that laid out, let’s get that database exported, moved, and imported.
Exporting the database
Instead of testing this on a production database, we’re going to create a test database to use for practice. Once you know how it works, you can then run the process on your production databases.
To create the test database, log into the MySQL prompt (of the initial server) with the command:
mysql -u root -p
Create the test database with the command:
CREATE DATABASE test;
Now that we have our test database, let’s export it with the command:
sudo mysqldump --add-drop-table -u root -p test > test.sql
You should now see the dump file test.sql in the current working folder.
Moving the test file
Now we have to move the file from one server to another. For that, we employ the secure copy command. Let’s say our new server is at IP address 192.168.1.100. To successfully move the file, we’ll issue the command:
scp test.sql USER@192.168.1.100:~/test.sql
Where USER is a valid user on the remote server.
You will be prompted for the USER password on the remote machine. Once authenticated, the test.sql file will be copied.
Importing the test file
Next we import the database into the new server’s MySQL database. It will be necessary to know the MySQL root user credentials for this task. Log into the remote server, and import the test database with the command:
mysql -u root -p test < test.sql
Note: Exchange root with your MySQL admin username in the above command.
You will be prompted for the admin user’s password. Once you successfully authenticate, the database will be imported. You can double check by logging into the MySQL prompt (using the command mysql -u root -p) and then issuing the command:
SHOW DATABASES;
You should see the imported database listed (Figure A).
Figure A
Our newly imported database, ready to use.
Simple database migration
And that, my friends, is all there is to migrating a database from one MySQL server to another. This process also works if you’re migrating from a MySQL database to a MariaDB database. The only caveat could be if you’re working with a much older MySQL database, and there are inconsistencies between how that older database handled tables and/or data, versus how the newer MariaDB server works with tables and/or data. Chances are, however, you aren’t using a database that old, so everything should work just fine. Happy migration.
This article is shared by www.fortunescripts.com | A leading resource of inspired clone scripts. It offers hundreds of popular scripts that are used by thousands of small and medium enterprises.
