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已經開啟！