MySQL
My folder mysql/data is huge. I have dropped some databases that have a huge amount of records, removed some logs tables but.. mysql/data size remains unchanged.
What is the best way to compact it? It takes something like 20 Gigabytes on HD and contains about 30 medium or small database. My whole databases folder takes 300 Megabytes.
In addition ibdata1, mysql-bin.000332, mysql-bin.000326, mysql-bin.000206 take about 20 Gigabytes.
PS: I use Windows 7
If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.
You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:
lsof | grep '/path_to_mysql'
You might see mysql schema tables marked as deleted in there:
(deleted)
Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.
As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.
And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.
/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start
For a MyISAM table mydb.mytable, you should have three files
\bin\mysql\mysql5.6.12\data\mydb\mytable.frm\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD\bin\mysql\mysql5.6.12\data\mydb\mytable.MYIThey should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.
Take a look at this Pictorial Representation of InnoDB
The only thing that attaches ibdata1 to the .ibd files is the data dictionary.
Your mission, should you decide to accept it, is to create each table and swap in the .ibd
Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another
Here is a sample
Suppose you have a database mydb with the table mytable. This means
\bin\mysql\mysql5.6.12\data\mydbmytable.frmmytable.ibdYou need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.
You should now do the following
mytable.ibd to \bin\mysql\mysql5.6.12\dataALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)\bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydbALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:
SELECT * FROM mydb.mytable LIMIT 10;
Give it a Try !!!
Best Answer
If all tables in your database uses InnoDB then fire Optimize table table_name ....make sure
"if your table size is 100MB then double of this size must be available in datadir drive.."
If you are using MyISAM you can check with MyISAMcheck utility to optimize myisam tables...
Which MySQL version you are using? If your MySQL DB version is 5.6 or 5.7 then ibtemp will be there in datadir. Normally MySQL not able purge all temporary tables which is created by user stored in ibtemp and it grows day by day. If you are affordable downtime then take restart of MySQL service. It free disk space used by ibtemp temporary Tablespace.
Read about innodb row format you will get idea..