author | Oleksandr Gavenko <gavenkoa@gmail.com> |
Wed, 30 Dec 2015 01:20:37 +0200 | |
changeset 1850 | 84707c692266 |
parent 1849 | a3b639d803e7 |
child 1851 | e6ffc5f7200e |
permissions | -rw-r--r-- |
1809 | 1 |
.. -*- coding: utf-8; -*- |
2 |
.. include:: HEADER.rst |
|
3 |
||
4 |
================ |
|
5 |
MySQL/MariaBD. |
|
6 |
================ |
|
7 |
.. contents:: |
|
8 |
||
9 |
SQL clients. |
|
10 |
============ |
|
11 |
||
1810 | 12 |
https://mariadb.com/kb/en/mariadb/clients-and-utilities/ |
13 |
Clients and Utilities. |
|
14 |
https://mariadb.com/kb/en/mariadb/graphical-and-enhanced-clients/ |
|
15 |
Graphical and Enhanced Clients. |
|
16 |
||
1809 | 17 |
mysql. |
18 |
------ |
|
19 |
||
20 |
Official client. Install ``mariadb-client`` package - ``mysql`` utility |
|
21 |
will be installed with dependent package. |
|
22 |
||
23 |
Usage:: |
|
24 |
||
25 |
$ mysql -u $USER -p |
|
26 |
Enter password: <== Type password. |
|
27 |
||
28 |
mycli. |
|
29 |
------ |
|
30 |
||
31 |
Python command line client with completion. Usage:: |
|
32 |
||
33 |
$ mycli -u $USER |
|
34 |
Password: <== Type password. |
|
35 |
||
1811
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
36 |
List databases, tables, columns. |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
37 |
================================ |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
38 |
:: |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
39 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
40 |
$ mysql -u $USER -p |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
41 |
Enter password: <== Type password. |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
42 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
43 |
SHOW DATABASES; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
44 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
45 |
USE mysql; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
46 |
MariaDB [mysql]> SHOW TABLES; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
47 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
48 |
MariaDB [mysql]> SHOW COLUMNS FROM user; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
49 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
50 |
MariaDB [mysql]> DESCRIBE user; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
51 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
52 |
MariaDB [mysql]> SHOW STATUS; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
53 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
54 |
MariaDB [mysql]> SHOW CREATE DATABASE mysql; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
55 |
MariaDB [mysql]> SHOW CREATE TABLE user; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
56 |
|
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
57 |
MariaDB [mysql]> SHOW GRANTS; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
58 |
|
1849
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
59 |
System information. |
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
60 |
=================== |
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
61 |
|
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
62 |
Server version, platform, etc:: |
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
63 |
|
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
64 |
[mysql]> SHOW VARIABLES LIKE "%version%"; |
a3b639d803e7
System information.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1836
diff
changeset
|
65 |
|
1850
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
66 |
Debugging queries error. |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
67 |
======================== |
1836 | 68 |
|
69 |
After error or warning run:: |
|
70 |
||
1811
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
71 |
MariaDB [mysql]> SHOW WARNINGS; |
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
72 |
MariaDB [mysql]> SHOW ERRORS; |
1836 | 73 |
MariaDB [mysql]> show engine innodb status; |
1811
072cc8a704ce
List databases, tables, columns.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1810
diff
changeset
|
74 |
|
1850
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
75 |
Monitor server execution. |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
76 |
========================= |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
77 |
:: |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
78 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
79 |
MariaDB [mysql]> SHOW PROCESSLIST; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
80 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
81 |
Profiling queries. |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
82 |
================== |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
83 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
84 |
Enable profiling, execute query and review profiling data:: |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
85 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
86 |
[mysql]> set profiling=1; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
87 |
[mysql]> select 1; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
88 |
[mysql]> show profile; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
89 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
90 |
Each profiled query results stored separately. To get list of results:: |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
91 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
92 |
[mysql]> show profiles; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
93 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
94 |
To retrieve selected profiling data:: |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
95 |
|
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
96 |
[mysql]> show profile for query 1; |
84707c692266
Monitor server execution. Profiling queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1849
diff
changeset
|
97 |