MySQL InnoDB Cluster setup

Hey folks, today I would like to show how easy is to do a HA cluster with MySQL InnoDB Cluster.

First a few things we must know: You’ll need at least 3 servers (for database) to do the job. We’ll need MySQL Shell on every server. Every node needs a two-way connection with each node (he need to be able to connect to the other node, and other nodes needs to be able to connect to the one). You will need a fourth server to work as a router. I’ll assume you already have a well configured MySQL Server on all three servers. Today I’ll be using version 8.

To be easier, every server will have an entry to the others on the hosts file. So let’s begin creating a common user on every server and give privileges enough to do the JOB. Be aware that I’m no focusing on security issues, like allowing the user to access the database from any IP, the best solution would be set the servers IPs.

CREATE USER 'syncron'@'%' IDENTIFIED BY 'Abacaxi22';
GRANT ALL PRIVILEGES ON *.* TO 'syncron'@'%' WITH GRANT OPTION;

1) Creating the cluster

Now let’s fire MySQL Shell on every node and prepare the server to join/create a InnoDB Cluster. If you never used this Shell, give it a try, it’s an awesome tool and it’s really easy to use and learn. We’ll basically use the dba commando for all of our needs today. Run dba.configureInstance() on every node and then, on the “main” server run the following.

You can run the Shell with mysqlch --uri=syncron@mysql1:3306 and voila. You’re on the server 1.

cluster = dba.createCluster('tasks') --Cluster name, take note
dba.getCluster('tasks').status()

After each command, you should see the following results. Look that I’m actually running the command on a server named mysql1.

Look at the status result, and see that after creating the cluster, we already have a node running and he’s a R/W node. And look at the ‘statusText’.. The cluster still not fault tolerant.

2) Adding a node to the cluster

Now let’s connect to the second server. On the second server, instead of connecting to itself, we’ll connect to the mysql1 server, so use the same command as before to spin the Shell. After that, let’s add the second instance to the cluster.

cluster = dba.getCluster('tasks') -- the cluster name, remember?
cluster.addInstance('syncron@mysql2:3306') -- now we use the second server uri

IF everything works as expected you’ll see the following as a result.

You can run the status on the cluster again to see how it’s now. But it still not fault tolerant. Do the same job on the third server (mysql3 on my environment) and THEN you can get the status and see the magic (or at least the status page of it).

Be aware that, the server now IS fault tolerant BUT, can tolerate just one. But take a closer look and see that we have just one node with R/W permission, the other two are R/O… and that’s how it works (in this case at least). How we handle the connections now that we have three different servers running? Easy… use the Mysql Router… Now to the fourth server.

3) The Router

That’s the easy step… just run .\mysqlrouter.exe --bootstrap syncron@mysql1:3306 and you’ll get the config file generated. That’s just the default configuration, take a look at the manual to achieve better results. The router will give you two port to handle the connections, one with R/W permission and the other one with R/O. It’ll handle the “reconnect” for your application if needed.

Easy huh? Go ahead and shut down one instance and look at the cluster status again. Now two extra tips..

  • If you get the following when trying to configure or join a cluster, you need to check the grant of the user, here we used the most of it, but you can manage the permissions to achieve a better (and more secure) login.

Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation (MySQL Error 3630)
  • If you plan to upgrade your server, do it with caution. You MUST do the R/W node at the END. Before shut down the server run set persist group_replication_start_on_boot=0; and set to true again after the upgrade and then restart it again.

Cheers!

MySQL: Sed for Scripts using “Show” from Command Line

Hi all,
So, a pretty basics one today… But useful to have handy. How to script an output from  mysql -B -e?

Easy, by using SED. Ok, by replacements are always tricky considering the line braking and etc. So, here goes an example with show tables:

Original Output.

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'
Tables_in_sbtest
sbtest1
sbtest10
sbtest2
sbtest3
sbtest4
sbtest5
sbtest6
sbtest7
sbtest8
sbtest9
Cool, now with all in one line:
[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ /g'
Tables_in_sbtest sbtest1 sbtest10 sbtest2 sbtest3 sbtest4 sbtest5 sbtest6 sbtest7 sbtest8 sbtest9

Great, so let’s put some useful code on it:

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ engine=innodb; \n alter table /g'
Tables_in_sbtest engine=innodb;
alter table sbtest1 engine=innodb;
alter table sbtest10 engine=innodb;
alter table sbtest2 engine=innodb;
alter table sbtest3 engine=innodb;
alter table sbtest4 engine=innodb;
alter table sbtest5 engine=innodb;
alter table sbtest6 engine=innodb;
alter table sbtest7 engine=innodb;
alter table sbtest8 engine=innodb;
alter table sbtest9 engine=innodb;

Hope you can use for your needs.

Cheers!

MySQL: ERROR 1356 (HY000): View ‘sys.innodb_lock_waits’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Hello all!
So, I was messing around with a Dev environment to simulate some strategies, doing some tests, and after a mysqldump exporting, dropping, and reimporting my whole database got this error.

If you arrived here from Google, Is this your case?

Well, this happens for a simple reason. The routines are not exported by mysqldump by default. Why? I don’t know either, this is an abomination to me. This would be very cheap to be the default right?

Happens that even for a new database, when importing a dump generated with –all-databases the sys/information_schema routines are deleted by the restore process. Well, at least this is recognized as a Bug (Bug 83259).

I noticed that when trying to query a sys view to get locks info, as per:

root@localhost-(none)-13:46:26>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id,
-> blocking_pid, blocking_query FROM sys.innodb_lock_waits;
ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Hm.. Weird message, let’s check for information_schema objects:

root@localhost-(none)-13:46:39>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

Ok, so, if you are already in this mess, how to quick recover?
Well, just run the mysql_upgrade (assuming you are in the top version/repository you have available on server):

root@localhost-(none)-13:47:34>exit
Bye
[root@greporasrv ~]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
mysqlslap.t1 OK
[... my other databases...]
sys.sys_config OK
world.city OK
world.country OK
world.countrylanguage OK
Upgrade process completed successfully.
Checking if update is needed.

Fine, lets test it:

[root@greporasrv ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost-(none)-13:49:44>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)

root@localhost-(none)-13:49:53>

Hope it helps.
Cheers!

Managing database changes with Sqitch

Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.

Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.

What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).

Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…

Create a folder, initialize GIT in it and initialize the Sqitch project.

mkdir awesomeapp
cd awesomeapp
git init .
sqitch init flipr --uri https://github.com/theory/sqitch-mysql-intro/ --engine mysql

Now you have a ready to go sqitch structure. Now let’s do some basic creates.

sqitch add createcustomeruser -n 'Creation of Customer table'

Add the create table script to the file inside the “deploy” dir.

CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);

Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.

sqitch deploy db:mysql://root@/awesomeapp
sqitch verify db:mysql://root@/awesomeapp
sqitch revert db:mysql://root@/awesomeapp

This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.

ERROR OGG-05290 The Oracle GoldenGate CDC cleanup job is not enabled for database Msql_DB

Hi.

When you try to start the Goldengate Extraction Process in MSQL server, and you receive the following error.

” ERROR OGG-05290 The Oracle GoldenGate CDC cleanup job is not enabled for database Msql_DB Create the Oracle GoldenGate CDC cleanup job prior to starting the capture process. “

   To create cleanUP job for Goldengate SQL Server, use the .bat script in the GOLDENGATE home directory.

Comand Sintax

ogg_cdc_cleanup_setup.bat createJob [goldengate user] [goldengate password] [database name] [database host] [instance]

Exemple

ogg_cdc_cleanup_setup.bat createJob GGATE welcome1 Msql_DB msql-db01.net dbo

 

In some cases, it may return an error, stating that the process already exists.

” Msg 50000, Level 16, State 1, Server msql-db01, Line 34 The specified @name (‘OracleGGCleanup_Msql_DB_Job’) already exists. “

In this case, you may drop and recreate the Job. just change “createJob” for “dropJob”

The following is the success message of job creation

” INFO OGG-05281 Current OGG cleanup Job Settings – Job Name: OracleGGCleanup_Msql_DB_Job, JobSchedRec: , JobSchedFreq: , DatabaseName: Msql_DB, Tranlogoption managecdccleanup: 1, threshold: 500, retention: 4.320. “

Hope this helps!

Oracle Heterogeneous Services for PostgreSQL (ODBC Driver): Error (HY000,NativeErr = 1)

Hi all!
Some time ago a client reported issues when integrating Oracle and PostgreSQL. The error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
No query has been executed with that handle;
Could not send Query(connection dead) {HY000,NativeErr = 1}

Braking down errors:
Oracle: Connection from ORACLE to a non-Oracle system returned this message
PostgreSQL: {HY000,NativeErr = 1} Could not send Query(connection dead)

Both are general errors:
– Oracle’s one is for any error returned by remote service, when using Oracle Heterogeneous Services (old Database Gateway).
– Postgre’s one is for connections ended.

I also found some other similar errors:
Oracle: ORA-28511: lost RPC connection to heterogeneous remote agent using SID
PostgreSQL: {08S01,NativeErr = 26} Error fetching next row

In summary, the root cause was a firewall configuration ending connection. But what I want to share with you the workaround. 🙂
You know that sometimes, due certain rules, firewall rules may require formal change requests and procedures, so what you can do on database side is basically disable parameter UseDeclareFetch (default is false) in ODBC descriptor, as per below.

Continue reading

Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…

Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online

I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…

Continue reading

MySQL Network Connections on ‘TIME_WAIT’

Hi all!
Recently I caught a bunch of connections in ‘TIME_WAIT’ on a MySQL Server through ‘netstat – antp 3306’…
After some time, we identified this was caused by the environment not using DNS, only fixed IPS (uuugh!)…

As you know, for security measures MySQL maintains a host cache for connections established. From MySQL docs:

“For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.”
9.12.6.2 DNS Lookup Optimization and the Host Cache

For this reason, there is a DNS ‘reverse’ lookup for each login was hanging this connections.

The solution?
Right way: Add an A type registry in DNS for the hosts. Use DNS!
Quick way: Add on /etc/hosts from database server the mapping for the connected hosts, avoiding the DNS Lookup.
Quicker way: Setting the skip-name-resolve variable at /etc/my.cnf. This variable avoids this behavior in database layer for new connections and solve the problem.

This is a good (portuguese) post about it: http://wagnerbianchi.com/blog/?p=831

See ya!
Matheus.