Copy database from one mysql sever to another mysql server

Question

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.

Answer 1

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