List:General Discussion« Previous MessageNext Message »
From:fsb Date:January 13 2010 9:11pm
Subject:Re: upgrading mysql
View as plain text  
On 1/13/10 2:28 PM, "Lawrence Sorrillo" <sorrillo@stripped> 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.

i can't point at anything in your recipe and say that it doesn't work. it
might work. i'd be nervous that something in steps 5 and 6 might involve a
change on the master that needs to be replicated. since your using a dump
and not a binary copy of myisam file, i suppose this ought to be safe. but i
would be nervous all the same.

on the other hand, i do know that the recipe i gave works because i've used
it often. it also has the virtue of no need for "recording binlog file names
and position etc etc". plus it's the procedure recommended by the mysql folk
themselves, which is worth something to me.

the other thing i've done is:

initial status: A is the master and B is the slave. service is operating off
the master.

1 stop B, upgrade it, restart it, let it catch up.

2 stop service and then stop A

3 change B's conf file to make it the master. restart it

4 resume service using B

5 upgrade A and bring it online as a slave

this has the virtue of very short service outage. with some rehearsal, it
isn't beyond my skills.


> Anyone knows anything special about position 106? It seems to be the
> very initial position in MySQL 5.1 servers?

the manual says:

"If the master has been running previously without binary logging enabled,
the log name and position values displayed by SHOW MASTER STATUS or
mysqldump --master-data will be empty. In that case, the values that you
need to use later when specifying the slave's log file and position are the
empty string ('') and 4."

perhaps you have an init-file that advances it to position 106?



> 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
>>>>> 
>>>>> 
>>>>> 
>>>>>     
>>>>>         
>>>>   
>>>>       
>>>     
>> 
>> 
>> 
>>   
> 
> 


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