Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.
I wrote posts on why not to do this:
However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.
For example
- Suppose the table to shrink is
mydata.mytable
datsdir
is /var/lib/mysql
Your procedure would be
STEP 01: You should create a database called mydatatemp
. Do not put any data in it just yet.
STEP 02: Move the /var/lib/mysql/mydatatemp
over to another disk.
STEP 03: Create a Symlink /var/lib/mysql/mydatatemp
over to the folder on the other disk
STEP 04: chown -R mysql:mysql
against the external folder and against the symlink.
STEP 05: Now perform the data shrinkage as follows:
CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;
To recap, do the following (assuming /tmpdata
is the external disk)
STEPS 01-04
chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp
then run STEP 05
CAVEAT
This would be rather high risk, but it should work. Please test this with an empty table first like this:
USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;
If these SQL command worked, go to OS and do this:
cd /tmpdata
ls -l
You should see two files:
Also, test if mrnorm can be moved
CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;
Go to the OS and run
cd /tmpdata
ls -l
The table should be gone. Now, do this:
cd /var/lib/mysql/helloworld
ls -l
The table should be there
END OF TEST
Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.
Unfortunately, Yes you have to do every database. I have a step-by-step process I wrote up back on Oct 29, 2010 in StackOverflow as well as thorough explanation why there is nothing else you can do.
You also asked
Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database ?
You cannot reclaim half. It is an all-or-nothing decision. It is good to keep in mind what lives in ibdata1 when innodb_file_per_table is disabled :
Still, if you want to know which InnoDB tables are in ibdata1 and which are in their own tablespace file (.ibd
), here is what you do:
STEP 01 : Run this query in the MySQL client
SELECT COUNT(1) InnoDBTableCount
FROM information_schema.tables
WHERE engine='InnoDB';
STEP 02 : Collect the number of .ibd
files in the OS
ls -lR | grep -c ".ibd$"
STEP 03 : Subtract those values in the OS
Here is the shell script to compute these things
cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}
If you have partitioned tables, then do this:
cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.partitions WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}
Here is what you get:
IB_TOT
: Number of InnoDB Tables in the MySQL Instance
IB_IBD
: Number of InnoDB Tables Residing Outside ibdata1
as .ibd
Files
IB_SYS
: Number of InnoDB Tables Residing Inside ibdata1
Best Answer
PARTITION BY RANGE(TO_DAYS(date))
and have daily partitions. Every nightDROP PARTITION
for the week-old partition andREORGANIZE
the normally empty "future" partition into tomorrow and a new "future".That makes the delete essentially free and instantaneous. And keeps the disk space down to not much more than a week's worth of data.
More details .
(No Triggers are needed, no table rotation. Queries can work transparently on
PARTITIONed
tables. There may need to be some changes to the indexes.)