Best way to compare MySQL database between 2 servers

anon091 · 09-18-2014, 10:54 AM

We just online cloned a MySQL server to a virtual machine, but what is the best way to compare the database on the real physical server to the clone we just made, to make sure they match (that nothing got messed up during the cloning)?

rigor · 09-18-2014, 01:03 PM

I can't presume to tell you the best way, but since the mysqldump command produces a text output file, two dump files can simply be diffed.

Habitual · 09-18-2014, 01:04 PM

Well,

Code:

mysqldump -uroot -p <db> > /path/to/file.sql

I trust and at the other end

Code:

mysql -uroot -p <db> < /path/to/file.sql

How was it cloned exactly?

anon091 · 09-18-2014, 01:21 PM

It was hot/online cloned to VMware, and I think mysqld was still running when they did it, so i'm kinda leery on if it actually worked right getting the db's, as we've never cloned anything before.

anon091 · 09-18-2014, 02:45 PM

OK, for one of the databases, i dumped them from each server, and diff'd them, here was the output

Quote:

365c365
< -- Dump completed on 2014-09-18 14:54:05
---
> -- Dump completed on 2014-09-18 15:20:17

So i think that means they are identical since the line numbers are the same in line 1 and lines 2 and 4 are just timestamps from the dumps, correct?

I have another one where it appears to have found a bunch of differences though based on how many times i had to scroll.

Habitual · 09-18-2014, 04:20 PM

Erm, I hate diff results, gives me a migraine.

Another method is to try mysqlhotcopy...
or creating the same dbs on the new host and stopping the source mysql server daemon and rsync the /var/lib/mysql/DIRs over to the new host...
or set the new host as a slave mysql-server...
or...
mysql> use <db>; show table status;

I've always used mysqldump and just let the interface(s) and the users (gasp) tell me what's what.
Easiest method is the one I tend to use/utilize.
Do 2 mysql dumps (back-to-back) and diff those results.
If they're the "same" then do an import.

Code:

# mysqldump mysql > file.sql
# mysqldump mysql > file.sql2
diff file.sql file.sql2
543c543
< -- Dump completed on 2014-09-18 14:18:46
---
> -- Dump completed on 2014-09-18 14:18:54

but these seem comparable to what you have already done.

anon091 · 09-18-2014, 04:32 PM

everything lately gives me a migraine, I feel your pain haha

Maybe i'll just try restoring the ones that show multiple changes from diff and pray that doesn't blow anything up. luckily the largest/most important database was the one I posted the results from earlier, so my stress level went from 10 to about a 6

anon091 · 09-23-2014, 12:45 PM

May have lucked out on this one, only 1 of the 8 db's had any differences other than the backup timestamp

deathsfriend99 · 09-23-2014, 12:49 PM

I've had problems with mysqldump not exporting or importing properly. I've always just rsync'd the /var/lib/mysql/<database> directory to the new server. It's fine as long as you aren't changing distros.

anon091 · 09-23-2014, 12:51 PM

It's a straight clone. The db in question is for a mediawiki site. do you think that one is safe to just rsync the directory for? I don't know much about mediawiki.

deathsfriend99 · 09-23-2014, 01:26 PM

I've never had a problem with using rsync for databases.
Just make sure you keep the same attributes and do an update. I'm assuming since you used vmware to clone, they have the same passwd, group, and shadow files, so doing a simple:

Code:

(I'm assuming the name of the database is mediawiki)
# rsync -avrlu /var/lib/mysql/mediawiki root@yournewmachine:/var/lib/mysql/mediawiki

Should do the trick.

Would be beneficial to stop mysqld services on both machines before the transfer.
Also, double check the ownership is correct on the destination before firing up the mysqld server

My setup is CentOS, so I'm not sure where other distros keep their mysql databases.
user:group for me is mysql:mysql

anon091 · 09-23-2014, 01:30 PM

cool, thanks. looking at the physical and clone mediawiki sites, I can't actually find a difference in the site. I wonder if the changes in the db were actually just from people reading its pages, so maybe I don't even need to do this.

Wish there was some tool that I could say, look at this site and all the stuff it links to, and compare it to this site and all the stuff it links to, and tell me what the differences are. cuz really if nothing is different for the users, then i'm good with it, know what I mean.

jlinkels · 09-23-2014, 07:02 PM

mysqldump was designed to dump a live, active database.
If you import the dumped file in the remote mysql server you have a guaranteed non-corrupt database.

When you do a diff of two dumps and it shows no differences you can be sure the databases are identical. However if you do encounter differences it doesn't mean something went wrong. Since you dumped a live database changes might have been added during or after the dump process.

rsyncing a live mysql directory is a bad idea. It might go well in 99% of the cases, but there is still a chance you end up with a corrupted database. It is simply the wrong way to do it.

I have never seen rsync itself making an error during copy.

jlinkels

anon091 · 09-24-2014, 08:12 AM

What I ended up doing, even though it sounds so silly, was to just look at the mediawiki site page by page, as displayed from both servers, and I actually couldn't find any visible differences on any of the pages. So not sure what the diffs were between the 2 db dumps I did, or what they signified, even though I could have missed it with my eyeball test.