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

Editor to share with you what MySQL 5.7 commonly used commands are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Start and stop database 1.1 Windows

Administrator permissions

Net start mysql

Net stop mysql

No administrator privileges are required

Mysqladmin-uroot-p shutdown

1.2 Linux

Follow-up instructions on the installation of Linux version

Service mysqld stop

Service mysqld start

two。 Post-installation Security Settings 2.1 change password

$mysql-uroot-p

Mysql > set password=password ('your_passord')

2.2 Delete test users and databases

Mysql > delete from mysql.db where db in ('test',' test\ _%')

Mysql > delete from mysql.user where user=''

Mysql > delete from mysql.user where authentication_string=''

2.3 modify administrator account

The following command renames root to system and gives local login permissions

Mysql > update mysql.user set user='system' where user='root' and host='localhost'

Mysql > grant all on *. * to 'system'@'127.0.0.1' identified by' 123456 'with grant option

Mysql > delete from mysql.user where Host='%' and user='root'

Mysql > flush privileges

After modification, you need to log in using the following command

Mysql-usystem-p123456-h 127.0.0.1

3. New database and users

The following statement creates a new test database, using the default character set

And set up a user1 account and give it all permissions in the test database.

% means that all clients can log in

PS:MySQL account is a combination of user name and login permissions, which together form an account.

Mysql > create database test

Mysql > grant all on test.* to 'user1'@'%' identified by' 123456'

4. Related operations of the table

Mysql-uuser1-p123456-h 127.0.0.1

Mysql > use test

CREATE TABLE IF NOT EXISTS `test` (`test` INT UNSIGNED AUTO_INCREMENT, `test_ title` VARCHAR) NOT NULL, PRIMARY KEY (`test_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert into test values (1 perfect first row')

Select * from test

5. Query of variables 5.1 query of system variables

Query session variables

Session variables can be queried by specifying session

If it is not added, the default is the session variable.

Use% to match all characters

Mysql > show variables like'% read_only%'

Mysql > show variables like'% chara%'

The following methods do not support% wildcards

Mysql > select @ @ character_set_client

Query global variables

Global variables can be queried through global

Mysql > show global variables like'% read_only%'

Mysql > show global variables like'% character%'

5.2 query status variables

User system Monitoring and performance Analysis of State variables in MySQL

Session and global references to the above system variables

You can also use% wildcards

Mysql > show session status like'% Table_locks_immediate%'

6. Modify system variable 6.1 global variable

Modify global variables to affect subsequent newly connected sessions

Does not affect the current session

The database becomes invalid after it is rebuilt. If you need to keep it permanently, please write it to the configuration file.

Set global character_set_client=utf8

6.2 session variables

Modifying session variables affects only the current session

Modify session variables without keywords by default

Set character_set_client=gbk

The above is all the contents of this article "what are the common commands in MySQL 5.7.Thank you for reading!" I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.