DROP DATABASE IF EXISTS meb_extreme_db1; DROP DATABASE IF EXISTS MEB_EXTREME_DB2; DROP DATABASE IF EXISTS MebExtreme_Db3; # # Check setting of lower_case_table_names. # SELECT @@lower_case_table_names; @@lower_case_table_names 0 # # Set character set for table contents. # SET NAMES 'utf8'; # # Create some databases. # CREATE DATABASE meb_extreme_db1; CREATE DATABASE MEB_EXTREME_DB2; CREATE DATABASE MebExtreme_Db3; # # Create a table. # CREATE TABLE meb_extreme_db1.table1 ( c01 BIT, c02 BIT(64), c03 TINYINT, c04 TINYINT UNSIGNED, c05 TINYINT ZEROFILL, c06 BOOL, c07 SMALLINT, c08 SMALLINT UNSIGNED, c09 SMALLINT ZEROFILL, c10 MEDIUMINT, c11 MEDIUMINT UNSIGNED, c12 MEDIUMINT ZEROFILL, c13 INT, c14 INT UNSIGNED, c15 INT ZEROFILL, c16 BIGINT, c17 BIGINT UNSIGNED, c18 BIGINT ZEROFILL, c19 FLOAT, c20 FLOAT UNSIGNED, c21 FLOAT ZEROFILL, c22 DOUBLE, c23 DOUBLE UNSIGNED, c24 DOUBLE ZEROFILL, c25 DECIMAL, c26 DECIMAL UNSIGNED, c27 DECIMAL ZEROFILL, # c28 DATE, c29 DATETIME, c30 TIMESTAMP, c31 TIME, c32 YEAR, # c33 CHAR, c34 CHAR(0), c35 CHAR(1), c36 CHAR(255), c37 NATIONAL CHAR, c38 NATIONAL CHAR(0), c39 NATIONAL CHAR(1), c40 NATIONAL CHAR(255), c41 CHAR CHARACTER SET UCS2, c42 CHAR(0) CHARACTER SET UCS2, c43 CHAR(1) CHARACTER SET UCS2, c44 CHAR(255) CHARACTER SET UCS2, # c45 VARCHAR(0), c46 VARCHAR(1), c47 VARCHAR(255), c48 VARCHAR(261), c49 NATIONAL VARCHAR(0), c50 NATIONAL VARCHAR(1), c51 NATIONAL VARCHAR(255), c52 NATIONAL VARCHAR(261), c53 VARCHAR(0) CHARACTER SET UCS2, c54 VARCHAR(1) CHARACTER SET UCS2, c55 VARCHAR(255) CHARACTER SET UCS2, c56 VARCHAR(261) CHARACTER SET UCS2, # c57 BINARY, c58 BINARY(0), c59 BINARY(1), c60 BINARY(255), # c61 VARBINARY(0), c62 VARBINARY(1), c63 VARBINARY(255), c64 VARBINARY(261), # c65 TINYBLOB, c66 TINYTEXT, c67 TINYTEXT CHARACTER SET UCS2, c68 BLOB, c69 TEXT, c70 TEXT CHARACTER SET UCS2, c71 MEDIUMBLOB, c72 MEDIUMTEXT, c73 MEDIUMTEXT CHARACTER SET UCS2, c74 LONGBLOB, c75 LONGTEXT, c76 LONGTEXT CHARACTER SET UCS2, # c77 ENUM('a','b','c'), c78 SET('a','b','c'), # crn INT -- row number ) ENGINE=InnoDB DEFAULT CHARSET latin1; # # Insert minimum values. # INSERT INTO meb_extreme_db1.table1 VALUES ( b'0', -- c01 b'0000000000000000000000000000000000000000000000000000000000000000', -- c02 -128, -- c03 0, -- c04 000, -- c05 false, -- c06 -32768, -- c07 0, -- c08 00000, -- c09 -8388608, -- c10 0, -- c11 00000000, -- c12 -2147483648, -- c13 0, -- c14 0000000000, -- c15 -9223372036854775808, -- c16 0, -- c17 00000000000000000000, -- c18 -3.402823466E+38, -- c19 1.175494351E-38, -- c20 000000000000, -- c21 -1.7976931348623E+308, -- c22 three digits cut for ps-protocol 2.2250738585072E-308, -- c23 three digits cut for ps-protocol 0000000000000000000000, -- c24 -9999999999, -- c25 0, -- c26 0000000000, -- c27 # '1000-01-01', -- c28 '1000-01-01 00:00:00', -- c29 '1970-01-02 00:00:01', -- c30 one day later due to timezone issues '-838:59:59', -- c31 '1901', -- c32 # '', -- c33 '', -- c34 '', -- c35 '', -- c36 '', -- c37 '', -- c38 '', -- c39 '', -- c40 '', -- c41 '', -- c42 '', -- c43 '', -- c44 # '', -- c45 '', -- c46 '', -- c47 '', -- c48 '', -- c49 '', -- c50 '', -- c51 '', -- c52 '', -- c53 '', -- c54 '', -- c55 '', -- c56 # '', -- c57 '', -- c58 '', -- c59 '', -- c60 # '', -- c61 '', -- c62 '', -- c63 '', -- c64 # '', -- c65 '', -- c66 '', -- c67 '', -- c68 '', -- c69 '', -- c70 '', -- c71 '', -- c72 '', -- c73 '', -- c74 '', -- c75 '', -- c76 # 'a', -- c77 '', -- c78 # 1 -- crn -- row number ); # # Insert maximum values. # INSERT INTO meb_extreme_db1.table1 VALUES ( b'1', -- c01 b'1111111111111111111111111111111111111111111111111111111111111111', -- c02 127, -- c03 255, -- c04 255, -- c05 true, -- c06 32767, -- c07 65535, -- c08 65535, -- c09 8388607, -- c10 16777215, -- c11 16777215, -- c12 2147483647, -- c13 4294967295, -- c14 4294967295, -- c15 9223372036854775807, -- c16 18446744073709551615, -- c17 18446744073709551615, -- c18 3.402823466E+38, -- c19 3.402823466E+38, -- c20 3.402823466E+38, -- c21 1.7976931348623E+308, -- c22 three digits cut for ps-protocol 1.7976931348623E+308, -- c23 three digits cut for ps-protocol 1.7976931348623E+308, -- c24 three digits cut for ps-protocol 9999999999, -- c25 9999999999, -- c26 9999999999, -- c27 # '9999-12-31', -- c28 '9999-12-31 23:59:59', -- c29 '2038-01-08 03:14:07', -- c30 one day earlier due to timezone issues '838:59:59', -- c31 '2155', -- c32 # x'ff', -- c33 '', -- c34 x'ff', -- c35 REPEAT(x'ff',255), -- c36 _utf8 x'efbfbf', -- c37 '', -- c38 _utf8 x'efbfbf', -- c39 REPEAT(_utf8 x'efbfbf',255), -- c40 _ucs2 x'ffff', -- c41 '', -- c42 _ucs2 x'ffff', -- c43 REPEAT(_ucs2 x'ffff',255), -- c44 # '', -- c45 x'ff', -- c46 REPEAT(x'ff',255), -- c47 REPEAT(x'ff',261), -- c48 '', -- c49 _utf8 x'efbfbf', -- c50 REPEAT(_utf8 x'efbfbf',255), -- c51 REPEAT(_utf8 x'efbfbf',261), -- c52 '', -- c53 _ucs2 x'ffff', -- c54 REPEAT(_ucs2 x'ffff',255), -- c55 REPEAT(_ucs2 x'ffff',261), -- c56 # x'ff', -- c57 '', -- c58 x'ff', -- c59 REPEAT(x'ff',255), -- c60 # '', -- c61 x'ff', -- c62 REPEAT(x'ff',255), -- c63 REPEAT(x'ff',261), -- c64 # 'tinyblob', -- c65 not using maximum value here 'tinytext', -- c66 not using maximum value here 'tinytext-ucs2', -- c67 not using maximum value here 'blob', -- c68 not using maximum value here 'text', -- c69 not using maximum value here 'text-ucs2', -- c70 not using maximum value here 'mediumblob', -- c71 not using maximum value here 'mediumtext', -- c72 not using maximum value here 'mediumtext-ucs2', -- c73 not using maximum value here 'longblob', -- c74 not using maximum value here 'longtext', -- c75 not using maximum value here 'longtext-ucs2', -- c76 not using maximum value here # 'c', -- c77 'a,b,c', -- c78 # 2 -- crn -- row number ); # # Insert a row with NULL values. # INSERT INTO meb_extreme_db1.table1 VALUES ( NULL, -- c01 NULL, -- c02 NULL, -- c03 NULL, -- c04 NULL, -- c05 NULL, -- c06 NULL, -- c07 NULL, -- c08 NULL, -- c09 NULL, -- c10 NULL, -- c11 NULL, -- c12 NULL, -- c13 NULL, -- c14 NULL, -- c15 NULL, -- c16 NULL, -- c17 NULL, -- c18 NULL, -- c19 NULL, -- c20 NULL, -- c21 NULL, -- c22 NULL, -- c23 NULL, -- c24 NULL, -- c25 NULL, -- c26 NULL, -- c27 # NULL, -- c28 NULL, -- c29 NULL, -- c30 NULL, -- c31 NULL, -- c32 # NULL, -- c33 NULL, -- c34 NULL, -- c35 NULL, -- c36 NULL, -- c37 NULL, -- c38 NULL, -- c39 NULL, -- c40 NULL, -- c41 NULL, -- c42 NULL, -- c43 NULL, -- c44 # NULL, -- c45 NULL, -- c46 NULL, -- c47 NULL, -- c48 NULL, -- c49 NULL, -- c50 NULL, -- c51 NULL, -- c52 NULL, -- c53 NULL, -- c54 NULL, -- c55 NULL, -- c56 # NULL, -- c57 NULL, -- c58 NULL, -- c59 NULL, -- c60 # NULL, -- c61 NULL, -- c62 NULL, -- c63 NULL, -- c64 # NULL, -- c65 NULL, -- c66 NULL, -- c67 NULL, -- c68 NULL, -- c69 NULL, -- c70 NULL, -- c71 NULL, -- c72 NULL, -- c73 NULL, -- c74 NULL, -- c75 NULL, -- c76 # NULL, -- c77 NULL, -- c78 # 3 -- crn -- row number ); # # Insert a row with arbitrary values. # INSERT INTO meb_extreme_db1.table1 VALUES ( b'1', -- c01 b'1111111111111111111111111111111111111111111111111111111111111111', -- c02 127, -- c03 0, -- c04 001, -- c05 true, -- c06 32767, -- c07 0, -- c08 00001, -- c09 8388607, -- c10 0, -- c11 00000001, -- c12 2147483647, -- c13 0, -- c14 0000000001, -- c15 9223372036854775807, -- c16 0, -- c17 00000000000000000001, -- c18 -1.175494351E-38, -- c19 1.175494351E-38, -- c20 000000000000001, -- c21 -2.2250738585072E-308, -- c22 2.2250738585072E-308, -- c23 00000000000000000000001, -- c24 -9999999999, -- c25 9999999999, -- c26 0000000001, -- c27 # '2008-08-04', -- c28 '2008-08-04 16:18:06', -- c29 '2008-08-04 16:18:24', -- c30 '16:18:47', -- c31 '2008', -- c32 # 'a', -- c33 '', -- c34 'e', -- c35 REPEAT('i',255), -- c36 _utf8 x'c3a4', -- c37 '', -- c38 _utf8 x'c3b6', -- c39 REPEAT(_utf8 x'c3bc',255), -- c40 _ucs2 x'00e4', -- c41 '', -- c42 _ucs2 x'00f6', -- c43 REPEAT(_ucs2 x'00fc',255), -- c44 # '', -- c45 'a', -- c46 REPEAT('e',255), -- c47 REPEAT('i',261), -- c48 '', -- c49 _utf8 x'c3a4', -- c50 REPEAT(_utf8 x'c3b6',255), -- c51 REPEAT(_utf8 x'c3bc',261), -- c52 '', -- c53 _ucs2 x'00e4', -- c54 REPEAT(_ucs2 x'00f6',255), -- c55 REPEAT(_ucs2 x'00fc',261), -- c56 # '0', -- c57 '', -- c58 '1', -- c59 REPEAT('1',255), -- c60 # '', -- c61 'b', -- c62 REPEAT('c',255), -- c63 REPEAT('\'',261), -- c64 # 'tinyblob', -- c65 'tinytext', -- c66 'tinytext-ucs2', -- c67 'blob', -- c68 'text', -- c69 'text-ucs2', -- c70 'mediumblob', -- c71 'mediumtext', -- c72 'mediumtext-ucs2', -- c73 'longblob', -- c74 'longtext', -- c75 'longtext-ucs2', -- c76 # 'b', -- c77 'b,c', -- c78 # 4 -- crn -- row number ); # # Create more tables. # CREATE TABLE meb_extreme_db1.TABLE2 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE meb_extreme_db1.TaBle3 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MEB_EXTREME_DB2.table1 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MEB_EXTREME_DB2.TABLE2 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MEB_EXTREME_DB2.TaBle3 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MebExtreme_Db3.table1 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MebExtreme_Db3.TABLE2 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; CREATE TABLE MebExtreme_Db3.TaBle3 ENGINE=InnoDB AS SELECT * FROM meb_extreme_db1.table1; # TEST 1 : Perform backup using innobackup script # Perform backup without compression and note down the datafile size # Running innobackup ..... # .... # Innobackup is Success! # # Clean up. # DROP DATABASE meb_extreme_db1; DROP DATABASE MEB_EXTREME_DB2; DROP DATABASE MebExtreme_Db3; SHOW DATABASES; Database information_schema MEB_EXTREME_DB2 MebExtreme_Db3 meb_extreme_db1 mtr mysql test DROP TABLE mysql.ibbackup_binlog_marker; # Clean up for first test # TEST 2: Execute backup using ibbackup SHOW DATABASES; Database information_schema MEB_EXTREME_DB2 MebExtreme_Db3 meb_extreme_db1 mtr mysql test # Perform backup using ibbackup # starting ibbackup .... # .... # Success! # Now copy the .frm files from datadir to backupdir # Copy back .frm files from backupdir to datadir SHOW DATABASES; Database information_schema MEB_EXTREME_DB2 MebExtreme_Db3 meb_extreme_db1 mtr mysql test # # Clean up. # DROP DATABASE meb_extreme_db1; DROP DATABASE MEB_EXTREME_DB2; DROP DATABASE MebExtreme_Db3;