(第二十一周作业)

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

1.1 LVM2备份操作

配合lvm2实现数据库备份要求mysql的数据目录存放在lvm的分区上。
1）创建lvm分区
创建一个PV
# pvcreate /dev/sdb
创建卷组
# vgcreate /dev/sdb mysql
创建逻辑卷，大小40GB
# lvcreate -L40G  -n datadir mysql
在逻辑卷上创建一个文件系统
# mke2fs /dev/mysql/datadir
挂载逻辑卷
# mkdir -p /mysql/DataDir
# mount  /dev/mysql/datadir /mysql/DataDir
2)创建一个mysql服务器实例，并使用--datadir指定逻辑卷上的数据目录
# mysqld  --console  -uroot  --datadir=/mysql/DataDir
3)使用LVM进行备份和还原，备份前需要临时锁定所有的表，执行快照后再解锁。
临时锁定所有的表
# mysql -uroot -p
mysql> FLUSH TABLES WITH READ LOCK
创建逻辑卷的快照
# lvcreate -L100M -s  -n backup /dev/mysql/datadir
(说明：-s选项表示快照）
解锁所有表
mysql>UNLOCK TABLES
装载快照
# mkdir /mysql/Backup
# mount /dev/mysql/backup /mysql/Backup
执行快照备份
# tar -czf snapshot.tgz  /mysql/Backup
备份完成后，卸载快照卷
# umount /mysql/Backup
删除快照卷
# lvremove /dev/mysql/backup

1.2 慢查询优化

1) 对于mysql查询执行慢的情况，我们先要找出是哪里语句执行慢
在mysql的配置文件my.cnf中配置开启慢查询日志功能

[mysqld]
slow_query_log=1  #表示开启慢查询日志功能
slow_query_log_file=/var/log/mysql/log-slow-queries.log  #慢查询日志位置
log_query_time=1  #查询时间超过这个设置（秒），则记录下来

注意这个慢查询日志文件，mysql用户有写权限
# chown mysql:mysql -R /var/log/mysql/log-slow-queries.log
# chmod 600 -R /var/log/mysql/log-slow-queries.log
重新加载mysql配置文件
# /etc/init.d/mysql reload
2）分析慢查询日志
把慢查询日志拷到其他机器上，然后可以使用mysqldumpslow进行慢查询日志分析统计。
-s 表示sort排序的条件，可用的条件如下：
   al 平均锁定时间
   ar 平均返回记录时间
   at 平均查询时间（缺省）
   c  计数
   l  锁定时间
   r  返回记录
   t  查询时间
-t 表示top n ,指定返回排序中前几个的记录
-g 表示grep，后跟一个正则表达式进行模式匹配，大小写不敏感。
3)发现慢的查询语句后，可以根据业务逻辑和业务情况进行优化，常用方式有合理的添加索引。
mysql> alter table 表名 add index 索引名 (字段名1,字段名2，...）;
然后进行测试确保有效。

2、搭建数据库实现备份

使用XtraBackup进行备份。

2.1 完全备份

创建备份用户并赋权
mysql> create user backuper@'localhost' identified by 'backuper';
mysql> grant reload,process,lock tables,replication client on *.* to backuper@localhost;
创建存放目录
# mkdir -p /data/backupDir
进行数据库全备份
# innobackupex --defaults-file=/etc/my.cnf  --user=backuper --password=backuper --socket=/tmp/mysql.sock  /data/backupDir
结果显示"completed OK!"则为备份成功。
会在/data/backupDir目录下生成一个yyyy-mm-dd_HH-MM-SS子目录来存放备份文件
进到这个目录中，查看xtrabackup_checkpoints文件，有这么一句：
backup_type = full-backuped   表示这是一个全备份目录

2.2 全备文件恢复

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中
事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件
于一致性状态。
关闭数据库
# /etc/init.d/mysqld stop
移除数据目录下的原始文件
准备一个完全备份，参数为备份目录
# innobackupex  --apply-log /data/backupDir/yyyy-mm-dd_HH-MM-SS 
执行恢复操作
# innobackupex  --defaults-file=/etc/my.cnf --copy-back --rsync  /data/backupDir/yyyy-mm-dd_HH-MM-SS
--copy-back  拷贝先前备份所有文件到它们的原始路径。但原路径下不能有任何文件或目录
--rsync    rsync工具一次性拷贝所有非InnoDB文件，而不是为每个文件单独创建cp，在备份恢复很多数据库和表时非常高效
更改 data数据目录权限并启动mysql
# chown -R mysql:mysql dataDir
# /etc/init.d/mysqld start

2.3 增量备份

在全量备份的基础上做增量备份
# innobackupex --defaults-file=/etc/my.cnf --user=backuper --password=backuper  --socket=/tmp/mysql.sock --incremental
data/backupDir/inc --incremental-basedir=/data/backupDir/yyyy-mm-dd_HH-MM-SS/ --parallel=2
--incremental  表示创建一个增量备份
--incremental-basedir=DIRECTORY    指定作为增量备份的基本数据集的完整备份目录。
--parallel 指定xtrabackup子进程应用于同时备份文件的线程数。
备份后在/data/backupDir/inc目录下生成一个yyyy-mm-dd_HH-MM-SS子目录
可以查看这个目录下的xtrabackup_checkpoints文件,可以看出备份类型
backup_type = incremental    #说明是增量备份
后再做增量备份时要把--incremental-basedir选项指定为前一增量备份的目录，即/data/pxb/inc/yyyy-mm-dd_HH-MM-SS/

2.4 增量备份的恢复

准备一个全备
# innobackupex --apply-log --redo-only /data/backupDir/yyyy-mm-dd_HH-MM-SS/
将增量1应用到完全备份
# innobackupex --apply-log --redo-only /data/backupDir/yyyy-mm-dd_HH-MM-SS/ --incremental-dir=/data/backupDir/inc/yyyy-mm-dd_HH-
M-SS/
……
最后一个增量备份应用到完全备份，注意不加 --redo-only 参数
# innobackupex --apply-log /data/backupDir/yyyy-mm-dd_HH-MM-SS/ --incremental-dir=/data/backupDir/inc/yyyy-mm-dd_HH-MM-SS/
把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据
# innobackupex --apply-log /data/backupDir/yyyy-mm-dd_HH-MM-SS/
然后就可以像全备份文件一个进行恢复了。
# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/backupDir/yyyy-mm-dd_HH-MM-SS/
# chown -R mysql:mysql dataDir
# /etc/init.d/mysqld start

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

需要两台虚拟机进行操作
A机： 192.168.184.36
B机： 192.168.184.37
已安装好mysql软件环境

3.1 主从复制

1）以A机为主机，修改配置
修改my.cnf配置文件
# vi /etc/my.cnf

[mysqld]
……
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 1

其中log-bin表示二进制日志文件的基本名，
log-bin-index 二进制索引文件的文件名，它保存了所有binlog文件的列表,
server-id要唯一，A机与B机中这一参数不能相同。
保存配置文件，重启mysql使配置生效
# /etc/init.d/mysqld restart
在A机上创建一个复制用户
# mysql -u root
mysql>CREATE USER repl_user;
mysql>GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.184.37 IDENTIFIED BY 'copycopy';
mysql>FLUSH PRIVILEGES;
3)以B机为从机，修改配置
修改my.cnf配置文件
# vi /etc/my.cnf

[mysqld]
……
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
server-id = 2

relay-log和relay-log-index是中继日志文件和中继索引文件的设置
保存配置文件，重启mysql使配置生效
# /etc/init.d/mysqld restart
在从机上创建和运行复制
# mysql -u root
mysql> CHANGE MASTER TO
    ->    MASTER_HOST = '192.168.183.36',
    ->    MASTER_PORT = '3306',
    ->    MASTER_USER = 'repl_user',
    ->    MASTER_PASSWORD = 'copycopy';
mysql> START SLAVE;
查看复制状态
在A机上
# mysql -u root
mysql>SHOW MASTER STATUS\G
在B机上
# mysql -u root
mysql>SHOW SLAVE STATUS\G
当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

3.2 主主复制

主主复制即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。
1）修改配置文件
在A机上，修改my.cnf配置文件
# vi /etc/my.cnf

[mysqld]
……
server-id=1
log-bin = mysql-bin
auto_increment_increment=2 
auto_increment_offset=1 
replicate-do-db=student 

其中
server-id要唯一，A机与B机中这一参数不能相同。
log-bin表示二进制日志文件的基本名
auto_increment_increment 有两台mysql主机就写2
auto_increment_offset 一般填第n台主MySQL
replicate-do-db 要同步的数据库，默认所有库
保存配置文件，重启mysql使配置生效
# /etc/init.d/mysqld restart
在B机上，修改my.cnf配置文件
# vi /etc/my.cnf

[mysqld]
……
server-id=2
log-bin = mysql-bin
auto_increment_increment=2 
auto_increment_offset=2 
replicate-do-db=student 

保存配置文件，重启mysql使配置生效
# /etc/init.d/mysqld restart
2）在B机上配置用户
mysql>GRANT REPLICATION SLAVE ON *.* TO 'mysql36'@'192.168.184.36' IDENTIFIED BY ‘mysql36’;
mysql>FLUSH PRIVILEGES;
3)在B机上查看二进制日志名和位置
mysql>show master status;
4）配置二进制文件名与位置
在A机上执行
mysql>CHANGE MASTER TO
    ->MASTER_HOST='192.168.184.37',
    ->MASTER_USER=’mysql36’,
    ->MASTER_PASSWORD=’mysql36’,
    ->MASTER_LOG_FILE=’mysql-bin.xxxx’,
    ->MASTER_LOG_POS=xxx;
最后两行是从B机上用show master status查询出来的。
5）开启主主复制
在A和B机上执行
mysql>START SLAVE;
mysql>SHOW SLAVE STATUS\G
当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

3.3 半自动复制

半同步复制模式。开启这种模式，可以保证slave数据库接收完master数据库发送过来的binlog日志并写入自己的中继日志中，然后反馈给master数据库，告知已经复制完毕。开启这种模式后，当出现超时，主数据库将会自动转为异步复制模式，直到至少有一台从服务器接受到主
据库的binlog，并且反馈给主数据库。这时主数据库才会切换回半同步复制模式。
1）前提条件
mysql版本是5.5或者以上，且已做好主从复制
检查mysql是否有自动加载功能,要加载安装功能插件
mysql>show variables like 'have_dynamic_loading';  #检查是否具有自动加载功能
2）A机上执行：
mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql>show plugins;  #查看是否加载成功
mysql>SET GLOBAL rpl_semi_sync_master_enabled = 1;  #开启半同步复制，默认是关闭的
3)B机上执行：
mysql>install plugin rpl_semi_sync_master soname 'semisync_slave.so';
mysql>show plugins;
mysql>SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql>STOP SLAVE IO_THREAD;
mysql>START SLAVE IO_THREAD;
最后两个命令是重启从服务器IO线程，手动将异步模式切换为半同步模式
4）修改配置文件，使得以后启动mysql就使半同步生效
rpl_semi_sync_master_enabled=1  #主库配置文件添加
rpl_semi_sync_slave_enabled=1  #从库配置文件添加
5）查看半同步参数
在A机上执行
mysql>show variables like '%semi%';
显示结果中
　　Rpl_semi_sync_master_enabled=ON表示开启半同步复制
　　Rpl_semi_sync_master_timeout表示多少毫秒后超时，将切换为异步复制
　　Rpl_semi_sync_master_wait_no_slave表示是否允许master每个事物都要等待slave接收确认，默认为ON
　　Rpl_semi_sync_master_trace_level 表示用于开启半同步复制时的调试级别，默认32
mysql>show status like '%semi%';
显示结果中
　　Rpl_semi_sync_master_status表示主服务器使用是异步还是半同步复制
　　Rpl_semi_sync_master_client表示从服务器有多少个配置成半同步复制
　　Rpl_semi_sync_master_yes_tx表示从服务器确认成功提交的数量
　　Rpl_semi_sync_master_no_tx表示从服务器确认失败提交的数量
在B机上执行
mysql>show variables like '%semi%';
显示结果中
    Rpl_semi_sync_slave_enabled=ON表示在slave已经开始半同步复制模式
　　Rpl_semi_sync_slave_trace_level=32表示用于开启半同步复制时的调试级别，默认32
mysql>show status like '%semi%';
显示结果中
    Rpl_semi_sync_slave_status表示从服务器开启半同步复制