Inserting data from a shell script under MySQL

I. Creating Databases and Tables

Set up a database

Create database dic character set UTF8;

Build table

create table  ' dic '   (   ' id '  int ( unsigned not null auto_) increment comment  ' id ',   ' principal '  varchar (213)  character set utf8  NOT NULL COMMENT  ' responsible ',   ' use '  varchar (222)  CHARACTER SET  utf8 not null comment  ' use ',   ' type '  varchar (222)  character set  utf8 NOT NULL COMMENT  ' machine type ',   ' model '  varchar (222)  character  SET utf8 NOT NULL COMMENT  ' machine model ',   ' place '  varchar (222)   character set utf8 not null comment  ' location ',   ' IP '  varchar (222)  CHARACTER SET utf8 NOT NULL COMMENT  ' IP ',   ' addtime '   datetime not null comment  ' time ',  primary key  (' id '))  ENGINE= Innodb auto_increment=23 default charset=utf8; 

Second, write the relevant script

Precautions

Mysql-u User name-p password-e "command;" Multiple commands are written in double quotes, separated by semicolons.
INSERT into ' table name ' (' Field ', ' field ') VALUES (value, value);

The following two scripts can be implemented

#!/bin/bashread-p "Please enter MySQL password:" passwdtable= "dic" query= "NULL, ' Zhang Wei Yi ', ' new DNS server 1 ', ' Physical machine ', ' DELL R410 ', ' 14 layer A5 ', ' 10.240.210.61 ', ' 2015-06-06 13:04:09 ' "Echo $query/usr/bin/mysql dic-u root-p$passwd <<eofinsert into $ TABLE VALUE ($query); #EOF

#!/bin/bashread-p "Please enter MySQL password:" passwdtable= "dic" query= "NULL, ' Zhang Wei Yi ', ' new DNS server 1 ', ' Physical machine ', ' DELL R410 ', ' Layer 14 A5 ', ' 10.240.210.61 ', ' 2015-06-06 13:04:09 ', ' echo $query/usr/bin/mysql dic-u root-p$passwd-e ' INSERT into $TABLE VALU E ($query); "

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/0A/wKioL1VykizyyFU1AAMBmkc9fpI850.jpg "title=" Qq20150606141720.jpg "alt=" Wkiol1vykizyyfu1aambmkc9fpi850.jpg "/>

In addition, you can also import the plain text into the database by formatting the output

Here we take test1 as an example

[[email protected] lxh]# cat test1 Ming server 1 physical machine DELL R410 14 layer A5 10.240.210.61

The

script is as follows

#!/bin/bash>test_newread -p  "Please enter you mysql password:"   Passwdmysql=/usr/bin/mysqltable= "dic" Time= ' date +%f\ %t ' p= "\e[01;35m" #sed   "s/\ (^.*$\)/\1, $TIME/ " test >> test_new# #在每行行尾添加时间cat  test1 | sed  ' s/^/null\t/g '  |sed   ' s/\t/,/g '  |sed  s/\ (^.*$\)/\1, $TIME/" >>test_newcat test_new |while  read line#echo  "$TIME"  >>mysql_err_logdoecho  $linequery = ' echo  ' $line "  |awk -F  ","    ' { printf ("%s,\"%s\ ","%s\ ", \"%s\ ", \"%s\ ", \"%s\ ", \"%s\ ", \"%s\ "",  $1, $2, $3, $4, $5, $6, $7 , $8)} "echo  $query  1> >mysql_err_log 2>&1echo  $query $mysql dic -u root -p$passwd   <<EOFINSERT INTO  $TABLE  values ($query); eofdone#echo -e  "  $P *********************** $TIME *******************" >>mysql_err_log 

Execute script

[Email protected] lxh]# sh mysqltest.shnull, xiaoming, new server 1, physical machine, DELL r410,14 layer a5,10.240.210.61,2015-06-06 14:26:07null, "Xiaoming" , "New server 1", "Physical Machine", "DELL R410", "14 layer A5", "10.240.210.61", "2015-06-06 14:26:07"

Insert Successful

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/0B/wKioL1VynFizALA3AAPIAsLuMl0934.jpg "title=" Qwe.jpg "alt=" Wkiol1vynfizala3aapiasluml0934.jpg "/>

Inserting data from a shell script under MySQL