Set MySQL user password (5.6/5.7), remote connection database, common commands

The 13th Chapter MySQL common operation

MySQL version 5.6.35

13.1 setting, changing the root user password

The first direct use of MySQL will prompt ' This command does not exist ' because it has not been added to the environment variable, if you want to use the command, you need to use its absolute path:/usr/local/mysql/bin/mysql, for convenience, first add it to the system environment variable:

[Email protected] ~]# exprt path= $PATH:/usr/local/mysql/bin/
At this point, the MySQL command path is temporarily added to the environment variable, the variable will be invalidated after the system restarts, to be permanently effective, it needs to be added to the environment variable configuration file:

[Email protected] ~]# Vim/etc/profile
......
Export path= $PATH:/usr/local/mysql/bin/

Refresh the configuration file (otherwise does not take effect):
[Email protected] ~]# Source/etc/profile
Set & Change Password

First login mysql,root user no password, login directly:

[Email protected] ~]# Mysql-uroot
#-u:=user, specifying the user name
Welcome to the MySQL Monitor. Commands End With; or \g.
......
Mysql> quit
#退出
Description: After logging in to MySQL you can do some MySQL related actions, but set the MySQL user's password need to do the following actions!

Set Password

[Email protected] ~]# mysqladmin-uroot password ' 123456 '

Log in again:
[Email protected] ~]# Mysql-uroot
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)
Note: After setting the password, login directly will error (error), you need to enter a password to log in.

[Email protected] ~]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor.
Mysql>
Note:-p=passwd, log in with a password, you can enter the password directly on the command line (followed by-p, without spaces:-P ' 123456 ' < here single quotes can be added, but when there are special symbols in the password must be added, so enter the password at the command line habit: Add single quotation marks >), or you can not enter the command line, just follow the-P option, and then follow the prompt message: "Enter Password", enter the password to log in (this method does not expose the user password, security).

Change Password

When you know the user password, make a password change:
[[email protected] ~]# mysqladmin-uroot-p ' 123456 ' password ' 1234567 '

[[email protected] ~]# mysql-uroot-p ' 1234567 '
Welcome to the MySQL Monitor.
Mysql>
The change was successful!

When you forget your password, make a password change:
Edit the MySQL configuration file first:
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Skip-grant
#忽略授权!
Datadir=/data/mysql
Socket=/tmp/mysql.sock

To restart the MySQL service:
[Email protected] ~]#/etc/init.d/mysqld restart
Shutting down MySQL ... success!
Starting MySQL ......... ....... success!
Description: After the completion of the operation can be arbitrary login mysql (no password), so at this time MySQL security is very poor, usually in the configuration file do not add this parameter!!!

[Email protected] ~]# Mysql-uroot
Welcome to the MySQL Monitor.
mysql> use MySQL;
#切换mysql库
Database changed
Mysql> select * from User\g;
#查看用户的表信息, the table contains information about the user (password, authorization ...). ）
#G选项的作用是使输出信息有序显示, without this option, the display will be messy
Mysql> select password from user;
#查看用户密码, Show results wie encrypted string!
mysql> Update user Set Password=password (' 123456 ') where user= ' root ';
Query OK, 4 rows affected (0.11 sec)
Rows Matched:4 Changed:4 warnings:0
#将密码更改为 ' 123456 '
Mysql> quit
Bye
Password changed successfully!

To restore a configuration file:
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/data/mysql
Socket=/tmp/mysql.sock

To restart the MySQL service:
[Email protected] ~]#/etc/init.d/mysqld restart
Shutting down MySQL. success!
Starting MySQL ...... success!

Login:
[[email protected] ~]# mysql-uroot-p ' 123456 '
Welcome to the MySQL Monitor.
Mysql> quit
Bye
finished!
Step: vim/etc/my.cnf--> Add skip-grant-->mysql restart--> login-->use mysql-->update user set password=...-- >vim/etc/my.cnf--> Delete skip-grant-->mysql restart.

13.2 Connecting to MySQL (local, remote)

Remote connection: Using Ip/port connection

[Email protected] ~]# mysql-uroot-p123456-h127.0.0.1-p3306
Welcome to the MySQL Monitor.
Mysql> quit
Bye
Note:-h:=host, specify Ip;-p:=port, specify port.

Local Area Connection: using the socket connection

[Email protected] ~]# Mysql-uroot-p123456-s/tmp/mysql.sock
Welcome to the MySQL Monitor.
Mysql> quit
Bye
Note:-s:=socket, specify the socket. This method applies only to local connections, equivalent to "mysql-uroot-p123456".

Show all databases

[Email protected] ~]# mysql-uroot-p ' 123456 '-e "show Databases"
Warning:using a password on the command line interface can is insecure.
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
Note: This method is used in shell scripts.

13.3 MySQL Common commands

To view library information:

Mark

The following commands need to be executed after switching the library (use MySQL):

Mark

To edit a library:

Mark

Note: The above commands need to be executed under MySQL, with a semicolon at the end of each line of command in MySQL, indicating the end of the line command execution. Tb_name is the table name ().

Example:

[[email protected] mysql]# mysql-uroot-p ' 123456 '
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 5
Server version:5.6.35 MySQL Community Server (GPL)

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

mysql> use MySQL;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| Columns_priv |
| db |
| Event |
| Func |
| Time_zone |
| Time_zone_leap_second |
+---------------------------+
Rows in Set (0.00 sec)

mysql> desc Time_zone;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| time_zone_id | Int (Ten) unsigned | NO | PRI | NULL | auto_increment |
| Use_leap_seconds | Enum (' Y ', ' N ') | NO | | N | |
+------------------+------------------+------+-----+---------+----------------+
2 rows in Set (0.11 sec)

Mysql> Show CREATE TABLE time_zone\g;
#G =grep Filter text content, the law shows
1. Row
Table:time_zone
Create table:create Table time_zone (
Time_zone_idInt (ten) unsigned not NULL auto_increment,
Use_leap_secondsEnum (' Y ', ' n ') not NULL DEFAULT ' N ',
PRIMARY KEY ( Time_zone_id )
) Engine=myisam DEFAULT Charset=utf8 comment= ' time zones '
1 row in Set (0.03 sec)

ERROR:
No query specified

Mysql> Select User ();
+----------------+
| User () |
+----------------+
| [Email protected] |
+----------------+
1 row in Set (0.07 sec)

Mysql> Select Database ();
+------------+
| Database () |
+------------+
| MySQL |
+------------+
1 row in Set (0.00 sec)

Mysql> select * from User\g;
To create a library:
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.02 sec)

To create a table:
mysql> use DB1;
#先切换到指定库下
Database changed
mysql> CREATE TABLE T1 ( id int (4), name char (40));
#括号中是定义字段及字段格式, use back quotation marks.
Query OK, 0 rows affected (1.51 sec)

Mysql> select version ();
+-----------+
| Version () |
+-----------+
| 5.6.35 |
+-----------+
1 row in Set (0.06 sec)

Mysql> Show status;
+-----------------------------------------------+-------------+
| variable_name | Value |
+-----------------------------------------------+-------------+
| aborted_clients | 0 |
| aborted_connects | 0 |
+-----------------------------------------------+-------------+

Mysql> show Variables\g;

Mysql> Show variables like ' max_connect% ' \g;
#like表示匹配;% is a wildcard character

To change the parameters:
mysql> set global max_connect_errors=110;
Query OK, 0 rows affected (0.04 sec)
#在此只是临时更改, if you want to change permanently, you need to edit the configuration file

To view queues:
Mysql> show Processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | Root | localhost | DB1 | Query | 0 | init | Show Processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in Set (0.01 sec)

mysql> drop table T1;
Query OK, 0 rows affected (0.32 sec)

mysql> drop Database db1;
Query OK, 0 rows affected (0.10 sec)
Extension: MySQL5.7 change root password

Unlike the MySQL 5.6 release, the root user password (random) is generated automatically during the installation of MySQL 5.7, so how do I change the root user password after the installation is complete? The steps are as follows:

View the default password

[Email protected] mysql]# Cat/root/.mysql_secret

The random password set for the root Userat Fri Jan 20:00:34 (local time): 3A) 2DdJLkcFP

Change root password: Known default password

Log in with the default password:
[[email protected] mysql]#/usr/local/mysql/bin/mysql-uroot-p ' 3A) 2DdJLkcFP '
Welcome to the MySQL Monitor.
Your MySQL Connection ID is 3
Server version:5.7.17

To set a new password:
Method 1:
mysql> Set Password = password (' 123456 ');
Query OK, 0 rows affected, 1 Warning (0.00 sec)
Method 2:
mysql> SET PASSWORD for ' root ' @localhost = PASSWORD (' 123456 ');
Mysql> quit
Bye
finished!

Change root password: Do not know the default password

To edit a configuration file:
[Email protected] mysql]# VI/ETC/MY.CNF

[Mysqld]
Skip-grant-tables
Datadir=/data/mysql
Socket=/tmp/mysql.sock
#增加参数: Skip-grant-tables

Restart:
[Email protected] mysql]#/etc/init.d/mysqld restart

Login: No password required at this time
[Email protected] mysql]#/usr/local/mysql/bin/mysql-uroot

Change Password:
mysql> Update user Set Authentication_string=password (' 12456 ') where user= ' root ';
Mysql>quit

[Email protected] mysql]# VI/ETC/MY.CNF
[Mysqld]
Datadir=/data/mysql
Socket=/tmp/mysql.sock

Restart:
[Email protected] mysql]#/etc/init.d/mysqld restart

Set MySQL user password (5.6/5.7), remote connection database, common commands