Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.


Monday, January 24, 2011

Purge Bin_Logs, Save Disk Space - Mysql

Purge Bin_Logs Save Disk Space

Oftenly i meet with a question in mysql that, "How can I remove old mysql-bin log file in log directory?"

So here is the way... sort, good and practically proved.

There is two way to purge the bin-logs
1: Automatic way
2: Manually way, and (May be next step in-case.)

To purge bin-logs automatic, you can add a "expire_logs_days" Variable in my.cnf during the configuration of replication server.

expire_logs_days = 7

It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log switch

Now one condition, if you have deleted some binary logs earlier manually and hasn't updated the ".index" file, then you have to run the  below MySQL query.

Note: The "expire_logs_days" settings won't work if you have already deleted some bin logs manually.
It will always tries to find the old deleted bin logs, unless you manually update the index file and delete the binlogs file name which is deleted.

# mysqladmin -u root -p  flush-logs


mysql> show slave status \G
check here for current "Master_Log_File"

Like this:
Slave_IO_State: Waiting for master to send event
Master_Host: xxx.xxx.xxx.xx (IP of master server)
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlcluster1-bin.000233
Read_Master_Log_Pos: 929859308
Relay_Log_File: mysql-repl-lb-relay-bin.000050
Relay_Log_Pos: 929859453
Relay_Master_Log_File: mysqlcluster1-bin.000233
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

goto in data dir of mysql slave:

ls -lht on the bases of this:  Run the below

mysql> PURGE BINARY LOGS BEFORE '2010-02-27 11:02:30';

but before you purge please make sure that slave is on sync with master or confirm the current status from slaves and also confirm that, you are leaving 2-3 days bin-logs on server.

No comments:

Post a Comment