Using Bash Shell Scripts for Automated Database Testing

Automated testing has become essential in the testing industry, and this article demonstrates how everyday tools like Bash shell scripts can be employed to automate database testing tasks, especially for MySQL databases.

Case Background : In the Qiyun project, a large number of forms need to be generated and database dynamics monitored, prompting the use of shell scripts to automate these operations.

Shell Introduction : Bash (Bourne Again Shell) is highlighted as a free, easy‑to‑use scripting language on Linux, allowing the creation of executable scripts that can automate repetitive tasks.

Creating a Script : A script must start with the shebang line #!/bin/sh to specify the interpreter. After writing the script, it should be made executable with chmod +x filename and run via /filename .

Script Instance : The script performs two main testing aspects – verifying database CRUD functionality and dynamically monitoring database status (capacity, parameter changes). It uses loops and variables to generate changing data, and includes MySQL commands such as insert into temp1 values($i,'Tom','dkdkkdkdkdksldfk',123456789); , select * from temp1 where id=$i+3; , replace into temp1(id,name,info,mobile) values ($i,'Yujun','124324dsdfs',48484848); , and temporary table creation commands.

Running the Script : After granting execution permission, the script is executed, and monitoring charts reflect the results, confirming successful automation.

Conclusion : Effective automated testing does not require heavyweight frameworks; leveraging built‑in tools like Bash can achieve significant efficiency gains.

Full Script :

#!/bin/sh

echo "Run the test script of mysql!"

for i in {1..1000}; do
  mysql -h192.168.0.153 -P3306 -usuyujun -pSuyujun123456 -e "
    use yujun;
    insert into temp1 values($i,'Tom','dkdkkdkdkdksldfk',123456789);
    insert into temp1 values($i+1,'Tomssl','dkdkkdkdkdksldfk',123456789);
    insert into temp1 values($i+2,'Tomieie','dkdkkdkdkdksldfk',123456789);
    insert into temp1 values($i+3,'Tom333','dkdkkdkdkdksldfk',123456789);
    select * from temp1 where id=$i+3;
    replace into temp1(id,name,info,mobile) values ($i,'Yujun','124324dsdfs',48484848);
    replace into temp1(id,name,info,mobile) values ($i+1,'Yujun','124324dsdfs',48484848);
    replace into temp1(id,name,info,mobile) values ($i+2,'Yujun','124324dsdfs',48484848);
    replace into temp1(id,name,info,mobile) values ($i+3,'Yujun','124324dsdfs',48484848);
    select * from temp1 where name='Yujun';
    update temp1 set name='Suyujun' where id=$i;
    update temp1 set name='Suyujun' where id=$i+1;
    update temp1 set name='Suyujun' where id=$i+2;
    update temp1 set name='Suyujun' where id=$i+3;
    select * from temp1 where id=1000;
    select sleep(3);
    CREATE TEMPORARY TABLE tmp_table1 (id int,name varchar(10)) select * from temp;
    CREATE TEMPORARY TABLE tmp_table2 (id int,name varchar(10)) select * from temp;
    CREATE TEMPORARY TABLE tmp_table3 (id int,name varchar(10)) select * from temp1;
    CREATE TEMPORARY TABLE tmp_table4 (id int,name varchar(10)) select * from temp1;
    quit;"
  done

done