MySQL Management

Start and shutdown MySQL server

Under Windows system

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

Startup:

cd c:/mysql/bin
mysqld　--console

Shutdown:

cd c:/mysql/bin
mysqladmin　-uroot shutdown

Under Linux system

Firstly, we need to check if the MySQL server is running by executing the following command:

ps　-ef | grep mysqld

If MySql is already running, the above command will output the MySQL process list, If MySQL is not running, 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 to the user table in the mysql database.

The following is an example of adding a user, the username is guest, and the password is guest123，and grant the user permission 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)

Please note that when adding a user, you should use the PASSWORD() function provided by MySQL to encrypt the password. You can see from the above examples that the user password is encrypted as follows: 6f8c114b58f2ce9e.

Note:In MySQL5.7 The password in the user table ofauthentication_string.

Note:The password() encryption function has been added in 8.0.11 has been removed, and MD can be used.5() function instead.

Note:Pay attention to need to execute FLUSH PRIVILEGES Statement. This command will reload the authorization table after execution.

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 permissions 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 method to add a user is to use the SQL GRANT command. The following command will add a 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 commands will create a user information record in the user table of the mysql database.

Note: MySQL SQL statements end with a semicolon (;) as the identifier.

/etc/my.cnf file configuration

In general, you do not need to modify this configuration file. The default configuration 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.

MySQL management commands

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

• USE Database name :
  Select the Mysql database to be operated, after executing this command, all Mysql commands will only target this database.

  mysql> use w3codebox;
  Database changed

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

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

• SHOW TABLES:
  Displays all tables in the specified database, you need to use the use command to select the database to be operated before using this command.

  mysql> use w3codebox;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_w |3codebox |
  +------------------+
  | employee_tbl |
  |　w3codebox_tbl |
  | tcount_tbl |
  +------------------+
  3　rows in set (0.00 sec)

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

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

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

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

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

  mysql>　SHOW　TABLE　STATUS　　FROM　w3codebox;　　　#　Show database　w3Information of all tables in codebox
  mysql>　SHOW　TABLE　STATUS　from　w3codebox　LIKE　'w3codebox%';　　　　　#　Table　names　starting　with　w3Information of tables starting with codebox
  mysql>　SHOW　TABLE　STATUS　from　w3codebox　LIKE　'w3codebox%'\G;　　　#　Adding　\G，to　print　query　results　by　columns