MySQl创建用户和授权，mysquldump

深藏阁楼爱情的钟 2023-08-17 17:18 104阅读 0赞

最高权限管理者是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.sqlEnter password: \*\*\*

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

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

1.  \--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

转载于:https://www.cnblogs.com/sundawei7/p/11479401.html