Rename MySQL database

Question

I created a database with the name of hrms  Now I need to change database name to sunhrm  But  It is disabled in MySQL workbench  Can I do that on the Linux server itself

User · Answer

First backup the old database called HRMS and edit the script file with replace the word HRMS to SUNHRM  After this step import the database file to the mysql

User · Answer

You can do it by RENAME statement for each table in your  current db  after create the new schema  other db   RENAME TABLE current db tbl name TO other db tbl name   Source Rename Table Syntax

User · Answer

If your DB contains only MyISAM tables  do not use this method if you have InnoDB tables     shut down the MySQL server go to the mysql data directory and rename the database directory  Note  non-alpha characters need to be encoded in a special way  restart the server adjust privileges if needed  grant access to the new DB name    You can script it all in one command so that downtime is just a second or two

User · Answer

Another way to rename the database or taking image of the database is by using Reverse engineering option in the database tab  It will create a ERR diagram for the database  Rename the schema there   after that go to file menu and go to export and forward engineer the database   Then you can import the database

User · Answer

Well there are 2 methods    Method 1  A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema  and then dropping the old schema  if needed    From Shell   mysqldump emp  gt  emp out  mysql -e  CREATE DATABASE employees    mysql employees  lt  emp out   mysql -e  DROP DATABASE emp     Although the above method is easy  it is time and space consuming  What if the schema is more than a 100GB  There are methods where you can pipe the above commands together to save on space  however it will not save time      To remedy such situations  there is another quick method to rename   schemas  however  some care must be taken while doing it    Method 2  MySQL has a very good feature for renaming tables that even works across different schemas  This rename operation is atomic and no one else can access the table while its being renamed  This takes a short time to complete since changing a table   s name or its schema is only a metadata change  Here is procedural approach at doing the rename    Create the new database schema with the desired name  Rename the tables from old schema to new schema  using MySQL   s    RENAME TABLE    command  Drop the old database schema    If there are views  triggers  functions  stored procedures in the schema  those will need to be recreated too  MySQL   s    RENAME TABLE    fails if there are triggers exists on the tables  To remedy this we can do the following things    1   Dump the triggers  events and stored routines in a separate file  This done using -E  -R flags  in addition to -t -d which     dumps the triggers  to the mysqldump command  Once triggers are     dumped  we will need to drop them from the schema  for RENAME TABLE     command to work       mysqldump  lt old schema name gt  -d -t -R -E  gt  stored routines triggers events out   2  Generate a list of  only    BASE    tables  These can be found using a     query on information schema TABLES table    mysql gt  select TABLE NAME from information schema tables where      table schema   lt old schema name gt   and TABLE TYPE  BASE TABLE     3  Dump the views in an out file  Views can be found using a query on     the same information schema TABLES table   mysql gt  select TABLE NAME from information schema tables where     table schema   lt old schema name gt   and TABLE TYPE  VIEW      mysqldump  lt database gt   lt view1 gt   lt view2 gt       gt  views out   4  Drop the triggers on the current tables in the old schema   mysql gt  DROP TRIGGER  lt trigger name gt         5  Restore the above dump files once all the    Base    tables found in step  2 are renamed   mysql gt  RENAME TABLE  lt old schema gt  table name TO  lt new schema gt  table name        mysql  lt new schema gt   lt  views out   mysql  lt new schema gt   lt  stored routines triggers events out   Intricacies with above methods   We may need to update the GRANTS for users such that they match the correct schema name  These could fixed with a simple UPDATE on mysql columns priv  mysql procs priv  mysql tables priv  mysql db tables updating the old schema name to new schema and calling    Flush privileges      Although    method 2  seems a bit more complicated than the    method 1   this is totally scriptable  A simple bash script to carry out the above steps in proper sequence  can help you save space and time while renaming database schemas next time   The Percona Remote DBA team have written a script called    rename db    that works in the following way     root dba     tmp rename db rename db  lt server gt   lt database gt   lt new database gt    To demonstrate the use of this script  used a sample schema    emp     created test triggers  stored routines on that schema  Will try to rename the database schema using the script  which takes some seconds to complete as opposed to time consuming dump restore method   mysql gt  show databases   --------------------    Database              --------------------    information schema     emp                    mysql                  performance schema     test                  --------------------     root dba     time  tmp rename db localhost emp emp test create database emp test DEFAULT CHARACTER SET latin1 drop trigger salary trigger rename table emp   emp new to emp test   emp new rename table emp  emp new to emp test  emp new rename table emp departments to emp test departments rename table emp dept to emp test dept rename table emp dept emp to emp test dept emp rename table emp dept manager to emp test dept manager rename table emp emp to emp test emp rename table emp employees to emp test employees rename table emp salaries temp to emp test salaries temp rename table emp titles to emp test titles loading views loading triggers  routines and events Dropping database emp  real    0m0 643s user    0m0 053s sys     0m0 131s    mysql gt  show databases   --------------------    Database              --------------------    information schema     emp test               mysql                  performance schema     test                  --------------------    As you can see in the above output the database schema    emp    was renamed to    emp test    in less than a second  Lastly  This is the script from Percona that is used above for    method 2       bin bash   Copyright 2013 Percona LLC and or its affiliates set -e if   -z   3     then     echo  rename db  lt server gt   lt database gt   lt new database gt       exit 1 fi db exists  mysql -h  1 -e  show databases like   3   -sss  if   -n   db exists     then     echo  ERROR  New database already exists  3      exit 1 fi TIMESTAMP  date   s  character set  mysql -h  1 -e  show create database  2 G  -sss   grep  Create   awk -F CHARACTER SET     print  2     awk   print  1    TABLES  mysql -h  1 -e  select TABLE NAME from information schema tables where table schema   2  and TABLE TYPE  BASE TABLE   -sss  STATUS    if     STATUS     0        -z   TABLES     then     echo  Error retrieving tables from  2      exit 1 fi echo  create database  3 DEFAULT CHARACTER SET  character set  mysql -h  1 -e  create database  3 DEFAULT CHARACTER SET  character set  TRIGGERS  mysql -h  1  2 -e  show triggers G    grep Trigger    awk   print  2    VIEWS  mysql -h  1 -e  select TABLE NAME from information schema tables where table schema   2  and TABLE TYPE  VIEW   -sss  if   -n   VIEWS     then     mysqldump -h  1  2  VIEWS  gt   tmp   2  views  TIMESTAMP  dump fi mysqldump -h  1  2 -d -t -R -E  gt   tmp   2  triggers  TIMESTAMP  dump for TRIGGER in  TRIGGERS  do     echo  drop trigger  TRIGGER      mysql -h  1  2 -e  drop trigger  TRIGGER  done for TABLE in  TABLES  do     echo  rename table  2  TABLE to  3  TABLE      mysql -h  1  2 -e  SET FOREIGN KEY CHECKS 0  rename table  2  TABLE to  3  TABLE  done if   -n   VIEWS     then     echo  loading views      mysql -h  1  3  lt   tmp   2  views  TIMESTAMP  dump fi echo  loading triggers  routines and events  mysql -h  1  3  lt   tmp   2  triggers  TIMESTAMP  dump TABLES  mysql -h  1 -e  select TABLE NAME from information schema tables where table schema   2  and TABLE TYPE  BASE TABLE   -sss  if   -z   TABLES     then     echo  Dropping database  2      mysql -h  1  2 -e  drop database  2  fi if    mysql -h  1 -e  select count    from mysql columns priv where db   2   -sss  -gt 0    then     COLUMNS PRIV      UPDATE mysql columns priv set db   3  WHERE db   2    fi if    mysql -h  1 -e  select count    from mysql procs priv where db   2   -sss  -gt 0    then     PROCS PRIV      UPDATE mysql procs priv set db   3  WHERE db   2    fi if    mysql -h  1 -e  select count    from mysql tables priv where db   2   -sss  -gt 0    then     TABLES PRIV      UPDATE mysql tables priv set db   3  WHERE db   2    fi if    mysql -h  1 -e  select count    from mysql db where db   2   -sss  -gt 0    then     DB PRIV      UPDATE mysql db set db   3  WHERE db   2    fi if   -n   COLUMNS PRIV         -n   PROCS PRIV         -n   TABLES PRIV         -n   DB PRIV     then     echo  IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW       if   -n   COLUMNS PRIV     then echo   COLUMNS PRIV   fi     if   -n   PROCS PRIV     then echo   PROCS PRIV   fi     if   -n   TABLES PRIV     then echo   TABLES PRIV   fi     if   -n   DB PRIV     then echo   DB PRIV   fi     echo      flush privileges   fi

User · Answer

I don t think you can do this  Basic answers will work in many cases  and in others cause data corruptions   A strategy needs to be chosen based on heuristic analysis of your database   That is the reason this feature was implemented  and then removed   doc  You ll need to dump all object types in that database  create the newly named one and then import the dump   If this is a live system you ll need to take it down  If you cannot  then you will need to setup replication from this database to the new one  If you want to see the commands that could do this   satishD has the details  which conveys some of the challenges around which you ll need to build a strategy that matches your target database

User · Answer

For impatient mysql users  like me   the solution is    etc init d mysql stop mv  var lib mysql old database  var lib mysql new database   etc init d mysql start

User · Answer

I used following method to rename the database   take backup of the file using mysqldump  or any DB tool eg heidiSQL mysql administrator etc Open back up  eg backupfile sql  file in some text editor  Search and replace the database name and save file  Restore the edited SQL file

User · Answer

You can create a new database exactly as the previous database existed and then drop the old database when you re done  Use the mysqldump tool to create a  sql backup of the database via mysqldump orig db  gt  orig db sql or if you need to use a username and password then run mysqldump -u root -p orig db  gt  orig db sql  orig db is the name of the database you want to  rename   root would be the user you re logging in as and orig db sql would be the file created containing the backup  Now create a new  empty database with the name you want for the database  For example  mysql -u root -p -e  create database new db   Once that s done  then run mysql -u root -p new db  lt  orig db sql  new db now exists as a perfect copy of orig db  You can then drop the original database as you now have it existing in the new database with the database name you wanted   The short  quick steps without all the above explanation are    mysqldump -u root -p original database  gt  original database sql mysql -u root -p -e  create database my new database  mysql -u root -p my new database  lt  original database sql mysql -u root -p -e drop database originl database   Hope this helps and this is a reliable means to accomplish it without using some ad-hoc method that will corrupt your data and create inconsistencies

User · Answer

In short no   It is generally thought to be too dangerous to rename a database  MySQL had that feature for a bit  but it was removed   You would be better off using the workbench to export both the schema and data to SQL then changing the CREATE DATABASE name there before you run import it

User · Answer

In case you need to do that from the command line  just copy  adapt  amp  paste this snippet     mysql -e  CREATE DATABASE   new database     for table in  mysql -B -N -e  SHOW TABLES   old database  do    mysql -e  RENAME TABLE   old database      table   to   new database      table    done mysql -e  DROP DATABASE   old database

User · Answer

It s possible to copy database via mysqldump command without storing dump into file    mysql -u root -p -e  create database my new database  mysqldump -u root -p original database   mysql -u root -p my new database mysql -u root -p -e  drop database original database

[mysql] Rename MySQL database

Examples related to mysql

Examples related to database

Examples related to linux

Examples related to rename