Copy Database from One MySQL Server to Another – Step-by-Step Guide

database-designdatabase-recommendationMySQLmysqldump

Following are the details of the database and server:

Server1:
Host Name: abc
Port Num: 01
database: test

Server2:
Host Name: xyz
Port Num 02
database: spareparts
userid: spts
password: abcdefgh@123

I have to copy all tables from database test to database spareparts.

Please guide me how I can copy them. I am new in using mysql. Also please let me know if any other details are required.

Best Answer

There are several methods you can use such as

mysqldump
folder copy
enterprise backup

for mysqldump use following command:

mysqldump -uuser -ppassword --database your_db_name --routines --triggers --events --lock-all-tables >outputfile.sql

and use following command at destination:

mysql -uroot -ppassword<outputfile.sql.

You can also use following method - stop server at source - stop server at destination - copy all the database folders from source to destination - start destination server - start source server

Hope it helps

Related Solutions

MYSQL database how to check if database is active and on which port (Linux)

It seems that your mysql connection is only listening by unix sockets and now by the actual TCP.

To solve that, go to your configuration file, usually on /etc/mysql/ folder with the name my.cnf on Ubuntu(if you are using some other system should be similar).

In the configuration file you should have a section called [client].

make your server to listen on 127.0.0.1:3306 by:

[client]
port        = 3306
#socket     = /var/run/mysqld/mysqld.sock
host = 127.0.0.1

And than restart the server.

Now you should have access to the mysql shell by doing on your command line.

mysql -U TEST -h 127.0.0.1 -p db_name

Or you can use any other client you want.

Mysql – Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server

What I traditionally do, if I can't sync between databases, is take a copy of the schema, add dummy data, and develop on that locally. Then I save all my modification scripts and run them on the main server when I'm ready to push.

I would avoid moving data from development to production. Production -> Development may be fine if you don't have large amounts of data. Otherwise, use a dummy data generator if you can.

Ultimately, you'll have to develop your own methodology just like you do when you introduce version control or backups to a project. It is dependent on the situation.

Best Answer

Related Solutions

MYSQL database how to check if database is active and on which port (Linux)

Mysql – Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server

Related Question