I’ve been working with MySQL databases years. Since most of my projects have been fairly small, I’ve managed backups and schema updates manually using phpMyAdmin. Typically I will setup 3 separate server environments and databases: dev, test, and live.
User Scenarios
The scenarios that I would like to implement are:
- Create ‘dev’ database, tables, and optionally initial content.
- Optionally import existing content from ‘live’ to ‘dev’.
- Develop and test code using the ‘dev’ environment and database.
- Copy the ‘dev’ database to the ‘test’ database
- Optionally import existing content from ‘live’ to ‘test’
- Test using the ‘test’ environment
- Lock the ‘live’ database
- Backup the ‘live’ database
- Copy the ‘test’ database to the ‘live’ database
- Restore the ‘live’ content
- Unlock the ‘live’ database
MySQL supports doing all of these things.
Creating the Database and (optionally) Initial Content
Creating the database is done using CREATE TABLE. Using a PHP function to do this works well because then the structure of the table is documented and can be checked into the SCM.
Importing Existing Content
Development on the ‘dev’ site is easier if the ‘dev’ database contains valid data. If the ‘live’ and ‘dev’ databases reside on the same server then the content from the ‘live’ database can easily be copied into the ‘dev’ database using ‘INSERT INTO dbname.tablename (cols) SELECT cols FROM dbname.tablename’. If not, then the ‘live’ database will need to be unloaded to a file, then the file loaded into the ‘dev’ database as described for backup below.
Copying the Dev Database to Test
Once new code is developed and tested in the ‘dev’ environment it needs to be copied as-is to the ‘test’ environment. This is easily done using ‘CREATE TABLE dbname.tablename AS (SELECT * FROM dbname.tablename)’. Updated content can be copied from ‘live’ to ‘test’ as described above for copying data into ‘dev’.
Lock the Live Database
Although MySQL provides a locking mechanism, this is probably not the best approach in this case. A better approach might be to set a flag in the user login or identification code. I call this setting a ‘service mode’. Other users that try to interact with the site will be told that the site is down for service, and to try again later.
Backup the Live Database Content
MySQL provides a BACKUP and RESTORE commands, but these are marked as ‘deprecated’. Since the structure of the database is recorded in the code which creates the database, only the content needs to be backed up. This can be done using what MySQL calls ‘unloading’ and ‘loading’: “SELECT * FROM table INTO OUTFILE filename FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ” and “LOAD DATA INFILE ‘filename’ INTO TABLE table FIELDS TERMINATED BY ‘,'”
Recreate the Live Database
Use the same code that was used to create the ‘dev’ tables to recreate the ‘live’ tables.
Restore the Live Database Content
As stated above, MySQL provides a RESTORE command but it is marked as ‘deprecated’. Since the structure of the database was recreated in the previous step, load the content using “LOAD DATA INFILE ‘filename’ INTO TABLE table FIELDS TERMINATED BY ‘,'”
Unlock the Live Database
This is done by turning off ‘service mode’.