Clean space from mysql-bin logs

Today, I was found out one of customer server filled up with mysql-bin log and the server var space fully utilized.

From their MySQL path, file as below have filled up,
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
….

As mysql-bin used to record the event of the MySQL databases and it is not necessary to keep the log from the first day of the databases create until the present date, hence I would suggest for maximum 30 days of logs to be kept.

To disable the mysql-bin, simply remove log-bin from the MySQL configuration file, /etc/.my.cnf or my.ini.
log-bin = mysql-bin

Otherwise, keep only 30 days of log,
expire_logs_days = 30

MySQL: Load Data Infile or Outfile

For many cases, you might want to load your data out from the database to a text file or dump the database through the SQL query with a .sql file without a control panel such as phpmyadmin, etc. You may actually done it through the MySQL console query engine.

In this article, Mick Genie will guide you on Load Data Infile and Outfile from MySQL databases query.

Basically, you will need to understand what is infile and what is outfile, to differentiate it, you might refer to the detail as below.
infile: when you using infile, that meant you are inserting data to the database and it is the same when you are using < symbol from the MySQL console.
outfile: If you understand infile, then you shall understand outfile is use to grep the data from the database and store to the text.

Below is the sample script with infile could be used from the PHP script.

$tableName¬† = ‘mickgenie’;
$query = “LOAD DATA INFILE ‘mickgenie.sql’ INTO TABLE $tableName”;
$result = mysql_query($query);

Below will be the outfile sample from the MySQL query.

SELECT * INTO OUTFILE ‘data.txt’ FIELDS TERMINATED BY ‘,’ FROM table2;

How to repair your MySQL databases

If you are using MySQL databases as your database engine, you surely ever get the error message as below if your database grown day by day.

Table “table” is marked as crashed and should be repaired.

To understand MySQL database table with MyISAM type, you will have .frm, .MYI and .MYD type, so the data stored from MyISAM type is storing to .MYI file extension.

To repair the database, open your SSH Console, type the following command.

myisamchk -o /path_to_mysql/database_name/table_name.MYI

where the table_name is the table name.

If you got tmp drive too low, you may add the following command from the my.ini or my.cnf.

[mysqldump]
tmpdir = /home/mysql/tmp

assume you assign the path to /home/mysql/tmp/.

Your website shall working without any issue now.