Shulou(Shulou.com)06/01 Report--

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 RUNOOB;Database changed

SHOW DATABASES:

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

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 RUNOOB;Database changedmysql > SHOW TABLES;+-+ | Tables_in_runoob | +-+ | employee_tbl | | runoob_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 runoob_tbl +-+ | Field | Type | Null | Key | Default | Extra | +- -+ | runoob_id | int (11) | NO | PRI | NULL | runoob_title | varchar | YES | | NULL | | runoob_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 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 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 RUNOOB; # displays the information of all tables in the database RUNOOB mysql > SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # information of tables whose names begin with runoob mysql > SHOW TABLE STATUS from RUNOOB LIKE' runoob%'\ G; # plus\ G, query results are printed as columns

The Gif diagram shows:

MySQL installation

MySQL PHP syntax

Note list

Oocarain

Ooc***in@163.com

Record the problems encountered in the MySQL learning process.

Systems: win32 bit

MySQL version: 5.7.17-log

MySQL syntax is not case-sensitive, but uppercase is easier to see.

Starting and shutting down the MySQL service

1 [start menu] search services.msc to open windows [Service Manager], where you can turn on and off the MySQL service.

2 use the command in cmd:

Net start mysql # start the mysql service net stop mysql # turn off the mysql service

The net command is not recognized, as follows:

This is the reason why the environment variables are not configured. Which file's environment variables are not configured?

The net.exe under C:\ windows\ system32\ is not configured with environment variables.

Now switch to this path and try whether you can use the net command:

In Powershell, you need to use

.\ net stop mysql

Shut down the service.

Can be used directly in cmd

Net start mysql

Start the service.

After adding c:\ windows\ system32 to the Path of the system:

Success!

Oocarain

Oocarain

Ooc***in@163.com

9 months ago (03-06)

A fish

Ili***yun@163.com

Reference address

When you add a user with insert, you may get an error:

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

There is a statement in my-default.ini:

Strict mode is specified. For security, strict mode forbids directly modifying the user table in the mysql library to add new users in the form of insert.

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

After deleting the STRICT_TRANS_TABLES, you can add it using insert.

A fish

A fish

Ili***yun@163.com

Reference address

5 months ago (06-25)

Morrison

982***639@qq.com

Reference address

Add a new user suggestion GRANT command

Grant ordinary data users have the right to query, insert, update and delete all table data in the database.

Grant select on testdb.* to common_user@'%'grant insert on testdb.* to common_user@'%'grant update on testdb.* to common_user@'%'grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

Grant select, insert, update, delete on testdb.* to common_user@'%'

Second, grant database developers, create tables, indexes, views, stored procedures, functions. Wait for permission.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@'192.168.0.%';grant alter on testdb.* to developer@'192.168.0.%';grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions.

Grant references on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL temporary table permissions.

Grant create temporary tables on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL index permissions.

Grant index on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL view and view view source code permissions.

Grant create view on testdb.* to developer@'192.168.0.%';grant show view on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL stored procedures and function permissions.

Grant create routine on testdb.* to developer@'192.168.0.%';-now, can show procedure statusgrant alter routine on testdb.* to developer@'192.168.0.%';-now, you can drop a proceduregrant execute on testdb.* to developer@'192.168.0.%'

Third, the permission of grant ordinary DBA to manage a MySQL database.

Grant all privileges on testdb to dba@'localhost'

The keyword privileges can be omitted.

4. Grant Advanced DBA manages the permissions of all databases in MySQL.

Grant all on *. * to dba@'localhost'

Fifth, MySQL grant permissions, which can be used at multiple levels.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost;-- dba can query tables in all databases in MySQL. Grant all on *. * to dba@localhost;-- dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost;-- dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

Here, when authorizing multiple tables to a user, the above statements can be executed multiple times. For example:

Grant select (user_id,username) on smp.users to mo_user@'%' identified by '123345 grant select on smp.mo_sms to mo_user@'%' identified by' 123345'

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'grant execute on function testdb.fn_add to' dba'@'localhost'

6. View MySQL user permissions

View current user (own) permissions:

Show grants

View additional MySQL user rights:

Show grants for dba@localhost

Revoke the permissions that have been granted to MySQL users.

The syntax of revoke is similar to that of grant, except that you need to replace the keyword to with from:

Grant all on *. * to dba@localhost;revoke all on *. * from dba@localhost

VIII. Considerations for user rights of MySQL grant and revoke

1. After the grant, revoke user permission, the user will not take effect until the user reconnects to the MySQL database.

two。 If you want authorized users, you can also grant these permissions to other users. You need the option grant option.

Grant select on testdb.* to dba@localhost with grant option

This feature is generally not needed. In practice, database permissions are best managed by DBA.

Note: the FLUSH PRIVILEGES statement needs to be executed after the creation.

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.