MySQL How to prepare 100 million records of the table to test

Once a friend asked me how to quickly online to a large table to add a field or modify the length of a field, the MySQL version is 5.6, so prepare a test environment to prepare a 100 million records of the table, and then to the actual test in which way faster, first to start preparing 100 million records of the table.

I have hundreds of millions of records on the line of the table, but a lot of online friends are not, then I practice here a way to achieve their own construction of 100 million data records of the table. The realization of the idea is to build a universal 20 fields around the user table, and then write a stored procedure, continue to write data to the table, while the loop write 100 million times, so that the formation of a 100 million records of the table out.

1, build a common user table

Use test; CREATE TABLE ' Uc_user ' ( ' ID ' BIGINT (20), ' User_name ' VARCHAR (400), ' User_pwd ' VARCHAR (800), ' BIRTHDAY ' DATETIME, ' NAME ' VARCHAR (800), ' User_icon ' VARCHAR (2000), ' SEX ' CHAR (4), ' Nickname ' VARCHAR (800), ' STAT ' VARCHAR (40), ' User_mall ' BIGINT (20), ' Last_login_date ' DATETIME, ' Last_login_ip ' VARCHAR (400), ' src_open_user_id ' BIGINT (20), ' EMAIL ' VARCHAR (800), ' MOBILE ' VARCHAR (200), ' Is_del ' CHAR (4), ' Is_email_confirmed ' VARCHAR (4), ' Is_phone_confirmed ' VARCHAR (4), ' Creater ' BIGINT (20), ' Create_date ' DATETIME, ' Update_date ' DATETIME, ' Pwd_intensity ' VARCHAR (4), ' MOBILE_TGC ' VARCHAR (256), ' MAC ' VARCHAR (256), ' SOURCE ' VARCHAR (4), ' ACTIVATE ' VARCHAR (4), ' Activate_type ' VARCHAR (4), ' Is_life ' VARCHAR (4) ) Engine=innodb;

2, preparing the stored procedure for data entry

Stored Procedure content:

DELIMITER $$ Use ' Test ' $$ DROP PROCEDURE IF EXISTS test. ' Pro_test_data ' $$ CREATE PROCEDURE test. ' Pro_test_data ' (pos_begin int,pos_end INT) BEGIN DECLARE i INT; SET I=pos_begin; While I>=pos_begin && i<= pos_end do INSERT into Test. ' Uc_user ' (' ID ', ' user_name ', ' user_pwd ', ' BIRTHDAY ', ' NAME ', ' User_icon ', ' SEX ', ' nickname ', ' STAT ', ' US Er_mall ', ' last_login_date ', ' last_login_ip ', ' src_open_user_id ', ' EMAIL ', ' MOBILE ', ' Is_del ', ' is_email_confirmed ', ' Is_phone_confirmed ', ' creater ', ' create_date ', ' update_date ', ' pwd_intensity ', ' mobile_tgc ', ' MAC ', ' SOURCE ', ' ACTIVATE ', ' activate_type ', ' Is_life ') VALUES (i, ' admin ', ' 1ba613b3676a4a06d6204b407856f374 ', now (), ' super tube ', ' group1/m00 /03/bc/wki0d1qkfawahhewaaaoj58qocg271.jpg ', ' 1 ', ' admin2014 ', ' A ', ' 1 ', now (), ' 192.168.121.103 ', NULL, ', ' 10099990001 ', ' 0 ', ' 1 ', ' 0 ', null,null,null, ' 1 ', ' e5f10caa4ebb44c4b23726cbbd3ac413 ', ' 1-3 ', ' 0 ', ' 2 ', ' 2 ', ' 1 '); SET i=i + 1; END while; end$$ DELIMITER;

Blog Source address: http://blog.csdn.net/mchdba/article/details/52938114, Bo master mchdba (Douglas Fir), declined reprint

The following procedure is performed in the SQL command Window interface:

Mysql> DELIMITER $$ mysql> use ' Test ' $$ Database changed mysql> DROP PROCEDURE IF EXISTS test. ' Pro_test_data ' $$ Query OK, 0 rows Affected (0.00 sec) mysql> CREATE PROCEDURE test. ' Pro_test_data ' (pos_begin int,pos_end INT) BEGIN -DECLARE i INT; SET I=pos_begin; While I>=pos_begin && i<= pos_end do - Display all 1421 possibilities? (Y or N) INSERT into Test. ' Uc_user ' (' ID ', ' user_name ', ' user_pwd ', ' BIRTHDAY ', ' NAME ', ' User_icon ', ' SEX ', ' nickname ', ' STAT ', ' user_mall ', ' last_login_date ', ' last_login_ip ', ' src_open_user_id ', ' EMAIL ', ' MOBILE ', ' Is_del ', ' Is_email_ ' Confirmed ', ' is_phone_confirmed ', ' creater ', ' create_date ', ' update_date ', ' pwd_intensity ', ' mobile_tgc ', ' MAC ', ' SOURCE ', ' ACTIVATE ', ' activate_type ', ' Is_life ') VALUES (i, ' admin ', ' 1ba613b3676a4a06d6204b407856f374 ', now (), ' super tube ', ' Group1/m00/03/bc/wki0d1qkfawahhewaaaoj58qocg271.jpg ', ' 1 ', ' admin2014 ', ' A ', ' 1 ', now (), ' 192.168.121.103 ', NULL, ', ' 10099990001 ', ' 0 ', ' 1 ', ' 0 ', null,null,null, ' 1 ', ' e5f10caa4ebb44c4b23726cbbd3ac413 ', ' 1-3 ', ' 0 ', ' 2 ', ' 2 ', ' 1 '); - - Display all 1421 possibilities? (Y or N) SET i=i + 1; and END while; end$$ Query OK, 0 rows Affected (0.00 sec) Mysql> DELIMITER; Mysql>

3, start data entry

Start to enable stored procedure input 100 million data into the table, command call Test.pro_test_data (0,100000000), here time will be longer, after all, is 100 million records.

Mysql> call Test.pro_test_data (0,100000000); Query OK, 1 row affected (2 hours min 56.32 sec) # See time is 2 hours min 56.32 sec, C5>2 for half an hour. Mysql> Select COUNT (1) from Test. ' Uc_user '; +-----------+ | COUNT (1) | +-----------+ | 100000001 | +-----------+ 1 row in Set (3 min 0.14 sec) Mysql>

4, ClearBinlogLog

Because of the entry of 100 million records, so there will be a large number of binlog logs, we need to clear these binlog records, so as not to occupy disk space:

Mysql> Show master status; +------------------+----------+--------------+---------------------------------------+-------------------+ | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set | +------------------+----------+--------------+---------------------------------------+-------------------+ |  mysql-bin.007143 |              4589266 | |                   Information_schema,performance_schema | | +------------------+----------+--------------+---------------------------------------+-------------------+ 1 row in Set (0.01 sec) Mysql> purge master logs to "mysql-bin.0007143"; ERROR 1373 (HY000): Target log not found in Binlog index Mysql> purge master logs to "mysql-bin.007143"; Query OK, 0 rows affected (5.44 sec) Mysql>

MySQL How to prepare 100 million records of the table to test