1 用户授权
2 完全备份
3 增量备份
一 用户授权
Grant :添加用户并设置权限
(尤指正式地或法律上)同意,准予,允许;(勉强)承认,同意
n.
(政府、机构的)拨款\
Grant 权限 on 库 to 用户名@ “客户端” identified by “密码”
Grant all on db3.user to @192.168.4.51 identified by ”123456”
1 权限列表:
All
Usage
Select ,update ,insert ,drop -个别权限
Select ,update(字段1 字段2) -指定字段
grant select insert , update,delete, drop db3.* admin2@“localhost” identified by “aaa123…”;
2 库名
*.* 所有库所有表
库.* 一个库
库.表名 一张表
3 客户端
% 所有主机
192.168.4.%
192.168.4.1
Localhost -数据库服务本机
4 用户名
(1)授权时自定义,是要有标示性
(2)存储在mysql 库里的user 表
4案例
mysql> grant all on . to mydba@"%" identified by “123456”
-> with grant option; --给别人授权
客户端访问 mysql -h192.168.4.50 -umydba -p123456
1 Select user() 显示登录用户名\客户端地址
3 Show grants -显示自身访问权限
Grant all on db4.* to userone@”localhost” identifed by “123456”
\4.50\drop \create
虚拟库:infomation_schma
4.50 userone
3 Set password =password(“密码”) -授权用户连接后修改密码
grant select insert , update,delete, drop db3.* admin2@“localhost” identified by “aaa123…”;Select password=password(“aaa123…”)
Mysql -urtoot -paaa123…
44.50操作
Set password for 用户名@"客户端地址" =passord(“密码”)
Set passwoed for userone@localhost “ =password(“aaa123…”);
管理员重置收授权用户连接密码
4,51测试
mysql -h192.168.4.50 -uadmin -paaa123…
授权库(授权信息)
User 用户权限 mysql.user
Db 对数据库 mysql .db
Table _priv 对表权限 mysql.tables_priv
Columns_priv 对字段权限 mysql.columns_priv
数据库权限
(1)desc mysql.db;
(2)select host, db , user from mysql.db
(3)select * from mysql.db where db=“db4” and user=“userone” and host=“localhost” \G;
(4)update mysql.db set delete_priv=“N” where db=“db4” and user=“userone” and host=“localhost” \G;
Flush privileges; 刷新
Show grants for userone@”localhost”;
select * from mysql.db where db=“db4” and user=“userone” and host=“localhost” \G; -查看多出delete 权限
表权限
Desc mysql.tables_priv;
select * from mysql.tables_priv;
select host , db, user , table_name from mysql.tables_priv;
select * from mysql.tables_priv where user=“admin”\G
删除用户
drop user mydba;
select user ,host from mysql.user;
撤销权限
Root 登录
Revoke REVOKE insert ,drop ,update on 库.表名 from 用户匿名@客户端地址";
show grants for yaya @"%";
revoke update on db3.user from yaya@"%";
DBA :职位\
知道-了解\掌握 懂 -精通
二 备份
完全备份
注意权限:/var/lib/mysql 需要mysql:mysql权限
1 root恢复密码
Vim /etc/my.cnf
Skip-grant-tables (其他需要注释掉)
Systemctl restart mysqld
Mysql > update mysql.user set authentication_string=password(“密码”)
Where
User=”root” and host=”localhoset”;
Flush privileges;
Quit
正常启动 -注释skip行
2 命令修改root密码
Mysqladmin -uroot -p password “新密码”
Enter password:旧
Mysqladmin -uroot -p password “123456”
Enter password :13455
Grep password /var/log/mysqld.log
Set global vilidate_password_policy=0
Set global vilidate_password_length=6
Alter user user() identified by “密码”
3 数据备份方式
(1)物理备份
冷被: cp \ tar
50操作
mkdir
Cp -r /var/lib/mysql 备份目录
Tar -zcvf tar -zcvf /root/mysql.tar.gz /var/lib/mysql
Scp
51操作
Systemctl stop mysqld
Rm
Cp -r
Chown -r
Systemctl restart
Mysql -uroot
systemctl stop mysqld.service
cp -r /root/mysqlbak/ /var/lib/mysql.bak
ls /var/lib/mysql.bak
mv /var/lib/mysql.bak /var/lib/mysql
ls /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
ls -ld /var/lib/mysql
ls /var/lib/mysql
systemctl start mysqld.service
mysql -uroot -p123456
(2 )逻辑备份
Mysql \其他软件\mysqldump 生成对应信息
数据备份侧策略
完全备份 所有
增量备份 上次新产生所有
差异 完全后新产生所有
完全备份
Mysqldump -uroot -p密码 -A 库>目录/xxx.sql
完全回复
Mysql -uroot -p 密码 [库] < 目录/xxx.sql
库名
–All-databases \ -A 所有库
-B多个库
mysqldump -uroot -p123456 -A > /dbabak/all.sql
mysqldump -uroot -p123456 -A db3 > /dbabak/db3.sql
mysqldump -uroot -p123456 -A db3.user > /dbabak/db3_user.sql
mysqldump -uroot -p123456 -B db4 db3 > /dbabak/twodn.sql
Ls -lh *.sql
Vim 查看验证
恢复
mysql -uroot -p123456 < /root/twodn.sql
增量备份
完全+增量
完全+差异
增量 -/binlog
1 binlog
(1)二进制
/var/log/muysqld/log
(2)Mysql 日志一种
(3)记录所有sql命令(除了查) -desc -show
Insert select
(4)备份恢复
(5)配置mysql主从同步必要条件
2 启用
Vim /etc/my.cn
[mysqld]
Log-bin=/myload/db5
Server_id=50
Binlog_format=”mixed”
Cd /var/lib/mysql
ls -bin.
3
(1)创建新日志文件
mysql -uroot -p123456 -A --flush-logs db3 > /dbabak/db3.sql sql
Cd /var/lib/mysql
ls -bin.
(2)查看日志文件
过滤
Mysqlbinlog /var/lib/mysql/mysql51-bin.000001 | grep insert
(3)删除
(4)Flush logs 删除日志
Purge master logs to "binlog" 删除编号文件
(4)重置日志
Reset master;
cd /var/lib/mysql
ls -bin.
4 修改日志
Mysql>Show master status
如何查看Mysql数据库当前正在写入的binlog文件
三种
1statement -报表模式
2 row -行模式
3 mixed -混合模式
Show variables like “bin”
Vim /etc/my.cnf
[mysqld]
Server_id=1
Log-bin=mysqldb1
Binlog_format=”mixed”
System restart mysqld
Ls /var/lib/mysql/mysqld-bin.*
Cat
Mysql reset mysqld
Mysql -uroot -p 123456
Mysql> show variables like “binlog_format”
Reset master
Show master status