"Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 09:57:06 AM:
> > -----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... Yes, I meant exactly that. Within each MySQL server is a "special" database called `mysql`. That is the database that contains the tables of all of the user login and permission information for the server (and several other important bits of system-wide metadata). None of the tables in that database can be converted to InnoDB. That would be a "bad thing". The tables of every OTHER database on the server (including yours) are eligible for InnoDB conversion so long as you do not want to use fulltext searching. If you need a FT index, you have to keep that table as MyISAM for now (they are working on enabling FT indexes in InnoDB but there is no release date yet) > > > > 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. Yes, it is safer because you have a backup. Should the auto-conversion fail, you have an extra method of recovery. If you decide to move from 4.0 to 4.1 or 5.x, this is definitely the method you should use. "Going to text" is the most portable means of moving data between versions and will avoid any version-to-version incompatibilities. Remember to tell mysqldump how big the max_allowed_packet setting will be on the destination server. If you do not, it can (and will) create extended insert statement too big to be processed as you attempt to restore from the dump file. Trust me, I know :-) I still believe that because your are only performing a table type conversion, this step acts more as a safety net than it plays a role as part of the conversion process. > > > > > > > > > 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? I realize I may have said it backwards. The newer option (4.1+) is to have InnoDB create a separate tablespace per each table ( http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html). I think you understood what I meant, though, based on your comment. > > > > > > > 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 (I meant to apply the next comment to all 3 steps above (7, 8, and 9), not just to the last) > > > > 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 > > > Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
