Replication - multiple masters
Hello, list. I have seen this subject come up here more than once, but I have never seen a solution I liked. The drill is pretty standard: I have several (4 to be more precise) MySQL 4.1.2 servers running 13 different databases. All of them use MyISAM tables, except for one, which uses some InnoDB tables. I didn't want to (okay, I don't have the budget to) set up 4 slaves, one for each master, so I thought how could I use a single server to be slave for all 4 master servers. I don't like the obvious solution, which is to run 4 instances of MySQL on the slave, because this wastes my precious memory. I also wouldn't like to keep dumping and importing tables all day long, because mysqldump locks the master servers while doing the dump. So I came up with something that made me happy and seems to be working. Well, for at least 4 days now. It goes like this: First, replicate all master databases on the slave server just like the replication FAQ specifies (i.e.: lock tables, show master status, write down info, mysqldump, unlock tables). At this point you can choose your slave server to be slave for any of your masters. Now the tricky part begins. For each of your master servers, do the following on the slave: 1. change master to master #1 2. start slave 3. see that it is replicating ok 4. stop the slave io_thread (stop slave io_thread) 5. wait until the sql_thread has processed all the relay log (you must see Has read all relay log; waiting for the slave I/O thread to update it as the output of mysqladmin processlist | grep system user) 6. stop the slave (stop slave) and then copy master.info to master.1 (this is master #1, remember) Now repeat steps 1 to 5 for all the masters (#2, #3 etc). I had to do that four times, so I ended up with master.1 to master.4. Everytime you reach step 5 your relay log should have been fully processed by the slave sql_thread. Running change master ensures another relay log is created from scratch. All you need now is rotate through your master.# files. This is an example for my situation (4 masters): a. Read line #2 from master.1; this is the master_log_file parameter b. Read line #3 from master.1; this is the master_log_pos parameter c. Read line #4 from master.1; this is the master_host parameter d. Read line #5 from master.1; this is the master_user parameter e. Read line #6 from master.1; this is the master_pass (shame on me, I'm not using passwords, so I don't know if it will work on that situation) f. On mysql do a change master using the values gathered above on the parameters g. Still on mysql do a start slave To point the slave to the next master, you repeat steps 4, 5, 6, 1, 2, 3 (on this order) from the previous instructions (of course, use the values your got from a-g on step #1). I wrote a little script (attached to this message) that does all the job of rotating through the masters for me. I have it on the system crontab to run every 10 minutes. So far, so good. So... what am I missing here? If anyone wants more details, just ask. I'd be pleased to help. Oh yes. I don't have to say to try this stuff at your own risk, right? :-) Regards. -- Thiago Figueiró Gerência de Operações [EMAIL PROTECTED]Cel.: (21) 9329-0308 (21) 9888-6784 rotate_mysql_slave.DEFANGED-22 Description: application/defanged-22 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
I've come up with pretty much the same solution to that problem. Here's an alternative solution that requires a lot more work, but is prettier. Set up a MySQL proxy server (of sorts). What it will do is act as a slave to multiple masters, merge the log files it receives from them, and act as a master. It will not have any databases, or anything like that. The MySQL protocol is fairly simple, so I can't imagine this would be TOO hard. Anyway, we're not actually going to do it that way, but I just thought I'd point out that there is an alternative. Thiago Conde Figueiró - nTime wrote: Hello, list. I have seen this subject come up here more than once, but I have never seen a solution I liked. The drill is pretty standard: I have several (4 to be more precise) MySQL 4.1.2 servers running 13 different databases. All of them use MyISAM tables, except for one, which uses some InnoDB tables. I didn't want to (okay, I don't have the budget to) set up 4 slaves, one for each master, so I thought how could I use a single server to be slave for all 4 master servers. I don't like the obvious solution, which is to run 4 instances of MySQL on the slave, because this wastes my precious memory. I also wouldn't like to keep dumping and importing tables all day long, because mysqldump locks the master servers while doing the dump. So I came up with something that made me happy and seems to be working. Well, for at least 4 days now. It goes like this: First, replicate all master databases on the slave server just like the replication FAQ specifies (i.e.: lock tables, show master status, write down info, mysqldump, unlock tables). At this point you can choose your slave server to be slave for any of your masters. Now the tricky part begins. For each of your master servers, do the following on the slave: 1. change master to master #1 2. start slave 3. see that it is replicating ok 4. stop the slave io_thread (stop slave io_thread) 5. wait until the sql_thread has processed all the relay log (you must see Has read all relay log; waiting for the slave I/O thread to update it as the output of mysqladmin processlist | grep system user) 6. stop the slave (stop slave) and then copy master.info to master.1 (this is master #1, remember) Now repeat steps 1 to 5 for all the masters (#2, #3 etc). I had to do that four times, so I ended up with master.1 to master.4. Everytime you reach step 5 your relay log should have been fully processed by the slave sql_thread. Running change master ensures another relay log is created from scratch. All you need now is rotate through your master.# files. This is an example for my situation (4 masters): a. Read line #2 from master.1; this is the master_log_file parameter b. Read line #3 from master.1; this is the master_log_pos parameter c. Read line #4 from master.1; this is the master_host parameter d. Read line #5 from master.1; this is the master_user parameter e. Read line #6 from master.1; this is the master_pass (shame on me, I'm not using passwords, so I don't know if it will work on that situation) f. On mysql do a change master using the values gathered above on the parameters g. Still on mysql do a start slave To point the slave to the next master, you repeat steps 4, 5, 6, 1, 2, 3 (on this order) from the previous instructions (of course, use the values your got from a-g on step #1). I wrote a little script (attached to this message) that does all the job of rotating through the masters for me. I have it on the system crontab to run every 10 minutes. So far, so good. So... what am I missing here? If anyone wants more details, just ask. I'd be pleased to help. Oh yes. I don't have to say to try this stuff at your own risk, right? :-) Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Having that many instances on one box is going to be a management nightmare. I can only imagine the recovery scenarios should you have a hardware problem. Perhaps you may want to think about writing your metric data to a local mysql instance then pulling the data from each instance into the depot database with a script that runs every few minutes. Using this pull methodology you don't have to worry about losing metrics if your depot is down but you don't have the burden of up to tweleve databases to manage on one machine (twenty four databases total). Another option would be to write the metrics to a flat file on the web server, then simply serve that file up via HTTP. You then have only one database to manage, you can pull the metrics into it very easily and you still don't have to worry about a down depot server. In your depot you then have a couple options. You could have one master table that contains metrics from all the machines and contains a column for the machine name or you could have a seperate table for each machine, and if you want to examine metrics for all machines you could use a merge table. --- Marc Knoop [EMAIL PROTECTED] wrote: Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? -- ../mk -- 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 - multiple masters
On Tue, Jul 13, 2004 at 11:23:03AM -0400, Marc Knoop wrote: Jeremy Zawodny writes: The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. You cannot do that. snip You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. My planned DEPOT server is a Dell PowerEdge - dual Xeon, 2GB memory and oodles of disk space. Could mysql, or Linux ES 3.0 for that matter, handle it? Is there anyone on this list running several instances of mysql on the same box? Any experiences to share? That should be a problem at all. I know of much larger instances (millions of records) doing the same on similar (or less) hardware. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
Jeremy Zawodny writes: Can you, or anyone comment on the praticality of doing so? I estimate 10,000 to 30,000 records per web server, per day using 3 remote web servers. The number of web servers would not likely grow to more than 12. That should be a problem at all. I know of much larger instances (millions of records) doing the same on similar (or less) hardware. Jeremy - good to hear. Now that I know this is technically possible, which of the following possible solutions would be the cleanest or most efficient from a management perspective: 1) Use mysql replication to have mirror dbs on the DEPOT server. A job would regularly run on DEPOT to consolidate all data into one db so that an external system can query/report on. 2) Do not use mysql replication and instead have a job on DEPOT regularly pull from each webserver and consolidate all data into one db so that an external system can query/report on. 3) Same as #2, except the web servers would *push* to DEPOT instead of being *pulled* from. As another reader commented, #1 could be difficult to manage because of the number of DBs (N*2). Plus, DEPOT is already a master to all web servers for read only data. #2 and #3 seem to be more appropriate, as long as the jobs are FAST and can be managed. Would Perl be the ideal candidate for this? Since the web servers are remote, performance of DEPOT updates is important - something replication was good at. It's nice to have different solutions to this puzzle. Choosing the most elegant solution is tricky! -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - multiple masters
I have 4 servers in my environment: DEPOT - master server WWW1 - web server #1 WWW2 - web server #2 WWW3 - web server #3 The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. Is configuration as simple as the correct entries in my.cnf? That is, can muliple entries for master-host, master-user... exist? Any caveats with this configuration? Question #2 - A small databases exists on the DEPOT which I would like replicated to all web servers. Is there any reason why this would not work with the above situation [DEPOT acting as a slave for multiple masters]? Many thanks, I hope I have omitted any relevant information... -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
On Mon, Jul 12, 2004 at 03:49:33PM -0400, Marc Knoop wrote: I have 4 servers in my environment: DEPOT - master server WWW1 - web server #1 WWW2 - web server #2 WWW3 - web server #3 The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. Is configuration as simple as the correct entries in my.cnf? That is, can muliple entries for master-host, master-user... exist? Any caveats with this configuration? You cannot do that. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html See figure 7-2. You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, multiple masters
I have successfully configured two mysql instances to replicate to each other (According to /doc/en/Replication_Features.html, it is possible to do it in a A-B-C-A relationship, but I only did it with two servers and I don't have log-slave-updates on (I think if I did, it would immediately stop the slave thread as the updates get caught in a loop). FYI, I'm using a self-compiled RPM of 3.23.51 on Linux 2.4. All my tables are InnoDB. It's very slick, updates on either server get propagated to the other server. I have not stress tested it yet, and my (simple) application only does updates to a single server at a time. The only problem is the auto_increment columns in the tables. Updates that occur on both machines at the same time, that generate the same auto_increment value, causes the slave threads to die: and rightly so. I can, of course, program my application to generate non-conflicting, server independant key values without the need for the auto_increment, but has anyone had any experience with this? Is this the only impediment to doing full two-way replication? What other 'data corruption' or 'out of sync' issues are there to worry about? Andy. [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication, multiple masters
On Mon, Nov 25, 2002 at 06:02:19PM -0600, Andy Bakun wrote: I have successfully configured two mysql instances to replicate to each other (According to /doc/en/Replication_Features.html, it is possible to do it in a A-B-C-A relationship, but I only did it with two servers and I don't have log-slave-updates on (I think if I did, it would immediately stop the slave thread as the updates get caught in a loop). Err, no. MySQL prevents that looping as long as each server has a unique server-id. It's very slick, updates on either server get propagated to the other server. I have not stress tested it yet, and my (simple) application only does updates to a single server at a time. The only problem is the auto_increment columns in the tables. Updates that occur on both machines at the same time, that generate the same auto_increment value, causes the slave threads to die: and rightly so. I can, of course, program my application to generate non-conflicting, server independant key values without the need for the auto_increment, but has anyone had any experience with this? Is this really the only impediment to doing full two-way replication? That's the big limitation, yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 8 days, processed 268,724,961 queries (374/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
