All In One Script



PHP,HTLM,CSS,Jquery,AJAX,Javascript and etc doubts and sample codes

  • Home
  • Javascript
  • PHP
  • CSS
  • SQL/MYSQL

How do I quickly rename a MySQL database (change schema name)?

by Blogger 4:28:00 AM Database MYSQL Rename

How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this.
Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall.
This needs to work with InnoDB, which stores things very differently than MyISAM.


Answer :

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
Or
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
Notes: there is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
Also, if you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db






READ MORE
SHARE :

Search This Blog

Followers

  • Popular
  • Recent
  • Comments
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to efficiently iterate over each Entry in a Map?
    In Java, difference between default, public, protected, and private
    How to check if PHP array is associative or sequential?
    Length of a JavaScript object
    Does finally always execute in Java?
    How to return the date part only from a SQL Server datetime datatype
    Solved : curl_init() function not working in Ubuntu
    What is the difference between “INNER JOIN” and “OUTER JOIN”?
    Get first key in a (possibly) associative array?

Instagram

About

Popular Posts

  • How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP Php Get Real visiter's IP and ISP and Country and City and Countr...
  • How to efficiently iterate over each Entry in a Map?
    How to efficiently iterate over each Entry in a Map? If I have an object implementing the  Map  interface in Java and I wish to iterate...
  • In Java, difference between default, public, protected, and private
    In Java, difference between default, public, protected, and private In Java , are there clear rules on when to use each of access modifi...
  • How to check if PHP array is associative or sequential?
    How to check if PHP array is associative or sequential? PHP treats all arrays as associative, so there aren't any built in function...
  • Length of a JavaScript object
    Length of a JavaScript object If I have a JavaScript object, say var myObject = new Object (); myObject [ "firstname" ] ...
  • Does finally always execute in Java?
    Does finally always execute in Java? I have a try/catch block with  return s inside it. Will the finally block be called? For example...
  • How to return the date part only from a SQL Server datetime datatype
    How to return the date part only from a SQL Server datetime datatype SELECT GETDATE () Returns:  2008-09-22 15:24:13.790 I want tha...
  • Solved : curl_init() function not working in Ubuntu
    Solved : curl_init() function not working in Ubuntu  Here solved the error  Fatal error: Call to undefined function curl_init() ...
  • What is the difference between “INNER JOIN” and “OUTER JOIN”?
    What is the difference between “INNER JOIN” and “OUTER JOIN”? Also how do  LEFT JOIN ,  RIGHT JOIN  and  FULL JOIN  fit in? Answ...
  • Get first key in a (possibly) associative array?
    Get first key in a (possibly) associative array? What's the best way to determine the first key in a possibly associative array? My...

statcounter



statcounter



Template Created By ThemeXpose & Blogger Templates