creating scripts for updating production database

Question

I am searching for best practices regarding updating production database. 90% of updates are adding new columns, new tables, adding missing indexes. Sometimes, I also need to update data that is needed for the app to work (update statements or modify). I haven't come across deletion yet.

My current development setup is to create a mysqldump, rsync it to server and import it in database. However, this approach falls short once there is any user data present in the production database. I've searched SO and the suggested approach is to script any change you make to database

I am using graphical editor (Sequel Pro) to make changes so out of the box, I don't have the code for all the changes. How do you create the scrips that contains all the changes? Managing all the changes by hand is very error prone. Ideally I would compare 2 mysqldumps and generate the report of all changes.

Answer 1

To create scripts to make database changes, you'll need to install a tool called an editor. Here are some examples: MySQL Workbench, HeidiSQL, Toad for MySQL, dbForge Studio for MySQL. For other examples, just do an online search for "mysql editor". Some of these tools are free to use, others have a cost. Their purpose is to enable users to write SQL code and then execute that code against a database.

As for how to create scripts - you'll need to learn how to write SQL statements manually. This is a tremendously useful skill to develop. Once you know how to do this you'll have a very good understanding of how the database works and it's fun too!

Here is an example of a change script which contains some basic examples of MySQL statements that would do some of what you described needing to do in your post:

/*
filename: 2019-03-09_ScriptExample.sql
purpose:  demonstrate changing a database by using a script
author:   knot22
created:  03/09/2019
updated:  03/09/2019
*/

--create a new table
CREATE TABLE customer
(
  customer_id INT PRIMARY KEY,
  first_name CHAR(35),
  last_name CHAR(35),
  credit_limit DECIMAL(8,2),
  sales_representative_id INT
);

--add some data to table
INSERT INTO customer(customer_id, first_name, last_name, credit_limit, sales_representative_id)
VALUES
(1, 'Charlie', 'Brown', 1000.00, 14),
(2, 'Mickey', 'Mouse', 500.00, 14),
(3, 'Tweety', 'Bird', 750.00, 21);

--add a column to an existing table
ALTER TABLE customer
ADD COLUMN phone_number VARCHAR(15) AFTER last_name;

--update a row to add a customer's phone number
UPDATE customer
SET phone_number = '123-555-0001'
WHERE customer_id = 2;

You can also run queries to look at the data in tables. For instance -

--query table to have a look at the data in it
SELECT * FROM customer;

Here is a link to one of may sites out there to help you get started writing SQL: http://www.mysqltutorial.org/basic-mysql-tutorial.aspx

It is possible to execute just portions of a script. That is useful when writing it. In most SQL editors, you can highlight a block of code and click execute and it will only run the statements that are highlighted against the database. When an entire script is run the SQL statements are executed in the order that they're written in the script which is usually important if multiple changes need to be made.

Scripts can be saved as files with a .sql file extension. They can be viewed/modified/executed in SQL editors and viewed/modified in text editors. I like saving all of the change scripts because they serve as a record of what got changed. Adding comments in the script about when it was executed in Dev, Test, Prod environments can be useful too.

The process I use for making a database change is as follows. First, create the script in my Dev environment and try it out there until it works the way it's expected to work. Next, restore the Prod database to my Test environment and execute the script in Test. That is a simulation of what is going to happen in Prod. Once everything works as expected there, finally I execute the script in Prod.