In the last chapter of MySQL tutorial, we have successfully installed the MySQL database along with tools such as MySQL workbench, MySQL shell, MySQL 5.7 command line client, etc. In this chapter, we are going to learn various MySQL administration commands for which we are going to use MySQL 5.7 command line client. To start the MySQL 5.7 command line client, simply click on the desktop icon or open it from the Window’s program Menu. Once MySQL 5.7 command line client has opened, you can see the following command line window. You need to enter the root password (i.e. mysqldb), which we entered during the installation of the MySQL database and its products. It should be noted that the ‘root’ user for MySQL database has full database privileges and can act as DBA i.e. Database Administrator.
MySQL Administrations
The following are most frequently used MySQL administration commands.
1. MySQL User Table description and add a new user: – You can add a new user into MySQL database by simply adding a new entry into the user table present in the MySQL database through SQL insert statement. User table in MySQL has multiple columns (around 45 columns). You can view these columns after executing the following SQL command. To create a new user, you have to prepare an insert SQL statement after entering 45 values in the user table columns for a new user creation.
|
mysql> use mysql; Database changed mysql> describe user; +————————+———————————–+——+—–+———————–+——-+ | Field | Type | Null | Key | Default | Extra | +————————+———————————–+——+—–+———————–+——-+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum(‘N’,’Y’) | NO | | N | | | Insert_priv | enum(‘N’,’Y’) | NO | | N | | | Update_priv | enum(‘N’,’Y’) | NO | | N | | | Delete_priv | enum(‘N’,’Y’) | NO | | N | | | Create_priv | enum(‘N’,’Y’) | NO | | N | | | Drop_priv | enum(‘N’,’Y’) | NO | | N | | | Reload_priv | enum(‘N’,’Y’) | NO | | N | | | Shutdown_priv | enum(‘N’,’Y’) | NO | | N | | | Process_priv | enum(‘N’,’Y’) | NO | | N | | | File_priv | enum(‘N’,’Y’) | NO | | N | | | Grant_priv | enum(‘N’,’Y’) | NO | | N | | | References_priv | enum(‘N’,’Y’) | NO | | N | | | Index_priv | enum(‘N’,’Y’) | NO | | N | | | Alter_priv | enum(‘N’,’Y’) | NO | | N | | | Show_db_priv | enum(‘N’,’Y’) | NO | | N | | | Super_priv | enum(‘N’,’Y’) | NO | | N | | | Create_tmp_table_priv | enum(‘N’,’Y’) | NO | | N | | | Lock_tables_priv | enum(‘N’,’Y’) | NO | | N | | | Execute_priv | enum(‘N’,’Y’) | NO | | N | | | Repl_slave_priv | enum(‘N’,’Y’) | NO | | N | | | Repl_client_priv | enum(‘N’,’Y’) | NO | | N | | | Create_view_priv | enum(‘N’,’Y’) | NO | | N | | | Show_view_priv | enum(‘N’,’Y’) | NO | | N | | | Create_routine_priv | enum(‘N’,’Y’) | NO | | N | | | Alter_routine_priv | enum(‘N’,’Y’) | NO | | N | | | Create_user_priv | enum(‘N’,’Y’) | NO | | N | | | Event_priv | enum(‘N’,’Y’) | NO | | N | | | Trigger_priv | enum(‘N’,’Y’) | NO | | N | | | Create_tablespace_priv | enum(‘N’,’Y’) | NO | | N | | | ssl_type | enum(”,’ANY’,’X509′,’SPECIFIED’) | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | NO | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum(‘N’,’Y’) | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint(5) unsigned | YES | | NULL | | | account_locked | enum(‘N’,’Y’) | NO | | N | | +————————+———————————–+——+—–+———————–+——-+ 45 rows in set (0.00 sec) |
2. MySQL User Table details retrieval: You can retrieve the user details present in the table along with the label of access assigned to these users. In the following SQL, we going to retrieve details such as Host, username, account locked, select privilege, and delete privilege for all the users present in the user table.
|
mysql> select Host, user, account_locked, select_priv, delete_priv from user; +———–+—————+—————-+————-+————-+ | Host | user | account_locked | select_priv | delete_priv | +———–+—————+—————-+————-+————-+ | localhost | root | N | Y | Y | | localhost | mysql.session | Y | N | N | | localhost | mysql.sys | Y | N | N | +———–+—————+—————-+————-+————-+ 3 rows in set (0.00 sec) |
Points to Remember
-
When you add a new user, always remember to encrypt the password through the MySQL inbuilt PASSWORD () function. This will keep your password secured.
-
When working with the MySQL command line or work bench, it is mandatory to terminate a SQL command with a semi colon (;) at the end, otherwise the SQL statement won’t be executed.
-
We always need to execute the FLUSH PRIVILEGES statement after any insert to USER table. Such statement informs the server to reload the grant tables. It should be noted that without using this statement, you won’t be able to connect to MySQL database at all by using the new user account credentials. However, you may access that account after the server reboot.
-
You can grant or revoke privileges to any user after setting the flags to the user table columns as shown below. If flag is set to ‘Y’ then access has been granted otherwise, if it is set to ‘N’ then access has been revoked.
|
User table privilege columns |
|
Select_priv |
|
Insert_priv |
|
Update_priv |
|
Delete_priv |
|
Create_priv |
|
Drop_priv |
|
Reload_priv |
|
Shutdown_priv |
|
Process_priv |
|
File_priv |
|
Grant_priv |
|
References_priv |
|
Index_priv |
|
Alter_priv |
|
Show_db_priv |
|
Super_priv |
|
Create_tmp_table_priv |
|
Lock_tables_priv |
|
Execute_priv |
|
Repl_slave_priv |
|
Repl_client_priv |
|
Create_view_priv |
|
Show_view_priv |
|
Create_routine_priv |
|
Alter_routine_priv |
|
Create_user_priv |
|
Event_priv |
|
Trigger_priv |
|
Create_tablespace_priv |
Other Administrative MySQL Commands
The following are the frequently used MySQL administrative commands.
|
S No. |
Command |
Function |
|
1. |
USE Databasename |
This command is used to select a database from the MySQL work area. |
|
2. |
SHOW DATABASES |
This command is used to list out the databases which are accessible by the MySQL DBMS on the console. |
|
3. |
SHOW TABLES |
This command is used to list out the tables present in the current database which has been selected by using the use command. |
|
4. |
SHOW COLUMNS FROM table name |
This command is used to list the attributes, attributes types, key information, NULL permitted or not, defaults, etc. information about the table. |
|
5. |
SHOW INDEX FROM table name |
This command is used to show all indexes details applied on the table, which includes the PRIMARY KEY as well. |
|
6. |
SHOW TABLE STATUS LIKE table name\G |
This command is used to report about the MySQL DBMS performance details and other statistics. |
|
7. |
DESCRIBE table name |
This command is used to list down the DDL structure of the table displaying columns and each column data type. |
Conclusion
In this chapter, we have covered various MySQL administration tasks such as user creation, retrieving user details and assigning roles and privileges to the user through MySQL command line. Later in the chapter, we have covered other frequently used MySQL administrative commands.
