1、修改通用日志，记录通用日志至mysql.general_log表中
MariaDB [mysql]> set global log_output="table"; MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+ 1 row in set (0.002 sec)
MariaDB [mysql]> select * from mysql.general_logG ...省略... *************************** 6. row *************************** event_time: 2019-11-25 11:03:41.163896 user_host: root[root] @ localhost [] thread_id: 9 server_id: 1 command_type: Query argument: xxx *************************** 7. row *************************** event_time: 2019-11-25 11:03:44.549211 user_host: root[root] @ localhost [] thread_id: 8 server_id: 1 command_type: Query argument: select * from general_log 7 rows in set (0.000 sec)
2、对访问的语句进行排序
[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr [root@centos8 ~]#mysql -e 'select argument from mysql.general_log' | sort |uniq -c |sort -nr
3、在线查看二进制文件中的指定内容
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3 #第3.4.5行
4、mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
5、清除指定二进制日志
PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志 PURGE BINARY LOGS BEFORE '2017-01-23'; PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
6、删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
7、刷新日志文件
FLUSH LOGS;
8、MySQL备份工具
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份 LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份 mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部 分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份 xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份 MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现 mysqlbackup：热备份， MySQL Enterprise Edition组件 mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、 FLUSH TABLES和cp或scp来快速备份数据库
基于 LVM 的快照备份
(1) 请求锁定所有表 mysql> FLUSH TABLES WITH READ LOCK; (2) 记录二进制日志文件及事件位置 mysql> FLUSH LOGS; mysql> SHOW MASTER STATUS; mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE (3) 创建快照 lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME (4) 释放锁 mysql> UNLOCK TABLES; (5) 挂载快照卷，执行数据备份 (6) 备份完成后，删除快照卷 (7) 制定好策略，通过原卷备份二进制日志
9、特定数据库的备份脚本
#!/bin/bash TIME=`date +%F_%H-%M-%S` DIR=/backup DB=hellodb PASS=xxx mysqldump -uroot -p$PASS -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
10、分库备份并压缩 备份到一个独立的文件中
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /data/$db.sql.gz;done [root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /data/$db.sql.gz;done [root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B 1 | gzip > /data/1.sql.gz#p' |bash [root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B 1 |gzip > /data/1.sql.gz#p' |bash
11、分库备份的实战脚本
#!/bin/bash TIME=`date +%F_%H-%M-%S` DIR=/backup #PASS= [ -d "$DIR" ] || mkdir $DIR for DB in `mysql -uroot -e 'show databases' | grep -Ev "^Database|.*schema$"`;do mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz done ~
12、完全备份和还原
#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
mysqldump -uroot -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;
13、mysqldump 和二进制日志结合实现差异(增量)备份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz #观察上面备份文件中记录的二进制文件和位置，定期将其之后生成的所有二进制日志进行复制备份 [root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadbbin.000003是后续生成的二进制日志 [root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql
14、恢复误删除的表
案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表
#完全备份 [root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql [root@centos8 ~]#ll /backup/ total 2992 -rw-r--r-- 1 root root 3060921 Nov 27 10:20 allbackup_2019-11-27_10:20:08.sql #完全备份后数据更新 MariaDB [testdb]> insert students (name,age,gender) values('rose',20,'f'); Query OK, 1 row affected (0.001 sec) MariaDB [testdb]> insert students (name,age,gender) values('jack',22,'M'); Query OK, 1 row affected (0.001 sec) #10：00误删除了一个重要的表 MariaDB [testdb]> drop table students; Query OK, 0 rows affected (0.021 sec) #后续其它表继续更新 MariaDB [testdb]> use hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [hellodb]> insert teachers (name,age,gender)values('wang',30,'M'); Query OK, 1 row affected (0.002 sec) MariaDB [hellodb]> insert teachers (name,age,gender)values('mage',28,'M'); Query OK, 1 row affected (0.002 sec) MariaDB [hellodb]> select * from teachers; +-----+----------------+-----+--------+ | TID | Name | Age | Gender | +-----+----------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | wang | 30 | M | | 6 | mage | 28 | M | +-----+----------------+-----+--------+ 6 rows in set (0.001 sec) #10：10发现表删除，进行还原 #停止数据库访问 #从完全备份中，找到二进制位置 [root@centos8 ~]#grep '-- CHANGE MASTER TO' /backup/allbackup_2019-11-27_10:20:08.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389; #备份从完全备份后的二进制日志 [root@centos8 ~]#mysqlbinlog --start-position=389 /var/lib/mysql/mariadb-bin.000003 > /backup/inc.sql #找到误删除的语句，从备份中删除此语句 [root@centos8 ~]#vim /data/inc.sql #DROP TABLE `student_info` /* generated by server */ #如果文件过大，可以使用sed实现 [root@centos8 ~]#sed -i.bak '/^DROP TABLE/d' /data/inc.sql #利用完全备份和修改过的二进制日志进行还原 [root@centos8 ~]#mysql -uroot -p MariaDB [hellodb]> set sql_log_bin=0; MariaDB [hellodb]> source /backup/allbackup_2019-11-27_10:20:08.sql; MariaDB [hellodb]> source /backup/inc.sql MariaDB [hellodb]> set sql_log_bin=1;
15、MySQL8.0密码在哪
cat /var/log/mysqld.log |grep password