Shell Batch Import MySQL database

Because the project co-development, colleagues sent a project SQL file, open a look is a directory, there are hundreds of databases. Each database acts as a separate file.

Every SQL file has

CREATE DATABASE IF not EXISTS ' XXXX '
Use ' XXXX ';

He can import multiple SQL files directly by importing folders directly from the Mysql_workbench version of 6. But on my server it is not possible to install a GUI workbench for the bulk of this SQL.

So write a shell bar, change the inside of Host,username and password for you corresponding can.

dbname here, the default is sample, MySQL inside the sample table. The actual data is not imported into this, because I have created DB in the SQL file I want to import. If you are importing multiple tables into a database, you can modify the database name for your dbname

#!/bin/bash#author     Rainysia <[email protected]> #date       2014-11-14 10:26:27set-elc_all=clang=cunset tztzbase=$ (lc_all=c tz=utc0 date-r) utdate=$ (lc_all=c TZ=UTC0 date-d "$TZBase") tzdate=$ (unset TZ; Lang=c date-d "$TZBase") file_path= "/home/db/test/"   #要导入的sql文件夹host = "192.168.85.123"     &NBSP ;             #要导入的mysql主机username = "Dbroot"               & nbsp       #mysql的用户名password = "db1t#[email protected" #t "              #mysql的密码dbname = "Sample"                         #mysql的数据库名no w=$ (date "+%s")                       #计时mysql_source () {  &nbs P For file_name in ' ls-a '     do        seg_start_time=$ (date "+%s")       &N Bsp If [-f]$1$file_name "];then            command=" source $1$file_name "        &NB Sp   Mysql-h${host}-u${username}-p${password} ${dbname}-E "$command"             echo " Source: "\" $1$file_name\ "is OK, It takes" ' Expr $ (date "+%s")-${seg_start_time} ' "Seconds"       &NBSP ; fi    done    echo "All SQL is done! Total cost: "' Expr $ (date" +%s ")-${now} '" Seconds "}echo" Universal time was now:   $UTdate. " echo "Local time is now:       $TZdate." Mysql_source $file _path

Test it, please.

[Email protected]:/home/sh#./mysql_source.sh Universal Time is now:  Fri Nov 03:10:49 UTC 2014.Local time is now:
   fri Nov 11:10:49 CST 2014.source: "/home/db/test/hml2.sql" is OK, It takes  secondssource: "/home/db/test /hml3.sql "is OK, it takes  secondssource:"/home/db/test/hml4.sql "is OK, it takes  Secondsall SQL I S done! Total costs:  seconds

Then you find that you can import, and then the colleague needs to reload an app, removing all the tables from one of the databases. Because she has insufficient authority, can not delete the database directly, only one drop table manually. But there are more than 2000 table.

Originally intended to use

SELECT CONCAT (' DROP TABLE IF EXISTS ', table_name, '; ') From Information_schema.tables WHERE table_schema= ' cs_china_111 database name ';

The way to delete all the table, the discovery did not take effect.

So I still use the shell above. Just change the path, and now go ahead and write out the SQL for the table that needs to drop.

First to export this database, under the terminal use Mysqldump dbroot is my database user name, cs_china_1111 is I want to export the database,-d-add-drop-table is not exported data only export structure

#mysqldump-udbroot-p cs_china_1111-d--add-drop-table  > Cs_china_1111.sql

Then use grep to filter once, because the exported SQL contains a drop table

#find./-name "Cs_china_1111.sql" | Xargs grep "DROP TABLE IF" > Cs_china_1111_drop_table.sql

Next, use the previous shell to change the dbname to a database name that needs to be deleted and run it.

Shell Batch Import MySQL database