当前位置: 萬仟网 > IT编程>数据库>Mysql > Mysql 数据库常用配置命令

Mysql 数据库常用配置命令

2019年03月14日 07:26  | 萬仟网IT编程  | 我要评论

1.查看mysql数据库默认编码:  

  mysql> show variables like "character%";

+--------------------------+----------------------------+
| variable_name | value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

2.开启mysql数据库慢查询和binlog日志功能

  1)开启慢查询日志,找到my.cnf,添加如下内容 vim /usr/local/mysql/my.cnf

    

  然后重启mysql服务:systemctl restart mysqld

查看慢查询日志是否开启:

mysql> show variables like 'slow_query_log';
+----------------+-------+
| variable_name | value |
+----------------+-------+
| slow_query_log | on |
+----------------+-------+
1 row in set (0.03 sec)

 

mysql> show variables like '%quer%';
+----------------------------------------+---------------------+
| variable_name | value |
+----------------------------------------+---------------------+
| binlog_rows_query_log_events | off |
| ft_query_expansion_limit | 20 |
| have_query_cache | yes |
| log_queries_not_using_indexes | on |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | off |
| query_cache_wlock_invalidate | off |
| query_prealloc_size | 8192 |
| slow_query_log | on |
| slow_query_log_file | /tmp/mysql-slow.log |
+----------------------------------------+---------------------+
15 rows in set (0.00 sec)

如上所示:已经开启了慢查询日志!

注意:如果更改日志目录位置
比如,更改为/home/msyql/log/mysql-slow.log
至少需要将log目录所有者设置为mysql:mysql。
这样重启mysql的时候,才能自动生成mysql-slow.log文件。

如果log目录不是mysql用户权限,将无法生成日志文件,mysql用户无权写入数据。

       2)bin-log日志开启, 必须设置server-id.

    

保存退出,重启mysql服务:systemctl restart mysqld
查看 logbin是否开启:
  

mysql> show variables like "%log_bin%";
+---------------------------------+--------------------------------+
| variable_name | value |
+---------------------------------+--------------------------------+
| log_bin | on |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | off |
| log_bin_use_v1_row_events | off |
| sql_log_bin | on |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

如上所示binlog已经开启!


  

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com