How to Copy MySQL database from one server to another
In that blog we will see how to copy a MySQL database from one server to another. We have performed the following steps for the demonstration to copy a database from test01 to test02 server.
1. Create a new database clone_test and create a table in that database in server test01
2. Dump/copy the database clone_test in server test01
3. Transfer/scp the database from test01 to test02
4. Create database clone_test in test02
5. Load copied dump to the created database clone_test in test02
[root@test01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> CREATE DATABASE clone_test;
Query OK, 1 row affected (0.00 sec)
MariaDB [clone_test]> create table clone_table(
-> id int,
-> name varchar(60)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
MariaDB [clone_test]> insert into clone_table values(1,'Nazmul Huda');
Query OK, 1 row affected (0.00 sec)
MariaDB [clone_test]> select * from clone_table;
+------+-------------+
| id | name |
+------+-------------+
| 1 | Nazmul Huda |
+------+-------------+
1 row in set (0.01 sec)
MariaDB [clone_test]> quit
Bye
# mysqldump -u root -p clone_test >/tmp/clone_test.sql
Enter password:
[root@test01 ~]# ls -lrt /tmp/clone_test.sql
-rw-------. 1 root root 1902 May 20 15:07 /tmp/clone_test.sql
[root@test01 ~]# scp /tmp/clone_test.sql user1@test02:/tmp/
[root@test02 ~]# ls -lrt /tmp/clone_test.sql
-rw-------. 1 s82893 sshusers 1902 May 20 15:09 /tmp/clone_test.sql
[root@test02 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.3.13-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database clone_test;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> exit
Bye
[root@test02 ~]# mysql -u root -p clone_test </tmp/clone_test.sql
Enter password:
[root@test02 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.3.13-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| clone_test |
| commands |
| information_schema |
| mysql |
| performance_schema |
| test |
| transactions |
+--------------------+
7 rows in set (0.001 sec)
MariaDB [(none)]> use clone_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [clone_test]> show tables;
+----------------------+
| Tables_in_clone_test |
+----------------------+
| clone_table |
+----------------------+
1 row in set (0.000 sec)
MariaDB [clone_test]> select * from clone_table;
+------+-------------+
| id | name |
+------+-------------+
| 1 | Nazmul Huda |
+------+-------------+
1 row in set (0.000 sec)