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

1.1. The problem of root password loss in mysql Database

Skip the permission table by adding the-- skip-grant-tables parameter

# mysql-uroot-p

To stop the library forcibly, check the mysql process number first

[root@localhost mysql] # ps-ef | grep mysql

Root 16762 10 10:18 pts/3 00:00:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-datadir=/data/mysql-pid-file=/data/mysql/mysql.pid

Mysql 18122 16762 0 10:18 pts/3 00:00:01 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/data/mysql/error.log-open-files-limit=65535-pid-file=/data/mysql/mysql.pid-socket=/tmp/mysql.sock-port=3306

Kill drops the mysql process with the following command:

[root@localhost mysql] # kill-9 18122 16762

Skip the permission check table and restart the database

#. / mysqld_safe-defaults-file=/etc/my.cnf-skip-grant-tables &

For security reasons, remote connections can be prohibited like this:

# mysqld_safe-skip-grant-tables-skip-networking &

Log back into the database

Set a new password and authorize the root user. After 5.7, the password password is replaced by the authentication_string field.

# mysql

Mysql > use mysql

# View users

Mysql > select user,host from user

Mysql > update mysql.user set authentication_string=password ('root') where user='root' and Host =' localhost'

Mysql > flush privileges

The modification is complete. Restart

[root@localhost ~] # service mysql restart

Start the database normally

#. / mysqld_safe-defaults_file=/etc/my.cnf &

# mysql-uroot-p

Then mysql can connect and change the user's password normally.

Mysql > alter user 'root' identified by' root123'

Or

Mysql > set password for 'root'@'localhost'=password (' 123')

1.2. The connection method of mysql database

There are two connection methods for linux platform: 1.tcp/ip connection and 2.Socket connection

Windows platform: 1.name pipe;2.share memory (not considered)

TCP/IP connections are the most widely used:

Mysql-u username-p password-P port-h IP

The client initiates a link to the instance. The instance check permission Table mysql.user allows the link to be established.

The Unix Socket connection method is not a network protocol and can only be used by the client and the database instance on one server. The socket file path specified in the configuration file: socket=/tmp/mysql.sock

Connection command:

Mysql-u username-p password-S * / tmp/mysql.sock

Tool for connecting to mysql: sqlyog,navicat

1.3. User rights management

Users in MySQL database are divided into super-managed user root and ordinary user (created by root user).

Users with supervised permissions and all privileges permissions can only be managed by DBA.

Create users, try to have a dedicated account, do not want one account to manage multiple libraries.

Create user syntax:

Create user username@host identified by 'password'

Note: host IP avoids using% and can assign an IP segment

Permission classification:

Read-only permission: can only be queried, not DML

L read and write permissions: insert,update,delete,select

Example:

Create user 'erp_read'@'192.168.56.%' identified by' erp123'

Grant select on erp.* to 'erp_read'@'192.168.56.%' identified by' erp123'

Flush privileges

Create user' erp_user'@'192.168.56.%' identified by 'erp456'

Grant select,insert,update,delete on erp.* to 'erp_user'@'192.168.56.%' identified by' erp456'

Flush privileges

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.