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

Recovery and setting of mysql password

[root@mysql ~] # initial password randomly generated after startup of grep password / var/log/mysqld.log#mysql

2017-12-20T02:36:18.623330Z 1 [Note] A temporary password is generated for root@localhost: 5h) > QAdqbI7t

# Log in with initial password and reset password initial password cannot operate on database requires password reset

[root@mysql4-1] # mysql-hlocalhost-uroot-pendant 5h) > QAdqbI7t'

Modify password authentication policy

Mysql > set global validate_password_policy=0

# Policy 0 length

1 (default) length; numbers, lowercase / uppercase, and special characters

2 length; numbers, lowercase / uppercase, and special characters; dictionary files

Modify password length 6 default value is 8 characters

Mysql > set global validate_password_length=6

Mysql > alter user root@ "localhost" identified by "123456"

Mysql > show database;# test

Mysql > quit

Bye

[root@mysql4-1] # mysql-hlocalhost-uroot-pendant 123456'

Set the password authentication policy to take effect permanently

[root@mysql4-1 ~] # vim / etc/my.cnf

...

[mysqld]

Validate_password_policy=0

Validate_password_length=6

...

[root@mysql4-1 ~] # systemctl restart mysqld

Modify the local management password of the database administrator (operating system administrator)

Mysqladmin-hlocalhost-uroot-p Old password password 'New password'

[root@mysql] # mysqladmin-hlocalhost-uroot-p123456 password '123123'

When the password is forgotten, the password is restored

[root@mysql ~] # vim / etc/my.cnf

[mysqld]

Skip-grant-tables# does not verify user passwords when it starts

[root@mysql ~] # systemctl restart mysqld

[root@mysql ~] # mysql

Mysql > use mysql

Mysql > update user set password_expired= "N" where user= "root"

Mysql > update mysql.user set authentication_string=password ("abc123") where user= "root"

Mysql > flush privileges; # refresh the system permissions related table of MySQL

[root@mysql ~] # vim / etc/my.cnf

Do not verify the user's password at startup

[root@mysql ~] # systemctl restart mysqld

[root@mysql] # mysql-uroot-pabc123

Data management

Data import: store the contents of the system file in the table of the database

/ etc/passwdstudb.user

User name password placeholder UID GID description information home directory shell

Create database studb

Create table studb.user (

Name char (50)

Password char (1)

UID int (2)

GID int (2)

Comment varchar (100)

Homedir char (100)

Shell char (25)

) engine=innodb

Select * from studb.user

Load data infile 'directory / filename' into table 'library. Table name 'fields terminated by' field spacing symbol "lines terminated by" line spacing symbol

Check the default usage directory and whether it exists

Mysql > show variables like "secure_file_priv"

+-+

| | Variable_name | Value |

+-+

| | secure_file_priv | / var/lib/mysql-files/ |

+-+

[root@mysql4-1 ~] # cp / etc/passwd / var/lib/mysql-files/

[root@mysql4-1 ~] # setenforce 0

Mysql > load data infile'/ var/lib/mysql-files/passwd' into table user fields terminated by ":" lines terminated by "\ n"

Query OK, 44 rows affected (0.04 sec)

Records: 44 Deleted: 0 Skipped: 0 Warnings: 0

Modify the default usage directory

[root@mysql4-1 ~] # mkdir / myfile

[root@mysql4-1 ~] # chown mysql / myfile/

[root@mysql4-1 ~] # vim / etc/my.cnf

[mysqld]

Secure_file_priv= "/ myfile"

[root@mysql4-1 ~] # systemctl restart mysqld

Mysql > show variables like "secure_file_priv"

+-+ +

| | Variable_name | Value |

+-+ +

| | secure_file_priv | / myfile/ |

+-+ +

1 row in set (0.00 sec)

Data export: storing table records to the system

Sql query into outfile "directory / file name"

Sql query into outfile "directory / file name" fields terminated by field interval symbol "lines terminated by" line spacing symbol

Mysql > select name,UID from user limit 5 into outfile "/ myfile/user1.txt"

[root@mysql4-1 ~] # ls / myfile/

User1.txt

[root@mysql4-1 ~] # cat / myfile/user1.txt # default field spacing symbol is the default line spacing symbol "\ n"

Root0

Bin1

Daemon2

Adm3

Lp4

Mysql > select name,UID from user limit 5 into outfile "/ myfile/user2.txt" fields terminated by "#" lines terminated by ":"

[root@mysql4-1 ~] # ls / myfile/

User1.txt user2.txt

[root@mysql4-1 ~] # cat / myfile/user2.txt

Root#0:bin#1:daemon#2:adm#3:lp#4:

User Authorization grant

Is to add a new connection user to the database server

Grant permission list on library name to user @ "client address" identified by 'password' [with grant option]

Mysql > grant all on *. * to root@192.168.4.2 identified by '123456' with grant option

Representation of permissions: all (so permissions), usage (no permissions), select,update (name,age), delete

Representation of library name: library name. Table name library name.

User name customization

Client address representation: 192.168.4.117 (one machine) 192.168.2.2% (a network segment)

Identified by 'password' # login password

With grant option # can have authorization optional

Client test authorization

Which mysql

Mysql-h database server ip-u username-p password

[root@localhost] # mysql-h292.168.4.1-uroot-p123456

Select @ @ hostname

Mysql > select @ @ hostname

+-+

| | @ @ hostname |

+-+

| | mysql4-1 |

+-+

1 row in set (0.00 sec)

Select user ()

Mysql > select user ()

+-+

| | user () |

+-+

| | root@192.168.4.2 |

+-+

1 row in set (0.00 sec)

Show grants

Mysql > show grants

+-+

| | Grants for root@192.168.4.2 |

+-+

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.4.2' WITH GRANT OPTION |

+-+

1 row in set (0.00 sec)

Allow 123456 full permissions for bbsuser tables with small bbsdb only when connecting passwords from the web server

Mysql > grant all on bbsdb.* to bbsuser@192.168.4.3 identified by '123456'

MySQL [(none)] > show grants

+-+

| | Grants for bbsuser@192.168.4.3 |

+-+

| | GRANT USAGE ON *. * TO 'bbsuser'@'192.168.4.3' |

| | GRANT ALL PRIVILEGES ON `bbsdb`. * TO 'bbsuser'@'192.168.4.3' |

+-+

2 rows in set (0.00 sec)

MySQL [(none)] > create database bbsdb

Query OK, 1 row affected (0.00 sec)

The user running admin has only the right to query records in the database server native login password 123456.

Mysql > grant select on *. * to admin@localhost identified by '123456'

In the table under the authorization information store sub-authorization library mysql

Mysql > use mysql

Mysql > show tables

User's existing authorized user information

Db authorizes user access to the library

Tables_priv authorizes user access to the table

Columns_priv authorized user access to the fields in the table

Check what authorized users are on the server

Mysql > select user,host from mysql.user

+-+

| | user | host |

+-+

| | root | 192.168.4.2 | |

| | bbsuser | 192.168.4.3 | |

| | admin | localhost |

| | mysql.sys | localhost |

| | root | localhost |

+-+

5 rows in set (0.00 sec)

Mysql > show grants for bbsuser@192.168.4.3

+-+

| | Grants for bbsuser@192.168.4.3 |

+-+

| | GRANT USAGE ON *. * TO 'bbsuser'@'192.168.4.3' |

| | GRANT ALL PRIVILEGES ON `bbsdb`. * TO 'bbsuser'@'192.168.4.3' |

+-+

2 rows in set (0.00 sec)

Check the permissions of some authorized users to the libraries on the server

Mysql > select user,host,db from mysql.db

+-+

| | user | host | db | |

+-+

| | bbsuser | 192.168.4.3 | bbsdb | |

| | mysql.sys | localhost | sys | |

+-+

2 rows in set (0.01sec)

View the permissions of some authorized users on the tables in the library on the server

Mysql > select host,user,db,table_name from mysql.tables_priv

+-+

| | host | user | db | table_name | |

+-+

| | localhost | mysql.sys | sys | sys_config | |

+-+

After the authorized user logs in to the server, change the login password

Set password=password ("123456")

Administrator resets login password for authorized user

Set password for user name @ client address

Mysql > set password for bbsuser@192.168.4.3=password ('abc123')

Permission revocation revokel

Revokel permission list on library name for user name @ 'client address'

Delete authorized user

Drop user user name @ "client address"

View authorized user permissions

Show grants for user name @ "client address"

Mysql > show grants for root@192.168.4.2

Revoke the authorization of the root user to log in on the 192.168.4.2 host

Mysql > revoke grant option on *. * from root@192.168.4.2

Revoke the permissions of the root user to delete and modify records logged in on the 192.168.4.2 host

Mysql > revoke update,delete on *. * from roo

Revoke all permissions left by root users

Mysql > revoke all on *. * from root@192.168.4.2

You can also revoke the user's permissions by modifying the table record.

Mysql > select * from mysql.db where db='bbsdb' and user='bbsuser' and host='192.168.4.3'\ G

Modify the record information in the corresponding table

Mysql > update mysql.db set delete_priv= "N", Drop_priv= "N" where db='bbsdb' and user='bbsuser' and host='192.168.4.3'

Mysql > flush privileges

Mysql Optimization:

The database server is very slow to respond to customer requests, which may be caused by those reasons. How to eliminate it, please state your processing ideas.

1. Narrow network bandwidth, speed measurement software pays for bandwidth.

two。 Low hardware configuration

CPU memory hard disk utilization

Nuclear capacity large rotational speed 15000 / s

Cpu utilization rate

[root@mysql12 ~] # uptime

20:18:23 up 6 min, 1 user, load average: 0.08, 0.13, 0.09

Load

Memory utilization

[root@mysql12] # free-m

Total used free shared buff/cache available

Mem: 993 282 463 6 246 556

Swap: 2047 0 2047

Disk utilization

[root@mysql12 ~] # top

Top-20:20:52 up 8 min, 1 user, load average: 0.01,0.08,0.07

Tasks: 117 total, 2 running, 115 sleeping, 0 stopped, 0 zombie

% Cpu (s): 0.3 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st

KiB Mem: 1016916 total, 435968 free, 327600 used, 253348 buff/cache

KiB Swap: 2097148 total, 2097148 free, 0 used. 531780 avail Mem

The greater the percentage of wa, the more waiting for disk to be written or read.

3. The low version of the software that provides the service leads to the upgrade of the service software version

View the value of the parameter when the service is running

View parameter valu

Mysql > show variables like 'keyword'

Modify parameter valu

Command line modification

Set variable name = value

Set global variable name = value # Global

Permanent modification

Vim / etc/my.cnf

Variable name = value

: wq

Systemclt restart mysqld

Number of concurrent connections

Mysql > show variables like "connect%"

+-+-

| | Variable_name | Value |

+-+-

| | character_set_connection | utf8 |

| | collation_connection | utf8_general_ci |

| | connect_timeout | 10 | |

| | disconnect_on_expired_password | ON |

| | init_connect |

| | max_connect_errors | 100 | |

| | max_connections | 151 | |

| | max_user_connections | 0 | |

| | performance_schema_session_connect_attrs_size | 512 | |

+-+-

Mysql > show variables like "max_connections%"

+-+ +

| | Variable_name | Value |

+-+ +

| | max_connections | 151 | # the maximum number of concurrent connections is 151 |

+-+ +

1 row in set (0.01 sec)

Mysql > set GLOBAL max_connections=300

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like "max_connections%"

+-+ +

| | Variable_name | Value |

+-+ +

| | max_connections | 300 | |

+-+ +

The maximum number of connections / concurrent connections is about 0.85

Mysql > show global status like "Max_used_connections"

+-+ +

| | Variable_name | Value |

+-+ +

| | Max_used_connections | 1 | |

+-+ +

Mysql > show processlist;# to view the currently connected

+- -+-+

| | Id | User | Host | db | Command | Time | State | Info |

+- -+-+

| | 1 | system user | | NULL | Connect | 1961 | Connecting to master | NULL |

| | 2 | system user | | NULL | Connect | 1961 | Slave has read all relay log; waiting for more updates | NULL |

| | 5 | root | localhost | NULL | Query | 0 | starting | show processlist | |

+- -+-+

Timeout time

Mysql > show variables like "timeout%"

The timeout of the connect_timeout tcp three-way handshake is too long. The thread continues the pid number and cannot reclaim memory. The timeout is too short. The server will repeatedly generate multiple threads to respond to a request.

Timeout waiting for command execution after the wait_timeout connection is established (inactivity timeout waiting for the connection to be closed)

Number of reused threads

Mysql > show variables like "size%"

Thread_cache_size can reuse the number of threads saved in the cache

Number of tables opened simultaneously by multiple threads

Mysql > show variables like "cache%"

Number of table_open_cache all threads open tables at the same time

Query cache settings

Mysql > show variables like "query_cache%"

Query_cache_type = 0 | 1 | 2

0 storage is not allowed

1 as long as the query result does not exceed the limit, it can be stored in the query cache

2 explicitly specify that the query results should be stored in the cache before they can be saved.

Mysql > show global status like "qcache%"

Qcache_inserts adds one at a time in the query cache.

Qcache_hits adds one every time it finds it in the query cache.

4. Programmers write complex query statements to access data, resulting in slow processing

Enable slow query log files to record commands that display query results beyond a specified time

Mysql supports four types of log files:

Binlog log

Error log log-error=/var/log/mysqld.log# is enabled by default

Query log records all sql operations

Option

General-log

General-log-file= filename # Custom Log File

# vim / etc/my.cnf

[mysqld]

General-log

: wq

# systemctl restart mysqld

[root@mysql12 ~] # ls / var/lib/mysql

Mysql12.log

[root@mysql12] # mysql-uroot-p123456

Mysql > show databases

[root@mysql12 mysql] # tail-f mysql12.log

/ usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). Started with:

Tcp port: 0 Unix socket: / var/lib/mysql/mysql.sock

Time Id Command Argument

2018-01-02T03:29:48.534719Z 3 QuerySELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE'% partitioned%'

2018-01-02T03:30:55.132492Z 5 Connectroot@localhost on using Socket

2018-01-02T03:30:55.132850Z 5 Queryselect @ @ version_comment limit 1

2018-01-02T03:31:15.772203Z 5 Queryshow databases

2018-01-02T03:31:59.756227Z 5 Quit

Slow query log

Option

Slow-query-log enables slow query logs

Slow-query-log-file= filename # Custom Log File

Long-query-time is recorded only if it exceeds the specified number of seconds (default is 10 seconds)

Long-queries-not-using-indexes records queries that do not use indexes

# vim / etc/my.cnf

[mysqld]

Slow-query-log

: wq

# systemctl restart mysqld

[root@mysql12 ~] # ls / var/lib/mysql

Mysql12-slow.log

Mysql > select sleep (10)

[root@mysql12 mysql] # cat mysql12-slow.log

/ usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). Started with:

Tcp port: 0 Unix socket: / var/lib/mysql/mysql.sock

Time Id Command Argument

# Time: 2018-01-02T03:27:33.280720Z

# User@Host: root [root] @ localhost [] Id: 6

# Query_time: 10.000291 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1514863653

Select sleep (10)

5. The network topology is unreasonable and there is a data transmission bottleneck.

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.