MySQL management

MySQL Management

Start and shut down the MySQL server

Under Windows system

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

Startup:

cd c:/mysql/bin
mysqld --console

Close:

cd c:/mysql/bin
mysqladmin -uroot shutdown

Under Linux system

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

ps -ef | grep mysqld

If MySql has been started, the above command will output the mysql process list. 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 shutdownEnter password: ******

MySQL User Settings

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

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

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;Database changedmysql> 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, please pay attention Use the PASSWORD() function provided by MySQL to encrypt the password. You can see in the above example that the user password after encryption is: 6f8c114b58f2ce9e.

Note: In MySQL5.7, the password of the user table has been replaced by authentication_string.

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

Note: Note that you need to execute the FLUSH PRIVILEGES statement. After executing this command, the authorization table will be reloaded.

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

You can specify permissions for the user when creating a 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 SQL GRANT command. The following command will add user zara to the specified database TUTORIALS with the password zara123.

root@host# mysql -u root -pEnter 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: MySQL SQL statements end with a semicolon (;).

/etc/my.cnf file configurationGenerally, you do not need to modify this configuration file. The default configuration of this 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 do not need to change these configurations.

Commands to manage MySQL

The following is a list of commonly used commands when using the Mysql database:

• USE database name:

Select the Mysql database to be operated. After using this command, all Mysql commands will only target this database.

mysql> use RUNOOB;Database changed

SHOW DATABASES:
List the database list of the MySQL database management system.

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