行走在数据库上的行癫(四)

开源数据库SQL实战(四)

---

Author:行癫
GitHub:https://github.com/blackmed/mysql.git

---

目录：

● 用户管理

● 备份恢复

一：用户管理

MySQL用户管理
1. 登录和退出MySQL
    #mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’
    -h	指定主机名                       【默认为localhost】
    -P	MySQL服务器端口                  【默认3306】
    -u	指定用户名                       【默认root】
    -p	指定登录密码                     【默认为空密码】
	此处mysql为指定登录的数据库 
    -e	接SQL语句  (在脚本中使用)

2. 创建用户
方法一：CREATE USER语句创建
   mysql> create user xingdian;
   mysql> create user diandian@'%' identified by '123456';  这样可以直接从远程登录

方法二： GRANT语句创建(授权)
	mysql> GRANT ALL ON *.* TO 'dian'@’localhost’ IDENTIFIED BY ‘123456’;
	mysql> grant select,insert on k1.* to admin1@'%' identified by '123';
	FLUSH PRIVILEGES;
	查看单独的库权限
	mysql> select * from mysql.db\G
注意：  ALL   单独的权限
       *.*     单独的库和单独的表
       xingdian@localhost  用户有则授权无则创建   
       localhost % 10.19.40.% 10.19.40.11
3. 删除用户
方法一：DROP USER语句删除
	DROP USER 'user1'@’localhost’;

方法二：DELETE语句删除
	DELETE FROM mysql.user WHERE user='user2' AND host=’localhost’;
	FLUSH PRIVILEGES;	 刷新授权表
	
4. 修改用户密码
root修改自己密码
方法一：
    # mysqladmin -uroot -p'123' password 'new_password'	    //123为旧密码

方法二：
   mysql> update mysql.user set authentication_string=password('Qianfeng123!') where user='root' and host='localhost';

方法三：
    给那个用户设置密码，你要在那个用户下执行
    SET PASSWORD=password(‘new_password’);
    上面方法将会在后面的版本remove,使用下面方法
    SET PASSWORD='new_password';  直接设置密码

root修改其他用户密码
方法一：
    mysql> SET PASSWORD FOR user3@'localhost'=password('new_password');
    上面的方法会在将来remove，使用下面的方法：
    mysql> SET PASSWORD FOR user3@'localhost'='new_password';
    
方法二：
    UPDATE mysql.user SET authentication_string=password(‘new_password’)
    	WHERE user=’user3’ AND host=’localhost’;

普通用户修改自己密码
    mysql> SET password=password('new_password');
    mysql> select * from mysql.user\G
	
查看现有的密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%';
参数解释：
1).validate_password_dictionary_file 指定密码验证的文件路径;
2).validate_password_length  密码最小长度
3).validate_password_mixed_case_count  密码至少要包含的小写字母个数和大写字母个数;
4).validate_password_number_count  密码至少要包含的数字个数
5).validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1
    0/LOW：只检查长度;
    1/MEDIUM：检查长度、数字、大小写、特殊字符;
    2/STRONG：检查长度、数字、大小写、特殊字符字典文件。
6).validate_password_special_char_count密码至少要包含的特殊字符数	

二：备份恢复

1.概述

MySQL数据备份

所有备份数据都应放在非数据库本地，而且建议有多份副本。

测试环境中做日常恢复演练，恢复较备份更为重要。

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。

备份过程中必须考虑因素：

​ 1.数据的一致性

​ 2.服务的可用性

逻辑备份：

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

mysqldump

物理备份：

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

tar,cp

xtrabackup 脚本+cron 自动化备份

lvm snapshot

2.tar数据备份(物理)

tar备份数据库
注：备份期间，服务不可用

备份的过程：【完全物理备份】
1. 停止数据库
2. tar备份数据
3. 启动数据库
[root@slave2 ~]# systemctl stop mysqld
[root@slave2 ~]# mkdir /backup
[root@slave2 ~]# cd /var/lib/mysql
[root@slave2 ~]# tar -zcvf /backup/`date +%F`-mysql-all.tar ./*
注：备份文件应该复制其它服务器或存储上

还原的过程：
1. 停止数据库
2. 清理环境
3. 导入备份数据
4. 启动数据库
[root@slave2 ~]# systemctl stop mysqld
[root@slave2 ~]# rm -rf /var/lib/mysql/* 
[root@slave2 ~]# cd /backup
[root@slave2 ~]# tar -xvf /backup/2019-08-20-mysql-all.tar -C /usr/lib/mysql
[root@slave2 ~]# systemctl start mysqld

3.mysqldump数据备份

mysqldump实现逻辑完全备份
数据一致，服务可用
备份表
备份：    # mysqldump -u root -p1 db1 t1  > /db1.t1.sql
恢复：    # mysql -u root -p1 db1  < /db1.t1.sql
 
备份一个库
    # mysqldump -u root -p1 db1 > /db1.sql
备份多个库
    #mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.sql
备份所有的库
    #mysqldump  -u root -p1 -A > /alldb.sql
恢复数据库
    为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志
    因为binlog使用binlog日志恢复数据时也会产生binlog日志
    mysql> set sql_log_bin=0    
    mysql> source  db1.t1.sql
    或者
    #mysql -u root -p1 -D  db1 < db1.t1.sql

常用备份选项：
-A, --all-databases				            
    备份所有库

-B, --databases bbs test mysql 	
    备份多个数据库

4.binlog日志备份

binlog日志方法备份恢复数据
记录每一个操作
默认存储位置 ：
        rpm ： /var/lib/mysql
        编译:   安装目录的var下
        
 产生binlog日志
    一.在启动服务的时候启用日志(临时的)
    # mysqld_safe --log-bin --user=mysql --server-id=1 &
    
    查看binlog日志
     # mysqlbinlog slave2-bin.000001     -v --base64-output=decode-rows
        时间点  ：  141126 14:04:49
        位置点  :     at  106
 方法2.
        show binlog events;        默认查看第一个
        show binlog events in 'mylog.00001';
    二.配置文件(永久修改)
     #vim  /etc/my.cnf
        [mysqld]
        log-bin=mylog 
        server-id=1 //做AB复制的时候使用
     #/etc/init.d/mysqld  restart   
    
 		根据binlog恢复数据
        根据时间点恢复数据
     # mysqlbinlog --start-datetime='2019-07-30 15:45:39'  --stop-datetime='2019-07-30 15:59:10' wing-bin.000001 | mysql -u root -p1

        根据位置点恢复数据@后
     # mysqlbinlog --start-position 106 --stop-position  527 wing-bin.000001 | mysql -u root -p1
   		刷新bin-log日志
     #mysqladmin  flush-logs -u root -p''