List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 14 2010 2:13am
Subject:Re: upgrading mysql
View as plain text  
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)
> 
> 
> 
> root@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_TYPE=@OLD_COMPLETION_TYPE*/;
> root@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" <sorrillo@stripped> 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" <sorrillo@stripped>
> 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=1
> 

-- 
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com

Thread
upgrading mysqlLawrence Sorrillo12 Jan
  • Re: upgrading mysqlTom Worster12 Jan
    • RE: upgrading mysqlJoshua Gordon12 Jan
    • Re: upgrading mysqlLawrence Sorrillo12 Jan
      • Re: upgrading mysqlTom Worster12 Jan
        • Re: upgrading mysqlSuresh Kuna13 Jan
        • Re: upgrading mysqlLawrence Sorrillo13 Jan
          • Re: upgrading mysqlfsb13 Jan
          • Re: upgrading mysqlPaul DuBois14 Jan
  • Re: upgrading mysqlShawn Green12 Jan
    • Re: upgrading mysqlLawrence Sorrillo12 Jan
      • Re: upgrading mysqlPaul DuBois12 Jan
  • file per table performanceBryan Cantwell12 Jan
    • Re: file per table performanceJohnny Withers12 Jan