Re: InnoDB and rsync
Am 25.01.2011 05:37, schrieb Robinson, Eric: Is there a way to safely backup an InnoDB database using rsync? Not without stop mysqld Foregt it, do not try it and stop searching if you do not waste time If you understand how innodb works you will see that this is not possible by design your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? you can get a fresgh backup of the master with the following method but nobody do that daily - this is only for init/reinit a salve * rsync on master in another local folder while running * stop master * remove bin-logs * second rsync to get last changes * start master * rsync to final destination When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
Data Wizard for MySQL 11.1 released
Hi! SQL Maestro Group announces the release of Data Wizard for MySQL 11.1, a powerful Windows GUI solution for MySQL data management. The new version is immediately available at http://www.sqlmaestro.com/products/mysql/datawizard/ Data Wizard for MySQL provides you with a number of easy-to-use wizards to convert any ADO-compatible database to the MySQL database, import data into MySQL tables, export data from tables, views and queries to most popular file formats as well as generate data-driven ASP.NET pages for your MySQL database. New features = 1. Data Import: import from ODBC data sources has been implemented. This allows you to import data from any database accessible via an ODBC driver or OLE DB provider to MySQL. 2. Data Import: the Insert-or-Update mode has been added. 3. Data Import: now it is possible to specify SQL scripts to be executed before and/or after data import to each table. 4. Data Pump: starting with this version it is possible to define naming conventions for transferring metadata objects. 5. Data Pump: new version allows you to restrict number of pumped records, view source table data as well as count number of records in the source tables. 6. Data Export: when exporting to MS Access data from all the exported tables/views/queries are stored to a single target database. 7. Common: this release has been successfully tested with 64-bit operating systems including Windows 7 x64. In addition to this, several bugs have been fixed and some other minor improvements and corrections have been made. Full press-release is available at: http://www.sqlmaestro.com/news/company/data_wizards_are_updated_to_version_11_1/ Background information: --- SQL Maestro Group offers complete database admin, development and management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, The SQL Maestro Group Team http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all.) If you understand how innodb works you will see that this is not possible by design I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
2011/1/25 Robinson, Eric eric.robin...@psmnv.com: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! However, this could be a solution for your problem (maybe) http://www.pythian.com/news/5113/video-building-a-mysql-slave-and-keeping-it-in-sync/ if you watch the movie, at ~40 minutes, you can see a slide What causes slave to get out of sync... Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On Tue, Jan 25, 2011 at 3:00 PM, Robinson, Eric eric.robin...@psmnv.comwrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The Interesting. I never heard of that, and can't, at first glance, seem to find a lot of useful things on Google. Could you explain what you mean ? The one thing I can think of, would be the fact that your rows are not guaranteed to be in the same disk blocks, or even necessarily in the same data block of your file. This in itself doesn't really pose a problem for backups, though, afaik ? I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no It doesn't, exactly, no; but afaik no actual data will be written. Some metadata may not be fully sync, but I do not believe a lot could happen that the recovery when you start your slave can't fix. Still, the issue is there. really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) As a stopped MySQL can't update the files or keep data in memory, that should be safe, yes. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
Am 25.01.2011 15:00, schrieb Robinson, Eric: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. There is nothing drifting and nobody cares if the files on both servers are binary identical, the data must be consistent and it is binlog-format = ROW For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. sorry but these people have no plan My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves jesus christ nobody cares if they are binary replica as long as the data is consistent and ident I fail to see how this is crippled. It is crippled because you do not understand the sense of replication if you reinit it every day See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. so start another slave on the machine with his own socket for backups, i have running on all dedicated backup-servers two instances - one is useable r/w and the other one without tcp is the replication-slave, every hour the salve is stopped and datadir mirrored to the r/w-instance Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all. and if you running a clean solution the salves are never down but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? no, it is a database and not designed for access from external software as long as the database is running Is stopping the service really the only way? yes, and not only for innodb try to copy oracle, postgresql, ms-sql :-) if you do not stop the service you can be sure that the backup is not useable or missing data, even if there would exist a mode sync all to disk nobody would officially support copy datafiles while the service is running, even with myisam nobody will do that And even if I stop the service, is rsync totally safe with InnoDB? why not? the server is down and you copy the whole datadir what can be unsafe there? signature.asc Description: OpenPGP digital signature
Re: InnoDB and rsync
jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
On 1/25/2011 8:00 AM, Robinson, Eric wrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? I looked at Maatkit a year or so ago. It looked pretty interesting, but then I started reading the disclaimers carefully and they scared the bejeepers out of me. Warnings about data corruption and whatnot. I'll check it out again. (I was actually looking for it this morning but could not remember the name, so thanks for the reminder.) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for st...@internetretailconnection.com,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 25.01.2011 15:56, schrieb Johan De Meersman: jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. Where exactly do you see any problem? * the master writes his bin-log in row format * the slaves writes his relay-log and managing inserts updates * lvm makes a snapshot of relay-log / datafiles There is none and if there could be one LVM must be broken because a snapshot has to be consistent So if you stop the slave, make the snahpshot and start the slave again there are all buffers written to the vfs-layer and the snapshot must have a defined state. Only if the db-server is running and have some data in memory cache you could have any troubles and that is why not copy the files as long the server is running Nobody out there makes a copy of database files while the server is running, really nobody! -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, January 25, 2011 9:56 AM To: Reindl Harald Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: InnoDB and rsync jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. [JS] I don't get it. Isn't this like saying that you can't substitute a dictionary with a red cover for a dictionary with a blue cover? If you do a complete LVM restore, then you have complete and (hopefully) consistent copy of your files, even if the volume images were not identical; in fact, they almost certainly will NOT be identical at the disk-image level. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
nobody cares if they are binary replica as long as the data is consistent and ident Like I said, I'm no expert on this, but my approach seems like the only way to 100% absolutely sure that the data on the slave is in fact consistent and identical to the data on tha master. so start another slave on the machine with his own socket for backups You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 25.01.2011 16:56, schrieb Robinson, Eric: You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. 240 mysql-servers? why there is no consolidation? Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. this is simply impossible -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Mon, Jan 24, 2011 at 6:43 PM, Gavin Towey gto...@ffn.com wrote: If you show the EXPLAIN SELECT .. output, and the table structure, someone will be able to give a more definite answer. Thanks for the reply Gavin. I actually did place this info in my very first message on this thread, along with my basic table structure and server version. Myself and others have just stopped keeping the full, deeply-nested, quoted thread inside all subsequent messages which is why you probably haven't seen it. However, here is the EXPLAIN SELECT from the first message (reformatted for email): select_type: SIMPLE table: recipients type: ref possible_keys: messages_fk, employee_idx key: employee_idx key_len: 5 ref: const rows: 222640 Extra: Using where; Using temporary; Using filesort select_type: SIMPLE table: messages type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: email_archive.recipients.message_id rows: 1 Extra: Anyhow, having now copied these tables to another server (MySQL 5.1) and done some tests (bumping up innodb_buffer_pool_size and playing with innodb_flush_log_at_trx_commit for my writes and a few other knobs) it is simply that these somewhat large tables need lots of RAM to perform well, just as Reindl Harald originally pointed out. Thanks again for the help everyone! -- Kendall Gifford zettab...@gmail.com
Re: InnoDB and rsync
Am 25.01.2011 18:38, schrieb Robinson, Eric: 240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). 240 mysql-instances on 3 physical hosts? what crazy setup is this please? sorry but your smallest problem is really innodb per rsync just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances - i would think that not all others are ghost-drivers and nobody outside mysql would ever think of backup a running db-server signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-instances on 3 physical hosts? what crazy setup is this please? Processors average 90% idle, peaks are low, iowait is low, the system is not swapping, response time is good, and our users are happy all around the country. What is crazy about that? The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances I don't know how the rest of the world does it, but we have been doing it like this since 2006 and it has worked great and we have never regretted having multiple instances of mysql. In fact, it is really great because we can maintain each customer's service individually, stop and start mysql without affecting other customers, turn query logs on and off for each customer, customize performance parameters, and so on. I can maintain a customer's database right in the middle of a production day and the other customers won't even notice! It has been great being able to do all these things. outside mysql would ever think of backup a running db-server Then you're just not Googling very well. :-) --Eric Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 09:00, Robinson, Eric wrote: ... I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html Look for the section talking about clean backups. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. Sorry, no. The software is a canned medical application so we cannot easily inspect the queries that could have been causing the problem. Even though we could capture them in various ways (sniffer, proxy, query logs) it would not be easy to isolate the culprits out of the tens of thousands issued every day. And it was a year or more ago. We have not had the problem since we started rsyncing. :-) Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Robinson, Eric,Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
