Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use SSH commands to manage MySQL server", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "how to use SSH commands to manage MySQL server"!

MySQL management starts and shuts down the MySQL server

First, we need to check whether the MySQL server is started with 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 shutdownEnter password: * MySQL user Settings

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

The following is an example of adding a user, with the user name guest and password guest123, and authorizing the user to operate SELECT, INSERT, and UPDATE:

Root@host# mysql-u root-pEnter 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.01sec) 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 careful to use the PASSWORD () function provided by MySQL to encrypt the password. You can see in the above example that the encrypted user password is: 6f8c114b58f2ce9e.

Note: in MySQL5.7, the password of the user table has been replaced with authentication_string.

Note: note that the FLUSH PRIVILEGES statement needs to be executed. After this command is executed, the authorization table is 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 you create 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, which will add the user zara to the specified database TUTORIALS with a password of zara123.

Root@host# mysql-u root-p password;Enter password:*mysql > use mysql;Database changedmysql > 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: the SQL statement of MySQL ends with a semicolon (;).

/ etc/my.cnf file configuration

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

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/ mysql.sock[mysql.server] user=mysqlbasedir=/var/ lib[safe _ mysqld] err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

In the configuration file, you can specify different directories where the error log files are stored, and you generally do not need to change these configurations.

Commands for managing MySQL

The following is a list of commands commonly used in working with the Mysql database:

USE database name:

Select the Mysql database you want to operate on, and after using this command, all Mysql commands are directed only to that database.

Mysql > use chenweiliang;Database changed

SHOW DATABASES:

Lists the databases for 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:

Displays all tables for the specified database, and you need to use the use command to select the database to operate on before using this command.

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

SHOW COLUMNS FROM data sheet:

Displays the properties of the data table, attribute type, primary key information, whether it is NULL, default value 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 | YES | | NULL | | chenweiliang_author | varchar | 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 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 outputs the performance and statistics of the Mysql database management system.

Mysql > SHOW TABLE STATUS FROM chenweiliang; # displays information about all tables in the database chenweiliang mysql > SHOW TABLE STATUS from chenweiliang LIKE 'chenweiliang%'; # information about tables whose names begin with chenweiliang mysql > SHOW TABLE STATUS from chenweiliang LIKE' chenweiliang%'\ G # plus\ G, the query results are printed in columns. Thank you for reading. The above is the content of "how to use SSH commands to manage MySQL servers". After the study of this article, I believe you have a deeper understanding of how to use SSH commands to manage MySQL servers, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.