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:
- Check if binary logging is enabled.
- Enable binary logging.
- Setting the name and storage location of binary logs.
- Compression.
- 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';
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
Save the file and restart the database.
After re-checking the variable, we see that the value of OFF has changed to ON.
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%';
- 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
Save the file and restart the database.
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';
The variable is dynamic, so you can change the values in the console.
mariadb> SET GLOBAL log_bin_compress = ON;
And of course, we also change the values of the variable in the configuration file.
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';
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.
