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,
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
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;