List:InnoDB Storage Engine« Previous MessageNext Message »
From:drsystems Date:January 24 2012 12:55pm
Subject:reducing the size of a ibdata1
View as plain text  
Hi,
I have read several articles in the Internet on how to dump and re-create
an innodb to reclaim disk space. I am trying to do this in a test
environment, but no luck.

I run the commands and script below, but after the dump is fed back into
mysql, the sizes of ibdata1, logs, and dbmail/session.MYD are the same.
Can someone tell me if I am doing something wrong, or if my test is just
not big enough to work?

Thank you in advance,
  Dirce
-------
mysql> set foreign_key_checks = 0;

run the following script:
#################
#!/bin/sh

mysql set foreign_key_checks = 0;

# dump the database tables
cd /var/tmp

/usr/bin/mysqldump -R -q -u root --all-databases --skip-lock-tables >
/var/tmp/s
mg-dump-all.sql

service mysqld stop

# delete the ibdata files and table files
cd /var/lib/mysql
rm -f ib*
cd dbmail
rm -f *

# reinstall the base database
/usr/bin/mysql_install_db
service mysqld start

# recreate dbmail tables
mysql < /var/tmp/dbmail-recreate.sh

# reload the saved tables
mysql < /var/tmp/smg-dump-all.sql
service mysqld restart

# change the default character set back
mysql -u root -p
ALTER DATABASE dbmail DEFAULT CHARACTER SET utf8;
FLUSH PRIVILEGES;
quit

# reset the root password to blank (may not be needed)
mysql -u root -p
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');
FLUSH PRIVILEGES;
quit
##################

mysql> set foreign_key_checks = 1;


Thread
reducing the size of a ibdata1drsystems24 Jan
  • Re: reducing the size of a ibdata1Reindl Harald24 Jan
    • Re: reducing the size of a ibdata1franco riggi25 Jan
  • Re: reducing the size of a ibdata1drsystems27 Jan