b.sqlprofi,mysql-expain-slow-log,mysqllogfilter：分析日志
二、實踐：
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table emp(
-> id int(11),
-> info varchar(20)
-> ) engine = innnodb charset = utf8;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> insert into emp values(1,'z1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp values(1,'z2');
Query OK, 1 row affected (0.00 sec)
mysql> exit
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.5.44-log Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show global variables like '%log%';
+-----------------------------------------+---------------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 14 |
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/ubuntu.log |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 67108864 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /usr/local/mysql/data/mysql-error.log |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+---------------------------------------+
41 rows in set (0.02 sec)
-> Ctrl-C -- exit!
Aborted
abc@ubuntu:~/Downloads/mysql$ mysqld --verbose --help | grep -A 1 'Default options'
151106 15:37:14 [Warning] option 'table_definition_cache': unsigned value 100 adjusted to 400
151106 15:37:14 [Note] mysqld (mysqld 5.5.44-log) starting as process 76330 ...
151106 15:37:14 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106 15:37:14 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106 15:37:14 [Warning] One can only use the --user switch if running as root
mysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
151106 15:37:14 [ERROR] Aborting
abc@ubuntu:~/Downloads/mysql$ mysqld --verbose --help | grep -A 1 'Default options'
151106 15:37:32 [Warning] option 'table_definition_cache': unsigned value 100 adjusted to 400
151106 15:37:32 [Note] mysqld (mysqld 5.5.44-log) starting as process 76335 ...
151106 15:37:32 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106 15:37:32 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106 15:37:32 [Warning] One can only use the --user switch if running as root
mysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
151106 15:37:32 [ERROR] Aborting
//以下文件就是mysql的本機上的配置文件
abc@ubuntu:~/Downloads/mysql$ vi /usr/local/mysql/etc/my.cnf
mysql> select count(*) from payment p left join customer c on p.payment_id = c.customer_id;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.06 sec)
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> set long_query_time = 0.05;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.050000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> select count(*) from payment p left join customer c on p.payment_id = c.customer_id order by c.customer_id;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.07 sec)
mysql> Ctrl-C -- exit!
Aborted
abc@ubuntu:/usr/local/mysql/data$ sudo tail ./mysql-slow.log
[sudo] password for abc:
# Time: 151106 23:40:39
# User@Host: root[root] @ localhost []
# Query_time: 0.047216 Lock_time: 0.000099 Rows_sent: 1 Rows_examined: 16650
SET timestamp=1446882039;
select count(*) from payment p left join customer c on p.payment_id = c.customer_id;
# Time: 151106 23:41:02
# User@Host: root[root] @ localhost []
# Query_time: 0.075027 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 32699
SET timestamp=1446882062;
select count(*) from payment p left join customer c on p.payment_id = c.customer_id order by c.customer_id;
