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.

If the database is really huge, you could compress these files, eg with mysqldump dbname | gzip > file.gz then gunzip -c file.gz | mysql dbname – Basile Starynkevitch Jul 30, 2012 at 9:16
    # 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.

  •