Avatar of AXISHK
AXISHK
 asked on

Move a MySQL Database

How can I move current mySQL database to another server ? Can I simply copy all the physical files to another mysql server ?

Tks
MySQL Server

Avatar of undefined
Last Comment
Zephyr ICT

8/22/2022 - Mon
Zephyr ICT

A simple way of doing this is to make a backup/dump of the mysql db on the original host and copy it over to the new host, then import this backup/dump in the mysql server there (this implies you have mysql already installed of course).

Dump of db can be something like this:
mysqldump -u root -p --opt dbname > dbname.sql

Open in new window


Import the db on the other node:
mysql -u root -p dbname < /path/to/dbname.sql

Open in new window

SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
AXISHK

ASKER
I'm still looking for the mysql password as I can't find it in the handover list.

With the current physical file, (*.MYD, *.MYI), is it possible to copy them into another server and link them through some way ?

Tks
Zephyr ICT

Is it the root mysql password you lost? There's a way to reset that ...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
AXISHK

ASKER
So, we can't make use of the physical files to do anything ? Tks
Zephyr ICT

Well yes, it is possible to copy them over to another system and even possible to access them via MySQL again, but you might need to set the necessary permissions (eg: GRANT) on the database as well as set the correct permissions on the files once you copied them to the database directory (using chown mysql:mysql for example).

Also, you should look if you have the *.FRM file as well.
AXISHK

ASKER
Tks. How to create the database to link those files ? Any more example on this ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Zephyr ICT

I don't have a direct example for doing this no... It works something like this:

1. Make sure you have MySQL installed on the new server (at least same version, newer is possible to)
2. copy the necessary files over to the new server (.myd, .myi, .frm), put them in the /var/lib/mysql/dbname folder for example.
3. Change the permissions on the files (chown -R mysql:mysql /var/lib/mysql/dbname)
4. This should make the db/table available in MySQL
5. Set the necessary user/permissions inside MySQL (eg:
GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';

Open in new window

).

Something like this should get you there, no garantees given naturally, this is possible, but it's still not best practice I should say.
AXISHK

ASKER
Get the password and backup the database to a tsql file.

Restore on another server,  but pop out the errors after running for a while..

MySQL server has gone away.
Zephyr ICT

Sorry, I don't quite understand your last post. You found the password to your MySQL server and could make a dump of your db?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
AXISHK

ASKER
Yes, and restore on another MySQL DB using your previous command. It run for a while and stop with the message "MySQL server has gone away. "

I have also physically stopped the mysql on the source server and physically copy the individual files on the destination server. Afterwards, I restart the mysql service. However, I have two files that I have the following error:

ERROR 1146 (42S02): Table 'cms.path_c' doesn't exist

I have also run mysqlcheck but it can't fix it. Any advise ? Tks
Zephyr ICT

The sql dump file you have, that you want to import, is it large?

To try and resolve the issue you can try 2 things:

1. Adjust the "wait_timeout" setting in /etc/mysql/my.cnf (or wherever it is on your system) to a higher setting, eg: if it is set to 600 sec, set it to 1200 sec, or something like that.

2. Adjust the "max_allowed_packet" in  /etc/mysql/my.cnf to a higher value, eg: if the setting now is 32MB, make it 64MB.

After changing these settings restart mysql.
AXISHK

ASKER
Tks.
I can't find these two parameters, where should it be added, under [client], [mysql] or [mysqld] ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Zephyr ICT

Under mysqld

[mysqld]
interactive_timeout=600
wait_timeout=600
max_allowed_packet=32M


Noticed I added another option, it shouldn't really have impact though... But who knows.
AXISHK

ASKER
done but doesn't help...
ASKER CERTIFIED SOLUTION
Zephyr ICT

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.