13.4 mysql用户管理
13.5 常用sql语句
13.6 mysql数据库备份恢复
扩展
SQL语句教程
什么是事务？事务的特性有哪些？
根据binlog恢复指定时间段的数据
mysql字符集调整
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份
相关视频
mysql用户管理目录概要
grant all on . to 'user1' identified by 'passwd';
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.180.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
show grants;
show grants for user2@192.168.180.1;
mysql用户管理
场景，为了安全，新建的站点，创建新的用户，或者给予使用已有账户，给予权限
grant all on . to 'user1' identified by 'passwd';
grant 表示 授权
all 表示所有权限，查看，创建，删除等等
on . to 'user1' identified by 'passwd';
若是登录到mysql中后，输错了字符，并按了回车键，直接输入分号 ; 就会推出， 回到mysql的命令行
退出mysql除了使用 quit 命令，还可以使用 exit 命令，还可以ctrl+d快捷键退出
登录到mysql
创建普通用户user1，命令：
grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';——>在输入命令的时候，千万要注意符号，一旦漏失了符号 ' '，那么后面就无法登录到user1的mysql
第一个 * 表示库名，可以写成mysql.* 那就表示对mysql所有的表
'user1'@'127.0.0.1' 指定用户@指定来源IP （指定用户可以写 % 就是通配，表示所有的IP）如果指定了来源IP，那么只能通过来源IP登录
符号*.* 表示所有库，所有表
identified by 'passwd' 指定user1的mysql密码
grant语句，是不会记录到命令历史中的因为不安全
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a'; Query OK, 0 rows affected (0.02 sec)
退出数据库，并尝试user1是否可以登录
[root@yong-01 ~]# mysql -uuser1 -p'123456a' Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
会看到登录失败，因为它默认的是sock，需要指定 -h 指定IP，会看到成功登录到user1的数据库
[root@yong-01 ~]# mysql -uuser1 -p'123456a' -h127.0.0.1
授权localhost，授权本地，用sock去连接
重新登录root，并输入localhost，创建成功后，并退出
grant all on *.* to 'user1'@'localhost' identified by '123456a';
mysql> grant all on *.* to 'user1'@'localhost' identified by '123456a'; Query OK, 0 rows affected (0.00 sec)
这时不加-h 也可以登录到user1了，因为现在授权就是针对localhost，localhost就是针对的sock
[root@yong-01 ~]# mysql -uuser1 -p123456a
对具体的权限去授权
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.180.1' identified by 'passwd';
针对SELECT,UPDATE,INSERT，针对 db1这个库所有的表给用户user2来源IP，并设定密码
grant all on db1.* to 'user3'@'%' identified by 'passwd';
针对所有的IP去授权
show grants; 查看所有的授权
在登录到某一用户下，show grants;会查看到当前用户的权限的
登录user1用户的mysql，去查看授权
[root@yong-01 ~]# mysql -uuser1 -p123456a mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +-----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
show grants for user1@127.0.0.1; 指定用户去查看授权
登录root用户的mysql，然后查看user1用户的mysql的授权
[root@yong-01 ~]# mysql -uroot -p111111 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 '*B012E8731FF1DF44F3D8B26837708985278C3CED' | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
show grants;需求
show grants；看的是root
创建一个用户user2，并做一个授权
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.180.1' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec)
查看user2的授权
show grants for user2@'192.168.180.1';
mysql> show grants for user2@'192.168.180.1'; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.180.1 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.180.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.180.1' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
有一种情况会用到它，比如说，给192.168.180.1做了授权了，但发现一个IP不够，还有一个192.168.180.2，也就是说user2用户不仅需要在192.168.180.1上登录，还需要在192.168.180.2上登录，这时候就需要把授权的命令全部在执行一遍
这时候就可以直接把GRANT USAGE ON *.* TO 'user2'@'192.168.180.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68FF5C8B8F25762BCCEF0'; 复制一遍，将其中192.168.180.1改为192.168.180.2 并在语句结尾加上分号 ;
mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.180.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68FF5C8B8F25762BCCEF0'; Query OK, 0 rows affected (0.00 sec)
然后再将第二行复制GRANT SELECT, INSERT, UPDATE ON
db1.* TO 'user2'@'192.168.133.1' 把IP改为192.168.133.2，并加上分号 ;
mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.180.2'; Query OK, 0 rows affected (0.00 sec)
这时候在来查看show grants查看192.168.133.2
mysql> show grants for user2@'192.168.180.2'; +------------------------------------------------------------------------------------------------------------------+ | Grants for user2@192.168.180.2 | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'192.168.180.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.180.2' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
show grants;会看到同样的密码，同样的用户，唯一改变的就是IP
在知道mysql的用户名，但不知道密码，也可以这样去授权
常用sql语句目录概要
select count(*) from mysql.user;
select * from mysql.db;
select db from mysql.db;
select db,user from mysql.db;
select * from mysql.db where host like '192.168.%';
insert into db1.t1 values (1, 'abc');
update db1.t1 set name='aaa' where id=1;
truncate table db1.t1;
drop table db1.t1;
drop database db1;
常用sql语句
增删改查，就是mysql和其他关系型数据库常用的select语句操作命令
查询语句
首先登录root下的mysql mysql -uroot -p111111
使用db1库 use db1;
查看当前库的所有表show tables;
查看表的行数 select count(*) from mysql.user;
库和表中间有个分割符，就是用点 . 分割
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
就是说user表有10行内容
查看所有的内容 select * from mysql.db;（这样看起来会很乱） ——>可以在后面加上\G，如select * from mysql.db\G;
这里的 * 表示查看所有内容
查看db库的所有内容 select db from mysql.db; 第一个db是字段
mysql> select db from mysql.db; +---------+ | db | +---------+ | test | | test\_% | | db1 | | db1 | +---------+ 4 rows in set (0.01 sec) mysql>
查db字段和user字段 select db,user from mysql.db;
mysql> select db,user from mysql.db; +---------+-------+ | db | user | +---------+-------+ | test | | | test\_% | | | db1 | user2 | | db1 | user2 | +---------+-------+ 4 rows in set (0.00 sec) mysql>
模糊查询 select * from mysql.db where host like '192.168.%'; like 就是模糊匹配
插入语句
查看创建的表
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)
查看db1.t1表的内容，会发现为空 select * from db1.t1;
插入数据到 insert into db1.t1 values (1, 'abc');
插入1, 'abc'到db1.t1表
再来查询db1.t1
mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec)
这样就成功了插入了一条数据，在插入的时候 name 这个字段应该是是一个字符串，字符串需要加上一个单引号 ' ' ，数字可以不加单引号
mysql> insert into db1.t1 values (1, 234); Query OK, 1 row affected (0.01 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | | 1 | 234 | +------+------+ 2 rows in set (0.00 sec)
这里没有做限制，这里id和name都可以是相同的，同一个字段里有相同的数字，相同的值 ，也可以做一些限制，在插入相同的id的时候，就会冲突
update操作
更改db1.t1表 的字符串为name 的数据 和 字符串为id 的数据
update db1.t1 set name='aaa' where id=1;
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | | 1 | aaa | +------+------+ 2 rows in set (0.00 sec)
delete操作
删除db1.t1表 的数据 和 字符串为id 的数据
delete from db1.t1 where id=1;
mysql> delete from db1.t1 where id=1; Query OK, 2 rows affected (0.01 sec) mysql> select * from db1.t1; Empty set (0.00 sec)
truncate清空一个表
清空表数据 truncate table db1.t1;
即使表的数据清空了，但表的字段依旧存在的
mysql> truncate table db1.t1; Query OK, 0 rows affected (0.02 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)
truncate 只是清空的内容，而drop 会清空表的数据并清除表的框架
drop 会把表的框架也丢掉 drop table db1.t1;
mysql> drop table db1.t1; Query OK, 0 rows affected (0.01 sec) mysql> select * from db1.t1; //因为表的架构已经不存在了 ERROR 1146 (42S02): Table 'db1.t1' doesn't exist mysql>
丢掉表 drop database db1;
总结
在使用mysql的时候，少用 * 这样的操作，因为若是一个表里面的内容很多，select count(*)这样操作就会很耗时，浪费资源
数据库中常用引擎是myisam和innodb，默认mysql库里面都是使用的myisam引擎
use db1
show create table t1;
在select count(*)查看表的时候会很快
use mysql;
show create table user\G;
特点：myisam引擎，能自动去统计有多少行
特点：innodb引擎，不会自动统计行数，每次去查询，每次去统计行数，就会很耗时
所以select count(*)这种操作尽量减少，会耗费太多资源
mysql数据库备份恢复目录概要
备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
恢复是，必须保证目录一致
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
mysql数据库备份恢复
备份库
在执行mysqldump -uroot -p111111 mysql的时候会看到很多信息，屏幕上显示的这些就是备份的数据
备份mysql库文件 mysqlbak.sql文件就是mysql的备份库文件
[root@yong-01 ~]# mysqldump -uroot -p111111 mysql > /tmp/mysqlbak.sql Warning: Using a password on the command line interface can be insecure.
我们可以通过mysqlbak.sql来恢复数据库，还可以恢复到另外一个数据库里面去
创建一个新的库mysql2
[root@yong-01 ~]# mysql -uroot -p111111 -e "create database mysql2" Warning: Using a password on the command line interface can be insecure.
恢复库 mysql -uroot -p111111 mysql < /tmp/mysqlbak.sql
[root@yong-01 ~]# mysql -uroot -p111111 mysql < /tmp/mysqlbak.sql Warning: Using a password on the command line interface can be insecure.
进入到数据库里面，在后面加一个mysql2 就会进入到mysql2数据库里面
mysql -uroot -p111111 mysql2
[root@yong-01 ~]# mysql -uroot -p111111 mysql2
查看数据库
mysql> select database(); +------------+ | database() | +------------+ | mysql2 | +------------+ 1 row in set (0.00 sec)
备份表
针对库里面的某一个表去做备份，只需要在 库后面 加上 表名字 即可备份
先库 在表，中间是空格
备份表 mysqldump -uroot -p111111 mysql user > /tmp/user.sql
能看到备份的时候，库存在的话，先把库drop掉，然后创建库，表存在的话，先把表drop掉，然后创建表，然后在一步一步的插入每一行数据
[root@yong-01 ~]# mysqldump -uroot -p111111 mysql user >/tmp/user.sql Warning: Using a password on the command line interface can be insecure.
恢复表的时候，只需要写库的名字，不需要去写表的名字
恢复表 mysql -uroot -p111111 mysql < /tmp/user.sql
恢复mysql2库里面的表
[root@yong-01 ~]# mysql -uroot -p111111 mysql2 < /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
备份所有的库
备份所有库 mysqldump -uroot -p111111 -A >/tmp/mysql_all.sql
-A 表示all所有的意思
[root@yong-01 ~]# mysqldump -uroot -p111111 -A >/tmp/mysql_all.sql Warning: Using a password on the command line interface can be insecure.
只备份表结构 mysqldump -uroot -p111111 -d mysql > /tmp/mysql.sql
不需要表的数据，只需要表的语句
备份mysql2的表结构
[root@yong-01 ~]# mysqldump -uroot -p111111 -d mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
示例
两个机器的库备份，一个库备份到另一台机器上
解决：
首先两台机器能够通信
然后mysqldump -h 远程mysql-ip -uuser-ppassword dbname > /本地backup.sql
这样即可备份
