Home / Questions / 79216
Accepted
ArcanisGK507
Asked: 2020-06-17 07:12:05 +0800 CST 2020-06-17 07:12:05 +0800 CST

Methodology to update MySQL DB according to changes in its DDL structure

  • 772

How can I update a database with the following scenario?

I have a database in a test environment (server1) and I must update it with the one in the development environment (server2), but I only want to make the changes in the structure, without deleting the data already stored, I have already checked that:

I will not update the structure of existing columns, I will only add new ones, and the data that does not exist will be placed with a defaultor put as null.

So, how to make the comparison of the development server and get the differences with respect to the production server and bring as a result the changes that I must make in the production server?

mysql

3 Answers

  1. Best Answer

    If you are not working with Workbench I would recommend using it, it has a functionality that is to synchronize models.

    1. Reverse engineering

    It is necessary to first reverse engineer your development database, which using the tool is super simple. This retrieves your database by design.

    Design can be stored in a file

    enter image description here

    2. Model synchronization

    Then you can connect to your server from Workbench and perform the synchronization.

    enter image description here

    Later the program will show you the scripts that result in differences between your model and the production database.

    enter image description here

    • 6

  2. There is a set of tools called MySQL Utilities, within which we can find mysqldiff.

    This utility reads the object definitions and compares them using a method diff-liketo determine if they are the same. The utility displays differences for objects that are not the same.

    Assume the following object definitions:

    prod_host :

    CREATE TABLE prod_db.table (num int, misc char(30));

    dev_host

    CREATE TABLE dev_db.table (num int, notes char(30), misc char(55));

    To generate a set of statements SQLthat transform the definition of prod_db.tableinto dev_db.table, use this command:

    shell> mysqldiff --server1=root:pass@prod_host --server2=root:pass@dev_host --changes-for=server1 --difftype=sql  prod_db.table:dev_db.table

# server1 on prod_host: ... connected.
# server2 on dev_host: ... connected.
# Comparing prod_db.table to dev_db.table                         [FAIL]
# Transformation statements:

ALTER TABLE prod_db.table
  ADD COLUMN notes char(30) AFTER a,
  CHANGE COLUMN misc misc char(55);

  Compare failed. One or more differences found.

    --

    A common error when trying to run this command against a remote shared or dedicated server is the following:

    ERROR: Query failed. 1142 (42000): SELECT command denied to user 'USER'@'IP' for table 'proc'

    This is because you are mysqldifftrying to run SELECT * FROM mysql.proc;, and this command is blocked for all users except the account root. In addition, it is necessary to validate that the user roothas privileges to access remotely (eg: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD ...).

    • 3

  3. If you work with Symfony + doctrine there is a bundle called doctrinemigrationsbundle t. This has a Diff command that obtains the differences between the database and the model you have defined, creating a PHP migration file.

    In general, if you use an orm in your application to define the schema, there is usually a library that differentiates the schema depending on the environment you use (dev, prod, test...)

    • 1