Any tool that can help compare 2 memsql database schemas and create the migration script

3rd-Party Integrations
#1

To compare two oracle schemas and produce a migration script, we could use a tool called dbForge Schema Compare.

Do we have a similar tool that can compare two different Memsql database schemas and generate a migration script that can be applied to the Target schema.

#3

I don’t know of a tool that can do that out of the box for SingleStore, handling all our table types, indexes, and sharding. There’s a related discussion on this post, including how to do some schema comparison yourself.

1 Like
#4

I posted this in the other thread too, but thought will post it here and it may help someone. The below tool not only finds all of the differences but produces the exact ALTER / CREATE syntax that is used by Singlestore, including the sharding, indexes, table types.

The best tool I found for this, believe it or not, is the perl mysqldiff utility (part of MySQL::Diff module). This is not to be confused with a similarly named MySQL utility. I couldn’t really find anything else that does a good job.

Check this out:

(python) [ec2-user@prod-jump-1_10-1-1-5 db]$ mysqldiff --keep-old-tables -h memsql-prod -u proof -p $PASSWORD_DB_PT trading_hist trading
## mysqldiff 0.60
##
## Run on Tue Apr  6 17:47:13 2021
## Options: user=proof_post_trade, debug=0, keep-old-tables, host=memsql-prod.prooftrading.com
##
## ---   db: trading_hist ('host=memsql-prod' 'user=proof')
## +++   db: trading ('host=memsql-prod' 'user=proof')

ALTER TABLE Venue ADD COLUMN auctionCollarBps smallint(6) DEFAULT NULL;

ALTER TABLE ClientConnection ADD COLUMN senderSubId varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE ClientConnection ADD COLUMN targetSubId varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

CREATE TABLE KillSwitch (
                            sbeClassName varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                            sbeVersion int(11) DEFAULT NULL,
                            sessionId smallint(6) DEFAULT NULL,
                            sequenceTime timestamp(6) NULL DEFAULT NULL,
                            sequenceTime_nanos bigint(20) DEFAULT NULL,
                            sequence bigint(20) DEFAULT NULL,
                            sourceType smallint(6) DEFAULT NULL,
                            sourceInstance smallint(6) DEFAULT NULL,
                            sourceId int(11) DEFAULT NULL,
                            sourceSequence bigint(20) DEFAULT NULL,
                            active varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                            checkString varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                            note varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                            KEY sequenceTime_nanos (sequenceTime_nanos) USING CLUSTERED COLUMNSTORE
    , SHARD KEY ()
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES';
Which Tool to use for generating a Schema Diff?
#5

Thank you for sharing this!

#6

I am trying to use this tool but very little documentation, so struggling there.
I have set up vagrant on my local and i am trying to connect to a remote server that has 2 DB s and trying to compare them but got this error. Do we need to have mysql server installed on memsql node? or Any suggestion for the following error?

vagrant@vagrant-ubuntu-trusty-64:~$ mysqldiff --server1=rnq1_dw:password1@msql-xxy-81.real.com:3306 --server2=rnq5_dw:password@msql-xyx-82.real.com:3306 q1_dw:q2_dw

server1 on msql-xxy-81.real.com: … connected.

server2 on msql-xyx-82.real.com: … connected.

ERROR: Query failed. 1049 (42000): Unknown database ‘mysql’

#7

That’s the wrong tool. Like I said in my post, this is a perl utility, not the one that comes with MySQL.

Since you’re using ubuntu, you should be able to install this tool using instructions here:

sudo apt update
sudo apt install libmysql-diff-perl

Then the utility can be invoked as: mysql-schema-diff [...]

#8

image
image1009×95 7.42 KB

I have executed the same command for two schemas on same host ,
but it is running for a long time
Do you have an idea regarding time based on volumes ?
What time it takes

#9

Hi, I don’t think the time depends on the size of the data. You can try adding --debug=4 to the command to see what it might be doing.

#10

Thank you, command works but it takes more than 50 mins to execute.

1.Could you please provide me command for diff between two schemas having different host,
Like you posted example for two schemas on same host.?
2.Does it always required root user to execute?

#11

Here is the mysqldiff manual: https://metacpan.org/dist/MySQL-Diff/view/bin/mysqldiff

No, it doesn’t require root on the server or on the DB. For two DBs, the command should be something like this this:

mysqldiff --host1=<hostname> --user1=<user> --password1=<password> --host2=<hostname> --user2=<user> --password2=<password> db1 db2
  1. Replace arguments in <...> above like <hostname> with your actual values
  2. db1 should be the name of the db on the first server, and db2 should be the name of the db on the second server.
1 Like
#13

Facing below issue on Centos

Can’t exec “mysqlshow”: No such file or directory at /usr/lib64/perl5/IO/File.pm line 187.
[vagrant@vm1 bin]$
[vagrant@vm1 bin]$

#14

On Centos, mysqlshow should be available using the mariadb package.

Here’s the shell script I use to get mysqldiff working on a Centos server (put these into a script and run with sudo):

#!/bin/sh

# this is what is needed to get MySQL::Diff installed
yum -y install perl-CPAN
yum -y install mariadb
yum -y install mariadb-devel
yum -y install gcc
echo | cpan  # configures cpan for first use
cpan -i MySQL::Diff