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