mysqlbackup在线配置MySQL主从架构

前言：MySQL数据库很多情况下都是用于搭建电商平台，电商平台就意味着为公司赚钱的平台，必须24小时在线的；我们也搭建了属于自己的电商平台，但是最近反应需要增加一台从库来缓解主库的读取压力。网上百度了很多相关的方法，总结如下：MySQLdump搭建，该方法很简单，但是操作的过程中需要锁表，并停止应用。该方法适合系统未上线时操作，新手可以搭建用于学习；（ http://www.linuxidc.com/Linux/2014-11/109030.htm ）Xtrabackup搭建，该方法需要了解Xtrabackup工具的备份还原，可以实现在线搭建主从架构。MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htmMySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htmMySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm用XtraBackup实现MySQL的主从复制快速部署【主不锁表】 http://www.linuxidc.com/Linux/2012-10/71919p2.htm安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htmMySQL一直推荐的企业版的MySQL备份工具：MySQLbackup，抱着学习的心态就在测试环境中通过MySQLbakcup工具来搭建主从架构；在进行以下操作之前，需要先进行以下两个设置：主库和备库的参数文件已经修改完成、在主库上面创建主从连接用户；以下是详细的整理步骤：

数据库	主机名	IP地址	同步用户	备份位置
主数据库	Mysql01	192.168.47.152	server01	/backup
从数据库	Mysql02	192.168.47.151	server01	/backup

2 主库的操作步骤2.1 对主库进行全备，脚本如下mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log备份的目录为/backup，请确认这个目录的存在；2.2 记录这个时候主库的binlog状态 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set （0.00 sec）
2.3 为了验证主从是没有问题的，这个时候可以往主库的某个数据库插入数据，然后再记录状态 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 500 | | | +------------------+----------+--------------+------------------+ 1 row in set （0.00 sec）2.4 把备份从主库拷贝到从库 [root@mysql01 backup]# service iptables stop iptables：清除防火墙规则： [确定] iptables：将链设置为政策 ACCEPT：filter [确定] iptables：正在卸载模块： [确定] root@mysql01 backup]# scp bak.tar root@mysql02:/backup/* Warning: Permanently added the RSA host key for IP address "192.168.47.151" to the list of known hosts. root@mysql02"s password: bak.tar 100% 69MB 23.1MB/s 00:03 需要先关闭防火墙 3.从库的操作步骤
3.1 进行从库的恢复 [root@mysql01 backup]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26] Copyright （c） 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful "copy-back" run mysqlbackup prints "mysqlbackup completed OK！". 141118 16:19:35 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-18.16-19-35_copy_back.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /data/mysql innodb_data_home_dir = /data/mysql innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /data/mysql/ innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = Null innodb_checksum_algorithm = none -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /backup/datadir innodb_data_home_dir = /backup/datadir innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /backup/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = 16384 innodb_checksum_algorithm = none mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 141118 16:19:35 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin. Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. 141118 16:19:35 mysqlbackup: INFO: Copying /backup/datadir/ibdata1. 141118 16:19:37 mysqlbackup: INFO: Copying the database directory "john" 141118 16:19:37 mysqlbackup: INFO: Copying the database directory "mysql" 141118 16:19:37 mysqlbackup: INFO: Copying the database directory "performance_schema" 141118 16:19:37 mysqlbackup: INFO: Completing the copy of all non-innodb files. 141118 16:19:37 mysqlbackup: INFO: Copying the log file "ib_logfile0" 141118 16:19:37 mysqlbackup: INFO: Copying the log file "ib_logfile1" 141118 16:19:39 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql 141118 16:19:39 mysqlbackup: INFO: Copy-back operation completed successfully. 141118 16:19:39 mysqlbackup: INFO: Finished copying backup files to "/data/mysql"
更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-11/109773p2.htm

Oracle数据库静默安装总结ORA-01948和ORA-04052错误的解决方法相关资讯 Mysqlbackup MySQL主从

MySQL主从环境下存储过程，函数，（今 07:26）
在 CentOS7 上部署 MySQL 主从（08月20日）
恢复MySQL主从数据一致性的总结（12/29/2015 22:14:15）

XtraBackup构建MySQL主从环境（09月01日）
MySQL主从失败使用bin-log恢复（02月20日）
MySQL5.6 主从复制配置（12/21/2015 19:10:09）

本文评论查看全部评论（0）