MySQL replication server
Hi All sorry to bother everyone again. but now I have a question from a client which I am sure about my answer, however need to confirm. When setting up a master/slave replication set. As I understand it, the slave server can't accept any writes, however it will be able to accept reads. Is this correct, or will the slave server still be able to accept writes as well (even though it may not be replicated) ? Kind Regards Machiel
Re: MySQL replication server
You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: Hi All sorry to bother everyone again. but now I have a question from a client which I am sure about my answer, however need to confirm. When setting up a master/slave replication set. As I understand it, the slave server can't accept any writes, however it will be able to accept reads. Is this correct, or will the slave server still be able to accept writes as well (even though it may not be replicated) ? Kind Regards Machiel -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: MySQL replication server
Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL replication server
My reason for asking this is the following The client have database A on one machine, Database B on a second machine both of which are production. They want to setup replication of Database B to Server hosting Database A and still keep Server A as the primary production system. -Original Message- From: a.sm...@ukgrid.net To: John Daisley daisleyj...@googlemail.com Cc: Machiel Richards machi...@rdc.co.za, mysql mailing list mysql@lists.mysql.com Subject: Re: MySQL replication server Date: Mon, 22 Nov 2010 13:03:38 + Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:
Re: MySQL replication server
The replicated database should not be accepting writes, if it is then you haven't set it up correctly On 22 November 2010 13:03, a.sm...@ukgrid.net wrote: Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: MySQL replication server
Additionally, if a user has the SUPER privilege (eg. all privileges on *.*) they can write to a database running in read-only mode. Yet another reason to never allow this privilege for general purpose users. Tyler On 11/22/10 8:08 AM, John Daisley wrote: The replicated database should not be accepting writes, if it is then you haven't set it up correctly On 22 November 2010 13:03,a.sm...@ukgrid.net wrote: Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisleydaisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richardsmachi...@rdc.co.za wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL replication server
Hi, so yes you can do that, but then I guess you cannot set the server hosting database A as readonly (from memory this can only be set server wide, but worht checking it out). Which might leave you a few options to ensure data integrity, for example simply by user security either by disabling access to relevant users or via setting grants appropriately. Or you could look at a second MySQL instance on the database A server either listening on another port or in a virtual server/zone/jail, Andy. Quoting Machiel Richards machi...@rdc.co.za: My reason for asking this is the following The client have database A on one machine, Database B on a second machine both of which are production. They want to setup replication of Database B to Server hosting Database A and still keep Server A as the primary production system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL replication server
On Mon, Nov 22, 2010 at 2:08 PM, John Daisley daisleyj...@googlemail.comwrote: The replicated database should not be accepting writes, if it is then you haven't set it up correctly *shrug* I never bother. The slave is way too useful to fuck around with optimisations and whatnot, reporting tools tend to do useful aggregations, et cetera. You may like to set it read-only, but that doesn't make it the only way, let a lone a requirement. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel