upgrading mysql

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=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

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" 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=gcdmg-mysql-2@m.gmane.org

RE: upgrading mysql

Also see http://dev.mysql.con/doc/refman/5.0/en/mysql-upgrade.html.
And make sure you make a backup before you do anything :)

-----Original Message-----
From: Tom Worster [mailto:fsb@thefsb.org]=20
Sent: Tuesday, January 12, 2010 10:47 AM
To: Lawrence Sorrillo; mysql@lists.mysql.com
Subject: Re: upgrading mysql

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" wrote:

> Hi:
>=20
> I want to upgrade a master and slave server from mysql 4.1 to mysql
5.1.
>=20
> I want to so something like follows:
>=20
> 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).
>=20
> 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.
>=20
>=20
> Comments?
>=20
> ~Lawrence
>=20
>=20
>=20



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Djgordon@westernwats.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: upgrading mysql

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" 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=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

Lawrence Sorrillo 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.

ok

> 2. Ensure that replication on the slave is caught up to the last change
> on the master.

why? You are just going to replace it later.

> 3. stop binary logging on the master.

why? You can just disconnect the slave


> 4. stop replication on the slave.

You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL
thread can keep moving along.

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

Yes. No need to create binary logs for the rebuild.

> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload
> slave dump file under 5.1 server.

There is a faster way.

> 7. After loading is complete, test then start binary logging on master
> while still preventing updates to updates.

Once you have QA-ed your new 5.1 master, you can shut it down then copy
the entire image (binaries and all) directly to the slave machine. This
is much faster than rebuilding from a dump and it ensures that you have
identical data to start replication with.

After the copy, then restart the master with binary logging.


> 8. After loading slave, test then start slave (get configs in place and
> restart server).
>

Yes, it's always good to test any server image before putting it online.

The CHANGE MASTER TO command to use for the slave will be at position 4
of the first binary log created after the binary image was captured.


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

You are correct. Because you are re-imaging your slave from your master,
there is no need to track binary log or relay log positions.

See also:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto-raw data.html

** SAFETY ADVICE ** - always ensure you have a clean binary backup of
any server you want to perform major maintenance to. In the off-chance
that something does happen to go wrong, you will have it available for
the fastest possible restore-to-original-state

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

Hi:

I want to ensure that right after the reload that the same data is
present in both the master and the slave. They are in perfect sync. Then
I think its safe to consider starting binary logging and replication
etc. And after these are started, changes can start?

And in setting up replication in this manner I would not use the CHANGE
MASTER... I will just

master-host=xxx.xxx.xxx.xxx
master-connect-retry=60
master-user=auser
master-password=apassword

in the my.cnf file and restart the slave server. From there it should
start reading the binary logs and committing changes properly.

Is this correct?

~Lawrence


Shawn Green wrote:
> Lawrence Sorrillo 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.
>
> ok
>
>> 2. Ensure that replication on the slave is caught up to the last
>> change on the master.
>
> why? You are just going to replace it later.
>
>> 3. stop binary logging on the master.
>
> why? You can just disconnect the slave
>
>
>> 4. stop replication on the slave.
>
> You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL
> thread can keep moving along.
>
>> 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)
>
> Yes. No need to create binary logs for the rebuild.
>
>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and
>> reload slave dump file under 5.1 server.
>
> There is a faster way.
>
>> 7. After loading is complete, test then start binary logging on
>> master while still preventing updates to updates.
>
> Once you have QA-ed your new 5.1 master, you can shut it down then
> copy the entire image (binaries and all) directly to the slave
> machine. This is much faster than rebuilding from a dump and it
> ensures that you have identical data to start replication with.
>
> After the copy, then restart the master with binary logging.
>
>
>> 8. After loading slave, test then start slave (get configs in place
>> and restart server).
>>
>
> Yes, it's always good to test any server image before putting it online.
>
> The CHANGE MASTER TO command to use for the slave will be at position
> 4 of the first binary log created after the binary image was captured.
>
>
>> 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.
>
> You are correct. Because you are re-imaging your slave from your
> master, there is no need to track binary log or relay log positions.
>
> See also:
> http://dev.mysql.com/doc/refman/5.1/en/replication-howto-raw data.html
>
> ** SAFETY ADVICE ** - always ensure you have a clean binary backup of
> any server you want to perform major maintenance to. In the off-chance
> that something does happen to go wrong, you will have it available for
> the fastest possible restore-to-original-state
>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote:

> Hi:
>
> I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start?
>
> And in setting up replication in this manner I would not use the CHANGE MASTER... I will just
>
> master-host=xxx.xxx.xxx.xxx
> master-connect-retry=60
> master-user=auser
> master-password=apassword
>
> in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly.
>
> Is this correct?

You're upgrading to MySQL 5.1, for which several of those options no longer have any effect. Better to use CHANGE MASTER. See:

http://dev.mysql.com/doc/refman/5.1/en/replication-options-s lave.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html

--
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=gcdmg-mysql-2@m.gmane.org

file per table performance

Anyone have information they can provide on the performance hit of using
innodb_file_per_table?
I'd assume that since there are many individual tables that this would
slow performance, but perhaps not.
In a huge database, is this not a good idea, or a better one?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: file per table performance

--0016e6d7eea002f0a9047cfe270c
Content-Type: text/plain; charset=ISO-8859-1

There are a few articles on this at MySQL Performance Blog:

http://www.mysqlperformanceblog.com/?s=innodb_file_per_table +performance



On Tue, Jan 12, 2010 at 3:05 PM, Bryan Cantwell wrote:

> Anyone have information they can provide on the performance hit of using
> innodb_file_per_table?
> I'd assume that since there are many individual tables that this would slow
> performance, but perhaps not.
> In a huge database, is this not a good idea, or a better one?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d7eea002f0a9047cfe270c--

Re: upgrading mysql

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" 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" 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=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

--001636ed657e6b39de047d054730
Content-Type: text/plain; charset=ISO-8859-1

Hi,
The step 6 in simple terms is

Here we need to build two server ( both master and slave ). Instead of
building two server as it takes double the time of building in one server.
After building an server, make a copy of the first server files at OS level
and copy it to the server and start the same. Configure the replication
between the two server.

By doing this, We will save the import time in second server.

Thanks
Suresh Kuna
MySQL DBA

On Wed, Jan 13, 2010 at 3:58 AM, 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" 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" 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=sureshkumarilu@gmail.com
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--001636ed657e6b39de047d054730--

Re: upgrading mysql

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?

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 '
ABZOSw8FAAAAZgAAAGoAAAABAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAA
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" 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" 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=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

On 1/13/10 2: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.

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 '
> ABZOSw8FAAAAZgAAAGoAAAABAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAA
> 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" 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" 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=gcdmg-mysql-2@m.gmane.org

Re: upgrading mysql

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#Binar y_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 '
> ABZOSw8FAAAAZgAAAGoAAAABAAQANS4xLjQyLWxvZwAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAA
> 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" 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" 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.dubois@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=gcdmg-mysql-2@m.gmane.org