MySQL password change

Check whether MySQL service is started. If not, use / etc/init.d/mysqld start

[root@test-a ~]# ps aux | grep mysql
root      2180  0.0  0.1 115432  1724 ?        S    07:33   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/usr/local/mysql/mysqld.pid
mysql     2390  0.9 17.8 1117648 180356 ?      Sl   07:33   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/db.err --pid-file=/usr/local/mysql/mysqld.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root      2490  0.0  0.0 112704   972 pts/0    R+   07:35   0:00 grep --color=auto mysql

Log in to MySQL and change the password

[root@test-a ~]# mysql # The command was not found because the environment variable was not set for MySQL
-bash: mysql: command not found
[root@test-a ~]# export PATH=$PATH:/usr/local/mysql/bin/  
[root@test-a ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@test-a ~]# mysqladmin -uroot -p'test111'  password test222 #Change password
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
# In case of this error, the network basically means that the corresponding mysql.sock is not placed in the directory / tmp, but my configuration is / usr/local/mysql/mysql.sock
[root@test-a ~]# cat /etc/my.cnf
[mysql]
socket          = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
# If you think it may be a configuration file problem, go to find the difference and find that you don't have client configuration. Reconfigure and solve the problem  
[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
[client]
socket          = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql

[root@test-a ~]# mysqladmin -uroot -p'test111'  password test222
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

MySQL forgot to reset the password of root user

It mainly uses skip grant tables. Configure this field in the configuration file and restart it

[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
[client]
socket          = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@test-a ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@test-a ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set authentication_string=password('test111') where user='root'; # MySQL 5.7 is followed by authentication [string], and if it is before, password field
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

# Or use the following statement directly
mysql> alter user 'root'@'localhost' identified by 'test111';

MySQL connection mode

• mysql -uroot -p'test222 'ා directly enter password to connect
• MySQL - uroot - p'test11 '- h127.0.0.1 - p3306 - remote connection mode
• MySQL - uroot - p'test11 '- S / usr / local / MySQL / mysql.sock ා socket connection, only local connection is supported
• MySQL - uroot - p'test11 '- e "show databases" ා after connecting, execute the command through - e

MySQL common commands

• List all databases: show databases;
• Switch Library: use mysql;
• List the tables in the stock out: show tables;
• View the fields in the table: desc user;
• View the statement created by the table: show create table user \ g ා \ g displays the result vertically
• View the current user: select user(); (the result is "user name @ host name"
• View the currently used database: select database();
• Create database test "db0;
• Create table: create table test_tb0(`id` int(4));
• Delete table: drop table test_tb0;
• View database version: select version();
• View the database status: show status;
• View the parameters: Show variables; can fuzzy match show variables like 'max'connect%'; these parameters can be defined in my.cnf
• Modify the parameter set global Max connect errors = 1000; the change only takes effect in memory
• View the queue show processlist; show full processlist;