Found out that from MySql version 5.7 show grants nolonger exports passwords. Now there is a show create user.

LikeLike

After having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after having illustrated how to use Pacemaker, this article explains how to setup HA for MySQL Router using keepalived.

Keepalived is very popular, maybe because it’s also very easy to use. We can of course use 2 or more servers. The principle is the same as on the previous articles, if the router dies, the virtual IP used by the application server(s) to connect to MySQL is sent to another machine where mysqlrouter is still running.

Let’s have a look at the configuration, in this case we use 2 machines, mysql1 and …

This article will explain how to setup HA for MySQL Router as described in a previous article about where should the router stand.

For this setup, I will use Pacemaker (part of RedHat High Availability Add-on and available on RHEL, CentOS, Oracle Linux, …).

Of course we need a MySQL InnoDB Cluster but we won’t really use it for the HA setup of the MySQL Router.

Installing Pacemaker

The first step is to install pacemaker on all the machines we will use for our “MySQL Router Cluster”:

# yum install pacemaker pcs resource-agents

Now we need to start the pcsd service and enable it at boot (on all machines):

# systemctl start pcsd.service 
# systemctl enable pcsd.service

It’s time now to setup authentication, this operation is again …

As you know, MySQL InnoDB Cluster is composed of 3 elements:

• a group replication cluster of at least 3 servers
• the MySQL Shell used to manage the cluster
• the MySQL Router that send the traffic from the application server(s) to the cluster

When presenting the solution in conferences, one the main question is Where should I put the router ? and the answer is always the same: the best place to install the router is the application server !

The router is a very lightweight process that gets its configuration from the cluster’s metadata and doesn’t require a lot of resources or maintenance.

So the ideal setup is the following:

However for many (obscure?) reasons, sometimes people doesn’t want to have the MySQL …

My output of pt-show-grants –drop is

DROP USER ‘kjeld’@’localhost’;
DELETE FROM `mysql`.`user` WHERE `User`=’kjeld’ AND `Host`=’localhost’;
— Grants for ‘kjeld’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘kjeld’@’localhost’ WITH GRANT OPTION;

There is no statement like this:
CREATE USER IF NOT EXISTS ‘check’@’%’;
ALTER USER ‘check’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*B865CAE8F340F6CE1485A06F4492BB49718DF’ REQUIRE NONE PASSWORD EXPIRE

LikeLike

create table csv_date (date varchar(20)); insert into csv_date values ('2030-05-18 14:57:19'); insert into csv_date values ('2030-05-18 14:55:15'); insert into csv_date values ('2019-05-18 04:15:15'); insert into csv_date values ('2018-05-18 02:11:53'); insert into csv_date values ('2017-05-18 22:14:24'); Convert two digit to four digit in MySQL: Create a temporary table same as the original table but make the column …

The post How To Convert MySQL Two Digit Year To Four Digit Year appeared first on SQLgossip.

MariaDB Connector/Node.js First Alpha Now Available diego Dupin Fri, 07/20/2018 - 09:33

MariaDB is pleased to announce the immediate availability of MariaDB Connector/Node.js alpha version 0.7.0. This is a non-blocking MariaDB client for Node.js, 100 percent JavaScript, compatible with Node.js 6+.

Why a new client? While there are existing clients that work with MariaDB, (such as the mysql and mysql2 clients), the MariaDB Node.js Connector offers new functionality, like insert Streaming and Pipelining while making no compromises on performance.

Insert Streaming

Using a Readable stream in your application, you can …

Welcome to the third part of this series. I’m glad you’re still reading, as hopefully this means you find this subject interesting at least. Previously we presented the first two components of MySQL InnoDB Cluster: Group Replication and MySQL Router and now we will discuss the last component, MySQL Shell.

MySQL Shell

This is the last component in the cluster and I love it. Oracle have created this tool to centralize cluster management, providing a friendly, command-line based user interface.

The tool can be defined as an advanced MySQL shell, which is much more powerful than the well known MySQL client. With the capacity to work …

In this blog post, we talk about the basic function and features of the Tungsten Connector.

The Tungsten Connector is an intelligent MySQL proxy that provides key high-availability and read-scaling features. This includes the ability to route MySQL queries by inspecting them in-flight.

The most important function of the Connector is failover handling. When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node.

Next is the ability to route MySQL queries based on various factors. In the default Bridge mode, traffic is routed at the TCP layer, and read-only queries must be directed to a different port (normally 3306 for writes and 3307 for reads).

There are additional modes, Proxy/Direct and …

In a previous post, we discussed how you can take control of the failover process in ClusterControl by utilizing whitelists and blacklists. In this post, we are going to discuss a similar concept. But this time we will focus on integrations with external scripts and applications through numerous hooks made available by ClusterControl.

Infrastructure environments can be built in different ways, as oftentimes there are many options to choose from for a given piece of the puzzle. How do we define which database node to write to? Do you use virtual IP? Do you use some sort of service discovery? Maybe you go with DNS entries and change the A records when needed? What about the proxy layer? Do you rely on ‘read_only’ value for your proxies to decide on the writer, or maybe you make the required changes directly in the configuration of …