设置及更改root密码
MySQL数据库里也有个超级管理员用户root，默认密码为空，可以直接连接，但是这样不安全，需要给root设置密码，如果忘记密码，也可以更改密码。
设置root密码：
[root@aliyun ~]# /usr/local/mysql/bin/
mysql -uroot -p
Enter password:
-p指定密码,
目前还没设密码，直接回车，空密码时也可以不加-p选项
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 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 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>
quit
使用quit退出MySQL
Bye
[root@aliyun ~]# /usr/local/mysql/bin/
mysqladmin
-uroot
password
'
test.com
'
修改密码,密码用
' '
括
Warning:
Using a password
on the command line
interface can be insecure.
警告密码被明文显示了
修改root密码：
[root@aliyun ~]# mysqladmin -uroot
-p
'
test.com
'
password
'
123456
'
-p指定旧密码后输入新密码
Warning: Using a password on the command line interface can be insecure.
[root@aliyun ~]#
忘记密码时，更改密码：
[root@aliyun ~]# vim /etc/my.cnf
[mysqld]
skip-grant
在【mysqld】下方插入skip-grant，允许跳过密码
保存退出
[root@aliyun ~]# service mysqld restart
重启mysqld服务
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
[root@aliyun ~]#
[root@aliyun ~]# mysql -uroot
再次进入已经不需要密码了
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.40 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 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> use mysql
;
切换到mysql库，语法用；号
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
数据库已经切换
mysql> select password from user
;
查看用户密码（查看user表的password字段），语法用；号
+-------------------------------------------+
| password |
+-------------------------------------------+
| *FD59AB2FBA8EC13C2DB93D2AEB696E63804AB71 |
| |
| |
| |
| |
| |
+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> select password from user where user=root
;
查看root用户的密码表
ERROR 1054 (42S22): Unknown column 'root' in 'where clause'
语法报错，需要在给用户加上
' '
mysql> select password from user where user=
'
root
'
;
+-------------------------------------------+
| password |
+-------------------------------------------+
| *FD59AB2FBA8EC13C2DA93D2AEB346A630804AB71 |
| |
| |
| |
+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> update user set password=password('123.test') where user='root';
更新root用户密码
Query OK
,
4 rows affected (0.00 sec)
查询成功
Rows matched: 4 Changed: 4 Warnings: 0
匹配4行，更改4行，警告为0
mysql>
quit
退出mysql
[root@aliyun ~]# vim /etc/my.cnf
重新编辑my.cnf
[mysqld]
skip-grant
删除这一行
保存退出
[root@aliyun ~]# service mysqld restart
重启mysqld服务
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
将web网站常用的命令目录加入PATH变量
[root@aliyun ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@aliyun ~]# export PATH=$PATH:/usr/local
/mysql/bin/
:/usr/local
/nginx/sbin
/:/usr/local/
php-fpm/bin
/:/usr/local/
php-fpm/sbin
/:/usr/local/
apache2.4/bin
/
将 MySQL，nginx，php-fpm，apache 的命令目录加入PATH变量
[root@aliyun ~]# vim /etc/profile
在末尾加上：
export PATH=$PATH:/usr/local/mysql/bin/:/usr/local/nginx/sbin/:/usr/local/php-fpm/bin/:/usr/local/php-fpm/sbin/:/usr/local/apache2.4/bin/
保存退出
[root@aliyun ~]# source /etc/profile
引导配置文件
测试直接用mysql命令，不使用绝对路径
[root@aliyun ~]# mysql -uroot -p
重新进入mysql
Enter password:
输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 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 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、mysql -uroot -p‘123456’ 直接输入用户名和密码连接
2、mysql -uroot -p’123456‘ -h127.0.0.1 -P3306 通过ip+port连接远程数据库
3、mysql -uroot -p‘123456‘ -S/tmp/mysql.sock 通过socket连接远程数据库
4、mysql -uroot -p’123456’ -e “show databases” 列出所有数据库，用于shell脚本，监控连接数，-e =execute 执行 命令
例1：通过ip+port连接
[root@aliyun ~]# mysql -uroot
-p
123.test -h127.0.0.1
-P
3306
-p后面密码可不加
‘ ’,
指定端口用大写P，如果不指定端口，默认是3306端口。如果用的不是3306端口，则必须指定。
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 2
Server version: 5.6.40 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 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>
例2：用过服务监听的socket文件连接
[root@aliyun ~]# mysql -uroot -p'123.test' -S/tmp/mysql.sock
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 2
Server version: 5.6.40 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 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>
例3：列出所有数据库
[root@aliyun ~]# mysql -uroot -p'123.test' -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
|
information_schema
|
|
mysql
|
|
performance_schema
|
|
test
|
+--------------------+
[root@aliyun ~]#
mysql常用命令
查询库 show databases;
切换库 use mysql;
这条命令可以不加；号
查看库里的表 show tables;
查看表里的字段 desc tb_name;
查看建表语句 show create table tb_name\G;
查看当前用户 select user();
查看当前使用的数据库 select databsase();
这里不是database
s
创建库 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;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
