MySQL quick commands for Oracle DBAs

On 2010年08月30日, in database, by netoearth

How to connect to mysql?

[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 ~]#

Tagged with:  

Comments are closed.