linux 学习笔记-052-MySQL-设置更改 root 密码,连接 MySQL,MySQL 的常用命令

发布于 2018-03-22  349 次阅读


设置更改 root 密码

这里的 root 用户是指 mysql 的管理员用户,跟系统的 root 用户没关系

进入 MySQL 操作界面:

[root@am-01:~#] ps -aux | grep mysqld

root       4541  0.0  0.0 112676   980 pts/0    S+   22:17   0:00 grep --color=auto mysqld

[root@am-01:~#] service mysqld start

Starting MySQL.... SUCCESS!

[root@am-01:~#] !ps

ps -aux | grep mysqld

root       4576  0.3  0.1  11776  1552 pts/0    S    22:18   0:00 /bin/sh /usr/local/mysql/bin/mysql_safe --datadir=/data/mysql --pid-file=/data/mysql/am-01.pid

mysql      4714 35.3 45.0 1300776 451924 pts/0  Sl   22:18   0:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-01.err --pid-file=/data/mysql/am-01.pid --socket=/tmp/mysql.sock

root       4744  0.0  0.0 112676   980 pts/0    S+   22:18   0:00 grep --color=auto mysqld

#首先检查一下 mysql 是否启动,没启动的话就启动一下
[root@am-01:~#] export PATH=$PATH:/usr/local/mysql/bin/

[root@am-01:~#] mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

[root@am-01:~#] vim /etc/profile

  export PATH=$PATH:/usr/local/mysql/bin/

[root@am-01:~#] source /etc/profile

#先把 mysql 的绝对路径添加到环境变量 PATH,如果想要永久生效,可以把"export PATH=$PATH:/usr/local/mysql/bin/"添加到/etc/profile 文件的最后面并使用"source /etc/profile"让其生效,之后就可以使用 mysql 命令进入 mysql 操作界面了

设置 root 密码

[root@am-01:~#] mysqladmin -uroot password 'itsupport.0'

Warning: Using a password on the command line interface can be insecure.

[root@am-01:~#] mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@am-01:~#] mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

#使用"mysqladmin"命令设置 root 用户的密码,这时进入 mysql 操作界面的方法就跟之前不同了

修改 root 密码(第一种情况,知道原密码):

[root@am-01:~#] mysqladmin -uroot -p'itsupport.0' password '123456'

Warning: Using a password on the command line interface can be insecure.

[root@am-01:~#] mysql -uroot -p'123456'

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 7

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

#也可以使用"mysqladmin"来改密码,这种方法是在知道 root 密码的情况下

修改 root 密码(第二种情况,不知道 root 密码):

[root@am-01:~#] vim /etc/my.cnf

  [mysqld]

  skip-grant

[root@am-01:~#] service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL... SUCCESS!

[root@am-01:~#] mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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;

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 * from user;

………………内容过多,此处省略………………

mysql> select password from user where user='root';

+-------------------------------------------+

| password                                  |

+-------------------------------------------+

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

|                                           |

|                                           |

|                                           |

+-------------------------------------------+

4 rows in set (0.00 sec)



mysql> update user set password=password('itsupport.0') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4  Changed: 4  Warnings: 0



mysql> quit

Bye

[root@am-01:~#] vim /etc/my.cnf

  [mysqld]

  #skip-grant

[root@am-01:~#] service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 1

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

#第一步,编辑/etc/my.cnf,在[mysqld]下添加 skip-grant 参数,然后重启 mysql 服务,使不需要密码就能进入 mysql 操作界面

#第二步,使用"update user set password=password('新密码') where user='需要修改密码的用户';"来修改指定用户的密码

#第三步,继续编辑/etc/my.cnf,把 skip-grant 参数注释或者删除,重启 mysql 服务,测试使用新的密码进入 mysql 操作界面

#"use mysql;"表示进入 mysql 数据库,"select * from user;"表示查询 user 表,"select password from user where user='root';"表示查询 user 表中的 root 用户的 password 列

连接 MySQL 的几种方式

第一种:mysql -u 用户名 -p'密码'  ------  连接本地 mysql

[root@am-01:~#] mysql -uroot -p'itsupport.0'

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.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

第二种:mysql -u 用户名 -p'密码' -hIP 地址 -P 端口号  ------  连接远端 mysql

[root@am-01:~#] mysql -uroot -p'itsupport.0' -h127.0.0.1 -P3306

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 4

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

#这里自己连接自己,所以 IP 是 127.0.0.1

第三种:mysql -u 用户名 -p'密码' -Ssock 的全路径  ------  通过 sock 连接 mysql

[root@am-01:~#] mysql -uroot -p'itsupport.0' -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 5

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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

Bye

#只适合在本机使用,这时不是使用 TCP/IP 连接 mysql,是使用 sock 连接 mysql

第四种:mysql -u 用户名 -p'密码' -e “命令”  ------  连接 mysql 运行一些命令

[root@am-01:~#] mysql -uroot -p'itsupport.0' -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"命令,这种方式适合在 Shell 脚本中使用

MySQL 常用命令

[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 8

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, 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> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.01 sec)

#查看都有哪些库
mysql> use 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 库
mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| help_category             |

………………省略部分输出信息………………

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

28 rows in set (0.00 sec)

#查看 mysql 库下都有哪些表
mysql> mysql> desc user;

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Field                  | Type                              | Null | Key | Default               | Extra |

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Host                   | char(60)                          | NO   | PRI |                       |       |

| User                   | char(16)                          | NO   | PRI |                       |       |

| Password               | char(41)                          | NO   |     |                       |       |

………………省略部分输出信息………………

| x509_issuer            | blob                              | NO   |     | NULL                  |       |

| x509_subject           | blob                              | NO   |     | NULL                  |       |

| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |

| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |

| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |

| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |

| plugin                 | char(64)                          | YES  |     | mysql_native_password |       |

| authentication_string  | text                              | YES  |     | NULL                  |       |

| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

43 rows in set (0.00 sec)

#查看 user 表都有哪些字段,Field 为字段的名字,Type 字段的格式
mysql> show create table user\G

*************************** 1. row ***************************

       Table: user

Create Table: CREATE TABLE `user` (

  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 ………………省略部分输出信息………………

  `authentication_string` text COLLATE utf8_bin,

  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1 row in set (0.00 sec)

#查看建表语句,"\G"表示竖排显示
mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

#查看当前登录用户
mysql> select database();

+------------+

| database() |

+------------+

| mysql      |

+------------+

1 row in set (0.00 sec)

#查看当前正在使用的库
mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| db1                |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

#创建 db1 库
mysql> use db1;

Database changed

mysql> create table t1(`id` int(4), `name` char(40));

Query OK, 0 rows affected (0.16 sec)

mysql> show create table t1\G;

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `id` int(4) DEFAULT NULL,

  `name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)



ERROR:

No query specified

mysql> desc t1;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(4)   | YES  |     | NULL    |       |

| name  | char(40) | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

#在 db1 库中创建表 t1,t1 表有两列,分别为 id 和 name
mysql> drop table t1;

Query OK, 0 rows affected (0.07 sec)

mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.13 sec)

mysql> show create table t1\G;

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `id` int(4) DEFAULT NULL,

  `name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)



ERROR:

No query specified

#drop 命令删除表 t1,重新创建表 t1,这时指定字符集为 utf8
mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.36    |

+-----------+

1 row in set (0.00 sec)

#查看数据库的版本
mysql> show status;

+-----------------------------------------------+-------------+

| Variable_name                                 | Value       |

+-----------------------------------------------+-------------+

| Aborted_clients                               | 0           |

| Aborted_connects                              | 1           |

| Binlog_cache_disk_use                         | 0           |

| Binlog_cache_use                              | 0           |

………………省略部分输出信息………………

| Threads_created                               | 1           |

| Threads_running                               | 1           |

| Uptime                                        | 7471        |

| Uptime_since_flush_status                     | 7471        |

+-----------------------------------------------+-------------+

341 rows in set (0.01 sec)

#查看数据库状态
mysql> show variables;

………………省略部分输出信息………………

| wait_timeout                                           | 28800                                                                                                                                                                                                                                                                                                                                            |

| warning_count                                          | 0                                                                                                                                                                                                                                                                                                                                                |

+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

453 rows in set (0.00 sec)

#查看各参数,这些参数可以在 my.cnf 里面做定义
mysql> show variables like 'max_connect%';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_connect_errors | 100   |

| max_connections    | 151   |

+--------------------+-------+

2 rows in set (0.00 sec)

#查看指定参数
mysql> set global max_connect_errors=1000;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'max_connect%';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_connect_errors | 1000  |

| max_connections    | 151   |

+--------------------+-------+

2 rows in set (0.00 sec)

#把某个参数的值修改为 1000,临时生效,如果想永久生效,则需要修改/etc/my.cnf
mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

| 10 | root | localhost | NULL | Query   |    0 | init  | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

mysql> show full processlist;

+----+------+-----------+------+---------+------+-------+-----------------------+

| Id | User | Host      | db   | Command | Time | State | Info                  |

+----+------+-----------+------+---------+------+-------+-----------------------+

| 10 | root | localhost | NULL | Query   |    0 | init  | show full processlist |

+----+------+-----------+------+---------+------+-------+-----------------------+

1 row in set (0.00 sec)

#查看队列,有 full 和没 full,的区别是有 full 的显示的信息比较完整

注:

01:mysql 里面的操作命令均已分号";"结尾

02:库由表组成,表是由字段组成

03:可以使用上下方向键选择之前敲过的命令

04:当发现命令输错的时候,可以在命令的前面加上井号"#",再按回车,命令这时是不会生效的

[root@am-01:~#] less .mysql_history

_HiStOrY_V2_

use\040mysql;

select\040*\040from\040user

;

show\040databases;

use\040mysql;

show\040tables;

desc\040user;

show\040create\040table\040user/G;

show\040create\040table\040user\134G;

select\040user();

#这个文件是记录 mysql 的命令历史

扩展

mysql5.7 root 密码更改:

http://www.apelearn.com/bbs/thread-7289-1-1.html

myisam 和 innodb 引擎对比:

http://www.pureweber.com/article/myisam-vs-innodb/

mysql 配置详解:

http://blog.linuxeye.com/379.html

mysql 调优:

http://www.aminglinux.com/bbs/thread-5758-1-1.html

同学分享的亲身 mysql 调优经历:

http://www.apelearn.com/bbs/thread-11281-1-1.html