Skip to main content

mysql replication and binary logs expiration

Mysql replication is very well documented on mysql server web site and has a well explained howto that you can follow when you are setting up replication on your mysql databases.

You have to set in mysql server configuration file the log_bin parameter specifying the binary log file name that will be replicated across all the slaves. Mysql slaves take this binary logs from master and apply them to database; the problem I found is that Mysql server does not manage this binary logs expiration automatically, so if you are not careful and your database has a lot of changes your system can run out of disk space.

To address this issue, mysql has another parameter “expire_logs_days” that with you can specify the value in days of the binary logs expiration:


expire_logs_days = 5
max_binlog_size = 1G

In this config, 5 days after logs replication, mysql will automatically remove them. You have to adjust this value to be sure your hard disk is not going to run out of space and your slave has time enough to apply the binary log changes considering the number of days slaves might lag behind the master.

In the same way, you can use purge binary logs mysql command to purge old binary logs:

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)

 

I had this error  when I moved all database server files,  phisically,  from one server to another for replication purposes and used the mysql startup script. The new server is a Debian Squeeze,  in debian there is a user debian-sys-maint   to do maintenance tasks,  to avoid this error with the mysql startup script you have to create this user in your new database server.

 

The password for this user can be seen in this file:


# cat /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client] host = localhost
user = debian-sys-maint
password = password
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade] host = localhost
user = debian-sys-maint
password = password
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

To create the user,  log in as mysql root user and run this statement:

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'password'; 

In my case,  the original server was a Centos 5.5,  so that the user debian-sys-maint did not exist,  but in case the server was another Debian you can use the same statement to only reset the user password.