配置mysql命令_Mysql 数据库常用配置命令

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已经开启！