Generate test Data - SaikumarAdepollu/mylabs GitHub Wiki
Welcome to the mylabs wiki!
########################################################### [root@localhost mysql-files]# mysqldump -uroot -p --databases MasterDB --routines --no-data Enter password: -- MySQL dump 10.13 Distrib 5.7.10-3, for Linux (x86_64)
-- Host: localhost Database: MasterDB
-- Server version 5.7.10-3
-- Dumping routines for database 'MasterDB'
/!50003 DROP FUNCTION IF EXISTS datetimeval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION datetimeval(value int) RETURNS datetime(3)
DETERMINISTIC
BEGIN DECLARE select_var datetime(3);SET select_var = (FROM_UNIXTIME(UNIX_TIMESTAMP('2000-04-30 14:53:27') + FLOOR(0 + (RAND() * 53072000)) )); RETURN select_var ; END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 DROP FUNCTION IF EXISTS intval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION intval(value int) RETURNS int(11)
DETERMINISTIC
BEGIN DECLARE select_var int(11);SET select_var = (SELECT ROUND(RAND()(500000-10)+10)); RETURN select_var ; END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 DROP FUNCTION IF EXISTS smallintval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION smallintval(value int) RETURNS smallint(6)
DETERMINISTIC
BEGIN DECLARE select_var smallint(6);SET select_var = (SELECT ROUND(RAND()(1000-100)+100)); RETURN select_var ; END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 DROP FUNCTION IF EXISTS timestampval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION timestampval(value int) RETURNS datetime(3)
DETERMINISTIC
BEGIN DECLARE select_var datetime(3);SET select_var = (FROM_UNIXTIME(UNIX_TIMESTAMP('2000-04-30 14:53:27') + FLOOR(0 + (RAND() * 53072000)) )); RETURN select_var ; END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 DROP FUNCTION IF EXISTS tinyintval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION tinyintval(value int) RETURNS tinyint(4)
DETERMINISTIC
BEGIN DECLARE select_var int(11); IF value = 3 THEN
SET select_var = (SELECT ROUND(RAND()(120-100)+100));
ELSEIF value = 1 THEN
SET select_var = (SELECT ROUND(RAND()(1-0)+0));
END IF; RETURN select_var ; END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 DROP FUNCTION IF EXISTS varcharval /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost FUNCTION varcharval(value int) RETURNS varchar(11) CHARSET latin1
DETERMINISTIC
BEGIN DECLARE select_var varchar(11);SET select_var = (select SUBSTRING(conv(floor(rand() * 99999999999999), 20, 36),1,10)); RETURN select_var ; END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE /; /!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /; /!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /; /!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /; /!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-05-21 6:47:47
###########################################################
Script 👍
mysql -ss -e" select COLUMN_TYPE from information_schema.columns where table_name='AC_AccountContacts' and table_schema='MasterDB';"|awk '{print $1}'|tr '\n' '||'|sed "s/|/,'|',/g"|sed "s/,'|',$/)/g"|sed 's/(/val(/g'|awk '{print "SELECT CONCAT("$1}'>GenSQL_$2.sql
while : do mysql -ss -DMasterDB<GenSQL_$2.sql done
###########################################################
sort -u -t| -k1,1 MasterDB.AC_AccountContacts >MasterDB.AC_AccountContacts_sorted
########################################################### Loading into tables 👍
mysql -DMasterDB --force -e"LOAD DATA INFILE '/var/lib/mysql-files/MasterDB.AC_AccountContacts_sorted' into table AC_AccountContacts fields terminated by '|'"
###########################################################