Best way to compare MySQL database between 2 servers
Linux - ServerThis forum is for the discussion of Linux Software used in a server related context.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Best way to compare MySQL database between 2 servers
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)?
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.
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.
Last edited by Habitual; 09-19-2014 at 08:29 AM.
Reason: s/Eesiest/Easiest
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
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.
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.
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
Last edited by deathsfriend99; 09-23-2014 at 01:28 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.
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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.
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.