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