独角兽企业重金招聘Python工程师标准>>>
[toc]
MySQL用户管理，常用SQL语句，MySQL数据库备份与恢复
扩展 SQL语句教程 /sql/sql-tutorial.html 什么是事务？事务的特性有哪些？ /yenange/article/details/7556094 根据binlog恢复指定时间段的数据 /mysql//0204/4630.html mysql字符集调整 http://xjsunjie./999372/1355013
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top//08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql 相关视频
链接：/s/1miFpS9M 密码：86dx
一、MySQL用户管理
1.创建一个普通用户并授权
首先启动mysql，然后进入
[root@xavi ~]# /etc/init.d/mysqld startStarting MySQL... SUCCESS! [root@xavi ~]# mysql -uroot -pxavilinuxWarning: 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 1Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
all表示所有的权限（如读、写、查询、删除等操作）；创建user用户并授予其所有权限*.（第一个表示所有数据库，第二个*表示所有表） 这里的user1特指localhost上的user1，用户和主机的IP之间有一个@符号 identified by ：设定密码，用单引号括起来。
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
另外，命令中主机IP可以用%替代，表示所有主机
mysql> grant all on *.* to 'user2'@'%' identified by '123456a'; Query OK, 0 rows affected (0.00 sec)
1.1 退出，验证user1是否可以直接登入，因为默认是socket的登入模式，无法登入
mysql> quitBye[root@xavi ~]# mysql -uuser1 -p123456Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
1.2 加上主机ip： mysql -uuser1 -p123456 -h127.0.0.1
[root@xavi ~]# mysql -uuser1 -p123456 -h127.0.0.1Warning: 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 3Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
不加-h的方法把127.0.0.1换成localhost（针对的就是socket，localhost就是虚拟机IP地址192.168.72.130），mysql输入出错时，输入；后接着输quit退出
mysql> grant all on *.* to 'user1'@'localhost' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> quitBye[root@xavi ~]# mysql -uuser1 -p123456 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 5Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
查看用户授权
mysql> show grants //查看当前用户授权-> ;+-----------------------------------------------------------------------------------------------------------------------+| Grants for user1@localhost |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
查看指定用户授权：show grants for user1@'127.0.0.1';
mysql> show grants for user1@'127.0.0.1';+-----------------------------------------------------------------------------------------------------------------------+| Grants for user1@127.0.0.1 |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
授权部分权限(如读、写、查询、插入等)
这一部分开始操作是一只报错
mysql> grant all on db2.* to 'user2'@'192.168.188.129' identified by '111222';ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'db2'mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'db1'mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'db1'mysql> revoke all on *.* from user1@localhost;ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)mysql> revoke all on *.* from 'user1'@'localhost' identified by '123456'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''123456'' at line 1mysql> revoke all on *.* from 'user1'@'localhost' identified by '123456';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''123456'' at line 1mysql> show grants for user1@'127.0.0.1';
错误的原因是自己一直是在user1的数据库可里操作，之前的步骤中进入了“mysql -uuser1 -p123456”这就是陷阱了
正确操作方法是，进入mysql的root用户下操作
mysql> quitBye[root@xavi ~]# mysql -uroot -pxavilinuxWarning: 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 2Server version: 5.6.35 MySQL Community Server (GPL)
授权部分权限(如读、写、查询、插入等)
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';Query OK, 0 rows affected (0.00 sec)
给不同IP做同一个用户的授权，同一密码
mysql> show grants for user2@'192.168.133.1';+------------------------------------------------------------------------------------------------------------------+| Grants for user2@192.168.133.1 |+------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' || GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' |+------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';Query OK, 0 rows affected (0.00 sec)mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2';Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user2@'192.168.133.2';+------------------------------------------------------------------------------------------------------------------+| Grants for user2@192.168.133.2 |+------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' || GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2' |+------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
二、常用SQL语句
2.1 查询语句的两种形式
select count(*) from mysql.user; //查询mysql库中user表的行数
mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 10 |+----------+1 row in set (0.00 sec)
select * from mysql.db\G;//查询mysql库中db表的所有内容
mysql> select * from mysql.db\G;*************************** 1. row ***************************Host: %Db: testUser: Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: Y
2.2 select语句在数据库和表中对应的引擎不一样，其计算统计时间也不同，不建议多使用
例如mysql下的user的表，用的是MYISAM引擎，会自动统计行数，运行效率快
mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table user\G;*************************** 1. row ***************************Table: userCreate Table: CREATE TABLE `user` (`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',...PRIMARY KEY (`Host`,`User`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'1 row in set (0.01 sec)
对于db1下的t1表，其引擎是InnoDB，每次select时才开始统计行，运行效率低。（示例中的表格并未有数据，不能作为参考）
mysql> use db1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)
2.3 查询单个字段或者多个字段
mysql> select db from mysql.db;+---------+| db|+---------+| test || test\_% || db1|| db1|+---------+4 rows in set (0.01 sec)mysql> select db,user from mysql.db;+---------+-------+| db| user |+---------+-------+| test | || test\_% | || db1| user2 || db1| user2 |+---------+-------+4 rows in set (0.00 sec)
2.4 使用万能匹配符%，和like进行模糊匹配查询
mysql> select * from mysql.db where host like '192.168.%';
mysql> mysql> select * from mysql.db where host like '192.168.%'\G;*************************** 1. row ***************************Host: 192.168.133.1Db: db1User: user2Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: NCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N*************************** 2. row ***************************Host: 192.168.133.2Db: db1User: user2
2.5 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.01 sec)mysql> select * from db1.t1;Empty set (0.01 sec)mysql> insert into db1.t1 values (1, 'abc');Query OK, 1 row affected (0.02 sec)mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | abc |+------+------+1 row in set (0.00 sec)
2.6 更改表的某一行
mysql> update db1.t1 set name='aaa' where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | aaa |+------+------+1 row in set (0.00 sec)
2.7 清空某个表的数据，不删除表只清空表的数据
mysql> truncate table db1.t1;Query OK, 0 rows affected (0.04 sec)mysql> select * from db1.t1;Empty set (0.00 sec)
2.8 删除表drop
mysql> drop table db1.t1;Query OK, 0 rows affected (0.03 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
2.9 删除数据库
mysql> drop database db1;Query OK, 0 rows affected (0.00 sec)mysql> select * from db1;ERROR 1046 (3D000): No database selected
三、MySQL数据库备份恢复
备份和恢复MySQL数据库是非常重要的，要牢固掌握
3.1 用mysqldump命令备份，重定向到一个文本文档中.
mysql> quitBye[root@xavi ~]# mysqldump -uroot -pxavilinux mysql > /tmp/mysqlbak.sql;Warning: Using a password on the command line interface can be insecure.
3.2 mysql数据恢复，反向重定向
[root@xavi ~]# mysql -uroot -pxavilinux -e "create database mysql2"Warning: Using a password on the command line interface can be insecure.[root@xavi ~]# mysql -uroot -pxavilinux mysql2 < /tmp/mysqlbak.sqlWarning: Using a password on the command line interface can be insecure.
3.3 进入该数据库，检查
[root@xavi ~]# mysql -uroot -pxavilinux mysql2Warning: Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.6.35 MySQL Community Server (GPL)mysql> select database();+------------+| database() |+------------+| mysql2|+------------+1 row in set (0.00 sec)
备份数据库下的表
mysql> quitBye[root@xavi ~]# mysqldump -uroot -pxavilinux mysql user > /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.[root@xavi ~]# less /tmp/user.sql
less查看
[root@xavi ~]# less /tmp/user.sqlmysql Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86_64) using EditLine wrapperCopyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Usage: mysql [OPTIONS] [database]-?, --helpDisplay this help and exit.-I, --helpSynonym for -?--auto-rehash Enable automatic rehashing. One doesn't need to use'rehash' to get table and field completion, but startupand reconnecting may take a longer time. Disable with--disable-auto-rehash.(Defaults to on; use --skip-auto-rehash to disable.)-A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to gettable and field completion. This gives a quicker start ofmysql and disables rehashing on reconnect.
3.4 恢复某个数据表
[root@xavi ~]# mysql -uroot -pxavilinux mysql2 < /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.
3.5 备份所有的库
[root@xavi ~]# mysqldump -uroot -pxavilinux mysql2 >/tmp/user.sqlWarning: Using a password on the command line interface can be insecure.[root@xavi ~]# less /tmp/mysql_all.sql
3.6 只备份表结构
[root@xavi ~]# mysqldump -uroot -pxavilinux -d mysql2 > /tmp/mysql2.sqlWarning: Using a password on the command line interface can be insecure.[root@xavi ~]# less /tmp/mysql2.sql[root@xavi ~]# less /tmp/mysql2.sql-- MySQL dump 10.13 Distrib 5.6.35, for linux-glibc2.5 (x86_64)---- Host: localhost Database: mysql2-- -------------------------------------------------------- Server version 5.6.35/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `columns_priv`--DROP TABLE IF EXISTS `columns_priv`;/*!40101 SET @saved_cs_client= @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `columns_priv` (`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';/*!40101 SET character_set_client = @saved_cs_client */;---- Table structure for table `db`--DROP TABLE IF EXISTS `db`;/*!40101 SET @saved_cs_client= @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `db` (
