MySQL Management

Starting and Shutting Down MySQL Server

On Windows Systems

On Windows systems, open the Command Prompt (cmd) and navigate to the bin directory of your MySQL installation.

To start:

cd c:/mysql/bin
mysqld --console

To stop:

cd c:/mysql/bin
mysqladmin -uroot shutdown

On Linux Systems

First, we need to check if the MySQL server is running with the following command:

ps -ef | grep mysqld

If MySQL is already running, the above command will output the MySQL process list. If MySQL is not running, you can start the MySQL server with the following command:

root@host# cd /usr/bin
./mysqld_safe &

To shut down the currently running MySQL server, you can execute the following command:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

MySQL User Setup

If you need to add a MySQL user, you simply need to add a new user in the user table of the mysql database.

Below is an example of adding a user named guest with the password guest123, and granting the user SELECT, INSERT, and UPDATE privileges:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

When adding a user, make sure to use the PASSWORD() function provided by MySQL to encrypt the password. As you can see in the example, the encrypted password is: 6f8c114b58f2ce9e.

Note: In MySQL 5.7, the password column in the user table has been replaced with authentication_string.

Note: The PASSWORD() encryption function has been removed in 8.0.11. You can use the MD5() function instead.

Note: It is necessary to execute the FLUSH PRIVILEGES statement. This command reloads the grant tables.

If you do not use this command, you will not be able to connect to the MySQL server using the newly created user unless you restart the MySQL server.

You can specify privileges for the user when creating them by setting the corresponding privilege columns to 'Y' in the INSERT statement. The list of user privileges includes:

• 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

Another method to add a user is by using the SQL GRANT command. The following command will add a user named zara with the password zara123 to the specified database TUTORIALS.

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

The above command will create a user record in the user table of the mysql database.

Note: MySQL SQL statements end with a semicolon (;).

/etc/my.cnf File Configuration

Typically, you do not need to modify this configuration file, as it comes with the following default settings:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

In the configuration file, you can specify different directories for storing error log files, although you generally do not need to change these settings.

MySQL Management Commands

Below are some commonly used commands in MySQL database management:

• USE:

  mysql> use tutorialpro;
  Database changed
  

• SHOW DATABASES:

  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | tutorialpro             |
  | cdcol              |
  | mysql              |
  | onethink           |
  | performance_schema |
  | phpmyadmin         |
  | test               |
  | wecenter           |
  | wordpress          |
  +--------------------+
  10 rows in set (0.02 sec)
  

• SHOW TABLES:

  mysql> use tutorialpro;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_tutorialpro |
  +------------------+
  | employee_tbl     |
  | tutorialpro_tbl       |
  | tcount_tbl       |
  +------------------+
  3 rows in set (0.00 sec)
  

• SHOW COLUMNS FROM:

  mysql> SHOW COLUMNS FROM tutorialpro_tbl;
  +-----------------+--------------+------+-----+---------+-------+
  | Field           | Type         | Null | Key | Default | Extra |
  +-----------------+--------------+------+-----+---------+-------+
  | tutorialpro_id       | int(11)      | NO   | PRI | NULL    |       |
  | tutorialpro_title    | varchar(255) | YES  |     | NULL    |       |
  | tutorialpro_author   | varchar(255) | YES  |     | NULL    |       |
  | submission_date | date         | YES  |     | NULL    |       |
  +-----------------+--------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

• SHOW INDEX FROM:

  mysql> SHOW INDEX FROM tutorialpro_tbl;
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | tutorialpro_tbl |          0 | PRIMARY  |            1 | tutorialpro_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  1 row in set (0.00 sec)
  

• SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G:

  mysql> SHOW TABLE STATUS  FROM tutorialpro;   # Displays information for all tables in the tutorialpro database
  
  mysql> SHOW TABLE STATUS from tutorialpro LIKE 'tutorialpro%';     # Displays information for tables starting with 'tutorialpro'
  

mysql> SHOW TABLE STATUS FROM tutorialpro LIKE 'tutorialpro%'\G;   # Adding \G, query results are printed by column

GIF demonstration: