User Tools

Site Tools


Helping commands when migrating MySQL databases to different servers

If you have a full databases dump in a single file, you can get only the dump for the wanted database, by doing this commands:

# search for the create database sentences and track yours (get the line number)
grep -inP '^create database' all-databases-dump.sql
# imagine, the line number given by the previous command, is 564, then you'will do:
tail -n +564 all-databases-dump.sql > yourapp-dump.sql

In case you are migrating to a database and you get this datetime default value error, do as follow to fix the value:

> Invalid default value for 'Date'
sed -i 's/datetime NOT NULL DEFAULT '"'"'0000-00-00 00:00:00'"'"'/datetime NULL DEFAULT NULL/g' yourapp-dump.sql
sed -i 's/timestamp NOT NULL DEFAULT '"'"'0000-00-00 00:00:00'"'"'/timestamp NULL DEFAULT NULL/g' yourapp-dump.sql
sed -i 's/'"'"'0000-00-00 00:00:00'"'"'/NULL/g' yourapp-dump.sql
docu/csheet/sysadm/db/my/database_migration_help.txt · Last modified: 2021/06/17 13:01 by admin