CODEWITHSUNDEEP
mysql
Daryl Gill
Daryl Gill

Reputation: 5524

Automating MySQL migration from development to staging

I'm making an attempt to automate my migration process from development to staging without alot of unnecessary downtime/manual configuring. it's working "well" to a certain extent.

I'm using the mysqldump commands to grab the following:

mysqldump --no-data -u root -p{password} {db} > createdump.sql

On the development server which creates a dump of the create tables queries. FTPing over to the remote server and using the following:

mysqldump --no-create-info -u root -p{password} {db} > data.sql
# Import the new table structure
mysql -u root -p{password} {db} < /root/tempdumps/createdump.sql

# Import the old staging/production data
mysql -u root -p{password} {db} < /root/tempdumps/data.sql

99% of the time, there will be no new data on the new structure & The overall idea is to only populate these new fields/tables when the need for it arises. However, i'm getting hit with an error when importing the old data. The insert column lists do not match up to the insert query being called. What would be the best approach to ignore these errors and continue inserting?

As the table adjustments will contain default values, so the column name is not an absolute must when importing the old data

Upvotes: 1

Views: 96

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

I would try to use mysqldump --complete-insert along with your other options, when you dump your data.sql file.

This will cause mysqldump to output INSERT statements that name the columns explicitly, like this:

INSERT INTO mytable (col1, col2, col3) VALUES (1, 2, 3);

Thus when you import it, it will only try to set values in three columns, even if the table now has four columns after your latest alteration. The fourth column not mentioned in the INSERT will be set to the column's DEFAULT, or else NULL.

If you don't use the --complete-insert option, then mysqldump outputs a statement like this:

INSERT INTO mytable VALUES (1, 2, 3);

This causes problems if the table gains a fourth column because of your alteration.

This solution should account for the case of adding columns. It will not account for other cases, like dropping columns or renaming columns.

If you want a more robust solution, I'd recommend checking out Skeema.

Upvotes: 2

Related Questions