| 1 |
SET autocommit=1; |
| 2 |
RESET MASTER; |
| 3 |
# Find physical binlog file name format as it varies between platforms |
| 4 |
CREATE TABLE check_binlog_name (a int primary key) engine = NDB; |
| 5 |
INSERT INTO check_binlog_name VALUES (1); |
| 6 |
DROP TABLE check_binlog_name; |
| 7 |
RESET MASTER; |
| 8 |
# ======================================= |
| 9 |
# 1. Showing configuration required by test |
| 10 |
# ======================================= |
| 11 |
# Show that binlogging of NDB changes are on |
| 12 |
SHOW VARIABLES LIKE 'log_bin'; |
| 13 |
Variable_name Value |
| 14 |
log_bin ON |
| 15 |
SHOW VARIABLES LIKE 'ndb_log_bin'; |
| 16 |
Variable_name Value |
| 17 |
ndb_log_bin ON |
| 18 |
# Turn off binlogging statements in this file |
| 19 |
SET sql_log_bin = OFF; |
| 20 |
# Turn off writing queries to query log |
| 21 |
SET sql_log_off = ON; |
| 22 |
# Turn off using backslash as escape to avoid interfering with filename |
| 23 |
SET sql_mode=(SELECT CONCAT(@@sql_mode,',NO_BACKSLASH_ESCAPES')); |
| 24 |
# |
| 25 |
# InnoDB adaptive flushing on |
| 26 |
adaptive_flushing: ON |
| 27 |
# |
| 28 |
# Innodb buffer pool size |
| 29 |
buffer_pool_size: 24 MB |
| 30 |
# |
| 31 |
# InnoDB redo log capacity controls how much can be changed without flush |
| 32 |
redo_log_capacity: 10 MB |
| 33 |
# |
| 34 |
# The ndb binlog purger rate limit |
| 35 |
purge_rate: 8192 |
| 36 |
# |
| 37 |
# Show primary keys of mysql.ndb_binlog_index table |
| 38 |
SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS |
| 39 |
WHERE TABLE_SCHEMA = 'mysql' AND |
| 40 |
TABLE_NAME = 'ndb_binlog_index' AND |
| 41 |
COLUMN_KEY = 'PRI' |
| 42 |
ORDER BY ORDINAL_POSITION; |
| 43 |
COLUMN_NAME COLUMN_TYPE |
| 44 |
epoch bigint unsigned |
| 45 |
orig_server_id int unsigned |
| 46 |
orig_epoch bigint unsigned |
| 47 |
# |
| 48 |
# Show columns of mysql.ndb_binlog_index table |
| 49 |
SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS |
| 50 |
WHERE TABLE_SCHEMA = 'mysql' AND |
| 51 |
TABLE_NAME = 'ndb_binlog_index' |
| 52 |
ORDER BY ORDINAL_POSITION; |
| 53 |
COLUMN_NAME COLUMN_TYPE |
| 54 |
Position bigint unsigned |
| 55 |
File varchar(255) |
| 56 |
epoch bigint unsigned |
| 57 |
inserts int unsigned |
| 58 |
updates int unsigned |
| 59 |
deletes int unsigned |
| 60 |
schemaops int unsigned |
| 61 |
orig_server_id int unsigned |
| 62 |
orig_epoch bigint unsigned |
| 63 |
gci int unsigned |
| 64 |
next_position bigint unsigned |
| 65 |
next_file varchar(255) |
| 66 |
# ======================================= |
| 67 |
# 2. Create 9 binary log files |
| 68 |
# ======================================= |
| 69 |
FLUSH BINARY LOGS; |
| 70 |
FLUSH BINARY LOGS; |
| 71 |
FLUSH BINARY LOGS; |
| 72 |
FLUSH BINARY LOGS; |
| 73 |
FLUSH BINARY LOGS; |
| 74 |
FLUSH BINARY LOGS; |
| 75 |
FLUSH BINARY LOGS; |
| 76 |
FLUSH BINARY LOGS; |
| 77 |
FLUSH BINARY LOGS; |
| 78 |
SHOW BINARY LOGS; |
| 79 |
Log_name File_size Encrypted |
| 80 |
binlog.000001 201 No |
| 81 |
binlog.000002 201 No |
| 82 |
binlog.000003 201 No |
| 83 |
binlog.000004 201 No |
| 84 |
binlog.000005 201 No |
| 85 |
binlog.000006 201 No |
| 86 |
binlog.000007 201 No |
| 87 |
binlog.000008 201 No |
| 88 |
binlog.000009 201 No |
| 89 |
binlog.000010 157 No |
| 90 |
# ======================================= |
| 91 |
# 3. Add additional rows to ndb_binlog_index corresponding to the |
| 92 |
# binary logs, the number of rows should exceed the configured |
| 93 |
# ndb_log_rate_limit |
| 94 |
# ======================================= |
| 95 |
SELECT COUNT(*) FROM mysql.ndb_binlog_index; |
| 96 |
COUNT(*) |
| 97 |
90000 |
| 98 |
SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File; |
| 99 |
COUNT(*) File |
| 100 |
10000 BINLOG_NAME1 |
| 101 |
10000 BINLOG_NAME2 |
| 102 |
10000 BINLOG_NAME3 |
| 103 |
10000 BINLOG_NAME4 |
| 104 |
10000 BINLOG_NAME5 |
| 105 |
10000 BINLOG_NAME6 |
| 106 |
10000 BINLOG_NAME7 |
| 107 |
10000 BINLOG_NAME8 |
| 108 |
10000 BINLOG_NAME9 |
| 109 |
# ======================================= |
| 110 |
# 4. Purging the binary logs |
| 111 |
# ======================================= |
| 112 |
# |
| 113 |
# Remove the first binlog |
| 114 |
PURGE BINARY LOGS TO 'binlog.000002'; |
| 115 |
# Check that rows for first binlog file is removed |
| 116 |
SELECT COUNT(*) FROM mysql.ndb_binlog_index |
| 117 |
WHERE File IN (CONCAT('BINLOG_NAME', 1)); |
| 118 |
COUNT(*) |
| 119 |
0 |
| 120 |
# |
| 121 |
# Remove the subsequent two binlogs |
| 122 |
PURGE BINARY LOGS TO 'binlog.000004'; |
| 123 |
# Check that rows for the two purged binlog files has been removed |
| 124 |
SELECT COUNT(*) FROM mysql.ndb_binlog_index |
| 125 |
WHERE File IN (CONCAT('BINLOG_NAME', 2), |
| 126 |
CONCAT('BINLOG_NAME', 3)); |
| 127 |
COUNT(*) |
| 128 |
0 |
| 129 |
# |
| 130 |
# Remove the subsequent five binlogs |
| 131 |
PURGE BINARY LOGS TO 'binlog.000009'; |
| 132 |
# Check that rows for the five purged binlog files has been removed |
| 133 |
SELECT COUNT(*) FROM mysql.ndb_binlog_index |
| 134 |
WHERE File IN (CONCAT('BINLOG_NAME', 4), |
| 135 |
CONCAT('BINLOG_NAME', 5), |
| 136 |
CONCAT('BINLOG_NAME', 6), |
| 137 |
CONCAT('BINLOG_NAME', 7), |
| 138 |
CONCAT('BINLOG_NAME', 8)); |
| 139 |
COUNT(*) |
| 140 |
0 |
| 141 |
# Still rows left for the last binlog file |
| 142 |
SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File; |
| 143 |
COUNT(*) File |
| 144 |
10000 BINLOG_NAME9 |
| 145 |
# Rotate to new binary log and purge the last binlog file with rows |
| 146 |
FLUSH BINARY LOGS; |
| 147 |
PURGE BINARY LOGS TO 'binlog.000010'; |
| 148 |
# Show status counters: |
| 149 |
purged_rows: 90000 |
| 150 |
purged_files: 9 |
| 151 |
# ======================================= |
| 152 |
# 5. Check that all rows has been removed from ndb_binlog_index |
| 153 |
# ======================================= |