FreeKB - mySQL / MariaDB Binary Logs
mySQL / MariaDB - Binary Logs

The binary log files are used when you have configured replication between two (or more) mySQL or MariaDB databases.

For example, let's say you have configured Master-Slave replication on Linux.

The following command can be used to determine the primary configuration file being used by mySQL or MariaDB. In this example, /etc/my.cnf is the primary configuration file being used by mySQL or MariaDB.

~]# mysql --help | grep /my.cnf | xargs ls
ls: cannot access /etc/mysql/my.cnf: No such file or directory
ls: cannot access /usr/etc/my.cnf: No such file or directory
ls: cannot access ~/.my.cnf: No such file or directory
/etc/my.cnf

 

The log-bin directive in the main mySQL configuration file (e.g. /etc/my.cnf) will define the name and location of the binary log files.

log-bin=/var/lib/mysql/binary-log

 

Likewise, the max_binlog_size directive will determine how large a binary log file can become. It usually does not make much sense to increase or decrease this value if the goal is to reduce the number of binary logs that are being rotated, as a smaller size will just create more logs and a larger size will create less logs. At the end of the day, the logs will still use up the same amount of storage.

max_binlog_size=100M

 

The expire_logs_days directive will determine how long the binary logs will be retained. This option has been deprecated after mySQL version 8.0.

expire_logs_days=1

 

If using mySQL version 8.0 or higher, the binlog_expire_logs_seconds directive can be used. The mysql -V command can be used to display the version of mySQL.

binlog_expire_logs_seconds=43200

 

Assuming you have logged into your MariaDB or mySQL server or configure passwordless authentication, and the user issuing the following commands has the Alter_priv, you can use the show master status command to get the name of the binary log file currently being used by the master.

show master status;

 

Which should return something like this. If no output is returned, this may suggest that replication has not been configured.

             File: binary-log.000123
         Position: 146162499
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

 

On the system running the slave, the show slave status can be used.

show slave status;

 

Which should return something like this. Notice in this example the the slave is running and is using the same binary log as the master.

  Master_Log_File: binary-log.000123
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter a3477 in the box below so that we can be sure you are a human.




Comments

Web design by yours truely - me, myself, and I   |   jeremy.canfield@freekb.net   |