Forum archive
TKLBAM and MySQL
I have a MySQL server which backs up daily via TKLBAM. I am using a lot of InnoDB tables, but had not set the "innodb_file_per_table" value to true until well after my ibdata1 file surpassed 10GB.
My question is two-fold:
1. Would you consider setting innodb_file_per_table=true as a default for future distributions? It makes more sense to have it that way for a couple reasons. Especially when it comes to backup and restore. My 10GB ibdata1 file changes daily which makes it necessary to backup each day during incremental backups when it would be easier to backup/restore separate files so you can fix single tables and not have an all-or-nothing situation on your hands.
2. When I do a restore of the databases only with TKLBAM does it recreate each database like it is being imported and not just place the files in the appropriate dorectories? I want to build a new database server with better hardware and SSD drives so I am wondering if I need to export/sync from the current server and import in to the new server so that the ibdata1 file won't get too bloated. Apparently, the ibdata1 file remains the same size even after you change the setting to use individual files and if that thing goes belly up you lose all the databases in the system (so I have read/experienced).
Thanks,
Rick
Replies (5)
AFAIK TKLBAM does a MySQL dump
Reply 1Awesome!
Reply 2MySQL Dump is Perfect!!
Reply 3Actually, that is what I was hoping it did. I can make the new server and then just use the --skip-packages and --skip-files options of tklbam-restore to restore all of my databases.
I plan on doing it within the next few days, so I will let you know how it works out.
Awesome!
Reply 4Worked Like a Charm (So Far)
Reply 5So, this project came about for a few reasons.
1: I wanted to utilize some SSD drives I had lying around in the database server to see if they increased the performance at all.
2: my ibdata1 file was extremely bloated due to the fact that I had not setup InnoDB to create a separate file per table, like MyISAM does by default.
3: I wanted to upgrade my Turnkey MySQL Appliance to the new version
4: I was concerned that the sheer size of the ibdata1 file was a disaster waiting to happen if it got corrupted somehow.
In short, I built a new server with new hardware and the SSD drives, installed the new version of the MySQL appliance, and got everything up and running prior to migrating. I mapped the mysql data directory to the SSD drive and applied these settings to the my.cnf file :
innodb_file_per_table=1
innodb_open_files=10000
innodb_buffer_pool_size=12G
innodb_buffer_pool_instances=2
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=300
innodb_log_file_size=128M
open_files_limit=10000
Some of these settings are high for future expansion. Apparently, each innodb table opens a separate file and I have quite a few tables, but also 16GB of RAM that I plan to expand to 32GB eventually.
Then, I just did a restore of only the database backups (--skip-files --skip-packages). Everything seems to be in the right place and my ibdata1 file is no longer HUGE! I went from a 10.7GB ibdata1 file to a 18.9MB ibdata1.
However, I recently wrote a suggestion for someone else to minimise the size of MySQL incremental backups using TKLBAM hooks. Have a look here if you're interested. If you wanted to create a TKLBAM hook to make better utilisation of InnoDB files then I'm sure that could be done instead. If you do, please share your work.