How to insert datas into MySQL with Bash script
Hello, i am Erdenay Ateş.
In this post we will be taking a look to how Bash scripts and MySQL database can work together. And we will have pretty nice Bash scripting example as a real time task.
What is MySql? What is Bash? How they can work together?
MySQL is a open source-free to use database, which is keeps our datas in a place. And Bash is snipe of Linux, that covers the kernel of Linux and it is always keeping in communication with Linux kernel. If you want to have more informations about Bash, here is my Bash Scripting 101 course in Turkish :) After sign up to acikkaynakfikirler.com you can reach to course for free. Eğitimler — Açık Kaynak Fikirler (acikkaynakfikirler.com)
MySQL takes the datas with query in SQL(Structured Query Language) and SQL works like;
“Add that data to this table” = Insert Into TABLENAME values (‘ $value1’, ‘$value2’, ‘$value3’...)
“Show the all datas of that table” = Select *(all) From TABLENAME
“Show me datas when it’s value is higher than 10”. = Select * From TABLENAME Where VALUE = 10
And today we will just press Enter button then we will add 1000 lines random datas into MySQL with a Bash script. Lets create our hans-on lab!
Firstly, i am taking some random datas from Mockaroo like id, first name, last name, mail, gender, ip address. And we have got datas about 1000 random people.
And then lets have a MySQL environment with Docker, we will have a MySQL container with that steps. Our MySQL’s root account’s password will be admin.
And here it is! We have got a small workplace for MySQL!
We have got only defaults things now, lets create a database for us and get started to using that. Our database name will be random_datas.
Now will create the table for id, first name, last name, mail, gender, ip address values. I used only varchar data type, maybe this is not good for real tasks but i think enough as an example :)
create table random_datas (id varchar(1000), name varchar(50), lastname varchar(50), mail varchar(80), gender varchar(10));
Lets write the script for database now. In the first step we will slice the datas because they are not seperated and we will split the datas with awk command using with -F ‘,’ and we will prepare the datas how we want :) But we should remove that first part from .csv file. That id,first_name,last_name,email,gender part.
cat MOCK_DATA.csv | tail -1000 > datas
Looks okay for us now and we should have the correct script for us. As we said before our dataset will be id, name, lastname, email and gender. But our datas file has no id numbers for the users. Adding the id key we will use nl command. So lets dive into our script.
queryNo variable will be our cursor in that session, we will select the data with that variable. After this we will play with the data with Linux commands.
If we type nl datas we will see your file been seperated to two slices. First slice is for id numbers, the other is for name, last name, mail and gender. And with awk command we will take what variable we want to add to data table.
If we start to combine the commands, we should try first grep command with -w parameter. That will give us the line, if we type -w 1 that gives us the first line, if we type -w 2 we will have the second.
After that step with awk command we will say to Linux kernel “give me the second part of nl datas when it’s grep -w value is 1”
So now from that output we will pull our variables(name, last name…). If we try to use awk for pulling the variables, we should tell to Linux kernel what is the position of the variable in output. While we doing this we should use -F parameter with ‘ , ’.
Here it is! We just pulled the name, last name, mail and gender of first person’s! We made the 80% of the script, lets put these commands into a while loop untill to last data, our last id number is 1000. After all these steps we will add datas to MySQL database with instert query.
mysql -u root -padmin random_datas -e “insert into random_datas values (‘$queryNo’, ‘$name’, ‘$lastname’, ‘$email’, ‘$gender’);”
After that step every single variable will be sent into random_datas table. For checking the all lines we are adding echo command.
echo “$counter, $name $lastname, $email, $gender was correctly imported”
Our script is 100% done now, if we run that scipt in our MySQL container, everything will be done!
If we move that script from our computer to MySQL container, when we run the script, our task will be done. You can copy the script with docker cp command. It is docker cp datas database:/ for me.
docker cp text_file container_id:/file_path
If we have got database with random_datas with random_datas table when we run the script inside of the container, output will be;
That “Using a password…” part because of mysql -u root -padmin line in the script. If you had output like that, means everything is well!
Lets see the result now with SQL query with SELECT * FROM random_datas;
Thanks for reading :) You can reach everything from here;
erdenayates/Insert-Datas-into-DockerMySQL-with-Script (github.com)
