List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 23 2005 2:08pm
Subject:Fw: Merging / Moving InnoDB Databases
View as plain text  
(forwarded to the list)
----- Forwarded by Shawn Green/Unimin on 02/23/2005 09:08 AM -----

Udi.S.Karni@stripped 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. 
> 

> 
> SGreen@stripped 
> 02/22/2005 12:55 PM 
> 
> 
>         To:        phpninja <phpninja@stripped> 
>         cc:        Heikki.Tuuri@stripped, mysql@stripped 
>         Subject:        RE: Merging / Moving InnoDB Databases
> 
> 
> 
> 
> phpninja <phpninja@stripped> 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:Heikki.Tuuri@stripped] 
> > Sent: Tuesday, February 22, 2005 11:59 AM
> > To: mysql@stripped
> > 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" <james.green@stripped>
> > 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/mysql?unsub=1
> > > 
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: 
> > http://lists.mysql.com/mysql?unsub=1
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > 
> 

Thread
Fw: Merging / Moving InnoDB DatabasesSGreen23 Feb