Why else would I write a blog post if everything works out-of-the-box? The connection failed, as you might have expected. After we add the details, we can use the “Test connection” button at the bottom of the page. Now it’s time to kick off the MySQL Workbench client and create a connection for the app_rw user to the port off the proxysql1 instance. | username | default_hostgroup | frontend | backend |Īdmin> SELECT hostname, hostgroup_id, comment FROM runtime_mysql_servers Īll of this runs in the local lab environment on a machine that is reachable on the local IP address 192.168.56.2. Type '\c' to clear the current input statement.Īdmin> SELECT username, default_hostgroup, frontend, backend FROM runtime_mysql_users ORDER BY username Server version: 5.5.30 (ProxySQL Admin Module)Ĭopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Here are configuration details for the ProxySQL instances: $ docker exec -ti proxysql1 mysql In this demo environment, we’ll be splitting reads and writes from the application side by using different usernames and passwords to connect to MySQL (through ProxySQL). The version of ProxySQL makes no difference for this post so I decided not to touch my running system. Here are details for the ProxySQL containers: $ docker ps | grep proxysqlĪa11e92b4a97 centos:7 "/bin/bash" About an hour ago Up About an hour 0.0.0.0:33062->6033/tcp proxysql2ī775502ac18e centos:7 "/bin/bash" About an hour ago Up About an hour 0.0.0.0:33061->6033/tcp proxysql1Īs you can see in the Docker output, ProxySQL is listening on it’s default client port 6033 and we exposed that port on ports 33061 for proxysql2 for proxysql2.Ĭurrently this lab is still running ProxySQL 1.4.15, but I’ve tested this on ProxySQL 2.0 instances before. All of these instances are running in Docker containers and they’re exposing different ports depending on their role. In front of these MySQL instances are two ProxySQL nodes (proxysql1 and proxysql2). This is Orchestrator’s view of the current situation: One writable master, two read-only replicas, and automatic failovers enabled. We have three MySQL servers, all running 5.7.25 in a pretty standard replication topology. To start off, I’ll describe the demo lab environment.
Introduction to the Cluster Configuration
If you want to know more about this kind of setup, check out my slidedecks published on speakerdeck. This is a common setup with many of our customers and it’s something that I’ve frequently spoken about in conferences around the world. In this post, we’ll be using MySQL Workbench 8.0.18 to connect to a MySQL cluster which is configured with a ProxySQL server in front of it and managed by Orchestrator for high availability. In a simple environment where you can connect directly to the database it works very intuitively, but in more complex environments it might be a little less straightforward. MySQL Workbench is a popular tool for many developers working with a MySQL backend database.
0 kommentar(er)
