8.22 13.1-13.3

原创

qq580f435de1a8d 2018-08-23 07:29:06 ©著作权

文章标签 设置更改 root 密码连接 文章分类 运维

©著作权归作者所有：来自51CTO博客作者qq580f435de1a8d的原创作品，请联系作者获取转载授权，否则将追究法律责任

13.1 设置更改root密码

Mysql的root用户为mysql的超级管理员用户，类似linux下的root

也可以创建普通用户连接mysql

默认mysql的root密码为空

操作：

检查mysql服务是否启动

[root@hyc-01-01 ~]# ps aux|grep mysql

root 922 0.0 0.1 115432 1476 ? S 8月21 0:00 /bin/sh /usr/local/mysql/binmysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/hyc-01-01.pid

mysql 1155 0.0 44.7 1302728 451120 ? Sl 8月21 1:25 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=hyc-01-01.err --pid-file=/data/mysql/hyc-01-01.pid --socket=/tmp/mysql.sock

root 2607 0.0 0.0 112724 980 pts/1 S+ 20:31 0:00 grep --color=auto mysql

若未启动则需要启动：

[root@hyc-01-01 ~]# /etc/init.d/mysqld start

Mysql相关命令在/usr/local/mysql/bin路径下，但该路径不在环境变量PATH中

[root@hyc-01-01 ~]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

所以执行mysql相关命令时会报错提示命令未找到

为了能正常执行mysql的命令，需要将该路径加入环境变量PATH

[root@hyc-01-01 ~]# export PATH=$PATH:/usr/local/mysql/bin/

[root@hyc-01-01 ~]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin/ 临时生效（系统重启后失效）

2 永久生效

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

…

done

unset i

unset -f pathmunge

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

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

执行profile文件（改写/etc/profile后若不重新执行则改写的内容不生效）

此时mysql相关命令执行正常

[root@hyc-01-01 ~]# mysql -uroot –p 指定使用root登录mysql，-p参数用于指定密码

Enter password:

…

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 此时已经登入mysql，用户可以在这里操作mysql

mysql> quit quit登出mysql

Bye

首次设置root用户密码：

[root@hyc-01-01 ~]# mysqladmin -uroot password 'hyclinux.1'

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

这里系统认为密码明文显示不安全，所以会出现提示，不是错误，可以不管

再次使用root用户登入mysql：

[root@hyc-01-01 ~]# mysql -uroot

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

未提供密码登入操作被拒绝

再次修改root用户密码：

[root@hyc-01-01 ~]# mysqladmin -uroot -p'hyclinux.1' password 'hyc940421'

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

注意：

需要使用-p参数输入原来的密码

-p参数后紧跟密码，没有空格

通常使用单引号使所有特殊字符去义

在不知道root密码的情况下修改root密码：

修改/etc/my.cnf配置文件：

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

1 [mysqld]

2 skip-grant 忽略授权，即在用户登录mysql时不需要密码

3 federated

4 datadir=/data/mysql

5 socket=/tmp/mysql.sock

…

重启服务：

[root@hyc-01-01 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

无需密码直接登录：

[root@hyc-01-01 ~]# mysql -uroot

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

Your MySQL connection id is 1

Server version: 5.6.39 MySQL Community Server (GPL)

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>

更改root密码：

修改mysql中的mysql库中的user表，这里记录了用户名、密码、权限等信息

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 * from user; 查看mysql库中的信息

mysql> select password from user;

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

| password |

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

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| |

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

6 rows in set (0.00 sec)

在user表中密码信息是加密的字符串，这些加密的字符串由password函数生成，所以修改密码时也需要password函数对密码进行加密

mysql> update user set password=password('123456') where user='root';

设置root密码为123456并使用password函数加密

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0 第4行发生改变

删除/etc/my.cnf中的skip-grant后重启mysql服务测试：

[root@hyc-01-01 ~]# mysql -uroot -p

Enter password: 这里输入123456

…

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

13.2 连接mysql

连接本机

[root@hyc-01-01 ~]# mysql -uroot -p

连接远程

[root@hyc-01-01 ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306

Socket连接（仅本机连接）

[root@hyc-01-01 ~]# mysql -uroot -p123456 -S/tmp/mysql.sock

连接后执行操作

[root@hyc-01-01 ~]# mysql -uroot -p123456 -e "show databases"

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

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

13.3 mysql常用命令

Mysql的中的所有命令需要；作为命令的结尾

查看包含了哪些数据库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 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> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

28 rows in set (0.00 sec)

查看user表中包含的字段

mysql> desc user;

查看mysql数据库中的user表是如何创建的

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',

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions` int(11) unsigned NOT NULL DEFAULT '0',

`max_updates` int(11) unsigned NOT NULL DEFAULT '0',

`max_connections` int(11) unsigned NOT NULL DEFAULT '0',

`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',

`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)

ERROR:

No query specified

显示的内容包括表的名称、字段、每个字段的格式及其他参数，还有表的引擎、字符集和创建表使用的语句

如果不加/G显示的内容会比较混乱

mysql> select * from user;

…

…

| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N |

| hyc-01-01 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N |

| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N |

| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N |

| localhost | | | N | N

0 | 0 | 0 | mysql_native_password | NULL | N |

…

6 rows in set (0.00 sec)

…

mysql> select * from user\G;

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

Host: localhost

User: root

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

…

authentication_string:

password_expired: N

…

*************************** 3. row ***************************

Host: 127.0.0.1

User: root

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

…

authentication_string:

password_expired: N

*************************** 4. row ***************************

Host: ::1

User: root

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

…

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 5. row ***************************

Host: localhost

User:

Password:

…

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

…

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: NULL

password_expired: N

*************************** 6. row ***************************

Host: hyc-01-01

User:

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

…

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: NULL

password_expired: N

6 rows in set (0.00 sec)

ERROR:

No query specified

查看当前用户：

mysql> select user();

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

| user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

当前用户为root，主机名为localhost（即本地）

登出后重新登录：

通过127.0.0.1登录，默认端口3306

[root@hyc-01-01 ~]# mysql -uroot -p123456 -h127.0.0.1

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 10

Server version: 5.6.39 MySQL Community Server (GPL)

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> select user();

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

| user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

登录用户为root，主机名localhost

[root@hyc-01-01 ~]# mysql -uroot -p123456 -h192.168.31.129

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 11

Server version: 5.6.39 MySQL Community Server (GPL)

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> select user();

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

| user() |

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

| root@hyc-01-01 |

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

1 row in set (0.00 sec)

登录用户为root，主机名hyc-01-01

这里ip地址192.168.31.129被反解析到主机名hyc-01-01

Mysql中也可以记录命令历史，使用上下方向键可以查看历史命令（类似linux）

Mysql历史命令记录位置：

[root@hyc-01-01 ~]# ls -a|grep mysql_history

.mysql_history

[root@hyc-01-01 ~]# pwd

/root

Mysql也支持ctrl+L清屏

查看当前使用的数据库：

mysql> select database();

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

| database() |

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

| NULL |

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

1 row in set (0.00 sec)

mysql> use 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 database();

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

| database() |

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

| mysql |

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

1 row in set (0.00 sec)

创建库：

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

mysql> create database test1; 创建库test1

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test1 |

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

5 rows in set (0.00 sec)

创建表：

mysql> use test1

Database changed

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

Query OK, 0 rows affected (0.04 sec)

在数据库test1下创建表test1，表的字段包括id（数字形式，最长为4）和name（字符串形式，最长为40）

mysql> show create table test1\G;

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

Table: test1

Create Table: CREATE TABLE `test1` (

`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

使用了默认引擎InnoDB，默认字符集latin1

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

CHARSET指定字符集为utf-8

ENGINE指定数据库引擎为InnoDB

删除表：

mysql> drop table test1;

Query OK, 0 rows affected (0.03 sec)

重新创建表test1：

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

Query OK, 0 rows affected (0.03 sec)

mysql> show create table test1\G;

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

Table: test1

Create Table: CREATE TABLE `test1` (

`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

此时表使用的字符集为utf8

查看当前数据库版本：

mysql> select version();

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

| version() |

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

| 5.6.39 |

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

1 row in set (0.00 sec)

查看数据库的状态：

mysql> show status;

查看各种参数：

mysql> show variables;

这些参数均可以在my.cnf中定义

指定查看某个或某些参数：

mysql> show variables like 'max_connect%'; 查看匹配max_connect字段的参数（使用%表示通配）

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

| Variable_name | Value |

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

| max_connect_errors | 100 |

| max_connections | 151 |

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

2 rows in set (0.00 sec)

不加%则表示严格匹配

mysql> show variables like 'slow%';

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

| Variable_name | Value |

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

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /data/mysql/hyc-01-01-slow.log |

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

3 rows in set (0.00 sec)

mysql> show variables like 'slow';

Empty set (0.00 sec)

修改参数：

1 可以修改配置文件重启生效，永久有效

[root@hyc-01-01 hyc]# vim /etc/my.cnf

2 命令行修改，使新的值在内存中生效重启失效

mysql> set global max_connect_errors=1000; 原来100

Query OK, 0 rows affected (0.00 sec)

查看队列：

类似linux系统下用ps aux查看

mysql> show processlist;

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

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

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

3 rows in set (0.00 sec)

使用show processlist显示的最后一列（info）可能不完整，使用show full processlist会显示完整

mysql> show full processlist;

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

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

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

3 rows in set (0.00 sec)

上一篇：8.17 12.21-12.24

下一篇：8.23 13.4-13.6

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯