How do I Resize a ibdata1 data file and ib_logfile0/ib_logfile1 log files using MySQL, 5.0.45, INNODB

EE DBA Experts on MySQL:

I am a software developer and I have an application that I have written in VB.NET 2005 that uses MySQL version 5.0.45 in a Windows Development Environment that I am getting ready to deploy to a production environment. In my development environment I have both my application client and RDBMS server on the same machine. In production my RDBMS will be on a Network Server and my client executables will be on each workstation unless I can figure out a way to install the client software in a share on the Network Server and map a drive letter or a short cut on the workstations so that the clients are executing their program off a Network Server. This will make upgrades easier by not requiring that I visit each workstation to install up grades every time I have one. But I digress.

My development database has the structure that I am ready to go live with even though I am certain that I will have to do a few alter tables to implement changes my user community requires of me. My question is this. I have a lot of test data that I need to remove so my users can have a fresh start. But some of the tables have codes with their descriptions that my application needs when it goes live. If this were a Data Warehouse application I believe those would be called Dimension Tables not Fact Tables which have transaction data in them. Can I truncate the data from the Fact tables leaving the Dimension tables as is then resize the whole ibdata1 data file and the two ib_logfile0 and ib_logfile1 log files.

I want to resize them so that they don't grow a new extent very often. I have decided 600 MB would be a good size for ibdata1 and maybe 200 MB each for the two log files. I am thinking that I have to do the truncates I mentioned then export all the data to a dump file. Delete the ibdata1 and two log files. Restart the MySQL server without the files there which will cause the server to create those files using the parameters it finds in the my.ini file. This might be an excerpt from the my.ini file.

The log file sizes are small compared to the data file size because I don't care about being a perfect DBA where all transactions are logged and a restore includes a copy of a full backup followed by using the log files to add whatever transactions were missed.  I know that MySQL complains seriously whenever I fail to copy the log files that go with the data file. They have to stay together or else the MySQL server won't restart. As it is the MySQL server scolds me in the hostname.err file, but it starts and runs and that is what I care about most. I can do a restore from a full backup taken each day and the users will just have to reenter their data. They are OK with that. But . . . . Maybe I could use the commnity version of Zamanda. Would that take care of restores up to the moment the database crashed for whatever reason?

I am looking for a way to resize these fiiles without doing a dump. But if I have to do that I can. It would be my first time though. Any suggestions about that?

Thank you,

Ted Palmer
www.tedpalmer.com
Start Free Trial

1:
2:
3:
4:
5:
6:
7:

#*** INNODB Specific options *** 2008-07-22
innodb_data_home_dir="C:/MySQL5045A InnoDB Datafiles/"
innodb_data_file_path=ibdata1:600M:autoextend
innodb_autoextend_increment=600M
innodb_log_group_home_dir="C:/MySQL5045A InnoDB Datafiles/"
innodb_log_file_size=200M
max_binlog_size=10M