Rename mysql5.7 database

The database version is 5.7.17

Today, I received a demand that I need to change the name of the database from A to B. I checked that there are less than 200 tables under the A database, which is not very big. If you can use import and export, it will still be slow, time is precious, and the less time you use, the better.

There is a rename table command, write a script to mainly use it to implement the requirements

#! / bin/bash

# first determine whether the new database exists, and create it if it does not exist.

# list the tables in the old database, where the N and s parameters play the role of formatting, which is similar to pagesize in sqlplus under Oracle

# use for loop to rename.

Mysql-uroot-pxxxxDB2017#-e'create database if not exists cus_0042'

List_table=$ (mysql-uroot-pxxxxDB2017#-Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='old_databases'")

For table in $list_table

Do

Mysql-uroot-pxxxxDB2017#-e "rename table old_databases.$table to cus_0042.$table"

Done

After the execution is successful, the old_databases is empty, but deleted.

If it is the storage engine of myisam, just mv it under the data directory.