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] >
