Introduction détaillée aux 4 types de logs dans Mysql

Mysql dispose de 4 types de journaux : journal des erreurs, journal des requêtes générales, journal binaire et journal des requêtes lentes**

1 journal des erreurs

enregistre les erreurs pendant le fonctionnement de Mysql, avertissement. et d'autres informations, s'il y a une erreur système ou un problème avec un certain enregistrement, vous pouvez consulter le journal des erreurs.

• Le journal des erreurs MySQL est stocké dans le répertoire des journaux Mysql sous le nom d'hôte.err par défaut. Il peut être consulté via l'instruction suivante :

  mysql> show variables like "log_error";
  +---------------+----------------+
  | Variable_name | Value          |
  +---------------+----------------+
  | log_error     | /tmp/mysql.log |
  +---------------+---------------

• .

  Modifier L'adresse du journal des erreurs peut être ajoutée à /etc/my.cnf --log-error = [filename] pour activer le journal des erreurs mysql. Le mien est :

   log_error = /tmp/mysql.log

• Vérifions-le d'abord : tail -f /tmp/mysql.log

  bash-3.2# tail -f /tmp/mysql.log 
  2015-12-23T02:22:41.467311Z 0 [Note] IPv6 is available.
  2015-12-23T02:22:41.467324Z 0 [Note]   - '::' resolves to '::';
  2015-12-23T02:22:41.467350Z 0 [Note] Server socket created on IP: '::'.
  2015-12-23T02:22:41.584287Z 0 [Note] Event Scheduler: Loaded 0 events
  2015-12-23T02:22:41.584390Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: ready for connections.
  Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
  2015-12-23T02:22:42.540786Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151223 10:22:42
  151223 10:22:51 mysqld_safe A mysqld process already exists
  2015-12-23T02:25:30.984395Z 2 [ERROR] Could not use /tmp/mysql_query.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL GENERAL_LOG=ON" or restart the MySQL server.
  2015-12-23T07:28:03.923562Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 61473ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

Quantité de informations Il est relativement volumineux et ne sera pas analysé pour l’instant. . . . Bien sûr, s'il y a une erreur dans la configuration ou la connexion de MySQL, vous pouvez toujours suivre le journal via tail -f

2. Journal général des requêtes

Enregistrez le journal quotidien de MySQL, y compris les requêtes , modifications, mises à jour, etc. Chaque sql.

• Vérifiez d'abord si mysql a activé le journal des requêtes : afficher les variables globales comme "%genera%"

  mysql> show global variables like "%genera%";
  +----------------------------------------+----------------------+
  | Variable_name                          | Value                |
  +----------------------------------------+----------------------+
  | auto_generate_certs                    | ON                   |
  | general_log                            | OFF                  |
  | general_log_file                       | /tmp/mysql_query.log |
  | sha256_password_auto_generate_rsa_keys | ON                   |
  +----------------------------------------+----------------------+
  4 rows in set (0.00 sec)

  Je l'ai configuré ici Fichier de sortie du journal : /tmp/mysql_query.log, et la fonction de journalisation est désactivée

• Le fichier de sortie du journal des requêtes peut être ajouté dans /etc/my.cnf general-log-file = [filename]

• Une fois que Mysql a ouvert le journal du journal général, toutes les instructions de requête peuvent être affichées dans le fichier journal général. S'il est ouvert, le fichier sera très volumineux, il est recommandé de l'ouvrir pendant le débogage et de le fermer normalement

  mysql> set global general_log = on;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> set global general_log = off;
  Query OK, 0 rows affected (0.01 sec)

• Remarque :

  Si la fonction de journalisation est activée, mais qu'aucune écriture n'est effectuée dans le journal, il se peut que MySQL ne dispose pas de suffisamment d'autorisations sur le fichier journal, vous devez donc spécifier les autorisations. Mon fichier journal est /tmp/mysql_query.log. , puis :

  chown mysql:mysql /tmp/mysql_query.log

trois, journal binaire

Le journal binaire contient certains événements qui décrivent les modifications de la base de données, telles que la création de tables, les modifications de données, etc. est principalement utilisé pour les opérations de sauvegarde et de récupération, de restauration, etc.

1. Fonction :

• Contient toutes les données mises à jour ou potentiellement mises à jour (comme un DELETE qui le fait). ne correspond à aucune ligne)

• Contient environ Les informations de temps d'exécution de chaque instruction qui met à jour la base de données (DML)

• n'inclut pas les instructions qui ne modifiez aucune donnée. Si vous devez activer cette option, vous devez activer la fonction de journalisation générale

• L'objectif principal est de restaurer la base de données au point de défaillance de la base de données le plus près possible. autant que possible, car le journal binaire contient toutes les mises à jour effectuées après que la sauvegarde

• est utilisée sur le serveur de réplication principal Enregistrez toutes les instructions qui seront envoyées au serveur esclave

• L'activation de cette option réduit les performances de la base de données de 1 %, mais garantit l'intégrité de la base de données. Pour les bases de données importantes, cela vaut la peine d'échanger les performances contre l'intégrité

2.

Binlog a 3 formats

• STATATION : Chaque SQL qui modifie les données sera enregistré dans le binlog du maître, de l'esclave. Lors de la copie, le processus SQL sera analysé dans le même SQL exécuté du côté maître d'origine, puis exécuté.

  Un petit peu : en mode instruction, les défauts du mode ligne sont d'abord résolus. Il n'est pas nécessaire d'enregistrer les modifications dans chaque ligne de données, ce qui réduit la quantité de journaux binlog, économise les ressources d'E/S et de stockage, et améliore les performances. Parce qu'il n'a besoin que de stimuler les détails de l'instruction exécutée sur le maître et les informations contextuelles lors de l'exécution de l'instruction.
  Inconvénients : en mode instruction, puisqu'il s'agit d'une instruction d'exécution enregistrée, pour que ces instructions soient exécutées correctement du côté esclave, elle doit également enregistrer certaines informations pertinentes lorsque chaque instruction est exécutée, et ce sont des informations contextuelles à assurez-vous que lorsque toutes les instructions sont exécutées du côté esclave, elles peuvent obtenir les mêmes résultats que lorsqu'elles sont exécutées du côté maître. De plus, comme MySQL se développe rapidement, de nombreuses nouvelles fonctions sont constamment ajoutées, ce qui rend la réplication de MySQL confrontée à de nombreux défis. Naturellement, plus le contenu est complexe dans la réplication, plus il est facile pour les bogues d'apparaître. Dans la déclaration, il a été constaté que de nombreuses situations entraîneraient des problèmes avec la réplication Mysql, principalement lorsque certaines fonctions ou fonctions sont utilisées lors de la modification des données. Par exemple : la fonction sleep() ne peut pas être utilisée dans certaines versions et est copiée correctement. la fonction last_insert_id() est utilisée dans la procédure stockée, ce qui peut provoquer des identifiants incohérents sur l'esclave et le maître, etc.
  

• ROW : Le journal enregistrera la forme modifiée de chaque ligne de données, puis modifiera les mêmes données côté esclave. Seules les données à modifier seront enregistrées, seules les. valeur, pas la valeur. Il y aura des situations où plusieurs tables SQL sont associées.

  Avantages : En mode ligne, le bin-log n'a pas besoin d'enregistrer les informations liées au contexte de l'instruction SQL exécutée. Il a seulement besoin d'enregistrer quel enregistrement a été modifié et quelle a été la modification, donc le contenu du journal de la ligne. sera Les détails de chaque ligne de modification des données sont enregistrés très clairement, ce qui le rend très facile à comprendre. De plus, il n'y aura aucun problème si les procédures et fonctions stockées dans certaines circonstances, ainsi que les appels de déclencheurs et les déclencheurs, ne peuvent pas être copiés correctement.
  Inconvénients : en mode ligne, lorsque toutes les instructions exécutées sont enregistrées dans le journal, elles seront enregistrées sous forme de modifications apportées à chaque ligne, ce qui peut générer une grande quantité de contenu du journal.
  

• MIXED：MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种

3. 配置

• 查看mysql中二进制文件的配置情况：show variables like "%log_bin%";

  mysql> show variables like "%log_bin%";
  +---------------------------------+-------+
  | Variable_name                   | Value |
  +---------------------------------+-------+
  | log_bin                         | OFF   |
  | log_bin_basename                |       |
  | log_bin_index                   |       |
  | log_bin_trust_function_creators | OFF   |
  | log_bin_use_v1_row_events       | OFF   |
  | sql_log_bin                     | ON    |
  +---------------------------------+-------+

log_bin : 用于设定是否启用二进制日志, 由此看是未开启

• 配置文件仍然是在 /etc/my.cnf 中， 修改/etc/my.cnf， 增加日志文件目录：

  log_bin = /tmp/mysql-bin.log

• 重启mysql ：

  bash-3.2# mysql.server start;
  Starting MySQL
  . ERROR! The server quit without updating PID file (/usr/local/Cellar/mysql/5.7.9/data/mysql.pid).

• 又报错，查看错误日志，我的配置在/tmp/mysql.log

  151224 00:37:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
  2015-12-23T16:37:34.643998Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  2015-12-23T16:37:34.644124Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_pISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
  2015-12-23T16:37:34.644129Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
  2015-12-23T16:37:34.644189Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
  2015-12-23T16:37:34.644226Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld (mysqld 5.7.9-log) starting as process 24268 ...
  2015-12-23T16:37:34.646468Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
  2015-12-23T16:37:34.646945Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
  2015-12-23T16:37:34.646978Z 0 [ERROR] Aborting
  
  2015-12-23T16:37:34.646991Z 0 [Note] Binlog end
  2015-12-23T16:37:34.647068Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: Shutdown complete
  
  151224 00:37:34 mysqld_safe mysqld from pid file /usr/local/Cellar/mysql/5.7.9/data/mysql.pid ended

重点：

You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation

说明需要配置一个server-id， 再拿这句话百度，果然是这样。所以在 配置文件/etc/my.cn中添加 server-id = 1，再重启mysql，解决问题。而且在配置的bin-log同级目录增加了mysql-bin.000001 mysql-bin.index mysql-bin.log 三个文件，前两个是自动生成。

参数：

log_bin：设置此参数表示启用binlog功能，并指定路径名称

log_bin_index：设置此参数是指定二进制索引文件的路径与名称

binlog_do_db：此参数表示只记录指定数据库的二进制日志
binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size：此参数表示binlog使用的内存最大的尺寸

binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。binlog_cache_use：使用二进制日志缓存的事务数量

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size：Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog：这个参数直接影响mysql的性能和完整性

sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

• 登录mysql，再次查看bin-log的状态，属于启用状态

  mysql> show variables like "%log_bin%";
  +---------------------------------+----------------------+
  | Variable_name                   | Value                |
  +---------------------------------+----------------------+
  | log_bin                         | ON                   |
  | log_bin_basename                | /tmp/mysql-bin       |
  | log_bin_index                   | /tmp/mysql-bin.index |
  | log_bin_trust_function_creators | OFF                  |
  | log_bin_use_v1_row_events       | OFF                  |
  | sql_log_bin                     | ON                   |
  +---------------------------------+----------------------+

• binlog的删除
  binlog的删除可以手工删除或自动删除

自动删除binlog

通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog

    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       869 |
    +------------------+-----------+
    1 row in set (0.00 sec)

    mysql> show variables like 'expire_logs_days' ;
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 0     |
    +------------------+-------+
    1 row in set (0.00 sec)

    mysql> ;set global expire_logs_days=3;
    ERROR: 
    No query specified

    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like 'expire_logs_days' ;
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 3     |
    +------------------+-------+
    1 row in set (0.00 sec)

手工删除binlog

    mysql> reset master;   //删除master的binlog
    mysql> reset slave;    //删除slave的中继日志
    mysql> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
    mysql> purge master logs to 'mysql-bin.000001';   //删除指定日志文件的日志索引中binlog日志文件

或者直接用操作系统命令直接删除

    mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
    mysql> show master logs; //查看master的binlog日志 
    mysql> show binary logs; //查看master的binlog日志
    mysql> show master status; //用于提供master二进制日志文件的状态信息
    mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

• blog查看：通过mysqlbinlog 查看日志文件

  bash-3.2# mysqlbinlog /tmp/mysql-bin.log

四、Slow Query Log

记录Mysql 慢查询的日志
修改配置文件 /etc/my.cnf

1. Mysql 慢查询配置相关命令：

• 查看日志功能是否开启：show variables like "%slow%";

  mysql> show variables like "%slow%";
  +---------------------------+----------------------------------------------------+
  | Variable_name             | Value                                              |
  +---------------------------+----------------------------------------------------+
  | log_slow_admin_statements | OFF                                                |
  | log_slow_slave_statements | OFF                                                |
  | slow_launch_time          | 2                                                  |
  | slow_query_log            | OFF                                                |
  | slow_query_log_file       | /usr/local/var/mysql/tongkundeMacBook-Pro-slow.log |
  +---------------------------+----------------------------------------------------+

  slow_query_log 配置为OFF ， 说明未开启慢日志

• 打开慢日志功能：set global slow_query_log = on;

  mysql> set global slow_query_log = on;
  Query OK, 0 rows affected (0.06 sec)

• 查看下默认设置的慢查询的时间：show variables like "%long_query%";

  mysql> show variables like "%long_query%";
  +-----------------+-----------+
  | Variable_name   | Value     |
  +-----------------+-----------+
  | long_query_time | 10.000000 |
  +-----------------+-----------+

  可以看出，默认是10秒，按照这个配置，数据得上n亿才能达到，为了测试我们修改一下

2. 修改Mysql配置文件的方式

• 打开/etc/my.cnf , 加入慢查询配置文件

  slow-query-log = 1
  slow-query-log-file = /tmp/mysql-slow.log
  long_query_time = 1 #设置满请求时间, 设置查多少秒的查询算是慢查询

  保存退出后要重启mysql

  mysql.server restart;

• 通过mysql命令查看配置：

  mysql> show variables like "%slow%";
  +---------------------------+---------------------+
  | Variable_name             | Value               |
  +---------------------------+---------------------+
  | log_slow_admin_statements | OFF                 |
  | log_slow_slave_statements | OFF                 |
  | slow_launch_time          | 2                   |
  | slow_query_log            | OFF                 |
  | slow_query_log_file       | /tmp/mysql-slow.log |
  +---------------------------+---------------------+

  这里显示慢日志功能还未开启

• 打开慢日志功能

  mysql> set global slow_query_log = on;
  ERROR 29 (HY000): File '/tmp/mysql-slow.log' not found (Errcode: 13 - Permission denied)

  恩，报错了，说明什么呢，权限不够，那就给权限，退出mysql 执行：

   chown mysql:mysql /tmp/mysql-slow.log

  回到mysql，再次打开慢日志：

  mysql> set global slow_query_log = on;
  Query OK, 0 rows affected (0.00 sec)

  ok, 解决。

3. 测试一下

• 先监控下日志： tail -f /tmp/mysql-slow.log

• 在mysql中分别执行两句查询：

  mysql> SELECT 2;
  +---+
  | 2 |
  +---+
  | 2 |
  +---+
  1 row in set (0.00 sec)
  
  mysql> SELECT sleep(3);
  +----------+
  | sleep(3) |
  +----------+
  |        0 |
  +----------+
  1 row in set (3.01 sec)

• 查看一下日志文件的输出：
  

  # Time: 2015-12-23T15:50:44.140140Z
  # User@Host: root[root] @ localhost []  Id:     2
  # Query_time: 3.003542  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
  SET timestamp=1450885844;
  SELECT sleep(3);

  基本上查询的所有信息都有显示，就不多白花了。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!