配置文件 proxysql.cnf datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:admin;radmin:radmin" mysql_ifaces="0.0.0.0:6032" } 启动容器 docker run --name proxysql -d -v /path/to/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql:2.0.12 使用下面的配置文件来运行容器，其中包括管理员账户和管理端口。 MySQL配置 在数据库中创建用于代理监控的用户。 CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; GRANT SELECT on sys.* to 'monitor'@'%'; FLUSH PRIVILEGES; Proxysql具有组复制功能，只要将用于运行状况监视的用户添加到一个MySQL节点上，便会在所有三个节点上对其进行完全配置。 Proxysql配置 登录管理端 admin/admin：本地用户 radmin/radmin：远程用户 6032：proxysql管理端口 # 本地 mysql -uradmin -pradmin -h127.0.0.1 -P6032 --prompt='ProxySQLAdmin> ' # 远程 mysql -uradmin -pradmin -h172.17.0.2 -P6032 --prompt='ProxySQLAdmin> ' # --prompt是一个可选标志，用于更改默认提示，通常为mysql>。 # 在这里，将其更改为ProxySQLAdmin>，以明确表明我们已连接到ProxySQL管理界面。 # 这将有助于避免以后在连接复制数据库服务器上的MySQL接口时造成混乱。 修改密码 通过更新（UPDATE）数据库中的全局变量admin-admin_credentials来更改管理帐户密码，多个帐号用;分隔。 UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials'; 由于ProxySQL的配置系统的工作原理，此更改不会立即生效，所以配置都是这样，要注意。 memory：从命令行界面进行修改时会更改。(会话级别) runtime：ProxySQL将其用作有效配置。（运行时级别） disk：用于使配置在重新启动后保持不变。（持久化） 在命令行修改后的配置存储在memory中。为了使更改生效，必须将memory设置复制到runtime，然后将它们保存到disk以使其持久。 LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK; ADMIN变量处理与管理命令行界面有关的变量 MYSQL变量处理其配置的其他部分 配置监控 配置已经在MySQL示例中创建好的监控账户，返回ProxySQL管理界面，将mysql-monitor_username变量更新为新帐户的用户名，并将配置持久化，注意此时使用的是MYSQL变量。 UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; 添加节点 在Proxysql中配置主机组，每个主机组都由一个正数标识，例如1或2。使用ProxySQL查询路由时，主机组可以将不同的SQL查询路由到不同的主机集群。 在静态副本设置中，主机组可以任意设置。但是，ProxySQL的组副本机制支持自动将组中的所有节点划分为四个逻辑状态： writer：MySQL节点可以接受更改数据的查询。ProxySQL确保将所有主节点保存在该组中的最大定义数量之内。 backup writer：MySQL节点以接受更改数据的查询。但是，这些节点并没有被设置为writer；超过writer定义数量的主节点将保留在该组中，如果writer中有节点发生故障，则将backup writer中的一个节点升级到writer。 reader：MySQL节点不能接受更改数据的查询，将其用作只读节点。ProxySQL仅在此处放置从属节点。 offline：放置由于缺乏连通性或流量缓慢等问题而出现异常的节点。 这四个状态中的每一个都有对应的主机组，但是不会自动分配数字组标识符。 我们需要告诉ProxySQL每个状态应使用哪些标识符。比如： 将1用于offline主机组 将2用于writer主机组 将3用于reader主机组 将4用于backup writer主机组 要设置这些标识符，就在mysql_group_replication_hostgroups表中使用这些变量和值创建一个新行。 INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, \ offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) \ VALUES (2, 4, 3, 1, 1, 3, 1, 100); 字段说明： 如果将active设置为1，则ProxySQL可以监视这些主机组。 max_writers定义writer节点的最大数量。在这里使用3是因为在多主节点设置中，所有节点都可以被视为相等，因此在这里使用3（节点总数）。 将writer_is_also_reader设置为1会指示ProxySQL将writer也认为是reader。 max_transactions_behind设置定义节点为offline状态的最大延迟事务数。 这样ProxySQL就知道如何在主机组之间分配节点，然后将MySQL服务器添加到池中。为此，需要将每个服务器的IP地址和初始主机组插入mysql_servers表，该表包含ProxySQL可以与之交互的服务器列表。 添加MySQL服务器，并确保替换以下命令中的示例IP地址。 INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.1', 3306); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; ProxySQL现在应按指定在主机组之间分布我们的节点。我们通过对runtime_mysql_servers表执行SELECT查询来进行检查，该表显示了ProxySQL正在使用的服务器的当前状态。 SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers; 配置数据库用户凭据 ProxySQL充当负载均衡；用户连接到ProxySQL，然后ProxySQL将该连接依次传递到所选的MySQL节点。为了连接到单个节点，ProxySQL会重用其访问的凭据。 为了允许访问位于复制节点上的数据库，需要创建一个具有与ProxySQL相同的凭据的用户帐户，并向该用户授予必要的特权。 创建一个名为PlaygroundUser的新用户，密码是playgroundpassword。 CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword'; GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%'; FLUSH PRIVILEGES; EXIT; 可以通过直接在节点上尝试使用新配置的凭据访问数据库来验证是否已正确创建用户。 mysql -u playgrounduser -pSHOW TABLES FROM playground; +----------------------+ | Tables_in_playground | +----------------------+ | equipment | +----------------------+ 1 row in set (0.00 sec) 创建Proxysql用户 最后的配置步骤是允许playgrounduser用户与ProxySQL建立连接，并将这些连接传递给节点。 为此，我们需要在mysql_users表中设置配置变量，该表包含用户凭据信息。将用户名，密码和默认主机组添加到配置数据库（对于writer主机组，该名称为2）。 INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; ProxySQL在端口6033上监听传入的客户端连接，因此请尝试使用Playgrounduser和端口6033连接到真实数据库（而非管理界面）。在这里，我们将提示设置为ProxySQLClient>，以便将其与管理界面提示区分开。 mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> ' 让我们执行一条简单的语句来验证ProxySQL是否将连接到其中一个节点。此命令在数据库中查询正在运行的服务器的主机名，并返回服务器的主机名作为唯一输出。 SELECT @@hostname; +------------+ | @@hostname | +------------+ | member1 | +------------+ 1 row in set (0.00 sec) 根据我们的配置，此查询应由ProxySQL定向到分配给编写者主机组的三个节点之一。其中member1是一个MySQL节点的主机名。 验证ProxySQL配置 ProxySQL和MySQL节点之间的连接正常，测试确保数据库权限允许从ProxySQL读取和写入语句，并确保当其中的某些节点宕机仍能成功执行这些语句。 -- 读 SELECT * FROM playground.equipment; -- 写 INSERT INTO playground.equipment (type, quant, color) VALUES ("drill", 5, "red"); -- 再读 SELECT * FROM playground.equipment; 完成。
该协议具有一些在整个协议中使用的非常基本的类型： 整数类型 字符串 Integer Types MySQL协议具有一组可编码的整型： 定长整数类型 长度可编码整数类型 Protocol::FixedLengthInteger 定长整数类型。固定长度的无符号整数将其值存储在一系列字节中，最低有效字节在前（小端存储模式）。最低有效位是指删除后对整个值影响最小的那一位。 通过网络发送这字节时，接收方需要知道字节内容是如何编码的（小端存储模式/大端存储模式）才能将原始字节转换为内部整数表示形式。 小端存储模式：较高的有效字节存放在较高的存储器地址，较低的有效字节存放在较低的存储器地址。（即正常的高位在左的模式，有利于计算机处理，因为计算机都是从低位开始往高位处理的） 大端存储模式：较高的有效字节存放在较低的存储器地址，较低的有效字节存放在较高的存储器地址。（符合正常的人类思维，人类都是从高位开始往低位阅读的） 大小端模式与硬件的体系结构相关。 MySQL使用以下固定长度的无符号整数变体： int<1>： 1 byte int<2>： 2 byte int<3>： 3 byte int<4>： 4 byte int<6>： 6 byte int<8>： 8 byte 示例 一个长度为3的定长整数类型，内容值为1，存储形式如下：01 00 00。 Protocol::LengthEncodedInteger int<lenenc>：长度可编码整数类型。使用1、3、4或9个字节的整数，具体取决于其数字值。 将数字值转换为长度可编码整数： 大于/等于 小于 存储为 0 251 1-byte integer 251 2^16 0xFC + 2-byte integer 216 2^24 0xFD + 3-byte integer 224 2^64 0xFE + 8-byte integer 要将长度可编码整数转换为数字值，要检查第一个字节。 如果它小于0xfb，则将其视为1字节整数。 如果为0xfc，则后跟2个字节的整数。 如果为0xfd，则后跟3个字节的整数。 如果为0xfe，则后跟8个字节的整数。 警告：如果数据包的第一个字节是长度可编码整数，并且其字节值为0xFE，则必须检查数据包的长度以验证其是否有足够的空间用于8字节整数。如果不是，则可能是EOF_Packet。 根据上下文，第一个字节可能还具有其他含义： 如果为0xfb，则在ProtocolText::ResultsetRow中表示NULL。 如果为0xff，则是ERR_Packet的第一个字节。 警告：未定义0xff作为长度可编码整数类型的第一个字节。 示例 fa小于0xfb，表示一个字节，它的十进制值为250 fc fb 00的第一个字节是0xfc，表示长度是两个字节的整数，它的值是fb 00即十进制值为251 String Types 字符串是字节序列，在协议中以多种形式出现。 Protocol::FixedLengthString string<fix>：固定长度的字符串具有已知的硬编码长度。 示例 ERR_Packet长度始终为5个字节。 Protocol::NullTerminatedString string<NUL>：以00字节结尾的字符串。 Protocol::VariableLengthString string<var>：字符串的长度由另一个字段确定或在运行时计算出来。 Protocol::LengthEncodedString string<lenenc>：长度可编码字符串是一个以描述字符串长度的长度编码整数类型作为前缀的字符串，其实是上面一种类型的特例。 字段 length（int<lenenc>）：字符串的长度 string（string<fix>）：[len=$length]string Protocol::RestOfPacketString string<EOF>：如果字符串是数据包的最后一个组成部分，则可以从总数据包长度减去当前位置来计算其长度。 Describing Packets 首先定义每个数据包的payload来描述每个数据包，并提供一个示例，显示每个发送的数据包，包括其数据包头： <packetname> <description> direction: client -> server response: <response> payload: <type> <description> Example: 01 00 00 00 01 其中<type>描述数据包字节的顺序： Type Description int<1> 1 byte Protocol::FixedLengthInteger int<2> 2 byte Protocol::FixedLengthInteger int<3> 3 byte Protocol::FixedLengthInteger int<4> 4 byte Protocol::FixedLengthInteger int<6> 6 byte Protocol::FixedLengthInteger int<8> 8 byte Protocol::FixedLengthInteger int<lenenc> Protocol::LengthEncodedInteger string<lenenc> Protocol::LengthEncodedString string<fix> Protocol::FixedLengthString string<var> Protocol::VariableLengthString string<EOF> Protocol::RestOfPacketString string<NUL> Protocol::NulTerminatedString 注意：某些数据包具有可选字段或不同的字段分布，具体取决于作为Protocol::HandshakeResponse数据包一部分发送的Protocol::CapabilityFlags。 如果字段具有固定值，则在description部分将其显示为十六进制值，如方括号：[00]。 Binary Protocol Value 下面这一批都是类似的 ProtocolBinary::MYSQL_TYPE_STRING ProtocolBinary::MYSQL_TYPE_VARCHAR ProtocolBinary::MYSQL_TYPE_VAR_STRING ProtocolBinary::MYSQL_TYPE_ENUM ProtocolBinary::MYSQL_TYPE_SET ProtocolBinary::MYSQL_TYPE_LONG_BLOB ProtocolBinary::MYSQL_TYPE_MEDIUM_BLOB ProtocolBinary::MYSQL_TYPE_BLOB ProtocolBinary::MYSQL_TYPE_TINY_BLOB ProtocolBinary::MYSQL_TYPE_GEOMETRY ProtocolBinary::MYSQL_TYPE_BIT ProtocolBinary::MYSQL_TYPE_DECIMAL ProtocolBinary::MYSQL_TYPE_NEWDECIMAL 字段 value(lencenc_str)---string 示例 03 66 6f 6f -- string = "foo" ProtocolBinary::MYSQL_TYPE_LONGLONG 字段 value (8) -- integer 示例 01 00 00 00 00 00 00 00 -- int64 = 1 ProtocolBinary::MYSQL_TYPE_LONG, ProtocolBinary::MYSQL_TYPE_INT24 字段 value (4) -- integer 示例 01 00 00 00 -- int32 = 1 ProtocolBinary::MYSQL_TYPE_SHORT, ProtocolBinary::MYSQL_TYPE_YEAR 字段 value (2) -- integer 示例 01 00 -- int16 = 1 ProtocolBinary::MYSQL_TYPE_TINY 字段 value (1) -- integer 示例 01 -- int8 = 1 ProtocolBinary::MYSQL_TYPE_DOUBLE MYSQL_TYPE_DOUBLE以IEEE 754双精度格式存储浮点数。 在C语言存储中，第一个字节是有效位的最后一个字节，即小端存储。 字段 value (string.fix_len) -- (len=8) double 示例 66 66 66 66 66 66 24 40 -- double = 10.2 ProtocolBinary::MYSQL_TYPE_FLOAT MYSQL_TYPE_FLOAT以IEEE 754单精度格式存储浮点数。 字段 value (string.fix_len) -- (len=4) float 示例 33 33 23 41 -- float = 10.2 ProtocolBinary::MYSQL_TYPE_DATE, ProtocolBinary::MYSQL_TYPE_DATETIME, ProtocolBinary::MYSQL_TYPE_TIMESTAMP 在二进制协议中存储DATE，DATETIME和TIMESTAMP字段。 为了节省空间，可以压缩数据包： 如果年，月，日，小时，分钟，秒和微秒均为0，那么长度为0，且不发生为0字段 如果小时，分钟，秒和微秒均为0，那么长度为4，且不发送为0字段 如果微秒为0，那么长度为7，并且不发送微秒字段 其他情况长度为11 字段 length (1) -- number of bytes following (valid values: 0, 4, 7, 11) year (2) -- year month (1) -- month day (1) -- day hour (1) -- hour minute (1) -- minutes second (1) -- seconds micro_second (4) -- micro-seconds 示例 0b da 07 0a 11 13 1b 1e 01 00 00 00 -- datetime 2010-10-17 19:27:30.000 001 04 da 07 0a 11 -- date = 2010-10-17 0b da 07 0a 11 13 1b 1e 01 00 00 00 -- timestamp ProtocolBinary::MYSQL_TYPE_TIME 在二进制协议中存储TIME字段。 为了节省空间，可以压缩数据包： 如果天，小时，分钟，秒和微秒均为0，那么长度为0，且不发生为0字段 如果微秒为0，那么长度为8，并且不发送微秒字段 其他情况长度为12 字段 length (1) -- number of bytes following (valid values: 0, 8, 12) is_negative (1) -- (1 if minus, 0 for plus) days (4) -- days hours (1) -- hours minutes (1) -- minutes seconds (1) -- seconds micro_seconds (4) -- micro-seconds 示例 0c 01 78 00 00 00 13 1b 1e 01 00 00 00 -- time -120d 19:27:30.000 001 08 01 78 00 00 00 13 1b 1e -- time -120d 19:27:30 01 -- time 0d 00:00:00 ProtocolBinary::MYSQL_TYPE_NULL 仅存储在NULL位图中。
如果MySQL客户端或服务器要发送数据，则： 将数据拆分为大小为（2^24-1）字节的数据包 在每个块之前添加一个packet header Protocol::Packet 客户端和服务器之间的数据以最大16MByte的数据包交换。 Type Name Description int<3> payload_length payload长度，数据包中字节数，前4个字节作为作为数据包的header int<1> sequence_id Sequence ID string<var> payload 数据包的payload，[len=payload_length] 示例： COM_QUIT的数据包如下： packet ： 01 00 00 00 01 +------------+-----------------+-------------+ | Length = 1 | Sequence ID = 0 | Payload = 1 | +------------+-----------------+-------------+ | 01 00 00 | 00 | 01 | +------------+-----------------+-------------+ length: 1 sequence_id: x00；序列号随每个数据包增加，它从0开始，并在Command phase开始新命令时重置为0。 payload: 0x01 发送超过16Mbyte的数据包 如果payload大于或等于2^24-1字节，则将数据包的长度设置为2^24-1（ff ff ff），同时剩下的payload也一起发送，直到数据包的payload小于2^24-1字节为止。 发送payload是16777215（2^24-1）字节的数据包如下： ff ff ff 00 ... 00 00 00 01 Protocol::Handshake 初始握手包，当客户端连接到服务器时，服务器将向客户端发送握手数据包。根据服务器版本和配置选项，将发送初始数据包的不同变体。 为了允许服务器添加对较新协议的支持，第一个字节定义了协议版本。 从3.21.0版本开始发送Protocol::HandshakeV10。 Protocol::HandshakeV10 数据包格式： Type Name Description int<1> protocol version Always 10 string<NUL> server version human readable status information int<4> thread id a.k.a. connection id string[8] auth-plugin-data-part-1 first 8 bytes of the plugin provided data (scramble) int<1> filler 0x00 byte, terminating the first part of a scramble int<2> capability_flags_1 The lower 2 bytes of the Capabilities Flags int<1> character_set default server a_protocol_character_set, only the lower 8-bits int<2> status_flags SERVER_STATUS_flags_enum int<2> capability_flags_2 The upper 2 bytes of the Capabilities Flags if capabilities & CLIENT_PLUGIN_AUTH { int<1> auth_plugin_data_len length of the combined auth_plugin_data (scramble), if auth_plugin_data_len is > 0 } else { int<1> 00 constant 0x00 } string[10] reserved reserved. All 0s. $length auth-plugin-data-part-2 Rest of the plugin provided data (scramble), $len=MAX(13, length of auth-plugin-data - 8) if capabilities & CLIENT_PLUGIN_AUTH { NULL auth_plugin_name name of the auth_method that the auth_plugin_data belongs to } 如果客户端支持SSL（将设置Capabilities Flags和CLIENT_SSL同时客户端的mysql_ssl_mode不会被设置为SSL_MODE_DISABLED）将发送一个名为Protocol::SSLRequest的简短数据包，使服务器建立SSL层并等待客户端的下一个数据包。 客户端将会回复Protocol::HandshakeResponse。 任何时候，任何错误，客户端都会断开连接。
在命令阶段，客户端发送command packet，其sequence-id为[00]： 13 00 00 00 03 53 ... 01 00 00 00 01 ^^- command-byte ^^---- sequence-id == 0 payload的第一个字节描述了命令类型，如下： Hex Constant Name 00 COM_SLEEP 01 COM_QUIT 02 COM_INIT_DB 03 COM_QUERY 04 COM_FIELD_LIST 05 COM_CREATE_DB 06 COM_DROP_DB 07 COM_REFRESH 08 COM_SHUTDOWN 09 COM_STATISTICS 0a COM_PROCESS_INFO 0b COM_CONNECT 0c COM_PROCESS_KILL 0d COM_DEBUG 0e COM_PING 0f COM_TIME 10 COM_DELAYED_INSERT 11 COM_CHANGE_USER 12 COM_BINLOG_DUMP 13 COM_TABLE_DUMP 14 COM_CONNECT_OUT 15 COM_REGISTER_SLAVE 16 COM_STMT_PREPARE 17 COM_STMT_EXECUTE 18 COM_STMT_SEND_LONG_DATA 19 COM_STMT_CLOSE 1a COM_STMT_RESET 1b COM_SET_OPTION 1c COM_STMT_FETCH 1d COM_DAEMON 1e COM_BINLOG_DUMP_GTID 1f COM_RESET_CONNECTION 这些command属于： Text Protocol Utility Commands Prepared Statements Stored Programs Text Protocol COM_QUERY 发送基于文本协议的SQL查询，执行立即开始。响应COM_QUERY Response。 有效负载如下： Type Name Description int<1> command 0x03: COM_QUERY string<EOF> query 要执行的SQL查询的文本 示例： 21 00 00 00 03 73 65 6c 65 63 74 20 40 40 76 65 !....select @@ve 72 73 69 6f 6e 5f 63 6f 6d 6d 65 6e 74 20 6c 69 rsion_comment li 6d 69 74 20 31 mit 1 COM_QUERY Response 查询响应数据包是一个元数据包，可以是以下之一： ERR_Packet OK_Packet LOCAL INFILE Request Text Resultset 注意：如果打开CLIENT_DEPRECATE_EOF，则发送OK_Packet而不是实际的EOF_Packet数据包。 Prepared Statements COM_STMT_EXECUTE COM_STMT_EXECUTE要求服务器执行由stmt_id标识的准备好的语句。 它以Binary Protocol Value形式发送准备好的语句的占位符的值（如果包含）。每个参数的类型由两个字节组成： 在Protocol::ColumnType中的类型 如果类型是无符号的，则标志字节的最高位被设置[80] 用于此数据包的num_params必须与相应的准备好的语句的COM_STMT_PREPARE_OK的num_params匹配。 响应COM_STMT_EXECUTE Response COM_STMT_EXECUTE: COM_STMT_EXECUTE execute a prepared statement direction: client -> server response: COM_STMT_EXECUTE Response payload: 1 [17] COM_STMT_EXECUTE 4 stmt-id 1 flags 4 iteration-count if num-params > 0: n NULL-bitmap, length: (num-params+7)/8 1 new-params-bound-flag if new-params-bound-flag == 1: n type of each parameter, length: num-params * 2 n value of each parameter example: 12 00 00 00 17 01 00 00 00 00 01 00 00 00 00 01 ................ 0f 00 03 66 6f 6f ...foo iteration-count总是为1。 flags如下： Flags Constant Name 0x00 CURSOR_TYPE_NO_CURSOR 0x01 CURSOR_TYPE_READ_ONLY 0x02 CURSOR_TYPE_FOR_UPDATE 0x04 CURSOR_TYPE_SCROLLABLE null_bitmap就像Binary Protocol Resultset Row的NULL-bitmap一样，只是它的bit_offset为0。 COM_STMT_EXECUTE Response 与COM_QUERY响应类似，COM_STMT_EXECUTE返回以下之一： OK_Packet ERR_Packet Binary Protocol Resultset
MySQL协议是一个有状态的协议。 当一个（TCP）连接建立后，服务器会发起Connection Phase。 连接阶段完成后进入Command Phase，（TCP）连接终止命令阶段结束。 如果发送复制命令，那么可以从连接阶段进入Replication Protocol。 Connection Phase 在连接阶段执行一下任务： 交换客户端和服务器的功能 如果需要，设置SSL通信通道 针对服务器对客户端进行身份验证 从客户端连接服务器开始，服务器可以发送一个ERR数据包来结束握手或者发生一个初始Handshake数据包，客户端收到后会回复一个Handshake响应包。在此阶段，客户端可以请求SSL连接，在这种情况下，必须在客户端发送身份验证响应前建立SSL通信通道。 如果服务器将ERR数据包作为第一个数据包发送，那么这个发送行为是在客户端和服务器协商任何功能之前发生的。因此，ERR数据包将不包含SQL状态。 初次握手后，服务器将通知客户端有关身份验证的方法（除非在握手期间已经确定该方法），并且身份验证交换继续进行，直到服务器发送OK_Packet接受连接或者发送ERR_Packet拒绝连接。 初次握手 初次握手从服务器发送Protocol::Handshake数据包开始。在这之后，客户端可以选择使用Protocol::SSLRequest数据包请求建立SSL连接，然后客户端发送Protocol::HandshakeResponse数据包。 普通握手 服务器发送Protocol::Handshake 客户端回复Protocol::HandshakeResponse SSL握手 服务器发送Protocol::Handshake 客户端返回Protocol::SSLRequest 通常通过SSL交换来建立SSL连接（这个过程挺消耗资源的） 客户端发送Protocol::HandshakeResponse 功能协商 为了允许旧客户端连接到新服务器，Protocol::Handshake包含MySQL服务器版本即服务器的功能标志。 客户端在Protocol::HandshakeResponse中只声明与服务器相同的功能。 然后使用如下参数达成一致： 状态标识（status flags） SQL状态错误码（SQL states for error codes） 认证方法（authentication methods） SSL（SSL Support） 压缩（Compression） 确定身份验证的方法 用于身份验证的方法将与用户帐户绑定，并存储在mysql.user表的plugin列中。客户端在Protocol::HandshakeResponse数据包中发送将要登录的用户帐户。这样，服务器就能查找mysql.user表并找到要使用的身份验证方法。 为节省通信成本，服务器和客户端在发送初始Handshake数据包时就对要使用的身份验证方法进行了推测。 服务器使用其默认身份验证方法default_auth_plugin生成初始身份验证数据的有效负载，并将其与方法名放在Protocol::Handshake中一起发给客户端。 客户端在Protocol::HandshakeResponse中包含对服务器发送的身份验证数据的答复。 当在Protocol::HandshakeResponse中包括身份验证回复时，客户端没有义务使用与Protocol::Handshake数据包中服务器所使用的身份验证方法相同的身份验证方法。客户端使用的身份验证方法的名称存储在响应数据包中。如果客户端或服务器在初始握手中包换的推测身份验证方法不正确，则服务器会使用Protocol::AuthSwitchRequest通知客户端应使用哪种身份验证方法。 在MySQL 4.0之前，MySQL协议仅支持Old Password Authentication。在MySQL 4.1中，添加了Native Authentication方法，而在MySQL 5.5中，可以通过身份验证插件实现任意身份验证方法。 如果客户端或服务器不支持插件式身份验证（即未设置CLIENT_PLUGIN_AUTH功能标志），则从客户端和服务器功能继承使用的身份验证方法，如下所示： 如果未设置CLIENT_PROTOCOL_41或CLIENT_SECURE_CONNECTION，则使用的方法是Old Password Authentication。 如果同时设置了CLIENT_PROTOCOL_41和CLIENT_SECURE_CONNECTION，但未设置CLIENT_PLUGIN_AUTH，则使用的方法是Native Authentication。 快速身份验证路径 假设客户端要通过用户帐户U登录，并且该用户帐户已定义为使用身份验证方法server_method。在以下情况下使用快速身份验证路径： 服务器使用server_method生成身份验证数据后放入Protocol::Handshake数据包中。 客户端在Protocol::HandshakeResponse中声明使用client_authentication_method，该方法与服务器使用的server_method兼容。 这样在握手期间就已经开始了第一轮身份验证。然后，根据身份验证方法server_method，进一步交换身份验证，直到服务器接受或拒绝身份验证为止。 验证成功 成功执行快速身份验证路径的步骤如下： 客户端连接服务器 服务器发送Protocol::Handshake 客户端响应Protocol::HandshakeResponse 客户端和服务器根据服务器身份验证方法的要求，为客户端尝试进行身份验证的用户帐户交换其他数据包 服务器响应OK_Packet 服务器在步骤4中发送一个Protocol::AuthMoreData数据包，其前缀为0x01，来区别于ERR_Packet和OK_Packet。 注意：许多身份验证方法（包括mysql_native_password方法）由单个请求-响应交换组成。因此，在步骤4中不会交换任何额外的数据包，并且服务器在接收到Protocol::HandshakeResponse数据包后（如果身份验证成功）就直接发送OK_Packet。 验证失败 它与身份验证成功完全一样，只是在用户身份验证失败的时候，服务器将以ERR_Packet而不是OK_Packet进行回复。 验证方法不匹配 假设客户端要以用户U身份登录，并且该用户帐户使用身份验证方法M。如果： 服务器用于生成放在Protocol::Handshake数据包中的身份验证有效负载的默认方法与M不同 客户端用于生成放在Protocol::HandshakeResponse数据包中的方法与M不兼容 则说明身份验证方法不匹配，必须使用正确的身份验证方法重新启动身份验证交换过程。 注意： 即使客户端和服务器在初始握手中使用了兼容的身份验证方法，也可能发生不匹配，因为，服务器使用的方法与用户帐户所需的方法不同。 在4.1-5.7版本的服务器和客户端中，默认身份验证方法是Native Authentication。 在8.0版本的服务器和客户端中，默认的身份验证方法是Caching_sha2_password information。 客户端和服务器可以通过--default-auth选项更改其默认身份验证方法。 对客户端来说，查看在Protocol::Handshake数据包中声明的服务器的默认身份验证方法，并从中推断出身份验证方法，比在生成Protocol::HandshakeResponse数据包时直接使用客户端默认身份验证方法更好。但是，由于服务器和客户端之间存在一对多的身份验证方法插件，而且，客户端通常都不知道这种映射关系，因此这在mysql客户端库中未实现。 如果发生身份验证方法不匹配，服务器将向客户端发送Protocol::AuthSwitchRequest数据包，其中包含服务器要使用的身份验证方法的名称以及使用新方法重新生成的第一个身份验证有效负载。客户端应切换到服务器请求的身份验证方法，并按照该方法的指示继续进行交换。 如果客户端不知道所请求的方法，则应断开连接。 变更验证方法 客户端连接服务器 服务器发送Protocol::Handshake 客户端响应Protocol::HandshakeResponse 服务器发送Protocol::AuthSwitchRequest来告知客户端需要更换一个新的验证方法 客户端和服务器根据服务器身份验证方法的需要，为客户端尝试进行身份验证的用户帐户交换其他数据包 服务器响应OK_Packet或者ERR_Packet表示拒绝 客户端功能不满足 如果服务器发现客户端功能不足以完成身份验证，则服务器将使用ERR_Packet拒绝。在以下情况下可能会发生这种情况： 不支持插件式身份验证（未设置CLIENT_PLUGIN_AUTH标志）的客户端连接到使用与Native Authentication方法不同的帐户 不支持安全身份验证（未设置CLIENT_SECURE_CONNECTION标志）的客户端尝试建立连接 服务器的默认身份验证方法（用于在Protocol::Handshake数据包中生成身份验证数据）与Native Authentication不兼容，并且客户端不支持插件式身份验证（未设置CLIENT_PLUGIN_AUTH标志） 在以上任何一种情况下，身份验证阶段都将如下所示： 客户端连接服务器 服务器发送Protocol::Handshake 客户端响应Protocol::HandshakeResponse 服务器发现客户端没有足够的功能来处理请求的验证方法，然后发生ERR_Packet并关闭连接 客户端未知的新验证方法 即便客户端支持外部（插件式）身份验证（设置了CLIENT_PLUGIN_AUTH标志），也可能不知道Protocol::AuthSwitchRequest数据包中声明的新的身份验证方法。在这种情况下，客户端只需断开连接即可。 客户端连接服务器 服务器发送Protocol::Handshake 客户端响应Protocol::HandshakeResponse 服务器发送Protocol::AuthSwitchRequest来告知客户端需要更换一个新的验证方法 客户端发现它不知道服务器请求的身份验证方法，然后，断开连接 Non-CLIENT_PLUGIN_AUTH客户端 注意：这只会在8.0版本之前的服务器上发生。 8.0版本开始移除了Old Password Authentication。 服务器将向未设置CLIENT_PLUGIN_AUTH标志的客户端请求更改身份验证方法的唯一可能是满足一下条件： 客户端在Protocol::HandshakeResponse数据包中使用Old Password Authentication 客户端支持安全身份验证（已设置CLIENT_SECURE_CONNECTION） 服务器的默认身份验证方法是Native Authentication 在这种情况下，服务器发送Protocol::OldAuthSwitchRequest数据包，其中不包含新的验证方法的名称，因为它被隐式假定为Native Authentication，并且其中不包含身份验证数据。 客户端响应Protocol::HandshakeResponse320。要生成密码哈希，客户端必须重用服务器在Protocol::Handshake中发送的随机字节。 执行COM_CHANGE_USER指令后的验证 在命令阶段，客户端可以发送COM_CHANGE_USER命令，该命令将通过完全身份验证握手来触发对新帐户的身份验证。 与连接阶段类似，服务器可以使用ERR_Packet或OK_Packet来响应快速身份验证路径，或者发送Protocol::AuthSwitchRequest数据包进行响应，在该数据包中包含要用于新帐户的身份验证方法以及客户端要使用的第一个身份验证数据的有效负载 。根据新帐户的身份验证方法的定义，执行进一步的握手。最终，服务器将接受新帐户并响应OK_Packet，或者发生ERR_Packet来拒绝这种比变更并断开连接。 客户端发送COM_CHANGE_USER数据包 服务器响应Protocol::AuthSwitchRequest，来使用正确的身份验证方法启动身份验证握手 客户端和服务器根据新帐户的身份验证方法的要求交换其他数据包 服务器以OK_Packet响应并返回命令阶段或以ERR_Packet相应并关闭连接 COM_CHANGE_USER和Non-CLIENT_PLUGIN_AUTH客户端 不支持可插件式身份验证的客户端可以为使用Native Authentication或Old Password Authentication验证的账户发送COM_CHANGE_USER命令。在这种情况下，假定服务器已经发送了身份验证质询（与客户端第一次连接时发送的身份质询相同），并且客户端对该质询的答复（即新密码的哈希）应在发送auth_response中包含 COM_CHANGE_USER字段。 客户端发送COM_CHANGE_USER数据包，其中包含Native Authentication（4.1版后的客户端）或Old Password Authentication（4.1版之前的客户端）方法的身份验证响应（即密码的哈希值） 服务器以OK_Packet响应并返回到命令阶段，或者以ERR_Packet返回并关闭连接 与正常连接期间一样，不支持插件式身份验证的4.1版客户端也有可能连接到使用Old Password Authentication的帐户，在这种情况下，服务器将发送Protocol::OldAuthSwitchRequest并期望客户端以Protocol::HandshakeResponse320来响应。 客户端发送COM_CHANGE_USER数据包来响应Native Authentication 服务器响应Protocol::OldAuthSwitchRequest（0xFE字节） 客户端再次以Old Password Authentication所需的形式发送响应 服务器以OK_Packet响应并返回到命令阶段或以ERR_Packet返回并断开连接
概念 指标 TPS：每秒事务处理量(Transaction per second)，即服务器每秒能处理的事务数。TPS经常包括数据的输入和输出，以及加上用户数据库访问或者一些rpc请求的时间。 QPS：每秒查询率(Queries-per-second)，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准，在因特网上，作为域名系统服务器的机器的性能经常用每秒查询率来衡量。 一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算一个业务事务所使用的时间。 线程池 线程池技术通过预先创建一定数量的线程，在监听到有新的请求时，线程池直接从现有的线程中分配一个线程来提供服务，服务结束后这个线程不会直接销毁，而是又去处理其他的请求。 这样就避免了线程和内存对象频繁创建和销毁，减少了上下文切换，提高了资源利用率，从而在一定程度上提高了系统的性能和稳定性。 连接池 频繁的建立、关闭连接，会极大的减低系统的性能，连接的使用成了系统性能的瓶颈。 连接复用。通过建立一个数据库连接池以及一套连接使用管理策略，使得一个数据库连接可以得到高效、安全的复用，避免了数据库连接频繁建立、关闭的开销。 资源池。该模式正是为了解决资源频繁分配、释放所造成的问题的。把它应用到数据库连接管理领域，就是建立一个数据库连接池，提供一套高效的连接分配、使用策略，最终目标是实现连接的高效、安全的复用。 测试 并发线程数 数据库连接池大小 每个请求的等待事件 获得连接后的执行时间 数据库情况 9600 2048 33ms 77ms 各种buffer busy waits / CPU 95% 9600 1024 38ms 30ms wait事件减少一半 9600 96 1ms 2ms 几乎没有wait事件，吞吐量上升 说明 单核 CPU 的计算机也能 “同时” 运行数百个线程。但这只不过是操作系统用时间分片玩的一个小把戏。一颗 CPU 核心同一时刻只能执行一个线程，然后操作系统切换上下文，核心开始执行另一个线程的代码，以此类推。 给定一颗 CPU 核心，其顺序执行 A 和 B 永远比通过时间分片 “同时” 执行 A 和 B 要快，这是一条计算机科学的基本法则。一旦线程的数量超过了 CPU 核心的数量，再增加线程数系统就只会更慢，而不是更快。推荐：多线程内容聚合。 数据库瓶颈 数据库的性能瓶颈通常分为：CPU、磁盘和网络。 CPU 不考虑磁盘和网络的影响，在一个 8 核的服务器上，设定线程数/连接池大小=8能够提供最优的性能，再增加连接数就会因上下文切换的损耗导致性能下降。 磁盘 数据库通常把数据存储在磁盘上，磁盘通常是由一些旋转着的金属碟片和一个装在步进马达上的读写头组成的。读/写头同一时刻只能出现在一个地方，然后它必须 “寻址” 到另外一个位置来执行另一次读写操作。 所以就有了寻址的耗时，此外还有旋回耗时，读写头需要等待碟片上的目标数据 “旋转到位” 才能进行操作。使用缓存当然是能够提升性能的，但上述原理仍然成立。 在这一时间段（即 "I/O 等待"）内，线程是在 “阻塞” 着等待磁盘，此时操作系统可以将那个空闲的 CPU 核心用于服务其他线程。所以，由于线程总是在 I/O 上阻塞，可以让线程数/连接池大小比 CPU 核心多一些，这样能够在同样的时间内完成更多的工作。 那么应该比CPU核心多多少呢？这要取决于磁盘。 较新型的 SSD 不需要寻址，也没有旋转的碟片，意味着更少的阻塞，所以更少的线程 [更接近于 CPU 核心数] 会发挥出更高的性能。只有当阻塞创造了更多的执行机会时，更多的线程数才能发挥出更好的性能。 网络 网络和磁盘类似，带宽越高阻塞越少，线程数就更少。 总结 PostgreSQL 提供的的公式：连接数 = ((核心数 * 2) + 有效磁盘数) 。 核心数不应包含超线程 (hyper thread)，即使打开了 hyperthreading 也是。 如果活跃数据全部被缓存了，那么有效磁盘数是 0，随着缓存命中率的下降，有效磁盘数逐渐趋近于实际的磁盘数。 这一公式作用于 SSD 时的效果尚未有分析。 按这个公式，4 核 i7 数据库服务器的连接池大小应该为 ((4 * 2) + 1) = 9。取个整就算是是 10 吧。 注：这一公式不仅适用于数据库连接池的计算，大部分涉及计算和 I/O 的程序，线程数的设置都可以参考这一公式。 需要一个小连接池，和一个充满了等待连接的线程的队列。 连接池的大小最终与系统特性相关： 一个混合了长事务和短事务的系统，通常是任何连接池都难以进行调优的。最好的办法是创建两个连接池，一个服务于长事务，一个服务于短事务。 一个系统执行一个任务队列，只允许一定数量的任务同时执行，此时并发任务数应该去适应连接池连接数，而不是反过来。 操作 show variables like '%thread%' 参数 说明 thread_handling 该参数是配置线程模型，默认情况是one-thread-per-connection，即不启用线程池；将该参数设置为pool-of-threads即启用了线程池。 thread_pool_size 该参数是设置线程池的Group的数量，默认为系统CPU的个数，充分利用CPU资源。 thread_pool_oversubscribe 该参数设置group中的最大线程数，每个group的最大线程数为thread_pool_oversubscribe+1，注意listener线程不包含在内。 thread_pool_high_prio_mode 高优先级队列的控制参数，有三个值（transactions【对于已经启动事务的语句放到高优先级队列中】/statements【这个模式所有的语句都会放到高优先级队列中，不会使用到低优先级队列】/none【这个模式不使用高优先级队列】），默认是transactions thread_pool_high_prio_tickets 该参数控制每个连接最多语序多少次被放入高优先级队列中，默认为4294967295，注意这个参数只有在thread_pool_high_prio_mode为transactions的时候才有效果。 thread_pool_idle_timeout worker线程最大空闲时间，默认为60秒，超过限制后会退出。 thread_pool_max_threads 该参数用来限制线程池最大的线程数，超过该限制后将无法再创建更多的线程，默认为100000。 thread_pool_stall_limit 该参数设置timer线程的检测group是否异常的时间间隔，默认为500ms。
默认情况下，MySQL镜像创建的Docker容器启动时只是一个空的数据库实例，为了简化Docker部署，需要在Docker创建MySQL容器时： 自动建好数据库和表 并且自动录入初始化数据 也就是说容器启动后数据库就可用了。 这就需要容器启动时能自动执行sql脚本。 在MySQL官方镜像中提供了容器启动时自动执行/docker-entrypoint-initdb.d文件夹下的脚本的功能(包括shell脚本和sql脚本) 。docker-entrypoint.sh中下面这段代码就是干这事儿的。 # usage: process_init_file FILENAME MYSQLCOMMAND... # ie: process_init_file foo.sh mysql -uroot # (process a single initializer file, based on its extension. we define this # function here, so that initializer scripts (*.sh) can use the same logic, # potentially recursively, or override the logic used in subsequent calls) process_init_file() { local f="$1"; shift local mysql=( "$@" ) case "$f" in *.sh) echo "$0: running $f"; . "$f" ;; *.sql) echo "$0: running $f"; "${mysql[@]}" < "$f"; echo ;; *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | "${mysql[@]}"; echo ;; *) echo "$0: ignoring $f" ;; esac echo } echo ls /docker-entrypoint-initdb.d/ > /dev/null for f in /docker-entrypoint-initdb.d/*; do process_init_file "$f" "${mysql[@]}" done 也就是说只要把初始化脚本放到/docker-entrypoint-initdb.d/文件夹MySQL容器在启动的时候就会执行这些初始化脚本。
使用自定义MySQL配置文件 MySQL的默认配置文件在/etc/mysql/my.cnf，也可以包含其他目录，例如/etc/mysql/conf.d/或者/etc/mysql/mysql.conf.d/，可以查看mysql镜像本身相关的文件和目录来确定。 如果/my/custom/config-file.cnf是自定义配置文件的路径和名称，则可以像这样启动mysql容器（请注意，此命令中仅使用自定义配置文件的目录路径）： docker run -d --name some-mysql \ -v /my/custom:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=my-secret-pw \ mysql:tag 这将启动一个新容器some-mysql，其中MySQL实例使用/etc/mysql/my.cnf和/etc/mysql/conf.d/config-file.cnf中的组合启动设置，后者的设置优先。 不带cnf文件进行自定义配置 许多配置选项可以作为标志传递给mysqld。这将使您可以灵活地自定义容器，而无需cnf文件。例如，如果要更改所有表的默认编码和排序规则以使用UTF-8（utf8mb4），只需运行以下命令： docker run -d --name some-mysql \ -e MYSQL_ROOT_PASSWORD=my-secret-pw \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_unicode_ci \ mysql:tag 如果您想查看可用选项的完整列表，请运行： docker run -it --rm mysql:tag --verbose --help 环境变量 启动mysql镜像时，可以通过在docker run命令行上传递一个或多个环境变量来调整MySQL实例的配置。 请注意，如果使用已包含数据库的数据目录启动容器，则以下任何变量都不会产生任何影响：任何预先存在的数据库在容器启动时始终保持不变。 具体可以查看这个网站：https://dev.mysql.com/doc/refman/5.7/en/environment-variables.html MYSQL_ROOT_PASSWORD 此变量是必需的，并指定将为MySQL超级用户帐户设置的密码。在上面的例子中，它被设置为my-secret-pw。 MYSQL_DATABASE 此变量是可选的，允许指定要在镜像启动时创建的数据库的名称。如果提供了用户/密码（见下一个变量），则该用户将被授予对该数据库的超级用户访问权限（对应于GRANT ALL）。 MYSQL_USER，MYSQL_PASSWORD 这些变量是可选的，可以结合使用来创建新用户并设置该用户的密码。此用户将被授予MYSQL_DATABASE变量指定的数据库的超级用户权限（见上一个变量）。这两个变量都是创建用户所必需的。 请注意，不需要使用此机制来创建超级用户（root），默认情况下会使用MYSQL_ROOT_PASSWORD变量指定的密码创建该用户。 MYSQL_ALLOW_EMPTY_PASSWORD 这个变量是可选的，设置为yes以允许使用root用户的空密码启动容器。 注意：建议不要将此变量设置为yes，除非确实知道自己在做什么，因为这会使MySQL实例完全不受保护，从而允许任何人获得完整的超级用户访问权限。 MYSQL_RANDOM_ROOT_PASSWORD 这是变量是可选的。设置为yes以为root用户生成随机初始密码（使用pwgen）。生成的root密码将打印到stdout（GENERATED ROOT PASSWORD：.....）。 MYSQL_ONETIME_PASSWORD 初始化完成后，将root（不是MYSQL_USER中指定的用户！）用户设置为过期，在首次登录时强制更改密码。 注意：仅在MySQL 5.6+上支持此功能。在MySQL 5.5上使用此选项将在初始化期间抛出适当的错误。 Docker Secrets 作为通过环境变量传递敏感信息的替代方法，_FILE可以附加到先前列出的环境变量，从而使初始化脚本从容器中存在的文件加载这些变量的值。特别是，这可以用于从存储在/run/secrets/文件中的Docker机密加载密码。如下： $ docker run --name some-mysql \ -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root \ -d mysql:tag 目前，仅支持: MYSQL_ROOT_PASSWORD， MYSQL_ROOT_HOST， MYSQL_DATABASE， MYSQL_USER和MYSQL_PASSWORD。 在Kubernetes平台中，使用Secret对象来实现这个机制。 初始化一个新的实例 首次启动容器时，将创建具有指定名称的新数据库，并使用提供的配置变量进行初始化。此外，它将执行扩展名为.sh，.sql和.sql.gz的文件，这些文件位于/docker-entrypoint-initdb.d中。 文件将按字母顺序执行。 通过挂载一个SQL dump 到该目录并提供相应的数据来轻松完成自定义的mysql服务。 默认情况下，SQL文件将导入到MYSQL_DATABASE变量指定的数据库中。 注意事项 存储数据的位置 有多种方法存储Docker容器中运行的应用程序使用的数据。包括： 让Docker通过使用自己的内部卷将数据库文件写入主机系统上的磁盘来管理数据库数据的存储。 这是默认设置，对用户来说简单且相当透明。 缺点是对于运行在主机上的其他工具或应用（例如外部容器）来说，数据可能很难定位到。 在主机系统（容器外部）上创建一个数据目录，并将其挂载到容器内可见的目录中。 这将数据库文件放置在主机系统上的已知位置，并使主机系统上的工具和应用程序可以轻松访问这些文件。 缺点是用户需要确保目录存在，例如主机系统上的目录权限和其他安全机制已正确设置。 例子 在主机系统上的适当卷上创建数据目录，例如/my/own/datadir 使用如下命令启动mysql容器 $ docker run -d --name some-mysql \ -v /my/own/datadir:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=my-secret-pw \ mysql:tag 该命令的-v /my/own/datadir：/var/lib/mysql部分将/my/own/datadir目录从底层主机系统挂载到容器内的/var/lib/mysql，默认情况下MySQL将数据文件写在这里。 在MySQL init完成之前没有连接 如果在容器启动时没有初始化数据库，则将创建默认数据库。 虽然这是预期的行为，但这意味着在初始化完成之前它不会接受传入的连接。 当使用自动化工具（例如docker-compose）同时启动多个容器时，这可能会导致问题。 如果尝试连接到MySQL的应用程序无法处理MySQL停机或等待MySQL正常启动，则可能需要在服务启动之前进行连接重试循环。 有关官方镜像中此类实现的示例，请参阅WordPress或Bonita。 针对已有数据库的用法 如果使用已包含数据库的数据目录（特别是mysql子目录）启动mysql容器实例，应该从运行命令行中省略MYSQL_ROOT_PASSWORD变量;这个变量在任何情况下都会被忽略，并且不会以任何方式更改预先存在的数据库。 作为任意用户运行 如果目录的权限已经设置正确（例如针对已有数据库运行，如上一小节所写）需要使用特定的UID/GID运行mysqld，可以使用--user设置为任何值（root/0除外）来调用此镜像，以实现所需的访问/配置： $ mkdir data $ ls -lnd data drwxr-xr-x 2 1000 1000 4096 Aug 27 15:54 data $ docker run -d --name some-mysql \ -v "$PWD/data":/var/lib/mysql \ --user 1000:1000 \ -e MYSQL_ROOT_PASSWORD=my-secret-pw \ mysql:tag 创建数据库备份（dump） 大多数常规工具都可以使用，尽管在某些情况下它们的使用可能有点复杂，以确保它们可以访问mysqld服务器。确保这一点的一种简单方法是使用docker exec并从同一容器运行该工具，类似于以下内容： docker exec some-mysql \ sh -c 'exec mysqldump \ --all-databases -uroot \ -p"$MYSQL_ROOT_PASSWORD"' \ > /some/path/on/your/host/all-databases.sql
mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。 需要存储emoji的时候，MySQL需要设置字符集为utf8mb4，这样才能存储占四个字节的字符，默认的utf8只能存储占三个字节的字符,这是mysql的一个遗留问题。 初始化配置 容器 使用MySQL容器修改配置看这里，不带cnf文件进行自定义配置这一节。 非容器 直接修改my.cnf配置文件即可，一般在/etc/mysql或者/etc/mysql/conf.d等目录下。 修改JDBC连接串 JDBC URL 由下面这几部分组成。 protocol//[hosts][/database][?properties] 重点看[?properties]的配置，以key=value的形式，用&连接多个配置参数，且key是大小写敏感的（两个只有大小写不同的key会引起冲突）。 参数 说明 默认值 useUnicode 是否使用Unicode字符集，这是下一个参数的前置条件 false characterEncoding 当useUnicode设置为true时，指定字符编码 false autoReconnect 当数据库连接异常中断时，是否自动重新连接？ false autoReconnectForPools 是否使用针对数据库连接池的重连策略 false failOverReadOnly 自动重连成功后，连接是否设置为只读？ true maxReconnects autoReconnect设置为true时，重试连接的次数 3 initialTimeout autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒 2 connectTimeout 和数据库服务器建立socket连接时的超时，单位：毫秒。 0表示永不超时 0 socketTimeout socket操作（读写）超时，单位：毫秒。 0表示永不超时 0 修改运行中的MySQL 查看建表、建库时，默认的字符集配置，来确定是否要修改。 show create table <表名> show create database <库名> 使用客户端连接数据库的时候，可以指定默认编码： mysql -u root -p --default-character-set=utf8mb4 连接的时候，如果没有指定字符集，则取配置文件中的值；否则，取指定的字符集。 配置文件中的内容类似这样： [client] default-character-set=utf8mb4 使用不同的字符集连接数据库的时候，会影响下面 3 个变量： character_set_client character_set_connection character_set_results # 动态修改 SET character_set_client = utf8mb4; SET character_set_connection = utf8mb4; SET character_set_results = utf8mb4; # 下面这个设置与上面三个等价 SET NAMES utf8mb4; 当这 3 个变量值与数据库的字符集不统一的时候，就有可能出错或乱码。 character_set_client 指客户端请求内容的字符集， character_set_connection 指服务器处理内容的字符集， character_set_results 指服务器返回的响应的字符集。 服务器收到请求时，会将请求以 character_set_client 的字符集进行解码，然后以 character_set_connection 的字符集进行编码，然后丢给服务器处理。 待处理结束后，又将结果使用 character_set_connection 的字符集进行解码，然后使用 character_set_results 的字符集进行编码返回。 修改列 ALTER TABLE <表名> MODIFY <列名> varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题'; 修改表 为了让新增的列的字符串也是 utf8mb4 的字符集，修改表的字符集。 ALTER TABLE <表名> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 修改库 如果想让新增的表默认也是这个字符集，可以修改数据库的字符集。 ALTER DATABASE <库名> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 理解 在进行修改的时候，CHARACTER好理解，那么COLLATE是什么意思呢？ 维基百科翻译“文字排序”。如数值序或者字母序 。 COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，MySQL建索引的时候，如果索引列是字符类型，也会影响索引创建。凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。 COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。 Latin1编码的默认COLLATE为latin1_swedish_ci， GBK编码的默认COLLATE为gbk_chinese_ci， utf8mb4编码的默认值COLLATE为utf8mb4_general_ci。 很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写无关。
从binlog恢复数据步骤： 根据出现问题的事件定位到需要恢复的binlog位置 清空数据库，将全量备份恢复 根据binlog的位置恢复 定位binlog的位置 查看binlog是否开启： mysql> show variables like 'log_%'; +----------------------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | stderr | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | ON | | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_slave_statements | OFF | | log_statements_unsafe_for_binlog | ON | | log_throttle_queries_not_using_indexes | 0 | | log_timestamps | UTC | +----------------------------------------+----------------------------------------+ 18 rows in set (0.00 sec) 查看binlog日志列表： mysql> show logs; ERROR 1064 (42000): You have an error in your SQL syntax; \ check the manual that corresponds to your MySQL server version for the right syntax to use near 'logs' at line 1 mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 3091158 | No | | binlog.000002 | 141156437 | No | +---------------+-----------+-----------+ 2 rows in set (0.17 sec) 查看master状态，也就是最新一个binlog日志编号名称和最后一个操作事件pos结束位置： mysql> show master status; +---------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+-----------+--------------+------------------+-------------------+ | binlog.000002 | 141156437 | | | | +---------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 刷新log日志，将会产生一个新编号的binlog日志文件： mysql> flush logs; 如果需要清空原来的binlog： mysql> reset master; 查看binlog内容： # shell方式 mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 # 带条件查询 mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \ --start-datetime="2019-03-01 00:00:00" \ --stop-datetime="2019-03-10 00:00:00" \ --start-position="5000" \ --stop-position="20000" # binlog输入出下 # at 21019 # 190308 10:10:09 server id 1 end_log_pos 21094 # CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0 SET TIMESTAMP=1552011009/*!*/; BEGIN /*!*/; 每个字段的含义： position: 位于文件中的位置，即第一行的（# at 21019）,说明该事件记录从文件第21019个字节开始 timestamp: 事件发生的时间戳，即第二行的（#190308 10:10:09） server id: 服务器标识（1） end_log_pos 表示下一个事件开始的位置（即当前事件的结束位置+1） thread_id: 执行该事件的线程id （thread_id=113） exec_time: 事件执行的花费时间 error_code: 错误码，0意味着没有发生错误 type:事件类型Query mysql客户端查看binlog： mysql> show binlog events in 'binlog.000002' from 968 limit 10; +---------------+------+----------------+-----------+------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+------------- | binlog.000002 | 968 | Anonymous_Gtid | 1 | 1047 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1047 | Query | 1 | 1256 | \ ALTER USER 'cuishifeng'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*10320381F36BE49A18F09B06A4BC005223975101' /* xid=12 */ | | binlog.000002 | 1256 | Anonymous_Gtid | 1 | 1333 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1333 | Query | 1 | 1423 | flush privileges | | binlog.000002 | 1423 | Anonymous_Gtid | 1 | 1500 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1500 | Query | 1 | 1646 | GRANT ALL PRIVILEGES ON *.* TO 'cuishifeng'@'%' /* xid=70 */ | | binlog.000002 | 1646 | Anonymous_Gtid | 1 | 1723 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1723 | Query | 1 | 1813 | flush privileges | | binlog.000002 | 1813 | Anonymous_Gtid | 1 | 1890 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1890 | Query | 1 | 1968 | FLUSH TABLES | +---------------+------+----------------+-----------+------------- 10 rows in set (0.00 sec) # 从最早的binlog开始 mysql> show binlog events; 恢复binlog # 全量导入 mysql> source /var/lib/mysql/database-backup.sql # 根据时间恢复 mysqlbinlog --start-datetime="2013-11-29 13:18:54" --stop-datetime="2013-11-29 13:21:53" \ --database=zyyshop binlog.000002 | mysql -uroot -p123456 # 根据位置恢复 mysqlbinlog --start-position=293963814 --stop-position=346091760 --database=academy | mysql -uroot -pmysql 全量导入优化 加快source的一些MySQL参数： log_bin=OFF innodb_flush_log_at_trx_commit=0 sync_binlog max_allowed_packet=500M 注意，全局变量和会话级别变量的区别，使用global参数，即set global max_allowed_packet=500M。 innodb_flush_log_at_trx_commit 提交事务的时候将 redo 日志写入磁盘中，(所谓的 redo 日志，就是记录下来你对数据做了什么修改)。 如果要提交一个事务，此时就会根据一定的策略把 redo 日志从 redo log buffer 里刷入到磁盘文件里去。 此时这个策略是通过 innodb_flush_log_at_trx_commit 来配置的，它的配置选项： 值为0 : 提交事务的时候，不立即把 redo log buffer 刷入磁盘文件，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。 值为1 : 提交事务的时候，就必须把 redo log buffer 刷入磁盘文件，只要事务提交成功，redo log 必然在磁盘。 注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。 值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。 sync_binlog 该参数控制着二进制日志写入磁盘的过程，他的配置选项： 0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。 1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。 N：每写N次操作系统缓冲就执行一次刷新操作。 max_allowed_packet max_allowed_packet 参数（单位字节 ）限制Server接受的数据包大小。
官方工具mysqldumpslow，查看慢查询之前，需要对MySQL进行一些配置操作。 mysql> show variables like '%query%'; +------------------------------+-----------------------------------+ | Variable_name | Value | +------------------------------+-----------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/tdh524-01-slow.log | +------------------------------+-----------------------------------+ # 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; # 更新配置 FLUSH LOGS; # 查找完成之后，要关闭 SET GLOBAL slow_query_log = 'OFF'; # 更新配置 FLUSH LOGS; slow_query_log_file : 慢查询日志存储位置 long_query_time：运行查询执行的时长，超过这个时长，则认为是慢查询 log_queries_not_using_indexes：是否记录不使用索引的查询 slow_query_log：是否开始慢查询日志 mysqldumpslow /var/lib/mysql/tdh524-01-slow.log mysqldumpslow用法
基于主从同步实现高可用、高性能的MySQL架构，有如下三种方案，权衡对比如下。 通常备库设置为readonly模式（对超级权限无效），以此来判断该节点为从节点。可以防止在备库上出现误操作，防止切换逻辑有BUG，在切换过程中出现双写，造成主备不一致。 方案 高可用 可能丢数据 性能 一主一从（异步复制，手动切换） 否 可控 好 一主一从（异步复制，自动切换） 是 是 好 一主二从（同步复制，自动切换） 是 否 差 MySQL 自身就提供了主从复制的功能，通过配置就可以让一主一备两台 MySQL 的数据库保持数据同步，具体的配置方法可以参考MySQ官方文档。 从库的数据是有可能比主库上的数据旧一些的，这个主从之间复制数据的延迟，称为“主从延迟”。正常情况下，主从延迟基本都是毫秒级别，你可以认为主从就是实时保持同步的。麻烦的是不正常的情况，一旦主库或者从库繁忙的时候，有可能会出现明显的主从延迟。 主从架构 MySQL内置有binlog（实时的增量备份）和relay log两种日志文件。 MySQL 也支持同步复制，开启同步复制时，MySQL 主库会等待数据成功复制到从库之后，再给客户端返回响应。配置多个从库来解决从库宕机阻塞主库的问题。 实现步骤 将所有的DML操作记录在binlog中 binlog中的数据内容，通过网络发送给从数据库中的relay log中 从数据库通过解析relay log记录，对主数据库进行备份操作 mysqlbinlog工具可以解析并查看binlog中的内容。mysqlbinlog -vv data/master.000001 --start-position=8900; mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-master-0-bin | | log_bin_index | /var/lib/mysql/mysql-master-0-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------+ 6 rows in set (0.00 sec) mysql> show master status; +---------------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------------+-----------+--------------+------------------+-------------------+ | mysql-master-0-bin.000053 | 464152221 | | | | +---------------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 可以看到当前这个数据库已经开启了 binlog，log_bin_basename 表示 binlog 文件在服务器磁盘上的具体位置。 show master status命令可查看当前 binlog 的状态，显示正在写入的 binlog 文件，及当前的位置。 假设每天凌晨用 mysqldump 做一个全量备份，然后开启了 binlog，有了这些，就可以把数据恢复到全量备份之后的任何一个时刻。 使用binlog # 1. 恢复全量备份的数据 mysql -uroot test < dump.sql # 2. 恢复binlog记录的数据 mysqlbinlog --start-datetime "2020-02-20 00:00:00" --stop-datetime "2020-02-20 15:09:00" /usr/local/var/mysql/binlog.000001 | mysql -uroot MySQL的官方备份和恢复文档。 注意点： 全量备份的数据要保存在不同的服务器上 binlog的回放起始时间可以比全量备份的时间稍微早一点，确保恢复数据的完整性 因为回放 binlog 的操作是具备幂等性的（为了确保回放幂等，需要设置 binlog 的格式为 ROW 格式)，多次操作和一次操作对系统的影响是一样的，所以重复回放的那部分 binlog 并不会影响数据的准确性。 注意事项 不能把主从同步作为数据备份的唯一操作 主从同步需要依赖良好的网络环境，网络异常会加大备份的延迟，甚至造成备份的失败 对数据的物理文件备份也是必不可少的 海量请求会使数据库的读写操作处于长期的高并发环境中，为了保证高并发环境下的数据安全，需要设计数据库的事务隔离级别，或者采用一些加锁机制。 读写分离 将数据库的读操作和写操作分离，将大部分的读操作汇聚到从数据库中，将相对较少的写操作集中在主数据中。读写分离后： 只需要对写操作进行加锁等数据安全处理 对要被读的数据进行冗余备份，从而实现并发读访问的负载均衡 用户与数据库之间的拦截处理：读写分离，分库分表等。 分库分表 分库：（库的容量不够，拆分数据库） 分表：（表的容量不够，拆分单表） 原理：通过算法将对同一对象的写操作映射到多个数据库形成一对多的关系，读操作需要在拆分后的库或者表中进行数据拼接操作。 分库分表都是水平拆分。基于微服务可以采用垂直拆分，让每个微服务独立的存储到一个数据库里边。 中间件 MyCat实现分库分表和读写分离操作。 中间件单点故障问题。 部署去中心化的中间件集群，使用多个MyCat实例，通过一个虚拟IP来访问。 Zookeeper，选举 HAProxy，虚拟IP Keepalived，心跳 MySQL优化 字段类型选取 数据库缓存 关闭无用服务 数据库参数调优 选取适合的存储引擎 SQL语句编写 索引处理 可以借助explain关键字来查询SQL执行计划，在通过SQL执行计划来进行具体的分析和调优。 慢查询日志 使用慢查询日志或者mysqldumpslow等一些慢查询工具，直接找到性能较低的SQL语句，从而有针对性的进行优化。 海量请求处理方案 限流 削峰填谷（使用队列实现） 缓存 非关系型数据库