下载地址，选择相应的版本来进行安装测试
http://dev.mysql.com/doc/index-other.html
相关说明
http://dev.mysql.com/doc/sakila/en/sakila-installation.html
outous-MacBook-Pro:~ wangtao$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.20 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.20 |
+-----------+
1 row in set (0.00 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| apphomebiz |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.01 sec) mysql> source /Users/wangtao/Downloads/sakila-db/sakila-schema.sql
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed
Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec) mysql> source /Users/wangtao/Downloads/sakila-db/sakila-data.sql
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed
Query OK, 0 rows affected (0.00 sec) Query OK, 200 rows affected (0.00 sec)
Records: 200 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 603 rows affected (0.00 sec)
Records: 603 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 600 rows affected (0.00 sec)
Records: 600 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 109 rows affected (0.00 sec)
Records: 109 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 599 rows affected (0.02 sec)
Records: 599 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1000 rows affected (0.13 sec)
Records: 1000 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 5462 rows affected (0.04 sec)
Records: 5462 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1000 rows affected (0.01 sec)
Records: 1000 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 4581 rows affected (0.04 sec)
Records: 4581 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 14885 rows affected (0.24 sec)
Records: 14885 Duplicates: 0 Warnings: 0 Query OK, 1164 rows affected (0.01 sec)
Records: 1164 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 12377 rows affected (0.21 sec)
Records: 12377 Duplicates: 0 Warnings: 0 Query OK, 3667 rows affected (0.05 sec)
Records: 3667 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> use sakila;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec) mysql> select * from store;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec) mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec) mysql> select count(*) from film_text;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec) mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec) mysql> show variables like '%log%';
+-----------------------------------------+----------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/outous-MacBook-Pro.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/outous-MacBook-Pro.local.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| 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_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+----------------------------------------------------+
58 rows in set (0.00 sec) mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%log%';
+-----------------------------------------+----------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/outous-MacBook-Pro.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/outous-MacBook-Pro.local.err |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| 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_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------------------------+----------------------------------------------------+
58 rows in set (0.00 sec)
#生产环境中，这个时间设置的过短的话，短时间内会产生大量的日志文件，可能造成磁盘写满
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec) mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec) mysql> use sakila;
Database changed
mysql> select * from store limit 10;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.01 sec) mysql> show variables linke "slow%";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'linke "slow%"' at line 1
mysql> show variables like "slow%";
+---------------------+---------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log |
+---------------------+---------------------------------------------------+
3 rows in set (0.00 sec) mysql>
查看保存的日志
/usr/local/mysql/data/outous-MacBook-Pro-slow.log
outous-MacBook-Pro:~ wangtao$ sudo cat /usr/local/mysql/data/outous-MacBook-Pro-slow.log
Password:
/usr/local/mysql/bin/mysqld, Version: 5.6.20 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 141217 22:41:51
#执行sql的主机信息
# User@Host: root[root] @ localhost [] Id: 16
#sql的执行信息，扫描的行数，发送的行数
# Query_time: 0.000181 Lock_time: 0.000072 Rows_sent: 2 Rows_examined: 2
use sakila;
#sql执行时间，时间戳格式
SET timestamp=1418827311;
#sql内容
select * from store limit 10;
outous-MacBook-Pro:~ wangtao$