Re: replication question replacing the master
2014/1/17 Richard Reina gatorre...@gmail.com I have 3 relay MySQL database servers on my small office LAN backing up a master and 3 more machines backing up each relay (1 each). They are all replicating all databases and all tables. The master although running fine is almost eight years old. I'm thinking it's probably time to make one of the relays the master, but I've never done this before. I want the new master to have the IP address of the old master 192.168.0.1 . To make the change I was going to take the master off the LAN and shutdown mysql on all the machines, change the IP address on the chosen relay to that of the master 192.168.0.1, then restart mysql on all the machines. I always refer to the machines by their IP addresses and never by their hostnames. Once I successfully make the change I was planning on making the old master a relay since it is still working fine. Will this plan work ok? Is there a better or easier way? If the three machines are sync'ed and have consistent data I don't see the need of stopping MySQL: - Stop whatever writes to your current master - Once you are completely sure there are no writes in your current master, set it to read_only = ON - In the slave which will become the master, get the logfile and current position with: show master status; - Set the new IP in the new master Using the position taken in the new master go to the slaves machines and: stop slave; change master to master_host='IP', master_log_file='whatever_file_name_you_got', master_log_pos=whatever_number_you_got, master_user='replication_or_whatever_you_have', master_password='replication_or_whatever_you_have'; start slave; - Set read_only = OFF in your new master - Start your application so you can start getting writes again. As soon as you get writes if you do a show master status; in the new master you should see the position going forward. I see that faster than any other thing. Hope this helps Manuel.
Re: replication question replacing the master
Manuel, Thank you very much for this information. This sounds like a very good strategy. I think I will try switching some slaves from one relay to another to familiarize myself and get practice and them do it to deploy a new master. Again, thank you very much. Richard El Jan 18, 2014, a las 2:00 AM, Manuel Arostegui man...@tuenti.com escribió: 2014/1/17 Richard Reina gatorre...@gmail.com I have 3 relay MySQL database servers on my small office LAN backing up a master and 3 more machines backing up each relay (1 each). They are all replicating all databases and all tables. The master although running fine is almost eight years old. I'm thinking it's probably time to make one of the relays the master, but I've never done this before. I want the new master to have the IP address of the old master 192.168.0.1 . To make the change I was going to take the master off the LAN and shutdown mysql on all the machines, change the IP address on the chosen relay to that of the master 192.168.0.1, then restart mysql on all the machines. I always refer to the machines by their IP addresses and never by their hostnames. Once I successfully make the change I was planning on making the old master a relay since it is still working fine. Will this plan work ok? Is there a better or easier way? If the three machines are sync'ed and have consistent data I don't see the need of stopping MySQL: - Stop whatever writes to your current master - Once you are completely sure there are no writes in your current master, set it to read_only = ON - In the slave which will become the master, get the logfile and current position with: show master status; - Set the new IP in the new master Using the position taken in the new master go to the slaves machines and: stop slave; change master to master_host='IP', master_log_file='whatever_file_name_you_got', master_log_pos=whatever_number_you_got, master_user='replication_or_whatever_you_have', master_password='replication_or_whatever_you_have'; start slave; - Set read_only = OFF in your new master - Start your application so you can start getting writes again. As soon as you get writes if you do a show master status; in the new master you should see the position going forward. I see that faster than any other thing. Hope this helps Manuel.
replication question replacing the master
I have 3 relay MySQL database servers on my small office LAN backing up a master and 3 more machines backing up each relay (1 each). They are all replicating all databases and all tables. The master although running fine is almost eight years old. I'm thinking it's probably time to make one of the relays the master, but I've never done this before. I want the new master to have the IP address of the old master 192.168.0.1 . To make the change I was going to take the master off the LAN and shutdown mysql on all the machines, change the IP address on the chosen relay to that of the master 192.168.0.1, then restart mysql on all the machines. I always refer to the machines by their IP addresses and never by their hostnames. Once I successfully make the change I was planning on making the old master a relay since it is still working fine. Will this plan work ok? Is there a better or easier way? Thanks for you attention. Richard
Re: replication question replacing the master
Am 17.01.2014 22:42, schrieb Richard Reina: I have 3 relay MySQL database servers on my small office LAN backing up a master and 3 more machines backing up each relay (1 each). They are all replicating all databases and all tables. The master although running fine is almost eight years old. I'm thinking it's probably time to make one of the relays the master, but I've never done this before. I want the new master to have the IP address of the old master 192.168.0.1 . To make the change I was going to take the master off the LAN and shutdown mysql on all the machines, change the IP address on the chosen relay to that of the master 192.168.0.1, then restart mysql on all the machines. I always refer to the machines by their IP addresses and never by their hostnames. Once I successfully make the change I was planning on making the old master a relay since it is still working fine. Will this plan work ok? Is there a better or easier way? * stop the master * sync the complete datadir to the new machine * give the new machine the same ip * start mysqld how should the salve smell that anything has changed? signature.asc Description: OpenPGP digital signature
Re: Replication question
On 24/07/2013 19:52, Rick James wrote: 4) 3 tables from the slaves are to be replicated back to the master NO. However, consider Percona XtraDb Cluster or MariaDB+Galera. They allow multiple writable masters. But they won't let you be so selective about tables not being replicated. Here are the gotchas for Galera usage: http://mysql.rjweb.org/doc.php/galera If you can live with them (plus replicating everything), it may be best for you. Ok thanks Rick for confirming my initial gut feelings about this...! Will have to implement a manual process to push the required data back to the master. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication question
I have been asked to set up multiple database replication which I have done before for simple cases however there are some nuances with this instance that add some complexity and I'd like to hear your collective expertise on this proposed scenario:- 1) Single master database 2) n (probably 3 to start with) number of slave databases 3) All but 5 tables (123 tables in total) are to be replicated from the master to all the slaves 4) 3 tables from the slaves are to be replicated back to the master It is mainly item 4) that concerns me - the primary ID's are almost certain to collide unless I seed the auto increment ID to partition the IDs into separate ranges or does MySQL handle this issue? There are some foreign keys on one of the 3 slave to master tables but they are pointing at some extremely static tables that are very unlikely to change. Is the above a feasible implementation...? Thanks in advance for any advice/pointers! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Replication question
4) 3 tables from the slaves are to be replicated back to the master NO. However, consider Percona XtraDb Cluster or MariaDB+Galera. They allow multiple writable masters. But they won't let you be so selective about tables not being replicated. Here are the gotchas for Galera usage: http://mysql.rjweb.org/doc.php/galera If you can live with them (plus replicating everything), it may be best for you. -Original Message- From: rich gray [mailto:r...@richgray.com] Sent: Wednesday, July 24, 2013 8:21 AM To: mysql@lists.mysql.com Subject: Replication question I have been asked to set up multiple database replication which I have done before for simple cases however there are some nuances with this instance that add some complexity and I'd like to hear your collective expertise on this proposed scenario:- 1) Single master database 2) n (probably 3 to start with) number of slave databases 3) All but 5 tables (123 tables in total) are to be replicated from the master to all the slaves 4) 3 tables from the slaves are to be replicated back to the master It is mainly item 4) that concerns me - the primary ID's are almost certain to collide unless I seed the auto increment ID to partition the IDs into separate ranges or does MySQL handle this issue? There are some foreign keys on one of the 3 slave to master tables but they are pointing at some extremely static tables that are very unlikely to change. Is the above a feasible implementation...? Thanks in advance for any advice/pointers! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Chain Replication QUestion
To activate log-slave-updates do I just add log-slave-updates to the my.cnf file? 2013/4/30, Manuel Arostegui man...@tuenti.com: 2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Chain Replication QUestion
That is correct. On Mon, May 6, 2013 at 11:06 AM, Richard Reina gatorre...@gmail.com wrote: To activate log-slave-updates do I just add log-slave-updates to the my.cnf file? 2013/4/30, Manuel Arostegui man...@tuenti.com: 2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: Chain Replication QUestion
Hello Manuel, Thank you for your reply. Could I do the following?: 1) Enable log-bin on master2 (slave that will be converted to a master) 2) Enable log-slave-updates on master2 3) Execute CHANGE MASTER to on another existing slave so that it gets it's updates from master2 instead of master1. Thanks for the help thus far. 2013/4/30, Manuel Arostegui man...@tuenti.com: 2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Chain Replication QUestion
If you're able to use MySQL 5.6 and enable GTIDs then it gets a whole lot simpler as you don't need to worry about finding the correct positions in the binary logs. Take a look at http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/ and http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ Andrew. -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: 01 May 2013 16:29 To: Richard Reina; Manuel Arostegui Cc: mysql@lists.mysql.com Subject: RE: Chain Replication QUestion 1) Enable log-bin on master2 (slave that will be converted to a master) That does not 'convert' it -- it makes it both a Master and a Slave (a Relay). The CHANGE MASTER is probably correct, but it is difficult to find the right spot. A simple way is to 1. Stop all writes everywhere. 2. Wait for replication to catchup everywhere. 3. FLUSH LOGS everywhere. 4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the freshly created binlog in the machine that is the Slave's new Master. 5. Start writes. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Wednesday, May 01, 2013 6:00 AM To: Manuel Arostegui Cc: mysql@lists.mysql.com Subject: Re: Chain Replication QUestion Hello Manuel, Thank you for your reply. Could I do the following?: 1) Enable log-bin on master2 (slave that will be converted to a master) 2) Enable log-slave-updates on master2 3) Execute CHANGE MASTER to on another existing slave so that it gets it's updates from master2 instead of master1. Thanks for the help thus far. 2013/4/30, Manuel Arostegui man...@tuenti.com: 2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Chain Replication QUestion
1) Enable log-bin on master2 (slave that will be converted to a master) That does not 'convert' it -- it makes it both a Master and a Slave (a Relay). The CHANGE MASTER is probably correct, but it is difficult to find the right spot. A simple way is to 1. Stop all writes everywhere. 2. Wait for replication to catchup everywhere. 3. FLUSH LOGS everywhere. 4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the freshly created binlog in the machine that is the Slave's new Master. 5. Start writes. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Wednesday, May 01, 2013 6:00 AM To: Manuel Arostegui Cc: mysql@lists.mysql.com Subject: Re: Chain Replication QUestion Hello Manuel, Thank you for your reply. Could I do the following?: 1) Enable log-bin on master2 (slave that will be converted to a master) 2) Enable log-slave-updates on master2 3) Execute CHANGE MASTER to on another existing slave so that it gets it's updates from master2 instead of master1. Thanks for the help thus far. 2013/4/30, Manuel Arostegui man...@tuenti.com: 2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Chain Replication QUestion
2013/4/30 Richard Reina gatorre...@gmail.com I have a few slaves set up on my local network that get updates from my main mysql database master. I was hoping to turn one into a master while keeping it a slave so that I can set up a chain. Does anyone know where I can find a how to or other documentation for this specific task? It is quite easy: Enable log-slave-updates in the slave you want to be a master. Do a mysqldump -e --master-data=2 and put that mysqldump in the future slaves. Take a look at the first lines of the mysqldump where you'll find the position and logfile those slaves need to start the replication from. You can also use xtrabackup if you like. Manuel.
Re: Replication Question
2012/10/25 Sabika M sabika.makhd...@gmail.com I have replication setup in the following way: A - B -C I am making updates to server A. I want to stop all my updates and point them to server C. After I start writing to server C, can I use the change master statement to make the C the master of A (take B out of the topology) and proceed to set up peer-to-peer between A - C without taking a new backup of c to set up the replication between A-C? I guess what I am really asking is if the data is the same, is a backup required for initialization? Hello, Make sure you run a show master status at C before sending the writes to it so you can get to know at which position/binlog you have to start A replicating from. Also make sure read_only = OFF in C. Manuel.
Replication Question
I have replication setup in the following way: A - B -C I am making updates to server A. I want to stop all my updates and point them to server C. After I start writing to server C, can I use the change master statement to make the C the master of A (take B out of the topology) and proceed to set up peer-to-peer between A - C without taking a new backup of c to set up the replication between A-C? I guess what I am really asking is if the data is the same, is a backup required for initialization?
RE: Replication Question
Sound right. Be sure there are no writes during certain critical times. And that replication is caught up. If you want to verify the sameness see Percona's pt-table-checksum. -Original Message- From: Sabika M [mailto:sabika.makhd...@gmail.com] Sent: Thursday, October 25, 2012 10:16 AM To: MySql Subject: Replication Question I have replication setup in the following way: A - B -C I am making updates to server A. I want to stop all my updates and point them to server C. After I start writing to server C, can I use the change master statement to make the C the master of A (take B out of the topology) and proceed to set up peer-to-peer between A - C without taking a new backup of c to set up the replication between A- C? I guess what I am really asking is if the data is the same, is a backup required for initialization? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication question: How to make a slave a master?
I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? Thanks, Richard
Re: Replication question: How to make a slave a master?
Am 24.08.2012 17:25, schrieb Richard Reina: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? add the same master-conig lines as you did on the other master there is no magic, master-configurazion does not have any impact to slave-operation and vice versa signature.asc Description: OpenPGP digital signature
RE: Replication question: How to make a slave a master?
Pointing the other slave to the new master is a bit tricky. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 8:29 AM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? Am 24.08.2012 17:25, schrieb Richard Reina: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? add the same master-conig lines as you did on the other master there is no magic, master-configurazion does not have any impact to slave-operation and vice versa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication question: How to make a slave a master?
what would be tricky? remove all relay-logs, remove master.info adn that was it done this many times in the last years and it takes 30 seconds if you are fast enough to type the slave-commands Am 24.08.2012 23:35, schrieb Rick James: Pointing the other slave to the new master is a bit tricky. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 8:29 AM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? Am 24.08.2012 17:25, schrieb Richard Reina: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? add the same master-conig lines as you did on the other master there is no magic, master-configurazion does not have any impact to slave-operation and vice versa -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
RE: Replication question: How to make a slave a master?
It's the sequence that is tricky. 1. STOP SLAVE 2. CHANGE MASTER 3. START SLAVE Do it wrong, and you miss or duplicate replication stuff that happens between #1 and #2. Could you please lay out the precise steps, so that I can understand how un-tricky it can be. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 2:43 PM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? what would be tricky? remove all relay-logs, remove master.info adn that was it done this many times in the last years and it takes 30 seconds if you are fast enough to type the slave-commands Am 24.08.2012 23:35, schrieb Rick James: Pointing the other slave to the new master is a bit tricky. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 8:29 AM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? Am 24.08.2012 17:25, schrieb Richard Reina: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? add the same master-conig lines as you did on the other master there is no magic, master-configurazion does not have any impact to slave-operation and vice versa -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication question: How to make a slave a master?
* stop mysqld * rm -f mysql-relay-bin* * rm -f master.info * rm -f relay-log.info * start mysqld so, and now your slave is no longer any slave to make sure you are binary-identical with the new master stop the new master, remove all it's BINLOGS, not its relay-logs, stop the new salve, rsync the data, start both mywqld and start replication from slave this is really easy after you understand what each file in the datadir is supposded to do Am 24.08.2012 23:53, schrieb Rick James: It's the sequence that is tricky. 1. STOP SLAVE 2. CHANGE MASTER 3. START SLAVE Do it wrong, and you miss or duplicate replication stuff that happens between #1 and #2. Could you please lay out the precise steps, so that I can understand how un-tricky it can be. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 2:43 PM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? what would be tricky? remove all relay-logs, remove master.info adn that was it done this many times in the last years and it takes 30 seconds if you are fast enough to type the slave-commands Am 24.08.2012 23:35, schrieb Rick James: Pointing the other slave to the new master is a bit tricky. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, August 24, 2012 8:29 AM To: mysql@lists.mysql.com Subject: Re: Replication question: How to make a slave a master? Am 24.08.2012 17:25, schrieb Richard Reina: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? add the same master-conig lines as you did on the other master there is no magic, master-configurazion does not have any impact to slave-operation and vice versa -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Replication question: How to make a slave a master?
Are you trying to promote a slave as a new master and replace current master or create intermediate slave? If it is the latter all you need to do is to: 1. Stop the slave 2. Add log_slave_updates = 1 in the slave's config file 3. Copy the slave files to the new slave(s) 4. Start your intermediate slave 5. Add replication user on your intermediate slave and allow new slaves to replicate 6. Start your slave(s) of intermediate slave 7. Issue following on your new slaves: CHANGE MASTER TO master_log_file = 'mysql-bin.01', master_log_pos = 4; On Aug 24, 2012, at 11:25 AM, Richard Reina wrote: I have a couple of mysql database slaves and would like to make one of them be a master as well so that I can set another machine to replicate from it. Can anyone tell me how I should go about it or know of any howtos for this specific task? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
replication question
-- Richard Reina Rush Logistics, Inc. Watch our 3 minute movie: http://www.rushlogistics.com/movie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Another replication question
really really a little noisy. 这下犯众怒了吧，招人烦了~ 囧 Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/29 杨涛涛 david.y...@actionsky.com: Ok, I'll not post any more! Just reading! David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 在 2010年12月23日 下午8:14，Johan De Meersman vegiv...@tuxera.be写道： Glad to hear I'm not the only one annoyed :-) I've plonked him in the meantime. 2010/12/23 Jorg W Young jorgwyoung...@gmail.comjorgwyoung%2...@gmail.com jorgwyoung%2...@gmail.com jorgwyoung%252...@gmail.com This guy has been saying nothing meaningful on this list, but advertise his blog everywhere. Just be shame. He should be kicked out from the list. Jorg. 2010/12/23 杨涛涛 david.y...@actionsky.com: This way is very well, but it has to do lots of human work. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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: Another replication question
Ok, I'll not post any more! Just reading! David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 在 2010年12月23日 下午8:14，Johan De Meersman vegiv...@tuxera.be写道： Glad to hear I'm not the only one annoyed :-) I've plonked him in the meantime. 2010/12/23 Jorg W Young jorgwyoung...@gmail.comjorgwyoung%2...@gmail.com jorgwyoung%2...@gmail.com jorgwyoung%252...@gmail.com This guy has been saying nothing meaningful on this list, but advertise his blog everywhere. Just be shame. He should be kicked out from the list. Jorg. 2010/12/23 杨涛涛 david.y...@actionsky.com: This way is very well, but it has to do lots of human work. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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: Another replication question
This guy has been saying nothing meaningful on this list, but advertise his blog everywhere. Just be shame. He should be kicked out from the list. Jorg. 2010/12/23 杨涛涛 david.y...@actionsky.com: This way is very well, but it has to do lots of human work. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.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: Another replication question
Glad to hear I'm not the only one annoyed :-) I've plonked him in the meantime. 2010/12/23 Jorg W Young jorgwyoung...@gmail.com jorgwyoung%2...@gmail.com This guy has been saying nothing meaningful on this list, but advertise his blog everywhere. Just be shame. He should be kicked out from the list. Jorg. 2010/12/23 杨涛涛 david.y...@actionsky.com: This way is very well, but it has to do lots of human work. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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: Another replication question
This way is very well, but it has to do lots of human work. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 2010/11/24 Rolando Edwards redwa...@logicworks.net MySQL, by design, cannot do that. A DB Server can be Master to Multiple Slaves Think of the CHANGE MASTER TO command. Its internal design cannot accommodate reading from more than one set of relay logs. You could attempt something convoluted, like 1) STOP SLAVE; 2) CHANGE MASTER TO Master 1 3) START SLAVE; 4) Read and process some binary log transactions, wait till you are zero sec behind master 5) STOP SLAVE; 6) CHANGE MASTER TO Master 2 7) START SLAVE; 8) Read some process binary log transactions, wait till you are zero sec behind master 9) Repeat from step 1 Make sure Each Master is updating only one specific set of databases, mutual exclusive from other Masters Make sure you properly record the log file and log position from each master I would never try this under normal circumstances. I think this was described in the High Performance MySQL book http://www.amazon.com/dp/0596101716?tag=xaprb-20 Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Wednesday, November 24, 2010 7:20 AM To: mysql mailing list Subject: Another replication question Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel
Another replication question
Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel
Re: Another replication question
You can't, plain and simple - a slave may only have a single master. You could script binlog shipping, I suppose, if you're so inclined, and apply them on the slave. No guarantees about it working as expected, though. On Wed, Nov 24, 2010 at 1:20 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel -- 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: Another replication question
Johan, Are you sure mmm couldn't handle this? Machiel, Maybe you could `simulate` the whole thing using some federated tables on one of the slave instances? Then although you are replicating to 3 instances you could access all the data from a single instance? Regards John On 24 November 2010 13:08, Johan De Meersman vegiv...@tuxera.be wrote: You can't, plain and simple - a slave may only have a single master. You could script binlog shipping, I suppose, if you're so inclined, and apply them on the slave. No guarantees about it working as expected, though. On Wed, Nov 24, 2010 at 1:20 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: Another replication question
On Wed, Nov 24, 2010 at 2:50 PM, John Daisley daisleyj...@googlemail.comwrote: Are you sure mmm couldn't handle this? That, I don't know, but MySQL's internal replication mechanisms definitely don't support multimaster slaves. If mmm does it, it'll likely be akin to the offline log shipping I proposed earlier. Maybe you could `simulate` the whole thing using some federated tables on one of the slave instances? Then although you are replicating to 3 instances you could access all the data from a single instance? Purely with federated tables, you're not actually replicating. You'd have the data visible on the slave, but it would be useless for failover/backup; and it would be less than optimal for querying since you'll be pulling all the data off the primaries on every select anyway. -- 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: Another replication question
MySQL, by design, cannot do that. A DB Server can be Master to Multiple Slaves Think of the CHANGE MASTER TO command. Its internal design cannot accommodate reading from more than one set of relay logs. You could attempt something convoluted, like 1) STOP SLAVE; 2) CHANGE MASTER TO Master 1 3) START SLAVE; 4) Read and process some binary log transactions, wait till you are zero sec behind master 5) STOP SLAVE; 6) CHANGE MASTER TO Master 2 7) START SLAVE; 8) Read some process binary log transactions, wait till you are zero sec behind master 9) Repeat from step 1 Make sure Each Master is updating only one specific set of databases, mutual exclusive from other Masters Make sure you properly record the log file and log position from each master I would never try this under normal circumstances. I think this was described in the High Performance MySQL book http://www.amazon.com/dp/0596101716?tag=xaprb-20 Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Wednesday, November 24, 2010 7:20 AM To: mysql mailing list Subject: Another replication question Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel
RE: Another replication question
If you use a chain of masters, you can accomplish the same effect: Master1-Master2-Master-3-Slave However I don't recommend this since the more links you have in a replication chain, the easier it is to break it in a way that's very not-fun to deal with. -Original Message- From: Rolando Edwards [mailto:redwa...@logicworks.net] Sent: Wednesday, November 24, 2010 6:53 AM To: Machiel Richards; mysql mailing list Subject: RE: Another replication question MySQL, by design, cannot do that. A DB Server can be Master to Multiple Slaves Think of the CHANGE MASTER TO command. Its internal design cannot accommodate reading from more than one set of relay logs. You could attempt something convoluted, like 1) STOP SLAVE; 2) CHANGE MASTER TO Master 1 3) START SLAVE; 4) Read and process some binary log transactions, wait till you are zero sec behind master 5) STOP SLAVE; 6) CHANGE MASTER TO Master 2 7) START SLAVE; 8) Read some process binary log transactions, wait till you are zero sec behind master 9) Repeat from step 1 Make sure Each Master is updating only one specific set of databases, mutual exclusive from other Masters Make sure you properly record the log file and log position from each master I would never try this under normal circumstances. I think this was described in the High Performance MySQL book http://www.amazon.com/dp/0596101716?tag=xaprb-20 Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Wednesday, November 24, 2010 7:20 AM To: mysql mailing list Subject: Another replication question Hi All I am back once again with another replication question (maybe this can also be handled by MMM but not sure) this time for a different client. We are trying to find out how to setup 3 different masters to replicate to a single slave server (without the need to have 3 different instances running on the slave machine). Does anybody have any ideas? Any ideas will be greatly appreciated. Regards Machiel This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com
Re: Master/Slave Replication Question
from what i've read and seen geographical load balancer works as: multiple DNS A records routes to multiple Apache Servers(mod_php tucks in as a module under Apache) each web servers would connect to MySQL on their own localhost 1.2.3.4-WebServer would communicate directly to 1.2.3.4-MySQL (localhost) master-slave configurations imply A1-MySql master replicate to A2-MySql slave how should this new replication be handled? You are correct about the DNS load balancing and connecting to localhost. Here is the actual set-up I'm working with: We have three LAMP servers: www-01, www-02 and www-03. Currently, mySQL on www-01 replicates to www-02, which replicates to www-03, which replicates back to www-01. What I'd like to do is have www-01 be the authoritative server, and have www-02 and www-03 both be replication slaves to www-01. When a user connect to www-01 and makes an update, nothing special - the update gets recorded, written to the binary log and replicated to www-02 and www-03. When a users attempts to update/insert/delete a record on www-02, I want www-02 to: 1. open a connection to www-01 (or have one connection that's already open for this purpose). if the connection to www-01 fails because of a TCP problem, www-02 should return an error along the lines of replication master is unavailable; query not executed to the client 2. authenticate against www-01 as the user the is currently authenticated on www-02. again, if this fails, return an error along the lines of could not authenticate on replication master; query not executed 3. pass the exact same query that www-02 received upstream to www-01 4. wait for www-01 to respond ok or query error 5. pass the response from www-01 back to the client connected to www-02 Is that clear? Normal replication would then pass the update from www-01 back to www-02 and www-03 so that all three servers are in sync. Since the update happened on www-01, auto_increments would not need to use the auto_increment_increment and auto_increment_offset, stored functions and stored procedures would not have to worry about their respective replication problems (certain types of functions/procedures aren't safe to use with normal mySQL replication), and locking would work (because lock statements would also be passed up the chain to the master server). Yes, this means that updates wouldn't be as fast as multi-master replication, but I think dealing with almost all the other problems with replication would become much more manageable. This set-up makes the most sense in read-heavy environments. As I mentioned before, my main goal here is geographic redundancy, load balancing and the ability to use applications that aren't really designed to be used with master/slave replication (Drupal, WordPress, etc) without having to modify those applications. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master/Slave Replication Question
Another thought would be at the application layer, sending all the updates (insert,delete, update, etc) to server A, and the selects to the local slave servers. This has been suggested before, and I'm totally against it. Applications like PHPBB, Drupal, WordPress, etc can't be easily configured to do this, and I'd really like to use those applications in a more robust and redundant environment. Pushing the work of this sort of master/slave relationship to the application level means that most applications will not support it. Replication is a database server problem, not an application problem. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave Replication Question
You'd be surprised how many places uses this as an actual solution. All arguments aside about what level of the architecture should be doing what, it simply works well. Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) I would love to see a drop-in solution that requires no application changes, and doesn't introduce any additional complications such as adding excessive overhead, or have yet another single point of failure, but none (or at least no good ones) exist. This is because optimization is a very specific process. Applications like PHPBB, Drupal and WordPress should have their database access encapsulated well enough that making the changes to split reads and writes is trivial. If not, then it's bad design on their part. Scalability is very much a part of application design as well, and just because lots of people use those apps, doesn't mean they were designed to scale well. Regards, Gavin Towey -Original Message- From: Tim Gustafson [mailto:t...@soe.ucsc.edu] Sent: Friday, September 25, 2009 2:44 PM To: mysql@lists.mysql.com Subject: Re: Master/Slave Replication Question Another thought would be at the application layer, sending all the updates (insert,delete, update, etc) to server A, and the selects to the local slave servers. This has been suggested before, and I'm totally against it. Applications like PHPBB, Drupal, WordPress, etc can't be easily configured to do this, and I'd really like to use those applications in a more robust and redundant environment. Pushing the work of this sort of master/slave relationship to the application level means that most applications will not support it. Replication is a database server problem, not an application problem. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Master/Slave Replication Question
Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) With all due respect to the mySQL cluster people, setting up a mySQL cluster just isn't in the cards for lots of organizations. It's just too much. There's a huge implementation gap between a single mySQL server and a mySQL Cluster. I've also heard from people who have tried to implement mySQL clustering that wide-area cluster replication is hard or impossible (I can't remember which), so the ability to provide geographic redundancy (one of my requirements here) isn't workable. I think saying that I'd have to wait until the end of time is a bit harsh. Sure, it's not going to happen tomorrow, but I wasn't expecting that anyhow. I'm not sure if you've looked at the database integration for things like Drupal, but there will probably never be a way for Drupal to use an updates go to this server, reads go to this server configuration, as there are thousands of Drupal modules and almost all of them use the database directly, and each would have to be re-coded to work with the read/write split configuration. And anyhow, I think that suggestion is missing the point: If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. So yeah, maybe lots of custom-written software handles the read/write split configuration well, but there's lots more that doesn't. I don't know of a single open source application that does. So again, I go back to my original statement: replication is a database server problem, not an application problem. :) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave Replication Question
Cluster is not supposed to be a universal solution, for a reason. Universal solutions tend not to be very performant. If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. Do you want geographic redundancy or do you want to scale reads? In this case you're talking about scaling reads for a bunch of apps all running together. If you want performance in that case, then first you'd want to isolate the apps from each other. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. Geographical redundancy is different: a dns record with a zero ttl, with a master-slave replication setup. Point the record a the master and if it fails, change the dns entry to point to the slave. Your applications never need to know about replication. That’s even if you don't want to go with the more complex Linux HA or hardware based ip takeover solutions. There are many ways you could add redundancy without modifying the apps. That's the great thing about open source software and techniques. They're like building blocks, and you can put them together however you want. I find this much more preferable to the all-in-one black-box solution. Regards, Gavin Towey -Original Message- From: Tim Gustafson [mailto:t...@soe.ucsc.edu] Sent: Friday, September 25, 2009 4:18 PM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Master/Slave Replication Question Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) With all due respect to the mySQL cluster people, setting up a mySQL cluster just isn't in the cards for lots of organizations. It's just too much. There's a huge implementation gap between a single mySQL server and a mySQL Cluster. I've also heard from people who have tried to implement mySQL clustering that wide-area cluster replication is hard or impossible (I can't remember which), so the ability to provide geographic redundancy (one of my requirements here) isn't workable. I think saying that I'd have to wait until the end of time is a bit harsh. Sure, it's not going to happen tomorrow, but I wasn't expecting that anyhow. I'm not sure if you've looked at the database integration for things like Drupal, but there will probably never be a way for Drupal to use an updates go to this server, reads go to this server configuration, as there are thousands of Drupal modules and almost all of them use the database directly, and each would have to be re-coded to work with the read/write split configuration. And anyhow, I think that suggestion is missing the point: If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. So yeah, maybe lots of custom-written software handles the read/write split configuration well, but there's lots more that doesn't. I don't know of a single open source application that does. So again, I go back to my original statement: replication is a database server problem, not an application problem. :) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Master/Slave Replication Question
Do you want geographic redundancy or do you want to scale reads? In this case you're talking about scaling reads for a bunch of apps all running together. If you want performance in that case, then first you'd want to isolate the apps from each other. Geographic redundancy is my primary goal. Geographic redundancy also by its very nature helps boost performance of reads because you have more than one server from which to read. Each web site hosted on this group of servers has an A record that points to all three servers - so, foo.com has A records for 1.2.3.4, 2.3.4.5 and 3.4.5.6. Using round-robin DNS, the connections for clients are distributed across all three geographically redundant servers using poor-man's load balancing. Geographical redundancy is different: a dns record with a zero ttl, with a master-slave replication setup. Point the record a the master and if it fails, change the dns entry to point to the slave. Your applications never need to know about replication. Except I'm not just making my database geographically redundant, but my web server and applications as well. Each server runs mySQL, Apache and PHP. PHP connects to localhost on each server. Using your method (which by the way requires human intervention to activate), only the mySQL would be redundant. And your solution does not balance reads across all mySQL servers. That's the great thing about open source software and techniques. They're like building blocks, and you can put them together however you want. I find this much more preferable to the all-in-one black-box solution. You find it preferable to handle your database redundancy with something other than your database server? Any mySQL is black-box? I was pretty sure mySQL was open source. Anyhow, you have actually proven my point: mySQL has already provided the building blocks of master/slave replication. Now I'd like to see an addition to that protocol (upstream updates) and bam, we've got a whole new replication methodology that fills the needs of a set of people whose needs were not met by multi-master replication or mySQL cluster. At any rate, this part of the discussion is getting very off-topic. My original suggestion pertained to a new way of doing replication that I'd like to see in mySQL. If you don't like my idea, don't use the feature if and when it ever comes into being. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Master/Slave Replication Question
Hi, I'm a big fan of mySQL's multi-master replication, but I've run into gotchas over the years. Off the top of my head, I can think of: - auto_increment complications, - if you have a-b-c-a, it's not exactly graceful to insert a d server for a-b-c-d-a - if you have a-b-c-a and b fails, it's tricky to change your config to a-c-a - no one authoritative data set - problems with certain types of stored procedures/functions I think I may have asked this question before, but I don't recall, so I thought I'd ask here: Assuming all your grant tables are replicated and identical, wouldn't it be possible for a read-only mySQL slave to pass update queries to its master server, and then return the response from the master to the client? I think this approach has several neat advantages: - your client software doesn't have to know anything about replication (like to connect to a different server for updates) - problems with stored functions and procedures go away - no auto_increment problems - the master would maintain auto_increment consistency - rebuilding a failed slave in this arrangement is worry-free - one failed slave doesn't interrupt the replication of all the other servers - you can do multi-level replication, where you have a-b, b-c, and then send an update query to c, which would send it to b, which would send it to a, which would process the query, return the result to b, which would then return the result to c (if c couldn't run the update query on b, or if b couldn't run the update query on a, an SQL error could be returned to the client) I can see a few retorts right off the bat: - this complicates the replication protocol - not everyone would want to do it like this - updates might take a bit longer since they have to be sent to the master But I think this arrangement could be very useful in certain situations, and it seems to me that this is something that could totally be implemented as a configurable option, something like a simple my.cnf setting that says slave_pass_upadtes_to_master or something. Has anyone suggested anything like this before? Any thoughts/comments/flames? Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
replication question -different db name on slave server
In mysql 4, can one replicate a database to another server but have the DB name on the slave server be different? Master : dbname1 Slave: dbname1 AND Slave : dbname2 (but contains data from db2name1) A project manager is asking for this.. don't blame the guy asking :-) Regards, Martin Corona
Re: replication question -different db name on slave server
On Mon, Oct 13, 2008 at 11:43 AM, AM Corona [EMAIL PROTECTED] wrote: In mysql 4, can one replicate a database to another server but have the DB name on the slave server be different? Master : dbname1 Slave: dbname1 AND Slave : dbname2 (but contains data from db2name1) A project manager is asking for this.. don't blame the guy asking :-) Regards, Martin Corona If you don't need things to synched you could *not* run a sql thread on the slave, and then use sed/awk/whatever to filter the relay log files and then run the sql thread. In fact I bet there is some way for awk to sit between the relay logs and sql threads in real time... -- Rob Wultsch
Re: replication question -different db name on slave server
In the last episode (Oct 13), AM Corona said: In mysql 4, can one replicate a database to another server but have the DB name on the slave server be different? Master : dbname1 Slave: dbname1 AND Slave : dbname2 (but contains data from db2name1) So you want the same data in two different databases on the same slave, or is the 2nd slave line a different server? If it's a different server, then it can be done: http://dev.mysql.com/doc/refman/5.0/en/replication-options.html#option_mysqld_replicate-rewrite-db -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've been looking for a way to fake replication from mysql to a local BDB database. I'm not finding anything. Anybody ever come across this? It seems like it wouldn't be too terribly difficult to read from the relay-log and keep track of where you are. But not having written anything like this before, I'm far from authoritative. Any comments or suggestions? (For reference, I'm implementing Bind-DLZ. I'm going to replicate tables from a multi-master server to a couple of slaves running on the nameservers. Then I want a daemon to read that relay-log and replicate those commands into a local BDB database. The reason is for maximum speed.) - -- Regards... Todd we're off on the usual strange tangents. next will be whether it is ethical to walk in your neighbor's open house if they're running ipv6:-). --Randy Bush Linux kernel 2.6.22-14-generic 4 users, load average: 0.18, 0.06, 0.02 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvKwfY2VBGxIDMLwRAuYJAJ9vwyx/iZ2iNjR6hk5vTT57pmViJgCeJ6sx QbkxNY3AbnTtRU7z2YqyWuU= =ryok -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Question
In the last episode (Jul 03), mos said: If I'm replicating a master database to a slave (MyISAM tables), but the slave is busy serving up web pages, how does it get write access to the slave's table if it is always being read? TIA Mysql places inserts in front of selects in its internal queue, so as soon as an insert comes in, subsequent selects will queue up behind it until the insert has finished. You can adjust this for individual queries with the HIGH_PRIORITY and LOW_PRIORITY flags. http://dev.mysql.com/doc/refman/5.0/en/insert.html http://dev.mysql.com/doc/refman/5.0/en/select.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Question
If I'm replicating a master database to a slave (MyISAM tables), but the slave is busy serving up web pages, how does it get write access to the slave's table if it is always being read? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database replication question
Hi, As I know, master will replicate all database to the slave in default. If I want to replicate only specified database, we can use parameter in master. binlog-do-db=databasename I found that we can also use parameters to replicate database even table in slave. replicate-do-db=db_name (replicate this database) replicate-ignore-db=db_name (don't replicate this database) replicate-do-table=db_name.table_name (replicate this table) replicate-ignore-table=db_name.table_name (don't replicate this table) I have tried the second method but nothing to replicate. Anyone can tell if it works for you. ango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi master replication question
I'm about to re-create a mulit master replication setup that was dismantled during server hardware/software upgrades. Replication setup: A - B - A | C One thing I can't remember is do I have to set an option somewhere to tell the masters to ignore the queries in the binlog that oringated from them? I know I have to have log-bin and log-slave-updates set on each but is there something else or will Server A automatically know to ignore an update query that originated on it, then replicated to server B which wrote it into it's binlog due to log-slave-updates setting (there are downstream servers doing one way replication off of server B) and then replicated back to A? Just having a brain fart here... Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi master replication question
Replication setup: A - B - A | C One thing I can't remember is do I have to set an option somewhere to tell the masters to ignore the queries in the binlog that oringated from them? Make sure you set different Server IDs on each machine and you should be just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi master replication question
-Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 18, 2006 11:06 To: mysql@lists.mysql.com Subject: Re: Multi master replication question Replication setup: A - B - A | C One thing I can't remember is do I have to set an option somewhere to tell the masters to ignore the queries in the binlog that oringated from them? Make sure you set different Server IDs on each machine and you should be just fine. -- Excellent. Many thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Quick Replication Question
When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Replication Question
no. - Original Message - From: Robinson, Eric [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 28, 2006 8:51 AM Subject: Quick Replication Question When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Quick Replication Question
Okay, so that is not too encouraging. Is there any way to find out what mysqkcheck did so it can be manually applied to the slave? Or is mysqlcheck not the best way to fix things when you are using replication? -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, April 28, 2006 9:05 AM To: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Quick Replication Question no. - Original Message - From: Robinson, Eric [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 28, 2006 8:51 AM Subject: Quick Replication Question When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
- Original Message - From: Kishore Jalleda as per http://dev.mysql.com/doc/mysql/en/replication-compatibility.html there should be no problems On 8/17/05, Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Well, there are a few differences between 4.0.x and 4.1.x that might cause some problems: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html Lists most if not all of them. At a quarter of the page the block Server changes lists differences in the way tables are built internally, differences in comparing things, etc. Half way on that page the block SQL changes list incompatibilities in query syntax, etc. For example: - DELETE from multiple tables ( In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name: ) http://dev.mysql.com/doc/mysql/en/delete.html There is no easy solution as the master (4.0) requires a different syntax than the slave (4.1). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
Hello. According to: http://dev.mysql.com/doc/mysql/en/replication-compatibility.html you shouldn't have any problems, but 4.0.16 is a very old version. I strongly recommend you to upgrade. Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
as per http://dev.mysql.com/doc/mysql/en/replication-compatibility.html there should be no problems Kishore Jalleda On 8/17/05, Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql total replication question
Hi All! I'm using the latest mysql 4.0.x and I've successfully set-up database replication on it over 2 machines. I have noticed though that mysql doesn't seem to replicate CREATE TABLE operations on the master to the slave. Is this a limitation? Or I just missed something. If so, can someone please give me a clue or point me to the right place documentations? Also, is it possible to setup replication so that everything in the master is replicated to the slave, included CREATE DATABASE operations? Many Thanks! Shannon __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql total replication question
Hello. In my replication setup with 4.1.11 master and 5.0.4 slave CREATE TABLE and CREATE DATABASE statements are replicated perfectly. Shannon R. [EMAIL PROTECTED] wrote: Hi All! I'm using the latest mysql 4.0.x and I've successfully set-up database replication on it over 2 machines. I have noticed though that mysql doesn't seem to replicate CREATE TABLE operations on the master to the slave. Is this a limitation? Or I just missed something. If so, can someone please give me a clue or point me to the right place documentations? Also, is it possible to setup replication so that everything in the master is replicated to the slave, included CREATE DATABASE operations? Many Thanks! Shannon __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question...
Jason Lixfeld [EMAIL PROTECTED] wrote on 03/12/2004 03:32:32: I'm very new to mysql and replication. I've got a case where I have 2 servers, each have database A and database B. I want server 1 to be master for database A and slave for database B and I would like server 2 to be slave for database A and master for database B. From what I've read, if a server is a master, it's a master and likewise for a slave and they can't share the role depending on the requirements of the user. Can someone enlighten me? No, I think it is more flexible than that, and will probably fulfil your needs. You can set up circular replication, of which your two-server case is the simplest example. It is then up to you to ensure at application level that no single table is updated on more than one machine. If you observer that rule than, so far as I understand, replication will do what you want. If you break that rule, behaviour will be unpredictable. You might want to use the replicate_do_db variable to provide a partial enforcement of the rule. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question...
I'm very new to mysql and replication. I've got a case where I have 2 servers, each have database A and database B. I want server 1 to be master for database A and slave for database B and I would like server 2 to be slave for database A and master for database B. From what I've read, if a server is a master, it's a master and likewise for a slave and they can't share the role depending on the requirements of the user. Can someone enlighten me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto-Increment Starting Point? (Multimaster Replication Question)
The binary logs used for replication set the value used for autoincrementing before each insert query. The number on the master will always be replicated properly. If a row already exists with the autoincrement value, my guess is that replication will die with an error. I'm not too sure which version of mysql introduced the feature. On Thu, 18 Nov 2004 13:35:08 -0800, Robinson, Eric [EMAIL PROTECTED] wrote: When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto-Increment Starting Point? (Multimaster Replication Question)
When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson
RE: Auto-Increment Starting Point? (Multimaster Replication Question)
5001 Peter -Original Message- From: Robinson, Eric [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 21:35 To: [EMAIL PROTECTED] Subject: Auto-Increment Starting Point? (Multimaster Replication Question) When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Replication Question
Hi! I wanted to set-up a master-slave replication on my win2k box. When my master server fails, my slave server will automatically becomes my new master server. And when my master server is back online, any changes on my slave server is replicated back to my master server. Normal: A - B ^ L inserts/updates from clients Failure:XB New Master: XB - inserts/updates from clients Master is back: A - B x- inserts/updates are blocked manually. Normal: A - B ^ L inserts/updates from clients Any idea? Thanks. l.yeung __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Replication Question
L. Yeung [EMAIL PROTECTED] wrote on 09/07/2004 08:38:38: Hi! I wanted to set-up a master-slave replication on my win2k box. When my master server fails, my slave server will automatically becomes my new master server. And when my master server is back online, any changes on my slave server is replicated back to my master server. Normal: A - B ^ L inserts/updates from clients Failure:XB New Master: XB - inserts/updates from clients Master is back: A - B x- inserts/updates are blocked manually. Normal: A - B ^ L inserts/updates from clients Any idea? Thanks. You cannot do this automatically: you need a supervisor progream. The way you need to do it is to have both machines come up with their slave thread *not* running. The supervisor then determines which is master and which slave, and starts the appropriate slave thread running. If it determines that the original master has failed, it stops replicatio on the slave and directs updates to it: the slave has now become master. When the original master reappears, it determines that updates have been made to the original slave later than those to the original master, it instructs the originl master to reload its databse from the slave. Master and slave have now exchanged roles. The determination of which is the most-recently updated is done by a single row, single column table which is incremented whenever the slave takes over from the master. If, at startup, two active machines are found with differeent values in this entry, the higher value becomes master and the lower must be re-synchronized. If the values are the same, the slave status can be inspected to see which is slaving to which. We have implemented such a system in our own middleware. We have a target changeover time of 10 seconds, which we are meeting easily. It only works for MyISAM tables, since LOAD DATA FROM MASTER is only available for these. Note to MySQL development team: this request comes up often enough that I hope the idee of embedding this supervisor in the MySQL daemon is at least on the wish list. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Replication Question
Very good, gmail does not handle mailing lists properly.. Sorry for sending this off-list to you, Alec. On Fri, 9 Jul 2004 17:09:45 -0300, João Paulo Vasconcellos [EMAIL PROTECTED] wrote: On Fri, 9 Jul 2004 10:44:42 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: L. Yeung [EMAIL PROTECTED] wrote on 09/07/2004 08:38:38: Hi! I wanted to set-up a master-slave replication on my win2k box. When my master server fails, my slave server will automatically becomes my new master server. And when my master server is back online, any changes on my slave server is replicated back to my master server. Normal: A - B ^ L inserts/updates from clients Failure:XB New Master: XB - inserts/updates from clients Master is back: A - B x- inserts/updates are blocked manually. Normal: A - B ^ L inserts/updates from clients Any idea? Thanks. You cannot do this automatically: you need a supervisor progream. The way you need to do it is to have both machines come up with their slave thread *not* running. The supervisor then determines which is master and which slave, and starts the appropriate slave thread running. If it determines that the original master has failed, it stops replicatio on the slave and directs updates to it: the slave has now become master. When the original master reappears, it determines that updates have been made to the original slave later than those to the original master, it instructs the originl master to reload its databse from the slave. Master and slave have now exchanged roles. The determination of which is the most-recently updated is done by a single row, single column table which is incremented whenever the slave takes over from the master. If, at startup, two active machines are found with differeent values in this entry, the higher value becomes master and the lower must be re-synchronized. If the values are the same, the slave status can be inspected to see which is slaving to which. We have implemented such a system in our own middleware. We have a target changeover time of 10 seconds, which we are meeting easily. It only works for MyISAM tables, since LOAD DATA FROM MASTER is only available for these. Note to MySQL development team: this request comes up often enough that I hope the idee of embedding this supervisor in the MySQL daemon is at least on the wish list. Alec I personally think that, if my master has gone away, there must be a reason and I do not want the former master to take over when (and if) it came back. So, in my production environment, I set this with heartbeat (linux-ha.org) and a perl script that makes my slave works as master when needed. Of course, my slave is nothing but a hot backup spare server. -- João Paulo Vasconcellos ICQ: 123-953-864 -- João Paulo Vasconcellos ICQ: 123-953-864 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Hello list, I am about to embark on a little repliction mission;) I was thinking about setting up a MySQL slave on my Windows box and replicate my MySQL 5.0 Master which runs on my Linux box. Could the mix of architectures become a problem? I also use innodb tables alot, are they replicateble? or are just MyISM tables replicatable. I have not tried anything yet just thought I ask around abit first:) Best regards /Jonas Lindén
RE: Replication question
Jonas, It's recommended in the docs that you upgrade your slaves to 5.0.0 before your Master (http://www.mysql.com/doc/en/Replication_upgrade_5.0.html) You shouldn't run into any problems with replicating InnoDB tables.(http://www.mysql.com/doc/en/InnoDB_and_MySQL_Replication.html) good luck! --bmansell From: Jonas Lindén [mailto:[EMAIL PROTECTED] Sent: Thu 3/25/2004 11:56 PM To: [EMAIL PROTECTED] Subject: Replication question Hello list, I am about to embark on a little repliction mission;) I was thinking about setting up a MySQL slave on my Windows box and replicate my MySQL 5.0 Master which runs on my Linux box. Could the mix of architectures become a problem? I also use innodb tables alot, are they replicateble? or are just MyISM tables replicatable. I have not tried anything yet just thought I ask around abit first:) Best regards /Jonas Lindén
Re: Replication question
Jonas Lindén [EMAIL PROTECTED] wrote on 26/03/2004 07:56:18: Hello list, I am about to embark on a little repliction mission;) I was thinking about setting up a MySQL slave on my Windows box and replicate my MySQL 5.0 Master which runs on my Linux box. Could the mix of architectures become a problem? I also use innodb tables alot, are they replicateble? or are just MyISM tables replicatable. The only problem we encountered was case significance in table (= file, for MyIsam) names. Since you are going Linux-Windows, this shouldn't be a problem - the other way round can cause problems unless you stick to lower case only table names. The InnoDB tables should be no problem since replication is done via the binlog, not by file copies. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Replication Question
Greetings, I am replicating from 4.0.17 to 4.0.16. I read through the replication docs, and I didn't see anything relating to what I'm trying. What I am hoping to do ... is slave the multiple databases ( the whole thing ), in order to avoid shutting down ( or at least locking ) the master and making a copy/dump. When I tried, and did a show master status ... the message was Writing to Net. I left it for several hours, but nothing at all was replicated that I saw, and the status never changed. My question the, is that is this possible to do a full replication without a dump of some sort ? My replication config on the slave side is: [mysqld] server-id=15 master-host=master master-user=repuser master-password=pass replicate-do-db = db1 replicate-do-db = db2 replicate-do-db = db3 And on the master the following: [mysqld] server-id = 11 log-bin = /mysql/log/binlog binlog-do-db= db1 binlog-do-db= db2 binlog-do-db= db3 **( this is not the complete config, just the relevant portions ) Additional information: The master server, got its data by replicating from the original. In this regard, even though binary logging has been turned on since the *this* master was built, the binary logs will not contain the sum total of all the transactions the db has ever seen. I can of course do a dump if I need to, but it would be much easier for me to replicate fully on the fly if I can, even though it will take much longer. Any advice would be great, Sean -- Real Time, adj.: Here and now, as opposed to fake time, which only occurs there and then. Ambrose Bierce (1842 - 1914), The Devil's Dictionary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Question
Is it possible to have 2 database on one server replicating from the same Master server? In other words. DB01 is the Master on System01, System02 has DB01_rep1 and DB01_rep2, each with their own replication from DB01. I need to do this to have a development copy of DB01 on System02. I have production software that pulls data from DB01_rep1 and I need to set up DB01_rep2 to do some development work. When I copy the DB01 snapshot into a new database (DB01_rep2) on System02 and then log into that database and do a Show slave status it shows me the slave status information that was set up for DB01_rep1. Is replication System dependant or database dependant? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Question
On Mon, 22 Dec 2003, Jeff McKeon wrote: Is it possible to have 2 database on one server replicating from the same Master server? yes. In other words. DB01 is the Master on System01, System02 has DB01_rep1 and DB01_rep2, each with their own replication from DB01. Shouldn't be a problem. I need to do this to have a development copy of DB01 on System02. I have production software that pulls data from DB01_rep1 and I need to set up DB01_rep2 to do some development work. When I copy the DB01 snapshot into a new database (DB01_rep2) on System02 and then log into that database and do a Show slave status it shows me the slave status information that was set up for DB01_rep1. Just make sure that you're not running against the same logfiles/data, so you can have different relay-logs and master.info files. Is replication System dependant or database dependant? Neither, it's server instance dependant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Question
-Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Monday, December 22, 2003 11:21 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Replication Question On Mon, 22 Dec 2003, Jeff McKeon wrote: Is it possible to have 2 database on one server replicating from the same Master server? yes. In other words. DB01 is the Master on System01, System02 has DB01_rep1 and DB01_rep2, each with their own replication from DB01. Shouldn't be a problem. I need to do this to have a development copy of DB01 on System02. I have production software that pulls data from DB01_rep1 and I need to set up DB01_rep2 to do some development work. When I copy the DB01 snapshot into a new database (DB01_rep2) on System02 and then log into that database and do a Show slave status it shows me the slave status information that was set up for DB01_rep1. Just make sure that you're not running against the same logfiles/data, so you can have different relay-logs and master.info files. So I have to do something on the master then to add this second slave on the same system as the first slave? I'm not sure how to set up a separate logfile/data for the second slave... Is this done on the master or slave? Is replication System dependant or database dependant? Neither, it's server instance dependant You mean mysql server as in the software correct? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication question
Greetings, Firstly I have searched the archives and received exactly 0 results returned for 'START SLAVE 1064' so... I have a mysql server I wish to replicate, its running 3.23.54. I have two test FreeBSD 5.1 servers I installed and from ports version 3.23.58. I completed the following steps to setup replication. I followed the steps in the documentation on mysql.com, made the necessary my.conf modification, archived and imported my test db to the slave, defined the master config w/ 'change master to' on the slave. Everything worked well up to this point, but if I execute 'START SLAVE' on my slave I get ERROR 1064: You have an error in your SQL syntax near 'START SLAVE' at line 1 This tells me that my version of mysql does not support the 'START' command. Do I need to add something at compile time? Are the instructions only for 4.x? Am I just missing something really obvious? Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication question
Try slave start -Original Message- From: Andrew Hall [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 2:46 PM To: [EMAIL PROTECTED] Subject: replication question Greetings, Firstly I have searched the archives and received exactly 0 results returned for 'START SLAVE 1064' so... I have a mysql server I wish to replicate, its running 3.23.54. I have two test FreeBSD 5.1 servers I installed and from ports version 3.23.58. I completed the following steps to setup replication. I followed the steps in the documentation on mysql.com, made the necessary my.conf modification, archived and imported my test db to the slave, defined the master config w/ 'change master to' on the slave. Everything worked well up to this point, but if I execute 'START SLAVE' on my slave I get ERROR 1064: You have an error in your SQL syntax near 'START SLAVE' at line 1 This tells me that my version of mysql does not support the 'START' command. Do I need to add something at compile time? Are the instructions only for 4.x? Am I just missing something really obvious? Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication question
DUH!! I should have tried that... Thank you! Drew On Fri, 2003-12-05 at 15:49, Victor Pendleton wrote: Try slave start -Original Message- From: Andrew Hall [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 2:46 PM To: [EMAIL PROTECTED] Subject: replication question Greetings, Firstly I have searched the archives and received exactly 0 results returned for 'START SLAVE 1064' so... I have a mysql server I wish to replicate, its running 3.23.54. I have two test FreeBSD 5.1 servers I installed and from ports version 3.23.58. I completed the following steps to setup replication. I followed the steps in the documentation on mysql.com, made the necessary my.conf modification, archived and imported my test db to the slave, defined the master config w/ 'change master to' on the slave. Everything worked well up to this point, but if I execute 'START SLAVE' on my slave I get ERROR 1064: You have an error in your SQL syntax near 'START SLAVE' at line 1 This tells me that my version of mysql does not support the 'START' command. Do I need to add something at compile time? Are the instructions only for 4.x? Am I just missing something really obvious? Thank you for your time in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: RE: replication question]
Didn't have the list cc-ed. -Forwarded Message- From: Andrew Hall [EMAIL PROTECTED] To: Mike Johnson [EMAIL PROTECTED] Subject: RE: replication question Date: Fri, 05 Dec 2003 16:17:14 -0500 Ok, at this point it appears my replication is working, but I have two questions: 1. I tested by creating a table on the master, populated it, then on the slave did a 'show tables', and 'select * from test', and my test table and data was there. Is this methodology acceptable for a basic test? Any suggestions on testing methodology? 2. When I execute 'show master status' the Binlog_db_do fields is empty. This seems erroneous to me. Shouldn't this field be populated with the db name(s) to replicate? Thanks again, Drew execute 'START SLAVE' on my slave I get ERROR 1064: You have an error in your SQL syntax near 'START SLAVE' at line 1 This tells me that my version of mysql does not support the 'START' command. Do I need to add something at compile time? Are the instructions only for 4.x? Am I just missing something really obvious? Don't quote me on it, but I think START SLAVE was introduced in 4.x. Try SLAVE START. I recently read that SLAVE START was being deprecated; I can only assume START SLAVE was a recent introduction. Regardless, I always used SLAVE START on 3.23.x. HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication question
Hi, I'm trying to use replication and the slave stopped with error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. The master version is 4.0.14-standard (official binaries) The slave version is 4.0.15-standard (official binaries too) When I 'mysqlbinlog host-relay-bin.011' it gives me: ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 106, event_type: 2 ERROR: Could not read entry at offset 171971 : Error in log format or read error My question is, how can I recover from this error? TIA --- Luciano Barcaro Depto. Informática - Laboratório Alvaro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
SQL Andrey Kotrekhov [EMAIL PROTECTED] wrote: Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 What replication options do you use? Full scheme is: | +-A| C | -| | +-B Configuration A: server-id = 4 master-host=XXX master-user=XXX master-password=XXX master-port=3306 replicate-do-db=ZZ master-connect-retry=60 log-slave-updates = Configuration B: server-id = 3 user= mysql master-host = 195.248.191.22 master-user = ghsdr_repl master-password = Dtp6TrH master-port = 3306 replicate-do-db = ZZ replicate-do-table = ZZ.counter replicate-do-table = ZZ.counter_price replicate-do-table = ZZ.reserv_unit replicate-do-table = ZZ.service replicate-do-table = ZZ.ppp_utmp replicate-do-table = ZZ.net replicate-do-table = ZZ.service_net But there are no tables A and B in replicate-do-table list. I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Hi, All! SQL, mysql I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
I'm pretty sure you need to sync the entire database (all tables) to all slaves before starting replication..Your servers are technically already out of sync.. And no wonder it crashes, tables are missing in it's view..You need to hit the initial replication setup manual pages.. On Thu, 18 Sep 2003, Andrey Kotrekhov wrote: Hi, All! SQL, mysql I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
Hi, All! SQL, mysql Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; Is it right? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
Andrey Kotrekhov [EMAIL PROTECTED] wrote: Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 What replication options do you use? I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Here is the scenario: 4.0.15 as the master 3.23.57 is the slave I can't get them to replicate until I downgraded 4.0.15 to 3.23.57 This was the error produced 030911 23:45:47 Slave: connected to master '[EMAIL PROTECTED]:3306', replication star ted in log 'FIRST' at position 79 030911 23:45:47 Slave: did not get the expected error running query from master - ex pected: 'Invalid error code' (256), got 'no error' (0) 030911 23:45:47 Slave: error running query '' 030911 23:45:47 Error running query, slave aborted. Fix the problem, and re-start th e slave thread with mysqladmin start-slave. We stopped at log 'FIRST' position 79 030911 23:45:47 Slave thread exiting, replication stopped in log 'FIRST' at position 79 I was under the impression that replication between 3.23.x and 4.0.x is possible. I did check and make sure that CHANGE MASTER TO MASTER_LOG_FILE='another_log_file', MASTER_LOG_POS=4; was executed. On 4.0.15 I made sure the REPLICATION user had REPLICATION permissions. A Connection was made did change the log position from 4 to 79, then replication could not continue even with SET SQL_SLAVE_SKIP_COUNTER=x;slave start; I did a Google search on the error message, and saw no solutions to this problem. Any suggestions? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
mysql replication question(s)
Hi All, I'm having a few issues getting things up and running in regards to replication. I believe I have everything setup correctly the slave is roughly in the same spot as the master as far as the logs are concerned. However, when I make changes in the master db I don't see them show up in the slave db? show master status: 'mhqedi02-bin.012','622609','','' show slave status: 'mhqedi02','replicant','7003','60','mhqedi02-bin.012','630264','royedi01-relay-bin.001','630874','mhqedi02-bin.007','Yes','Yes','','','0','','0','629259','630874' show process list (slave): '3','system user','none','','Connect','20139','Reading master update','' '4','system user','none','','Connect','20139','Slave: waiting for binlog update','' my main concern is that the time on the process list is so long. Am I missing anything? I noticed there is a show hosts command to run on the master. When I run this I get an empty set back. Is this needed? Thanks for your help, Mario __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql replication question(s)
Hi All, I'm having a few issues getting things up and running in regards to replication. I believe I have everything setup correctly the slave is roughly in the same spot as the master as far as the logs are concerned. However, when I make changes in the master db I don't see them show up in the slave db? show master status: 'mhqedi02-bin.012','622609','','' show slave status: 'mhqedi02','replicant','7003','60','mhqedi02-bin.012','630264','royedi01-relay-bin.001','630874','mhqedi02-bin.007','Yes','Yes','','','0','','0','629259','630874' show process list (slave): '3','system user','none','','Connect','20139','Reading master update','' '4','system user','none','','Connect','20139','Slave: waiting for binlog update','' my main concern is that the time on the process list is so long. Am I missing anything? I noticed there is a show hosts command to run on the master. When I run this I get an empty set back. Is this needed? Thanks for your help, Mario __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication question
Should be fine as long as the column on C has a default and is not a unique index. ---Original Message- --From: Jeff McKeon [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 20, 2003 6:05 AM --To: MySql --Subject: RE: Replication question -- --Nobody has any advice for this one? -- --Jeff -- -- -Original Message- -- From: Jeff McKeon -- Sent: Tuesday, August 19, 2003 8:22 AM -- To: [EMAIL PROTECTED] -- Subject: Replication question -- -- -- Hey all, -- -- I have 3 databases replicating (ver 3.23) A to B and B to C -- -- On C I want to modify one of the tables and add a column. -- Tables A and B will not have this new column added. Will -- this cause a problem replicating data form B to C? -- -- Thanks, -- -- Jeff -- -- -- -- MySQL General Mailing List -- For list archives: http://lists.mysql.com/mysql -- To unsubscribe: -- http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
Hey all, I have 3 databases replicating (ver 3.23) A to B and B to C On C I want to modify one of the tables and add a column. Tables A and B will not have this new column added. Will this cause a problem replicating data form B to C? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
I have connect my web server (master) to my local server (slave) through dial-up and replicate 2 databases. a) Is there a log file describing the replications that happend (if any)? b) how do I know when to stop the dial-up so not to stop any data transfer through repliication? Thanx Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication question
HI all, Is it possible to perform replication as follow? HOW? master (innodb) ---replication--- slave (myisam) Rgds, Ringo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication question
Replication just executes the commands in the binary log (i.e INSERT, UPDATE, DELETE, ...). So if you make sure that you do not issue a DDL for creation/altering in the master DB, everything should work fine. However, I would advice against it (it is not a supported feature, but a trick) Cheers /rudy -Original Message- From: unplug [mailto:[EMAIL PROTECTED] Sent: maandag 4 augustus 2003 12:03 To: mysql Subject: replication question HI all, Is it possible to perform replication as follow? HOW? master (innodb) ---replication--- slave (myisam) Rgds, Ringo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
GlacierI have connect my web server (master) to my local server (slave) through dial-up and replicate 2 databases. a) Is there a log file describing the replications that happend (if any)? b) how do I know when to stop the dial-up so not to stop any data transfer through repliication? Thanx Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication question
Chris Petersen [EMAIL PROTECTED] wrote: I work for a small (but very fast-growing) company, and we're about to start deploying our new website. Because it has a live inventory setup, and because our internal tools are so integrated with the ecommerce side of things, I figure that replication is the best tool for us - we'd have a fast database at our colo, and one in the office, and data would be mirrored as needed. I can work around issues that might arise with auto-increment fields when the servers can't talk, but I'm wondering if there is a way to do table-based replication instead of db-based. I'd prefer it if the master machine only paid attention to changes on certain tables from the slave (my code already handles this, but I'm still looking for that extra bit of security - plus as I understand it, if things are read-only they will be a bit faster). Take a look at replication options like replicate-do-table etc. http://www.mysql.com/doc/en/Replication_Options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication question
I work for a small (but very fast-growing) company, and we're about to start deploying our new website. Because it has a live inventory setup, and because our internal tools are so integrated with the ecommerce side of things, I figure that replication is the best tool for us - we'd have a fast database at our colo, and one in the office, and data would be mirrored as needed. I can work around issues that might arise with auto-increment fields when the servers can't talk, but I'm wondering if there is a way to do table-based replication instead of db-based. I'd prefer it if the master machine only paid attention to changes on certain tables from the slave (my code already handles this, but I'm still looking for that extra bit of security - plus as I understand it, if things are read-only they will be a bit faster). Also, is there a good link for how to set up two-way replication? As noted on the FAQ comments section, there is very little information in the documentation. Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question...
I am in the process of setting up replication for my mysql servers, and had a couple of questions I wanted to clarify. One of the slaves will be located in an offsite hotsite, and another local. The hotsite slave will only be used to mirror the data, in the event of a failure or accident at the office, the data is safe in the offsite location. The local slave will server 2 purposes, and here is where my question comes in. Currently I shutdown the mysql server once/night for backups. When I get the replication in place I would like to do the backups on the local slave so the master remains up and available 24x7. So 2-3 times per day the slave would go down run a quick backup and get back to its slave duties. When the server goes down for backup, it will pick back up where it was when it comes back online correct? I read in the archives that the replication is near instantanous depending on network connection. Would it be beneficial for the master and slave to have an additional network card and the replication be done over a cross-over cable and therefore out-of-band with the general network traffic? What kind of traffic volume is generated with replication, our database has a pretty steady read/update volume throughout the day and its pretty much 50/50 read/write. I do plan to offload some of the reads (such as for daily reports and informational sites to the slave. Hopefully this isn't too long, and clear. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question...
At 04:02 AM 7/4/2003 -0500, woody at nfri dot com wrote: What kind of traffic volume is generated with replication, our database has a pretty steady read/update volume throughout the day and its pretty much 50/50 read/write. I do plan to offload some of the reads (such as for daily reports and informational sites to the slave. According to this, the network traffic over your replication path will be about half of the normal traffic to your master plus any duties you assigned to your slave. Think of it as every write to the master happening again to the slave, so if you have a 50/50 ratio then half of the traffic will be duplicated in the network. In very high availability systems we typically use two networks. The first network provides access to services - this would be your current network. The second network (you were thinking of using a cross-over cable) would be used for administration and backups. Rather than using a cross-over cable for your replication, you should consider using a separate network that you can eventually build to all of your critical boxes. Plan to use this network for all of your admin, backup, and replication mechanisms. that said, if your system is and will remain small, and if you currently could add half again as much traffic to your current network, then you will be fine to stay with the one network you have - just be sure you have a good switch in place (preferably only one) between your master and primary slave servers. Hope this helps, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question...
Hi Woody, I can't answer your question, but I just wanted you to know that I like your signature ;-). Thats definitely a good one.!! Best regards Nils Valentin Tokyo/Japan 2003 7 4 18:02woody at nfri dot com : I am in the process of setting up replication for my mysql servers, and had a couple of questions I wanted to clarify. One of the slaves will be located in an offsite hotsite, and another local. The hotsite slave will only be used to mirror the data, in the event of a failure or accident at the office, the data is safe in the offsite location. The local slave will server 2 purposes, and here is where my question comes in. Currently I shutdown the mysql server once/night for backups. When I get the replication in place I would like to do the backups on the local slave so the master remains up and available 24x7. So 2-3 times per day the slave would go down run a quick backup and get back to its slave duties. When the server goes down for backup, it will pick back up where it was when it comes back online correct? I read in the archives that the replication is near instantanous depending on network connection. Would it be beneficial for the master and slave to have an additional network card and the replication be done over a cross-over cable and therefore out-of-band with the general network traffic? What kind of traffic volume is generated with replication, our database has a pretty steady read/update volume throughout the day and its pretty much 50/50 read/write. I do plan to offload some of the reads (such as for daily reports and informational sites to the slave. Hopefully this isn't too long, and clear. -- Woody In a world without boundaries why do we need Gates and Windows? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
