Due to collaborative project development, my colleague sent an SQL file of the project and opened it as a directory, there are hundreds of databases, each database is used as a separate file, and each SQL file contains CREATEDATABASEIFNOTEXISTS 'xxxx 'use 'xxxx'. because of collaborative project development, my colleague sent an SQL file of the project and opened a directory containing hundreds of databases. each database acts as a separate file.
Each SQL file contains
Create database if not exists 'xxxx'
USE 'xxxx ';
He can directly use mysql_workbench 6 or a later version to directly import folders to import multiple SQL files. However, on my server, it is impossible to install a GUI workbench for batch import of this SQL statement.
So write a shell and change the host, username, and password in it to your corresponding one.
Dbname. the default value is "sample", which is the sample table in mysql. the actual data is not imported here, because the SQL file I want to import already has a db. if you want to import multiple tables to a database, you can modify the dbname for your database name.
#! /Bin/bash # author rainysia
# Date 2014-11-14 10: 26: 27 set-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/" # host = "192.168.85.123" # mysql host username = "dbroot" # mysql username =" db1t #2 w $ 3r @ 4 # t "# mysql password dbname =" sample "# mysql database name now =$ (date" + % s ") # timing mysql_source () {for file_name in 'ls-A $1 'Do seg_start_time = $ (date "+ % s") if [-f "$1 $ file_name"]; then command = "source $1 $ file_name" 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" fi done echo "All SQL is done! Total cost: "'expr $ (date" + % s ")-$ {now} '" seconds "} echo" Universal Time is now: $ UTdate. "echo" Local time is now: $ TZdate. "mysql_source $ file_path
Test,
root@debian:/home/sh# ./mysql_source.sh Universal Time is now: Fri Nov 14 03:10:49 UTC 2014.Local time is now: Fri Nov 14 11:10:49 CST 2014.source: "/home/db/test/hml2.sql" is ok, It takes 18 secondssource: "/home/db/test/hml3.sql" is ok, It takes 19 secondssource: "/home/db/test/hml4.sql" is ok, It takes 18 secondsAll sql is done! Total costs: 55 seconds
Next, we found that the data can be imported, and the colleague needs to reinstall an app and delete all the tables in a database. because she has insufficient permissions and cannot directly delete the database, she can only manually drop the table one by one. however, there are over 2000 tables.
Originally intended to use
Select concat ('drop table if exists', table_name, ';') FROM information_schema.tables WHERE table_schema = 'CS _ china_111 database name ';
To delete all tables.
So I still use the shell above to change the path as needed. now I will write the SQL statement of the table to be dropped first.
Export the database first. in the terminal, use mysqldump dbroot as the username of my database, cs_china_1111 as the database to be exported, and-d-add-drop-table as the only structure to export data without exporting data.
#mysqldump -udbroot -p cs_china_1111 -d --add-drop-table > cs_china_1111.sql
Then use grep to filter it once, because the exported SQL contains the 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 dbname to the name of the database to be deleted and run it.