MySQL change root password, connect MySQL, mysql common command introduction

Change the MySQL database root password 1. The first entry into the database is not a password, as follows:

[[email protected] ~] # / usr / local / mysql / bin / mysql -uroot // Use absolute path to enter mysql
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

Note: Exit directly input quit or exit, above we use absolute path into MySQL, this is inconvenient, because/usr/local/mysql/bin is not in the path of this environment variable, so can not directly use the MySQL command, Then we need to add it to the environment variable path in the following way:

[[email protected] ~] # ls / usr / local / mysql / bin / mysql
/ usr / local / mysql / bin / mysql
[[email protected] ~] # echo $ PATH
/ usr / local / sbin: / usr / local / bin: / usr / sbin: / usr / bin: / root / bin
[[email protected] ~] # export PATH = $ PATH: / usr / local / mysql / bin / // Join PATH, but it will be invalid after restart
[[email protected] ~] # mysql -uroot //-u specifies the user to log in, with or without a space after it.
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 2
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>
[[email protected] ~] # vi / etc / profile

Add the following command to the last line:

export PATH = $ PATH: / usr / local / mysql / bin /

[[email protected] ~] # source / etc / profile // Effective immediately

2. Set the root password for MySQL

[[email protected] ~] # mysqladmin -uroot password ‘szyino-123’ // Set password
Warning: Using a password on the command line interface can be insecure.

Warning: The password is exposed under the command line, which is not secure.

3. Log in to MySQL with a password

[[email protected] ~] # mysql -uroot // An error is reported, a password is required to log in
ERROR 1045 (28000): Access denied for user ‘root’ @ ‘localhost’ (using password: NO)
[[email protected] ~] # mysql -uroot -p // Enter the password to log in interactively
Enter password:
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) 2000, 2016, 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> exit
Bye
[[email protected] ~] # mysql -uroot -p’szyino-123 ’// Directly log in with -p followed by a password, and there is no space after -P
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 6
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

4. Change the root password of MySQL

[[email protected] ~] # mysqladmin -uroot -p‘szyino-123 ’password‘ Szyino-123 ‘// Change password
Warning: Using a password on the command line interface can be insecure.
[[email protected] ~] # mysql -uroot -p‘Szyino-123 ’// login with new password
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 8
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

5. Reset Password

• Change the configuration file

[[email protected] ~] # vim /etc/my.cnf

Add the following:

skip-grant

• Restart MySQL

[[email protected] ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[[email protected] ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

• Enter password list Change password

mysql> use mysql; // username and password are stored in the user table, and the user table is stored in the mysql database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user; // View user table
mysql> select password from user where user = ‘root’; // The query statement queries the password table. The encrypted string is generated by the function password
+ ------------------------------------------- +
| password |
+ ------------------------------------------- +
| * EBBC0E0C643D4DC86D226068E9C5A6693BB555A6 |
| |
| |
| |
+ ------------------------------------------- +
4 rows in set (0.01 sec)

mysql> update user set password = password (‘szyino-123‘) where user = ‘root’; // change password command
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

• The vi/etc/my.cnf added skip-grant removed, otherwise all user login does not require a password, unsafe.

[[email protected] ~] # vi /etc/my.cnf // Remove skip-grant
[[email protected] ~] # /etc/init.d/mysqld restart // Restart mysql
Shutting down MySQL .. SUCCESS!
Starting MySQL. SUCCESS!
[[email protected] ~] # mysql -uroot -pszyino-123 // test login mysql with new password
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> quit
Bye

Connecting the MYSQL1. Connecting to the native database

[[email protected] ~]# mysql -uroot -p‘123456‘

2. Remote connection login Mysql,a machine Connection b server MySQL, you need to add IP and port, as follows:

[[email protected] ~] # mysql -uroot -pszyino-123 -h127.0.0.1 -P3306 //-h specifies the IP of the remote host -P specifies the port
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 4
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

3. Using sock remote connection

[[email protected] ~]# mysql -uroot -pszyino-123 -S/tmp/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

Description: MySQL Our machine not only listens to 3306 also listens the sock, therefore may use the sock to log in, but this time is not uses the TCP/IP connection, is uses the sock, only fits in the native. 4. After MySQL is connected, the database table is listed, which is only applicable in shell scripts.

[[email protected] ~]# mysql -uroot -pszyino-123 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

MySQL Common commands

In the daily work, will inevitably encounter some MySQL-related operations, such as building a library, build tables, query MySQL status, and master the most basic operation.

Note: You need to add a semicolon at the end of the MySQL command. 1. Querying the current library

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

2. Switch libraries

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

Database changed

3. Querying the library's tables

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

4. View the fields in the table

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Password               | char(41)                          | NO   |     |                       |       |
| Select_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Insert_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Update_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Delete_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Drop_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Reload_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Shutdown_priv          | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Process_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| File_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Grant_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| References_priv        | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Index_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Alter_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Show_db_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Super_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Execute_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Repl_client_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_view_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Show_view_priv         | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_routine_priv    | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_user_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Event_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Trigger_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
| ssl_type               | enum(‘‘,‘ANY‘,‘X509‘,‘SPECIFIED‘) | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | YES  |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum(‘N‘,‘Y‘)                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.01 sec)

5. View the Build Table statement

mysql> show create table user\G;

6. View Current User

mysql> select user();
+----------------+
| user()         |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

7. View the currently used database

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+

8. Create a library

mysql> create database db1; // Create database
Query OK, 1 row affected (0.00 sec)

mysql> show databases; // View libraries
+ -------------------- +
Database |
+ -------------------- +
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+ -------------------- +
5 rows in set (0.00 sec)

9. Create a table

mysql> use db1; create table t1 (`id` int (4),` name` char (40));
Database changed
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1 \ G; // View the created table
*************************** 1. row ******************** *******
        Table: t1
Create Table: CREATE TABLE `t1` (
   `id` int (4) DEFAULT NULL,
   `name` char (40) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.01 sec)

ERROR:
No query specified
mysql> drop table t1; // Delete table
Query OK, 0 rows affected (0.01 sec)

mysql> use db1; create table t1 (`id` int (4),` name` char (40)) ENGINE = InnoDB DEFAULT CHARSET = utf8; // Specify CHARSET = utf8 when creating the table
Database changed
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1 \ G;
*************************** 1. row ******************** *******
        Table: t1
Create Table: CREATE TABLE `t1` (
   `id` int (4) DEFAULT NULL,
   `name` char (40) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8
1 row in set (0.00 sec)

ERROR:
No query specified

9. View the current database version

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.35    |
+-----------+
1 row in set (0.00 sec)

10. View Database Status

mysql> show status;

11. View each parameter

mysql> show variables; 
mysql> show variables like ‘max_connect%‘;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

12. Modifying parameters

mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘max_connect%‘;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

13. View Database queues

mysql> show processlist; 
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | db1  | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.01 sec)

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 11 | root | localhost | db1  | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

MySQL change root password, connect MySQL, mysql common command introduction