WordPress数据库错误: [Got error 28 from storage engine]
SHOW FULL COLUMNS FROM `wp_options`

WordPress数据库错误: [Got error 28 from storage engine]
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (256) ORDER BY t.name ASC

mysql innodb xtrabackup备份 – Crazy knowledge base
Crazy knowledge base

mysql innodb xtrabackup备份

mysql的备份方式


1.mysqldump
效率比较低,备份和还原的速度都很慢,任何数据插入和更新操作都会被挂起

2.mysqlhotcopy
mysqlhotcopy 是专门针对myisam 数据表进行备份,备份的过程中,任何数据插入和更新操作都会被挂起

3.准备一台从服务器,专门做备份(master-slave方式)

4.xtrabackup 是 percona 的一个开源项目,可热备份innodb ,XtraDB,MyISAM(会锁表)

Xtrabackup有两个主要的工具:xtrabackup、innobackupex
xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表
innobackupex-1.5.1则封装了xtrabackup,是一个脚本封装,所以能同时备份处理innodb和myisam,但在处理myisam时需要加一个读锁

相比之下 xtrabackup 备份比较靠谱

下载 xtrabackup-1.6-245.rhel5.x86_64.rpm
安装 rpm -ivh  xtrabackup-1.6-245.rhel5.x86_64.rpm

备份(xtrabackup只备份数据文件,并不备份数据表结构(.frm))
xtrabackup –backup –target-dir=/backup/base
恢复(执行两次)
xtrabackup –prepare –target-dir=/backup/base
xtrabackup –prepare –target-dir=/backup/base

二、 增量备份

首先确保两边的/etc/my.cnf 文件里边都指定了datadir=/var/lib/mysql

对比innobackupex和xtrabackup,innobackupex操作起来更方便,但是innobackupex会有短暂的锁表.xtrabackup备份还有另一个重要特性:增量备份.

1. 全量备份
xtrabackup –backup –target-dir=/backup/base

2. 增量备份
xtrabackup –backup –target-dir=/backup/inc1 –incremental-basedir=/backup/base

在增量备份的目录下,数据文件都是以.delta结尾的。增量备份只备份上一次全量备份之后被修改过的page,所以增量备份一般只暂用较少的空间。

3. 增量备份恢复
分别对全量、增量备份各做一次prepare操作。
xtrabackup –prepare –target-dir=/backup/base
xtrabackup –prepare –target-dir=/backup/base –incremental-dir=/backup/inc1
先停止mysql,把/backup/base下的数据文件COPY放到你的MySQL数据目录下,重启mysql恢复数据。

一般的备份 比如不会换全新的环境 用xtrabackup 就可以了

在不停生产的情况下备份数据给slave

接着备份数据:
innobackupex-1.5.1   –defaults-file=/etc/my.cnf –stream=tar  –user=root –password=**** –port=3306 –slave-info /bak | gzip>/bak/bak_mysql.tar.gz

将bak_mysql.tar.gz cp 到另一台机器上
scp  /bak/bak_mysql.tar.gz root@host:/home/bak_mysql.tar.gz

解压bak_mysql.tar.gz到/bak
tar -ixvf mysqlbak.tar(一定要加i 参数  我也不知道为什么)

恢复先 –apply-log  再 –copy-back
innobackupex-1.5.1 –defaults-file=/etc/my.cnf  –user=root  –password=XXXX –port=3306 –apply-log /bak

–copy-back这里容易报错
(innobackupex-1.5.1: Copying directory ‘/bak/tech_soft_drp_ywmy’
innobackupex-1.5.1: Copying directory ‘/bak/mysql’
mkdir: 无法创建目录 “arb/mysql/mysql”: 文件已存在
innobackupex-1.5.1: Error: Failed to create directory ‘arb/mysql/mysql’ : 没有那个文件或目录 at /usr/bin/innobackupex-1.5.1 line 336.)
我也不知道咋整。好像是官方1bug 见https://bugs.launchpad.net/percona-xtrabackup/+bug/737569

我看了看/bak目录  决定把mysql  test 等自带的一些库直接rm 了。执行已下命令成功

innobackupex-1.5.1  –defaults-file=/etc/my.cnf  –user=root  –password=XXXX –port=3306  –copy-back /bak

重启数据库服务又有问题
(110719 19:30:16 mysqld_safe Starting mysqld daemon with databases from arb/mysql
110719 19:30:16 [Note] Plugin ‘FEDERATED’ is disabled.
110719 19:30:16 InnoDB: The InnoDB memory heap is disabled
110719 19:30:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110719 19:30:16 InnoDB: Compressed tables use zlib 1.2.3
110719 19:30:16 InnoDB: Using Linux native AIO
110719 19:30:16 InnoDB: Initializing buffer pool, size = 2.0G
110719 19:30:16 InnoDB: Completed initialization of buffer pool
110719 19:30:16  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: ‘open’.
InnoDB: Cannot continue operation.
110719 19:30:16 mysqld_safe mysqld from pid file arb/mysql/r410.pid ended)


不知道啥情况。网上搜搜好像是权限问题 直接将mysql 目录 chmod 777 -R mysql 重启服务好了

菩萨保佑 问题好了

将my.cnf 配置成server-id=2  主从配置在这里不多提了
CHANGE MASTER TO MASTER_HOST=’xxxx’,MASTER_USER=’xx’,MASTER_PASSWORD=’xxx’,MASTER_LOG_FILE=’mysql-bin.000047′,MASTER_LOG_POS=43446639

MASTER_LOG_FILE这个值去/bak 下面的 xtrabackup_binlog_info 找

大功告成

省了以前通宵用mysqldump 来同步数据再做主从同步

引用:http://www.2cto.com/database/201107/96804.html

mysql innodb xtrabackup备份

WordPress数据库错误: [Got error 28 from storage engine]
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (255) ORDER BY t.name ASC

WordPress数据库错误: [Got error 28 from storage engine]
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (257) ORDER BY t.name ASC

滚动到顶部