Thursday, June 1, 2017

MySQL Shell - Easy scripting

With the introduction of MySQL InnoDB Cluster we also got the MySQL Shell (mysqlsh) interface. The shell offers scripting in Javascript (default), SQL or Python. This offers a lot more options for writing scripts on MySQL, for example it is much easier now to use multiple server connections in a single script.
A customer recently asked for a way to compare the transaction sets between servers. That is useful when setting up replication or identifying the server that has most transactions applied already. So I wrote this little script which can be executed from the OS shell:

 #!/usr/bin/mysqlsh -f  
 // it is important to connect to the X protocol port,  
 // usually it is the traditional port + "0"  
 //  
 var serverA="root:root@localhost:40010"  
 var serverB="root:root@localhost:50010"  
 shell.connect(serverA)  
 var gtidA=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 shell.connect(serverB)  
 var gtidB=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 //  
 // If you want to use pure XdevAPI the former statements should be  
 //  
 // gtid = session.getSchema("performance_schema").global_variables.select("VARIABLE_VALUE").where("VARIABLE_NAME='gtid_executed'").execute().fetchOne()[0]  
 //  
 println(" ")  
 println ("Transactions that exist only on "+serverA)  
 println (session.sql("SELECT gtid_subtract('"+gtidA+"','"+gtidB+"')").execute().fetchOne()[0])  
 println(" ")  
 println ("Transactions that exist only on "+serverB)  
 println (session.sql("SELECT gtid_subtract('"+gtidB+"','"+gtidA+"')").execute().fetchOne()[0])