8、登录mysql
mysql -urot -p  单实例
 
mysql -uroot -p -S /data/3306/mysql.sock  多实例
 
9、MySQL帮助命令help
mysql> help
 
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
 
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
 
mysql> show databases like "my%";
+----------------+
| Database (my%) |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)
 
 
mysql> show grants for root@localhost; 
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec) 
 
mysql> grant all privileges on *.* to system@'localhost' identified by '123456' with grant option;
增加system用户，并提升为超级管理员，即和root等价的用户，只是名称不同 
 
mysql> delete from mysql.user;   删除所有mysql中的用户 
 
为root账户设置密码方法
[root@centos02 tools]# mysqladmin -uroot password '123456'    没有密码的用户设置密码命令
 
[root@centos02 tools]# mysqladmin -uroot -p'123456' password '654321' -S /data/3306/mysql.sock  适合多实例
 
修改管理员root密码方法1
[root@centos02 tools]# mysqladmin -uroot -p'123456' password 'martin'
[root@centos02 tools]# mysqladmin -uroot -p'654321' password 'martin' -S /data/3306/mysql.sock  适合多实例
 
修改管理员root密码方法2
mysql> update mysql.user set password=password('martin') where user='root';  
mysql> flush privileges;
第一个password代表要修改的字段   第二个password代表是一个函数
此方法适合密码丢失后通过 --skip-grant-tables参数启动数据库后修改密码
 
mysql> select user,host,password from mysql.user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost |                                           |
| root   | centos02  |                                           |
| root   | 127.0.0.1 |                                           |
| root   | ::1       |                                           |
|        | localhost |                                           |
|        | centos02  |                                           |
| system | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-----------+-------------------------------------------+
 
mysql> update mysql.user set password=password('martin') where user='system';      
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
 
mysql> quit
Bye
[root@centos02 tools]# mysql -usystem -pmartin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6ei
Server version: 5.5.49 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 
 
 
找回丢失的mysql密码
1、先停止数据库
[root@centos02 tools]# /etc/init.d/mysqld stop
2、使用 --skip-grant-tables 启动mysql，忽略授权登录验证
[root@centos02 tools]# /application/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &
[root@centos02 tools]# mysql -uroot -p 登录时空密码
 
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';    #修改密码为123456            
Query OK, 0 rows affected (0.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.11 sec)
 
[root@centos02 tools]# mysqladmin -uroot -p123456 shutdown   优雅的关闭数据库
160802 13:01:47 mysqld_safe mysqld from pid file /application/mysql/data/centos02.pid ended
[1]+  Done                    /application/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql
[root@centos02 tools]# 
[root@centos02 tools]# /etc/init.d/mysqld start  
Starting MySQL..                                           [  OK  ]
[root@centos02 tools]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.49 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 
 
 
多实例找回丢失的密码
killall mysqld
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
mysql -uroot -p -S /data/3306/mysql.sock 登录时空密码
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';    #修改密码为123456            
mysql> flush privileges;
 
killall mysqld
/data/3306/mysql start