My scenario is the following: Right now I am using one big MySQL database with multiple tables to store user data. Many tables contain auto increment columns. I would like to split this into 2 or more databases. The distribution should be done by user_id and is determined (cannot be randomized). E.g. user 1 and 2 should be on database1, user 3 on database2, user 4 on database3. Since I don't want to change my whole frontend, I would like to still use one db adapter and kind of add a layer between the query generation (frontend) and the query execution (on the right database). This layer should distribute the queries to the right database based on the user_id. I have found MySQL Proxy which sounds exactly like what I need. Unfortunately, it's in alpha and not recommended to be used in a production environment. For php there is MySQL Native Driver Plugin API which sounds promising but then I need a layer that supports at least php *and* java. Is there any other way I can achieve my objectives? Thanks!

When developing data analytical applications, the workstation needs to use MySQL-Proxy (version 0.8) to connect to the MySQL data source. This method has been working fine for years, however, we recently noticed that the connection is no longer working. The MySQL's version is 5.7.43-47-log, however, we are not one hundred percent sure whether it was due to the version upgrade. The issue is blocking our development, so we highly appreciate any hints and suggestions.

I have a standard master-slave replication for MariaDB to sync one database (master) to multiple slaves. One of the slaves lives behind a proxy, so I need to proxy the replication request to the proxy and from the proxy to the master. Can I use the proxy_protocol_networks parameter to proxy the requests? Can I proxy replications requests? Example: - master: 192.168.1.1 - slave: 172.16.1.20
mysqld.cnf: proxy_protocol_networks= "172.16.1.1" - proxy: 172.16.1.1 slave ---> proxy ----> master

I have a system that end users will need to query but they are not guaranteed to be well versed in query writing. We wanted to ensure every SELECT statement ends with a LIMIT 100000. I have thought of some regex to do this and some tools. It seems the native Query Rewrite is a good option but we needed to use ProxySQL for other things, thus I wanted to see if anyone knows of a way to force this. It seems like the patter or regex would be something like: anything that starts with select needs to have a LIMIT on the final line followed by a space and int equal to or less than 100000 then maybe a semi colon. Has anyone had any luck doing this?

Is there any solution (say a proxy) that makes MySQL replication cluster behave like one database towards developers? Developers then won't need to worry about using master to write and slaves to read etc.?

One of our customers has an online-shop running xt-commerce 4.2. This version of the e-commerce-software has a very expensive query for the category-tree. The query adds about 3 seconds delay and is executed on every page request. Since the software is protected by IonCube I have no possibility to change the query made by the shop software. As I found out, the query is not satisfied from the built-in MySQL-Query-Cache. The contents of the resultset do not vary very often and could be cached without problems. ###What are the possibilities to cache this one specific query? **Idea 1:** Increase RAM for MySQL so that all tables and indexes fit in RAM. - **result:** Queries duration dropped to "only" 2.5 seconds. Unsatisfactory. **Idea 2:** Find out why the query is not satisfied from the query-cache and change MySQL-Settings to cache it. - **result:** Not found settings that change the behaviour (until now). For reference: here is the query. In the slow-log it is the same query every time (no changing typos). SELECT COUNT(parent.categories_id) AS level, c.*, cd.*, su.*, cl.link_url, group_permission.*, shop.* FROM xt_categories AS c CROSS JOIN xt_categories AS parent LEFT JOIN xt_categories_description cd ON c.categories_id = cd.categories_id AND cd.categories_store_id = '1' LEFT JOIN xt_seo_url su ON (c.categories_id = su.link_id AND su.link_type = '2' AND su.store_id = '1') LEFT JOIN xt_categories_custom_link_url cl ON (cl.categories_id = c.categories_id AND cl.store_id = '1') LEFT JOIN xt_categories_permission group_permission ON (group_permission.pid = c.categories_id AND group_permission.pgroup = 'group_permission_1') LEFT JOIN xt_categories_permission shop ON (shop.pid = c.categories_id AND shop.pgroup = 'shop_1') WHERE c.categories_status = '1' AND c.categories_left BETWEEN parent.categories_left AND parent.categories_right AND cd.language_code = 'de' AND cd.categories_store_id = '1' AND ((c.category_custom_link = 0 AND su.language_code = 'de' AND su.store_id = '1') OR (c.category_custom_link = 1 AND cl.language_code = 'de' AND cl.store_id = '1')) AND group_permission.permission IS NULL AND shop.permission IS NULL GROUP BY c.categories_id , c.categories_left , c.categories_right ORDER BY c.sort_order , c.categories_left , cd.categories_name **Idea 3:** Install MySQL Proxy and implement something like https://github.com/stephan-hof/mysql-proxy-in-memory-lru-cache/blob/master/in_memory_lru_cache.lua - **result:** Works well. Changed the lua-script to only cache the specific queries. Query-times are now equal to queries satisfied from query-cache. ###Explain extended-output +---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+ | 1 | SIMPLE | cd | ALL | PRIMARY,language_code | | | | 5724 | 75.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dilife-eu.cd.categories_id | 1 | 100.00 | Using where | | 1 | SIMPLE | su | ref | link_id | link_id | 4 | dilife-eu.c.categories_id | 83 | 100.52 | Using where | | 1 | SIMPLE | cl | ALL | | | | | 1 | 100.00 | Using where | | 1 | SIMPLE | parent | ALL | | | | | 9530 | 100.00 | Using where; Using join buffer | | 1 | SIMPLE | group_permission | eq_ref | PRIMARY | PRIMARY | 771 | dilife-eu.c.categories_id,const | 1 | 100.00 | Using where | | 1 | SIMPLE | shop | eq_ref | PRIMARY | PRIMARY | 771 | dilife-eu.c.categories_id,const | 1 | 100.00 | Using where | +---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+ ###Explain output after changing all tables to MyISAM. +---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+ | 1 | SIMPLE | cl | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | group_permission | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | shop | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | cd | ALL | PRIMARY,language_code | | | | 5724 | 75.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dilife-eu.cd.categories_id | 1 | 100.00 | Using where | | 1 | SIMPLE | su | ref | link_id | link_id | 4 | dilife-eu.c.categories_id | 83 | 100.57 | Using where | | 1 | SIMPLE | parent | ALL | | | | | 9791 | 100.00 | Using where; Using join buffer | +---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+

Does somebody here know the latest state of the development of MySQL Proxy? Most materials I found on web are outdated.

I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2. Everything seems to function normally replication wise, no issues per se, except for the occasional query that causes a collision but those are few and far between. Recently I set up mysql-proxy to attempt to load balance the two servers, the load balancing really on kicks in after the local machine receives 40 or more connections at which point subsequent database connections get shuffled off to the EC2 machine. Something we noticed recently is that mysql proxy will notify us that there are 41 connections and then start its balancing. However when I connect to the local machine and do a SHOW PROCESSLIST; it may only provide me with 30 connections. Anyone have any idea why this maybe? In addition to that as a result of issuing the SHOW PROCESSLIST; command I've noticed that there are a great many queries running on both machines that state they have been running in excess of 5000 seconds. I'm pretty sure these are "zombie" queries but does anyone know why they are created in the first place? FYI We're running mysql version 5.1.54 on the latest versions of ubuntu and debian. Any ideas would be extremely helpful. [Addendum] Turns out we're not using mysql_pconnect and are infact using the mysqli libraries. I still have been unable to find out why this happening and will report back once I find out.