Monday, December 9, 2013

Renaming a Mysql database

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
Does not work anymore since this is quite dangerous.
So the best way to do this will be first create a database with command: create database myDB in mysql prompt, then do a:
mysqldump -u root -p oldname | mysql -u root -ppassword myDB

This may or may not work depending on what is your max_allowed_packet is set. In many cases, it may fail giving an error such as:

ERROR 1153 (08S01) at line 1441: Got a packet bigger than 'max_allowed_packet' bytes

For this, you may have to go back to your mysql prompt and use a command:

mysql> show GLOBAL variables like 'max_allowed_packet%';
| Variable_name      | Value   |
| max_allowed_packet | 1048576 |


Now go to your my.cnf file as root (most likely this will be located in your /etc folder)

edit or add the line (if there is no variable as max_allowed_packet) below your mysqld line.

max_allowed_packet = 2024M

Now as root do a:

service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

Then go to mysql prompt and check if your max_allowed_packet size has changed by querying:

show global variables like 'max_allowed_packet%';

This time you may see a changed number such as this:

| Variable_name      | Value      |
| max_allowed_packet | 1073741824 |
1 row in set (0.00 sec)

Now run your copy database command:
mysqldump -u root -p oldname | mysql -u root -ppassword myDB

see if it is working...