mysql 备份、同步操作

(第二十一周作业)

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表示从服务器开启半同步复制

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,383评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,522评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,852评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,621评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,741评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,929评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,076评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,803评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,265评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,582评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,716评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,395评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,039评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,027评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,488评论 2赞 361
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,612评论 2赞 350