Shell batch import mysql database, shellmysql Database

Source: Internet
Author: User
Tags mysql host

Shell batch import mysql database, shellmysql Database

Due to project collaborative development, my colleague sent an SQL file of the project and opened it in a directory containing hundreds of databases. Each database is used 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 <rainysia@gmail.com> # 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.




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.