马哥教育N36第二十周作业

一、对数据库实现lvm2的备份和慢查询优化

1.LVM2备份

要求数据文件和事务日志位于同一个逻辑卷

• 锁定所有表

    mysql> FLUSH TABLES WITH READ LOCK;

• 记录二进制文件事件位置

    mysql> FLUSH LOGS;
    mysql> SHOW MASTER STATUS;
    
    mysql  -e  'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE

• 创建快照卷

    lvcreate  -L size -s -p r - SNAM-NAME /dev/VG-NAME/LV-NAME 

• 释放锁

    mysql> UNLOCK TABLES

• 挂载快照卷，并执行备份，备份完成后删除快照卷

mkdir -p /mnt/snap
mount -o ro /dev/VG-NAME/LV-NAME /mnt/snap
cp /mnt/snap/* /data/backup/base/
umount /dev/VG-NAME/LV-NAME
lvremove /dev/VG-NAME/LV-NAME

• 周期性备份二进制日志

2.慢查询优化

• 记录慢查询

vim /etc/my.cnf
[mysqld]
long_query_time=2
slow_query_log=on
slow_query_log_file="slow-query.log"

• 分析慢查询
  使用explain工具分析，慢查询的语句是否使用索引

explain select 语句

• 建立适合的索引

create index index_name on tbl_name(index_col_name,...);

• 其他问题
  是否缓存设置不妥当，查询语句写的不规范

二、搭建数据库实现备份

使用xtrabackup 进行备份和还原，它是一种物理备份工具

• 安装工具

 yum install ./percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 

• 查看软件包构成

~]# rpm -ql percona-xtrabackup-24
-------------------------------------------------------------
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.9
/usr/share/doc/percona-xtrabackup-24-2.4.9/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
-------------------------------------------------------------

• 完全备份

innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
# 示例
innobackupex --user=root /data/backups/base
ll 2019-08-08_09-16-19/
----------------------------------------------------------------
total 18460
-rw-r----- 1 root root      417 Aug  8 09:16 backup-my.cnf
drwxr-x--- 2 root root       20 Aug  8 09:16 helldb
-rw-r----- 1 root root 18874368 Aug  8 09:16 ibdata1
drwxr-x--- 2 root root     4096 Aug  8 09:16 mysql
drwxr-x--- 2 root root     4096 Aug  8 09:16 performance_schema
-rw-r----- 1 root root       20 Aug  8 09:16 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Aug  8 09:16 xtrabackup_checkpoints
-rw-r----- 1 root root      462 Aug  8 09:16 xtrabackup_info
-rw-r----- 1 root root     2560 Aug  8 09:16 xtrabackup_logfile
----------------------------------------------------------------

• 增量备份

innobackupex --incremental /data/backups --incremental-basedir=BASEDIR
# 示例
innobackupex --no-timestamp --incremental  /data/backups/inc1/ --incremental-basedir=/data/backups/base/2019-08-08_09-16-19/

三、搭建数据库实现主从复制、主主复制、半自动复制

1. 主从复制

(1)首先同步主从服务器的时间
(2)主服务器上配置

• 修改配置文件

vim /etc/my.cnf  
[mysqld]  
log_bin=log-bin  
server_id=1  
innodb_file_per_table  
skip_name_resolve=on  

• 数据备份

mysqldump -A -F --single-transaction --master-data=1 --flush-privileges > /backups/full_backup.sql

• 创建主从复制的帐号和权限

mysql> grant replication slave on *.* to rpl_user@'192.168.30.%' identified by 'test';  

(3)从服务器上配置

• 修改配置文件

vim /etc/my.cnf  
[mysqld]  
innodb_file_per_table=on  
skip_name_resolve=on  
server_id=11  
relay_log=relay-log  
read_only=on  

• 使用主服务器的备份数据还原数据库

mysql <  /backups/full_backup.sql  

• 设置从服务器复制帐号

mysql> CHANGE MASTER TO   
  MASTER_HOST='192.168.30.103',   
  MASTER_USER='rpl_user',   
  MASTER_PASSWORD='test',  
  MASTER_PORT=3306,   
  MASTER_LOG_FILE='log-bin.000002',   
  MASTER_LOG_POS=245;  

• 开启从服务器复制

mysql> start slave;  

• 查看从服务器状态

mysql> show slave status \G  

2. 主主复制

所有节点互为主从

• 修改配置文件

vim /etc/my.cnf  
[mysqld]  
innodb_file_per_table=on  
skip_name_resolve=on  
server_id=1                      # 各个节点的Id不同
bin_log=bin-log                # 每一个节点都要开启二进制日志和中级日志
relay_log=relay-log  
auto_increment_offset=1  # 自动增长列协商使用不同的编号
auto_increment_increment=2

• 开启复制

mysql> start slave;  

• 查看状态

mysql> show slave status \G 

3. 半同步复制

插件的so文件位置：
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

• 主节点安装半同步插件

Mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';    
Mysql> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
Mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;	

• 从节点安装半同步插件

Mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Mysql> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; 
Mysql> STOP SLAVE IO_THREAD;
Mysql> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
Mysql> START SLAVE IO_THREAD;

• 判断是否开启半同步
  在主节点上查看

Mysql> SELECT @@global.rpl_semi_sync_master_clients；