linux 云计算 mysql 数据库 架构设计（日志）

mysql日志
错误日志   是mysql运行当中错误信息  mysql启动  关闭   重启的信息    用户登录失败的信息      默认是开启的
mysql> show variables like "log_error";
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.10 sec)
set   global    临时修改
更改配置文件
/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
 26 log-error=/var/log/mysqld.log
错误日志的登记   
mysql> show variables like "%log_warning%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+
1 row in set (0.00 sec)
0 记录error级别以上的日志
1记录warning以上的日志
2记录warning和warning以上的日志

查询日志    记录mysql的sql语句执行的信息     手动开启
mysql> show variables like "%general_log%";
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.01 sec)
[root@localhost ~]# vim /etc/my.cnf
 28 general_log=1
 29 general_log_file=/var/lib/mysql/aa.log
[root@localhost ~]# systemctl restart mysqld
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /var/lib/mysql/aa.log |
+------------------+-----------------------+
2 rows in set (0.54 sec)
/data    chown  -R   mysql:mysql   /data
5.5     log=路径
慢查询日志    记录执行时间比较长的查询语句   设置阈值    1m
mysql> show variables like "%slow_query_log%";
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
[root@localhost ~]# vim /etc/my.cnf
slow-query-log=on
slow-query-log-file=/var/lib/mysql/slow.log
long-query-time=2
[root@localhost ~]# systemctl restart mysqld
+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| slow_query_log      | ON                      |
| slow_query_log_file | /var/lib/mysql/slow.log |
+---------------------+-------------------------+
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.06 sec)
[root@localhost mysql]# cat /var/lib/mysql/slow.log 

二进制日志     记录mysql里面增删改查的语句     恢复数据     主从
mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
[root@localhost mysql]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
log-bin=/var/lib/mysql/mysql-bin  两种写法   
[root@localhost mysql]# systemctl restart mysqld

log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls
mysql-bin.000001
每次重启一次    二进制日志都会生成新文件  每次增加1
mysql-bin.000002

中继日志       主从   从节点需要开启的日志      用来保留主节点传输过来的二进制日志
mysql> show variables like "%relay_log%";
| relay_log                 |  
[root@localhost mysql]# vim /etc/my.cnf
relay-log=/var/lib/mysql/relay-bin  两种写法
relay-log=relay-bin
[root@localhost mysql]# systemctl restart mysqld
| relay_log                 | relay-bin