Some basic MySQL commands for system administrators

# HOW TO CREATE A USER IN MYSQL

mysql> CREATE USER 'wordpress_user'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)


# CREATE A DATABASE

mysql> CREATE DATABASE LINUXAVID;
Query OK, 1 row affected (0.00 sec)

# DROP/DELETE A DATABASE

mysql> drop database LINUXAVID;
Query OK, 0 rows affected (0.00 sec)



#CHECK THE PROCESS LIST

root@cpanel [~]# mysqladmin proc
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id    | User      | Host      | db        | Command | Time | State | Info             |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| 46457 | eximstats | localhost | eximstats | Sleep   | 8564 |       |                  |
| 47181 | root      | localhost |           | Query   | 0    |       | show processlist |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+

#CHECK PROCESS LIST STATUS AND EXTENDED STATUS

 mysqladmin processlist extended-status
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id    | User      | Host      | db        | Command | Time | State | Info             |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| 46457 | eximstats | localhost | eximstats | Sleep   | 8657 |       |                  |
| 47186 | root      | localhost |           | Query   | 0    |       | show processlist |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 10       |
| Aborted_connects                  | 27418    |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 6750461  |
| Bytes_sent                        | 14672974 |
| Com_admin_commands                | 3073     |
| 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        |

# CHECK THE STATUS AT 5 SECONDS INTERVAL

root@cpanel [~]# mysqladmin -i 5 proc status
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id    | User      | Host      | db        | Command | Time | State | Info             |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| 46457 | eximstats | localhost | eximstats | Sleep   | 8791 |       |                  |
| 47195 | root      | localhost |           | Query   | 0    |       | show processlist |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
Uptime: 900686  Threads: 2  Questions: 128791  Slow queries: 0  Opens: 59  Flush tables: 1  Open tables: 52  Queries per second avg: 0.142

+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id    | User      | Host      | db        | Command | Time | State | Info             |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| 46457 | eximstats | localhost | eximstats | Sleep   | 8796 |       |                  |
| 47195 | root      | localhost |           | Query   | 0    |       | show processlist |
+-------+-----------+-----------+-----------+---------+------+-------+------------------+
Uptime: 900691  Threads: 2  Questions: 128887  Slow queries: 0  Opens: 59  Flush tables: 1  Open tables: 52  Queries per second avg: 0.143

+-------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id    | User      | Host      | db        | Command | Time | State | Info             |
+-------+-----------+-----------+-----------+---------+------+-------+-------------

# DUMP OR TAKE THE BACKUP OF A DATABASE AS .sql

root@cpanel [~]# mysqldump wordpress > word.sql
root@cpanel [~]# ll word.sql
-rw-r--r-- 1 root root 385228 Jul 24 02:46 word.sql

# IMPORT DATABASE 

$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON 

<dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>'];

REVOKE ALL ON <dbname> FROM <dbuser@localhost>;

CREATE DATABASE <dbname>;a

DROP DATABASE <dbname>;

DROP TABLE <tablename1[, table2, table3...]>;

#TO ACTIVATE THE PERMISSION

FLUSH PRIVILEGES; 

USE <nameOfDatabase>;

SHOW DATABASES;

#SHOW TABLES BEGINNING WITH PREFIX

mysql> show tables like 'wp_use%';( where wp_use is the prefix I have searched)
+-------------------------------+
| Tables_in_wordpress (wp_use%) |
+-------------------------------+
| wp_usermeta                   |
| wp_users                      |
+-------------------------------+
2 rows in set (0.00 sec)
 

SELECT * FROM <nameOfTable>;

DESCRIBE <nameOfTable>;

INSERT INTO <table> <username, password, name1, name2, ...>

            VALUES ('user', password('pass'), 'value1', 'value2' ...);

CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;

INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;

ALTER TABLE <tableOldName> RENAME <tableNewName>;

UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];

HOW TO SEE THE CONNECTION STATUS TO MYSQL
============================================

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 27418 |
| Connections              | 47167 |
| Max_used_connections     | 6     |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 2     |
+--------------------------+-------+
7 rows in set (0.00 sec)