本文目录导读：
我来介绍几种批量创建MySQL数据库的脚本方法：
Shell脚本方法
基本Shell脚本
#!/bin/bash
# MySQL连接参数
MYSQL_USER="root"
MYSQL_PASS="your_password"
MYSQL_HOST="localhost"
# 要创建的数据库列表
databases=(
"db_test1"
"db_test2"
"db_test3"
"db_development"
"db_production"
)
# 创建数据库
for db in "${databases[@]}"
do
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "CREATE DATABASE IF NOT EXISTS $db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
if [ $? -eq 0 ]; then
echo "数据库 $db 创建成功"
else
echo "数据库 $db 创建失败"
fi
done
带参数的灵活脚本
#!/bin/bash
# 从文件读取数据库名
DB_LIST_FILE="databases.txt"
MYSQL_USER="root"
MYSQL_PASS="your_password"
# 创建数据库函数
create_database() {
local db_name=$1
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "CREATE DATABASE IF NOT EXISTS \`$db_name\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
return $?
}
# 从文件读取数据库列表
if [ -f "$DB_LIST_FILE" ]; then
while IFS= read -r db_name; do
if [ ! -z "$db_name" ]; then
create_database "$db_name" && echo "创建成功: $db_name" || echo "创建失败: $db_name"
fi
done < "$DB_LIST_FILE"
else
echo "数据库列表文件不存在: $DB_LIST_FILE"
exit 1
fi
databases.txt 文件内容示例：
db_project1
db_project2
db_project3
db_test
db_stagingPython脚本方法
使用mysql-connector-python
#!/usr/bin/env python3
import mysql.connector
from mysql.connector import Error
# MySQL连接配置
config = {
'user': 'root',
'password': 'your_password',
'host': 'localhost'
}
# 要创建的数据库列表
databases = [
'db_app1',
'db_app2',
'db_app3',
'db_analytics',
'db_backup'
]
def create_databases():
try:
# 连接MySQL
connection = mysql.connector.connect(**config)
cursor = connection.cursor()
for db_name in databases:
try:
# 创建数据库
create_query = f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
cursor.execute(create_query)
print(f"数据库 {db_name} 创建成功")
except Error as e:
print(f"数据库 {db_name} 创建失败: {e}")
except Error as e:
print(f"MySQL连接失败: {e}")
finally:
if 'connection' in locals() and connection.is_connected():
cursor.close()
connection.close()
if __name__ == "__main__":
create_databases()
使用SQLAlchemy
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
# 数据库连接字符串
db_url = "mysql+mysqlconnector://root:your_password@localhost"
# 要创建的数据库
databases = ['db_test1', 'db_test2', 'db_test3']
def create_databases():
try:
# 创建引擎
engine = create_engine(db_url)
with engine.connect() as connection:
for db_name in databases:
try:
# 创建数据库
query = text(f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
connection.execute(query)
print(f"数据库 {db_name} 创建成功")
except SQLAlchemyError as e:
print(f"数据库 {db_name} 创建失败: {e}")
except SQLAlchemyError as e:
print(f"连接MySQL失败: {e}")
if __name__ == "__main__":
create_databases()
生成批量SQL脚本
#!/bin/bash
# 生成批量SQL文件
OUTPUT_FILE="create_databases.sql"
# 数据库列表
databases=(
"db_202401"
"db_202402"
"db_202403"
"db_202404"
)
# 写入SQL文件
echo "-- 批量创建数据库脚本" > $OUTPUT_FILE
echo "-- 生成时间: $(date)" >> $OUTPUT_FILE
echo "" >> $OUTPUT_FILE
for db in "${databases[@]}"
do
echo "CREATE DATABASE IF NOT EXISTS \`$db\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" >> $OUTPUT_FILE
done
echo "SQL脚本已生成: $OUTPUT_FILE"
echo "执行: mysql -u root -p < $OUTPUT_FILE"
批量创建带用户的数据库
#!/bin/bash
# 创建数据库和用户
create_db_with_user() {
local db_name=$1
local db_user=$2
local db_pass=$3
mysql -u root -p${MYSQL_PASS} <<EOF
CREATE DATABASE IF NOT EXISTS \`$db_name\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS '$db_user'@'localhost' IDENTIFIED BY '$db_pass';
GRANT ALL PRIVILEGES ON \`$db_name\`.* TO '$db_user'@'localhost';
FLUSH PRIVILEGES;
EOF
}
# 定义数据库、用户、密码对应关系
declare -A db_config=(
["app_db1"]="user1:pass1"
["app_db2"]="user2:pass2"
["app_db3"]="user3:pass3"
)
# 批量创建
for db in "${!db_config[@]}"; do
IFS=':' read -r user pass <<< "${db_config[$db]}"
create_db_with_user "$db" "$user" "$pass"
echo "数据库 $db 和用户 $user 创建完成"
done
使用配置文件批量导入
config.json
{
"mysql": {
"host": "localhost",
"user": "root",
"password": "your_password"
},
"databases": [
{"name": "db_test1", "charset": "utf8mb4", "collation": "utf8mb4_unicode_ci"},
{"name": "db_test2", "charset": "utf8mb4", "collation": "utf8mb4_unicode_ci"},
{"name": "db_test3", "charset": "latin1", "collation": "latin1_swedish_ci"}
]
}
#!/usr/bin/env python3
import json
import mysql.connector
# 读取配置文件
with open('config.json', 'r') as f:
config = json.load(f)
# 连接MySQL
connection = mysql.connector.connect(**config['mysql'])
cursor = connection.cursor()
# 批量创建数据库
for db in config['databases']:
query = f"CREATE DATABASE IF NOT EXISTS `{db['name']}` CHARACTER SET {db['charset']} COLLATE {db['collation']}"
cursor.execute(query)
print(f"数据库 {db['name']} 创建完成")
cursor.close()
connection.close()
使用建议
-
安全方面：
- 不要在脚本中硬编码密码，使用环境变量或配置文件
- 限制脚本执行权限（
chmod 700 script.sh） - 使用
IF NOT EXISTS避免重复创建错误
-
灵活扩展：
- 使用配置文件管理数据库列表
- 添加错误处理和日志记录
- 支持不同的字符集和排序规则
-
执行方式：
- 可以先测试运行（dry-run模式）
- 支持回滚操作
- 记录执行日志
选择哪种方法取决于你的具体需求和环境，Shell脚本适合简单的场景,Python脚本则有更好的错误处理和灵活性。
