Learning Mysql

$ mysqladmin --user=root --password=the_mysql_root_password variables
+---------------------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------+
| auto_increment_increment | 1 |
| wait_timeout | 28800 From the monitor, you can view a subset of the variables by adding a
LIKE
clause:
mysql> SHOW VARIABLES LIKE 'k%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
+--------------------------+----------+
4 rows inset sec)
The SHOW STATUS
command shows you MySQL server status information:
mysql> SHOW STATUS;
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 8 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 858887090 |
| Bytes_sent | 8535929437 |
| Com_insert | 318046 |
| Com_lock_tables | 126 |
| Com_select | 4541404 |
| Com_unlock_tables | 126 |
| Com_update | 153656 |
| Connections | 238544 |
| Created_tmp_disk_tables | 83154 |
| Created_tmp_files | 47 |
| Created_tmp_tables | 128857 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6119 |
| Key_blocks_used | 6698 |
| Key_read_requests | 45921497 |
| Key_reads | 35348 |
| Key_write_requests | 1612717 |
| Key_writes | 986186 |
| Max_used_connections | 15 |
Checking Server Settings | 389

| Open_files | 128 |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 21 |
| Sort_scan | 212588 |
| Table_locks_immediate | 5831792 |
| Table_locks_waited | 185 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 238543 |
| Threads_running | 1 |
| Uptime | 1786334 |
+----------------------------+------------+
157 rows inset sec)
We’ve omitted most of the rows here for space considerations your instance may well show over 250 variable values.
You can also display the server status using mysqladmin status or mysqladmin extended- status commands mysqladmin --user=root --password=the_mysql_root_password status
Uptime: 12093 Threads 1 Questions 7160 Slow queries 0 Opens Flush tables 1 Open tables 60 Queries per second avg The extended-status command produces the same output as the monitor’s
SHOW
STATUS
command.
The SHOW PROCESSLIST
command displays all running threads on the MySQL server and is a useful tool for diagnosing problems or understanding what users are doing. Try it on your server when you’re logged in as the root user:
mysql> SHOW PROCESSLIST;
+-------+------------+-----------------------------+--------+...
| Id | User | Host | db |...
+-------+------------+-----------------------------+--------+...
| 26533 | moodleuser | zahra.learningmysql.com:63593| Moodle |...
| 26534 | root | localhost | |...
+-------+------------+-----------------------------+--------+...
... +---------+------+-------+------------------+
... | Command | Time | State | Info |
... +---------+------+-------+------------------+
... | Sleep | 1 | | |
... | Query | 0 | | show processlist |
... +---------+------+-------+------------------+
2 rows inset sec)
The output is fairly self-explanatory, and details are in the SHOW syntax section of the MySQL manual. The mysqladmin processlist command produces the same output mysqladmin --user=root --password=the_mysql_root_password processlist
+-------+------------+-----------------------------+--------+...
| Id | User | Host | db |...
+-------+------------+-----------------------------+--------+...

Download 4.24 Mb.

Share with your friends: