MySQL database rename fast and safe method (3 kinds)

How to rename MySQL database

How to change the database name of the Innodb engine table and how to operate the MyISAM engine.

If the table is a MyISAM engine, you can go directly to the database directory and mv to rename the folder. Innodb is completely unacceptable and will prompt that the relevant table does not exist.

The first method: rename database is deprecated

RENAME database old_db_name TO new_db_name

This is available for versions 5.1.7 to 5.1.23, but it is not officially recommended and there is a risk of data loss.

The second method: mysqldump backup

1. Create the database that needs to be renamed.
2.mysqldum exports the database to be renamed
3. Delete the original old library (determine whether it is really needed)
Of course, this method is safe, but it will be time-consuming if the amount of data is large.

mysqldump -uxxxx -pxxxx -h xxxx db_name > db_name_dump.SQL #Backupmysqldump -uroot -p123456 -h127.0.0.1 test > test.sql #Backupmysql -uxxxx -pxxxx -h xxxx -e “CREATE DATABASE new_db_name”
mysql -uxxxx -pxxxx -h xxxx new_db_name < db_name_dump.SQL #Restore mysql -uxxxx -pxxxx -h xxxx -e “DROP DATABASE db_name”

The third method: fast and safe traversal of the rename table

I'm going to use a script here, it's very simple, I believe everyone can understand it

#!/bin/bash
# Assume that the db_name database name is changed to new_db_name
# MyISAM can directly change the files in the database directory mysql_login=mysql -uroot -p123456
olddb="db_name"
newdb="new_db_name"

#$mysql_login -e “CREATE DATABASE $newdb
$mysql_login -e 'create database if not exists $newdb'
list_table=$($mysql_login -Nse "select table_name from information_schema.TABLES
    where TABLE_SCHEMA='$olddb'")
for table in $list_table;
do
$mysql_login -e "rename table $olddb.$table to $newdb.$table"
done;
#$mysqlconn -e “DROP DATABASE $olddb”

The rename table command is used here to change the table name. However, if the database name is added after the new table name, the table of the old database will be moved to the new database. Therefore, this method is both safe and fast.

Finally, the usage of rename is attached

Command: rename table original table name to new table name;

For example: Change the name of the table MyClass to YouClass
mysql> rename table MyClass to YouClass;

You cannot have any locked tables or active transactions when you perform a RENAME. You must also have ALTER and DROP permissions on the original table, and CREATE and INSERT permissions on the new table.

If MySQL encounters any errors during a multi-table rename, it will perform a rollback rename of all renamed tables, returning everything to its original state.

Mysql: Using Navicat to implement scheduled backup

1. Functional Description

The data in the database needs to be backed up every day, and the backup can be restored in time if any problem occurs;

2. Implementation steps

Open navicat and click on plan and click on new batch job

Select the database to back up

Click Select and Save

Click on the upper toolbar to set the scheduled task

Click Plan, click Create, and set the execution time.

The setting is successful, and the data is backed up by default in C:\Users\Administrator\Documents\Navicat\MySQL\servers;

You can also see it by clicking on backup in Navicat. Click to select the backup file to restore the backup.

Confirm that the backup is in C:\Users\Administrator\Documents\Navicat\MySQL\servers;

You can also see it by clicking on backup in Navicat. Click to select the backup file to restore the backup.

This concludes this article on 3 quick and safe methods to rename MySQL databases. For more information on MySQL database renaming, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!