更新时间:2024-04-13 GMT+08:00

数据库代理最佳实践

用户认证和连接

  1. 用户账号如果需要使用数据库代理登录,则必须赋予账号远程登录权限,否则无法通过数据库读写分离访问。

    执行下列SQL语句查看使用的账号的host是否包含数据库读写分离网段。

    SELECT user,host FROM mysql.user;

    如果查询的host不包含数据库代理所在网段,则需要赋予远程访问权限,例如root用户从192.168.0网段连接到mysql服务器:

    GRANT
    ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY password  WITH GRANT 
    OPTION;
    flush privileges;
    查询读写分离网段方法:
    1. 可在主实例的“基本信息”页签,找到“连接信息”模块的“子网”处,单击子网名称,进入子网控制台。
    2. “基本信息”页签的“子网IPv4网段”,即为数据库读写分离的网段。
  2. 当修改安全组时,确保入方向规则和出方向规则允许读写分离的地址访问,读写分离默认端口号为3306。
    1. 登录管理控制台。
    2. 单击管理控制台左上角的,选择区域和项目。
    3. 选择数据库 > 关系型数据库。进入关系型数据库信息页面。
    4. “实例管理”页面,选择目标实例,单击实例名称前的,进入只读实例的“基本信息”页面。
    5. “基本信息”页面中,在“连接信息”模块的“安全组”处,单击安全组名称,进入安全组页面。
    6. 在入方向规则页签下,默认允许3306端口访问。如果没有该条规则,单击“快速添加规则”,弹框页面中勾选“MySQL(3306)”,单击“确定”
  3. 由于数据库代理不支持RDS for MySQL 8.0的caching_sha2_password身份认证插件,当使用RDS for MySQL 8.0客户端访问数据库代理时,如果出现auth user failed报错时,执行以下SQL检查用户名的身份认证插件是否是mysql_native_password。
    select plugin from mysql.user where user="用户名";

    • 是,则可在连接时添加 --default-auth=mysql_native_password,或者使用 RDS for MySQL 5.*版本客户端进行连接。

    • 否,则需执行SQL将用户名使用的身份认证插件改为mysql_native_password。
      ALTER USER '用户名'@'%' IDENTIFIED WITH 'mysql_native_password' BY '密码';
      FLUSH PRIVILEGES;

连接池设置

使用连接池时,需要设置连接探活机制(如jdbc连接池和Druid连接池设置testOnBorrow=true,HikariCP连接池设置connectionTestQuery="SELECT 1"),确保部分连接超时断开时不会被继续使用。

读请求路由到主实例的场景

  1. 如果查询语句被放在事务中,事务请求都会路由到主实例,如果在查询语句前设置set autocommit=0也会被当做事务处理路由到主实例。
  2. 如果无只读实例或所有只读节点均异常、只读节点权重为0时,则查询会路由到主实例。对于成功开启读写分离功能的实例,您可以设置其主实例和只读实例的权重。具体操作请参见设置延时阈值和读写分离权重
  3. 如果执行了Multi-Statements(如“insert xxx;select xxx”),当前连接的后续请求会全部路由到主节点,需断开当前连接并重新连接才能恢复读写分离。
  4. 带锁的读操作(如SELECT for UPDATE)会被路由到主节点。
  5. 当使用/*FORCE_MASTER*/这个Hint语句时,会被路由到主实例。