A database proxy is a wonderful tool that is able to provide significant functionality across various use cases. For example, a seamless master role switch to another node for maintenance; transparency with read and write connections; or providing automatic, intelligent database load balancing.
In the MySQL world, these proxies provide a single entry point into MySQL databases for the calling client applications. Or put differently, the proxy is a middle layer sitting between a MySQL database and an application. The application connects to a proxy, which then forwards connections into the database.
MySQL Proxy , ,
DOWNLOAD: https://urlgoal.com/2vI4Xu
ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers and also improve availability by automatically failing over to a standby if one or more of the database servers fail.
You can find the latest package on the release list. The naming convention is proxysql_version-distribution.deb, where version is a string like 1.4.4 for version 1.4.4, and distribution is a string like ubuntu16_amd64 for 64-bit Ubuntu 16.04.
Now that ProxySQL knows how to distribute nodes across host groups, we can add our MySQL servers to the pool. To do so, we need to INSERT the IP address and initial host group of each server into the mysql_servers table, which contains the list of servers ProxySQL can interact with.
To do so, we need to set configuration variables in the mysql_users table, which holds user credential information. In the ProxySQL interface, add the username, password, and default host group to the configuration database (which is 2, for the writer host group)
The MySQL server authenticates client connections using authentication plugins. The plugin that authenticates a given connection may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to assume the privileges of the second user:
The account for the external proxy user must be set up to be authenticated by the plugin. Use the CREATE USER statement to associate an account with an authentication plugin, or ALTER USER to change its plugin.
For a client connecting to the proxy account to be treated as a proxy user, the authentication plugin must return a user name different from the client user name, to indicate the user name of the proxied account that defines the privileges to be assumed by the proxy user.
If that account is a proxy account, the server attempts to determine the appropriate proxied account by finding a match for a proxied account using the user name returned by the authentication plugin and the host name of the proxy account. The host name in the proxied account is ignored.
The server verifies that proxy authentication for employee is possible for the employee_ext user by checking whether employee_ext (the proxy user) has the PROXY privilege for employee (the proxied user). If this privilege has not been granted, an error occurs. Otherwise, employee_ext assumes the privileges of employee. The server checks statements executed during the client session by employee_ext against the privileges granted to employee. In this case, employee_ext can access tables in the employees database.
When proxying occurs, the USER() and CURRENT_USER() functions can be used to see the difference between the connecting user (the proxy user) and the account whose privileges apply during the current session (the proxied user). For the example just described, those functions return these values:
In the CREATE USER statement that creates the proxy user account, the IDENTIFIED WITH clause that names the proxy-supporting authentication plugin is optionally followed by an AS 'auth_string' clause specifying a string that the server passes to the plugin when the user connects. If present, the string provides information that helps the plugin determine how to map the proxy (external) client user name to a proxied user name. It is up to each plugin whether it requires the AS clause. If so, the format of the authentication string depends on how the plugin intends to use it. Consult the documentation for a given plugin for information about the authentication string values it accepts.
Proxied accounts generally are intended to be used only by means of proxy accounts. That is, clients connect using a proxy account, then are mapped onto and assume the privileges of the appropriate proxied user.
The initial root account created during MySQL installation has the PROXY ... WITH GRANT OPTION privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. For example, root can do this:
If the password is correct and ldap_auth finds that myuser is a developer, it returns the user name developer to the MySQL server, rather than myuser. Returning a user name different from the client user name of myuser signals to the server that it should treat myuser as a proxy. The server verifies that ''@'' can authenticate as developer (because ''@'' has the PROXY privilege to do so) and accepts the connection. The session proceeds with myuser having the privileges of the developer proxied user. (These privileges should be set up by the DBA using GRANT statements, not shown.) The USER() and CURRENT_USER() functions return these values:
The first account (''@'') is intended as the default proxy user, used to authenticate connections for users who do not otherwise match a more-specific account. The second account (''@'%') is an anonymous-user account, which might have been created, for example, to enable users without their own account to connect anonymously.
Both accounts have the same user part (''), which matches any user. And each account has a host part that matches any host. Nevertheless, there is a priority in account matching for connection attempts because the matching rules sort a host of '%' ahead of ''. For accounts that do not match any more-specific account, the server attempts to authenticate them against ''@'%' (the anonymous user) rather than ''@'' (the default proxy user). As a result, the default proxy account is never used.
Assume that the some_plugin/'some_auth_string' combination causes some_plugin to map the client user name to developer. Local connections match the ''@'localhost' proxy user, which maps to the 'developer'@'localhost' proxied user. Remote connections match the ''@'%' proxy user, which maps to the 'developer'@'%' proxied user.
Some authentication plugins implement proxy user mapping for themselves (for example, the PAM and Windows authentication plugins). Other authentication plugins do not support proxy users by default. Of these, some can request that the MySQL server itself map proxy users according to granted proxy privileges: mysql_native_password, sha256_password. If the check_proxy_users system variable is enabled, the server performs proxy user mapping for any authentication plugins that make such a request:
Assuming that the relevant system variables have been enabled, create the proxy user as usual using CREATE USER, then grant it the PROXY privilege to a single other account to be treated as the proxied user. When the server receives a successful connection request for the proxy user, it finds that the user has the PROXY privilege and uses it to determine the proper proxied user.
When a single account has been granted proxy privileges for more than one proxied account, server proxy user mapping is nondeterministic. Therefore, granting to a single account proxy privileges for multiple proxied accounts is discouraged.
proxy_user: This value is NULL if proxying is not used. Otherwise, it indicates the proxy user account. For example, if a client authenticates through the ''@'' proxy account, this variable is set as follows: 2ff7e9595c
Comments