Rsync and mysql

sci3ntist · 01-12-2010, 03:15 AM

Hello,
Im trying to migrate mysql to a new server, the first server is FreeBSD 6.2 and the second server is ubuntu, so I used rsync for that, on ubuntu:
show database; works successfully and shows the database imported from the FreeBSD box, show tables; also works, but select data from any didn't work, I keep getting "ERROR 1146 (42S02): Table 'DATABASE.TABLENAME' doesn't exist", although .frm .MYI .MYD are available in /var/lib/mysql/databasename

Thanks in advance.

centosboy · 01-12-2010, 04:55 AM

Quote:

Originally Posted by sci3ntist

Hello,
Im trying to migrate mysql to a new server, the first server is FreeBSD 6.2 and the second server is ubuntu, so I used rsync for that, on ubuntu:
show database; works successfully and shows the database imported from the FreeBSD box, show tables; also works, but select data from any didn't work, I keep getting "ERROR 1146 (42S02): Table 'DATABASE.TABLENAME' doesn't exist", although .frm .MYI .MYD are available in /var/lib/mysql/databasename

Thanks in advance.

are both source and destination databases the same versions? can you confirm this?
i think mysqldump is best for this.
why?? because it actually creates the database/tables on the new location in the proper format and structure that was used originally and will also drop the databases/tables if they already exist in the new location first..

Code:

mysqldump -uuser -ppassword database (table) | mysql -h host -uuser -ppassword database (table)

sci3ntist · 01-12-2010, 06:43 AM

Thanks for the reply, but doing it over rsync would be easier for me, Ill figure out writing a script for that.