8.22任务

最新推荐文章于 2024-10-10 10:24:33 发布
最新推荐文章于 2024-10-10 10:24:33 发布
阅读量70 收藏
点赞数
文章标签： 开发工具 shell 数据库
原文链接：https://my.oschina.net/u/3866688/blog/1932016
版权

13.1 设置更改root密码

默认mysql的root用户密码为空，可以直接连接。但是这样很不安全，我们需要设置密码，但是可能长时间不用密码丢失，这一节我们学习如何重置mysql的root用户的密码。

首先启动mysql服务。

[root@localhost: ~]# ps aux | grep mysql
root        930  0.0  0.1 115428  1696 ?        S    10:08   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/data/localhost.localdomain.pid
mysql      1191  0.0  4.9 465628 49112 ?        Sl   10:08   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/mysql/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
root       1389  0.0  0.0 112708   972 pts/0    S+   10:30   0:00 grep --color=auto mysql

然后为了方便，把mysql命令路径添加到PATH里。

[root@localhost: ~]# PATH=$PATH:/usr/local/mysql/bin/
[root@localhost: ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.61-log Source distribution

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的交互界面。

mysql> quit
Bye

quit可以退出。

使用mysqladmin设置密码

[root@localhost: ~]# mysqladmin -uroot password 'lhy123'

这时候进入命令行交互模式就需要输入密码了，-p选项。

[root@localhost: ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost: ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.61-log Source distribution

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>

修改密码的话，如果知道原密码 很简单

[root@localhost: ~]# mysqladmin -uroot -p'lhy123' password 'lhy456'
[root@localhost: ~]#

登录的时候可以显式的输入密码，最好加单引号

[root@localhost: ~]# mysql -uroot -p'lhy456'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.61-log Source distribution

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
Bye

如果忘记密码了，我们要重置密码，需要先关闭密码授权。

[root@localhost: ~]# cat /etc/my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

在[mysqld]下面添加一行

skip-grant
[root@localhost: ~]# vim /etc/my.cnf
[root@localhost: ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost: ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.61-log Source distribution

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库，然后查看一下user表

mysql> use mysql;
Database changed
mysql> select * from user;
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host                  | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost             | root | *46F1BC66806FC4863B356CD337D65118EDE11E7A | 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 |        |                       |
| localhost.localdomain | root |                                           | 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 |        |                       |
| 127.0.0.1             | root |                                           | 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 |        |                       |
| ::1                   | root |                                           | 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 |        |                       |
| localhost             |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
| localhost.localdomain |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
6 rows in set (0.03 sec)

这个时候就可能更改password字段了。

mysql> select password from user where user='root';
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *46F1BC66806FC4863B356CD337D65118EDE11E7A |
|                                           |
|                                           |
|                                           |
+-------------------------------------------+
4 rows in set (0.06 sec)

这里的密码是加密的，是用password（）函数生成的。

mysql> update user set password=password('lhy123456') where user='root';
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4  Changed: 4  Warnings: 0

这样密码就更该完成了

[root@localhost: ~]# vim /etc/my.cnf
[root@localhost: ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost: ~]# mysql -uroot -p'lhy123456'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.61-log Source distribution

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
Bye

13.2 连接mysql

链接mysql的方式有以下几种

用户名密码直接连接本机的已经用过了

mysql -uroot -p'lhy123456'

也可以使用socket文件连接本机

[root@localhost: ~]# mysql -uroot -p'lhy123456' -S/tmp/mysql.sock

连接远程的话从A机器连接B机器，可以使用ip：port的方式

[root@localhost: ~]# mysql -uroot -p'lhy123456' -h127.0.0.1 -P3306

连接后返回请求，多用于shell脚本

[root@localhost: ~]# mysql -uroot -p'lhy123456' -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

13.3 mysql常用命令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Database changed

这个是查看数据库，切换数据库的命令。

mysql的命令结尾必须是分号，如果没有结尾按了回车就会等待第二次输入，可以用\c退出。

查看表的字段：

mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| 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  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.62 sec)

这些表的格式我们不需要掌握，这个是开发人员的工作。

查看表的创建，\G 的作用是将查到的结构旋转90度变成纵向

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 '',
  `authentication_string` text COLLATE utf8_bin,
  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等效为分号。如果不加\G会很乱。

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | 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 '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看登录用户

mysql> select user()
    -> \c
mysql> select user() ;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

不妨换一个登录方式

先授权一个登录ip

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.244.128' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
Query OK, 0 rows affected (0.33 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

这样user就变了、

[root@localhost: ~]# mysql -uroot -p'mypassword' -h192.168.244.128 -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.61-log Source distribution

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> select user();
+----------------------+
| user()               |
+----------------------+
| root@192.168.244.128 |
+----------------------+
1 row

命令历史存放在家目录

[root@localhost: ~]# cat ~/.mysql_history 
select * from user
;
select * from user;
desc user;
show databases
;
select * from information_schema
;
select * from information_schema;
select * from performance_schema
;
select * from performance_schema;
 update user set authentication_string=password('123') where user='root';
select * from mysql;
use performance_schema;
select * from user;
use mysql;
select * from user;
select password from user where user='root';
update user set password=password('lhy123456) where user='root';'
;
update user set password=password('lhy123456) where user='root';
update user set password=password('lhy123456') where user='root';
;
;;
'
''
user mysql
;
user mysql;
use mysql;
update user set password=password('lhy123456') where user='root';
update user set password=password('lhy123456') where user='root'
\c
update user set password=password('lhy123456') where user='root' 
show databases;
use mysql;
desc user;
show create table user\G;
show create table user;
select user()
\c
select user() 
select user() ;
select user();
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY 
\c'mypassword' WITH GRANT OPTION; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY  
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.244.128' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
select user();

当前所在的库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

创建一个库

mysql> create database db1;
Query OK, 1 row affected (0.33 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

创建一个表

mysql> use db1;
Database changed
mysql> create table t1(`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.48 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.32 sec)

ERROR: 
No

默认引擎是InnoDB 字符集是utf8

也可以在创建的时候指定

mysql> drop table t1;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 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> select version();
+------------+
| version()  |
+------------+
| 5.5.61-log |
+------------+
1 row in set (0.04 sec)
mysql> show status;

查看各种参数

mysql> show variables;

支持通配

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

也可以修改参数

mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

这个是内存生效。

查看队列

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 13 | root | localhost | db1  | Query   |    1 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.31 sec)


mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 13 | root | localhost | db1  | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

full会显示全部。非常有用的命令。

扩展 
mysql5.7 root密码更改
myisam 和innodb引擎对比
知乎上的答案
mysql 配置详解
mysql调优
同学分享的亲身mysql调优经历

转载于:https://my.oschina.net/u/3866688/blog/1932016

chenglichu0862
关注
TomCat8.22
05-16
1. **Servlet容器**：TomCat作为Servlet容器，其主要任务是解析HTTP请求，并将这些请求转发给相应的Servlet进行处理，再将Servlet的响应返回给客户端。Servlet是Java Web开发的核心组件，用于扩展服务器的功能。 2....
coreutils-8.22.tar.gz
07-19
本文将深入探讨"coreutils-8.22.tar.gz"这个源码包，它是coreutils的一个版本，包含了用于处理日常任务的各种命令源代码。由于原版只提供xz格式，我们这里将其转换为gz格式，以便更广泛地使用和理解。 1. **...
13.1 设置更改root密码 13.2 连接mysql 13.3 mysql常用命令
weixin_34163741的博客
01-13 75
2019独角兽企业重金招聘Python工程师标准>>> ...
8.22
weixin_30340617的博客
08-22 171
curlhttps://www.cnblogs.com/guixiaoming/p/8507268.html -o 保存 -x GET POST 动作 -i http 头 -H自定义头信息传递给服务器 每一个vcpu在宿主机中就是一个进程 宿主机：1 颗 node 6 核cpu vcpu 亲和力 cpuinfo vcpu和cpu实时关系 emulato...
8.22面经
weixin_44320074的博客
08-22 90
事务的持久性：重做日志 原子性：undo log回滚日志 mvcc：rc和rr下提高并发读写 快照读和当前读 mvcc在rr下读事务开始后的第一个快照。rc下读行最新的快照数据。（就记这个算了） 事务ID递增，每一行数据都有对应事务id。 事务第一次快照读生成一致性视图，包括活跃的事务id数组、低水位高水位。 ...
实训笔记8.22
cai_4的博客
08-23 573
用户自定义函数就是我们觉得hive内置函数不满足我们的需求，我们可以自定义函数实现我们想要的功能（Hive底层也都是Java，自定义函数也是编写Java代码的）创建一个Java项目引入编程依赖创建lib目录，自己找jar包放到lib目录下，然后lib目录add as library hive的安装目录的lib目录下使用maven然后根据gav坐标引入依赖编写对应的函数类：UDF、UDTF、UDAF大部分自定义都是UDF和UDTF函数将编写好的Java代码打成jar包。
8.22模拟赛
蒟蒻xjh的博客
08-22 257
T1 有一个不下降序列a1≤a2≤…≤an。bi定义为数列中有多少个数严格小于ai，ci定义为数列中有多少个数严格大于ai。 你的秘书把n个数对{(bi, ci) | 1≤i≤n}打乱顺序抄写在一张纸上。由于她很粗心，致使不少数对被抄错了。 你的任务是：改动最少的数对，使得存在与这些数对对应的原数列{ai | 1≤i≤n}。 输入格式：第一行n(n<=1000),表示n个数对 ...
8.22 Linux基础用户管理
ljb1838757949的博客
08-23 517
假设现在有三个用户：root、xiaoming、xiaowang,三个用户同一时间登录了同一个系统，root去修改配置文件了，xiaoming去创建目录了，xiaowang去访问数据库了，每个用户互不干扰的独立执行自己的任务，并且每个用户不能够越线去访问其他用户正在操作的目录或执行其他用户下的任务，由此可见，不同的用户是拥有各自不同的权限的，Linux通过权限的划分和管理来实现多用户、多任务的运行机制。系统用户 :别称“伪用户”，无法登录系统，其主要作用是支撑系统运行，方便系统管理。
每日小结8.22
08-23 68
今日任务：美化各个页面 今日小结;将七个页面进行了细节设计，敲定了所用的图片素材，墨刀这块的任务已全部完成，并为次日演示做准备 明日任务：完成演示，并初步开始代码编写 转载于:https://www.cnblogs.com/leslie520912/p/11397583.html...
8.22 今日头条笔试
weixin_30869099的博客
08-23 66
这次套路深啊，怎么还有改错题！。 上来看题，每个题目的输入数据都很大，果断上scanf,printf, 千万不能用cin,cout 1. 右上角的点，我的思路是先对每一行去重（题目好像说没有重的点耶！），每一行只有最右边的点才是候选点，然后对这些候选点按照y坐标，从大到小访问，要求相应的x是严格递增的，最后打印输出。 1 #include<bits/stdc++.h...
手把手教你微调GPT-3.5 Turbo[8.22新特性]
刘炫320的博客
08-26 1524
3天前，OpenAI公布了一个新特性，那就是宣布用户可以自定义微调GPT-3.5 Turbo了。这意味着什么呢？这就意味着很多任务都可以先拿GPT-3.5 Turbo进行一个微调作为基准系统了。但是，需要注意的是，这里能够微调GPT-3.5 Turbo应该和之前能够微调GPT-3使用的是相似的技术，也就是参数高效的微调，不然每个用户光保存模型大小，即使是传言中的10B左右的模型，也是很难做到的。那么什么场景比较适合它呢？
ipython-8.22.2.tar.gz
05-26
Python库是一组预先编写的代码模块，旨在帮助开发者实现特定的编程任务，无需从零开始编写代码。这些库可以包括各种功能，如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库，如NumPy、...
ipython-8.22.1.tar.gz
05-26
Python库是一组预先编写的代码模块，旨在帮助开发者实现特定的编程任务，无需从零开始编写代码。这些库可以包括各种功能，如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库，如NumPy、...
ipython-8.22.0.tar.gz
05-26
Python库是一组预先编写的代码模块，旨在帮助开发者实现特定的编程任务，无需从零开始编写代码。这些库可以包括各种功能，如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库，如NumPy、...
旅游管理系统开发新篇章：SpringBoot技术解析
2401_85812053的博客
10-07 554
通过实践让我获得了实践经验。在这次设计中我得到了老师和同学的指导和帮助，使我们认识到团队精神的重要性，因为一个人所学的知识不可能面面俱到的，只有通过合作，发挥自己的优点，体现团队精神，才能使工作做得更为出色。我们开发的是一套常州地方旅游管理系统，我们可以把常州地方旅游管理系统系统配置在远程的服务器上，在得到访问权限之后，只要能够上网就可以使用和访问该系统并进行相关的操作，这样的一种先进模式我们之后只需要对服务器上的程序进行维护即可保证程序的正常使用，大大的提高了工作效率，降低了维护的成本。
IDEA启动项目报错Command line is too long
最新发布
小白
10-10 330
Debug时报Command line is too long
Pycharm里设置关于designer.exe以及pyuic5.exe的外部工具
zhang1379的博客
10-03 791
Pycharm里设置关于designer.exe以及pyuic5.exe的外部工具
JavaFX加载fxml文件几种方法
weiweiqiao的专栏
10-04 164
环境：idea，maven创建JavaFX工程。
台达es 8.22解密
01-02
台达es 8.22是台达公司推出的一款加密设备，其主要功能是保护用户数据的安全。通过对用户数据进行加密处理，台达es 8.22可以有效防止数据被非法使用或窃取。同时，台达es 8.22还具有防止病毒和恶意软件入侵的功能，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值