Start and stop the MySQL server

Windows system.

In Windows, open the command window (CMD) and go to the bin directory of the MySQL installation directory.

Activation:

cd c:/mysql/bin
mysqld --console
Copy the code

Close:

cd c:/mysql/bin
mysqladmin -uroot shutdown
Copy the code

Linux system.

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

ps -ef | grep mysqld
Copy the code

If MySql is already started, the above command will print a list of MySql processes. If MySql is not started, you can use the following command to start MySql server:

root@host# cd /usr/bin
./mysqld_safe &
Copy the code

If you want to shut down the MySQL server currently running, you can run the following command:

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

MySQL user Settings

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

The following is an example of adding a user with the username guest and password Guest123, and granting the SELECT, INSERT, and UPDATE permissions to the user:

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.01sec) mysql> SELECT host, user, password FROM user WHERE user = 'guest'; +-----------+---------+------------------+ | host | user | password | +-----------+---------+------------------+ | 6 f8c114b58f2ce9e localhost | guest | | + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

When adding a user, note that the PASSWORD() function provided by MySQL is used to encrypt the PASSWORD. You can see in the example above that the user password is encrypted to 6f8c114b58f2ce9e.

Note: the password for the user table in MySQL5.7 has been changed to authentication_string.

Note: the password() encryption function was removed in 8.0.11 and can be replaced with the MD5() function.

Note: The FLUSH PRIVILEGES statement needs to be executed. The authorization table is reloaded after this command is executed.

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.

When creating a user, you can specify permissions for the user. In the corresponding permission column, set it to ‘Y’ in the insert statement. The user permissions 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 and 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';
Copy the code

The above command creates a user information record in the user table of the mysql database.

Note: MySQL SQL statements start with semicolons (;) As a closing sign.

/etc/my.cnf file configuration

In general, 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
Copy the code

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

Command to manage MySQL

The following is a list of common commands for using the Mysql database:

  • USE database name: select the Mysql database that you want to operate. After this command is executed, all Mysql commands apply only to this database.

    mysql> use RUNOOB;
Database changed
Copy the code

  • SHOW DATABASES: Lists the DATABASES 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)
Copy the code

  • SHOW TABLES: Displays all TABLES of a specified database. Before running this command, you need to run the use command to select the database to be operated.

    mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)
Copy the code

  • SHOW COLUMNS FROM data table: Displays data table attributes, attribute types, primary key information, whether the COLUMNS are NULL, default values and other information.

    mysql> SHOW COLUMNS FROM runoob_tbl; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | runoob_id | int(11) | NO | PRI | NULL | | | runoob_title | varchar(255) | YES | | NULL | | | runoob_author | varchar(255) | YES | | NULL | | | submission_date | The date | YES | | NULL | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + 4 rows in the set (0.01 SEC)Copy the code

  • SHOW INDEX FROM table: Displays detailed INDEX information about a table, including the PRIMARY KEY.

    mysql> SHOW INDEX FROM runoob_tbl; +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | runoob_tbl | 0 | PRIMARY | 1 | runoob_id | A | 2 | NULL | NULL | | BTREE | | |  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------ + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

  • SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern’] \G: This command displays performance and statistics of Mysql database management system.

    mysql> SHOW TABLE STATUS FROM RUNOOB; Mysql > SHOW TABLE STATUS from RUNOOB LIKE 'RUNOOB %'; Mysql > SHOW TABLE STATUS from runoob LIKE 'runoob%'\G; # plus \G, the query results are printed in columnsCopy the code

Gif illustration: