Fw: Merging / Moving InnoDB Databases
(forwarded to the list) - Forwarded by Shawn Green/Unimin on 02/23/2005 09:08 AM - [EMAIL PROTECTED] wrote on 02/22/2005 04:59:39 PM: > > Regardless of the Mysql angle - a windows editor that allows you to > view large text files is an absolute necessity. Notepad tries to > load the entire file into memory before opening it which is a > problem with large text files. > > There used to be a great text editor - "Multi-Edit 8.0" made by > "American Cybernetics" that could easily handle large text files. It > would open the first block right away and then read ahead as you > paged down. It was very clever software with lots of features. We > couldn't live without it. Looking at the Web all I see are old > references. There's a "multieditsoftware.com" but their product > appears to be more of a programming editor. > > Maybe you can still find it somewhere. Another product is "SPF/PC" > by commandtechnology.com which is a windows version of the venerable > Mainframe SPF editor. At the time it was more limited than Multi- > Edit but things may have changed. > > HTH. > > Udi > > This message and any files or text attached to it are intended only > for the recipients named above, and contain information that may be > confidential or privileged. If you are not an intended recipient, > you must not read, copy, use, or disclose this communication. > Please also notify the sender by replying to this message, and then > delete all copies of it from your system. Thank you. > > > [EMAIL PROTECTED] > 02/22/2005 12:55 PM > > > To: phpninja <[EMAIL PROTECTED]> > cc:[EMAIL PROTECTED], mysql@lists.mysql.com > Subject:RE: Merging / Moving InnoDB Databases > > > > > phpninja <[EMAIL PROTECTED]> wrote on 02/22/2005 03:37:37 PM: > > > I have a small question. Whenever I try to dump a sizeable Innodb > > table, lets say 33,000,000 records I find that mysqldump cannot handle > > that kind of load and usually freezes. I am not sure if it is my > > system, as its only a pentium 4 1.7ghz celeron running on windows > > server, but with my mysql tables nice and optimized mysql still flys > > in this heavy load envirornment. I decided testing it with smaller > > tables, maybe 2,000,000 records and it produced the .sql file > > successfully after giving it some time. Unfortunately when I loaded > > that .sql file I was getting all kinds of SQL errors about the syntax > > in that huge .sql file. I was kind of expecting a clean load in with > > the .sql file without any errors. At this point i decided to open the > > .sql file and attempt to see that lines it was complaining about with > > the error. Unfortunatlely, windows text editors do not handle > > displaying 2,000,000 of anything in 1 scrollable window, so i could > > not find the line errors. What do you suggest is the best way to > > backup an InnoDB table of this size? I have been thinking about an > > upgrade for quite a while now (moving from mysql 4.0 branch to 4.1) > > but I need a good solution to backup the data, and I am not sure if I > > rely on mysqldump after these tests. I've seen that Tool thats > > available at for purchase at innodb.com and I am considering it, but > > is that the only way to backup the data and have it all in tact 100%? > > I read that mysqlhotcopy does not cut it for InnoDB, and I have not > > tested it much on very big myISAM tables. > > > > -phpninja > > > > Read about and learn how to use the following mysqldump options: > > --quick > --max_allowed_packet= > > http://dev.mysql.com/doc/mysql/en/mysqldump.html > > "Quick" will avoid putting the entire dump file into memory before it gets > written to disk (straight write = faster finish) and max_allowed_packet > will keep the extended INSERT statements to a reasonable length. Ask your > destination server what that length should be (SHOW VARIABLES LIKE > 'max%';) then tell mysqldump to not make INSERT packets any larger than > that. > > There may be other options that could help you go faster but those are the > two that pop to mind based on your problem descriptions. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > -Original Message- > > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, February 22, 2005 11:59 AM > > To: mysql@lists.mysql.com > > Subject: Re: Merging / Moving InnoDB Databases > >
Re: Merging / Moving InnoDB Databases
Hi Heikki, Thanks for the reply. If you were charged with moving an InnoDB database with 40,000,000 records (based on an old version of one of our databases, has since grown) within a standard maintenance slot (say up to 4 hrs) what would you do? Are we looking at requiring a separate hardware target? Last time we attempted an import it took 48hrs on our spare production servers (dual xeons with gigs of ram otherwise idle). Ordinarily I would be looking to use InnoDB Hot backup to copy the binary files across, you say this is not possible when the target host already runs InnoDB? Thanks, James Heikki Tuuri wrote: James, unfortunately, you cannot move InnoDB tables in that way, like you would be able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files over to the other database installation. In the future, we may add a feature that allows one to copy 'clean' .ibd files across installations. But presently, you must dump the tables and import them to the other installation. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: "James Green" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 21, 2005 1:16 PM Subject: Merging / Moving InnoDB Databases Hi, Server A - Multiple InnoDB databases Server B - Replication of Server A Server C - Other InnoDB databases I need to be able to move Server C's databases onto Server A and continue to replicate (with new databases) to Server B. Can I: 1. Take down Server C, use ibbackup to back up the innodb data files and frm data 2. Taken down Servers A & B 3. Put on Servers A & B the data files from Server C, but under different InnoDB names (ibdata3 for example) 4. Start Servers A & B and watch all the databases fly happily? Is this the correct procedure? Thanks, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Merging / Moving InnoDB Databases
phpninja <[EMAIL PROTECTED]> wrote on 02/22/2005 03:37:37 PM: > I have a small question. Whenever I try to dump a sizeable Innodb > table, lets say 33,000,000 records I find that mysqldump cannot handle > that kind of load and usually freezes. I am not sure if it is my > system, as its only a pentium 4 1.7ghz celeron running on windows > server, but with my mysql tables nice and optimized mysql still flys > in this heavy load envirornment. I decided testing it with smaller > tables, maybe 2,000,000 records and it produced the .sql file > successfully after giving it some time. Unfortunately when I loaded > that .sql file I was getting all kinds of SQL errors about the syntax > in that huge .sql file. I was kind of expecting a clean load in with > the .sql file without any errors. At this point i decided to open the > .sql file and attempt to see that lines it was complaining about with > the error. Unfortunatlely, windows text editors do not handle > displaying 2,000,000 of anything in 1 scrollable window, so i could > not find the line errors. What do you suggest is the best way to > backup an InnoDB table of this size? I have been thinking about an > upgrade for quite a while now (moving from mysql 4.0 branch to 4.1) > but I need a good solution to backup the data, and I am not sure if I > rely on mysqldump after these tests. I've seen that Tool thats > available at for purchase at innodb.com and I am considering it, but > is that the only way to backup the data and have it all in tact 100%? > I read that mysqlhotcopy does not cut it for InnoDB, and I have not > tested it much on very big myISAM tables. > > -phpninja > Read about and learn how to use the following mysqldump options: --quick --max_allowed_packet= http://dev.mysql.com/doc/mysql/en/mysqldump.html "Quick" will avoid putting the entire dump file into memory before it gets written to disk (straight write = faster finish) and max_allowed_packet will keep the extended INSERT statements to a reasonable length. Ask your destination server what that length should be (SHOW VARIABLES LIKE 'max%';) then tell mysqldump to not make INSERT packets any larger than that. There may be other options that could help you go faster but those are the two that pop to mind based on your problem descriptions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 22, 2005 11:59 AM > To: mysql@lists.mysql.com > Subject: Re: Merging / Moving InnoDB Databases > > James, > > unfortunately, you cannot move InnoDB tables in that way, like you would be > able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files > over to the other database installation. > > In the future, we may add a feature that allows one to copy 'clean' .ibd > files across installations. > > But presently, you must dump the tables and import them to the other > installation. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > http://www.innodb.com/order.php > > Order MySQL Network from http://www.mysql.com/network/ > > > - Original Message - > From: "James Green" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Monday, February 21, 2005 1:16 PM > Subject: Merging / Moving InnoDB Databases > > > > Hi, > > > > Server A - Multiple InnoDB databases > > Server B - Replication of Server A > > Server C - Other InnoDB databases > > > > I need to be able to move Server C's databases onto Server A and > > continue to replicate (with new databases) to Server B. > > > > Can I: > > > > 1. Take down Server C, use ibbackup to back up the innodb data files and > > frm data > > 2. Taken down Servers A & B > > 3. Put on Servers A & B the data files from Server C, but under > > different InnoDB names (ibdata3 for example) > > 4. Start Servers A & B and watch all the databases fly happily? > > > > Is this the correct procedure? > > > > Thanks, > > > > -- > > James Green > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
RE: Merging / Moving InnoDB Databases
I have a small question. Whenever I try to dump a sizeable Innodb table, lets say 33,000,000 records I find that mysqldump cannot handle that kind of load and usually freezes. I am not sure if it is my system, as its only a pentium 4 1.7ghz celeron running on windows server, but with my mysql tables nice and optimized mysql still flys in this heavy load envirornment. I decided testing it with smaller tables, maybe 2,000,000 records and it produced the .sql file successfully after giving it some time. Unfortunately when I loaded that .sql file I was getting all kinds of SQL errors about the syntax in that huge .sql file. I was kind of expecting a clean load in with the .sql file without any errors. At this point i decided to open the .sql file and attempt to see that lines it was complaining about with the error. Unfortunatlely, windows text editors do not handle displaying 2,000,000 of anything in 1 scrollable window, so i could not find the line errors. What do you suggest is the best way to backup an InnoDB table of this size? I have been thinking about an upgrade for quite a while now (moving from mysql 4.0 branch to 4.1) but I need a good solution to backup the data, and I am not sure if I rely on mysqldump after these tests. I've seen that Tool thats available at for purchase at innodb.com and I am considering it, but is that the only way to backup the data and have it all in tact 100%? I read that mysqlhotcopy does not cut it for InnoDB, and I have not tested it much on very big myISAM tables. -phpninja -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 11:59 AM To: mysql@lists.mysql.com Subject: Re: Merging / Moving InnoDB Databases James, unfortunately, you cannot move InnoDB tables in that way, like you would be able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files over to the other database installation. In the future, we may add a feature that allows one to copy 'clean' .ibd files across installations. But presently, you must dump the tables and import them to the other installation. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: "James Green" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 21, 2005 1:16 PM Subject: Merging / Moving InnoDB Databases > Hi, > > Server A - Multiple InnoDB databases > Server B - Replication of Server A > Server C - Other InnoDB databases > > I need to be able to move Server C's databases onto Server A and > continue to replicate (with new databases) to Server B. > > Can I: > > 1. Take down Server C, use ibbackup to back up the innodb data files and > frm data > 2. Taken down Servers A & B > 3. Put on Servers A & B the data files from Server C, but under > different InnoDB names (ibdata3 for example) > 4. Start Servers A & B and watch all the databases fly happily? > > Is this the correct procedure? > > Thanks, > > -- > James Green > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging / Moving InnoDB Databases
James, unfortunately, you cannot move InnoDB tables in that way, like you would be able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files over to the other database installation. In the future, we may add a feature that allows one to copy 'clean' .ibd files across installations. But presently, you must dump the tables and import them to the other installation. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: "James Green" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 21, 2005 1:16 PM Subject: Merging / Moving InnoDB Databases Hi, Server A - Multiple InnoDB databases Server B - Replication of Server A Server C - Other InnoDB databases I need to be able to move Server C's databases onto Server A and continue to replicate (with new databases) to Server B. Can I: 1. Take down Server C, use ibbackup to back up the innodb data files and frm data 2. Taken down Servers A & B 3. Put on Servers A & B the data files from Server C, but under different InnoDB names (ibdata3 for example) 4. Start Servers A & B and watch all the databases fly happily? Is this the correct procedure? Thanks, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merging / Moving InnoDB Databases
Hi, Server A - Multiple InnoDB databases Server B - Replication of Server A Server C - Other InnoDB databases I need to be able to move Server C's databases onto Server A and continue to replicate (with new databases) to Server B. Can I: 1. Take down Server C, use ibbackup to back up the innodb data files and frm data 2. Taken down Servers A & B 3. Put on Servers A & B the data files from Server C, but under different InnoDB names (ibdata3 for example) 4. Start Servers A & B and watch all the databases fly happily? Is this the correct procedure? Thanks, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
