asked on 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
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
#*** 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
MySQL Server
Last Comment
Ted Palmer
ASKER
I didn't mean to claim to be "Advanced on this subject" on this subject. That is a default I forgot to change before submitting the question. I am Intermediate at best on this subject.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
NovaDenizen:
I'm stuck with some commitments that I have to complete today, but I will experiment with this technique late this afternoon or this evening. Thank you for your help and I'll respond again as soon as I know something.
TedPalmer
www.tedpalmer.com
I'm stuck with some commitments that I have to complete today, but I will experiment with this technique late this afternoon or this evening. Thank you for your help and I'll respond again as soon as I know something.
TedPalmer
www.tedpalmer.com
ASKER
NovaDenizen:
I basicly followed your suggested solution with some modification in the implementation. I didn't say explicity but the Operating Systems in my target environment are all some version of Windows. Your examples were for Unix/Linux. I downloaded from the MySQL website their MySQL_GUI_Tools one of which is MySQL Administrator. It made doing Backup and Restore easy and intuitive. A lot easier than doing mysql_dump.
1.) I stopped the MySQL servers on both my development machine and my Windows Server 2003 machine that simulates the production environment.
2.) On the Windows Server 2003 machine I modified the my,ini file according to the code snippet I perviously entered.
3.) On the Windows Server 2003 I renamed both the data file and log_files and I restarted the MySQL server. Since the files the server was expexting according to the content of the my.ini file were not there, the server created them using the parameters specified in the my.ini file for size. Since the parameters called for some pretty big files the creation process to a lot longer than I expected. So if somebody reads this and does what I did, don't get paniced if it takes a few minutes to build those large files. I made the log files 100M instead of 200M.
4.) On my Windows XP workstation development PC I used MySQL Administrator to do a Backup of my application database to a SQL file.
5.) On the Windows Server 2003 machine I did a Restore using MySQL Administrator, which worked fast and easy and without any complaints (error messages) or problems and the SQL file created by the backup process in step 4 above.
6.) I verified the results using TOAD for MySQL. TOAD (Tool for Oracle Application Developers) really Rocks..!! It is awesome and it is a free download from http://www.toadsoft.com/toadmysql/Overview.htm
I also installed my client software on my Windows Server 2003 machine. It ran just fine using the DB on the server.
Now I can delete the data I don't want on my Windows Server 2003 machine and keep the data that I do want. Once I get the data the way I want it for installation to production, I can Back it up using MySQL Administrator. Then when I install MySQL Server, I can size the data and log files whatever size I think is best for that installation; load the initial data using MySQL Administrator Restore and the SQL file I created doing the Backup. Life is Good..!!
THANK YOU FOR YOUR HELP,
Ted Palmer
www.tedpalmer.com
I basicly followed your suggested solution with some modification in the implementation. I didn't say explicity but the Operating Systems in my target environment are all some version of Windows. Your examples were for Unix/Linux. I downloaded from the MySQL website their MySQL_GUI_Tools one of which is MySQL Administrator. It made doing Backup and Restore easy and intuitive. A lot easier than doing mysql_dump.
1.) I stopped the MySQL servers on both my development machine and my Windows Server 2003 machine that simulates the production environment.
2.) On the Windows Server 2003 machine I modified the my,ini file according to the code snippet I perviously entered.
3.) On the Windows Server 2003 I renamed both the data file and log_files and I restarted the MySQL server. Since the files the server was expexting according to the content of the my.ini file were not there, the server created them using the parameters specified in the my.ini file for size. Since the parameters called for some pretty big files the creation process to a lot longer than I expected. So if somebody reads this and does what I did, don't get paniced if it takes a few minutes to build those large files. I made the log files 100M instead of 200M.
4.) On my Windows XP workstation development PC I used MySQL Administrator to do a Backup of my application database to a SQL file.
5.) On the Windows Server 2003 machine I did a Restore using MySQL Administrator, which worked fast and easy and without any complaints (error messages) or problems and the SQL file created by the backup process in step 4 above.
6.) I verified the results using TOAD for MySQL. TOAD (Tool for Oracle Application Developers) really Rocks..!! It is awesome and it is a free download from http://www.toadsoft.com/toadmysql/Overview.htm
I also installed my client software on my Windows Server 2003 machine. It ran just fine using the DB on the server.
Now I can delete the data I don't want on my Windows Server 2003 machine and keep the data that I do want. Once I get the data the way I want it for installation to production, I can Back it up using MySQL Administrator. Then when I install MySQL Server, I can size the data and log files whatever size I think is best for that installation; load the initial data using MySQL Administrator Restore and the SQL file I created doing the Backup. Life is Good..!!
THANK YOU FOR YOUR HELP,
Ted Palmer
www.tedpalmer.com
ASKER
Oh.!! I forgot to mention; I can also keep my test data that I am accustom to working with on my development machine. That way, when I develop reports, I'll have reasonable data to report on.