MySQL常用操作(1)设置及更改root密码，连接mysql的方式，mysql常用命令

设置及更改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 -p123.test -h127.0.0.1 -P3306 -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();  这里不是databases

创建库 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;