On Jan 13, 2010, at 1:28 PM, Lawrence Sorrillo wrote: > The issue is that in theory this should work given the facts announced by > MySQL regarding binary logging and replication. > I can certainly do it the way you propose, but to my mind I should also be > able to do it using the fact that both machines are fully synced and hence at > that point I should be able to to local respective dumps and restores and > still be in sync. > > Anyone knows anything special about position 106? It seems to be the very > initial position in MySQL 5.1 servers?
It's not. 4 is still the initial position, as shown by the "at 4" in your mysqlbinlog output below. The 106 that you observe is the position *after* the server writes the initial event to the binary log. It writes this event immediately after opening the file, even before executing any statements. If you want the gory details: This event is the format description event that identifies in the binary log file the server version and other information. See http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log#Binary_Log_Versions if you have a high tolerance for pain. :-) > > mysql> show master status; > +-------------------+----------+--------------+------------------+ > | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | > +-------------------+----------+--------------+------------------+ > | XXXXX-bin.000001 | 106 | | | > +-------------------+----------+--------------+------------------+ > 1 row in set (0.00 sec) > > > > r...@xxxx:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog > mssdb2-bin.000001 > /*!40019 SET @@session.max_insert_delayed_threads=0*/; > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; > DELIMITER /*!*/; > # at 4 > #100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4, server v > 5.1.42-log created 100113 13:50:40 at startup > # Warning: this binlog is either in use or was not closed properly. > ROLLBACK/*!*/; > BINLOG ' > ABZOSw8FAAAAZgAAAGoAAAABAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA > AAAAAAAAAAAAAAAAAAAAFk5LEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC > '/*!*/; > DELIMITER ; > # End of log file > ROLLBACK /* added by mysqlbinlog */; > /*!50003 SET completion_ty...@old_completion_type*/; > r...@xxxx:/usr/local/mysql/data ] > > ~Lawrence > > > > > Tom Worster wrote: >> Frankly, I didn't entirely understand what you were proposing. I got lost >> around step 6. >> >> Is the issue total time for the procedure or service downtime? >> >> >> On 1/12/10 12:58 PM, "Lawrence Sorrillo" <sorri...@jlab.org> wrote: >> >> >>> This is two upgrades done in sequence(the reload takes about three hours >>> per machine) . I can do what I am proposing in parallel. >>> >>> Do you see it as problematic? >>> >>> ~Lawrence >>> >>> >>> Tom Worster wrote: >>> >>>> How about: >>>> >>>> 1 shut down the slave, upgrade it, restart it, let it catch up. >>>> >>>> 2 shut down the master, upgrade it, restart it, let the slave catch up. >>>> >>>> ? >>>> >>>> >>>> >>>> >>>> >>>> On 1/12/10 12:34 PM, "Lawrence Sorrillo" <sorri...@jlab.org> wrote: >>>> >>>> >>>>> Hi: >>>>> >>>>> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. >>>>> >>>>> I want to so something like follows: >>>>> >>>>> 1. Stop all write access to the master server. >>>>> 2. Ensure that replication on the slave is caught up to the last change >>>>> on the master. >>>>> 3. stop binary logging on the master. >>>>> 4. stop replication on the slave. >>>>> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload >>>>> master dump file under 5.1 server ( binary logging is turned off) >>>>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload >>>>> slave dump file under 5.1 server. >>>>> 7. After loading is complete, test then start binary logging on master >>>>> while still preventing updates to updates. >>>>> 8. After loading slave, test then start slave (get configs in place and >>>>> restart server). >>>>> >>>>> I am thinking that in this scenario I dont have to bother with recording >>>>> binlog file names and position etc etc. >>>>> That both servers will have the same databases abd replication and >>>>> binary logging will start on the two databases with no data loss and >>>>> continue forward. >>>>> >>>>> >>>>> Comments? >>>>> >>>>> ~Lawrence >>>>> >>>>> >>>>> >>>>> >>>> >>> >> >> >> >> > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com > -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org