Bash script to insert values in MySQL

Solution 1:

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.

Solution 2:

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.

Solution 3:

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;

Solution 4:

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