On Sep 22, 2005, at 11:46 AM, Jeff wrote:
>
> True, is there a way to tell a slave to not replicate certain queries
> like alter table or would I need to get creative and stop replication
> and all writes to the main database, then issue the alter table
> statement, then restart replication with a set global
> slave_sql_skip_counter=1 so that it skips the alter statemtent?
>
>
On a single connection use:
SET SQL_LOG_BIN = 0
It's a connection variable, the default is 1, which means queries
altering the data get written to the bin log... changing this to 0
means data altering commands from this specific connection do not get
written to the binary log... It's best not to leave a connection
lying around with this setting because it's the sort of thing you
forget about and later end up with data inconsistencies. However
short term use by turning it off, doing your thing, and turning it on
again usually works without trouble... eg:
SET SQL_LOG_BIN = 0;
ALTER TABLE ....some stuff here....;
SET SQL_LOG_BIN = 1;
Not all users have permission to issue such a command.
> If I understand what you're saying here, some MySQL front end gui
> software will add onto any "Alter table" statement you submit a
> statement specifying the type of table like myisam automatically.
> So if
> you used that gui and tried to issue an alter statement to say add an
> index to a InnoDB table it would add on a table type = MyISAM and
> cause
> havoc?
>
> Normally I don't rely on gui tools to do my serious quiries like
> altering tables or adding indexes etc. I'll do them logging directly
> into mysql server on the linux box itself. In this case there
> shouldn't
> be a problem correct?
>
>
Some GUI's take simple steps and write them out into their full long
SQL format... whereas adding a table's engine or type to an alter
table is optional in MySQL, officially it is suppose to be there...
so some GUI's put it there... typically if you haven't told it to
change the table type it will just use whatever table type it is
now... but the end result in the binary log will still go to the
other server and potentially change something there.
There shouldn't be a problem using the mysql command line client...
but I'm going to emphasize "shouldn't" here... when you have two
different table types on master and slave you need to be **really**
sure you don't mess that up.
Best Regards, Bruce
