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>
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
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]#
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>