I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have already run OPTIMIZE TABLE on them which saved some space. Index data length is only a few MBs. I am looking for any other way to reduce the size of my growing tables and if possible the whole database. I am only keeping binary logs for 3 days for recovery. I will appreciate a solution that will cause no downtime or minimal downtime and risk. I also have a local replicated data of the same.
Mysql – Reducing the size of thesql files
MySQLmysql-5.7
Related Solutions
Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1
This file normally houses many classes of information (when innodb_file_per_table is 0)
- Table Data
- Table Indexes
- MVCC (Multiversioning Concurrency Control) Data
- Rollbacks Segments
- Undo Tablespace
- Table Metadata
- See Pictorial Representation
Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.
Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:
- Makes the table's data and indexes contiguous inside ibdata1
- It makes ibdata1 grow because the contiguous data is appended to ibdata1
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. To shrink ibdata1 once and for all you must do the following
Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) (More details here)
Step 02) Drop all databases (except mysql
, performance_schema
, and information_schema
)
Step 03) Shutdown mysql
Step 04) Add the following lines to /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
Step 06) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
Step 07) Reload SQLData.sql into mysql
ibdata1 will grow but only contain table metadata
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table
- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable
and the file /var/lib/mysql/mydb/mytable.ibd
will actually shrink.
I have done this many times in my career as a MySQL DBA
In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.
Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!
If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
Last time, I answered your question by suggesting you perform a well-scripted parallel dump of all tables.
Since the tables are lopsided in size, parallel table dumps are not much help for the 20GB and 13GB tables. What can be done? You must try something out of the ordinary. The good thing here is that all the tables are MyISAM.
Have you ever considered running an rsync? How do you do that?
EXAMPLE
You have /var/lib/mysql as your datadir for a remote production database
Perform an rsync on /var/lib/mysql on prod server (IP Address 10.1.2.30) to /backups on a backup server. Just run a script something like this on the backup server:
INIT_TS=0
COMP_TS=0
DIFF_TS=61
RSYNC_THRESHOLD=60
DBSERVER_IP=10.1.2.30
FOLDER_TO_BACKUP=/var/lib/mysql
mkdir /backups/backup_temp
while [ ${DIFF_TS} -gt ${RSYNC_THRESHOLD} ]
do
INIT_TS=`date +%s`
rsync -arv user01@${DBSERVER_IP}:${FOLDER_TO_BACKUP}/ /backups/backup_temp
CURR_TS=`date +%s`
(( DIFF_TS = CURR_TS - PREV_TS ))
done
MYSQL_CONN="-h${DBSERVER_IP} -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
sleep 3
SEARCHING_FOR_SLEEP=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 3
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
rsync -arv user01@${DBSERVER_IP}:/var/lib/mysql/ /backups/backup_temp
BACKUP_TIMESTAMP=`date +"%Y%m%d%H%M%S"`
mv /backups/backup_temp /backups/${BACKUP_TIMESTAMP}
mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"
Here is what this basic backup script does
- Step 01) rsync production's /var/lib/mysql (from remote server) to /backups on local server
- Step 02) If running time of rsync is greater than 60 seconds, repeat Step 01
- Step 03) Close all tables and issue global read lock
- Step 04) Perform final rsync (SELECTs allowed only)
- Step 05) Release global read lock
Notice the following:
- the only mysql interaction is
- issuing the global read lock
- checking for the process ID that is holding the global read lock
- releasing the global read lock
- FOLDER_TO_BACKUP is the datadir. If you want to backup a specific database (say mydb), set FOLDER_TO_BACKUP to /var/lib/mysql/mydb
- Actual downtime is from the acquisition of the SLEEP_ID to the processing killing of the SLEEP_ID
Give it a Try !!!
UPDATE 2012-02-02 11:47 EDT
The above script secures mysqld in such a way that it takes a disklevel copy of the database while allowing mysqld to perform INSERTs, UPDATEs, and DELETEs during the copy. This is necessary since the MyISAM Storage Engine does not have a natural mechanism for allowing a copying of itself. Even powerful products like MySQL Enterprise Backup and Percona XtraBackup perform full table locking on MyISAM tables just like this. My personal preference is to script it so I can see it, feel it, taste it, and touch it and just know for myself that MyISAM is fully protected.
The advantage of using my script does stems from the fact that I shorten the length of downtime. In the context of this anwser, downtime is really the period where the mysqld process will only allow SELECTs to all MyISAM tables during the final rsync.
An additional benefit is that all MyISAM tables copied will all have the same point-in-time should you wish to restore all MyISAM tables as of the same point-in-time. That point-in-time will be based, not on the start of the backup process, but when the global read lock as acquired.
If you need to copy only specific tables and you can guarantee no DB Connection will touch it, only then can you toss aside any failsafes and copy any MyISAM table to your heart's content.
UPDATE 2012-02-03 11:47 EDT
If the length of time the rsync takes concerns you, here is an additional suggestion:
You should also make nightly backups of the binary logs. By having nightly copies of binary logs, you would essentially have the incremental backups via those binary logs.
That being said, you could set up a weekly or monthly run of the backup script. Recovery to a specific point-in-time would be your responsibility from there, but you would have every piece of data necessary to do so.
Related Question
- Mysql – increasing disk space for thesql data volume
- Mysql – Is it safe to run optimze on mutliple tables in parallel in thesql (percona)
- Mysql – Managing ibtmp1 File Growth on MySQL 5.7
- Mysql – table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines)
- Mysql – Is it possible to allocate data free for certain table
- MySQL- reducing memory footprint
- MySQL DBs taking up way too much disk space
Best Answer
u can do something like this: add this to /etc/my.cnf
[mysqld] expire_logs_days=3
and mysqld will delete them logs for you
if you talking about reduce the size of the table , i would advise this:
1st u need to sort from workbench or phpmyadmin , which tables consumes the most. 2nd maybe u can start plan on how to reduce that 'biggest table' 3rd u think to think or consult with your superior , how long u need to keep the record , some company might wanna keep 3months record only ,then dump and keep somewhere(cloud or Harddisk) , or 1 year or 3 years . 4th before dump , maybe u can create archive table. eg: your original table 'test' , archive table rename as 'test_archive' then move older records or records that u want to dump there. 5th dump and move to somewhere safer.