A client ask me to migrate his MySQL DB: The server doesn't have free space and it also has a very big table that is broken, so I can't dump it.
I can't REPAIR it because of lacking of free space.
Question : Is there a way to physically move MySQL DB data files to another server and use them for the "new" MySQL ?
Here is what you can do:
STEP 01) On the Old Server, service mysql stop
STEP 02) On the Old Server, rsync -av --progress /var/lib/mysql [email protected]:/var/lib/mysql
STEP 03) On the New Server, chown -R mysql:mysql /var/lib/mysql
STEP 04) On the New Server, service mysql start
Make sure /var/lib/mysql on the New Server is on a much bigger disk mount
/var/lib/mysql so when its finished I just have to upload that to New DB Server. Thanks.
Commented
Mar 11, 2013 at 4:05
/var/lib/mysql. And I know that mysql already has a mysql in it. So I had both /var/lib/mysql/mysql and /var/lib/mysql/mysql/mysql
You can also migrate it on-the-fly to another server, sending data through pipes and SSH. It's useful if the target host have a different MySQL version, for example.
I just wrote a blog post explaining how I did it:
http://techutils.bcmedeiros.dev/2016/02/on-fly-database-migration-between-two.html
There is an explanation there, but if you don't care, just run something like below.
Target host:
nc -l 3456 | \
gunzip | \
pv | \
mysql -u tdb_user -ptdb_pass targetdatabase
Source host:
mysqldump -u sdb_user -psdb_pass sourcedatabase | \
pv | \
gzip | \
ssh sshuser@targethost nc 127.0.0.1 3456
