Common commands for setting up a MySQL database password, connecting to a database, and basic operations

First, change the root user password for MySQL 1, to enter the database

[[email protected] ~]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.36 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> quitBye

The first entry into the database uses an absolute path, and the direct use mysql of the command is not possible because /usr/local/mysql/bin/ it is no longer PATH in this environment variable. There is also an empty password when you first enter the database. When exiting, enter quit or be exit ready.

2. Add the MySQL command absolute path to the environment variable

[[email protected] ~]# export PATH=$PATH:/usr/local/mysql/bin    //临时加入环境变量，重启就会失效[[email protected] ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile      //追加到profile文件中，使环境变量永久生效[[email protected] ~]# source /etc/profile     //重新加载配置[[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  

3. Setting and changing the root password

[[email protected] ~]# mysqladmin-uroot password ' 123456 '//in the actual production environment do not set such a simple password warning:using a pas Sword on the command line interface can is insecure.  [[email protected] ~]# mysqladmin-uroot password ' 123456 ' warning:using a password on the command line interface can Be insecure. [[email protected] ~]# mysql-uroot//Set the password, again using the previous login Chitian error Error 1045 (28000): Access denied for user ' root ' @ ' L Ocalhost ' (using Password:no) [[email protected] ~]# mysql-uroot-p123456//Enter the password before logging in, the-p option followed by a password, no spaces warning:u Sing 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 6Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c' To clear the current input statement.mysql> quitbye[[email protected] ~]# mysql-uroot-p//-p no password, in the form of an interactive output  Enter the password entered password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 7Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> exitbye[[email protected] ~]# mysqladmin-uroot-p123456 pas Sword ' zlinux '//Change password warning:using a password on the command line interface can be insecure.  [[email protected] ~]# mysql-uroot-pzlinux//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 9Server version:5.6.36 mysql Community ServeR (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.  Type ' \c ' to clear the current input statement.mysql>

4, forget the root password when the operation

[[email protected] ~]# vim/etc/my.cnf# for advice The change settings please see# Http://dev.mysql.com/doc/ref man/5.6/en/server-configuration-defaults.html# * * * does not EDIT the this FILE.  It's a template which'll be copied to the# * * * default location during install, and would be replaced if you# * * * Upgrade To a newer version of MySQL. [Mysqld]skip-grant//Add the field under [Mysqld] [[email protected] ~]#/etc/init.d/mysqld Res Tart//restart mysqlshutting down MySQL. success! Starting MySQL. success!  [[email protected] ~]# mysql-uroot//Now no password required Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to CLEar the current input statement.mysql> use MySQL; Switch database reading table information for completion of table and column namesyou can turn off this feature to get a quicker st            Artup with-adatabase changedmysql> Update user set Password=password (' zlinux123456 ') where user= ' root ';  Update password query OK, 4 rows Affected (0.00 sec) rows Matched:4 changed:4 warnings:0mysql> exitbye[[email protected]  ~]# vim/etc/my.cnf//delete skip-grant field [[email protected] ~]#/etc/init.d/mysqld restart//restart shutting down MySQL. success! Starting MySQL. success! [[email protected] ~]# mysql-uroot//Cannot login error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using PA Ssword:no) [[email protected] ~]# mysql-uroot-pzlinux123456//Login with new password warning:using a password on the command Li Ne interface can be insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> Exitbye

Second, connect the database

[[email protected] ~]# mysql-uroot-p-h192.168.242.128-p3306//-p Specify port,-H specify IP to sign in Enter password:welcom  E to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 3Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> exitbye[[email protected] ~]# mysql-uroot-p-s/tmp/mysql.s  Ock//Use sock login, only for local connection, equivalent to "mysql-uroot-p123456" Enter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 4Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Names May TrademaRks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> Exitbye

Iii. common commands for basic MySQL operations

Querying all current libraries: show databases;
Switch libraries: use MySQL;
View the table in the library: show tables;
View all fields of the table: Desc tb_name; Tb_name indicates the field name
View Build statement: Show CREATE TABLE tb_name\g; \g indicated by vertical display (more organized)
View Current User: Select User ();
View the database currently in use: Select Databsase ();
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 parameters: Set global max_connect_errors=1000;
View queue: Show Processlist; Show full processlist;

Simple demo:

[[email protected] ~]# mysql-uroot-p-s/tmp/mysql.sockenter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. 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.01 sec) mysql> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> 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) mysql> desc func;+-------+------------ ------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------------------+------+-----+---------+-------+| name | CHAR (64) | NO |         PRI |       | || RET | tinyint (1) |     NO | |       0 | || DL | CHAR (128) |     NO |         |       | || Type | Enum (' function ', ' aggregate ') |     NO | |       NULL | |+-------+------------------------------+------+-----+---------+-------+4 rows in Set (0.00 sec) mysql> Select User ();+----------------+| User () |+----------------+| [email protected] |+----------------+1 row in Set (0.00 sec) mysql> Select Database (); +------------+| Database () |+------------+|  MySQL |+------------+1 row in Set (0.00 sec) mysql>

Set MySQL database password, connect to database, and common commands for basic operations