Renaming mysql database

circus78 · 10-02-2014, 04:30 AM

Hi,
I need to rename a huge MySQL database.
I can't do this through phpmyadmin, as it will take very long time.
Can I simply stop mysql server daemon, rename database directory in /var/lib/mysql, and after restart mysql?
Is it a safe way to rename database?
Thank you

carlosinfl · 10-02-2014, 07:35 AM

Quote:

Originally Posted by circus78

Hi,
I need to rename a huge MySQL database.
I can't do this through phpmyadmin, as it will take very long time.
Can I simply stop mysql server daemon, rename database directory in /var/lib/mysql, and after restart mysql?
Is it a safe way to rename database?
Thank you

This is really bad. DO NOT DO THIS!!!

Your MySQL installation folder is /var/lib/mysql/ - that does not mean 'Database' in the word I think you're trying to define. The database server program should not / never be renamed as this will break MySQL. If you want to rename / change the name of an actual database you or someone has created in MySQL, you need to connect to the database as a database user w/ admin privileges.

To rename a database in MySQL, you do the following from the 'mysql' database:

Code:

use mysql;

RENAME DATABASE old_dbname TO new_dbname;

^
You MUST / SHOULD use SQL statements to alter / make changes correctly. DO NOT MAKE DATABASE CHANGES FROM THE FILE SYSTEM LEVEL. THAT IS VERY VERY BAD and makes pandas cry!

http://dev.mysql.com/doc/refman/5.1/...-database.html

circus78 · 10-02-2014, 10:03 AM

Hi,

I tried with your command but without success;

Quote:

mysql> use mysql;

mysql> RENAME DATABASE amavis TO no_amavis;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE db_amavis amavis' at line 1
mysql>

# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for debian-linux-gnu (x86_64) using readline 6.1

szboardstretcher · 10-02-2014, 10:05 AM

Rename Database was removed around that version.

You will probably want Alter Database instead.

Read here for correct syntax: http://dev.mysql.com/doc/refman/5.1/...-database.html

Working on the database is usually reserved for a DB admin or sysadmin,. do you have one available to help you out? That is the best course of action, since the person responsible will make backups and be able to restore and fix it if there are issues.

circus78 · 10-02-2014, 10:26 AM

Hi,
I am the sysadmin of this machine

What is the correct syntax for renaming database (using "ALTER DATABASE")?
It seems that the only way is:

1. dump database
2. drop database
3. import database with new name

but, as I said, I would like to avoid this, since it's a big amount of data.
Thank you very much.

szboardstretcher · 10-02-2014, 11:02 AM

Cool.

Read here for correct syntax: http://dev.mysql.com/doc/refman/5.1/...-database.html

circus78 · 10-04-2014, 02:18 PM

Quote:

Originally Posted by szboardstretcher

Cool.

Read here for correct syntax: http://dev.mysql.com/doc/refman/5.1/...-database.html

No way.

Code:

mysql> create database old_name;
Query OK, 1 row affected (0.00 sec)

mysql> alter database old_name upgrade data directory new_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new_name' at line 1
mysql> alter database old_name upgrade data directory name new_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new_name' at line 1
mysql> alter database old_name upgrade data directory name to new_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to new_name' at line 1
mysql>

astrogeek · 10-04-2014, 02:45 PM

Quote:

Originally Posted by circus78

No way.

Code:

mysql> create database old_name;
Query OK, 1 row affected (0.00 sec)

mysql> alter database old_name upgrade data directory new_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new_name' at line 1
mysql>...

ALTER DATABASE is not intended to support changing the name of the database - it is for updating the character encoding of the name. From the built-in help (MariaDB)

Quote:

"It is intended to update a database directory name to the current
encoding format if the name contains special characters that need
encoding."

Also be sure to read that page in its entireity - is is also necessary to drop and rebuild all stored procedures to use the new character encodings.

How large is this DB?

A quick search turned up this solution, table by table rename. Of course, you will also need to update access rights and all other things that reference the old DB to use the new DB.

Otherwise I think that you are stuck with dump/restore as the safest path.

circus78 · 10-05-2014, 01:57 PM

Quote:

Originally Posted by astrogeek

ALTER DATABASE is not intended to support changing the name of the database - it is for updating the character encoding of the name.

Quote:

Otherwise I think that you are stuck with dump/restore as the safest path.

Yes, I agree.
I was looking for a quicker solution
Thank you very much