最近开始在学mysql,在学习事物日志的时候配置遇到点困难,上网翻了很多东西,发现很多都是不行的,于是在这里把我自己配置好的文件分享给大家。
首先给大家分享几个可能用到的linux指令:
sudo nautilus //linux下获取一个带有root权限文件管理窗口,可以对系统文件进行最高权限的读写
systemctl start mysql //linux下开启mysql服务端的指令
这两个指令可以让你相当方便地调节mysql的各种参数,另外在linux中mysql的配置文件是存放在/etc/mysql/mysql.conf.d/mysql.cnf中,和windows不大一样。
好了,现在来说一如何配置二进制日志:
mysql的配置文件中其实已经存在了相关的注释了:
这个是二进制日志相关段:
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
只要把server-id与log_bin字段中的#去掉即可,当然如果你的注释实在没有的话也可以在[mysql]中加入这些字段,最终只要写成这样就行了:
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name//仅对标记的数据库开启二进制日志
#binlog_ignore_db = include_database_name//对标记的数据库不开启二进制日志
这个是关于错误日志的一段,默认是开着的,如果没有的话跟上边那样加入到[mysql]中就行了
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
这个是慢查询日志相关的一段:
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes//日志查询不使用索引
但是你按照我的方法把下面三条语句都删掉等号后并不能成功配置数据库原因是这里差了一条语句:log_slow_queries = NO
因此只要把它改成这样即可:
# Here you can see queries with especially long duration
log_slow_queries = NO
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes