5

I want to change MYSQL database name of the DB instance of Amazon RDS.

How it can be possible?

  • 3
    This question appears to be off-topic because it is easily answered by checking the documentation: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… – Michael - sqlbot Sep 9 '14 at 21:49
  • Thanks for your time. I have already read your suggested link. But it is for DB Instances not for Database. Can you give me any other link? – Napster Sep 10 '14 at 4:42
  • 1
    You have not mentioned which RDBMS your RDS instance is running (MySQL? Oracle? MSSQL? Postgres?) and that information is relevant, if you are asking how to rename one database in an RDS Instance. I assumed you were asking how to rename the instance... the word "database" is ambiguous, here. – Michael - sqlbot Sep 10 '14 at 13:32
  • Please check edit!! – Napster Sep 11 '14 at 5:56
  • I have four(4) critical questions: 1) Does the database have stored procedures ? 2) Does the database have triggers ? 3) Does the database have events ? 4) Do any tables have foreign key constraints ? – RolandoMySQLDBA Sep 12 '14 at 19:16
4

You can pull this off using mysqldump. Here the catch: You cannot ship the data because there may be a cost associated with shipping the data.

For this example, let's says you want to rename mydb to ourdb

STEP 01 : Create the new database

mysql> CREATE DATABASE ourdb;

STEP 02 : Get schema without the triggers

mysqldump -hrdshost -uuser -ppassword -d -t -R --skip-triggers mydb > /tmp/schema.sql

STEP 03 : Get the triggers

mysqldump -hrdshost -uuser -ppassword --skip-routines --triggers mydb > /tmp/triggers.sql

STEP 04 : Generate script to do INSERT ... SELECT across all tables

IF THERE ARE FOREIGN KEY CONSTRAINTS

ETL_DATA_SCRIPT=/tmp/DataTransfer.sql
echo -n > ${ETL_DATA_SCRIPT}
echo "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;" >> ${ETL_DATA_SCRIPT}
echo "SET NAMES utf8;" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_TIME_ZONE=@@TIME_ZONE;" >> ${ETL_DATA_SCRIPT}
echo "SET TIME_ZONE='+00:00';" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';" >> ${ETL_DATA_SCRIPT}
echo "SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;" >> ${ETL_DATA_SCRIPT}
echo "SET group_concat_max_len = 104857600;" >> ${ETL_DATA_SCRIPT}
SQL="SELECT CONCAT('INSERT INTO ourdb.',table_name,' SELECt * FROM mydb.',table_name,';')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='mydb'"
mysql -hrdshost -uuser -ppassword -ANe"${SQL}" >> ${ETL_DATA_SCRIPT}

IF THERE ARE NO FOREIGN KEY CONSTRAINTS

ETL_DATA_SCRIPT=/tmp/DataTransfer.sql
SQL="SELECT CONCAT('ALTER TABLE mydb.',table_name,' RENAME ourdb.',table_name,';')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='mydb'"
mysql -hrdshost -uuser -ppassword -ANe"${SQL}" > ${ETL_DATA_SCRIPT}

If there are no foreign key constraints, this should go fast.

STEP 05 : Combine the files into a single script

ETL_SCRIPT=/tmp/ETL.sql
cat /tmp/schema.sql        > ${ETL_SCRIPT}
cat /tmp/DataTransfer.sql >> ${ETL_SCRIPT}
cat /tmp/triggers.sql     >> ${ETL_SCRIPT}

STEP 06 : Review the script

vi -R /tmp/ETL.sql

or

less /tmp/ETL.sql

STEP 07 : Run the script

mysql -hrdshost -uuser -ppassword -Dourdb < ${ETL_SCRIPT}

STEP 08 : Make sure all the data is in the target database

You can do that

If there were no foreign key constraints, mydb should be empty and ourdb should have all the tables.

If there were foreign key constraints, make sure mydb and ourdb have the same number of tables and the same number of rows. Make sure all triggers present by running

SELECT COUNT(1) trigger_count,table_schema
FROM information_schema.triggers
GROUP By table_schema;

STEP 09 : Drop the Old Database Manually

mysql> DROP DATABASE mydb;

I did not include dropping the database in the script, just in case. :-)

GIVE IT A TRY !!!

  • Why not just RENAME TABLE tablename TO db2.tablename; for each table? Then there shouldn't be any shipping of data back and forth. – Bill Karwin Sep 12 '14 at 22:04
  • Aha, I tried it, and that doesn't work if you have triggers. I get an error: ERROR 1435 (HY000): Trigger in wrong schema – Bill Karwin Sep 12 '14 at 22:16
  • 1
    @BillKarwin I corrected a few mistakes (Left out database parameters). This should work because I just wrote something 10 timex more complicated to merge two schemas and their data where schema1 has more columns than schema2, and schema2 has more data the schema1. – RolandoMySQLDBA Sep 13 '14 at 1:00
6
+25

you can rename the database using the following command but make sure to take backup first

CREATE database new_db_name;
RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name;
DROP database db_name;

references: http://www.rndblog.com/how-to-rename-a-database-in-mysql/ https://stackoverflow.com/questions/12190000/rename-mysql-database

  • @RolandoMySQLDBA: My bad, I edited the script. thank you. – Ahmad Abuhasna Sep 12 '14 at 19:01
  • +1 for you because you came up with the principle idea first. – RolandoMySQLDBA Sep 13 '14 at 2:36
0

You can export the database, then import it again as a different name:

mysqldump -u username -p pwd -v old_db_name > old_db_name.sql mysqladmin -u username -p pwd create new_db_name mysql -u username -p pwd new_db_name < old_db_name.sql

Source: MySQL Reference for dumps

  • Your approach should work, but data transfer charges may apply because you mysqldump to the client and then ship the data back to a different database on the same instance (See my ServerFault post serverfault.com/a/418772/69271). – RolandoMySQLDBA Sep 12 '14 at 18:06
  • @RolandoMySQLDBA Good point! One of the sticky parts of RDS for sure :) – LowlyDBA Sep 12 '14 at 18:43
  • If this were EC2, then your answer would be quick-and-dirty. – RolandoMySQLDBA Sep 12 '14 at 19:06
0

The best way is to create a new database, then do show tables in the database you want to move from, and then do for every table:

rename table old_db.TABLE_NAME to new_db.TABLE_NAME
  • 1
    Adding the sql to do this will earn a +1 – Thronk Nov 16 '15 at 2:08
0

In my case, I realized there was no DB name when I've tried to modify it.

I couldn't find why this happened; I ultimately rebuilt the RDS to get things working.

-1
  1. Under the RDS "Instances" listing, click the arrow next to the DB Instance name.
  2. You will find additional details on the selected instance
  3. Click on the "Instance Actions" button and click "Modify"
  4. Modify your instance information and save
  • the instance name != the database name – Jordan Arseno Aug 28 '15 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.