MySQL范例

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