MySQL management

MySQL management

Start and shut down the MySQL server

First, we need to check if the MySQL server is up by following these commands:

ps -ef | grep mysqld

If MySQL is already started, the above command outputs a list of MySQL processes, and if MySQL does not start, you can start the MySQL server using the following command:

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

If you want to shut down the MySQL server that is currently running, you can do the following:

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

MySQL user settings

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

The following is an instance of the added 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 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, be aware that passwords are encrypted using the PASSWORD() function provided by MySQL. Y ou can see the user password encryption in the above example: 6f8c114b58f2ce9e.

Note: In MySQL5.7, the password for the user table has been replaced with authentication_string.

Note: Again, the FLUSH PRIVILEGES statement needs to be executed. This command is reloaded into the authorization table after execution.

If you don't use this command, you won't be able to connect to the MySQL server with the newly created user unless you restart the MySQL server.

When you create a user, you can specify permissions for the user, and in the corresponding permission column, set it to 'Y' in the insert statement, and the list of user rights 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 SQL's GRANT command, which adds user zara to the specified database TUTORIALS with the password zara123.

root@host# mysql -u root -p password;
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 creates a user information record in the user table in the MySQL database.

Note: M ySQL's SQL statement is a half sign (;) A s the end identity.

/etc/my.cnf file configuration

In general, you do not need to modify the profile, which is configured as follows by default:

[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 directories where the different error log files are stored, and you generally do not need to change these configurations.

Manage MySQL commands

The following is a list of the commands commonly used in using the MySQL database:

• USE database name: Select the MySQL database to operate on, and when you use this command, all MySQL commands are for that database only.

mysql> use W3CSCHOOL;
Database changed

• SHOW DATABASES: Lists the databases of the MySQL database management system.

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

• SHOW TABLES: Displays all tables for the specified database, using the use command to select the database to operate on before using this command.

mysql> use W3CSCHOOL;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_W3Cschool |
+------------------+
| employee_tbl     |
| W3Cschool_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)

• SHOW COLUMNS FROM Data Sheet: Displays the properties of the data table, property types, primary key information, whether NULL, default values, and other information.

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

• SHOW INDEX FROM Data Sheet: Displays detailed index information for the data table, including PRIMARY KEY (primary key).

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

• SHOW TABLE STATUS LIKE Data Sheet: This command outputs the performance and statistics of the MySQL database management system.

mysql> SHOW TABLE STATUS  FROM W3CSCHOOL;   # 显示数据库 W3CSCHOOL 中所有表的信息
mysql> SHOW TABLE STATUS from W3CSCHOOL LIKE 'W3Cschool%';     # 表名以W3Cschool开头的表的信息
mysql> SHOW TABLE STATUS from W3CSCHOOL LIKE 'W3Cschool%'\G;   # 加上 \G，查询结果按列打印