The solution of mysqldump backup report Binlogging on server not active

Redhat 6.5 x64 Mysql 5.7 perform backups:

[root@master 3306] # mysqldump-u root-p-master-data=2-- single-transaction-R-- triggers-A > all.sql

Enter password:

Mysqldump: Error: Binlogging on server not active

Solution:

Sudo vi / etc/my.cnf

Add: under the [mysqld] tag:

Log-bin=mysql-binserver-id=1

After saving and exiting, restart the service:

Systemctl restart mysqld

It's done. Note that if there are other mistakes, take a closer look

Error messages in / var/log/mysqld.log to find the problem faster.

Refer to the following.

1) first of all, why enable binlog logs and slow query logs?

The binlog log will record the addition, deletion and modification of the database. When you accidentally delete or empty the data, or if the database system goes wrong, you can use the binlog log to restore the database. To put it simply, it is something that records the backup.

Slow query log slow_query_log, this thing is used to record slow sql statements, query logs to find which sql statement is slower, and then you can optimize the database or sql statements or programs. To put it simply, it is an auxiliary tool for optimization.

The PS:binlog log should be open all the time (because you don't know when the database will collapse or something will be deleted by mistake). Slow query logs can not be opened usually. If you think the website is slow and need to be optimized, you can open the slow query to do an assistant.

2) how to open binlog log

Just add a binlog configuration under [mysqld] of my.cnf (be sure to add it under [mysqld], but not under [mysqld_safe] or other locations). The configuration is as follows:

Log-bin=mysql-binserver-id=1

PS: the mysql-bin above is actually the prefix of the binlog log file. If mysql-bin is set above (you can change it to mysql_bin or binlog or whatever), it will generate mysql-bin.000001,mysql-bin.000002,mysql-bin.000003 in / var/lib/mysql/ (the default location). Wait for log file

3) enable slow query log

Modify configuration method 1 (temporarily, automatically recover after restarting mysqld):

Set global log_slow_queries=ON;set global slow_launch_time=10

Modify configuration method 2 (permanent):

Just add a binlog configuration under [mysqld] of my.cnf (be sure to add it under [mysqld], but not under [mysqld_safe] or other locations). The configuration is as follows:

Log-slow-queries=/var/log/mysqld-slow.loglong_query_time=2

The first configuration is the log file location, and the second configuration is the slow query time configuration (seconds). The query statements that take longer than this time will be recorded.