MySQL备份与日志练习

1、创建对mysql数据库test1的定时备份任务，频率是每周一的2点

create database test1;

crond -e

0 2 * * 1 mysqldump -u root -pAdmin@123 --databases test1 > /opt/test1.sql

2、test1中有t1、t2、t3三张表，要求只备份t2这张表

mysqldump -u root -pAdmin@123  test1 t2 > /opt/test2.sql

3、开启binlog日志功能，在mysql中创建数据库a1，在a1中创建表aa1，新增5条数据，然后删除2条数据，删除数据库a1，要求通过binlog将a1数据库恢复道aa1中存有5条数据的状态

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED 
server-id = 1

systemctl restart mysqld
ls -l /var/lib/mysql/mysql-bin.*

create database a1;
use a1;
create table aa1(id int,name varchar(32));
insert into aa1 values (1,'zl1'),(2,'zl2'),(3,'zl3'),(4,'zl4'),(5,'zl5');
delete from aa1 where id in (1,2);
drop database a1;

show binlog events in 'mysql-bin.000002';

mysqlbinlog --no-defaults --start-position='1331' --stop-position='1908' /var/lib/mysql/mysql-bin.000002 | mysql -uroot -pAdmin@123

4、慢查询测试

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=mysql_slow_query.log
long_query_time=5
:wq
mysql -uroot -p

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
select sleep(7);
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

测试前：

测试后：

cat /var/lib/mysql/mysql_slow_query.log