> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
> Sent: Friday, September 23, 2005 09:40
> To: Jeff
> Cc: mysql@stripped
> Subject: RE: MyISAM to InnoDB
>
>
> Sorry to butt in but I wanted to make sure you didn't do
> actually do what
> you proposed to do, yet. More responses interspersed...
>
Nope nothing yet, I don't rush things when I'm unsure... ;o)
> "Jeff" <jsmforum@stripped> wrote on 09/23/2005 08:32:57 AM:
>
> > > -----Original Message-----
> > > From: Devananda [mailto:karnah805@stripped]
> > > Sent: Thursday, September 22, 2005 19:03
> > > To: Jeff
> > > Cc: mysql@stripped
> > > Subject: Re: MyISAM to InnoDB
> > >
> > >
> > > 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.
> > >
> >
> > Thanks, that answer my question regarding SQL_LOG_BIN varialbe.
> >
> > > 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.
> >
> > Well like you say a "lot" of data is subjective. Our situation is
> > this; we currently have a DB01 up and running and in production.
> > We're moving to a more redundant data center and have purchased new
> > hardware to migrate the database to (new server will be DB03). The
> > speed of the database directly affects our profitability.
> This being
> > the case I've suggested to our developers that we take the
> opertunity
> > to migrate the tables involved in heavy write actions from
> MyISAM to
> > InnoDB on the new DB03 server before we put it into production.
> > Currently I have DB03 up and running (all MyISAM) at the new
> > datacenter and doing circular replication with DB01 over
> VPN. No apps
> > or users are currently writing or even reading from DB03 yet.
> >
> > Even if we migrate selected tables in DB03 to InnoDB I'd
> like to keep
> > the downstream (one way) replicated servers using MyISAM as most of
> > the activity on those is read only.
> >
> > As for the size of the tables in question, they are currently;
> >
> > 2gig Table1.MYD
> > 200M Table1.MYI
> > 14k Table1.frm
> >
> > 422k Table2.MYD
> > 114k Table2.MYI
> > 11k Table2.frm
> >
> > Our lead developer here has made the statement, "why not
> just convert
> > the entire db to InnoDB for the sake of simplicity and ease?"
>
> The tables in the mysql database cannot be InnoDB. InnoDB
> does not yet
> support fulltext searching. Other than those two major
> restrictions, a
> full InnoDb setup is possible.
>
Shawn,
What do you mean by "The tables in the mysql database cannot be InnoDB?"
You kinda lost me there...
> > As far as
> > I'm concerned, simplicity and ease would be to leave it as is but
> > we're looking to eek out as much speed in our transactions
> as possible
> > so simplicity, ease and performance don't always go together.
> >
> > If I understand what your suggesting above I should:
> >
> > 1) Stop all writes/read to the table (just replication at
> this point)
>
> STOP SLAVE should be all you need. You don't need to worry
> about reads
> affecting either your data or your structures.
> http://dev.mysql.com/doc/mysql/en/stop-slave.html
>
> >
> > 2) Issue a "show create table" statement for Table1 and
> Table2, save
> > these results and modify the engine type to InnoDB for later use.
> >
> > 3) Dump all data from Table1 and Table2 to text files.
>
> You are doing this more as a backup measure than as a step in the
> conversion process.
Yes, you are correct but it was suggesed by Devananda that this was a
"safer" approach.
I'm in no rush so safer is better.
>
> >
> > 4) Setup the db for InnoDB use (add variables to my.cnf
> file as needed
> > to support InnoDB).
>
> This is a great time for you to decide if you want to use
> file-per-tablespace or the single tablespace model of InnoDB.
> There are
> advantages to each. Pick which one works best for your situation.
Actually it's not a choice right now as the MySQL version running on
these servers is 4.0.16 which I believe doesn't support single
tablespace. I do plan on moving to that however after upgrades as it
seems a more easily managed InnoDB setup. I suspect backups of a InnoDB
db with a single tablespace model would be easier as well?
> >
> > 5) Restart the database (make sure replication doesn't start)
> >
> > 6) Issue a SQL_LOG_BIN=0
>
> Good, this means that the binlog won't get the next few commands.
>
> >
> > 7) Drop the old Table1 and Table2
> >
> > 8) Use the previousely created "create table" queries to rebuild
> > Table1 and Table2 in InnoDB
> >
> > 9) Re-import the data from the text files into the new Table1 and
> > Table2
>
> Nope. That's overkill. All you need to do is to issue ALTER TABLE
> statements where you change ENGINE=myISAM to ENGINE=InnoDB. Looks
> something like this:
>
> ALTER TABLE mytable1 ENGINE=InnoDB;
>
> MySQL will take care of copying over the column definitions
> and moving the
> data from one set of files (the MyISAM files) into the
> appropriate InnoDB
> structures.
> http://dev.mysql.com/doc/mysql/en/alter-table.html
> http://dev.mysql.com/doc/mysql/en/create-table.html
>
> >
> > 10) Issue a SQL_LOG_BIN=1
> >
> > 11) Restart replication.
> >
> > 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
> > >
> >
> > I'd be very interested to discuss that. Even if I don't use
> it in this
> > particular situation, knowledge never hurts! ;o)
> >
> > Thanks!
> >
> > jeff
> >
> >
>
> Yep, that sounds like it will work.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
