| 1 |
SHOW VARIABLES LIKE 'log_bin'; |
| 2 |
Variable_name Value |
| 3 |
log_bin ON |
| 4 |
SHOW VARIABLES LIKE 'ndb_log_bin'; |
| 5 |
Variable_name Value |
| 6 |
ndb_log_bin ON |
| 7 |
RESET MASTER; |
| 8 |
# Find physical binlog file name format as it varies between platforms |
| 9 |
CREATE TABLE check_binlog_name (a int primary key) engine = NDB; |
| 10 |
INSERT INTO check_binlog_name VALUES (1); |
| 11 |
DROP TABLE check_binlog_name; |
| 12 |
RESET MASTER; |
| 13 |
# 1. Insert to create referencing rows |
| 14 |
CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=ndb; |
| 15 |
INSERT INTO t1 VALUES (1); |
| 16 |
FLUSH LOGS; |
| 17 |
INSERT INTO t1 VALUES (2); |
| 18 |
FLUSH LOGS; |
| 19 |
INSERT INTO t1 VALUES (3); |
| 20 |
FLUSH LOGS; |
| 21 |
INSERT INTO t1 VALUES (4); |
| 22 |
FLUSH LOGS; |
| 23 |
INSERT INTO t1 VALUES (5); |
| 24 |
FLUSH LOGS; |
| 25 |
INSERT INTO t1 VALUES (6); |
| 26 |
FLUSH LOGS; |
| 27 |
INSERT INTO t1 VALUES (7); |
| 28 |
FLUSH LOGS; |
| 29 |
INSERT INTO t1 VALUES (8); |
| 30 |
FLUSH LOGS; |
| 31 |
INSERT INTO t1 VALUES (9); |
| 32 |
FLUSH LOGS; |
| 33 |
INSERT INTO t1 VALUES (10); |
| 34 |
FLUSH LOGS; |
| 35 |
DROP TABLE t1; |
| 36 |
# 2. Save copies of the three first rows |
| 37 |
CREATE TEMPORARY TABLE save_rows_from_binlog_index AS |
| 38 |
SELECT * FROM mysql.ndb_binlog_index ORDER BY epoch LIMIT 3; |
| 39 |
# 3. Purge first five binlog files |
| 40 |
PURGE BINARY LOGS TO 'binlog.000006'; |
| 41 |
# 4. Restore saved rows, these should be removed during restart |
| 42 |
INSERT INTO mysql.ndb_binlog_index SELECT * FROM save_rows_from_binlog_index; |
| 43 |
# 5. Insert rows which will be removed |
| 44 |
insert into mysql.ndb_binlog_index values |
| 45 |
(9, 'Test.000001', 20, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 46 |
(9, 'Test.000001', 21, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 47 |
(9, 'Test.000001', 22, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 48 |
(9, './Test.000002', 23, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 49 |
(9, './Test.000002', 24, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 50 |
(9, '/home/a/b/c/d/e/f/Test.000004', 25, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 51 |
(9, 'c:\\Progra~1\\WinAmp\\Skins\\Test.000005', 26, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'), |
| 52 |
(9, './Test.000006/', 27, 0, 0, 0, 0, 0, 0, 0, 0, 'Next file'); |
| 53 |
show binary logs; |
| 54 |
Log_name File_size Encrypted |
| 55 |
binlog.000006 # # |
| 56 |
binlog.000007 # # |
| 57 |
binlog.000008 # # |
| 58 |
binlog.000009 # # |
| 59 |
binlog.000010 # # |
| 60 |
binlog.000011 # # |
| 61 |
SELECT DISTINCT(File) FROM mysql.ndb_binlog_index; |
| 62 |
File |
| 63 |
Test.000001 |
| 64 |
./Test.000002 |
| 65 |
/home/a/b/c/d/e/f/Test.000004 |
| 66 |
c:\Progra~1\WinAmp\Skins\Test.000005 |
| 67 |
./Test.000006/ |
| 68 |
binlog.000001 |
| 69 |
binlog.000002 |
| 70 |
binlog.000003 |
| 71 |
binlog.000006 |
| 72 |
binlog.000007 |
| 73 |
binlog.000008 |
| 74 |
binlog.000009 |
| 75 |
binlog.000010 |
| 76 |
# 6. Restart |
| 77 |
# restart |
| 78 |
# 7. Wait for 5 rows |
| 79 |
# 8. Show that referenced rows are still there |
| 80 |
SELECT DISTINCT(File) FROM mysql.ndb_binlog_index; |
| 81 |
File |
| 82 |
binlog.000006 |
| 83 |
binlog.000007 |
| 84 |
binlog.000008 |
| 85 |
binlog.000009 |
| 86 |
binlog.000010 |
| 87 |
RESET MASTER; |