How to manage MySQL database? SSH Commands to Manage MySQL Servers

how to manageMySQL database? SSH command managementMySQLserver

MySQL Management

Start and stop the MySQL server

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

ps -ef | grep mysqld

If MySql is already started, the above command will output a list of mysql processes, if mysql is not 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 the 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 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, please note that the password is encrypted using the PASSWORD() function provided by MySQL.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 replaced byauthentication_string.

note:Be aware of the need to implement FLUSH PRIVILEGES statement.After this command is executed, the grant 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.

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 list of user permissions 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 next command will add the user zara to the specified database TUTORIALS, and the password is 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 will create a user information record in the user table in the mysql database.

note: MySQL SQL statements are terminated with a semicolon (;).

/etc/my.cnf file configuration

Under normal circumstances, 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 do not need to change these configurations.

Commands to manage MySQL

The following lists the commands commonly used in the process of using the Mysql database:

• USE data storage name :
  Select the Mysql database to be operated. After using this command, all Mysql commands are only for this database.

  mysql> use chenweiliang;
  Database changed

• SHOW DATABASE: 
  Lists the database list of the MySQL database management system.

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

• SHOW TABLES:
  Display all tables of the specified database. Before using this command, you need to use the use command to select the database to be operated.

  mysql> use chenweiliang;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_chenweiliang |
  +------------------+
  | employee_tbl     |
  | chenweiliang_tbl       |
  | tcount_tbl       |
  +------------------+
  3 rows in set (0.00 sec)

• SHOW COLUMNS FROM data sheet:
  Display data table attributes, attribute types, primary key information, whether it is NULL, default values ​​and other information.

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

• SHOW INDEX FROM data sheet:
  Display the detailed index information of the data table, including PRIMARY KEY (primary key).

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

• SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
  This command will output the performance and statistics of the Mysql database management system.

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