MySQL common commands, normal login database, reset root password

Change the root password of the MySQL database to modify the root password command to

/usr/local/mysql/bin/mysql -uroot

Defining environment variables temporarily defining environment variables

export PATH=$PATH:/usr/local/mysql/bin/                //临时定义环境变量echo $PATH            //显示环境变量/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/tmp/:/root/bin:/usr/local/mysql/bin/

Permanently define environment variables

vim /etc/profile增加代码export PATH=$PATH:/usr/local/mysql/bin/

source /etc/profile    //生效文件

Log in to MySQL

Specify to use the account root password login,-u after the account,-P can be followed by password;

mysql -uroot -pmysql -uroot -p‘123456789‘

Set Password

Specify a password of 123456 under a blank password

mysqladmin -uroot password ‘123456‘;

Change your password with a password
Old password 123456 modified to 123456789

mysqladmin -uroot -p‘123456‘ password ‘123456789‘

Forgot root password edit configuration file

vim /etc/my.cnf

Add a row under [Mysqld] Skip-grant
Preview

[mysqld]skip-grant    //跳过密码验证datadir=/data/mysqlsocket=/tmp/mysql.sock

Restart MySQL Service

/etc/init.d/mysqld restart

Login MySQL Modify

mysql -urootuse mysql;update user set password=password(‘123456‘) where user=‘root‘;

To cancel a configuration file configuration

vim /etc/my.cnf取消代码skip-grant

Restart MySQL Service

/etc/init.d/mysqld restart

Test validation

mysql -uroot -p‘123456‘使用密码123456登录成功

Connect to MySQL

Common Connection Commands

mysql -uroot -p‘123456‘        //连接本机mysql -uroot -p‘123456‘ -h127.0.0.1 -P3306            //连接远程机器127.0.0.1为ip，3306为端口mysql -uroot -p‘123456‘ -S/tmp/mysql.sock            //使用sock方式连接，只适合本机连接mysql -uroot -p‘123456‘ -e "show databases"           //列出mysql的所有数据库，用于shell命令

Mysql-uroot-p ' 123456 '-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

Commands that must be logged in to MySQL to use
The use of commands must be appended;

Query library show databases;
Switch the library use MySQL;
View the table in the library show tables;
View the fields in the table desc tb_name;
View Build Table statement Show create TABLE tb_name\g;
View the current user Select User ();
View the database you are currently using Select Database ();
Creating a library Create database db1;
CREATE table use DB1; CREATE TABLE T1 ( id int (4), name char (40));
View current database version select version ();
View database status Show status;
View each parameter show variables; Show variables like ' max_connect% ';
Modify parameter set global max_connect_errors=1000;
View queue show Processlist; Show full processlist;

Query Library

mysql> show databases;

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

Switch libraries

mysql> use MySQL;

Database changed

View the table inside the library

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)

View fields inside a table

Note: The format is
desc [table name];
desc User;
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 (one) unsigned |     NO | |       0 | || Max_updates | int (one) unsigned |     NO | |       0 | || max_connections | int (one) unsigned |     NO | |       0 | || max_user_connections | int (one) 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.00 sec) 

View statements that create a table

Show CREATE TABLE user\g;

View Current User

Select User ();

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

View the database currently in use

Select Database ();

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

Create a library

mysql> CREATE DATABASE db1;

Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db1                || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

Create a table

use db1;        //切换到db1库create table t1(`id` int(4),`name` char(40));            //注意：中间使用的是反引号，创建表t1

View Database version

Mysql> select version ();

+-----------+| version() |+-----------+| 5.6.36    |+-----------+1 row in set (0.00 sec)

View database Status

Show status;

View parameters

show variables;show variables like ‘max_connect%‘;        //模糊查询max_connect，后面必须加%set global max_connect_errors=1000;        //修改max_connect_errors参数为1000

View queues

show processlist;show full processlist;        //查看哪些用户在连接数据库（完整版）

MySQL common commands, normal login database, reset root password