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.
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
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: 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.
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
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
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
RE: Replication question
Hello everybody, Is it possible to set up this kind of replication and what parameters I have to set in the my.cnf file ? Table A own by server A and replicated to server B and C Table B own by server B and replicated to server A Table C own by server C and replicated to server A The mysql level installed on each server is 4.0.9. Thank you in advance for your help. Best regards mysql replication query - 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 question
On Wed, Feb 05, 2003 at 10:37:29AM +0100, RAHARD Matthieu wrote: Hello everybody, Is it possible to set up this kind of replication and what parameters I have to set in the my.cnf file ? Table A own by server A and replicated to server B and C Table B own by server B and replicated to server A Table C own by server C and replicated to server A Not easily. A MySQL slave may only have one master. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 2 days, processed 93,565,682 queries (416/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
RE: Replication question
Hello, When you say not easily, you say that it's possible. Can you explain to me what I have to do to realize that even if it's a complex solution. Thank's for your help. -Message d'origine- De : Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Envoyé : jeudi 6 février 2003 03:05 À : RAHARD Matthieu Cc : mysql Objet : Re: Replication question On Wed, Feb 05, 2003 at 10:37:29AM +0100, RAHARD Matthieu wrote: Hello everybody, Is it possible to set up this kind of replication and what parameters I have to set in the my.cnf file ? Table A own by server A and replicated to server B and C Table B own by server B and replicated to server A Table C own by server C and replicated to server A Not easily. A MySQL slave may only have one master. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 2 days, processed 93,565,682 queries (416/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
Re: replication question
I set up two-way replication with two servers, A and B. Since all queries go to only one server, it seems not to matter. Anyway. When A starts it registers itself to B, and vice versa. I thought query SHOW SLAVE HOSTS to A should shows only about B and B should do about A, but both show the same result as following. +---+--+--+---+---+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +---+--+--+---+---+ | 1 | B | 3306 | 0 | 2 | | 2 | A | 3306 | 0 | 1 | +---+--+--+---+---+ Is it right? Why does the result show both master and slave? Thanks. -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com I don't think this is correct. A quick look at the source shows that it iterates over slave_list to generate the output. I don't think that A should be in it's own slave list. I don't know why it is. I'll look into this further. Is this causing problems for your application? What version are you using? Nick -- For technical support contracts, visit https://order.mysql.com/?ref=mnle __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Nicholas Leippe [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Spanish Fork, Utah USA ___/ www.mysql.com - 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 question
Alexander, Saturday, September 28, 2002, 12:47:19 PM, you wrote: AS Is it feasible to use replication to keep the database which powers my AS website up-to-date? The database itself is on the hosting companies server AS and contacted via dialup so the connection isn't there all of the time. AS Naturally I keep a copy of the database on my local machine too. If you use replication you don't need to be connected all the time: http://www.mysql.com/doc/en/Replication_FAQ.html -- 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 - 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 question
That would be a great way to keep your website up to date. The slave can handle loss of connection (though some data may not make sense if part of it is missing), and resume where it has left of previously. Or you could also manually start and stop the slave process when you know there is a live connection. As long as you are not modifying the slave database, that could cause problems. Some people have successfully looped thier replication process (A-B, B-A), which I know nothing about. I am assuming you want to have the hosting companies server replicate from your local copy of the database? If that is the case, starting and stopping the slave process is not really an option (unless you have your own install of mysql). And of course if you do not have a static IP address it would be a real pain in the behind ( since your hosting company will probably be the one changing the slave access, unless of course you have your own install ) Good luck Luc -Original Message- From: Alexander Shaw [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 28, 2002 5:47 AM To: MySQL List Subject: Replication question Hi Is it feasible to use replication to keep the database which powers my website up-to-date? The database itself is on the hosting companies server and contacted via dialup so the connection isn't there all of the time. Naturally I keep a copy of the database on my local machine too. Alex Alexander Shaw Agricultural Stock and Assignment Photography www.Agri-Image.com sql query - 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 question
Keith, Tuesday, August 13, 2002, 12:20:28 AM, you wrote: KJ Is this command supported under version 3.23.51? Nope. This command is supported since 4.0 KJ SHOW NEW MASTER FOR SLAVE WITH MASTER_LOG_FILE='logfile' AND KJ MASTER_LOG_POS=pos AND MASTER_LOG_SEQ=log_seq AND KJ MASTER_SERVER_ID=server_id KJ What does MASTER_LOG_SEQ refer to? master log sequence (.001, .002 and so on) -- 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 - 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 Question
At 11:10 -0700 8/13/02, Vicky Gonzalez wrote: Hi all, I am a newbie here. I was hoping someone would be able to tell me if hostnames for the Linux OS is required for MySql Replication, and if so why? I have been combing the manual and archives, but had no luck in finding this answer. When the slave server connects to the master server to get updates, it needs to know what host the master server is running on. But what do you mean by hostnames for the Linux OS? Operating systems don't have hostnames. Any and all help is greatly appreciated. Thanks!!! ~Vicky - 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 Question
From: Paul DuBois on August 13, 2002 11:13 AM wrote in response: At 11:10 -0700 8/13/02, Vicky Gonzalez wrote: Hi all, I am a newbie here. I was hoping someone would be able to tell me if hostnames for the Linux OS is required for MySql Replication, and if so why? I have been combing the manual and archives, but had no luck in finding this answer. When the slave server connects to the master server to get updates, it needs to know what host the master server is running on. Would the IPs alone be sufficient in allowing the slave server to get the needed update from the master server? How does the slave query the master for the information? But what do you mean by hostnames for the Linux OS? Operating systems don't have hostnames. You are correct, I should have been more clear and specified I wanted to only use IPs and not server hostnames for the MySQL Replication setup/feature. Thank you for the prompt response. ~Vicky - 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 Question
At 11:28 -0700 8/13/02, Vicky Gonzalez wrote: From: Paul DuBois on August 13, 2002 11:13 AM wrote in response: At 11:10 -0700 8/13/02, Vicky Gonzalez wrote: Hi all, I am a newbie here. I was hoping someone would be able to tell me if hostnames for the Linux OS is required for MySql Replication, and if so why? I have been combing the manual and archives, but had no luck in finding this answer. When the slave server connects to the master server to get updates, it needs to know what host the master server is running on. Would the IPs alone be sufficient in allowing the slave server to get the needed update from the master server? How does the slave query the master for the information? It can use any connection that MySQL supports: TCP/IP, UNIX socket file, named pipe on NT. But what do you mean by hostnames for the Linux OS? Operating systems don't have hostnames. You are correct, I should have been more clear and specified I wanted to only use IPs and not server hostnames for the MySQL Replication setup/feature. That shouldn't be a problem. When you use a hostname, it gets mapped to an IP number anyway when the connection is established. Thank you for the prompt response. ~Vicky - 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 Question
On Thu, Jul 04, 2002 at 09:33:54AM +0100, David Shields wrote: The MySQL manual for 3.23.38, Section 11.4 states that you can have a replication chain A - B - C - A. I guess this means A Master B Slave B Master C Slave C Master A Slave Can this loop be reduced to the most trivial case A - B - A ? If so, it would help me with a roving laptop issue I have. Naturally I accept there are contraints (creating dup key items on A and B while laptop disconnected etc), but is it doable ? Yes. That's a simple dual-master setup. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 35 days, processed 788,301,416 queries (257/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
RE: replication question
I am replicating to NT Server system from Red Hat Linux was not aware of any issues to win2k from linux with replication Is there something I am not aware of? -Original Message- From: Bartomiej Dolata [mailto:[EMAIL PROTECTED]] Sent: Monday, June 10, 2002 9:44 AM To: Mysql@Lists. Mysql. Com Subject: replication question hello, can someone please explain why it is not possible to do the replication between e.g. linux and bsd systems ? why wouldnt it be possible to exchange data in system-independent fashion ? i have set up replication between mysql running on win2k, but am unable to do that with linuxw2k nor linuxopenbsd combination. i would like to see technical explanation, not just 'filesystem difference' best regards, terry - 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 - 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 question
hello, I am replicating to NT Server system from Red Hat Linux was not aware of any issues to win2k from linux with replication Is there something I am not aware of? i dont know, but i am having major problems with it. i keep getting Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position ... whats strange is that master.info has no remote logfile name in it. i cant think of any reason why my setup of replication between w2k and w2k would be different from one i am trying to setup between linux (slave) and w2k (master) maybe my mysql rpm is broken ? ... regards, terry - 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 question
Are you positive that you are running compatible versions of MySql on both platforms? - Original Message - From: Terry [EMAIL PROTECTED] To: Luc Foisy [EMAIL PROTECTED]; MYSQL-List (E-mail) [EMAIL PROTECTED] Sent: Monday, June 10, 2002 9:59 AM Subject: RE: replication question | hello, | | I am replicating to NT Server system from Red Hat Linux | was not aware of any issues to win2k from linux with replication | | Is there something I am not aware of? | | i dont know, but i am having major problems with it. | i keep getting Slave: Failed reading log event, reconnecting to retry, | log 'FIRST' position ... | | whats strange is that master.info has no remote logfile name in it. | | i cant think of any reason why my setup of replication between w2k and | w2k | would be different from one i am trying to setup between linux (slave) | and w2k (master) | | maybe my mysql rpm is broken ? ... | | regards, | terry | | | | - | 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 | - 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 question
Subject: Re: replication question Are you positive that you are running compatible versions of MySql on both platforms? linux: mysql rebuild from source rpm: /usr/sbin/mysqld, Version: 3.23.49-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock openbsd: mysql build from sources: /opt/mysql/libexec/mysqld, Version: 3.23.49-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock 2 win2k machines: mysql installed by installer: MySql, Version: 3.23.49-max-debug-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock best regards, terry - 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 question
Jeremy You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Yes I did, but what put me off slightly was the concept of a master and slave. My requirement is per sql database not per sql server. Am I being stoopid or is this configuration possible:- Web Server Local Server DB1 copy of DB1 copy of DB2 DB2 Cheers Rob - 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 question
Hi Rob Read the manual, you can do exactly what you need to and it is explained in the manual. I am doing it myself. Regards Warren You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Yes I did, but what put me off slightly was the concept of a master and slave. My requirement is per sql database not per sql server. Am I being stoopid or is this configuration possible:- Web Server Local Server DB1 copy of DB1 copy of DB2 DB2 Cheers Rob - 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 - 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 question
On Wed, Mar 20, 2002 at 11:51:47AM -, Rob Moore wrote: Jeremy You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Yes I did, but what put me off slightly was the concept of a master and slave. My requirement is per sql database not per sql server. Am I being stoopid or is this configuration possible:- Web Server Local Server DB1 copy of DB1 copy of DB2 DB2 That will work. Each server just needs to be a slave of the other. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 41 days, processed 1,236,930,317 queries (346/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
Re: replication question
On Wed, Mar 20, 2002 at 02:02:49AM -, Rob Moore wrote: Hi there Apologies if this has been asked before; couldnt find anything in archives... We have a mysql database on a web server that I transfer manually to an office machine, and a different database on the office machine that I transfer to the web server. In each case only the database on the machine I transfer from ever gets updated. On the receiving machine read-only operations are performed. MySQL's replication should work great for this, provided there's no firewall in the way. Can anyone tell me if is possible to set up some sort of replication scheme/schedule for this; I understood (possibly mistakenly) that replication involves nominating a master and a slave for each entire server, not per database. You can set it up so that the slave only gets updates to certain databases. Have you looked at the replication docs in the manual yet? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 40 days, processed 1,198,178,348 queries (342/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
Re: Replication question
On Fri, Feb 15, 2002 at 10:32:06AM +, Simon Windsor wrote: Currently, I maintain two mysql server, hosted by separate ISPs, and keep them synchronised using standard MySQL replication. Great! Server A is master+slave to server B, and likewise server B is master +slave to server A. This allows both servers to receive inserts/updates/deletes and replicate them accordingly. This has, and continues to work well. The problem I have is simple, it has been decided to have a third web site, with an associated database. Okay... The replication model I have implemented only works with TWO servers. Or so you think. :-) The only solution to this is to add to one servers my.cnf the option LOG-SLAVE-UPDATES and enable read-only replication to this new server. Right. That's one way to do it. If I do add this option, and one server logs all its 'native' transactions + 'replicated' transactions, will this confuse the second server? Nope. Will the second server try and apply the remote native transaactions only, or will I get into a loop of a machine applying its own transactions back on to itself ? It's not a problem because each server has a unique id. The server id of the server which originated each query is stored in the binary log. This helps to prevent looping problems and servers getting confused. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 10 days, processed 328,170,509 queries (377/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
Re: replication question
The problem is still occurring. As I said in my earlier message, it tends to (but is not limited to) the beginning of a binary log file. Here is an example: mysql show slave status; +--+-+-+---+---+-+---+-+-+++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+---+-+---+-+-+++--+ | db1.tias.com | slave | 3306| 60| spode-bin.009 | 73 | No| | | 1146 | error 'Table 'curioscape.avstemp' doesn't exist' on query '# /etc/httpd/cgi-bin/search.curioscape.fcgi (19278) INSERT IGNORE INTO avstemp SET siteid='9241', payrate=0, relevance='0.007758' ' | 0| +--+-+-+---+---+-+---+-+-+++--+ 1 row in set (0.01 sec) Note that avstemp is a temporary table (CREATE TEMPORARY TABLE) and that the failures are happening on temporary tables. So I do a: mysql SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; and get: mysql show slave status; +--+-+-+---+---+-+---+-+-++-+--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+---+-+---+-+-++-+--+ | db1.tias.com | slave | 3306| 60| spode-bin.009 | 238 | No| | | 1146 | error 'Table 'curioscape.avstemp' doesn't exist' on query '# /etc/httpd/cgi-bin/search.curioscape.fcgi (19278) INSERT IGNORE INTO avstemp SET siteid='59588', payrate=0, relevance='0.007758' ' | 0| +--+-+-+---+---+-+---+-+-++-+--+ 1 row in set (0.00 sec) I've been untangling it by skipping over the errors. Since they are temporary tables and no client will ever seem them (because the client that created them was talking to a different server), the contents are really irrelevant. What can I do to stop this from happening? Is there more information I can provide to diagnose this? I could provide the error logs. Or even the binlogs (although they are 1GB each). Mike Wexler wrote: Jeremy Zawodny wrote: On Sun, Sep 02, 2001 at 11:40:08AM -0700, Mike Wexler wrote: I have a slave server and a master server both running 3.23.38. Every once in a while the slave stops updating: | db1.tias.com | slave | 3306| 60| spode-bin.005 | 912992502 | No| | | 1158 | Slave: query 'drop table tias.avtemp' partially completed on the master and was aborted. There is a chance that your master is
Re: replication question
On Sun, Sep 02, 2001 at 11:40:08AM -0700, Mike Wexler wrote: I have a slave server and a master server both running 3.23.38. Every once in a while the slave stops updating: | db1.tias.com | slave | 3306| 60| spode-bin.005 | 912992502 | No| | | 1158 | Slave: query 'drop table tias.avtemp' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; | 0| [snip] Why is this happening and what can be done to prevent it from happening. Hard to say. Was there actually a problem on the master? Did the slave work fine once you set the skip counter? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,533,446 queries (109/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
Re: replication question
Jeremy Zawodny wrote: On Sun, Sep 02, 2001 at 11:40:08AM -0700, Mike Wexler wrote: I have a slave server and a master server both running 3.23.38. Every once in a while the slave stops updating: | db1.tias.com | slave | 3306| 60| spode-bin.005 | 912992502 | No| | | 1158 | Slave: query 'drop table tias.avtemp' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; | 0| [snip] Why is this happening and what can be done to prevent it from happening. Hard to say. Was there actually a problem on the master? Not that I know of. Did the slave work fine once you set the skip counter? In this case yes. At least for a while but inevitably this either happens again. Or I get an error that insert or drop operations are being applied to a (temporary) table that doesn't exist. The latter error usuallly occurs at the beginning of a binlog and usually occurs multiple times. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,533,446 queries (109/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
Re: Replication question -
On Mon, Apr 30, 2001 at 02:24:53PM +1200, Desmond Armstrong wrote: Hi everyone, I was wondering if anyone could help me/or has come across the following problem: It appears to have nothing to do with MySQL, so you might try asking in a more on-topic forum. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 116 days, processed 721,262,890 queries (71/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
Re: Replication question!
wrote: Hi... I Have a Master(version 3.23.33) and one Slave(version 3.23.32) When I shutdown the slave, And make some change Master. then I restart the slave. But, Slave didn't catch the change on Master. bug or feature? thank you for advanced answer. 3.23.32 has a known slave problem. Upgrade it to 3.23.33. - 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: Re: Replication question.
On Sun, Feb 25, 2001 at 03:29:37PM +0900, wrote: It sounds like you are using a version of MySQL that had a replication bug in it. I suspect that if you upgrade to 3.23.33 the problem will go away. No, Master is 3.23.33, and slave is 3.23.32 3.23.32 was troublesome for slaves. Here http://www.mysql.com/doc/N/e/News-3.23.33.html you'll find an entry which says: Fixed bug in replication that broke slave server start with existing master.info. This fixes a bug introduced in 3.23.32. I'd suggest and upgrade to see if it solves your problem... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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