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]
