分类 MYSQL 下的文章

恢复过程说明：
1）解压数据备份

安装qpress软件
    rpm -ivh qpress-11.1.el7.x86_64.rpm
解压备份
    Cd /backup/Data/backup/xtrabackup/202042
    tar -xvf 192.168.138.2_41667_20201021_full.tar
    tar -xvf 192.168.138.2_41667_20201022_incre.tar
qpress解压数据文件
    cd /backup/Data/backup/xtrabackup/202042/41667/full
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)
    cd /backup/Data/backup/xtrabackup/202042/41667/incre
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)

2）合并备份

perpare全备
    innobackupex --apply-log --redo-only /backup/Data/backup/xtrabackup/202042/41667/full/
合并incre到全备
    innobackupex --apply-log --incremental-dir=/backup/Data/backup/xtrabackup/202042/41667/incre //backup/Data/backup/xtrabackup/202042/41667/full
perpare整个备份
    innobackupex --apply-log /backup/Data/backup/xtrabackup/202042/41667/full

3）恢复数据

停止数据库
    systemctl stop mysqld
清除数据文件目录数据
    cd /Data/mysql/
    rm -rf *
应用备份
    mv /backup/Data/backup/xtrabackup/202042/41667/full/* /Data/mysql
更改目录权限
    chown -R mysql.mysql /Data/mysql
重启数据库
    systemctl start mysqld

4）截取binlog日志

获得position起点
    cat /backup/Data/backup/xtrabackup/202042/41667/incre/xtrabackup_binlog_info 
    mysql-bin.002244    194    230f7b03-e281-11e9-a531-0894ef952262:1-7
登录源数据库服务器获取bin-log日志，拷贝日志至恢复测试服务器/home/pmoopr/目录
获得position终点
    mysqlbinlog mysql-bin.002244
    mysqlbinlog mysql-bin.002245
    mysqlbinlog mysql-bin.002246
    mysqlbinlog mysql-bin.002247
截取日志
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002244 >/backup/Data/backup/xtrabackup/202042/2244.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002245 >/backup/Data/backup/xtrabackup/202042/2245.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002246 >/backup/Data/backup/xtrabackup/202042/2246.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002247 >/backup/Data/backup/xtrabackup/202042/2247.sql

5）应用日志

mysql -uroot -p
停止记录binlog日志
    set sql_log_bin=0;
应用日志
    source /backup/Data/backup/xtrabackup/202042/2244.sql
    source /backup/Data/backup/xtrabackup/202042/2245.sql
    source /backup/Data/backup/xtrabackup/202042/2246.sql
    source /backup/Data/backup/xtrabackup/202042/2247.sql
开启记录binlog日志
    set sql_log_bin=1;

sql 执行频率

一、通过 show status 命令了解各种sql的执行频率

mysql客户端连接成功后，通过show[session|global] status命令，可以查看服务器的状态，如果不加默认为session（session级为当前连接的统计结果，global级为自数据库上次启动到现在的统计结果）。
Eg: show status like 'Com_%';

com_xx表示每个xx语句的执行次数，通常着重看curd操作次数（这些参数适用于所有存储引擎）；

Com_insert：执行select操作的次数，一次查询只累加1；
Com_select：执行insert操作的次数，批量插入的insert操作只累加一次；
Com_delete：执行update操作次数，提交和回滚均会累加；
Com_update：执行delete操作次数。
针对innodb存储引擎的表操作，累加的算法比如：show status like 'Innodb_rows_%';

show status like 'connections';----- 试图连接mysql数据库的次数

show status like ‘uptime’;-- 服务器工作时间

show status like ‘slow_queries’; – 慢查询的次数

二、定位执行效率较低的sql语句

​ 通过慢查询日志定位哪些sql执行效率低下，用–log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time 秒的sql语句的日志文件，具体可参看后面的日志管理部分。
​ 慢查询日志在查询结束以后才记录，所以在应用反正执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前mysql在进行的线程，包括线程的状态，是否锁表等，可以实时查看sql的执行情况，同时对一些锁表操作进行优化。

三、通过explain分析低效sql的执行计划

追查到效率低的sql后，可以通过explain或desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序，eg:统计某个email为租赁电影拷贝所支付的总金额，需要关联客户表customer和付款表payment,并且对付款金额amount字段做求和操作，相应sql的执行计划如下：
explain select sum(amount) from customer a ,payment b where 1=1 and a.customer_id=b.customer_id and email ='JANE.BENNETT@SAKILACUSTOMER.ORG'\G

下面对每个列进行简单说明：
select_type:表示select的类型，常见取值 simple(简单表，即不适用表连接或者子查询)，primary(主查询，即外层的查询)，union（union中的第二个或者后面的查询语句），subquery(子查询中的第一个select)等。
table：输出结果集的表。
possible_keys:表示查询时可能使用的索引，不表示出现在这里的就是表的全部索引。
key:表示实际使用的索引。
key_len：使用到索引字段的长度。
rows:扫描行的数量。
extra:执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

type:表示mysql在表中找到所需行的方式，或者叫访问类型，常见如下（性能由左至右由最差变道最好）：all 、 index 、 range 、 ref、 eq_ref 、 const,system 、 null

在数据库运维过程中，我们时常会关注数据库的链接情况，比如总共有多少链接、有多少活跃链接、有没有执行时间过长的链接等。数据库的各种异常也能通过链接情况间接反应出来，特别是数据库出现死锁或严重卡顿的时候，我们首先应该查看数据库是否有异常链接，并杀掉这些异常链接。本篇文章将主要介绍如何查看数据库链接及如何杀掉异常链接的方法。

1.查看数据库链接

查看数据库链接最常用的语句就是 show processlist 了，这条语句可以查看数据库中存在的线程状态。普通用户只可以查看当前用户发起的链接，具有 PROCESS 全局权限的用户则可以查看所有用户的链接。

show processlist 结果中的 Info 字段仅显示每个语句的前 100 个字符，如果需要显示更多信息，可以使用 show full processlist 。同样的，查看 information_schema.processlist 表也可以看到数据库链接状态信息。

普通用户只能看到当前用户发起的链接

mysql> select user();

| user() |

| testuser@localhost |

1 row in set (0.00 sec)

mysql> show grants;

| Grants for testuser@% |

| GRANT USAGE ON . TO 'testuser'@'%' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'%' |

2 rows in set (0.00 sec)

mysql> show processlist;

| Id | User | Host | db | Command | Time | State | Info |

| 769386 | testuser | localhost | NULL | Sleep | 201 | | NULL |

| 769390 | testuser | localhost | testdb | Query | 0 | starting | show processlist |

2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist;

| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

| 769386 | testuser | localhost | NULL | Sleep | 210 | | NULL |

| 769390 | testuser | localhost | testdb | Query | 0 | executing | select * from information_schema.processlist |

2 rows in set (0.00 sec)

授予了PROCESS权限后，可以看到所有用户的链接

mysql> grant process on . to 'testuser'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants;

| Grants for testuser@% |

| GRANT PROCESS ON . TO 'testuser'@'%' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'%' |

2 rows in set (0.00 sec)

mysql> show processlist;

| Id | User | Host | db | Command | Time | State | Info |

| 769347 | root | localhost | testdb | Sleep | 53 | | NULL |

| 769357 | root | 192.168.85.0:61709 | NULL | Sleep | 521 | | NULL |

| 769386 | testuser | localhost | NULL | Sleep | 406 | | NULL |

| 769473 | testuser | localhost | testdb | Query | 0 | starting | show processlist |

4 rows in set (0.00 sec)

通过 show processlist 所得结果，我们可以清晰了解各线程链接的详细信息。具体字段含义还是比较容易理解的，下面具体来解释下各个字段代表的意思：

Id：就是这个链接的唯一标识，可通过 kill 命令，加上这个Id值将此链接杀掉。

User：就是指发起这个链接的用户名。

Host：记录了发送请求的客户端的 IP 和 端口号，可以定位到是哪个客户端的哪个进程发送的请求。

db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。

Command：是指此刻该线程链接正在执行的命令。

Time：表示该线程链接处于当前状态的时间。

State：线程的状态，和 Command 对应。

Info：记录的是线程执行的具体语句。

当数据库链接数过多时，筛选有用信息又成了一件麻烦事，比如我们只想查某个用户或某个状态的链接。这个时候用 show processlist 则会查找出一些我们不需要的信息，此时使用 information_schema.processlist 进行筛选会变得容易许多，下面展示几个常见筛选需求：

只查看某个ID的链接信息

select * from information_schema.processlist where id = 705207;

筛选出某个用户的链接

select * from information_schema.processlist where user = 'testuser';

筛选出所有非空闲的链接

select * from information_schema.processlist where command != 'Sleep';

筛选出空闲时间在600秒以上的链接

select * from information_schema.processlist where command = 'Sleep' and time > 600;

筛选出处于某个状态的链接

select * from information_schema.processlist where state = 'Sending data';

筛选某个客户端IP的链接

select * from information_schema.processlist where host like '192.168.85.0%';

2.杀掉数据库链接

如果某个数据库链接异常，我们可以通过 kill 语句来杀掉该链接，kill 标准语法是：KILL [CONNECTION | QUERY] processlist_id;

KILL 允许使用可选的 CONNECTION 或 QUERY 修饰符：

KILL CONNECTION 与不含修改符的 KILL 一样，它会终止该 process 相关链接。

KILL QUERY 终止链接当前正在执行的语句，但保持链接本身不变。

杀掉链接的能力取决于 SUPER 权限：

如果没有 SUPER 权限，则只能杀掉当前用户发起的链接。

具有 SUPER 权限的用户，可以杀掉所有链接。

遇到突发情况，需要批量杀链接时，可以通过拼接 SQL 得到 kill 语句，然后再执行，这样会方便很多，分享几个可能用到的杀链接的 SQL ：

杀掉空闲时间在600秒以上的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

where command = 'Sleep' and time > 600;

杀掉处于某个状态的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.processlist

where state = 'Waiting for table metadata lock';

杀掉某个用户发起的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

user = 'testuser';

这里提醒下，kill 语句一定要慎用！特别是此链接执行的是更新语句或表结构变动语句时，杀掉链接可能需要比较长时间的回滚操作。

总结：

本篇文章讲解了查看及杀掉数据库链接的方法，以后怀疑数据库有问题，可以第一时间看下数据库链接情况。
————————————————
版权声明：本文为CSDN博主「y咯p秒杀软」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_35990581/article/details/113952377

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
concat('KILL ',id,';')
KILL 2476;
KILL 2447;

2 rows in set (0.00 sec)

  1. 查看mysql版本
    输入如下命令，回车，再输入mysql密码，即可查看mysql的版本。

    mysql -uroot -p

mysql的版本为：5.7.20-log

  1. 查看服务器版本
    通过如下命令：

    cat /etc/redhat-release

服务器版本为：CentOS Linux release 7.4.1708 (Core)

  1. 下载percona-xtrabackup
    通过如下命令下载：

官网路径：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/\
binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

注意：
percona-xtrabackup在官网最新版本是8.0，8.0版本只支持mysql8.0和percona8.0
早于mysql8.0的版本需要使用xtrabackup 2.4版本备份和恢复，因此我们上次选择的是2.4.4版本。
2.4版本的官网文档地址：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

通过如下命令安

yum localinstall -y percona-xtrabackup-80-8.0.5-1.el7.x86_64.rpm

  1. 全量备份

    innobackupex --defaults-file=${my.cnf} --user=${user} --password=${password} /opt

其中my.cnf为配置文件地址，user和password变量为数据库账号和密码，/opt为备份存储目录。

  1. 预处理

    innobackupex --user=${user} --password=${password} --apply-log /opt/2019-07-16_16-38-13

其中user和password变量为，数据库账号和密码，/opt/2019-07-16_16-38-13为备份存储目录。

  1. scp到从库

    scp -r /opt/2019-07-16_16-38-13 root@192.192.2.26:/opt

  2. 关闭从库并清空从库数据和日志信息

    systemctl stop mysqld

rm -rf ${dataPath}

dataPath为数据库数据所在目录的文件

  1. 在从库上恢复数据

    innobackupex --user=${user} --password=${password} --copy-back /opt/2019-07-16_16-38-13

其中user和password变量为，数据库账号和密码，/opt/2019-07-16_16-38-13为备份存储目录。

  1. 修改权限

    chmod -R 777 ${dataPath}

dataPath为数据库数据所在目录的文件

  1. 查看master位置

    cat /opt/2019-07-16_16-38-13/xtrabackup_binlog_info

结果如下：

mysql-bin.000468 742632435

  1. 从库执行同步
    打开mysql命令客户端，执行如下sql语句

    change master to master_host=${host}, master_user=${user}, master_password=${password}, master_log_file='mysql-bin.000468',master_log_pos=742632435

其中host为主库ip，user和password为主库账号密码，master_log_file和master_log_pos取第10步，获取到的值。

启动从库复制，执行如下sql语句

start slave;

经过以上步骤，即恢复mysql主从同步
————————————————
版权声明：本文为CSDN博主「chuxiongzouzhihui」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013294608/article/details/96114174

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5