93 lines
3.0 KiB
Plaintext
93 lines
3.0 KiB
Plaintext
#
|
|
# create a table with some data with and without binlogging
|
|
#
|
|
create table t1 (a int key, b int) engine ndb;
|
|
insert into t1 values (1,1);
|
|
#
|
|
# reset and restore schema
|
|
drop table t1;
|
|
reset master;
|
|
select * from t1 where 1=0 /* force discover table */;
|
|
a b
|
|
#
|
|
# restore and _no_ binlog
|
|
#
|
|
# check the binlog, should be empty apart from our marker
|
|
# create dummy table to wait for to ensure Binlog contains any previous events
|
|
create table binlogmarker (a int) engine=ndb;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
binlog.000001 # Query # # use `test`; create table binlogmarker (a int) engine=ndb
|
|
#
|
|
# reset and restore schema again
|
|
drop table binlogmarker;
|
|
drop table t1;
|
|
reset master;
|
|
select * from t1 where 1=0 /* force discover table */;
|
|
a b
|
|
#
|
|
# restore and binlog should now happen as well as the marker
|
|
#
|
|
# check the binlog, should contain data
|
|
# create dummy table to wait for to ensure Binlog contains any previous events
|
|
create table binlogmarker (a int) engine=ndb;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
binlog.000001 # Query # # BEGIN
|
|
binlog.000001 # Table_map # # table_id: # (test.t1)
|
|
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
|
|
binlog.000001 # Write_rows # # table_id: #
|
|
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F
|
|
binlog.000001 # Query # # COMMIT
|
|
binlog.000001 # Query # # use `test`; create table binlogmarker (a int) engine=ndb
|
|
drop table binlogmarker, t1;
|
|
#
|
|
# Now more complex using "BANK schema" including restore of log
|
|
#
|
|
CREATE DATABASE IF NOT EXISTS BANK;
|
|
DROP DATABASE BANK;
|
|
CREATE DATABASE BANK default charset=latin1 default collate=latin1_bin;
|
|
USE BANK;
|
|
CREATE TABLE GL ( TIME BIGINT UNSIGNED NOT NULL,
|
|
ACCOUNT_TYPE INT UNSIGNED NOT NULL,
|
|
BALANCE INT UNSIGNED NOT NULL,
|
|
DEPOSIT_COUNT INT UNSIGNED NOT NULL,
|
|
DEPOSIT_SUM INT UNSIGNED NOT NULL,
|
|
WITHDRAWAL_COUNT INT UNSIGNED NOT NULL,
|
|
WITHDRAWAL_SUM INT UNSIGNED NOT NULL,
|
|
PURGED INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY USING HASH (TIME,ACCOUNT_TYPE))
|
|
ENGINE = NDB;
|
|
CREATE TABLE ACCOUNT ( ACCOUNT_ID INT UNSIGNED NOT NULL,
|
|
OWNER INT UNSIGNED NOT NULL,
|
|
BALANCE INT UNSIGNED NOT NULL,
|
|
ACCOUNT_TYPE INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY USING HASH (ACCOUNT_ID))
|
|
ENGINE = NDB;
|
|
CREATE TABLE TRANSACTION ( TRANSACTION_ID BIGINT UNSIGNED NOT NULL,
|
|
ACCOUNT INT UNSIGNED NOT NULL,
|
|
ACCOUNT_TYPE INT UNSIGNED NOT NULL,
|
|
OTHER_ACCOUNT INT UNSIGNED NOT NULL,
|
|
TRANSACTION_TYPE INT UNSIGNED NOT NULL,
|
|
TIME BIGINT UNSIGNED NOT NULL,
|
|
AMOUNT INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY USING HASH (TRANSACTION_ID,ACCOUNT))
|
|
ENGINE = NDB;
|
|
CREATE TABLE SYSTEM_VALUES ( SYSTEM_VALUES_ID INT UNSIGNED NOT NULL,
|
|
VALUE BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY USING HASH (SYSTEM_VALUES_ID))
|
|
ENGINE = NDB;
|
|
CREATE TABLE ACCOUNT_TYPE ( ACCOUNT_TYPE_ID INT UNSIGNED NOT NULL,
|
|
DESCRIPTION CHAR(64) NOT NULL,
|
|
PRIMARY KEY USING HASH (ACCOUNT_TYPE_ID))
|
|
ENGINE = NDB;
|
|
#
|
|
# reset, restore and binlog should _not_ happen
|
|
reset master;
|
|
select count(*) from TRANSACTION;
|
|
count(*)
|
|
3444
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
DROP DATABASE BANK;
|