Clean - Shrink ibdata1
To shrink ibdata1 once and for all you must do the following:
MySQLDump all databases into a SQL text file (call it SQLData.sql)
Example, change directory to the /home directory which may be a partition with more space.
mysqldump -uroot -p --all-databases --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql
Drop all databases (except mysql schema) via PHPMyAdmin or shell.
service mysql stop
Add the following lines to /etc/my.cnf
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile
At this point, there should only be the mysql schema in /var/lib/mysql
service mysql start
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
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.
Was this article useful? Have any suggestions or improvements to the information?
Log into your control panel and go into the MariaDB - MySQL section. Make sure you have an...
PHP5 is now the default PHP ran on our servers. Nothing further is required.
To run a PHP file from cron, use the "cron jobs" section of your control panel. Use the following...
Error: SoftException in Application.cpp:303 UID of script is small than min_uid when going...
Basedir restriction is set for security. This most often occurs when you are calling an app...