Dear Members,

We are trying to transfer db of approx 786 MB having around 120,000 tables, PHPmyadmin fails to take dump and even mysqldump command gives table lock error

So we tried the var/lib/mysql and zipped the db folder and unzipped on another server by created db

We used zip -r . command to zip the folder. When we unzipped it did not work on another server.

We also tried this solution

--------------------------------
cd /var/lib/mysql
tar -czf yourdb.tar yourdb

then FTP yourdb.tar to the new server, or put it in some web folder and wget it from the new server. After you get it in the new server:

tar -xzf yourdb.tar
mv yourdb /var/lib/mysql
chown mysql /var/lib/mysql/yourdb -R
------------------------------------

It was advised by someone on forums
Permission with tar comes correct but we use plesk, when we crate db in plesk, it creates db in var/lib/mysql and can not over write the directory when we move the db to that folder

Please provide us solution with zip command and also tar solutions

You can use mysqldump no problem as long as you close anything that's accessing the database at the time (such as shutting down httpd first) or trying a more suitable command line like `mysqldump --skip-opt --add-drop-table -a database_name > filename`

As for copying the tables directly, it's a bad idea, as you're still leaving tables open/locked. Even if you managed to set the permissions correctly (which is the problem from reading above) you'll still have to run myisamcheck on them ... so try doing a dump properly, and all will be good.

in other words, kill everything that has any connection to the databases, etc. whatsoever then try.

That's usually what "close anything that's accessing the database at the time (such as shutting down httpd first)" means. ;-)