Re: MyISAM to InnoDB

[Devananda]

Jeff wrote:
>>-----Original Message-----
>>From: Devananda [mailto:karnah805@stripped] 
>>Sent: Thursday, September 22, 2005 16:14
>>To: Jeff
>>Cc: mysql@stripped
>>Subject: Re: MyISAM to InnoDB
>>
>>
>>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?
>>
>>There's a much easier way - issue the statement "SET 
>>SQL_LOG_BIN = 0;" 
>>before issuing any ALTER TABLE statements. This will cause all 
>>statements for the duration of that session to not be written to the 
>>binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html 
>>for more 
>>information.
>>
> 
> 
> First off, thanks for the help to you and Bruce both!
> 
You're quite welcome, Jeff :)

> When you say here, "for the duration of that session" does that mean
> that only queries I issue with my connection skip the binlog?  Or do all
> queries during that time skip the binlog.  In other words, when I SET
> SQL_LOG_BIN = 0; should I first stop all applications writing to the
> database to prevent missing data in the slaves?
> 


It only affects that connection. Bruce wrote a response at about the 
same time I did; his covers this topic as well. SQL_LOG_BIN is a session 
variable, meaning that it only affects the current session (connection). 
So, any applications running at the same time will not be affected by a 
change to this variable, and if you close your client and reconnect, you 
will have to set the variable again. As Bruce suggested, it's best to 
set it only when you need it and unset it immediately afterwards (as a 
precaution against operator error, not because it affects the server).

I do want to point out that while the commands you issue (after setting 
SQL_LOG_BIN to 0) will not be written to the binlog (thus will not run 
on any slave reading from this server), they may affect other running 
processes on the server. If, for example, you run an ALTER TABLE on a 
table currently in MyISAM format, the table will be locked and all 
processes running on that server that read from / write to that table 
will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't 
affect this in any way - it _only_ affects whether statements from that 
specific session are recorded in the binary log.

Side question - you've stated that you are planning to migrate to 
InnoDB, but you haven't said anything to the list about how much data 
you have. Just be aware that it can take a lot of time and disk space 
for MySQL to transfer all your data from one format to the other (of 
course depending on how much data you have) and if anything goes wrong 
during that time, the results will probably not be what you expect, or 
want. I would advise you to at least investigate an alternate approach 
if you have a lot of data - take the server you are going to migrate out 
of the 'cluster' and make sure it is not processing any data / no 
clients are connecting to it; dump all your data to text files, 
preferably separating your data definition statements (ie CREATE TABLE 
statements) from your actual data; modify the CREATE statements to 
specify the InnoDB engine; lastly load all the data from the text files 
into MySQL, and bring this server back into the 'cluster'.

If you don't have a _lot_ of data, then it may not be worth all that 
work. Of course, "a lot" is subjective; I'd say, based purely on my own 
experiences with this, that if you are going to migrate 1G of data, you 
will probably be better off exporting / alter the text files / 
importing. If you have 10's or 100's of G of data, I would strongly 
recommend that you do it this way. And regardless of how much data you 
have, it is, IMHO, safer to export/import. If you're interested, I would 
be happy to talk more about a method to automate this process over many 
tables / lots of data.


Best Regards,
Devananda vdv