MySQl创建用户和授权，mysquldump

最高权限管理者是root用户，它拥有着最高的权限操作。包括select、update、delete、update、grant等操作。那么一般情况在公司之后DBA工程师会创建一个用户和密码，去连接数据库的操作，并给当前的用户设置某个操作的权限（或者所有权限）。那么这时就需要来简单了解一下：

如何创建用户和密码
给当前的用户授权
移除当前用户的权限

　　如果想创建一个新的用户，则需要以下操作：

1.进入到mysql数据库下

mysql> use mysql
Database changed

2.对新用户增删改

1.创建用户:
# 指定ip：192.118.1.1的hao用户登录
create user 'hao'@'192.118.1.1' identified by '123';
# 指定ip：192.118.1.开头的hao用户登录
create user 'hao'@'192.118.1.%' identified by '123';
# 指定任何ip的hao用户登录
create user 'hao'@'%' identified by '123';

2.删除用户
drop user '用户名'@'IP地址';

3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

3.对当前的用户授权管理

#查看权限
show grants for '用户'@'IP地址'

#授权 hao用户仅对db1.t1文件有查询、插入和更新的操作
grant select ,insert,update on db1.t1 to "hao"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。hao用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to "hao"@'%';
#hao用户对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "hao"@'%';
#hao用户对所有数据库中文件有任何操作
grant all privileges  on *.*  to "hao"@'%';
 
#取消权限
 
# 取消hao用户对db1的t1文件的任意操作
revoke all on db1.t1 from 'hao'@"%";  

# 取消来自远程服务器的hao用户对数据库db1的所有表的所有权限

revoke all on db1.* from 'hao'@"%";  

取消来自远程服务器的hao用户所有数据库的所有的表的权限
revoke all privileges on *.* from 'hao'@'%';

一 mysqldump指令实现数据备份、mysql指令实现数据还原

　DBA到底是做什么的，百科上说：数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。DBA的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。

　　百科出来的内容总是那么的专业，让人看完之后的感觉是很解释的很好，我认为，DBA主要做三件事情：1.保证公司的数据不丢失不损坏 2.提高数据库管理系统的工作性能

　　对于现在的公司来讲，数据变得尤为重要，可以说最重要，你的网站可以无法访问，服务器可以宕机，但是数据绝对不能丢。

备份表：备份其中的某个表：语法：mysqldump -u 用户名 -p 库名表名> (路径)备份的文件名 mysqldump -uroot -p crm2 student> f:\数据库备份练习\crm2_table_student.sql

单纯进行表备份的时候，就不用写-B参数了，因为库crm2后面就是student表了，也就是说crm2库还在呢

备份多个表：语法：mysqldump -u 用户名 -p 库名表名1 表名2> (路径)备份的文件名

和多个库一起备份有一个同样的问题，就是如果只需要恢复某一张表怎么办，上面的多表备份是不是也不太合适，所以又要进行分表备份又是同样的套路，获取所有的表名，写一个循环脚本，执行单表备份的指令。分库分表备份有些缺点：文件多，很碎，数据量非常大的时候，效率低 1.做一个完整的全备，再做一个分库分表的备份 2.脚本批量恢复多个sql文件。备份数据库表结构：利用mysqldump -d参数只备份表的结果，例如：备份crm2库的所有表的结构：

C:\WINDOWS\system32>mysqldump -uroot -p -B -d crm2> f:\数据库备份练习\crm2stru.sql Enter password: ***

备份出来的文件打开一看，就没有了插入数据的部分

mysqldump的关键参数说明：　　　　　　1.-B指定多个库，增加建库语句和use 语句　　　　　　2.--compact 去掉注释，适合调试输出，生产上不用　　　　　　3.-A或者--all-databases 例如：C:\WINDOWS\system32>mysqldump -uroot -p -B -A> f:\数据库备份练习\all.sql Enter password: ***

　　　　　　4.-F刷新binlog日志　　　　　　5.--master-data 增加binlog日志文件名及对应的为支点。　　　　　　6.-x，--lock-all-tables 将所有的表锁住，一般mysql引擎都是锁表，全部都不能使用了，所有不太友好

　　　　　　7.--add-locks这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作(mysql默认是加上的) 　　　　　　8.-l，--lock-tables Lock all tables for read 　　　　　　9.-d 只备份表结构　　　　　　10.-t 只备份数据

--single-transaction 开启事务，适合innodb事务数据库备份，InnoDB表在备份时，通常启用选项--single-transaction来保证备份的一致性，实际上工作原理时设定本次会话的隔离界别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了数据。

　　　　　　　　MyISAM全库备份指令推荐：(gzip是压缩文件为zip类型的) 　　　　　　　　mysqldump -uroot -p666 -A -B --master-data=2 -x|gzip>f:\数据库备份练习\all.sql.gz 　　　　　　　　InnoDB全库备份指令推荐：　　　　　　　　mysqldump -uroot -p666 -A -B --master-data=2 --single-transaction|gzip>f:\数据库备份练习\all.sql.gz

　　　　数据恢复：

　　一、通过source命令恢复数据库　　　　进入mysql数据库控制台，mysql -uroot -p666登陆后　　　　mysql>use 数据库; 　　　　然后使用source命令，后面参数为脚本文件(如这里用到的是.sql文件，如果你备份的是.txt文件，那这里写.txt文件)

　　　　mysql>source crm2.sql #这个文件是系统路径下的，默认是登陆mysql前的系统路径，在mysql中查看系统路径的方法是通过system+系统命令来搞的　　　　mysql>system ls 　　二、利用mysql命名恢复(标准) 　　　　mysql -root -p666 -e "use crm2;drop table student;show tables;" 必须是双引号　　　　mysql -uroot -p666 crm2<f:\数据库备份练习\crm2.sql 　　　　mysql -uroot -p666 -e "use crm2;show tables;"

　　　　注：如果sql文件里面没有use db这样的字样时，在导入时就要指定数据库名了。

　　　　mysql -uroot -p666 crm2<.sql文件

　　　　建议备份数据库时都指定上-B参数，效果好

　　　　说明：mysql不光可以恢复mysqldump的备份，只要文件中是sql语句，都可以通过mysql命令执行到数据库中

　　　　mysql 带-e参数实现非交互式对话，就是不需要到mysql里面去，在外面执行里面的指令的方法,例如：mysql -uroot -p666 -e "use crm2;show tables;"，但是语句必须是双引号包裹。

　　　　批量恢复库：找到所有的数据库名，然后通过库名去循环恢复

二 MySQL数据备份

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 将表导入到文本文件中。

一、使用mysqldump实现逻辑备份

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

二、恢复逻辑备份

#方法一：
[root@localhost backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

#注：如果备份/恢复单个库时，可以修改sql文件
DROP database if exists school;
create database school;
use school;

三、备份/恢复案例

#数据库备份/恢复实验一：数据库损坏
备份：
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. mysql> set sql_log_bin=0; //模拟服务器损坏
mysql> drop database db;

恢复：
1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 
mysql> source /backup/last_bin.log //恢复最后个binlog文件


#数据库备份/恢复实验二：如果有误删除
备份：
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. drop table db1.t1 //模拟误删除
5. 插入数据 //模拟服务器正常运行

恢复：
1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql 
# mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /tmp/1.log //恢复最后个binlog文件
mysql> source /tmp/2.log //恢复最后个binlog文件

注意事项：
1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）
2. 恢复期间所有SQL语句不应该记录到binlog中

四、实现自动化备份

备份计划：
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本：
[root@localhost~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

手动测试：
[root@localhost ~]# chmod a+x /mysql_back.sql 
[root@localhost ~]# chattr +i /mysql_back.sql
[root@localhost ~]# /mysql_back.sql

配置cron：
[root@localhost ~]# crontab -l
2 * * * /mysql_back.sql

五、表的导出和导入

SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY '\n' ; //定义换行符

mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n';

#可能会报错
mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable


#数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录
在配置文件中
[mysqld]
secure_file_priv='C:\\' #只能将数据导出到C:\\下

重启mysql
重新执行上述语句

六、数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

posted @ 2019-09-07 10:20 天之坚毅阅读(2761) 评论(0) 编辑收藏举报

刷新页面返回顶部

天之坚毅

MySQl创建用户和授权，mysquldump

一 mysqldump指令实现数据备份、mysql指令实现数据还原

二 MySQL数据备份

公告