> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 23, 2005 09:40 > To: Jeff > Cc: mysql@lists.mysql.com > 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" <[EMAIL PROTECTED]> wrote on 09/23/2005 08:32:57 AM: > > > > -----Original Message----- > > > From: Devananda [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, September 22, 2005 19:03 > > > To: Jeff > > > Cc: mysql@lists.mysql.com > > > Subject: Re: MyISAM to InnoDB > > > > > > > > > Jeff wrote: > > > >>-----Original Message----- > > > >>From: Devananda [mailto:[EMAIL PROTECTED] > > > >>Sent: Thursday, September 22, 2005 16:14 > > > >>To: Jeff > > > >>Cc: mysql@lists.mysql.com > > > >>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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
