CODEWITHSUNDEEP
mysqlversion-controlsql-scripts
Jader Dias
Jader Dias

Reputation: 90573

How to put MySQL code into source control?

I know I can copy all my MySQL code manually to files and then put those files into source control. But is there any way to do this automatically?

I would like to do this to stored procedures, but also to table/event/trigger creation scripts.

Upvotes: 3

Views: 1676

Answers (4)

theking2
theking2

Reputation: 2852

PHP

If you are in a PHP environment this script will store the procedures and function in separate (PHP) files which can be use to generated them and stored in a repo.

<?php declare(strict_types=1);

// logon the db user, I'm using a mysqli version here
// PDO will need small adjustments.
require "../inc/config.inc.php";

function show_routine(string $type, string $name) {
  global $db;
  $query = "SHOW CREATE $type `$name`";
  $result = $db->query( $query );
  foreach( $result as $row ) {
    echo "<h1>$name</h1>";
    if( is_null($row["Create $type"]) ) {
      echo "<p>Not found</p>";
      continue;
    }
    $proc   = $row[$type];
    $source = $row["Create $type"];
    $source = preg_replace( "/(DEFINER=`\w*`@`\w*`)/", "/* $1 */", $source );
    echo "<pre>$source</pre>";
    $fh = fopen( "./$type/$name.sql", "w" );
    fwrite( $fh, "DROP $type IF EXISTS `$name`;" . PHP_EOL . PHP_EOL );
    fwrite( $fh, "DELIMITER $$" . PHP_EOL );
    fwrite( $fh, $source );
    fwrite( $fh, "$$" . PHP_EOL );
    fwrite( $fh, "DELIMITER ;" . PHP_EOL );
    fclose( $fh );
  }
}


foreach( $db->query("show procedure status where db = '<your db-scheme>'") as $row ) {
  $name = $row["Name"];
  show_routine("Procedure", $name);
}


foreach( $db->query("show function status where db = '<your-db-scheme>'") as $row ) {
  $name = $row["Name"];
  show_routine("Function", $name);
}

Make sure the db user has the proper rights.

Upvotes: 0

Sebastien Lorber
Sebastien Lorber

Reputation: 92210

Don't really understand what you'r trying to do.

Look at Liquibase, perhaps it will do what you need...

Upvotes: 1

Jader Dias
Jader Dias

Reputation: 90573

Based on Michal answer, the solution I am using so far is:

#!/bin/bash
BACKUP_PATH=/root/database_name
DATABASE=database_name
PASSWORD=Password
rm -f "$BACKUP_PATH/*.sql"
mysqldump -p$PASSWORD --routines --skip-dump-date --no-create-info --no-data --skip-opt $DATABASE > $BACKUP_PATH/$DATABASE.sql
mysqldump -p$PASSWORD --tab=$BACKUP_PATH --skip-dump-date --no-data --skip-opt $DATABASE
hg commit -Am "automatic commit" $BACKUP_PATH

Upvotes: 4

Michal Čihař
Michal Čihař

Reputation: 10091

You can create triggers on data change, which would store the change automatically to some source control. However there is no automatic way to track structure changes (tables, stored procedures and so on) this way. So probably the best way is to dump database and store these dumps in source control. You can do this periodically to automate the things.

Upvotes: 4

Related Questions