List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:December 29 2005 5:14pm
Subject:Re: ibdata1 File
View as plain text  
Hello.

>there, in fact, anything important in that ibdata1 file, or can I just
>shut down mySQL, move that file somewhere else, and then re-start mySQL?

Yes, this file contains important information.

>Is this possible, or am I stuck having my database server off-line for
>hours while the backup/restore procedure happens?

You can setup a second server (or just run another instance of MySQL
on the same machine), copy your databases there using mysqldump
or replication, and switch to second copy. Its data could be a bit
behind the main server, but you can quickly put it up to date. The
general way, which I see is, to perform the dump with --master-data, get
log file name and position in it from the dump file. Import the dump 
to the second instance. FLUSH TABLES WITH READ LOCK on the main server, 
get new updates which have appeared on the master, with mysqlbinlog utility
(use --start-position=N option). Import them to the second copy and switch
your applications to it. With replication this should be quite the same
(FLUSH TABLES WITH READ LOCK on the master, get information from SHOW
MASTER STATUS, use SELECT MASTER_POS_WAIT to put slave up to date).
I agree, that this approach is not ideal, however it makes the time
when you master is off-line to several minutes.


"Gustafson, Tim" <tjg@stripped> wrote:
>Hello!
>
>When I first set up my mySQL 4.1 server, I did not have the
>"innodb_file_per_table" option set.  I have since set this option, and
>re-created all my tables so that they are now in individual innoDB
>files.  However, the original, 44GB ibdata1 file still exists and I
>can't find any good way of shrinking it down to a manageable size.  I
>know that the file is mostly filled with junk now, but I'm also fairly
>sure that there is -some- important data store in there...so the
>question is this:
>
>How can I reduce the size of the ibdata1 file down to a manageable size,
>without breaking my mySQL server, and without backing up every database
>on my system (a 50GB proposition), and then deleting all databases and
>the ibdata1 file, and then re-creating everything from the backup?  Is
>there, in fact, anything important in that ibdata1 file, or can I just
>shut down mySQL, move that file somewhere else, and then re-start mySQL?
>
>Is this possible, or am I stuck having my database server off-line for
>hours while the backup/restore procedure happens?
>
>Thanks for any help you can give!
>
>


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
ibdata1 FileTim Gustafson28 Dec
  • Re: ibdata1 FileGleb Paharenko30 Dec