Bash script to insert values in MySQL

Question

I want to make a bash script that connects to my MySQL server and inserts some valuse from a txt file. I have written this down:

#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('cat test.txt');" | mysql -uroot -ptest test;

but I'm recieving the following error:

ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1

I suppose the error is in my txt file, but I've tried many variations and still no hope of success.

My txt file looks like this:

10.16.54.29 00:f8:e5:33:22:3f marsara

Answer 1

Assuming you have many rows to add, you probably need LOAD DATA INFILE statement, not INSERT. The source file has to be on the server, but it seems to be the case here.

Something like that:

#!/bin/bash

mysql -uroot -ptest test << EOF

LOAD DATA INFILE 'test.txt'
    INTO TABLE tbl_name
    FIELDS TERMINATED BY ' ';

EOF

LOAD DATA INFILE has many options as you will discover by reading the doc.

Answer 2

I use this and it works:

mysql -uroot -proot < infile

or select the database first

./mysql -uroot -proot db_name < infile

or copy the whole SQL into the clipboard and paste it with

pbpaste > temp_infile && mysql -uroot -proot < temp_infile && rm temp_infile

Answer 3

Try this one:

#!/bin/bash
inputfile="test.txt"
cat $inputfile | while read ip mac server; do
    echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');"
done | mysql -uroot -ptest test;

This way you streaming the file read as well the mysql comand execution.

Answer 4

You are trying to insert the value "cat test.txt" as a String in the database in an INSERT statement that requires 3 parameters (IP,MAC and SERVER) so this is why you get this error message.

You need to read the text file first and extract the IP, MAC and Server values and then use these in the query that would look like this once filled :

#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('10.16.54.29', '00:f8:e5:33:22:3f', 'marsara');" | mysql -uroot -ptest test;