95. Change the root user password for MySQL, common commands for MySQL basic operation

Change the root user password for MySQL 1, first access 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首次进入数据库使用了绝对路径，直接使用mysql命令是不行的，因为/usr/local/mysql/bin/不再PATH这个环境变量里。还有在首次进入数据库时，密码为空。退出时，输入quit或者exit即可。

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.  Commands end with ; or \g.Your MySQL connection id is 3Server 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>

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 ' sdwaqw '//Change password warning:using a password on the command line interface can be insecure.  [[email protected] ~]# mysql-uroot-psdwaqw//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, forgot root password operation [[email protected] ~]# vim/etc/my.cnf# for ADV Ice on Settings see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# * * * Do 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 (' sdwaqw123456 ') 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 password:no) [[email protected] ~]# mysql-uroot-psdwaqw123456//Login with new password Warning:usin G 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 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:welcome  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 in the table: Desc tb_name; Tb_name represents the field name to view the build statement: Show CREATE TABLE tb_name\g; \g indicates that the current user is viewed by a vertical display (shown more organized): Select User (); View the database currently in use: Select Databsase (); Create library: Create database db1; Creating 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 MyS  QL 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> showdatabases;+--------------------+| 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>

95. Change the root user password for MySQL, common commands for MySQL basic operation