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

This article mainly gives you a brief talk about the common management of Mysql5.7 database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the common management of Mysql5.7 database can bring you some practical help.

User management 1. The users created by the new users are saved in the user table of the mysql database. You can use the query statement to view the created users. (1) Command format: create user' user'@'host' identified by 'password'; View system user; select user,authentication_string,host from mysql.user;create user' test1'@'localhost' identified by 'abc123'

(2) you can also use the statement grant to modify permissions; command format: grant permissions on database. Table to user @ host identified by password; grant all on *. * to 'test2'@'localhost' identified by' abc123'

Delete user command format: after drop user' user'@'host';drop user' test2'@'localhost'; deletes the user test2, the user is no longer in the database.

Rename user command format: rename user' old_user'@'host' to 'new_user'@'host';rename user' test1'@'localhost' to' user1'@'192.168.30.55'

There are two ways to set a password for a user to change a user's password: to change the password of the currently logged-in user and to change the password of other users. (1) modify the command format of the password of the currently logged-in user: set password = password ('password')

(2) Command format for modifying passwords of other users: set password for 'user'@'host' = password (' password')

Solution to forget root password 1) systemctl stop mysqld.service closes database 2) modify mysql configuration file, vim / etc/my.cnf write skip-grant-tables parameter function is that users do not use authorization table when logging in, so users can log in directly without using password.

3) enter mysql and use update to modify the root password update mysql.user set authentication_string = password ('qazwsx123') where user='root'

4) delete the parameters just added in the configuration file, restart mysql, and log in with the new password

Authorization control in mysql, permission setting is very important, the allocation of permissions can clearly divide the responsibilities. (1) Grant permissions, using the grant command, is to specify that the user allows it to manipulate certain tables and has the appropriate permissions to operate on these tables. Command format: grant permissions on database. Table to user @ host identified by password; check the command format of user permissions: show grants for 'user'@'localhost'; creates a user lisi to do experimental verification, so that user lisi can connect to the host localhost and use the password "123qwe". He has the right to query and modify all database tables.

Use lisi login mysql to verify that the select,update statement executes normally, but the execution of the insert statement shows that there are not enough permissions

(2) revoke permissions. Use permission statement to revoke the database permissions of a specified user. The command format is as follows: revoke permissions on database. Table from user @ host; revoke update on *. * from 'lisi'@'localhost'

Mysql log management as the log file is an important reference to grasp the running status of the database, the maintenance of log files is also of great significance. Mysql log types are: error log, general log, binary log, slow query log default working directory is: / usr/local/mysql/data/ (1) error log is mainly recorded when mysql starts and stops And information about any errors that occur during the run. You can specify the location and file name of the error log in the configuration file my.cnf of mysql.

Vim / etc/my.cnf

.

[mysqld]

User = mysql

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

# error Log

Log-error=/usr/local/mysql/data/mysql_error.log # after the error log directory and file name are restarted, the mysql_error.log is generated to record the error log (2) the general query log is used to record all connections and statements of the mysql. It is turned off by default. Modify the general_log=ON option of the configuration file to open the general query log.

Vim / etc/my.cnf

.

[mysqld]

User = mysql

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

# General Log

General_log=ON

General_log_file=/usr/local/mysql/data/mysql_general.log # Open the general query log and specify the location and file name to restart the mysql service. The general query log takes effect. (3) the binary log is used to record all statements that have updated or potentially updated the data, recording changes to the data. The main purpose is to restore the database to the maximum extent possible when the data is recovered. Binary logging is turned on by default. Under the data directory, it is named after mysql-bin. When the amount of data is large, it will be automatically divided into multiple log files, with numbers as the extension.

Vim / etc/my.cnf

.

[mysqld]

User = mysql

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

# binary log

Log_bin=mysql-bin # add binary log parameters to view binary log files can be queried using the mysqlbinlog command

Mysqlbinlog-no-defaults mysql-bin.000001

(4) slow query log records all SQL statements whose execution time exceeds long_query_time seconds, and is used to find out which query statements have long execution time in order to optimize them. The default slow log is off. Slow_query_log is the option for slow log. The default is OFF.

Vim / etc/my.cnf

.

[mysqld]

User = mysql

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

# slow log

Slow_query_log=ON # enable the slow query function slow_query_log_file=mysql_slow_query.log # specify the slow query log storage directory and file name long_query_time=1 # set the statement executed for more than one second can set all the above parameters in the configuration file at the same time, and then restart the service to view the results

The reason for the occurrence of data garbled in the computer is that any data is stored in binary. To store a character, it is necessary to encode it and correspond to it with a binary number. This corresponding rule is character coding. There are many rules for coding, and the set of "characters" encoded by one rule is called "character set". When specifying coding standards, "set of characters" and "encoding" are generally established at the same time. Therefore, what we usually call "character set", such as GB2312,GBK, UTF-8, etc., not only has the meaning of "set of characters", but also contains the meaning of "coding". GB2312,GBK can be used for Chinese processing, which is a Chinese character set, while UTF-8 is a character set for the characters of every country and region in the world, and UTF-8 is a character set with variable storage length, such as the English alphabet needs only one byte of storage, which saves storage space, so the UTF-8 character set is usually used in the database. Garbled codes often occur in the use of Mysql CVM, and the main reasons are as follows (1) character set setting problem of CVM system (2) data table language setting problem (3) when using and storing data, if the character set of each link is different, garbled Mysql garbled solution will be generated. For mysql CVM, as long as the stored character set is set to UTF-8 and the corresponding client program uses the same code, there will be no garbled code. There are many ways for mysql to set the character set (1) specify the character set when creating the database, set the character set with the parameter character set, and collate is the rule for proofreading the character set.

Create database chartest character set 'utf8' collate' utf8_general_ci'

If you do not specify a character set when creating a table, it is specified by database character set by default. You can use show table status; to view the character set of the table.

(2) you can also specify a character set when creating a table, and the tables in the database can set different character sets as needed.

(3) once and for all, add the following settings to the configuration file my.cnf

[client]

Port = 3306

Default-character-set=utf8 # add this setting

Socket = / usr/local/mysql/mysql.sock

At this time, the created database and tables no longer need to be specified, as long as you modify this place, you can unify the character set of the database, and the character set will take effect after restart.

The common management of Mysql5.7 database will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.