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 ?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zephyr ICTCloud ArchitectCommented:
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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can also use some relevant tools (depends on your environment, like Windows/Linux, etc) to export the data (with options) from the old server into a .sql file and then use the same tool to connect to new server, and execute the .sql file accordingly.

For Windows base, you can try use mysql workbench

6.5.2 SQL Data Export and Import Wizard
AXISHKAuthor Commented:
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 ?

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Zephyr ICTCloud ArchitectCommented:
Is it the root mysql password you lost? There's a way to reset that ...
AXISHKAuthor Commented:
So, we can't make use of the physical files to do anything ? Tks
Zephyr ICTCloud ArchitectCommented:
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.
AXISHKAuthor Commented:
Tks. How to create the database to link those files ? Any more example on this ?
Zephyr ICTCloud ArchitectCommented:
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.
AXISHKAuthor Commented:
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 ICTCloud ArchitectCommented:
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?
AXISHKAuthor Commented:
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 ICTCloud ArchitectCommented:
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.
AXISHKAuthor Commented:
I can't find these two parameters, where should it be added, under [client], [mysql] or [mysqld] ?
Zephyr ICTCloud ArchitectCommented:
Under mysqld


Noticed I added another option, it shouldn't really have impact though... But who knows.
AXISHKAuthor Commented:
done but doesn't help...
Zephyr ICTCloud ArchitectCommented:
Well, try to set the values higher, 1200, 64M ... Did you restart MySQL?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.