MariaDB - Binary Log, part one

MariaDB - Binary Log, part one

Colleagues hello to all.

I'm starting a new series of articles about a very important component in MariaDB called Binary Log.

The binary log is important because it contains all the records and changes that occur in the database. The binary logs contain both the data and structure of all database schemas, as well as how long each statement took to execute. The only actions not logged in binary logs are actions that do not modify the database, such as: SELECT and SHOW.

Binary logs can be used for a variety of purposes. For example, when replicating or backing up, as well as rolling back the database to a certain point in time, and much more.

In my articles, I will tell you about all the possibilities of binary logs.

The version of the database I am using is 10.8.3.

 

Article content:

  1. Check if binary logging is enabled.
  2. Enable binary logging.
  3. Setting the name and storage location of binary logs.
  4. Compression.
  5. Binary log size.

 

1. Checking if binary logging is enabled.

Binary logging is disabled by default after DBMS installation. The  log_bin variable is responsible for enabling the binary log.

mariadb> SHOW VARIABLES LIKE 'log_bin';

binary_log

Values ​​OFF mean binary logging is disabled.


 

2. Enable binary logging.

The log_bin variable is static, which means that using the SET or SET GLOBAL command it will not be possible to change the value of the variable. In order to enable binary logging, you need to change the values ​​of a variable in the configuration file. The configuration file is located in the directory /etc/my.cnf.d/server.cnf

$. vim /etc/my.cnf.d/server.cnf

binary_log

Save the file and restart the database.

 

binary_log

After re-checking the variable, we see that the value of OFF has changed to ON.

 

binary_log

And on top of that, we have two files in the database directory.

  • ON.000001 - This is the binary log.
  • ON.index - This file contains the entire list of binary logs that are created during the database operation.

 

3. Setting the name and location of the binary logs.

I think many will agree with me that the beginning of a binary log name that starts with ON is somehow not very good. In general, it is better to store all binary logs somewhere separate from the database.

mariadb> SHOW VARIABLES LIKE '%log_bin%';

binary_log

  • log_bin - Indicates that binary logging is enabled.
  • log_bin_basename - Log name and directory where all logs will be stored. 
  • log_bin_index - A simple file that will list all binary logs.

 

I suggest creating a separate  binarylog directory to store all binary logs.

$. mkdir /mariadb/binarylog

After creating the directory, you can now change the values ​​of the  log_bin variable, but the variable is static and therefore we will change the values ​​in the database configuration file.

$. vim /etc/my.cnf.d/server.cnf

binary_log

Save the file and restart the database.

 

binary_log

binary_log

As you can see now the files are called mariadb-binary-log and they will always be stored in the binarylog directory.


 

4. Compression.

Binary logs can be compressed to save disk space, but database performance is not affected by compression. The  log_bin_compress variable is responsible for compression, compression is disabled by default.

mariadb> SHOW VARIABLES LIKE 'log_bin_compress';

binary_log

 

The variable is dynamic, so you can change the values ​​in the console.

mariadb> SET GLOBAL log_bin_compress = ON;

binary_log

 

And of course, we also change the values ​​of the variable in the configuration file.

binary_log


 

5. Binary log size.

Binary logs have a maximum size set by the  max_binlog_size variable. The default size of each binary log is 1 gigabyte.

mariadb> SHOW VARIABLES LIKE 'max_binlog_size';

binary_log

Size is in bytes.

The variable is dynamic and therefore you can change the size in the console. To be honest, I have never had to change the size of the binary log, and therefore it is better not to touch it like this.


 

Thank you all, I hope that my article helped you in some way.

SIMILAR ARTICLES

MariaDB - ERROR Incorrect definition of table
MariaDB - ERROR Incorrect definition of table
MARIADB
Read more
Mariabackup backup - gzip compression
Mariabackup backup - gzip compression
MARIADB
Read more
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
MARIADB
Read more