Linux - ServerThis forum is for the discussion of Linux Software used in a server related context.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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
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!
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
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.
Last edited by szboardstretcher; 10-02-2014 at 11:03 AM.
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>
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.