MySQL Study案例之--快速了解MySQL服务器

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

1、查看数据库版本

1
2
3
4
5
6
7
mysql> show variables like  'version' ;
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| version       |  5.6 . 4 -m7 |
+---------------+----------+
1  row in set ( 0.00  sec)

2、查看服务器状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> status
--------------
./mysql  Ver  14.14  Distrib  5.6. 4 -m7,  for  Linux (x86_64) using  EditLine wrapper
Connection id:           3
Current database:
Current user:           root@localhost
SSL:                    Not  in  use
Current pager:          stdout
Using outfile:           ''
Using delimiter:        ;
Server version:          5.6. 4 -m7 Source distribution
Protocol version:        10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                  26  min  33  sec
Threads:  1   Questions:  6   Slow queries:  0   Opens:  66   Flush tables:  1   Open tables:  59   Queries per second avg:  0.003
    该列表中主要包括MySQL的版本(为version  5.6. 4 )、运行平台(linux(x86_64))、数据库连接的ID、当前连接用户、以及连接的方式(本次连接采用UNIX socket,而未使用SSL)、服务端的字符集、MySQL运行时间和线程、查询、表的一些参数。

3、查看数据库存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking,  and  foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored  in  memory, useful  for  temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6  rows  in  set ( 0.00  sec)
    MySQL  5.6 默认的引擎为innodb。

4、查看引擎插件     

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> show plugins;
+----------------------------+--------+--------------------+---------+---------+
| Name                       | Status | Type               | Library | License |
+----------------------------+--------+--------------------+---------+---------+
| binlog                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| MyISAM                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INSERTED         | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| partition                  | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+--------+--------------------+---------+---------+
35  rows in set ( 0.01  sec)


5、查看引擎状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
mysql> Show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      | 
=====================================
150206  13 : 21 : 41  INNODB MONITOR OUTPUT
=====================================
Per second averages calculated  from  the  last  32  seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops:  0  srv_active,  0  srv_shutdown,  3076  srv_idle
srv_master_thread log flush  and  writes:  3076
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count  2
OS WAIT ARRAY INFO: signal count  2
Mutex spin waits  0 , rounds  0 , OS waits  0
RW-shared spins  2 , rounds  60 , OS waits  2
RW-excl spins  0 , rounds  0 , OS waits  0
Spin rounds per wait:  0.00  mutex,  30.00  RW-shared,  0.00  RW-excl
------------
TRANSACTIONS
------------
Trx id counter  2817
Purge done  for  trx's n:o <  0  undo n:o <  0
History list length  0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION  0 not  started
MySQL thread id  3 , OS thread handle  0x7f56600f3700 , query id  15  localhost root  init
Show engine innodb status
--------
FILE I/O
--------
I/O thread  0  state: waiting  for  completed aio requests ( insert  buffer thread)
I/O thread  1  state: waiting  for  completed aio requests (log thread)
I/O thread  2  state: waiting  for  completed aio requests (read thread)
I/O thread  3  state: waiting  for  completed aio requests (read thread)
I/O thread  4  state: waiting  for  completed aio requests (read thread)
I/O thread  5  state: waiting  for  completed aio requests (read thread)
I/O thread  6  state: waiting  for  completed aio requests (write thread)
I/O thread  7  state: waiting  for  completed aio requests (write thread)
I/O thread  8  state: waiting  for  completed aio requests (write thread)
I/O thread  9  state: waiting  for  completed aio requests (write thread)
Pending normal aio reads:  0  [ 0 0 0 0 ] , aio writes:  0  [ 0 0 0 0 ] ,
  ibuf aio reads:  0 , log i/o 's: 0, sync i/o' s:  0
Pending flushes (fsync) log:  0 ; buffer pool:  0
156  OS file reads,  5  OS file writes,  5  OS fsyncs
0.00  reads/s,  0  avg bytes/read,  0.00  writes/s,  0.00  fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size  1 , free list len  0 , seg size  2 0  merges
merged operations:
  insert  0 delete  mark  0 delete  0
discarded operations:
  insert  0 delete  mark  0 delete  0
Hash table size  276707 , node heap has  0  buffer(s)
0.00  hash searches/s,  0.00  non-hash searches/s
---
LOG
---
Log sequence number  1602881
Log flushed up to    1602881
Pages flushed up to  1602881
Last checkpoint  at   1602881
0  pending log writes,  0  pending chkp writes
8  log i/o 's done, 0.00 log i/o' s/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated  137363456 in  additional pool allocated  0
Dictionary memory allocated  34906
Buffer pool size    8192
Free buffers        8047
Database pages      145
Old database pages  0
Modified db pages   0
Pending reads  0
Pending writes: LRU  0 , flush list  0  single page  0
Pages made young  0 not  young  0
0.00  youngs/s,  0.00  non-youngs/s
Pages read  145 , created  0 , written  1
0.00  reads/s,  0.00  creates/s,  0.00  writes/s
Buffer pool hit rate  1000  1000 , young-making rate  0  1000  not  0  1000
Pages read ahead  0.00 /s, evicted without access  0.00 /s, Random read ahead  0.00 /s
LRU len:  145 , unzip_LRU len:  0
I/O sum[ 0 ]:cur[ 0 ], unzip sum[ 0 ]:cur[ 0 ]
--------------
ROW OPERATIONS
--------------
0  queries inside InnoDB,  0  queries  in  queue
0  read views open inside InnoDB
Main thread process no.  4728 , id  140008597153536 , state: sleeping
Number  of rows inserted  0 , updated  0 , deleted  0 , read  0
0.00  inserts/s,  0.00  updates/s,  0.00  deletes/s,  0.00  reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
  |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1  row  in  set ( 0.01  sec)

6、查看数据库状态统计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
mysql> show status;
+-------------------------------------------+-------------+
| Variable_name                             | Value       |
+-------------------------------------------+-------------+
| Aborted_clients                           |  0            |
| Aborted_connects                          |  1            |
| Binlog_cache_disk_use                     |  0            |
| Binlog_cache_use                          |  0            |
| Binlog_stmt_cache_disk_use                |  0            |
| Binlog_stmt_cache_use                     |  0            |
| Bytes_received                            |  1333         |
| Bytes_sent                                |  53031        |
| Com_admin_commands                        |  0            |
| Com_assign_to_keycache                    |  0            |
| Com_alter_db                              |  0            |
| Com_alter_db_upgrade                      |  0            |
| Com_alter_event                           |  0            |
| Com_alter_function                        |  0            |
| Com_alter_procedure                       |  0            |
| Com_alter_server                          |  0            |
| Com_alter_table                           |  0            |
| Com_alter_tablespace                      |  0            |
| Com_analyze                               |  0            |
| Com_begin                                 |  0            |
| Com_binlog                                |  0            |
| Com_call_procedure                        |  0            |
| Com_change_db                             |  1            |
| Com_change_master                         |  0            |
| Com_check                                 |  0            |
| Com_checksum                              |  0            |
| Com_commit                                |  0            |
| Com_create_db                             |  0            |
| Com_create_event                          |  0            |
| Com_create_function                       |  0            |
| Com_create_index                          |  0            |
| Com_create_procedure                      |  0            |
| Com_create_server                         |  0            |
| Com_create_table                          |  0            |
| Com_create_trigger                        |  0            |
| Com_create_udf                            |  0            |
| Com_create_user                           |  0            |
| Com_create_view                           |  0            |
| Com_dealloc_sql                           |  0            |
| Com_delete                                |  0            |
| Com_delete_multi                          |  0            |
| Com_do                                    |  0            |
| Com_drop_db                               |  0            |
| Com_drop_event                            |  0            |
| Com_drop_function                         |  0            |
| Com_drop_index                            |  0            |
| Com_drop_procedure                        |  0            |
| Com_drop_server                           |  0            |
| Com_drop_table                            |  0            |
| Com_drop_trigger                          |  0            |
| Com_drop_user                             |  0            |
| Com_drop_view                             |  0            |
| Com_empty_query                           |  0            |
| Com_execute_sql                           |  0            |
| Com_flush                                 |  0            |
| Com_get_diagnostics                       |  0            |
| Com_grant                                 |  0            |
| Com_ha_close                              |  0            |
| Com_ha_open                               |  0            |
| Com_ha_read                               |  0            |
| Com_help                                  |  0            |
| Com_insert                                |  0            |
| Com_insert_select                         |  0            |
| Com_install_plugin                        |  0            |
| Com_kill                                  |  0            |
| Com_load                                  |  0            |
| Com_lock_tables                           |  0            |
| Com_optimize                              |  0            |
| Com_preload_keys                          |  0            |
| Com_prepare_sql                           |  0            |
| Com_purge                                 |  0            |
| Com_purge_before_date                     |  0            |
| Com_release_savepoint                     |  0            |
| Com_rename_table                          |  0            |
| Com_rename_user                           |  0            |
| Com_repair                                |  0            |
| Com_replace                               |  0            |
| Com_replace_select                        |  0            |
| Com_reset                                 |  0            |
| Com_resignal                              |  0            |
| Com_revoke                                |  0            |
| Com_revoke_all                            |  0            |
| Com_rollback                              |  0            |
| Com_rollback_to_savepoint                 |  0            |
| Com_savepoint                             |  0            |
| Com_select                                |  4            |
| Com_set_option                            |  0            |
| Com_signal                                |  0            |
| Com_show_authors                          |  0            |
| Com_show_binlog_events                    |  0            |
| Com_show_binlogs                          |  0            |
| Com_show_charsets                         |  0            |
| Com_show_collations                       |  0            |
| Com_show_contributors                     |  0            |
| Com_show_create_db                        |  0            |
| Com_show_create_event                     |  0            |
| Com_show_create_func                      |  0            |
| Com_show_create_proc                      |  0            |
| Com_show_create_table                     |  0            |
| Com_show_create_trigger                   |  0            |
| Com_show_databases                        |  1            |
| Com_show_engine_logs                      |  0            |
| Com_show_engine_mutex                     |  0            |
| Com_show_engine_status                    |  1            |
| Com_show_events                           |  0            |
| Com_show_errors                           |  0            |
| Com_show_fields                           |  29           |
| Com_show_function_code                    |  0            |
| Com_show_function_status                  |  0            |
| Com_show_grants                           |  0            |
| Com_show_keys                             |  0            |
| Com_show_master_status                    |  2            |
| Com_show_open_tables                      |  0            |
| Com_show_plugins                          |  1            |
| Com_show_privileges                       |  0            |
| Com_show_procedure_code                   |  0            |
| Com_show_procedure_status                 |  1            |
| Com_show_processlist                      |  0            |
| Com_show_profile                          |  0            |
| Com_show_profiles                         |  0            |
| Com_show_relaylog_events                  |  0            |
| Com_show_slave_hosts                      |  0            |
| Com_show_slave_status                     |  1            |
| Com_show_status                           |  2            |
| Com_show_storage_engines                  |  3            |
| Com_show_table_status                     |  1            |
| Com_show_tables                           |  1            |
| Com_show_triggers                         |  0            |
| Com_show_variables                        |  3            |
| Com_show_warnings                         |  0            |
| Com_slave_start                           |  0            |
| Com_slave_stop                            |  0            |
| Com_stmt_close                            |  0            |
| Com_stmt_execute                          |  0            |
| Com_stmt_fetch                            |  0            |
| Com_stmt_prepare                          |  0            |
| Com_stmt_reprepare                        |  0            |
| Com_stmt_reset                            |  0            |
| Com_stmt_send_long_data                   |  0            |
| Com_truncate                              |  0            |
| Com_uninstall_plugin                      |  0            |
| Com_unlock_tables                         |  0            |
| Com_update                                |  0            |
| Com_update_multi                          |  0            |
| Com_xa_commit                             |  0            |
| Com_xa_end                                |  0            |
| Com_xa_prepare                            |  0            |
| Com_xa_recover                            |  0            |
| Com_xa_rollback                           |  0            |
| Com_xa_start                              |  0            |
| Compression                               | OFF         |
| Connections                               |  4            |
| Created_tmp_disk_tables                   |  2            |
| Created_tmp_files                         |  5            |
| Created_tmp_tables                        |  11           |
| Delayed_errors                            |  0            |
| Delayed_insert_threads                    |  0            |
| Delayed_writes                            |  0            |
| Flush_commands                            |  1            |
| Handler_commit                            |  0            |
| Handler_delete                            |  0            |
| Handler_discover                          |  0            |
| Handler_external_lock                     |  2            |
| Handler_mrr_init                          |  0            |
| Handler_prepare                           |  0            |
| Handler_read_first                        |  1            |
| Handler_read_key                          |  2            |
| Handler_read_last                         |  0            |
| Handler_read_next                         |  0            |
| Handler_read_prev                         |  0            |
| Handler_read_rnd                          |  0            |
| Handler_read_rnd_next                     |  515          |
| Handler_rollback                          |  0            |
| Handler_savepoint                         |  0            |
| Handler_savepoint_rollback                |  0            |
| Handler_update                            |  0            |
| Handler_write                             |  504          |
| Innodb_buffer_pool_dump_status            |  not  started |
| Innodb_buffer_pool_load_status            |  not  started |
| Innodb_buffer_pool_pages_data             |  150          |
| Innodb_buffer_pool_pages_dirty            |  0            |
| Innodb_buffer_pool_pages_flushed          |  2            |
| Innodb_buffer_pool_pages_free             |  8042         |
| Innodb_buffer_pool_pages_misc             |  0            |
| Innodb_buffer_pool_pages_total            |  8192         |
| Innodb_buffer_pool_read_ahead_rnd         |  0            |
| Innodb_buffer_pool_read_ahead             |  0            |
| Innodb_buffer_pool_read_ahead_evicted     |  0            |
| Innodb_buffer_pool_read_requests          |  3750         |
| Innodb_buffer_pool_reads                  |  151          |
| Innodb_buffer_pool_wait_free              |  0            |
| Innodb_buffer_pool_write_requests         |  1            |
| Innodb_data_fsyncs                        |  5            |
| Innodb_data_pending_fsyncs                |  0            |
| Innodb_data_pending_reads                 |  0            |
| Innodb_data_pending_writes                |  0            |
| Innodb_data_read                          |  4657152      |
| Innodb_data_reads                         |  161          |
| Innodb_data_writes                        |  5            |
| Innodb_data_written                       |  34304        |
| Innodb_dblwr_pages_written                |  1            |
| Innodb_dblwr_writes                       |  1            |
| Innodb_have_atomic_builtins               | ON          |
| Innodb_log_waits                          |  0            |
| Innodb_log_write_requests                 |  0            |
| Innodb_log_writes                         |  1            |
| Innodb_os_log_fsyncs                      |  3            |
| Innodb_os_log_pending_fsyncs              |  0            |
| Innodb_os_log_pending_writes              |  0            |
| Innodb_os_log_written                     |  512          |
| Innodb_page_size                          |  16384        |
| Innodb_pages_created                      |  0            |
| Innodb_pages_read                         |  150          |
| Innodb_pages_written                      |  1            |
| Innodb_row_lock_current_waits             |  0            |
| Innodb_row_lock_time                      |  0            |
| Innodb_row_lock_time_avg                  |  0            |
| Innodb_row_lock_time_max                  |  0            |
| Innodb_row_lock_waits                     |  0            |
| Innodb_rows_deleted                       |  0            |
| Innodb_rows_inserted                      |  0            |
| Innodb_rows_read                          |  0            |
| Innodb_rows_updated                       |  0            |
| Innodb_num_open_files                     |  3            |
| Innodb_truncated_status_writes            |  0            |
| Key_blocks_not_flushed                    |  0            |
| Key_blocks_unused                         |  6698         |
| Key_blocks_used                           |  0            |
| Key_read_requests                         |  0            |
| Key_reads                                 |  0            |
| Key_write_requests                        |  0            |
| Key_writes                                |  0            |
| Last_query_cost                           |  10.499000    |
| Max_used_connections                      |  1            |
| Not_flushed_delayed_rows                  |  0            |
| Open_files                                |  54           |
| Open_streams                              |  0            |
| Open_table_definitions                    |  77           |
| Open_tables                               |  77           |
| Opened_files                              |  195          |
| Opened_table_definitions                  |  11           |
| Opened_tables                             |  18           |
| Performance_schema_accounts_lost          |  0            |
| Performance_schema_cond_classes_lost      |  0            |
| Performance_schema_cond_instances_lost    |  0            |
| Performance_schema_file_classes_lost      |  0            |
| Performance_schema_file_handles_lost      |  0            |
| Performance_schema_file_instances_lost    |  0            |
| Performance_schema_hosts_lost             |  0            |
| Performance_schema_locker_lost            |  0            |
| Performance_schema_mutex_classes_lost     |  0            |
| Performance_schema_mutex_instances_lost   |  0            |
| Performance_schema_rwlock_classes_lost    |  0            |
| Performance_schema_rwlock_instances_lost  |  0            |
| Performance_schema_socket_classes_lost    |  0            |
| Performance_schema_socket_instances_lost  |  0            |
| Performance_schema_stage_classes_lost     |  0            |
| Performance_schema_statement_classes_lost |  0            |
| Performance_schema_table_handles_lost     |  0            |
| Performance_schema_table_instances_lost   |  0            |
| Performance_schema_thread_classes_lost    |  0            |
| Performance_schema_thread_instances_lost  |  0            |
| Performance_schema_users_lost             |  0            |
| Prepared_stmt_count                       |  0            |
| Qcache_free_blocks                        |  0            |
| Qcache_free_memory                        |  0            |
| Qcache_hits                               |  0            |
| Qcache_inserts                            |  0            |
| Qcache_lowmem_prunes                      |  0            |
| Qcache_not_cached                         |  0            |
| Qcache_queries_in_cache                   |  0            |
| Qcache_total_blocks                       |  0            |
| Queries                                   |  56           |
| Questions                                 |  54           |
| Select_full_join                          |  0            |
| Select_full_range_join                    |  0            |
| Select_range                              |  0            |
| Select_range_check                        |  0            |
| Select_scan                               |  11           |
| Slave_heartbeat_period                    |  0.000        |
| Slave_last_heartbeat                      |             |
| Slave_open_temp_tables                    |  0            |
| Slave_received_heartbeats                 |  0            |
| Slave_retried_transactions                |  0            |
| Slave_running                             | OFF         |
| Slow_launch_threads                       |  0            |
| Slow_queries                              |  0            |
| Sort_merge_passes                         |  0            |
| Sort_range                                |  0            |
| Sort_rows                                 |  0            |
| Sort_scan                                 |  0            |
| Table_locks_immediate                     |  70           |
| Table_locks_waited                        |  0            |
| Tc_log_max_pages_used                     |  0            |
| Tc_log_page_size                          |  0            |
| Tc_log_page_waits                         |  0            |
| Threads_cached                            |  0            |
| Threads_connected                         |  1            |
| Threads_created                           |  3            |
| Threads_running                           |  1            |
| Uptime                                    |  3221         |
| Uptime_since_flush_status                 |  3221         |
+-------------------------------------------+-------------+
302  rows  in  set ( 0.01  sec)

7、查看数据库参数

1
2
3
4
5
6
7
8
9
10
11
mysql> show variables;
......
| tx_isolation                                           | REPEATABLE-READ                                                                                                                                                                                                         |
| unique_checks                                          | ON                                                                                                                                                                                                                      |
| updatable_views_with_limit                             | YES                                                                                                                                                                                                                     |
| version                                                |  5.6. 4 -m7                                                                                                                                                                                                                |
| version_comment                                        | Source distribution                                                                                                                                                                                                     |
| version_compile_machine                                | x86_64                                                                                                                                                                                                                  |
| version_compile_os                                     | Linux                                                                                                                                                                                                                   |
| wait_timeout                                           |  28800                                                                                                                                                                                                                    |
| warning_count                                          |  0                     









本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1612448,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
234
分享
相关文章
服务器数据恢复—服务器raid5阵列中2块硬盘掉线的数据恢复案例
某公司一台服务器,服务器上有一组由8块硬盘组建的raid5磁盘阵列。 磁盘阵列中2块硬盘的指示灯显示异常,其他硬盘指示灯显示正常。上层应用不可用。
Nginx伪流媒体服务器搭建详细说明以及案例
Nginx伪流媒体服务器搭建步骤如下:1. 安装Nginx,根据系统选择命令;2. 编辑配置文件(/etc/nginx/nginx.conf),添加mp4相关设置;3. 创建视频目录/usr/share/nginx/html/videos并上传视频;4. 重启Nginx应用更改;5. 通过浏览器访问视频,如http://your_server_ip/videos/example.mp4。注意启用mp4模块,确保视频格式支持伪流媒体播放。
服务器数据恢复—服务器raid5阵列中硬盘离线的数据恢复案例
某公司一台服务器中有一组多块硬盘组成的磁盘阵列。磁盘阵列中有2块硬盘出现故障离线,服务器崩溃,上层数据丢失。
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
70 25
服务器数据恢复—服务器raid5阵列硬盘出现坏道掉线的数据恢复案例
一台服务器中有一组由16块SAS接口的硬盘组建的raid5阵列。 服务器磁盘阵列中有2块硬盘离线,服务器上跑的应用崩溃。 经过后续的分析发现丢失的数据为虚拟机文件,包含4个卷的数据。
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
如何解决 MySQL 数据库服务器 CPU 飙升的情况
大家好,我是 V 哥。当 MySQL 数据库服务器 CPU 飙升时,如何快速定位和解决问题至关重要。本文整理了一套实用的排查和优化套路,包括使用系统监控工具、分析慢查询日志、优化 SQL 查询、调整 MySQL 配置参数、优化数据库架构及检查硬件资源等步骤。通过一个电商业务系统的案例,详细展示了从问题发现到解决的全过程,帮助你有效降低 CPU 使用率,提升系统性能。关注 V 哥,掌握更多技术干货。
102 0
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
175 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等