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) |