更改MySQL的root用户密码

1、首次进入数据库

[root@sdwaqw ~]# /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、把mysql命令绝对路径加入环境变量

[root@sdwaqw ~]# export PATH=$PATH:/usr/local/mysql/bin    //临时加入环境变量，重启就会失效[root@sdwaqw ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile      //追加到profile文件中，使环境变量永久生效[root@sdwaqw ~]# source /etc/profile     //重新加载配置[root@sdwaqw ~]# 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、设置和更改root密码

[root@sdwaqw ~]# mysqladmin -uroot password '123456'                  //在实际生产环境中请勿设置如此简单的密码Warning: Using a password on the command line interface can be insecure.[root@sdwaqw ~]# mysqladmin -uroot password '123456'Warning: Using a password on the command line interface can be insecure.[root@sdwaqw ~]# mysql -uroot     //设置好密码之后，再次使用之前的登录密令就报错了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@sdwaqw ~]# mysql -uroot -p123456     //输入密码后再登录，-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 6Server 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[root@sdwaqw ~]# mysql -uroot -p     //-p后面不加密码，以交互的形式输入密码Enter 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) 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> exitBye[root@sdwaqw ~]# mysqladmin -uroot -p123456 password 'sdwaqw'   //修改密码Warning: Using a password on the command line interface can be insecure.[root@sdwaqw ~]# mysql -uroot -psdwaqw     //使用新密码登录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) 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>4、忘记root密码时的操作[root@sdwaqw ~]# vim /etc/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the# *** default location during install, and will be replaced if you# *** upgrade to a newer version of MySQL.[mysqld]skip-grant                                     //在[mysqld]下加入该字段[root@sdwaqw ~]# /etc/init.d/mysqld restart      //重启mysqlShutting down MySQL.. SUCCESS!Starting MySQL. SUCCESS![root@sdwaqw ~]# mysql -uroot              //现在就无需密码了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) 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> use mysql;                        //切换数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> update user set password=password('sdwaqw123456') where user='root';            //更新密码Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> exitBye[root@sdwaqw ~]# vim /etc/my.cnf    //删除skip-grant字段[root@sdwaqw ~]# /etc/init.d/mysqld restart   //重启Shutting down MySQL.. SUCCESS!Starting MySQL. SUCCESS![root@sdwaqw ~]# mysql -uroot    //无法登陆了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@sdwaqw ~]# mysql -uroot -psdwaqw123456    //使用新密码登录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 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> exitBye

二、连接数据库

[root@sdwaqw ~]# mysql -uroot -p -h192.168.242.128 -P3306            //-P指定端口， -h指定ip进行登录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) 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> exitBye[root@sdwaqw ~]# mysql -uroot -p -S/tmp/mysql.sock    //使用sock登录，只适用于本地连接，等同于“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) 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> exitBye

三、MySQL基本操作的常用命令

查询当前所有库：show databases;切换库： use mysql;查看库里的表： show tables;查看表所有字段： desc tb_name; //tb_name表示字段名查看建表语句： show create table tb_name\G; //\G表示由竖排显示（示的更加有条理）查看当前用户： select user();查看当前使用的数据库： select databsase();创建库： create database db1;创建表： use db1; create table t1(id int(4), name char(40));查看当前数据库版本： select version();查看数据库状态： show status;查看各参数 ：show variables; show variables like 'max_connect%';修改参数： set global max_connect_errors=1000;查看队列：show processlist; show full processlist;简单演示：[root@sdwaqw ~]# 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) 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> 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 with -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()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)mysql> select database();+------------+| database() |+------------+| mysql      |+------------+1 row in set (0.00 sec)

mysql>