Best practice to disable log bin for mysql_upgrade

Best practice to disable log bin for mysql_upgrade

am 21.08.2009 17:12:31 von Russell E Glaue

I am upgrading minor MySQL versions, 5.0.67 to 5.0.84
I have a master-master replication setup, and want to upgrade both installations.
The ideal procedure for upgrading mysql using the mysql_upgrade command is to
have binary logging turned off during the execution of mysql_upgrade.

My situation is I do not want to turn off binary logging for the entire server,
I would like to turn off binary logging just for the session of the
mysql_upgrade connection.

mysql_upgrade does not support anything like --disable-log-bin (which seems is a
feature that should be supported for this cli app) (1)
So it seems my only option is to turn off binary logging for the entire server
while I execute mysql_upgrade. Which also means blocking write access to the
server while it runs so that statements I do want logged for replication do not
occur while binary logging is off.

Is there another simple way to achieve this? Or what is best practice that
achieves the least amount of down time?



Alternately, there are sql files in the share directory of each archive:

mysql-5.0.67-linux-i686-glibc23/share/
|-- fill_help_tables.sql
|-- mysql_fix_privilege_tables.sql
|-- mysql_system_tables.sql
|-- mysql_system_tables_data.sql
|-- mysql_system_tables_fix.sql
`-- mysql_test_data_timezone.sql
mysql-5.0.84-linux-i686-glibc23/share/
|-- fill_help_tables.sql
|-- mysql_fix_privilege_tables.sql
|-- mysql_system_tables.sql
|-- mysql_system_tables_data.sql
|-- mysql_system_tables_fix.sql
`-- mysql_test_data_timezone.sql

Is it true (yes or no) that the difference between these 6 sql files from one
distribution to the next is what would be changed if the mysql_upgrade was
executed to upgrade an installation from 5.0.67 to 5.0.84 ?



According to this: http://forge.mysql.com/worklog/task.php?id=4991

There are two scripts: share/mysql_system_tables.sql and
share/mysql_system_tables_fix.sql
These two scripts comprise the content of: share/mysql_fix_privilege_tables.sql

The following is true about these three files:
cat share/mysql_system_tables.sql share/mysql_system_tables_fix.sql >
share/mysql_fix_privilege_tables.sql

To upgrade the schema of the mysql server, only the
share/mysql_fix_privilege_tables.sql sql script need be applied.

Is this correct?
Is it true for MySQL 5.0.x ?



References:
(1) http://bugs.mysql.com/bug.php?id=46638&thanks=4 "mysql_upgrade needs
--disable-log-bin option"
(2) http://forums.mysql.com/read.php?20,275140,275140#msg-275140 "MySQL Forums
:: General :: Best practice to disable log bin for mysql_upgrade"
(3) http://forums.mysql.com/read.php?11,274796,274796#msg-274796 "MySQL Forums
:: Install :: How to best disable log bin for mysql_upgrade"


-RG

--
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: Best practice to disable log bin for mysql_upgrade

am 26.08.2009 15:54:21 von Russell E Glaue

According to this page in the MySQL 5.0 Manual:
http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html
-
mysql_upgrade executes the following commands to check and repair tables and to
upgrade the system tables:

mysqlcheck --all-databases --check-upgrade --auto-repair
mysql < [fix_priv_tables]

....
[fix_priv_tables] represents a script generated interally by mysql_upgrade that
contains SQL statements to upgrade the tables in the mysql database.
-

Is there a way to generate this [fix_priv_tables] script outside of an actual
upgrade, so that I could apply it myself?

Is this [fix_priv_tables] script the same as the
share/mysql_fix_privilege_tables.sql script distributed with MySQL?

-RG



Russell E Glaue wrote:
> I am upgrading minor MySQL versions, 5.0.67 to 5.0.84
> I have a master-master replication setup, and want to upgrade both installations.
> The ideal procedure for upgrading mysql using the mysql_upgrade command is to
> have binary logging turned off during the execution of mysql_upgrade.
>
> My situation is I do not want to turn off binary logging for the entire server,
> I would like to turn off binary logging just for the session of the
> mysql_upgrade connection.
>
> mysql_upgrade does not support anything like --disable-log-bin (which seems is a
> feature that should be supported for this cli app) (1)
> So it seems my only option is to turn off binary logging for the entire server
> while I execute mysql_upgrade. Which also means blocking write access to the
> server while it runs so that statements I do want logged for replication do not
> occur while binary logging is off.
>
> Is there another simple way to achieve this? Or what is best practice that
> achieves the least amount of down time?
>
>
>
> Alternately, there are sql files in the share directory of each archive:
>
> mysql-5.0.67-linux-i686-glibc23/share/
> |-- fill_help_tables.sql
> |-- mysql_fix_privilege_tables.sql
> |-- mysql_system_tables.sql
> |-- mysql_system_tables_data.sql
> |-- mysql_system_tables_fix.sql
> `-- mysql_test_data_timezone.sql
> mysql-5.0.84-linux-i686-glibc23/share/
> |-- fill_help_tables.sql
> |-- mysql_fix_privilege_tables.sql
> |-- mysql_system_tables.sql
> |-- mysql_system_tables_data.sql
> |-- mysql_system_tables_fix.sql
> `-- mysql_test_data_timezone.sql
>
> Is it true (yes or no) that the difference between these 6 sql files from one
> distribution to the next is what would be changed if the mysql_upgrade was
> executed to upgrade an installation from 5.0.67 to 5.0.84 ?
>
>
>
> According to this: http://forge.mysql.com/worklog/task.php?id=4991
>
> There are two scripts: share/mysql_system_tables.sql and
> share/mysql_system_tables_fix.sql
> These two scripts comprise the content of: share/mysql_fix_privilege_tables.sql
>
> The following is true about these three files:
> cat share/mysql_system_tables.sql share/mysql_system_tables_fix.sql >
> share/mysql_fix_privilege_tables.sql
>
> To upgrade the schema of the mysql server, only the
> share/mysql_fix_privilege_tables.sql sql script need be applied.
>
> Is this correct?
> Is it true for MySQL 5.0.x ?
>
>
>
> References:
> (1) http://bugs.mysql.com/bug.php?id=46638&thanks=4 "mysql_upgrade needs
> --disable-log-bin option"
> (2) http://forums.mysql.com/read.php?20,275140,275140#msg-275140 "MySQL Forums
> :: General :: Best practice to disable log bin for mysql_upgrade"
> (3) http://forums.mysql.com/read.php?11,274796,274796#msg-274796 "MySQL Forums
> :: Install :: How to best disable log bin for mysql_upgrade"
>
>
> -RG
>

--
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: Best practice to disable log bin for mysql_upgrade

am 02.09.2009 20:23:09 von Russell E Glaue

Does anyone have any information regarding my question?

Is the 'share/mysql_fix_privilege_tables.sql' script distributed with MySQL the
same as the script (represented as '[fix_priv_tables]') generated interally by
the mysql_upgrade command that contains SQL statements to upgrade the tables in
the mysql database?

If it isn't the same, is there a way to generate this [fix_priv_tables] script
outside of an actual upgrade, so that I could apply it myself?

-RG


Russell E Glaue wrote:
> According to this page in the MySQL 5.0 Manual:
> http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html
> -
> mysql_upgrade executes the following commands to check and repair tables and to
> upgrade the system tables:
>
> mysqlcheck --all-databases --check-upgrade --auto-repair
> mysql < [fix_priv_tables]
>
> ...
> [fix_priv_tables] represents a script generated interally by mysql_upgrade that
> contains SQL statements to upgrade the tables in the mysql database.
> -
>
> Is there a way to generate this [fix_priv_tables] script outside of an actual
> upgrade, so that I could apply it myself?
>
> Is this [fix_priv_tables] script the same as the
> share/mysql_fix_privilege_tables.sql script distributed with MySQL?
>
> -RG
>
>
>
> Russell E Glaue wrote:
>> I am upgrading minor MySQL versions, 5.0.67 to 5.0.84
>> I have a master-master replication setup, and want to upgrade both installations.
>> The ideal procedure for upgrading mysql using the mysql_upgrade command is to
>> have binary logging turned off during the execution of mysql_upgrade.
>>
>> My situation is I do not want to turn off binary logging for the entire server,
>> I would like to turn off binary logging just for the session of the
>> mysql_upgrade connection.
>>
>> mysql_upgrade does not support anything like --disable-log-bin (which seems is a
>> feature that should be supported for this cli app) (1)
>> So it seems my only option is to turn off binary logging for the entire server
>> while I execute mysql_upgrade. Which also means blocking write access to the
>> server while it runs so that statements I do want logged for replication do not
>> occur while binary logging is off.
>>
>> Is there another simple way to achieve this? Or what is best practice that
>> achieves the least amount of down time?
>>
>>
>>
>> Alternately, there are sql files in the share directory of each archive:
>>
>> mysql-5.0.67-linux-i686-glibc23/share/
>> |-- fill_help_tables.sql
>> |-- mysql_fix_privilege_tables.sql
>> |-- mysql_system_tables.sql
>> |-- mysql_system_tables_data.sql
>> |-- mysql_system_tables_fix.sql
>> `-- mysql_test_data_timezone.sql
>> mysql-5.0.84-linux-i686-glibc23/share/
>> |-- fill_help_tables.sql
>> |-- mysql_fix_privilege_tables.sql
>> |-- mysql_system_tables.sql
>> |-- mysql_system_tables_data.sql
>> |-- mysql_system_tables_fix.sql
>> `-- mysql_test_data_timezone.sql
>>
>> Is it true (yes or no) that the difference between these 6 sql files from one
>> distribution to the next is what would be changed if the mysql_upgrade was
>> executed to upgrade an installation from 5.0.67 to 5.0.84 ?
>>
>>
>>
>> According to this: http://forge.mysql.com/worklog/task.php?id=4991
>>
>> There are two scripts: share/mysql_system_tables.sql and
>> share/mysql_system_tables_fix.sql
>> These two scripts comprise the content of: share/mysql_fix_privilege_tables.sql
>>
>> The following is true about these three files:
>> cat share/mysql_system_tables.sql share/mysql_system_tables_fix.sql >
>> share/mysql_fix_privilege_tables.sql
>>
>> To upgrade the schema of the mysql server, only the
>> share/mysql_fix_privilege_tables.sql sql script need be applied.
>>
>> Is this correct?
>> Is it true for MySQL 5.0.x ?
>>
>>
>>
>> References:
>> (1) http://bugs.mysql.com/bug.php?id=46638&thanks=4 "mysql_upgrade needs
>> --disable-log-bin option"
>> (2) http://forums.mysql.com/read.php?20,275140,275140#msg-275140 "MySQL Forums
>> :: General :: Best practice to disable log bin for mysql_upgrade"
>> (3) http://forums.mysql.com/read.php?11,274796,274796#msg-274796 "MySQL Forums
>> :: Install :: How to best disable log bin for mysql_upgrade"
>>
>>
>> -RG
>>
>

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