Table of Contents

Overview

Binary logs

Binary logs are essential for online and warm backup and technically we have two kinds of binary logs:

Because of the pros and cons of each type of binary logs generation, it is recommended to use the DEFAULT one, which is: BOTH. MySQL is able to decide which type of binary log to generate depending on the situation.Binary logs are indexed with index file, kept by the MySQL.

NEVER DELETE BINARY LOG MANUALLY, ALWAYS USE A TOOL TO REMOVE A BINARY LOGS.

You can check the binary log generation by running the following command:

[root@mysqlmaster ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW BINARY LOGS
    -> ;
ERROR 1381 (HY000): You are not using binary logging
mysql>

Enable Binary logs

The binary logs are easy to be enabled, just add the:

[root@mysqlmaster log]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1 <- This line
log-bin=mysql-bin   <- This line

Restart

After the binary logs are added, restart the mysqld sevice:

[root@mysqlmaster log]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@mysqlmaster log]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2018-02-10 13:29:11 EST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1827 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1809 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1831 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1831 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Feb 10 13:29:10 mysqlmaster systemd[1]: Starting MySQL Server...
Feb 10 13:29:11 mysqlmaster systemd[1]: Started MySQL Server.
[root@mysqlmaster log]#

Check

From database side, the location and mode of the binary logs can be checked as follows:

mysql>  SHOW VARIABLES LIKE '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name                           | Value                      |
+-----------------------------------------+----------------------------+
| basedir                                 | /usr/                      |
| binlog_direct_non_transactional_updates | OFF                        |
| character_sets_dir                      | /usr/share/mysql/charsets/ |
| datadir                                 | /var/lib/mysql/            |
| ignore_db_dirs                          |                            |
| innodb_data_home_dir                    |                            |
| innodb_log_group_home_dir               | ./                         |
| innodb_max_dirty_pages_pct              | 75.000000                  |
| innodb_max_dirty_pages_pct_lwm          | 0.000000                   |
| innodb_tmpdir                           |                            |
| innodb_undo_directory                   | ./                         |
| lc_messages_dir                         | /usr/share/mysql/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/   |
| slave_load_tmpdir                       | /tmp                       |
| tmpdir                                  | /tmp                       |
+-----------------------------------------+----------------------------+
15 rows in set (0.00 sec)

mysql> show binary logs
    -> ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql>