Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I have a database (db4) that was created by MySQL4, and a database (db5) that was created by MySQL5. db4 contains several tables with the charset latin1 and several indices, but no data that was encrypted using the MySQL "PASSWORD" function. db5 is empty.
I want to migrate all tables and indices from db4 to db5 (which are actually on the same server). Ideally this should be done without any loss of information and within a short period of time.
Which terminal commands do I need to download the complete database from MySQL4 and insert the data afterwards to db5? Do I have to re-create the indices?
You can make a dump of database in mysql4 using mysqldump. And than upload it to MySQL5 using mysql command.
mysqldump dbname > file
mysql dbname < file
All the indexes will be recreated automatically.
–
# change comment style from -- to #
sed -r -i -e 's/^--(.*)$/#\1/' db4.sql
# change type declaration keyword from "TYPE" to "ENGINE"
sed -i -e 's/) TYPE=/) ENGINE=/' db4.sql
# adapt timestamp field definition
sed -i -e 's/timestamp(14) NOT NULL,$/timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,/' db4.sql
on the mysql5 server you can now import the modified SQL dump
mysql -uuser -ppass db5 < db4.sql
Sven, I think doing a backup of your data in db4 and restoring it in db5 will work for you.
Backup
mysqldump database_name > file_name.sql
Restore
mysql < file_name.sql
It can be also done within a single step, using the following command:
mysqldump -u dbo4 --password="..." --default-character-set="latin1" db4 | mysql -S /tmp/mysql5.sock -u dbo5 --password="..." --default-character-set="latin1" db5
Unfortunately the standard-values with special characters are not correctly imported, and there seems to be no way to avoid that: How to maintain character-set of standard-values when uploading MySQL-dump.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.