一。Centos7解压安装Mysql5.7

1.在服务器上面下载mysql5.7源码包到服务器本地

1
/]$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-el7-x86_64.tar

2.查看是否安装mariadb和mysql，有安装就将其卸载和删除

1
2
/]$ rpm -qa |grep mysql
/]$ yum install -y libaio
1
2
3
4
5
6
7
3.查看mysql用户和组是否存在，不存在就创建
# 查看
/]$ cat /etc/passwd | grep mysql
/]$ cat /etc/group | grep mysql
# 创建
/]$ groupadd  mysql
/]$ useradd -s /sbin/nologin -M -r --G mysql  mysql

4.解压mysql源码包并将解压后的目录移动到/usr/local目录下更名为mysql

1
2
3
/]$ tar xzvf mysql-5.7.27-el7-x86_64.tar.gz
/]$ tar xzvf mysql-5.7.27-el7-x86_64.tar.gz
/]$ mv mysql-5.7.27-el7-x86_64 /data/mysql

5.更改mysql目录的属主和属组为mysql

1
/]$ chown -R mysql:mysql /data/mysql

6.创建mysql日志保存目录并更改属主为mysql

1
2
/]$ mkdir -p /var/log/mysql/
/]$ chown  mysql /var/log/mysql/

7.修改mysql配置文件添加数据目录日志目录位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/]$ vim /etc/my.cnf

[mysqld]
basedir = /data/mysql
datadir = /data/mysql/data
socket=/tmp/mysql.sock

[client]
port = 3306
default-character-set = utf8
socket=/tmp/mysql.sock

[mysql]
port = 3306
default-character-set = utf8
socket = /tmp/mysql.sock

8.初始化Mysql并配置根目录与数据目录

1
2
3
4
5
6
7
8
9
/]$ ./bin/mysqld --initialize --initialize-insecure --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
2019-07-25T02:17:26.926972Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-25T02:17:27.873911Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-25T02:17:27.989175Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-25T02:17:28.055380Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 59691eec-ae82-11e9-a601-525400f8d66e.
2019-07-25T02:17:28.060887Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-25T02:17:28.061415Z 1 [Note] A temporary password is generated for root@localhost: e7TbpjDNQb?a		//记住此处的密码

# 如果忘记密码使用此命令查看：# cat /root/.mysql_secret

9.复制mysql启动脚本至/etc/init.d目录下并启动mysql服务

1
2
3
4
/]$ cp /data/mysql/support-files/mysql.server  /etc/init.d/mysql
/]$ chkconfig  --add mysql
/]$ systemctl enable mysql
/]$ systemctl restart mysql	        # 重启报错检查日志、数据、pid目录是否存在与权限问题或者my.cnf配置文件参数是否正确

10.优化mysql可执行路径

1
/]$ ln -s /data/mysql/bin/* /usr/bin/

11.mysql5.7第一次登陆不能执行任何操作，需要修改root密码后才可以执行操作

1
2
3
4
5
6
7
/]$ mysqladmin  -uroot -p password 'newpasswd'
Enter password:         # 输入新密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.	    // 此提示可以忽略，是说直接输入密码不安全

sql> ALTER USER USER() IDENTIFIED BY 'putianhui';        # 修改新密码为putianhui
sql> flush privileges;

二。密码相关操作

2.1 update方式更改密码

1
2
3
mysql> update mysql.user set password=password('123123') where user='root';
													# 将root用户的密码更改为123123
mysql> flush privileges;					# 刷新用户权限信息

2.2 mysqladmin方式更改密码

1
2
[root@localhost ~]$ mysqladmin -u root -p'旧密码' password '新密码'
mysqladmin -u root password 123123					# 更改root用户的密码

2.3 忘记root密码找回

1
2
3
4
5
6
7
8
[root@localhost /]$ vim /etc/my.cnf			# 修改mysql配置文件
---------------------------------------添加下行内容----------------------------------
skip-grant-tables					# 跳过密码验证
---------------------------------------------------------------------------------------
[root@localhost /]$ systemctl restart mysqld		# 重启mysql服务
[root@localhost /]$ mysql –uroot			# 重新使用root登陆

## 注意：如果更改密码后还是无法登陆，就把user表中user字段和password字段为空的行记录删除，然后刷新权限信息再重试

三。Mysql常用操作

3.1 查看数据库

1
mysql> show databases;			# 查看数据库列表

3.2 查看表

1
2
mysql> use ceshi;				#切换到ceshi数据库
mysql> show tables;				#查看ceshi数据库都有哪些表

3.3 查看表结构

1
2
3
4
5
方法一：
mysql> describe mysql.user;			#查看mysql数据库user表的结构
方法二：
mysql> use mysql				#切换到mysql数据库
mysql> describe user;				#查看user表的结构

3.4 创建数据库

1
mysql> create database ceshi;			#创建一个数据库，名为ceshi

3.5 创建表

1
2
3
语法：create table 表名(字段1名称 类型,字段2名称 类型,primary key(主键名));
mysql> create table biao1 (name char(16) not null,passwd char(16) not null,primary key(name));			
# 创建表名称为biao1，有name列、passwd列，将name列设置为主键

3.6 删除一个表

1
2
3
4
5
方法一：
mysql> drop table ceshi.biao1;			#删除ceshi库中的biao1
方法二：
mysql> use ceshi;				#切换到ceshi数据库
mysql> drop table biao1;			#删除biao1

3.7 删除数据库

1
mysql> drop database ceshi;				#删除ceshi数据库

3.8 插入数据

1
2
3
语法： insert into 表名(字段1,字段2,) values(字段1的值,字段2的值)
mysql> insert biao1 (name,passwd) values('lisi','123');	
#向biao1中插入一条记录name为lisi，passwd为123

3.9 查询数据

1
2
3
语法：select 字段名1,字段名2 from 表名 where 条件表达式
mysql> select name,passwd from biao1 where name='lisi';
#查询biao1中name是lisi的name和passwd信息

3.10 修改数据

1
2
3
语法： update 表名 set 字段1名=字段1更改后的值 where 条件表达式
mysql> update ceshi.biao1 set passwd='456' where name='lisi';
#将biao1中name是lisi的passwd更改为456

3.11删除数据记录

1
2
3
# 不带条件表达式是删除所有记录
语法：delete from 表名where 条件表达式
mysql> delete from biao1 where name='lisi';		#删除biao1中name是lisi的记录

3.12数据库授予权限

GRANT注意事项：

  • Ø 权限列表：用于列出授权使用的各种数据库操作，以逗号分隔，使用all代表所有权限
  • Ø 库名.表名：用于指定授权操作的库和表的名车，可以使用通配符”*”表示所有库和表，”ceshi.*”表示ceshi库里的所有表
  • Ø 用户名@来源地址：用于指定用户名称和允许访问的客户机地址，就是谁能链接，从哪里连接，可以使用通配符“%”表示任何网络，如”%.aptech.com” “192.168.1.%”等
  • Ø IDENTIFIED BY：用于设置用户连接数据库时所用的密码，新建用户省略此字段则表示用户的密码为空
1
2
3
语法：GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [ identified by ‘密码’ ]
mysql> grant all on *.* to 'zhangsan'@'%' identified by 'putianhui';
#新建zhangsan用户授予所有库所有表有所有的权限

3.13查询用户的授权信息

1
2
语法：show grants for '用户名@'来源地址';
mysql> show grants for 'zhangsan'@'%';		#查询zhangsan用户授权信息

3.14撤销用户授权信息，撤销后还能登陆数据库，无法操作

1
2
语法：revoke 权限信息 on 库名.表名. from '用户名'@'来源地址';
mysql> revoke all on *.* from 'zhangsan'@'%';	#撤销zhangsan用户的授权信息

3.15修改表中列的属性

1
2
语法：alter table 表名 modify column 列名 新的列类型
Alter table user modify column 姓名 char(200)	#将user表中姓名列类型改为char(200)

四。Mysql备份与恢复

4.1 全库备份

4.1.1 mysqldump备份

1
2
3
[root@123 backup]$ mysqldump -uroot -p --databases log > log.sql     	 #备份指定数据库log，导出为log.sql
[root@123 backup]$ mysqldump -uroot -p log biao1 biao2 > biao12.sql		#备份log数据库中的biao1和biao2表
[root@123 backup]$ mysqldump -uroot -p --all-databases > all.sql 		#备份所有的数据库

4.1.2 全库备份恢复

注意

  • 当备份文件中只有表备份的时候，恢复时需要指定一个现有的数据库用来存放恢复的数据表
  • 全库备份中只恢复指定数据库的时候需目标数据库存在，不存在无法恢复
1
2
3
4
5
6
7
8
9
10
11
12
13
# 恢复指定数据库备份文件
]$ mysql -uroot -p < log.sql

# 恢复所有数据库备份文件
]$ mysql -u root -p < all.sql

# 恢复表备份至某个数据库
]$ mysql -u root -p test < biao12.sql





4.1.3 全库备份恢复特定表或库

1
2
3
4
5
6
7
8
9
10
11
12
# 恢复全库备份中test数据库备份数据
]$ mysql -uroot -p --one-database test < all-database.sql


# 示例：恢复hugeleafdata库中的movie_company这张表数据
# 获取目标表结构SQL语句
/]$ sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `movie_company`/!d;q' 2020-06-15-hugeleafdata.sql
# 从全库备份文件中获取目标表数据保存到sql文件中
/]$ grep 'INSERT INTO `movie_company`' 2020-06-15-hugeleafdata.sql  > data_movie_company.sql
# 将目标表数据库
先使用建表语句建表
/]$ mysql -uroot -p hugeleafdata < data_movie_company.sql

4.2 数据库增量备份

Mysql没有提供直接的增量备份方法，我们可以使用mysql提供的二进制日志间接的实现增量备份。使用增量备份前首先需要开启数据库的二进制日志功能，二进制日志文件扩展名是6位数字，如mysql-bin.000001

1
2
3
4
]$ vim /etc/my.cnf			# 编辑mysql配置文件
---------------------------------添加以下内容----------------------------------
log-bin=/usr/local/mysql/mysql-bin			# 开启mysql二进制文件支持
# 然后重启mysqld服务

4.2.1 增量恢复语法

参考博客：https://blog.csdn.net/neddiepeng/article/details/85246129

常用的增量恢复有三种：一般恢复、基于位置恢复和基于时间恢复

一般恢复：将所有备份的二进制日志内容全部恢复

1
2
#一般恢复语法：
mysqlbinlog --no-defaults 增量备份文件名 | mysql -u用户名 -p

基于位置恢复：将数据恢复到指定位置或指定开始恢复位置

1
2
3
4
5
6
#基于位置恢复语法：
# 格式一：恢复到指定位置
mysqlbinlog --stop-position='操作id' 二进制文件名 | mysql -u用户名 -p

# 格式二：从指定位置开始恢复
mysqlbinlog --start-postion='操作id' 二进制文件名 | mysql -u用户名 -p

基于时间点恢复：跳过某个发生错误的时间点实现数据恢复

1
2
3
4
5
6
7
8
9
基于时间恢复语法：
# 格式一：从日志开头截止到某个时间点的恢复
mysqlbinlog --no-defaults --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p

# 格式二：从某个时间点到日志的结尾
mysqlbinlog --no-defaults --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p

# 格式三：从某个时间点到某个时间点
mysqlbinlog --no-defaults --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p

4.2.2 增量备份恢复案例

一般恢复案例

①编辑配置文件，开启二进制日志的功能

1
2
3
4
5
6
~]$ vim /etc/my.cnf				# 编辑数据库配置文件
----------------添加以下参数-----------------------
log-bin=/usr/local/mysql/mysql-bin			# 开启二进制日志功能

~]$ systemctl restart mysqld			# 重启mysql服务
~]$ ls /usr/local/mysql/ | grep mysql-bin	# 查看二进制日志文件

②登录数据库，创建一个数据库，在数据库中创建一个表并录入张三和李四的信息

1
2
3
4
5
6
~]$ mysql -uroot				# 登录数据库
mysql> create database client;				# 创建一个数据库名为client
mysql> use client;					# 切换到client数据库
mysql> create table xinxi(身份证 char(20),姓名 char(20),性别 char(20),用户id char(20),资费 char(20));		# 创建一个表设置字段
mysql> insert xinxi values ('000006','张三','男','016','61');	# 在表中插入张三的信息
mysql> insert xinxi values ('000007','李四','男','017','91');	# 在表中插入李四的信息

③为了验证二进制日志的增量恢复功能，在插入两条数据后对client数据库进行一次完整的备份，然后生成新的二进制日志文件

1
2
3
4
~]$ mkdir /mysql_bak			# 创建完整备份存放的目录
~]$ mysqldump -uroot -p --databases client > /mysql_bak/client.sql	# 将client数据库完整备份保存到mysql_bak目录
~]$ mysqladmin -uroot -p flush-logs	# 生成新的二进制日志文件
~]$ ls /usr/local/mysql/ | grep mysql-bin	# 查看二进制日志文件

④继续插入新的用户数据并刷新二进制日志文件进行增量备份，这样在完整备份的基础上新增加的操作备份插入王五和赵六的信息保存在000002二进制文件中

1
2
3
4
mysql> insert xinxi values ('000008','王五','女','018','23');	# 在表中继续插入王五的信息
mysql> insert xinxi values ('000009','赵六','男','019','37');	# 在表中继续插入赵六的信息
~]$ mysqladmin -uroot -p flush-logs		    # 生成新的二进制文件
~]$ ls /usr/local/mysql/ | grep mysql-bin	# 查看二进制日志文件

⑤模拟误操作，把xinxi这个数据表删除

1
2
mysql> drop table xinxi;				# 删除xinxi这个表
mysql> select * from client.xinxi;		 # 查看表是否还存在

⑥恢复数据表信息，先进行完整备份恢复，然后在进行增量备份恢复

完整备份恢复

1
2
~]$ mysql -uroot -p < /mysql_bak/client.sql	 # 恢复完整备份
mysql> select * from client.xinxi;			# 查看表信息只有张三和李四的信息

增量备份一般恢复：在插入王五和赵六信息后重新生成了二进制文件，名为000002，因为这个二进制文件中保存了王五和赵六的信息

1
2
~]$ mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p				# 恢复000002二进制文件中的所有数据
mysql> select * from client.xinxi;			# 查看表王五和赵六信息也恢复了
基于位置恢复案例

由于之前已经做过备份，就接前面的实验接着操作模拟故障与数据恢复

①模拟误操作，将xinxi表删除

1
2
mysql> drop table xinxi;				# 删除xinxi这个表
mysql> select * from client.xinxi;		 # 查看表是否还存在

②完整备份恢复

1
2
~]$ mysql -uroot -p < /mysql_bak/client.sql		 # 恢复完整备份
mysql> select * from client.xinxi;			 	# 查看表信息只有张三和李四的信息

③要基于位置或者时间恢复数据，必须先查看二进制文件的内容获取到要恢复到的操作id或者要恢复到的时间点，可以使用“mysqlbinlog –no-defaults 二进制文件”查看二进制文件的具体内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
~]$ mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002 	# 查看000002二进制日志文件的内容
---------------------------------------------------------------------------------------------------------
# at 203
#181214  0:43:07 server id 1  end_log_pos 337 CRC32 0x4209f33e 	Query	thread_id=2	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1544719387/*!*/;
insert xinxi values ('000008','王五','女','018','23')
/*!*/;
# at 337
#181214  0:43:07 server id 1  end_log_pos 368 CRC32 0xf4cb4c7d 	Xid = 38
COMMIT/*!*/;
# at 368
#181214  0:43:15 server id 1  end_log_pos 451 CRC32 0xa428e268 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1544719395/*!*/;
BEGIN
/*!*/;
# at 451
#181214  0:43:15 server id 1  end_log_pos 585 CRC32 0xc3e48c8c 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1544719395/*!*/;
insert xinxi values ('000009','赵六','男','019','37')
/*!*/;
# at 585
#181214  0:43:15 server id 1  end_log_pos 616 CRC32 0xf05f2902 	Xid = 39
COMMIT/*!*/;
# at 616
#181214  0:45:59 server id 1  end_log_pos 663 CRC32 0xb3cc49ad 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

④通过查看二进制文件内容可以看到每个操作前都有一个id或者时间点，例如在ID at 451之前插入了王五的信息

格式一：例如执行下面操作可以只将数据恢复到ID451，也就是说只恢复王五的信息，赵六的信息不恢复

1
2
~]$ mysqlbinlog --no-defaults --stop-position='451' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p	# 恢复操作ID451之前的所有数据
mysql> mysql> select * from xinxi;		# 查看xinxi表发现王五信息已经恢复了

格式二：如果执行下面操作是从ID451开始进行恢复，也就是说恢复id451之后的所有的数据，因此只会恢复赵六的数据，王五的数据就跳过了

1
2
~]$ mysqlbinlog --no-defaults --start-position='451' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p   # 恢复操作ID451之后的所有数据
mysql> mysql> select * from xinxi;		# 查看xinxi表发现王五信息已经恢复了
基于时间点恢复案例

由于之前已经做过备份，就接前面的实验接着操作模拟故障与数据恢复

①模拟误操作，将xinxi表删除

1
2
mysql> drop table xinxi;				# 删除xinxi这个表
mysql> select * from client.xinxi;		 # 查看表是否还存在

②完整备份恢复

1
2
~]$ mysql -uroot -p < /mysql_bak/client.sql	   					 # 恢复完整备份
mysql> select * from client.xinxi;								# 查看表信息只有张三和李四的信息

③使用“mysqlbinlog –no-defaults 二进制文件”查看二进制文件时间点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
~]$ mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002
# 查看000002二进制日志文件的内容
---------------------------------------------------------------------------------------------------------
# at 203
#181214  0:43:07 server id 1  end_log_pos 337 CRC32 0x4209f33e 	Query	thread_id=2	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1544719387/*!*/;
insert xinxi values ('000008','王五','女','018','23')
/*!*/;
# at 337
#181214  0:43:07 server id 1  end_log_pos 368 CRC32 0xf4cb4c7d 	Xid = 38
COMMIT/*!*/;
# at 368
#181214  0:43:15 server id 1  end_log_pos 451 CRC32 0xa428e268 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1544719395/*!*/;
BEGIN
/*!*/;
# at 451
#181214  0:43:15 server id 1  end_log_pos 585 CRC32 0xc3e48c8c 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1544719395/*!*/;
insert xinxi values ('000009','赵六','男','019','37')
/*!*/;
# at 585
#181214  0:43:15 server id 1  end_log_pos 616 CRC32 0xf05f2902 	Xid = 39
COMMIT/*!*/;
# at 616
#181214  0:45:59 server id 1  end_log_pos 663 CRC32 0xb3cc49ad 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

④通过查看二进制文件内容可以看到每个操作前都有一个id或者时间点，例如在时间点****181214 0:43:15****之前插入了王五的信息

例如执行下面操作可以只将数据恢复到****181214 0:43:15****，也就是说只恢复王五的信息，赵六的信息不恢复

1
2
~]$ mysqlbinlog --no-defaults --stop-datetime='2018-12-14  0:43:15' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p									 		  # 恢复操作时间点之前的所有数据
mysql> select * from xinxi;		# 查看xinxi表发现王五信息已经恢复了

如果执行下面操作是从****181214 0:43:15****这个时间点开始恢复以后的所有数据，因此只会恢复赵六的数据，王五的数据就跳过了

1
2
~]$ mysqlbinlog --no-defaults --start-datetime='2018-12-14  0:43:15' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p															 		     # 恢复操作时间点之后的所有数据
mysql> mysql> select * from xinxi;								# 查看xinxi表发现赵六信息已经恢复了

如果执行以下操作是指恢复时间点****#181214 0:43:07*到*#181214 0:45:59****这个时间点之间的所有数据，如果前面或者时间点之后有数据则跳过不恢复 

1
2
~]$ mysqlbinlog --no-defaults --start-datetime='2018-12-14  0:43:07' --stop-datetime='2018-12-14 0:45:49'  /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p							   # 恢复时间点0:43:07到 0:45:49之间的所有数据
mysql> select * from xinxi;								 # 查看xinxi表发现王五和赵六信息已经恢复了

4.3 定时脚本自动备份

需求：每天晚上九点进行一次增量备份（刷新binlog），每周五进行全库备份一次。

实现逻辑：开启mysql数据库的binlog二进制增量备份功能，定义两个脚本，一个脚本是刷新binlog进行增量备份（每天晚上九点执行一次），一个脚本是进行全库备份的脚本（每周五晚上执行一次）

全库备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/]$ vim /root/scripts/mysql_data_fullbackup.sh
#!/bin/bash
#description: mysql-data automatically backup script
# 文件名为100mysql+日期.sql
backupDatabase=zentao
fileName=${backupDatabase}`date +"%Y-%m-%d_%H-%M-%S"`
mysql_user=root
mysql_pwd=root

# 定义baseDir变量,就是备份文件存储目录 
baseDir=/opt/mysql_backup/

# 备份数据库 
[ -e ${baseDir} ] || mkdir ${baseDir}
/usr/bin/mysqldump -u${mysql_user} -p${mysql_pwd}   --databases ${backupDatabase}  --single-transaction -R > ${baseDir}${fileName}.sql
# --delete-master-logs  删除之前无用的二进制备份日志
# -R                    备份存储过程

# 压缩 
cd $baseDir
tar -zcvPf ${baseDir}${fileName}.sql.tar.gz ${fileName}.sql  >> ${baseDir}/backup.log

# 删除刚刚备份的sql文件,留下压缩的   
rm -f ${baseDir}${fileName}.sql;

# 删除7天前备份的数据，只保留一星期内的数据
#ls -lt ${baseDir}${backupDatabase}*.tar.gz |awk 'NR>5{print $NF}'
find ${baseDir} -mtime +30 -name "${backupDatabase}*.tar.gz" -exec rm -rf {} \;

增量备份刷新脚本

1
2
3
4
5
6
7
/]$ vim /root/scripts/mysql_binlog_flush.sh
#!/bin/bash
#description: mysql-data automatically backup script
# 刷新二进制日志
mysql_user=root
mysql_pwd=root
/usr/bin/mysqladmin -u${mysql_user} -p${mysql_pwd}  flush-logs;

定时计划任务

1
2
3
/]$ crontab  -e 
05 21 * * 5 /bin/bash /root/scripts/mysql_data_fullbackup.sh
00 21 * * * /bin/bash /root/scripts/mysql_binlog_flush.sh

Mysql操作审计

由于MySQL社区版不支持审计系统，可通过第三方插件实现，此次采用MariaDB的server_audit插件来记录{时间，节点，用户，源IP，事件类型，库，语句，影响行数}，

注：需要mysql版本小于5.7.33，从mysql8开始已不支持该插件。

查看插件安装目录

1
2
3
4
5
mysql>  show global variables like 'plugin_dir';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| plugin_dir    | /opt/mysql/5.7.18/lib/plugin/ |

提取mariadb审计插件并放置插件目录（版本并不通用，自行根据版本号提取，这里就没总结了）

1
2
3
4
5
6
7
$ wget  https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.5.3/bintar-linux-x86_64/mariadb-10.5.3-linux-x86_64.tar.gz

$ tar -zxvf   mariadb-10.5.3-linux-x86_64.tar.gz

$ cp  ./mariadb-10.5.3-linux-x86_64/lib/plugin/server_audit.so /opt/mysql/5.7.18/lib/plugin/

$ chmod  +x  /opt/mysql/5.7.18/lib/plugin/server_audit.so

安装server_audit.so插件

1
2
3
4
5
mysql>  install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected (0.02 sec)           ##也可以在my.cnf 加载插件方式安装

# 查看当前MySQL插件情况
mysql>  show plugins;

开启审计，写入配置文件，重启mysql生效！

1
2
3
4
5
6
7
8
9
# 修改配置添加审计
$ vi /etc/my.cnf
### 审计 ###
server_audit_logging = ON                 #开启日志记录，默认是关闭
server_audit = FORCE_PLUS_PERMANENT        #防止插件被卸载
server_audit_file_path = server_audit.log     #定义审计日志文件名
server_audit_file_rotate_now = OFF         #是否强制切割审计日志
server_audit_file_rotate_size = 1073741824   #定义切割审计日志的文件大小1073741824=1GB
server_audit_file_rotations = 0              #定义审计日志的轮询个数，0为不轮询

如不便重启mysql用下面方式临时开启

1
2
3
4
5
# 临时开启  
mysql>  set global  server_audit_logging=on;

# 临时关闭  
mysql>  set global  server_audit_logging=off;

server_audit.log审计日志文件，默认存放于mysql的data目录下。

1
mysql>  show global variables like 'datadir';

测试查看日志

1
$ tail  /opt/mysql/data/server_audit.log

查看server_audit插件参数

1
mysql>  show variables like '%server_audit%';

参数说明

  • server_audit_events ：指定记录事件的类型，可以用逗号分隔的多个值
  • server_audit_excl_users ： 该列表的用户[行为]将不记录，connect信息将不受该设置影响
  • server_audit_file_path ：使用该变量设置存储日志的文件，可以指定目录，默认存放在数据目录的server_audit.log文件中
  • server_audit_file_rotate_now ：知否立即切割日志
  • server_audit_file_rotate_size ：限制日志文件的大小
  • server_audit_file_rotations ：指定日志文件的数量，如果为0日志将从不轮转
  • server_audit_incl_users ： 指定哪些用户的活动将记录，connect将不受此变量影响，该变量比server_audit_excl_users优先级高
  • server_audit_loc_info ：
  • server_audit_logging ：启动或关闭审计ON/OFF
  • server_audit_mode ：标识版本，用于开发测试
  • server_audit_output_type ：指定日志输出类型，可为SYSLOG或FILE，当为syslog时记录到/var/log/messages
  • server_audit_query_log_limit ：1024
  • server_audit_syslog_facility ：LOG_USER
  • server_audit_syslog_ident ：mysql-server_auditing
  • server_audit_syslog_info ：
  • server_audit_syslog_priority ：LOG_INFO

重点说明server_audit_events

省略值全部事件类型都会记录到审计日志中，可选择事件类型才记录。

如 server_audit_events = query,table,query_ddl,query_dml

1
2
3
4
5
6
7
8
9
10
11
12
13
CONNECT：连接、断开连接和失败的连接，包括错误代码

QUERY：以纯文本形式执行的查询及其结果，包括由于语法或权限错误而失败的查询

TABLE：受查询执行影响的表

QUERY_DDL：与QUERY相同，但只筛选DDL类型的查询（create、alter、drop、rename和truncate语句，create/drop[procedure/function/user]和rename user除外（它们不是DDL）

QUERY_DML：与QUERY相同，但只筛选DML类型的查询（do、call、load data/xml、delete、insert、select、update、handler和replace语句）

QUERY_DCL：与QUERY相同，但只筛选DCL类型的查询（create user、drop user、rename user、grant、revoke和set password语句）

QUERY_DML_NO_SELECT：与QUERY_DML相同，但不记录SELECT查询。（从1.4.4版开始）（do、call、load data/xml、delete、insert、update、handler和replace语句）

注意：

  1. 如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录

  2. 由于除了DDL和DML之外还有其他类型的查询，因此将查询DDL和查询DML选项一起使用并不等同于使用查询。从Audit插件的1.3.0版开始，有用于记录查询的DCL类型（例如GRANT和REVOKE语句）的QUERY_DCL选项。在同一版本中，添加了server_audit_query_log_limit变量，以便能够设置日志记录的长度。以前，由于查询字符串较长，日志条目将被截断。

卸载审计插件

如my.cnf 有相关配置，先清空重启再卸载。

1
mysql> UNINSTALL PLUGIN server_audit;

其他说明

在my.cnf 设置 plugin_load = server_audit=server_audit.so 载入审计插件，

这可以代替在mysql> install plugin server_audit soname 'server_audit.so';