MySQL: Generate Random Data using Stored Procedure

If you have setup your MySQL cluster or standalone database server correctly, you might need to do a stress test to the server. You can use many ways to achieve this and in my case, I will use the simplest way which is create generate a fake data and insert them into table using stored procedure. Actually, you can use any other script like BASH, Perl or PHP as long as it support looping.

We need to loop certain SQL command in order to generate more and more data. Before we start, make sure you have prepare the database and table to be insert into. Following informations are variable that I used:

MySQL: Version 5.5.20 Community Server
Database: dbTest
Table: tbl_data1

Firstly, we need create the database and table. Log into MySQL console or client via appropriate database user (in this case I will just use root), and execute following MySQL command:

CREATE DATABASE dbTest;
USE dbTest;
CREATE TABLE `tbl_data1` (
`a` INT(10) NULL,
`b` INT(10) NULL,
`c` INT(10) NULL
)
ENGINE=InnoDB;

Our aim is to insert 100,000 rows random number between 0 to 1000 into the table.

DELIMITER //
CREATE PROCEDURE GenerateFakeData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO tbl_data1 (a,b,c) VALUES ((SELECT floor(rand() * 1000) AS randNum), (SELECT floor(rand() * 1000) AS randNum),(SELECT floor(rand() * 1000) AS randNum));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;

To call the procedure above, execute following command:

USE dbTest;
CALL GenerateFakeData();

This will execute the stored procedure and the table will contains 100,000 rows of data.

SELECT * FROM dbTest.tbl_data1 LIMIT 1,10;

Example results:

+------+------+------+
| a    | b    | c    |
+------+------+------+
|  521 |  662 |  748 |
|  753 |  523 |  356 |
|  212 |  994 |  334 |
|  689 |  441 |  139 |
|  374 |  453 |  145 |
|  364 |  386 |  837 |
|   30 |  637 |   96 |
|  571 |  569 |  129 |
|  940 |  312 |  741 |
|  770 |  628 |  828 |
+------+------+------+
10 rows in set (0.00 sec)

You can start to play around with this table like do some heavy queries, play with indexes and much more.

Related Posts

• Install MySQL Cluster in Debian
• Customize and Disable PHPmyAdmin ‘Export’ Menu
• High Availability: cPanel with MySQL Cluster, Keepalived and HAProxy
• Monitor MySQL Galera Cluster from Split-Brain
• CentOS 6: Install MySQL Cluster – The Simple Way
• High Availability: Configure Piranha for HTTP, HTTPS and MySQL
• CentOS: Integrate ClusterControl into Existing MySQL Galera Cluster
• Easiest Way to Install A Complete MySQL Galera Cluster
• MySQL – Recover Data Using mysqlbinlog
• CentOS: Install Percona XtraDB Cluster