performing DML/DDL operation over object in bin错误模拟

发表于 2011 年 7 月 21 日 由 惜分飞

1、alert文件中现象
Thu Jul 21 09:49:38 2011
performing DML/DDL operation over object in bin.
Thu Jul 21 09:51:02 2011
performing DML/DDL operation over object in bin.

2、开始模拟
1）确认回收站功能启用
SQL> show parameter recyclebin;

NAME TYPE VALUE
———————————— ———– ——————————
recyclebin string ON
如果是OFF，使用alter system set recyclebin=on;开启回收站功能

2）创建和删除表
SQL> create table t_drop
2 as
3 select * from tab;

Table created.

SQL> drop table t_drop;

Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
T_DROP BIN$qIuF4JHvDJDgQKjADAsarA==$0 TABLE 2011-07-21:09:46:52

3）查看回收站表中数据
SQL> select * from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
T TABLE
TAB_CRM_CLIENT TABLE
TEST_COUNT TABLE
V_TEST VIEW
TEST TABLE
T_DROP TABLE
TAB_OLCM TABLE

7 rows selected.

4）对回收站中数据进行dml操作
SQL> delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

5）对回收站中数据进行ddl操作
SQL> drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

6）查看回收站，错误重现
Thu Jul 21 09:56:44 2011
performing DML/DDL operation over object in bin.
Thu Jul 21 09:57:19 2011
performing DML/DDL operation over object in bin.

7）说明
由于对回收站中的对象执行了dml或者ddl操作导致alert报类此做。在回收站中不能执行ddl或者dml操作

发表在 Oracle | 评论关闭

innobackupex增量备份测试

发表于 2011 年 7 月 19 日 由 惜分飞

1、全备
/opt/mysql/product/5.5/bin/innobackupex –defaults-file=/etc/my.cnf –no-timestamp –socket=/var/run/mysqld/mysqld.sock –user=root –password=passw0rd /opt/mysql/mysql_bak/full
2、修改库中数据
mysql> create database fei;
Query OK, 1 row affected (0.03 sec)
mysql> use fei;
Database changed
mysql> create table t_1 as select * from information_schema.tables;
Query OK, 83 rows affected (0.20 sec)
Records: 83 Duplicates: 0 Warnings: 0
mysql> create table t_2 as select * from information_schema.tables;
Query OK, 84 rows affected (0.03 sec)
Records: 84 Duplicates: 0 Warnings: 0
mysql> create table t_3 as select * from information_schema.tables;
Query OK, 85 rows affected (0.04 sec)
Records: 85 Duplicates: 0 Warnings: 0

3、增量备份
/opt/mysql/product/5.5/bin/innobackupex –defaults-file=/etc/my.cnf –no-timestamp –socket=/var/run/mysqld/mysqld.sock –user=root –password=passw0rd –incremental –incremental-basedir=/opt/mysql/mysql_bak/full /opt/mysql/mysql_bak/inc

4、恢复全备
/opt/mysql/product/5.5/bin/innobackupex /opt/mysql/mysql_bak/full –apply-log

5、恢复增量备份
/opt/mysql/product/5.5/bin/innobackupex /opt/mysql/mysql_bak/full –incremental –incremental-dir=/opt/mysql/mysql_bak/inc –apply-log

6、还原数据库到默认目录
/opt/mysql/product/5.5/bin/innobackupex –defaults-file=/etc/my.cnf /opt/mysql/mysql_bak/full/ –copy-back

7、启动数据库测试
结论为：所有新创建的对象都没恢复成功
后来创建的库和表结构都不存在，在执行增量恢复的时候，有如下错误提示：
110719 11:49:57 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: error: cannot open /opt/mysql/mysql_bak/full/fei/t_2.ibd
xtrabackup: Error: xtrabackup_apply_delta() failed.
xtrabackup: page size for /opt/mysql/mysql_bak/inc/fei/t_3.ibd.delta is 16384 bytes
110719 11:49:57 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: error: cannot open /opt/mysql/mysql_bak/full/fei/t_3.ibd
xtrabackup: Error: xtrabackup_apply_delta() failed.
xtrabackup: page size for /opt/mysql/mysql_bak/inc/fei/t_1.ibd.delta is 16384 bytes
110719 11:49:57 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: error: cannot open /opt/mysql/mysql_bak/full/fei/t_1.ibd
xtrabackup: Error: xtrabackup_apply_delta() failed.
fei.*对象是在全备之后创建的，全备份集中并不存在，因此应用增量时就报了错，此时如果原始数据文件已经损坏，那么fei.*的数据就无法恢复了，因为缺少了其数据文件。innobackupex增量备份和恢复功能还不完善，有新增对象时会出错

发表在 MySQL | 一条评论

mysql-cluster 7.x安装（linux）

发表于 2011 年 7 月 18 日 由 惜分飞

一、整体规划
192.168.11.10（ndb）
192.168.11.11（ndb）
192.168.11.12（mgm）
192.168.11.13（sql）
192.168.11.14（sql）

二、安装mysql-cluster软件(root)
所有节点相同操作，如果没有mysql用户请创建mysql用户和组
groupadd mysql
useradd -g mysql mysql

上传至/tmp目录，开始解压安装
cd/tmp
tarxzvftarxzvfmysql-cluster-gpl-7.1.15-linux-x86_64-glibc23.tar.gz
mvmysql-cluster-gpl-7.1.15-linux-x86_64-glibc23/opt/mysql_cluster
chownmysql.mysql-R/opt/mysql_cluster

三、管理节点（mgm）配置(mysql)
配置mgm节点的config.ini文件
[NDBDDEFAULT]
NoOfReplicas=2
[NDB_MGMD]
NodeId=1
hostname=192.168.11.12
[NDBD]
NodeId=11
hostname=192.168.11.11
datadir=/opt/mysql_cluster/ndbdata
[NDBD]
NodeId=12
hostname=192.168.11.10
datadir=/opt/mysql_cluster/ndbdata
[MYSQLD]
NodeId=21
hostname=192.168.11.13
[MYSQLD]
NodeId=22
hostname=192.168.11.14

启动mgm节点
/opt/mysql_cluster/bin/ndb_mgmd -f /opt/mysql_cluster/config.ini –configdir=/opt/mysql_cluster

四、数据节点配置(mysql)
配置ndb节点参数(/opt/mysql_cluster/my.cnf)
[mysqld]
ndbcluster
ndb-connectstring=192.168.11.12
[mysql_cluster]
ndb-connectstring=192.168.11.12

创建ndbdata文件夹
mkdir /opt/mysql_cluster/ndbdata

启动ndb节点
/opt/mysql_cluster/bin/ndbd –defaults-file=/opt/mysql_cluster/my.cnf –initial

五、SQL节点配置(mysql)
初始化sql节点
/opt/mysql_cluster/scripts/mysql_install_db –user=mysql –datadir=/opt/mysql_cluster/data –basedir=/opt/mysql_cluster/

配置my.cnf文件(root)
mv /opt/mysql_cluster/my-large.cnf /etc/my.cnf
添加
[mysqld]
ndbcluster
ndb-connectstring=192.168.11.12
[mysql_cluster]
ndb-connectstring=192.168.11.12

修改mysql.server(mysql)
/opt/mysql_cluster/support-files/mysql.server中的datadir和basedir为
basedir=/opt/mysql_cluster
datadir=/opt/mysql_cluster/data

启动sql节点(mysql)
/opt/mysql_cluster/support-files/mysql.server start

六、查看集群状态(msyql)
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=11 @192.168.11.11 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0, Master)
id=12 @192.168.11.10 (mysql-5.1.56 ndb-7.1.15, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.11.12 (mysql-5.1.56 ndb-7.1.15)

[mysqld(API)] 2 node(s)
id=21 @192.168.11.13 (mysql-5.1.56 ndb-7.1.15)
id=22 @192.168.11.14 (mysql-5.1.56 ndb-7.1.15)

发表在 MySQL安装配置 | 评论关闭