# 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)
Comments
Post a Comment