MySQL day-35 ( 13.1 设置更改root密码, 连接mysql, mysql常用命令, mysql用户管理, 常用sql语句 ,13.6 mysql数据库备份恢复)

13.1 设置更改root密码

不知道root密码情况下更改root密码
vi /etc/my.conf


再次登录就不用密码

select user,host,password,authentication_string from user;

设置更改root密码（这个是mysql5.6的方法）
mysql5.7版本或者mariadb10.3用这种方法：
https://blog.51cto.com/13293070/2400944?source=dra

vi /etc/my.conf

重启服务

13.2 连接MySQL

-h 指定远程 mysql数据库的IP -P 指定端口 默认是3306

13.3 MySQL常用命令

数据库里面的表

查看表里面的字段 field 列为字段名字
















在配置文件中定义，可实现永久生效

查看队列

13.4 MySQL用户管理

一、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’
grant insert on testdb.* to common_user@’%’
grant update on testdb.* to common_user@’%’
grant delete on testdb.* to common_user@’%’
或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

二、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@‘192.168.0.%’;
grant alter on testdb.* to developer@‘192.168.0.%’;
grant drop on testdb.* to developer@‘192.168.0.%’;

三，撤消授权
revoke select,update on zrlog.user from ‘user2’@‘192.168.137.132’ （grant 改为revoke to 改为from）

当前是在root身份下，不加指定用户默认是查看自己的授权

13.5 常用sql语句

select 查找




数据库的两种引擎：myisam 和 innodb
默认是myisam 引擎，特点能够自动统计多少行，查询速度快
innodb 引擎，查询较慢， 尽量减少使用select (*) 语法

insert 插入



update 更新

13.6 MySQL数据库备份恢复

备份库：



show tables; 查看表， mysql 2 库 跟之前的 mysql 库对比相同

备份表：

备份所有库：

less /tmp/mysql_all.sql
/Current Database: 可以查看到所有的库

只备份表结构:

less /tmp/mysql2.sql 会发现没有插入的语句

总结： -A （备份所有的库 ） -d+库（ 备份表结构）
mysql user (备份库里面的表)
mysqldump 针对数据量比较小时备份，当数据量大小几百G或者T时，备份较慢

innobackpex 备份恢复

备份


下载rpm包 扩展源，因为epel源里没有这个包
rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm




备份之前需要创建一个有备份权限的用户
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO ‘bfuser’@’%’ identified by ‘bfuser’;
grant process on . to ‘bfuser’@’%’ identified by ‘bfuser’; 还要加process这个权限


注意 ：1. --defaults-file=/etc/my.cnf 指定mysql的配置文件要放在 innobackupex的后面
2.-S /tmp/mysql.sock 指定mysql.sock 如果不加-S 可以加-h127.0.0.1 如查端口不是3306 还要指定-P

查看备份文件

恢复：


第一步：初始化配置文件，实际还没开始恢复

第二步：恢复数据库

innodbacket 增量备份恢复


在做一下实验，


查看全量备份的文件大小







恢复：

第一次：

第二次

第三次出错了，查看还原的目录 /data/mysql/里没有最后增量备份的lalala数据库

重新在来一次



1.全量备份


第一次变更

第一次增量备份

第二次变更，跟原先目录相比没有了db123, mysql2库

第二次增量备份

现MySQL备份目录里文件：



恢复：
全量恢复，并指定输出日志到文件里

第一次增量备份的恢复

查出/tmp/1.log

第二次增量备份的恢复

还是出错了，原因是： 每二次的增量备份基于第一次的增量备份（不是基于第一次的全量备份）

第3次重新实验：
重新第二次增量备份

第二次增量备份恢复

查看日志没有出错

最后应用正式还原，前面的步骤是在初始化



最后查看，数据库目录没有了db123 和mysql2数据库

总结：
每周周日做全量，剩余六天做增量， 每日在周日的全量的基础上做增量，最后一天实际是增量了7天的数据
恢复的话，只需要恢复最后一次

MariaDB新版本(10.3.x+)的备份和恢复

MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。
原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。
解决方案是，使用mariabackup，它是MariaDB提供的一个开源工具，用于对InnoDB，Aria和MyISAM表进行物理在线备份。
这个工具是基于Percona的XtraBackup（版本2.3.8）的解决方案：

全量备份：
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21

全量恢复：
停止数据库
清空datadir
mariabackup --prepare --target-dir /data/backup/2019-02-21/
mariabackup --copy-back --target-dir /data/backup/2019-02-21/
改属主、属组
chown -R mysql:mysql /data/mysql
启动

增量备份：
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21
第一个增量：mariabackup --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21_inc1 --incremental-basedir /data/backup/2019-02-21
第二个增量：mariabackup --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21_inc2 --incremental-basedir /data/backup/2019-02-21_inc1

增量恢复：
停止数据库
清空原数据库文件，最好改名备份 mv
创建新文件名 mysql
先准备全备
mariabackup --prepare --target-dir /data/backup/2019-02-21 --apply-log-only
准备第一个增量：
cd /data/backup
mariabackup --prepare --target-dir ./2019-02-21 --incremental-dir ./2019-02-21_inc1 --apply-log-only
准备第二个增量：
mariabackup --prepare --target-dir ./2019-02-21 --incremental-dir ./2019-02-21_inc2 --apply-log-only
恢复：
mariabackup --copy-back --target-dir ./2019-02-21

改属主、属组
chown -R mysql:mysql /data/mysql
启动服务

https://github.com/aminglinux/linux2019/blob/master/5.49-%E6%95%B0%E6%8D%AE%E5%A4%87%E4%BB%BD.md