MySQL常用操作——mysqladmin、连接mysql、grant、MyISAM、InnoDB

最新推荐文章于 2023-04-17 19:08:16 发布

weixin_34205826

最新推荐文章于 2023-04-17 19:08:16 发布

阅读量411

点赞数

文章标签：数据库开发工具 python

原文链接：https://my.oschina.net/u/3851633/blog/1844713

版权

2019独角兽企业重金招聘Python工程师标准>>>

MySQL常用操作

一、设置更改root密码

开启mysql服务

[root@ying01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@ying01 ~]# ps aux|grep mysql
root      1645  0.0  0.0 113308  1600 pts/0    S    08:39   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/ying01.pid
mysql     1783 13.5 24.1 1300828 453336 pts/0  Sl   08:39   0:00 /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/ying01.err --pid-file=/data/mysql/ying01.pid --socket=/tmp/mysql.sock
root      1807  0.0  0.0 112720   980 pts/0    S+   08:39   0:00 grep --color=auto mysql

启动mysql命令，需要用绝对路径，或者配置环境；（mysql -uroot 使用root身份启动）

[root@ying01 ~]# mysql -uroot                            //直接启动，找不到mysql命令
-bash: mysql: 未找到命令
[root@ying01 ~]# ls /usr/local/mysql/bin/mysql           //查看命令路径
/usr/local/mysql/bin/mysql
[root@ying01 ~]# /usr/local/mysql/bin/mysql -uroot       //用绝对路径启动，成功启动
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                                              //quit退出
Bye

把mysql命令的目录，添加到环境变量中，此时可以直接用：mysql命令

[root@ying01 ~]# echo $PATH                                   //以下为系统默认的环境变量
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/tmp/:/root/bin
[root@ying01 ~]# 
[root@ying01 ~]# export PATH=$PATH:/usr/local/mysql/bin/      //把mysqld的目录添加到环境变量
[root@ying01 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/tmp/:/root/bin:/usr/local/mysql/bin/
[root@ying01 ~]# 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

把mysql命令的目录，添加到环境变量中；只是临时有效，要永久生效，需要配置文件/etc/profile

[root@ying01 ~]# vim /etc/profile

export PATH=$PATH:/usr/local/mysql/bin/         //在配置文件中，添加此行语句


[root@ying01 ~]# source /etc/profile            //加载配置文件
[root@ying01 ~]# mysql -uroot -p                //此时就可以直接使；-P 使用密码
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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 -uroot password 'www123'

[root@ying01 ~]# mysqladmin -uroot password 'www123'       //设置密码为 www123
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]# mysql -uroot -p           //此时使用mysql命令，需要输入刚才设置的密码；
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 -uroot -p 'www123' password 'www1234' www1234 是修改后的密码；

[root@ying01 ~]# mysqladmin -uroot -p 'www123' password 'www1234'
Enter password: 
mysqladmin: Unknown command: 'www123'
[root@ying01 ~]# mysqladmin -uroot -p'www123' password 'www1234'
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]# mysql -uroot -p'www1234'
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> quit
Bye

在密码丢失的情况下，怎么进入？

第一步：先mysql的主配置文件my.cnf, 新增下面语句

[root@ying01 ~]# vim /etc/my.cnf

[mysqld]
skip-grant                       //新增语句
datadir=/data/mysql
socket=/tmp/mysql.sock

第二步：重启mysqld服务，就直接可以以root用户身份进去：mysql -uroot

[root@ying01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@ying01 ~]# mysql -uroot
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> use mysql;

第三步：使用库的命令：use mysql

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> select password from user;
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *65753B9F60E54A740174E63A9E5E5F9774637276 |
|                                           |
|                                           |
|                                           |
|                                           |
|                                           |
+-------------------------------------------+
6 rows in set (0.00 sec)

第四步：修改密码： update user set password=password('yinglinux') where user='root';

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

mysql> quit
Bye

第五步：退出mysql库后，再次编辑先mysql的主配置文件my.cnf；把之前添加的那一行取消；

[root@ying01 ~]# vim /etc/my.cnf


[mysqld]
#skip-grant               //把这行取消
datadir=/data/mysql
socket=/tmp/mysql.sock

第六步：重启mysql服务，输入新的密码，就完成密码重置

[root@ying01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@ying01 ~]# mysql -uroot -pyinglinux
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>

二、连接MySQL

连接mysql的几种方法：

直接连接：mysql -uroot -pyinglinux

[root@ying01 ~]# mysql -uroot -pyinglinux
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

指定IP，端口连接： mysql -uroot -pyinglinux -h127.0.0.1 -P3306

[root@ying01 ~]# mysql -uroot -pyinglinux -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 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

通过sock连接：mysql -uroot -pyinglinux -S/tmp/mysql.sock

[root@ying01 ~]# ps aux |grep mysql  //先查看sock
root      4813  0.0  0.0 113308  1632 pts/0    S    15:55   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/ying01.pid
mysql     4951  0.3 24.3 1366628 455836 pts/0  Sl   15:55   0:01 /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/ying01.err --pid-file=/data/mysql/ying01.pid --socket=/tmp/mysql.sock
root      5039  0.0  0.0 112720   980 pts/0    S+   16:01   0:00 grep --color=auto mysql
[root@ying01 ~]# mysql -uroot -pyinglinux -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 3
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

shell脚本里面使用：mysql -uroot -pyinglinux -e "show databases"

[root@ying01 ~]# mysql -uroot -pyinglinux -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@ying01 ~]#

三、 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)

mysql>

查看字段

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| 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       |       |
| 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       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_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       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

查看建表语句并列出，命令后加\G

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

查看当前是哪个用户

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

mysql> quit 
Bye

查看在mysql下，使用的命令历史

[root@ying01 ~]# less .mysql_history


_HiStOrY_V2_
use\040mysql;
select\040*\040from\040user;
show\040databases
show\040databases;
use\040mysql;
show\040tables;
desc\040db;
show\040create\040table\040user\134G;
select\040user();
.mysql_history (END)

查看当前所使用的数据库

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

mysql> use mysql;       //切换到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.00 sec)

mysql> use db1;                    //切换到代表db1库
Database changed

创建一个新表

mysql> create table t1(`id` int(4), `name` char(40)); //创建一个新建表
Query OK, 0 rows affected (0.06 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> drop table t1;                   //清空此表
Query OK, 0 rows affected (0.01 sec)

在建表的同时，还可以指定 ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.27 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        //注意此处CHARSET值的变化，与上面
1 row in set (0.01 sec)

ERROR: 
No query specified

查看数据库的版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

查看MySQL的当前状态

mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 2           |
| Binlog_cache_disk_use                         | 0           |
| Binlog_cache_use                              | 0           |
| Binlog_stmt_cache_disk_use                    | 0           |
| Binlog_stmt_cache_use                         | 0           |
| Bytes_received                                | 1234        |
| Bytes_sent                                    | 21089       |
| Com_admin_commands                            | 0           |

查看MySQL的参数

其中很多参数都是可以在/etc/my.cnf中定义，部分参数可在线编辑

mysql> show variables;

模糊查找某参数

mysql> show variables like '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/ying01-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

修改某项参数：set global max_connect_errors=1000;

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

mysql> show variables like 'max_connect_errors';  //查找修改后的参数，此时已经改变
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
+--------------------+-------+
1 row in set (0.00 sec)

查看当前MySQL服务器队列

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 10 | root | localhost | db1  | 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 | db1  | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+

mysql>

四、 MySQL用户管理

4.1 创建用户及登录

创建一个普通用户user1，并授权；

mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'user1'@'%' identified by '123456a'; //百分号%代替主机IP
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

以上语句释义：

all表示所有的权限（如读、写、查询、删除等操作）；创建user用户并授予其所有权限.
第一个星号：表示所有数据库，第二个星号：表示所有表；比如：mysql.table
这里的user1特指localhost上的user1，用户和主机的IP之间有一个@符号
identified by ：设定密码，用单引号括起来。

此时可以使用user1用户身份，并使用密码：123456a登录mysql，但是不能够登录；

[root@ying01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

只能加上参数-h：mysql -uuser1 -p123456a -h127.0.0.1

[root@ying01 ~]# mysql -uuser1 -p123456a -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 12
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，登录mysql，那就需要把IP，改为localhost；

进入 root用户下，把IP 改为 localhost

[root@ying01 ~]# mysql -uroot -pyinglinux
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 15
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> grant all on *.* to 'user1'@'localhost' identified by '123456a';  //把IP改为localhost
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

此时user1用户，不需要指定IP登录；

[root@ying01 ~]# mysql -uuser1 -p123456a 
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 16
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> exit
Bye

4.2 用户授权

进入root用户下，查看当前用户授权

[root@ying01 ~]# mysql -uroot -pyinglinux

mysql> show grants;           //显示当前的用户授权
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*FB4553CE81F8C9590097E0CDB81FC48EC19A6169' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看user1用户授权

mysql> show grants for user1@'127.0.0.1';        //显示user1用户授权
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在创建一个用户user2，并查看其授权；


mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.112.1' identified ed by 'passwd';
Query OK, 0 rows affected (0.00 sec)


mysql> show grants for user2@'192.168.112.1';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.112.1                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.112.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.112.1'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

给不同IP做同一个用户的授权，同一密码

mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.112.2' IDENTIFIED BY PASSWORD  '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';  //此处的密码位上面IP112.1的密码
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user2@'192.168.112.2';  //查看此IP授权情况
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.112.2                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.112.2' IDENTIFIED BY PASSWORD '*FB4553CE81F8C9590097E0CDB81FC48EC19A6169' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.112.2'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

五、常用sql语句

查询mysql库中user表的行数：select count(*) from mysql.user

mysql> use db1;
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 count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

查询mysql库中db表的所有内容：select * from mysql.db\G

mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 3. row ***************************
                 Host: 192.168.112.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 4. row ***************************
                 Host: 192.168.112.2
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
4 rows in set (0.00 sec)

ERROR: 
No query specified

select语句在数据库和表中对应的引擎不一样，其计算统计时间也不同，不建议多使用

MyISAM 引擎

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 create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
 .....省略
 
  `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

InnoDB 引擎

mysql> use db1
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 create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t1    | 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)

查询单个字段或者多个字段

mysql> select db from mysql.db;  //查找db库
+---------+
| db      |
+---------+
| test    |
| test\_% |
| db1     |
| db1     |
+---------+
4 rows in set (0.01 sec)

mysql> select db,user from mysql.db;  //查找db库和user项
+---------+-------+
| db      | user  |
+---------+-------+
| test    |       |
| test\_% |       |
| db1     | user2 |
| db1     | user2 |
+---------+-------+
4 rows in set (0.00 sec)

使用万能匹配符%，和like进行模糊匹配查询

mysql> select * from mysql.db where host like '192.168.%'\G;  //IP模糊处理
*************************** 1. row ***************************
                 Host: 192.168.112.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: 192.168.112.2
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
2 rows in set (0.00 sec)

ERROR: 
No query specified

MySQL中插入数据

mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from db1.t1;       //db1.t1的内容为空
Empty set (0.00 sec)

mysql> insert into db1.t1 values (1,'abc');   //在db1库,t1表中插入：id为1,name为abc的值
Query OK, 1 row affected (0.01 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> insert into db1.t1 values (1,123);
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
|    1 | 123  |
+------+------+
2 rows in set (0.00 sec)

更改表的某一行： update db1.t1 set name='aaa' where id=1;

mysql> update db1.t1 set name='aaa' where id=1;   //在db1库,t1表中,把所有id=1，其name设置为aaa
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from db1.t1;      //查看db1库,t1表内容
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (0.00 sec)

mysql> update db1.t1 set id=5 where name='aaa';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    5 | aaa  |
|    5 | aaa  |
+------+------+
2 rows in set (0.00 sec)

删除数据

用delete删除表内某些数据；

mysql> delete from db1.t1 where id=5;      //在db1库,t1表中，删除id=5的数据
Query OK, 2 rows affected (0.00 sec)

mysql> select * from db1.t1;               //查看表，此时表清空
Empty set (0.00 sec)

mysql> insert into db1.t1 values (1,123);  //在设置一个新表
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;               //里面已经写了数据
+------+------+
| id   | name |
+------+------+
|    1 | 123  |
+------+------+
1 row in set (0.00 sec)

用truncate，直接清空表内数据；

mysql> truncate db1.t1;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from db1.t1;         //清空数据
Empty set (0.00 sec)

mysql> desc db1.t1;                   //查看表架构
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

drop命令，直接删除表以及库的架构，此命令慎用；

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

mysql> select * from db1.t1;                        //此时已经报错，表已经不存在
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> desc db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> drop database db1;                          //删除db1
Query OK, 0 rows affected (0.00 sec)

mysql>

六、MySQL数据库备份恢复

6.1 备份库和恢复库

备份数据库，并把其放到指定目录

[root@ying01 ~]# mysqldump -uroot -pyinglinux mysql > /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1146: Table 'mysql.slave_worker_info' doesn't exist when using LOCK TABLES

此时出现错误：mysqldump: Got error: 1146: Table 'mysql.slave_worker_info' doesn't exist when using LOCK TABLES

slave_worker_info 不存在；

那么先找到 slave_relay_log_info 相关文件；

在 ls /data/mysql/mysql/目录下查看；

发现两个 slave_relay_log_info相关文件，把slave_relay_log_info.frm 删除或者移走；

[root@ying01 ~]# mv /data/mysql/mysql/slave_worker_info.frm /root/NBA/
[root@ying01 ~]# ls /data/mysql/mysql/slave_worker_info.frm 
ls: 无法访问/data/mysql/mysql/slave_worker_info.frm: 没有那个文件或目录

再备份一次；

[root@ying01 ~]# mysqldump -uroot -pyinglinux mysql > /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]#

注意：问题已经解决，但是原因，还需要解析，分析；先暂时，放下；

创建表mysql2数据库

[root@ying01 ~]# mysql -uroot -pyinglinux -e "create database mysql2"
Warning: Using a password on the command line interface can be insecure.

把mysqlbak.sql数据反定向于mysql2

[root@ying01 ~]# mysql -uroot -pyinglinux mysql2 < /tmp/mysqlbak.sql  //注意不是mysqldump
Warning: Using a password on the command line interface can be insecure.

进入mysql2数据库下

[root@ying01 ~]# mysql -uroot -pyinglinux mysql2
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
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> select database();         //查看当前数据库
+------------+
| database() |
+------------+
| mysql2     |
+------------+
1 row in set (0.00 sec)

mysql> show tables;              //查看数据库下数据表
+---------------------------+
| Tables_in_mysql2          |
+---------------------------+
| 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      |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
27 rows in set (0.00 sec)

更换到数据库mysql下，发现数据表和mysql2下的表一样；

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      |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
27 rows in set (0.00 sec)

mysql> quit
Bye

6.2 备份表和恢复表

[root@ying01 ~]# mysqldump -uroot -pyinglinux mysql user > /tmp/user.sql   //先备份,用mysqldump
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]# mysql -uroot -pyinglinux mysql2 < /tmp/user.sql           //再取出，注意不是mysqldump
Warning: Using a password on the command line interface can be insecure.

6.3 备份所有库

[root@ying01 ~]# mysqldump -uroot -pyinglinux -A > /tmp/mysql_all.sql    //-A，表示全部备份
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]# less /tmp/mysql_all.sql

6.4 备份表结构

[root@ying01 ~]# mysqldump -uroot -pyinglinux -d mysql2 > /tmp/mysql2.sql   //使用-d选项
Warning: Using a password on the command line interface can be insecure.
[root@ying01 ~]# less /tmp/mysql2.sql

转载于:https://my.oschina.net/u/3851633/blog/1844713