MySQL数据库改名的三种实现方式

rename database olddbname to newdbname

#创建数据库
create database if not exists `库名` default charset utf8mb4 collate utf8mb4_unicode_ci;

# 将db1库备份到db1.sql文件
mysqldump -u root -p db1 > /tmp/db1.sql;

#enter password: 
#[root@xxx ~]# 

# 导入备份文件到新库db2
mysql -u root -p db2 < /tmp/db1.sql;

#enter password: 
#[root@xxx ~]# 


# 删除旧库（如果真的需要）
drop database db1;

#!/bin/bash
# 假设将db1数据库名改为db2
# myisam直接更改数据库目录下的文件即可
# 下面脚本是创建新数据库，获取旧数据库所有表名，将其循环移动到新数据库

mysql -uroot -p123456 -e 'create database if not exists db2'
list_table=$(mysql -uroot -p123456 -nse "select table_name from information_schema.tables where table_schema='db1'")

for table in $list_table
do
    mysql -uroot -p123456 -e "rename table db1.$table to db2.$table"
done

[root@xxx script]# mysql --version
mysql  ver 8.0.35 for linux on x86_64 (mysql community server - gpl)

[root@xxx script]# bash newdatabase.sh 
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.
#mysql:[警告]在命令行界面使用密码可能是不安全的。 

#!/bin/bash
function b64code()
{
    #参数1: 需要加密的串
    passauth=$1
    #参数2: 加密的次数，次数越多密码越长
    for i in `seq 1 $2`
    do
        #python2
        passauth=`echo $passauth |python -c "import base64;s=raw_input();print(base64.b64encode(s))"`
        #python3
        #passauth=`echo $passauth |python -c "import base64;s=input(); print(base64.b64encode(s.encode()).decode());"`
        echo "[+]第${i}次加密结果:$passauth"
    done
}

#执行 b64code 加密方法，使用明文密码加密10次
b64code 123456 10
#密码是字符串加上单引号

[root@xxx script]# bash sginpwd.sh
[+]第1次加密结果:mtizndu2
[+]第2次加密结果:tvrjek5evti=
[+]第3次加密结果:vfzssmvrnuvwvek9
[+]第4次加密结果:vkzau1ntvnjovvzxvkvroq==
[+]第5次加密结果:vmtayvuxtnrwbkppvlzawfzrvnjput09
[+]第6次加密结果:vm10yvlwvxhubljxymtwufzswmfxrlpyvm5kufvumdk=
[+]第7次加密结果:vm0xmflwbfdwwghvymxkwfltdhdvrlpzv21gwfjschlwbtvlvuzwvu1eaz0=
[+]第8次加密结果:vm0wee1gbfdirmrxv0dovllteetxrmx0zehkvljschpwmjfhv0zkc2nibfdivfzmvlvav1zvmuvheja9
[+]第9次加密结果:vm0wd2vfmudirmrpum1swfywzg9wbgx0zuv0wfjtedbarwhrvmxkc2nicfdnakzivjbas2mytklirmrpvkzatvzsvmfwmvpwtvvwagvqqtk=
[+]第10次加密结果:vm0wd2qyvkznvwrpum1scfvtmvnxrll3wkc5v2jhedbavvywv0zkdgveqmfsv2hyvm14s2mytkljrmroywtasvzqqmftmk15vgtsavjtunbwa1phvfzac1ztrldnvnbxvfzwv2fhvnfrvgs9

#!/bin/bash
# 假设将db1数据库名改为db2
# myisam直接更改数据库目录下的文件即可
# 下面脚本是创建新数据库，获取旧数据库所有表名，将其循环移动到新数据库

function decode()
{
    #参数1: 加密串内容
    passdstr=$1
    #参数2: 密码解析次数
    for i in `seq 1 $2`
    do
        passdstr=`echo $passdstr |python -c "import base64;s=input(); print(base64.b64decode(s.encode()).decode());"`
    done
}

#b64code方法得到的密文放在这里
mysqlpass="vm0wd2qyvkznvwrpum1scfvtmvnxrll3wkc5v2jhedbavvywv0zkdgveqmfsv2hyvm14s2mytkljrmroywtasvzqqmftmk15vgtsavjtunbwa1phvfzac1ztrldnvnbxvfzwv2fhvnfrvgs9"
#使用加密10次的密文 解密
decode $mysqlpass 10
mysql -uroot -p"${passdstr}" -e "show databases;"

mysql -uroot -p"${passdstr}" -e 'create database if not exists db2'
list_table=$(mysql -uroot -p"${passdstr}" -nse "select table_name from information_schema.tables where table_schema='db1'")

for table in $list_table
do
    mysql -uroot -p"${passdstr}" -e "rename table db1.$table to db2.$table"
done
function decode()
{
    #参数1: 加密串内容
    passdstr=$1
    #参数2: 密码解析次数
    for i in `seq 1 $2`
    do
		#python2
		passdstr=`echo $passdstr |python -c "import base64;s=raw_input();print(base64.b64decode(s))"`
		#python3
        #passdstr=`echo $passdstr |python -c "import base64;s=input(); print(base64.b64decode(s.encode()).decode());"`

    done
}

#b64code方法得到的密文放在这里
mysqlpass="vm0wd2qyvkznvwrpum1scfvtmvnxrll3wkc5v2jhedbavvywv0zkdgveqmfsv2hyvm14s2mytkljrmroywtasvzqqmftmk15vgtsavjtunbwa1phvfzac1ztrldnvnbxvfzwv2fhvnfrvgs9"
#使用加密10次的密文 解密
decode $mysqlpass 10
mysql -uroot -p"${passdstr}" -e "show databases;"

mysql -uroot -p"${passdstr}" -e 'create database if not exists db2'
list_table=$(mysql -uroot -p"${passdstr}" -nse "select table_name from information_schema.tables where table_schema='db1'")

for table in $list_table
do
    mysql -uroot -p"${passdstr}" -e "rename table db1.$table to db2.$table"
done

[root@xxx script]# bash newdatabase_beff.sh
mysql: [warning] using a password on the command line interface can be insecure.
+--------------------+
| database           |
+--------------------+
| db1                |
| db2                |
+--------------------+
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.
mysql: [warning] using a password on the command line interface can be insecure.

#登录mysql，可以看到db1数据表迁移到db2了
mysql> use db1;
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql> show tables;
+---------------+
| tables_in_db1 |
+---------------+
| active_log    |
| new           |
+---------------+
2 rows in set (0.00 sec)

mysql> show tables;
empty set (0.00 sec)

mysql> use db2;
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql> show tables;
+---------------+
| tables_in_db2 |
+---------------+
| active_log    |
| new           |
+---------------+
2 rows in set (0.00 sec)

systemctl stop mysqld
#service mysql stop

mv /var/lib/mysql/old_database /var/lib/mysql/new_database

systemctl start mysqld
#service mysql start

show databases;