Rename table emp.employees to emp_test.employees Rename table emp.dept_manager to emp_pt_manager Rename table emp.departments to emp_partments Rename table emp._emp_new to emp_test._emp_new | test ~]# time /tmp/rename_db localhost emp emp_testĬreate database emp_test DEFAULT CHARACTER SET latin1 Will try to rename the database schema using the script, which takes some seconds to complete as opposed to time consuming dump/restore method. To demonstrate the use of this script, used a sample schema “emp”, created test triggers, stored routines on that schema. mysql> DROP TRIGGER ĥ) Restore the above dump files once all the “Base” tables found in step #2 are renamed. mysql> select TABLE_NAME from information_schema.tables whereĤ) Drop the triggers on the current tables in the old_schema. Views can be found using a query on the same information_schema.TABLES table. Table_schema='' and TABLE_TYPE='BASE TABLE' ģ) Dump the views in an out file. ![]() mysql> select TABLE_NAME from information_schema.tables where These can be found using a query on information_schema.TABLES table. ![]() Mysql employees -d -t -R -E > stored_routines_triggers_events.outĢ) Generate a list of only “BASE” tables. 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). You can alter that limit by running SET SESSION group_concat_max_len = 100000000 (or some other large number). Note that GROUP_CONCAT has a default length limit that may be exceeded for databases with a large number of tables. This will generate a SQL command that you'll have to then run. ($1 and $2 are source and target respectively) Or SELECT GROUP_CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name SEPARATOR ' ') ![]() Take your pick, but I prefer the original): SELECT CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name, ' ') You can use either of these two scripts (I originally suggested the former and someone "improved" my answer to use GROUP_CONCAT. You must create the destination database before running the script generated from the command. You can use SQL to generate an SQL script to transfer each table in your source database to the destination database. If you have stored procedures, you can copy them afterwards: If that is the case, use a traditional way to clone a database and then drop the old one: ![]() If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If your database has no password, remove the -u username -ppassword part. There is no space between the option -p and the password.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 You will need to adjust the permissions after that.įor scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe 'show tables' | while read table \ĭo mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table" done 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
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |