LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   Renaming mysql database (https://www.linuxquestions.org/questions/linux-server-73/renaming-mysql-database-4175520822/)

circus78 10-02-2014 04:30 AM
Renaming mysql database
 
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 (Post 5247908)
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 (Post 5248059)
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 (Post 5249037)
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 (Post 5249054)
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


All times are GMT -5. The time now is 02:31 AM.