linux中mysql的安装、数据、配置文件路径:
/usr/ :安装地址
/usr/lib/mysql :默认数据库地址,可改动到 /data/mysql(需要同时修改配置文件中[mysqld]参数datadir=/data/mysql、socket=/data/mysql/mysql.sock;如果本机通过socket连接,需要在配置文件中增加[client]参数socket=/data/mysql/mysql.sock)
/usr/my.cnf:配置文件路径,一般移动到/etc/my.cnf路径
数据库中相关查询:
1、查看basedir和datadir
mysql> show global variables like "%dir%";
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /data/mysql/ |
| ignore_db_dirs | |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_tmpdir | |
| innodb_undo_directory | . |
| lc_messages_dir | /usr/share/mysql/ |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
15 rows in set (0.00 sec)
2、慢查询相关
mysql> show global variables like "%query%";
+------------------------------+------------------------+
| Variable_name | Value |
+------------------------------+------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| 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 | /data/mysql/a-slow.log |
+------------------------------+------------------------+
开启慢查询开关,修改慢查询时间:
1、数据库中修改,如果不带global参数,表示在当前session生效,连接断开后失效;如果带global参数表示在当前service生效,服务重启后失效;
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
2、配置文件中修改,在service mysql start后一直生效;
vim /etc/my.cnf
[mysqld]
slow_query_log=on #开启慢查询开关
long_query_time=1 #设置慢查询时间