How to Convert Mysql Database?

Question

In the development of a web portal, using the git we made the control of everything that was changed at code level in the approval environment and with the Jenkins We moved the site to the production environment. To migrate the bank was made a dump of the entire base that was placed in the other environment.

The problem lies in the next modifications that interfere with the structure, or the contents of the database.

With the site being used, new content is being added and I can no longer make a new copy of the approval environment without risk of losing information.

If all changes in the database were made manually I could generate a file .sql and run it in production when it was going up new changes.

But installing modules and components on the site generates bank changes that may not be so simple to manually control.

I wonder if there is a way to view the database as we use the git to restore the source code.

UPDATE

My main need is to find a way/method to replicate the changes made in the development environment, for the type approval and production environment.

Answer 1

In some projects I simply saved all scripts in text files in a separate project and then versiono in my IDE.

On the other hand, there are several frameworks that try to automate the task of "migrating" a database from one version to another and ensure consistency.

One of the ones I’ve been studying is flyway, although it has not yet managed to deploy in a project.

Answer 2

A major difficulty in working with databases is that changes come from two sides: data changes occurring in the production environment, and structure changes following the "normal" process (development, testing, quality, production - or whatever the progression of environments you/your business uses). Both have to occur in harmony.

Data Changes

There is little reason to change the data, but if necessary it can be done simply by making a dump or backup of the same, and versioning these copies the way you think best (maybe even git itself). It is important, of course, that when restoring these copies the structure of the bank is as it was when the copy was made, but this becomes simple when keeping a record of the structure changes in the BD itself (more on this later).

Changes in Structure

Irreversibility

Whatever method is used to change the BD structure (execution of an sql script, installation of external components), this change can only be made once, and it cannot be undone. Of course, you can run the same script twice and the end result is the same, or you can run a script that brings the BD to an identical state, but still it counts as two changes (especially if something goes wrong).

Given the irreversibility of structural changes, it is important to keep a record of which changes have already been applied to a specific BD, and if a given change has already been made, not to make it again. One way is to keep in the BD itself a table listing which changes were made, and in which order.

Testing, testing, testing, testing...

If a change made, say, in the development environment has failed in the testing environment, you should not simply change the testing environment and move on. On the contrary, both environments should be restored to the pre-change state (although this means destroying the BD and restoring it from a backup) and create a new change to replace the one that did not work (discarding it). If this time it worked in tests but failed in quality, it starts all over again...

This ensures that once the changes are ready to be applied to the production environment, they have already been tested multiple times in different circumstances, with different pre-existing data sets.

(and, responding directly to your particular case, I do not think it is a good idea to copy the status of the homologation environment for production - a conclusion you yourself have also drawn; the ideal would be to apply the same actions - script execution, component installation - performed in an environment in the next environment in the same order, naturally preceded by a complete backup of the bank)

Data Migration

Often data needs to be adjusted as a result of a change - setting default values for a newly created column, moving data and a table to another, converting from one format to another, etc. As this is something that affects all BD data - whatever state they’re in - and it’s only done once, you can treat that kind of change as a normal structure change - by versioning it and testing it from environment to environment.

This can be done in a single sql script or depending on the particularities of your tool (the Django-South for example differentiate between schema migration and data migration), in multiple steps. Example:

1. Create a new column nullable, and mark an existing one also as nullable;
2. Move all data from the old column to the new one, converting the format if necessary;
   • Optional: test your application layer in this state;
3. Mark new column as no-nullable, and destroy the ancient column.

Restoring to a Previous State

Finally, if for any reason it is necessary to restore the archived data in the past, all you need to do is:

1. Verify in your copy what was the state of the bank at the time it was made (simple, because the set of changes that the BD suffered were registered in a table, and this was saved along with the other data);
2. Check whether or not the current database is in the same state (i.e. same set of changes, same order); if yes, simply restore the data.
3. If the bank is in a state previous, just perform the pending changes on it until it reaches the appropriate state to restore the copy.
4. If, on the other hand, he’s in a state posterior, then it is necessary to put this data in a new provisional database, apply the necessary changes to bring it to the desired state, and then transfer the data to the definitive database.

Needless to say, the application code to be used should be in the same version which supports the bank in its desired state. Assuming you have saved the change scripts in version control, this consists of checking which latest stable version contains that set of changes, and no newer.

Note: this response was partly based on the article (in English) "Database Changes Done Right" and how the schematic migration tool works Django-South, beyond my personal experience.

Answer 3

I suggest using Liquibase (http://www.liquibase.org/).

He organizes the entire evolution of the database. For example.

When I create a table I put the creation . sql in the base and run it. With this it will run the . sql and will create versioning methods (creating two tables) in the database.

Now let’s imagine that I need to do a table update and my database is already in production. I’ll create one. sql with update SQL and add to the file. liquibase will run, itself will recognize that the first sql script (table creation) has already been run and will run the second script (update).

If the Database is deleted, when running the base it will execute (in the configured order) the sql scripts configured in it.

I find a great tool, easy to use and can be used in production without any problem.

Answer 4

The ideal is to configure your server to generate a DUMP of the database as well as your data in a text file, and perform the versioning through this.

Versioning of the database itself is not possible.

And another, the database is always changing right? So in your case it should be a versioning only of the right database structure? To generate a dump without the data one should do so:

mysqldump -h host -u user --no-data --database nomedobanco > arquivo.sql -psenha

Answer 5

I am using Mysql Workbench and Laravel 4.

For each change in the database, I get five new files in the default directory app/database/migrations of Laravel.

Everything starts using the command line:

php artisan migration:make tabela_faturas

This causes Laravel to create a PHP script in app/database/migrations, which receives a standard name, containing date and time.

Then I copy the latest Mysql Workbench file ("mwb" extension) and put the same name as the automatically generated PHP file (only the extension is different). I apply the changes in the schema, and with the synchronization tool, I use the SQL file that applies the changes in the schema. Saved again with the same name, only with the extension "sql". And, to complete, I give a "png" image of the diagram, and give the same name.

Finally, I reopen the previous "mwb" file, and again using the synchronization tool, Gero the SQL that reverses the changes, and saved with the suffix "_revert".

That is, for each new version of the database I get FIVE new files, in the directory app/database/migrations. Here’s a real example:

jbruni@jbruni-laptop:~/pdm/dev/app/database/migrations$ ls -1

2013_09_29_045622_create_schema.mwb
2013_09_29_045622_create_schema.php
2013_09_29_045622_create_schema.png
2013_09_29_045622_create_schema_revert.sql
2013_09_29_045622_create_schema.sql

2013_10_08_015925_create_extra_table.mwb
2013_10_08_015925_create_extra_table.php
2013_10_08_015925_create_extra_table.png
2013_10_08_015925_create_extra_table_revert.sql
2013_10_08_015925_create_extra_table.sql

2013_10_21_185836_faturas_and_other_upgrades.mwb
2013_10_21_185836_faturas_and_other_upgrades.php
2013_10_21_185836_faturas_and_other_upgrades.png
2013_10_21_185836_faturas_and_other_upgrades_revert.sql
2013_10_21_185836_faturas_and_other_upgrades.sql

2013_10_30_095146_enhanced_business_model.mwb
2013_10_30_095146_enhanced_business_model.php
2013_10_30_095146_enhanced_business_model.png
2013_10_30_095146_enhanced_business_model_revert.sql
2013_10_30_095146_enhanced_business_model.sql

Finally, the content of PHP files is always the same. I just need to take care of KEEP the class name that Laravel also generates automatically:

<?php

use Illuminate\Database\Migrations\Migration;

class EnhancedBusinessModel extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $vals = Config::get('database.connections.mysql');
        $script_path = str_replace('php', 'sql', __FILE__);
        $command = "mysql -u{$vals['username']} -p{$vals['password']} -h {$vals['host']} -D {$vals['database']} < {$script_path}";
        shell_exec($command);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $vals = Config::get('database.connections.mysql');
        $script_path = str_replace('.php', '_revert.sql', __FILE__);
        $command = "mysql -u{$vals['username']} -p{$vals['password']} -h {$vals['host']} -D {$vals['database']} < {$script_path}";
        shell_exec($command);
    }

}

This leaves everything well documented, organized, and within the ability to go back and forth through Laravel’s "Migrations".

The archives enter a commit from the project’s Git repository, naturally...

---

Besides, I always do the following too:

1. I leave a daily cron job to back up the database (via mysqldump to a different server)

2. I leave the Binary log activated, in case it is necessary to restore the database for an exact moment in time. (I already had to do this once, and thanks to Binary log + Daily backup, it was a success.)

Answer 6

Your question leaves open two situations:

1. Versioning the data structure of your system during the development process.

2. And routine backups of data stored by your system.

For situation 1 it is so you will see the DUMP of the useful data structure in that version of the system, as you do with the system sources.

For situation 2, you need to define a data backup routine, that go beyond the time, there are several techniques, be it a script to dump the data, replication technique and even backups servers (2 instances of database on different servers).

They are two totally different moments and deserve different solutions, one thing is to protect and another thing is to have the backup. And for the explanation of your question, you need to define a backup/Recovery routine for your data and this should not be versioned along with your system code but in another versioning structure if you want to use this technique.

Answer 7

I recommend another approach.

After you take a full backup, you can collect the differences in binlogs The way to do incremental backups of your Mysql Server

I recommend reading (in English) http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html