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)