MYSQL : UPDATE DATABASE PREFIX

1. Go into mysql...then show tables;

2. Copy all tables that you want to rename ...

3. Paste in this script (eg:save the file as batch_rename.sh ) :

OLD_PREFIX = "mdl"

NEW_PREFIX = "mdlsirap"

data=
<<-EOD
| mdl_assignment |
| mdl_assignment_submissions |
| mdl_backup_config |
| mdl_backup_courses |
| mdl_backup_files |
| mdl_backup_ids |
| mdl_backup_log |
| mdl_block |
| mdl_block_instance |
| mdl_block_pinned |
EOD
tables = []
data.each_line do |line|
if line=~/(#{OLD_PREFIX}\w+)/
puts "RENAME TABLE #{$1} TO #{NEW_PREFIX}#{$1};"
end
end


4. Then run this script using ruby command-line (I assume you already install ruby) :
# ruby batch_rename.sh

5. The output :
RENAME TABLE mdl_assignment TO mdlsirap_assignment;
RENAME TABLE mdl_assignment_submissions TO mdlsirap_assignment_submissions; RENAME TABLE mdl_backup_config TO mdlsirap_backup_config;
RENAME TABLE mdl_backup_courses TO mdlsirap_backup_courses;
RENAME TABLE mdl_backup_files TO mdlsirap_backup_files;
RENAME TABLE mdl_backup_ids TO mdlsirap_backup_ids;
RENAME TABLE mdl_backup_log TO mdlsirap_backup_log;
RENAME TABLE mdl_block TO mdlsirap_block;
RENAME TABLE mdl_block_instance TO mdlsirap_block_instance;
RENAME TABLE mdl_block_pinned TO mdlsirap_block_pinned;

6. Just masukan sql statement ni dlm satu file lain...tapi kalau nak senang...buat step no. 4 tu camni :
# ruby batch_rename.sh > a.sql

7. Run the sql file using mysql...:
# mysql -uroot -p namadatabase < a.sql

**Check balik dalam database, prefix semua table dah bertukar.

8. Done