mysql 数据库创建,删除,导入,导出数据 shell 脚本

2019-11-07

新建立一个项目,经常会涉及到数据库的频繁改动,数据处理等,可以整合起来放到脚本,方便操作。


下面将数据库创建,删除,导入,导出数据等重复性比较高的操作整合到 shell 脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#!/bin/bash
# wfh/2018/09/18 - operate for db: create, drop, import, dump.

DB_HOST="127.0.0.1"
DB_PORT=3306
DB_USER=root
DB_PWD=123!@#
DB_NAME=db_test
DB_IMPORT_FILE="${DB_NAME}.sql"
DB_DUMP_FILE="${DB_NAME}_dump.sql"
SQL=""

clear

case "$1" in
    "help")
        echo "drop"
        echo "create"
        echo "import"
        echo "dump"
        ;;
    "drop")
        echo "${DB_USER} drop database: ${DB_NAME}"
        SQL="DROP DATABASE IF EXISTS ${DB_NAME}"
        mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD -Ns -e "$SQL"
        ;;
    "create")
        echo "${DB_USER} create database: ${DB_NAME}}"
        SQL="CREATE DATABASE IF NOT EXISTS $DB_NAME DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci"
        mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD -Ns -e "$SQL"
        ;;
    "import")
        echo "${DB_USER} import data from: ${DB_IMPORT_FILE}"
        mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD $DB_NAME < "${DB_IMPORT_FILE}"
        ;;
    "dump")
        echo "${DB_USER} dump data from ${DB_NAME} to ${DB_DUMP_FILE}"
        mysqldump -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD $DB_NAME > "${DB_DUMP_FILE}"
        ;;
    *)
        echo "invalid cmd! find for help!"
        exit 1
        ;;
esac

exit 0