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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值