1、mysql修改密码：
root用户时mysql的超级管理员,默认mysql的密码是空的,直接可以连接上去的,不过这样不安全；
注释：为了方便的使用mysql,需要把mysql加入到环境变量里； #后续自己输入mysql可以进入到mysql下；
(1):启动mysql后,使用mysql -uroot命令,会提示mysql命令不存在,这是因为安装的mysql是在/usr/local/mysql/bin/mysql,而这个目录不在环境变量PATH里面,所以会报错,若想要这个命令直接运行,需要把PATH做一个更改；
[root@localhost_001 vhost]# echo $PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin[root@localhost_001 vhost]# export PATH=$PATH:/usr/local/mysql/bin
注释：export声明的变量临时生效,会在重启后失效；
注释：若想变量永久生效,建议PATH=$PATH:/usr/local/mysql/bin写入到/etc/profile文件里,然后执行 source /etc/profile；
(2):进入mysql, 使用mysql -uroot 就可以进入mysql,退出mysql使用quit或者exit即可；
[root@localhost_001 vhost]# mysql -urootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL)Copyright (c) 2000, 2018, 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一般使用 mysql -uroot -p (-p表示指定密码)：密码为空时,直接回车也可进入到mysql；
(3):设置mysql的登录密码；(当前mysql没有密码的情况下设置)； mysqladmin
[root@localhost_001 ~]# mysqladmin -uroot password 'nihao123!'Warning: Using a password on the command line interface can be insecure.
注释：提示是警告信息,是说你现在的命令在当前的命令行显示出来了,这样不太安全；
登录mysql；
[root@localhost_001 ~]# mysqladmin -uroot password 'nihao123!' #要求密码登录；Warning: Using a password on the command line interface can be insecure.[root@localhost_001 ~]# mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@localhost_001 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 5.6.39 MySQL Community Server (GPL)Copyright (c) 2000, 2018, 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):在知道mysql的密码的情况下,去修改mysql的密码；
[root@localhost_001 ~]# mysqladmin -uroot -p'nihao123!' password 'nihao123@'Warning: Using a password on the command line interface can be insecure.
注释：如上格式： mysqladmin -uroot -p'旧密码' password '新密码'
注释：在明文指定密码的时候,密码可以加单引号,也可以不加单引号,建议加上单引号,防止密码有特殊字符的存在(若是不加单引号,而密码中又有中文字符,则会不识别)；
注释：指定新密码登录,当然也可以不明文登录, -p回车输入密码也可以；
[root@localhost_001 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 20Server version: 5.6.39 MySQL Community Server (GPL)Copyright (c) 2000, 2018, 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>
(5):在忘记mysql密码的时候修改mysql的密码；
首先要去mysql的配置文件/etc/my.cnf里加入skip-grant；然后重启mysql服务,再次进入mysql则不需要密码；
skip-grant 表示忽略授权,也就是登录mysql的时候不需要输入密码,可以直接登录；
[root@localhost_001 ~]# vim /etc/my.cnf[mysqld]skip-grantdatadir=/data/mysqlsocket=/tmp/mysql.sock[root@localhost_001 ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
(1):登录mysql,在登录mysql后,还需要更改一个user表(这个表里存放的用户名和密码),这个表在mysql库里面；(进入到这个库)
[root@localhost_001 ~]# mysql -uroot #进入mysql;Welcome to the MySQL monitor. Commands end with ; or \g.mysql> use mysql; #切换mysql库;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql>
(2):查看user表和password表相关信息；
select * from user; -----> 查看user表,会看到很多的乱码信息,这里保存的mysql的用户名和密码等信息；
select password from user user='root'; 针对root用户查看password表；
mysql> select password from user where user='root';+-------------------------------------------+| password |+-------------------------------------------+| *FA22FC2D09A3E4C4F63AAE8F4AE18DD2F3E7E695 || || || |+-------------------------------------------+4 rows in set (0.00 sec)
(3):更改mysql的密码；
update user set password=('111111') where user='root';
密码字段 函数 | 用于加密密码 |为条件语句
mysql> update user set password=password('111111') where user='root';Query OK, 4 rows affected (0.08 sec)Rows matched: 4 Changed: 4 Warnings: 0 注释：提示说4行则表示密码更改完成；可按如下方式验证；
先去my.cnf的配置文件了注释掉skip-grant(授权登录)；(如果不删除,那么所有的用户都不需要输入密码就可以登录,这样不安全)；
重启mysql服务; service msyqld restart
然后用新密码登录验证； mysql -uroot -p111111
这样即成功修改可mysql的密码；
连接mysql的方式, 本地连接 or 远程连接；
本地连接：即使没有使用-S来指定socket,默认也是使用/tmp/mysql.sock连接；
mysql -uroot -p11111 <==========> mysql -uroot -p11111 -S/tmp/mysql.sock
[root@localhost_001 ~]# mysql -uroot -p111111Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye[root@localhost_001 ~]# mysql -uroot -p111111 -S/tmp/mysql.sockWarning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.mysql>
注释：socket方式只能使用本地连接使用；
远程连接： mysql -uroot -p密码 -h[远端mysql主机IP地址] -P[远端mysql端口号] #无先后顺序；
[root@localhost_001 ~]# mysql -uroot -p111111 -h127.0.0.1 -P3306Warning: Using a password on the command line interface can be insecure.mysql> exitBye
假设我们从A机器(192.168.149.130)连接B机器(192.168.149.129)的mysql数据库；
[root@localhost_002 ~]# mysql -h192.168.149.129 -P3306 -p111111Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'192.168.149.130' (using password: YES)
注释：如上连接时提示错误；因为mysql默认是只允许127.0.0.1和localhost登录,所以需要添加允许192.168.149.130授权登录；
在B机器(192.168.149.129)执行如下；
mysql> grant all privileges on *.* TO 'root'@'192.168.149.130' identified by 'nihao123!' with grant option;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; #刷新权限；Query OK, 0 rows affected (0.00 sec)
在A机器(192.168.149.130)远程连接即可；
[root@localhost_002 ~]# mysql -h192.168.149.129 -P3306 -pnihao123!Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
注释：在B机器(192.168.149.129)查看授权的用户；
mysql> select host from user where user='root';+-----------------+| host |+-----------------+| 127.0.0.1 | | 192.168.149.130 || ::1 || localhost || localhost\_001 |+-----------------+6 rows in set (0.00 sec)
注释：目前的只允许本地和远程主机130登录,其余主机都拒绝；
注释：一般为了安全性,mysql主机是本地登录的；
注释：当然如果想让所有用户都连接mysql,可以如下设置；
mysql> grant all privileges on *.* TO 'root'@'%' identified by 'nihao123!' with grant option;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; #刷新权限；Query OK, 0 rows affected (0.00 sec)
1：连接mysql后远程执行命令后退出； mysql -uroot -p111111 -e "show databases;"
[root@localhost_001 ~]# mysql -uroot -p111111 -e "show databases;"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+
远程连接执行命令； mysql -h192.168.149.129 -uroot -pnihao123! -P3306 -e “show databases;”
[root@localhost_002 ~]# mysql -h192.168.149.129 -uroot -pnihao123! -P3306 -e "show databases;"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+
mysql常用命令；
查询库 show databases;切换库 use mysql;查看库里的表 show tables;查看表里的字段 desc tb_name;查看建表语句 show create table tb_name\G;查看当前用户 select user();查看当前使用的数据库 select database();创建库 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;
1:创建库； create databases db1;
mysql> create database db1;Query OK, 1 row affected (0.00 sec)
2:查看有哪些库； show databases;
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || db1 || mysql || performance_schema || test |+--------------------+5 rows in set (0.00 sec)
3:切换库； use mysql;
mysql> use db1;Database changed
4:创建表； create tables 后面加字段；
mysql> create table t1(`id` int(4), `name` char(40));Query OK, 0 rows affected (0.03 sec)
5:查看表里的字段; show create table t1\G; desc user; -------> 查看user表有哪些字段；
mysql> show create table t1\G;*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(4) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedmysql> desc t1\G*************************** 1. row *************************** Field: id Type: int(4) Null: YES Key: Default: NULL Extra: *************************** 2. row *************************** Field: name Type: char(40) Null: YES Key: Default: NULL Extra: 2 rows in set (0.00 sec)
注释：在命令前面加#号则表示取消这个命令；
删除一个表； drop table t1;
mysql> drop table t1;Query OK, 0 rows affected (0.03 sec)
重新创建一个表,并指定字符集为utf-8；
mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)
再次查看会发现字符集变成了utf-8；
mysql> show create table t1\G;*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(4) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified
6:查看当当前所在库； select database();
mysql> select database();+------------+| database() |+------------+| db1 |+------------+1 row in set (0.00 sec)
7:查看当前登录数据库的用户； select user();
mysql> select user();+--------------------+| user() |+--------------------+| root@localhost_001 |+--------------------+1 row in set (0.00 sec)
8:查看当前数据库的版本； select version();
mysql> select version();+-----------+| version() |+-----------+| 5.6.39 |+-----------+1 row in set (0.00 sec)
show status; 列出mysql的数据库状态,会把常用数据都列出来；
9:查看mysql的各个参数(在/etc/my.cnf定义的参数)；
查看mysql定义各个参数： show variables;
查看指定的参数,用like； show variables like 'max_connect%';
mysql> show variables like 'max_connect%';+--------------------+-------+| Variable_name | Value |+--------------------+-------+| max_connect_errors | 100 || max_connections | 151 |+--------------------+-------+2 rows in set (0.00 sec)
10:修改参数,不过仅在内存中生效； set global max_connect_errors=1000;
mysql> show variables like 'max_connect%';+--------------------+-------+| Variable_name | Value |+--------------------+-------+| max_connect_errors | 1000 || max_connections | 151 |+--------------------+-------+2 rows in set (0.00 sec)
注释：若是想重新后生效,可以修改/etc/my.cnf配置文件；
11:查看队列； show processlist; 用来查看有哪些用户在连mysql,在做什么操作,是否有锁表；
mysql> show processlist;+----+------+---------------------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+---------------------+------+---------+------+-------+------------------+| 6 | root | localhost_001:55484 | db1 | Query | 0 | init | show processlist |+----+------+---------------------+------+---------+------+-------+------------------+1 row in set (0.00 sec)
12： show full processlist; #同查看队列命令,不会最后一列会较详细的显示出来；
mysql> show full processlist;+----+------+---------------------+------+---------+------+-------+-----------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+---------------------+------+---------+------+-------+-----------------------+| 6 | root | localhost_001:55484 | db1 | Query | 0 | init | show full processlist |+----+------+---------------------+------+---------+------+-------+-----------------------+1 row in set (0.00 sec)
注释：
mysql5.7 root密码更改 http://www.apelearn.com/bbs/thread-7289-1-1.html
myisam 和innodb引擎对比 http://www.pureweber.com/article/myisam-vs-innodb/ 知乎上的答案 https://www.zhihu.com/question/20596402 mysql 配置详解：https://www.jb51.net/article/48082.htm mysql调优： http://www.aminglinux.com/bbs/thread-5758-1-1.html 同学分享的亲身mysql调优经历： http://www.apelearn.com/bbs/thread-11281-1-1.html