Logging in mysql linux

How to Enable and Use Binary Log in MySQL/MariaDB

In MySQL or MariaDB database, when you make any changes in the database, every event is logged. All events are stored in the binary log.

In simple terms, the binary log is a file set containing information on data modifications made to a MySQL server instance. It has all the information, updates the database, deletes it, creates and deletes the table, and more.

The binary log is very useful when you are using MySQL replication. In this case, a binary log will send the data from the master MySQL server to the slave server. You can also use the binary log to perform any recovery operations in MySQL.

This post will show you how to enable and use binary log in MySQL on Linux.

Table Of Contents

Requirements

• A server running Linux with MariaDB installed.
• A root password is set up in the server.

Verify Binary Log

Before starting, you will need to verify whether the bin-log option is turned on or not and what binary log format is used.

To check it, first log in to MySQL with the following command:

Once login, run the following command to check the binary log status:

 
MariaDB [(none)]> show variables like '%bin%';

You should get the following output:

 
+-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | bind_address | 127.0.0.1 | | binlog_annotate_row_events | ON | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_commit_wait_count | 0 | | binlog_commit_wait_usec | 100000 | | binlog_direct_non_transactional_updates | OFF | | binlog_file_cache_size | 16384 | | binlog_format | MIXED | | binlog_optimize_thread_scheduling | ON | | binlog_row_image | FULL | | binlog_stmt_cache_size | 32768 | | encrypt_binlog | OFF | | gtid_binlog_pos | | | gtid_binlog_state | | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | OFF | | log_bin_basename | | | log_bin_compress | OFF | | log_bin_compress_min_len | 256 | | log_bin_index | | | log_bin_trust_function_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | read_binlog_speed_limit | 0 | | sql_log_bin | ON | | sync_binlog | 0 | | wsrep_forced_binlog_format | NONE | +-----------------------------------------+----------------------+

As you can see, the binary log is off in the MySQL server.

Next, check whether the binary log is used or not with the following command:

 
MariaDB [(none)]> show binary logs;

You should see the following output:

 
ERROR 1381 (HY000): You are not using binary logging

Next, check the binary log data directory with the following command:

 
MariaDB [(none)]> show variables like 'datadir';

You should get the following output:

 
+---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.003 sec)

Enable Binary Log in MySQL

You must edit the MySQL main configuration file to enable binary logging.

 
nano /etc/mysql/mariadb.conf.d/50-server.cnf

 
log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_format = mixed

Save and close the file when you are finished. Next, restart the MySQL service to apply the changes.

 
systemctl restart mariadb

Now, connect to MySQL and verify the binary log status with the following command:

 
MariaDB [(none)]> show variables like '%bin%';

You should see that binary log is now enabled:

 
+-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | bind_address | 127.0.0.1 | | binlog_annotate_row_events | ON | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_commit_wait_count | 0 | | binlog_commit_wait_usec | 100000 | | binlog_direct_non_transactional_updates | OFF | | binlog_file_cache_size | 16384 | | binlog_format | MIXED | | binlog_optimize_thread_scheduling | ON | | binlog_row_image | FULL | | binlog_stmt_cache_size | 32768 | | encrypt_binlog | OFF | | gtid_binlog_pos | | | gtid_binlog_state | | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_basename | /var/log/mysql/mysql-bin | | log_bin_compress | OFF | | log_bin_compress_min_len | 256 | | log_bin_index | /var/log/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600 | | max_binlog_stmt_cache_size | 18446744073709547520 | | read_binlog_speed_limit | 0 | | sql_log_bin | ON | | sync_binlog | 0 | | wsrep_forced_binlog_format | NONE | +-----------------------------------------+--------------------------------+

Next, check whether the binary log is used or not with the following command:

 
MariaDB [(none)]> show binary logs;

You should see the following output:

 
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 328 | +------------------+-----------+ 1 row in set (0.000 sec)

All binary log files are stored in the /var/log/mysql directory. You can check them with the following command:

You should get the following output:

 
error.log mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.index mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009

Working with Binary Log

The mysqlbinlog is a command-line tool used to view the content of the binary log in a human-readable format.

For example, to view the content of mysql-bin.000001 binary file run the following command:

 
mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

You should get the following output:

 
#210303 11:52:15 server id 1 end_log_pos 503 CRC32 0x7260a26e GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 503 #210303 11:52:15 server id 1 end_log_pos 596 CRC32 0x2b572f9f Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1614772335/*!*/; create database testdb1 /*!*/; # at 596 #210303 11:53:26 server id 1 end_log_pos 619 CRC32 0xa1f8c5c4 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

As you can see, the above command will display a lot of data from all databases.

In this case, you can use the -d option to specify the specific database and display the information of the given database.

For example, run the following command to dump all the events of testdb database in the testdb.txt file.

 
mysqlbinlog --no-defaults -d testdb /var/log/mysql/mysql-bin.000001 > testdb.txt

If you want to display only SQL queries from the binary log file, run the following command:

 
mysqlbinlog --no-defaults -s /var/log/mysql/mysql-bin.000001

Sometimes, you do not want to log events in a binary log file, for example, a database recovery process. In that case, you can disable the binary log with the following command:

 
mysqlbinlog --no-defaults -D /var/log/mysql/mysql-bin.000001

Conclusion

The above guide taught you how to enable the binary log in MySQL. You also learned how to view a binary log with the mysqlbinlog command. I hope this will help you in your day-to-day operations.

What is the purpose of binary log?

The binary log is used for backup purposes and replication. Data is transmitted from one or more primary servers to one or more secondary servers depending on the binary log’s contents. For example, the binary log will cause a MariaDB server to operate a little bit more slowly.

How do I disable binary logging in Mariadb?

You can use the —skip-log-bin or —disable-log-bin options at startup to stop MySQL from logging binary data.

Scheduled AWS RDS Mariadb Backups SnapShooter

Backup your RDS Mariadb database to s3 or other providers s3 storage

Источник

MySQL Log Level

“Logging refers to the functionality of saving records of events and operations in an application. Logging is one of the most basic debugging forms as it allows the application to save a report detailing every instruction and process performed at a specific stage. This can, in turn, help developers backtrack, at which point the error occurs and more.”

This tutorial will explore how we can enable and configure various logging levels in the MySQL server. This determines what and how the log messages are created within the server.

MySQL Logging

Depending on the MySQL server installed and the configuration, MySQL will create a log file of the events in the server.

There are five types of log files supported in the MySQL server. These include:

1. Error log – this log file contains the logs of the errors encountered when starting, running, or stopping the MySQL server.
2. ISAM log – This is part of administrative log-level utilities. This log contains all the modifications made to the ISAM tables.
3. Query log – As the name states, this log file contains the records of user connections and executed queries.
4. Binary Log – Holds the log of the statements that modify the server. The mysqldbinlog utility handles this logging.
5. Slow log – holds the logs of the queries that exceed the long_query_time parameter value.

NOTE: Keep in mind that some of the logs discussed above are used in internal MySQL operations. As a regular user, various MySQL providers may abstract various logs.

We will only need to know the Error Log, General/Query Logs, and Slow Logs for this tutorial.

MySQL Enable Logging

Before configuring various logging levels in MySQL, we must ensure that the logging feature is enabled in the MySQL server.

Foremost, locate and edit the MySQL config file.

Keep in mind that the location of the MySQL configuration file may vary depending on system configuration, installation method, operating system, etc.

In the log file, add the entries as shown:

[ mysqld ]
log_error = / var / log / mysql / error.log
general_log_file = / var / log / mysql / mysql.log
general_log = 1
log_slow_queries = / var / log / mysql / mysql-slow.log
long_query_time = 10

The configuration above enables error logs, general logs, and slow logs. In addition, you can change the location of the log files by specifying the target path in the configuration above.

Save the changes and continue to restart the server.

In Windows, you can enable MySQL logging by editing the my.ini file with the following options.

general-log= 1
general_log_file = «mysql.log»
slow-query-log= 1
slow_query_log_file = «mysql-slow.log»
long_query_time = 10
log-error= «mysql.err»

Similarly, you can change the path and file names of the log files to your liking. By default, MySQL will store the logs files in the Data directory (Windows only).

MySQL Logging Levels

MySQL supports up to eight logging levels. Each level determines the details included in the log files and the type of messages.

By default, the MySQL shell will run at logging level 5. This includes an error, warnings, and informational messages.

The following are the supported logging levels and what each entails.

When starting the MySQL shell, you can configure the verbosity level using the –verbose parameter.

1. Verbosity Level 0 – No Messages are displayed.
2. Level 1 – Internal error, error, warning, and information messages.
3. Level 2 – includes all level 1 messages and Debug messages.
4. Level 3 – Adds Debug2 messages to Level and Level 2.
5. Level 4 – Adds Debug3 messages. These include highly detailed log messages.

Specifying MySQL Log Level in the Shell

To specify the log level you wish to use, we can use the mysqlsh command as shown:

You can specify the log level as a numeric value or its string representation as:

NOTE: In Windows, the mysqlsh command is located in “C:\Program Files\MySQL\MySQL Shell 8.0\bin”.

To view the current log level, Open your MySQL Shell:

Once in the shell, run the command:

This should list the MySQL Shell options, including the current log level:

Closing

And that’s it; you have successfully discovered how to work with MySQL Logging options. Keep in mind that MySQL log options are extended beyond the scope of this tutorial. You can check the docs for more.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list

Источник