MySQL management

Start and shut down MySQL server

Under Windows system

Under Windows system, open the command window (cmd) and enter the bin directory of MySQL installation directory.

Start:

cd c:/mysql/bin
mysqld --console

close:

cd c:/mysql/bin
mysqladmin -uroot shutdown

Under Linux system

First, we need to check whether the MySQL server is started through the following command:

ps -ef | grep mysqld

If MySQL has been started, the above command will output the list of MySQL processes. If MySQL has not been started, you can use the following command to start the MySQL server:

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

If you want 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 settings

If you need to add a MySQL user, you just need to add a new user in the user table in the MySQL database.

The following is an instance of adding a user with the user name of guest and the password of guest123. The user is authorized to perform SELECT, INSERT and UPDATE operations:

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 users, please note that the PASSWORD() function provided by MySQL is used to encrypt the password. You can see in the above example that the encrypted user password is: 6f8c114b58f2ce9e

Note: in MySQL 5.7, the password of the user table has been changed to authentication_string.

Note: the password() encryption function has been removed in 8.0.11 and can be replaced by the MD5() function.

Note: the FLUSH PRIVILEGES statement needs to be executed. After this command is executed, the authorization table will be reloaded.

If you do not use this command, you cannot use the newly created user to connect to the mysql server unless you restart the mysql server.

When creating a user, you can specify permission for the user. In the corresponding permission column, set it to 'Y' in the insert statement. The user permission list is as follows:

• 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 way to add users is through the GRANT command of SQL. The following command will add user Zara to the specified database TUTORIALS with the password zara123.

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 information record in the user table in the mysql database.

Note: the SQL statement of MySQL uses a semicolon (;) as the end identifier.

/Configuration file of / etc/my.cnf

Generally, you do not need to modify the configuration file. The default configuration of the file is as follows:

[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 the directory where different error log files are stored. Generally, you don't need to change these configurations.