数据库因为历史原因一直哉使用msyqldump作为数据备份工具,随着数据越来越多,当恢复的时候,慢如蜗牛,都是小时级别的(没有做主从),最近经过验证和测试,发现XtraBackup是个不错的工具,打算用用。
XtraBackup 有两个工具:xtrabackup 和 innobackupex:
- xtrabackup 本身只能备份 InnoDB 和 XtraDB ,不能备份 MyISAM;
- innobackupex 本身是 Hot Backup 脚本修改而来,同时可以备份 MyISAM 和 InnoDB,但是备份 MyISAM 需要加读锁。
安装依赖
yum -y install libev.x86_64 perl-DBD-MySQL
在线安装XtraBackup
rpm -Uhv https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
检查状态
yum list | grep percona
创建备份
#备份的目录,会自动生成日期文件目录
innobackupex --user=root --password=**** /root/db-backup/
#if mysql.sock error,append under segment to mysql-clients.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
#and then reload conf
service mysql reload
#options, bin-log on
[mysqld]
log-bin=mysql-bin
准备阶段
# rollback uncommited and flush commited tx
innobackupex --user=root --password=**** --apply-log /root/db-backup/
恢复数据
#slave mysql
service mysql stop
rm -rf /var/lib/mysql/data/*
#copy-back or move-back
innobackupex --user=root --password=**** --copy-back /root/db-backup/2016-04-10_00-38-46
service mysql start
#if server not work. pls run under command
chown -R mysql:mysql /var/lib/mysql/
install MariaDB on CenOS6.5
#add repo
vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name=MariaDB
baseurl=http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum update (may be error , so ignore it)
yum install MariaDB-server -y
yum install MariaDB-client -y
service mysql start
chkconfig mysql on
#secure config
mysql_secure_installation
mysql -u root -p
#允许root远程登录
$ mysql -uroot -p123456
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
> FLUSH PRIVILEGES;
查看binlog日志
$mysqlbinlog mysql-bin.000001| more
#mysql>show master logs
生成新binlog文件
- mysql restart
- flush logs
- greater then limit
查看进程信息
show processlist;
网友评论