[root@vmxdb01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
[root@vmxdb01 ~]# mysql -u donghua -pora123 -h 192.168.1.21
Welcome to the MySQL monitor. Commands end with ; or \g.
How to disconnect from mysql?
mysql> exit
Bye
Do I need to commit after DML?
It’s good to commit, but by default, auto_commit=1, which means it will command after each statement.
How to list databases (database = schema)?
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
How to connect to a database?
mysql> use hr;
Database changed
How to list tables inside a database?
mysql> show tables;
+--------------+
| Tables_in_hr |
+--------------+
| employees |
+--------------+
1 row in set (0.00 sec)
How to list tables from another database?
mysql> show tables from information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)
How to describe a table?
mysql> desc hr.employees;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int(11) | YES | | NULL | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
How to retrieve the DDL for a table?
mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`employee_id` int(11) default NULL,
`first_name` varchar(20) default NULL,
`last_name` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
How to retrieve list of indexes of a table?
mysql> show index from employees;
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees | 1 | employees_pk | 1 | employee_id | A | NULL | NULL | NULL | YES | BTREE | |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
How to create a database?
mysql> create database hr;
Query OK, 1 row affected (0.00 sec)
How to drop a database?
mysql> drop database hr;
Query OK, 0 rows affected (0.00 sec)
How to create a table?
mysql> create table employees (employee_id integer, first_name varchar(20), last_name varchar(20)) type=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)
How to drop a table?
mysql> drop table employees;
Query OK, 0 rows affected (0.00 sec)
How to create an index?
mysql> create index employees_pk on employees (employee_id);
How to create a user?
mysql> create user 'donghua'@ '192.168.1.%' identified by 'ora123';
Query OK, 0 rows affected (0.00 sec)
How to change password for a user?
mysql> set password for 'donghua'@'192.168.1.%' = password('ora123');
Query OK, 0 rows affected (0.00 sec)
How to grant privilege for a user?
mysql> grant select on hr.* to 'donghua'@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)
How to drop a user?
mysql> drop user 'donghua'@'172.168.1.%';
Query OK, 0 rows affected (0.01 sec)
How to create a readonly user in single command?
mysql> grant select on hr.* to 'donghua'@'172.168.1.%' identified by 'ora123';
Query OK, 0 rows affected (0.01 sec)
How to check a user’s privilege?
mysql> show grants for 'donghua'@'192.168.1.%';
+---------------------------------------------------+
| Grants for donghua@192.168.1.% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'donghua'@'192.168.1.%' |
| GRANT SELECT ON `hr`.* TO 'donghua'@'192.168.1.%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
How to retrieve list of users?
mysql> select Host,user from mysql.user;
+-----------+------+
| Host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
How to monitor mysql status?
mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 7 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 200 |
| Threads_running | 1 |
| Uptime | 2059 |
| Uptime_since_flush_status | 92 |
+-----------------------------------+----------+
249 rows in set (0.01 sec)
mysql> show status like '%conn%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_connects | 7 |
| Connections | 24 |
| Max_used_connections | 1 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+--------------------------+-------+
7 rows in set (0.00 sec)
How to monitor thread status? (Similar to v$process & v$session)
mysql> show processlist;
+----+---------+--------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+--------------------------------+------+---------+------+-------+------------------+
| 23 | donghua | vmxdb01.lab.dbaglobe.com:22931 | NULL | Query | 0 | NULL | show processlist |
+----+---------+--------------------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show full processlist;
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
| 23 | donghua | vmxdb01.lab.dbaglobe.com:22931 | NULL | Query | 0 | NULL | show full processlist |
+----+---------+--------------------------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
How to kill a process (based on ID retrieved from “show processlist”)?
mysql> kill 25;
Query OK, 0 rows affected (0.00 sec)
How to “spool” the output to a file?
mysql> tee output.txt
Logging to file 'output.txt'
mysql> select * from hr.employees;
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 1 | donghua | luo |
+-------------+------------+-----------+
1 row in set (0.00 sec)
mysql> notee
Outfile disabled.
How to execute OS command inside mysql command prompt?
mysql> system date
Wed Jul 28 22:50:01 SGT 2010
mysql> \! date
Wed Jul 28 22:50:04 SGT 2010
How to run a SQL file inside mysql command prompt?
mysql> source test.sql
mysql> \. test.sql
How to cancel a partial finished SQL statement?
mysql> select
-> \c
mysql>
How to retrieve your session status?
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1
Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.77 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 8 min 22 sec
Threads: 2 Questions: 37 Slow queries: 0 Opens: 24 Flush tables: 1 Open tables: 18 Queries per second avg: 0.074
--------------
How to dump data into a text file?
mysql> select * from hr.employees into outfile '/tmp/employees.txt';
Query OK, 1 row affected (0.00 sec)
How to load data from text file into table?
mysql> load data infile '/tmp/employees.txt' into table hr.employees;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
How to list global variables?
mysql> show global variables;
How to list session variables?
mysql> show session variables;
How to retrieve on 1 row (or n rows) from a table?
mysql> select * from hr.employees limit 1;
+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 1 | donghua | luo |
+-------------+------------+-----------+
1 row in set (0.00 sec)
How to turn on query log and slow query log?
[root@vmxdb01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-slow-queries=/var/log/slow-query-mysqld.log
log=/var/log/mysql_query.log
long_query_time=10
[root@vmxdb01 ~]# touch /var/log/slow-query-mysqld.log
[root@vmxdb01 ~]# touch /var/log/mysql_query.log
[root@vmxdb01 ~]# chown mysql:mysql /var/log/slow-query-mysqld.log
[root@vmxdb01 ~]# chown mysql:mysql /var/log/mysql_query.log
[root@vmxdb01 ~]# service mysqld restart
How to use escape character?
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> select '\\',"\\";
+---+---+
| \ | \ |
+---+---+
| \ | \ |
+---+---+
1 row in set (0.00 sec)
Performance: How to retrieve explain plan?
mysql> explain select * from employees where employee_id=1;
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | employees_pk | employees_pk | 5 | const | 1 | |
+----+-------------+-----------+-------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from employees;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
How to start/stop mysql in standalone environment?
[root@vmxdb01 ~]# service mysqld status
mysqld (pid 19167) is running...
[root@vmxdb01 ~]# service mysqld stop
Stopping MySQL: [ OK ]
[root@vmxdb01 ~]# service mysqld status
mysqld is stopped
[root@vmxdb01 ~]# service mysqld start
Starting MySQL: [ OK ]
[root@vmxdb01 ~]#
No comments:
Post a Comment