• 【数据库】MySQL基准测试工具Sysbench
    Sysbench是一个模块化的、跨平台、多线程基准测试工具主要用于评估测试各种不同系统参数下的数据库负载情况。它主要包括以下几种方式的测试:1、CPU性能2、磁盘IO性能 3、调度程序性能4、内存分配及传输速度5、POSIX线程性能6、数据库性能(OLTP基准测试)目前Sysbench主要支持 MySQL、PostgreSQL、Oracle 这3种数据库。源码安装:1)从http://sourceforge.net/projects/sysbench 下载源码包。2)tar -xzvf sysbench-0.4.8.tar.gz3)cd sysbench-0.4.8 ./configure && make && make installstrip/usr/local/bin/sysbench注:1)以上方法适用于 MySQL 安装在标准默认目录下的情况,如Mysql安装在/usr/local/mysql 下 ./configure --with-mysql-includes=/usr/local/mysql/include--with-mysql-libs=/usr/local/mysql/lib && make && makeinstall 2)如果想要让 sysbench 支持 pgsql/oracle ,在编译的时候加上参数 --with-pgsql 或者 --with-oracle(这2个参数默认是关闭的,只有 MySQL 是默认支持的)通过yum管理工具安装:1)yum install sysbench:Installed: sysbench.x86_64 0:0.4.12-5.el6Dependency Installed: postgresql-libs.x86_64 0:8.4.20-1.el6_5Complete!Sysbench常用参数1、--num-threads=300:启动的线程数2、--max-time=60:运行时间3、--test=oltp:测试类型4、--db-driver=mysql:测试的数据库类型5、--oltp-table-size=1500000:表初始化数据量(行)6、--oltp-nontrx-mode=insert:测试非事务模式7、--mysql-table-engine=innodb:测试的表引擎8、--mysql-host=localhost:要测试的主机9、--mysql-port=5100:mysql端口10、--mysql-user=root --mysql-password=** :mysql用户名和密码11、--mysql-socket=/home/mysql/mysql/tmp/mysql.sock :mysql本地socket套接字路径 Sysbench使用步骤1)清理数据:sysbench --num-threads=300 --max-requests=0--max-time=60 --test=oltp --db-driver=mysql --oltp-table-size=1500000--oltp-nontrx-mode=insert --oltp-test-mode=nontrx --mysql-table-engine=innodb--mysql-host=localhost --mysql-port=5100 --mysql-db=test --mysql-user=_root--mysql-password=cnN4HBH98lublJXMf5hPEp48 --mysql-socket=/home/mysql/mysql/tmp/mysql.sock cleanupsysbench 0.4.12: multi-threaded system evaluation benchmarkDropping table ´sbtest´...Done.2)准备数据:sysbench --num-threads=300 --max-requests=0--max-time=60 --test=oltp --db-driver=mysql --oltp-table-size=1500000--oltp-nontrx-mode=insert --oltp-test-mode=nontrx --mysql-table-engine=innodb--mysql-host=localhost --mysql-port=5100 --mysql-db=test --mysql-user=_root--mysql-password=cnN4HBH98lublJXMf5hPEp48 --mysql-socket=/home/mysql/mysql/tmp/mysql.sock preparesysbench 0.4.12: multi-threaded system evaluation benchmarkCreating table ´sbtest´...Creating 1500000 records in table´sbtest´...3)执行测试:sysbench --num-threads=300 --max-requests=0--max-time=60 --test=oltp --db-driver=mysql --oltp-table-size=1500000--oltp-nontrx-mode=insert --oltp-test-mode=nontrx --mysql-table-engine=innodb--mysql-host=localhost --mysql-port=5100 --mysql-db=test --mysql-user=_root--mysql-password=cnN4HBH98lublJXMf5hPEp48 --mysql-socket=/home/mysql/mysql/tmp/mysql.sock run sysbench 0.4.12: multi-threaded system evaluation benchmark========================OLTP test statistics: queries performed: read: 0 write: 110707 other: 0 total: 110707 transactions: 110707 (1841.17 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 110707 (1841.17 per sec.) other operations: 0 (0.00 per sec.)
  • [技术干货] 如何在云上安装Mysql
    Mysql是一款很常见的关系型数据库,虽然目前在逐渐式微,并且分裂出了MariaDB, 但仍然是很多的轻型应用的首选。那么如何在华为云上部署Mysql呢,笔者经过实验,发现与传统部署模式完全一致,无需做任何改动,现提供以下安装步骤,仅作抛砖引玉:Linux/UNIX上安装MysqlLinux平台上推荐使用RPM包来安装Mysql,MySQL AB提供了以下RPM包的下载地址:[*]MySQL - MySQL服务器。你需要该选项,除非你只想连接运行在另一台机器上的MySQL服务器。[*]MySQL-client - MySQL 客户端程序,用于连接并操作Mysql服务器。[*]MySQL-devel - 库和包含文件,如果你想要编译其它MySQL客户端,例如Perl模块,则需要安装该RPM包。[*]MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*),使用MySQL。[*]MySQL-bench - MySQL数据库服务器的基准和性能测试工具。以下安装Mysql RMP的实例是在SuSE Linux系统上进行,当然该安装步骤也适合应用于其他支持RPM的Linux系统,如:Centos。安装步骤如下:使用root用户登陆你的Linux系统。下载Mysql RPM包,下载地址为:MySQL 下载。通过以下命令执行Mysql安装,rpm包为你下载的rpm包:以上安装mysql服务器的过程会创建mysql用户,并创建一个mysql配置文件my.cnf。你可以在/usr/bin和/usr/sbin中找到所有与MySQL相关的二进制文件。所有数据表和数据库将在/var/lib/mysql目录中创建。以下是一些mysql可选包的安装过程,你可以根据自己的需要来安装:Window上安装MysqlWindow上安装Mysql相对来说会较为简单,你只需要载 MySQL 下载中下载window版本的mysql安装包,并解压安装包。双击 setup.exe 文件,接下来你只需要安装默认的配置点击"next"即可,默认情况下安装信息会在C:\mysql目录中。接下来你可以通过"开始" =》在搜索框中输入 " cmd" 命令 =》 在命令提示符上切换到 C:\mysql\bin 目录,并输入一下命令:如果安装成功以上命令将输出一些mysql启动及InnoDB信息。验证Mysql安装在成功安装Mysql后,一些基础表会表初始化,在服务器启动后,你可以通过简单的测试来验证Mysql是否工作正常。使用 mysqladmin 工具来获取服务器状态:使用 mysqladmin 命令俩检查服务器的版本,在linux上该二进制文件位于 /usr/bin on linux ,在window上该二进制文件位于C:\mysql\bin 。linux上该命令将输出以下结果,该结果基于你的系统信息:如果以上命令执行后未输入任何信息,说明你的Mysql未安装成功。使用 MySQL Client(Mysql客户端) 执行简单的SQL命令你可以在 MySQL Client(Mysql客户端) 使用 mysql 命令连接到Mysql服务器上,默认情况下Mysql服务器的密码为空,所以本实例不需要输入密码。命令如下:以上命令执行后会输出 mysql>提示符,这说明你已经成功连接到Mysql服务器上,你可以在 mysql> 提示符执行SQL命令:Mysql安装后需要做的Mysql安装成功后,默认的root用户密码为空,你可以使用以下命令来创建root用户的密码:现在你可以通过以下命令来连接到Mysql服务器:注意:在输入密码时,密码是不会显示的,请确保输入正确。Linux系统启动时启动 MySQL如果你需要在Linux系统启动时启动 MySQL 服务器,你需要在 /etc/rc.local 文件中添加以下命令:同样,你需要将 mysqld 二进制文件添加到 /etc/init.d/ 目录中。
  • [技术干货] MySQL多源复制和半同步改进
    一、 必要的配置参数从库:--master-info-repository=TABLE--relay-log-info-repository=TABLE 如果不想停止mysql,也可以动态修改,如下: STOP SLAVE; SET GLOBALmaster_info_repository = 'TABLE';SETGLOBAL relay_log_info_repository = 'TABLE';二、 如果主1是基于GTID,那么应该这样建立主从关系CHANGEMASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451,MASTER_PASSWORD='', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1';这里最主要的就是后面的for channel …为每个主开通一个复制通道。三、 如果主2是基于binlog,那么应该这样建立主从关系CHANGEMASTER TO MASTER_HOST='master2', MASTER_USER='rpl', MASTER_PORT=3451,MASTER_PASSWORD='' MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FORCHANNEL 'master-2';开启第二个复制通道四、 启动复制startslave thread_types (默认会启动所有配置的复制通道)如果想启动某个复制通道,需要使用下面的语法:STARTSLAVE thread_types FOR CHANNEL channel;五、 停止复制和启动复制类似,STOPSLAVE thread_types; (默认会关闭所有配置的复制通道如果想关闭某个复制通道,需要使用下面的语法:STOPSLAVE thread_types FOR CHANNEL channel;六、 ResetRESETSLAVE; (默认会reset所有复制通道)RESETSLAVE FOR CHANNEL channel; (reset某个复制通道)七、 对于复制通道的监控SHOWSLAVE STATUS; (显示所有复制通道,一个通道一行)SHOWSLAVE STATUS FOR CHANNEL channel_name. ; (显示单个复制通道)可以查看Performance Schema tablesSELECT *FROM replication_connection_status\G;八、 复制通道复制通道的名字基本可以随便取,必须唯一,但是有两个名字是系统保留的: group_replication_applier group_replication_recovery九、 对于多通道的复制相关命令• CHANGEMASTER TO•START SLAVE•STOP SLAVE•SHOW RELAYLOG EVENTS•FLUSH RELAY LOGS•SHOW SLAVE STATUS• RESETSLAVE 这些命令都可以跟上FOR CHANNEL channel_name,但是对于group_replication_recovery这个通道,下面两个命令是不允许的。 • START SLAVE • STOP SLAVE十、 半同步复制改进在MySQL5.7中,半同步复制技术升级为全新的Loss-lessSemi-Synchronous Replication,引进了rpl_semi_sync_master_wait_point变量,它可以取值两种:AFTER_SYNC: (默认就是这个值)AFTER_COMMIT: MySQL5.6半同步存在的问题:当客户提交一个事务(这个时候主库已经将事务提交给引擎了),master把binlog发送给slave,假如在发送期间,网络出现问题,slave并没有真正收到binlog,默认等待10秒后就会返回给客户端。如果恰好这时主库crash了,从库就和主库的数据不一致了。 那么能否当客户在主库提交一个事务时,不立即提交给引擎。而是等到从库收到了binlog后,主库才会将事务提交给引擎。如果中途出现网络问题,那么主库是看不到事务提交成功的。(这里有一个疑问,如果主库提交给引擎出错了怎么办,是不是还会发送一个rollback的动作到从库) 前者就是传统的模式:AFTER_COMMIT后者是新的模式: AFTER_SYNC
  • [技术干货] MGR(MySQL Group Replication介绍(一)
    IntroductionMySQLGroup Replication is a MySQL pluginand it builds on the existing MySQL replication infrastructure, takingadvantage of features such as the binary log, row-based logging, and globaltransaction identifiers. It integrates with current MySQLframeworks, such as the performance schema or pluginand service infrastructures. The following figure presents a block diagramdepicting the overall architecture of MySQL Group Replication.lTosummarize, MySQL Group Replication provides ahighly available, highly elastic, dependable MySQL service.
  • [技术干货] 最佳实践——购买RDS MySQL实例
    在购买RDS MySQL实例过程中,用户需要设置各种选项。针对这些选项,本文档向您提供设置参考,使您购买的实例更符合要求,以避免后续不必要的变更,减少维护工作量。购买RDS MySQL实例的具体操作,请参见《关系型数据库购买指南》的“购买RDS MySQL实例”章节。1.1 选择付费方式RDS支持付费方式如下:[*]包年包月[*]按需付费从价格上看,在相同时长的情况下,包年包月比较优惠。[*]对于长期使用的数据库实例,建议在购买时“付费方式”选择“包年包月”。[*]对于临时使用的数据库实例,建议在购买时“付费方式”选择“按需付费”。RDS MySQL实例成功购买后,不支持变更付费方式,请谨慎选择付费方式。1.2 选择区域华为云在不同区域(Region),支持不同可用分区(Available zone,简称AZ)。表1-1 RDS支持的Region和AZ中国华北区1具有2个可用区(Available zone),其它区域为单可用区。如果需要跨可用区高可用特性,区域请选择中国华北区1。1.3 选择HA和同步模型选择HA(High Availability)[*]如果购买MySQL实例时,不开启HA,则购买的实例为单实例。当该实例出现系统崩溃时,则没有任何替代实例接管业务读写请求。[*]如果购买MySQL实例时,开启HA,RDS会自动为用户创建主备实例,组成双机热备高可用架构。当主实例宕机时,RDS会快速将备实例升为主实例并接管业务读写请求。该过程为系统自动完成,无需用户介入参与。因此,在购买实例时,用于生产的数据库,建议开启HA;用于测试的数据库,可以选择不开启HA,以减少性能损耗和节省资源成本。选择同步模型MySQL主从复制原理流程如下:1. 主实例数据库将数据变更记录到二进制日志“Binary log”中。该部分变更记录称作二进制日志事件,即binary log events。2. 备实例数据库将主实例数据库的binary log events,拷贝到备实例数据库的中继日志“Relay log”。3. 备实例数据库重做“Relay log”中的事件,将变更应用到它自己的数据库。对于主实例数据库是否等待备实例数据库收到“Binary log”才响应客户端,不同的同步模型(也称为复制模型),处理方式存在差异。根据处理方式的不同,同步模型可以分为“异步”和“半同步”。[*]异步RDS默认异步复制,即主实例数据库执行完Commit后,在主实例数据库写入“Binarylog”日志后便可成功响应客户端,无需等“Binary log”日志传到备实例数据库,一旦主实例数据库系统崩溃,则有可能会丢失日志。[*]半同步等待备实例数据库接收到“Binarylog”日志,并成功写入“RelayLog”之后,才返回Commit操作成功给客户端。事务成功提交后,至少有两份日志记录,一份在主实例数据库“Binary log”上,另一份在备实例数据库“Relay Log”上,进一步保证数据完整性。半同步复制很大程度取决于主备网络RTT(往返时延),以“semisync_master”和“semisync_slave”插件形式存在。从上面的复制原理可以看出,采用异步复制模型,可减少性能损耗,但可能存在数据丢失风险。选择半同步复制模型,则可确保数据完整性,但存在性能损耗,按目前测试结果,性能会降低10%左右。最佳实践[*]用于生产的数据库建议开启HA,用于测试的数据库可关闭。[*]如果数据库的性能优先,并且能容忍一定的数据丢失,推荐HA选择异步复制。[*]如果数据库的数据完整性优先,并且能容忍一定的性能损耗,推荐HA选择半同步复制。[*]1.4 选择数据库版本选择版本RDS for MySQL支持的数据库版本,如表1-2所示。表1-2 MySQL版本列表5.7版本与5.6兼容,除了性能上有提升外,还具有如下新特性和改进功能:[*]安全提升[*]在线索引更名[*]InnoDB增强[*]JSON支持[*]新增sys schema[*]在线修改复制关系[*]支持多源复制[*]支持多线程复制(MTS)[*]半同步增强其中,最值得关注的是,支持多线程复制特性。它不同于MySQL 5.6中schema级别的多线程复制,而是提供了真正的多线程复制能力,大大降低复制延迟。最佳实践[*]新上线的业务,推荐使用MySQL 5.7数据库。[*]已有业务迁移上云,在充分的兼容性测试后,推荐迁移到MySQL 5.7。[*]推荐使用数据库最新的子版本。RDS暂不支持将MySQL 5.6直接升级到MySQL 5.7,后续会推出该功能。请确定好您所需的数据库版本,并在购买时选择。1.5 选择数据库实例规格RDS提供了多种实例规格,以满足不同需求。一般情况下,2核以下的实例都用于测试,不建议用作生产数据库。同时,选择何种的实例规格,取决于如下主要因素:[*]数据库吞吐量,即QPS(Query PerSecond)。[*]最大数据库连接数。常用规格测试数据,请参见表1-3:表1-3 常用规格测试数据上述测试数据基于sysbench的“oltp.lua”模型测试得出,数据量为64个表,每个表1000万条记录。建议数据库上云前做实际的测试,以确定满足业务需要的数据库实例规格。1.6 选择参数组选择参数组数据库参数组类似于数据库引擎配置值的容器,参数组中的参数可应用于一个或多个数据库实例。它相对于传统一个实例一套参数的模式,在使用上有一些差异,例如:[*]RDS提供了若干默认参数组,例如default-MySQL-5.6和default-MySQL-5.7。默认参数组不可编辑。[*]如果要修改某个参数值,需要基于默认参数组创建一个新的参数组,然后修改新参数组的参数值。[*]如果您对购买实例时选择的参数组不满意,可在创建成功后,变更该实例对应的参数组。[*]有些参数值的修改需重启实例才能生效。最佳实践推荐使用自定义参数组。将自定义参数组应用于数据库实例,不要使用默认参数组。即使购买实例时使用了默认参数组,创建成功后也可变更实例的参数组。1.7 选择存储类型RDS与云硬盘(Elastic Volume Service,简称为EVS)均采用分布式块存储作为存储资源,具有3副本冗余,数据持久性达到99.99999%。RDS支持存储类型:普通IO、高IO和超高IO。各存储类型含义如下:[*]普通IO,是由SATA存储类型组成的存储资源池,IOPS为1/GB。[*]高IO,是由SAS存储类型组成的存储资源池,IOPS为2/GB。[*]超高IO,是由SSD存储类型组成的存储资源池,IOPS为50/GB。数据库系统通常是IT系统最为重要的系统,对存储IO性能要求高,存储类型建议选择超高IO,RDS暂时不支持购买实例后变更存储类型。1.8 选择备份策略使用场景HA高可用替代不了自动备份的作用。例如,由于某种原因,某个数据库或表被恶意或误删除,虽然有HA高可用,但是主备数据库由于数据同步原因,数据同步被删除,无法还原。遇到这种情况,只能求助于自动备份。通过恢复到指定时间点(PITR)功能,用户可以将数据库实例恢复到删除前的某一时刻,还原被删除的数据。所以,自动备份具有很高的重要性。用户可根据业务情况,设置自动备份保留天数,范围为1~35天,同时,可设置自动备份开始时间。用户一旦打开自动备份策略,RDS每天会在预设时间1小时内触发一次全量备份,每5分钟进行一次增量备份(binlog备份),并将该部分备份数据上传到对象存储服务(ObjectStorage Service,简称OBS)的对象存储空间。最佳实践[*]生产数据库请务必打开自动备份策略。[*]保留天数建议至少为3天,如果要求更高的话,可增加保留天数,最大可保留35天。[*]由于备份会损耗数据库读写性能,请根据业务情况设置备份开始时间,建议选择业务低峰时间段启动自动备份
  • [技术干货] 华为数据库服务MySQL默认时区修改问题
    最近有不少客户吐槽,华为数据库服务MySQL默认时区使用的是UTC,而不是我们正常人生活的东八区时间,我们面壁......用户可以通过修改参数组的方法修改时区,方法如下:1.创建好数据库实例后,在数据库管理控制台里创建新的参数组2.点击编辑,选择time_zone参数,修改为Asia/shanghai3.将已创建的实例绑定刚刚编辑的参数组即可,如果已绑定,则不需要重新操作,时区修改不需要重启数据库
  • [技术干货] 一个经常被忽略的命令
    其实,在MySQL中,有这么一个命令status,运行后可以用来查看实例运行的时间,简单易用。有了这个命令后,不用看错误日志,也不用看进程的运行时间,就可以明确知道MySQL实例最近有没有被重启。mysql> status;--------------mysql Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using EditLine wrapper Connection id: 67914788Current database: Current user: SSL: Notin useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.6.34 Source distributionProtocol version: 10Connection: rm-bp1h9rsb31t57hetjo.mysql.rds.aliyuncs.comvia TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 11min 44 sec Threads: 15 Questions: 2505 Slow queries:3 Opens: 77 Flush tables: 1 Open tables: 70 Queries per second avg: 3.558
  • [技术干货] MySQL时区问题
    在MySQL中,和时区相关的参数有两个,分别是:system_time_zone和time_zone1. system_time_zone代表系统时区当MySQL启动的时候,会从操作系统中获取时区,用它来设置system_time_zone系统变量,一旦设置好了,以后就不能修改。 2. time_zone代表server时区,初始值为SYSTEM,表示server时区和系统时区一致。 3. 两个option太容易混淆了在启动脚本中,可以加上两个options,--timezone=timezone_name 用于设置系统时区--default-time-zone=timezone 用于设置server时区 4. 当前时区的影响影响与时区相关的显示和存储,包括:NOW(), CURTIME()还有TIMESTAMP字段 5. timezone值的设置 [*]可以设置为’SYSTEM’, 表示和系统时区一致 [*]可以设置为UTC的偏移值, 如: ‘+10:00’ 或者 ‘-6:00’ [*]可以设置为具体的时区名字, 如’Europe/Helsinki’, ‘MET’
  • [技术干货] MySQL 5.6用户资源限制
    首先有一个系统变量MAX_USER_CONNECTIONSmysql> show variables like 'MAX_USER_CONNECTIONS';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| max_user_connections | 0 |+----------------------+-------+1 row in set (0.01 sec) The maximum number of simultaneous connectionspermitted to any given MySQL user account. Avalue of 0 (the default) means “no limit.” 允许同一账号同时连接的最大数,如果为0则没有限制。如果用户对这个MAX_USER_CONNECTIONS设置了资源限制,那么session值就是这个限制值。否则,session就设置这个全局量。• Otherwise, the sessionmax_user_connections value is set to the global value. 实验如下:mysql> set global max_user_connections=5;Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_user_connections';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| max_user_connections | 5 |+----------------------+-------+ 1 row in set (0.00 sec) 创建一个账号:Create user tom@’localhost’ identified by ‘Huawei@123’;Grant all on *.* to tom@’localhost’; 我们开启5个session, 发现第六个时报错:[root@SZX1000165720 ~]# mysql -utom -pEnter password: ERROR 1203 (42000): User tom already has more than 'max_user_connections'active connections 我们来用下面的语句设置用户资源限制:mysql> GRANT ALL ON *.* TO 'tom'@'localhost' WITH MAX_QUERIES_PER_HOUR20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_user_connections';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| max_user_connections | 5 |+----------------------+-------+1 row in set (0.00 sec) [root@SZX1000165720 ~]# mysql -utom -pEnter password: ERROR 1226 (42000): User 'tom' has exceeded the 'max_user_connections'resource (current value: 2) MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_QUERIES_PER_HOUR参数的意义是一个小时区间内,某个账号所运行执行的查询次数,如果使用了query cache,则不计算在内。如果为0,则代表对这个账号没有限制。 MAX_CONNECTIONS_PER_HOUR参数的意义是一个小时区间内,某个账号连接数据的次数。如果为0,则代表对这个账号没有限制。 当我运行mysql> select * from INNODB_FT_INDEX_TABLE;ERROR 1226 (42000): User 'tom' has exceeded the 'max_questions' resource(current value: 20) 修改某个用户的资源限制有两种办法:1. GRANT USAGE ON *.* TO 'zabbix'@'localhost' WITH MAX_QUERIES_PER_HOUR100; (对于不存在的账户会报错)2. 直接修改mysq.user表的字段值
  • [技术干货] MySQL SSL连接
    RPM包安装时会在数据目录下生成很多证书文件,比如:ca-key.pemca.pemclient-cert.pemclient-key.pemprivate_key.pempublic_key.pemserver-cert.pemserver-key.pem 这些文件的作用如下:ca.pem : 作为--ssl-ca参数值,在服务器和客户端,CA证书,如果使用,服务器端和客户端必须相同server-cert.pem, server-key.pem : 在服务器端分别作为--ssl-cert和--ssl-key两个参数的值。client-cert.pem, client-key.pem:在客户端分别作为--ssl-cert和--ssl-key两个参数的值。 服务器端:--ssl-ca: 表示CA证书--ssl-cert: 表示服务器端公钥证书--ssl-key: 表示服务器端私钥 客户端:--ssl-ca: 表示CA证书--ssl-cert: 表示客户端公钥证书--ssl-key: 表示客户端私钥 服务器端要如下配置:[mysqld]ssl-ca=ca.pemssl-cert=server-cert.pemssl-key=server-key.pem 客户端连接的时候需要:mysql –ssl-ca=ca.pem –ssl-cert=client-cert.pem --ssl-key=client-key.pem 查看server端是否支持mysql> show variables like '%ssl%';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| have_openssl | YES || have_ssl | YES || ssl_ca | ca.pem || ssl_capath | || ssl_cert | server-cert.pem || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_key |server-key.pem |+---------------+-----------------+9 rows in set (0.00 sec) 我们从客户端连接数据库mysql -h100.100.184.152 -P3306 --ssl-ca=ca.pem --ssl-cert=client-cert.pem--ssl-key=client-key.pem -uroot –p 如果我们要求一个账号必须使用SSL来连接数据库,需要下面这样创建账号mysql> create user testuser@'10.21.215.86' identified by '**D';Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'10.21.215.86' REQUIRE SSL;Query OK, 0 rows affected, 1 warning (0.00 sec) 这样必须用证书连接[root@SZX1000165720 software]# mysql -h100.100.184.152 -P3306--ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -utestuser-pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 31Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rightsreserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current inputstatement. mysql> SHOW STATUS LIKE 'Ssl_cipher';+---------------+--------------------+| Variable_name | Value |+---------------+--------------------+| Ssl_cipher | DHE-RSA-AES256-SHA|+---------------+--------------------+1 row in set (0.00 sec) mysql> 否则就会报错:[root@SZX1000165720 software]# mysql -h100.100.184.152 -P3306 -utestuser-pEnter password: ERROR 1045 (28000): Access denied for user 'testuser'@'10.21.215.86'(using password: YES) 二、自己来发放证书这都是默认的证书和文件,我们来自己生成新的看看,方法如下:# Create clean environmentshell> rm -rf newcertsshell> mkdir newcerts && cd newcerts # Create CA certificateshell> openssl genrsa 2048 > ca-key.pemshell> openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -outca.pemYou are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or aDN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:GuangdongLocality Name (eg, city) [Default City]:ShenzhenOrganization Name (eg, company) [Default Company Ltd]:HuaweiOrganizational Unit Name (eg, section) []:CloudCommon Name (eg, your name or your server's hostname) []:caEmail Address []:luozhaode@huawei.com # Create server certificate, remove passphrase, and sign it# server-cert.pem = public key, server-key.pem = private keyshell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyoutserver-key.pem -out server-req.pemGenerating a 2048 bit RSA private key.....+++...................................+++writing new private key to 'server-key.pem'-----You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or aDN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:GuangdongLocality Name (eg, city) [Default City]:ShenzhenOrganization Name (eg, company) [Default Company Ltd]:HuaweiOrganizational Unit Name (eg, section) []:CloudCommon Name (eg, your name or your server's hostname) []:masterEmail Address []:luozhaode@huawei.com Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []: shell> openssl rsa -in server-key.pem -out server-key.pemshell> openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem-CAkey ca-key.pem -set_serial 01 -out server-cert.pem # Create client certificate, remove passphrase, and sign it# client-cert.pem = public key, client-key.pem = private keyshell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyoutclient-key.pem -out client-req.pem Generating a 2048 bit RSA private key.+++...............................................................+++writing new private key to 'client-key.pem'-----You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or aDN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:GuangdongLocality Name (eg, city) [Default City]:ShenzhenOrganization Name (eg, company) [Default Company Ltd]:HuaweiOrganizational Unit Name (eg, section) []:CloudCommon Name (eg, your name or your server's hostname) []:slaveEmail Address []:luozhaode@huawei.com Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []: shell> openssl rsa -in client-key.pem -out client-key.pemshell> openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem-CAkey ca-key.pem -set_serial 01 -out client-cert.pem 最后可以用下面的命令来验证:[root@SZX1000186519 newcerts]# openssl verify -CAfile ca.pemserver-cert.pem client-cert.pemserver-cert.pem: OKclient-cert.pem: OK 在主库的参数文件中:[mysqld]ssl-ca=/var/software/newcerts/ca.pemssl-cert=/var/software/newcerts/server-cert.pemssl-key=/var/software/newcerts/server-key.pem 然后在主库上创建用户:mysql> create user testuser@'100.100.185.169' identified by '**D'REQUIRE X509;Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* TO 'testuser'@'100.100.185.169';Query OK, 0 rows affected (0.01 sec) mysql> flush privileges;Query OK, 0 rows affected (0.01 sec) 这个时候使用系统自带的证书是不行的,会报错。[root@SZX1000186273 mysql]# mysql -h100.100.184.152 -P3306 -utestuser -p--ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pemEnter password: ERROR 2026 (HY000): SSL connection error: ASN: bad other signatureconfirmation 为此我们需要把主库上生成的证书信息拷贝过来,然后[root@SZX1000186273 newcerts]# mysql -h100.100.184.152 -P3306 -utestuser-p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pemEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rightsreserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current inputstatement. mysql> SHOW STATUS LIKE 'Ssl_cipher';+---------------+--------------------+| Variable_name | Value |+---------------+--------------------+| Ssl_cipher | DHE-RSA-AES256-SHA|+---------------+--------------------+1 row in set (0.01 sec) mysql> 我们在从库中进行如下的配置:CHANGE MASTER TO MASTER_HOST='100.100.184.152', MASTER_PORT=3306, MASTER_USER='testuser', MASTER_PASSWORD='**D', MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=1408, MASTER_AUTO_POSITION=0, MASTER_SSL=1, MASTER_SSL_CA='/var/software/newcerts/ca.pem', MASTER_SSL_CAPATH='/var/software/newcerts', MASTER_SSL_CERT='/var/software/newcerts/client-cert.pem', MASTER_SSL_KEY='/var/software/newcerts/client-key.pem'; 启动就可以了。如果我们将证书目录移动并且再停止复制线程,则会报错。 mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State:Waiting for master to send event Master_Host:100.100.184.152 Master_User: testuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File:mysql-bin.000035 Read_Master_Log_Pos: 2459 Relay_Log_File:relay-log.000004 Relay_Log_Pos: 320 Relay_Master_Log_File:mysql-bin.000035 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2459 Relay_Log_Space: 3039 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File:/var/software/newcerts/ca.pem Master_SSL_CA_Path:/var/software/newcerts Master_SSL_Cert:/var/software/newcerts/client-cert.pem Master_SSL_Cipher: Master_SSL_Key:/var/software/newcerts/client-key.pem Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1024 Master_UUID:75f1a32f-a4e2-11e6-8b15-286ed48**e Master_Info_File:/var/db/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slavehas read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
  • [技术干货] MySQL几个重要参数
    sync_binlogsync_binlog是MySQL的binlog写入方式。它可以设置0以上的值。1. sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。2. 当sync_binlog =N (N>0) ,MySQL 在每写 N次二进制日志binarylog时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。很明显,最安全的设置是1,即写一个binlog,同步一次。innodb_flush_log_at_trx_commit和sync_binlog两个参数,在均被设置成1的时候,被称作双1模式。这个模式下,MySQL的数据可靠性是最好的。innodb_support_xa前面几个参数已经极大的保证了MySQL不丢数据。但还是可能出现一种情况。那就是,当事物提交时,MySQL挂掉。这个时候,binlog已经落盘,而redo中事务未提交。就会回滚掉事物。而binlog是不能回滚的,于是就出现binlog有这个操作,而innodb中没有。如果有主备复制的话,就会出现主备不一致。 究其原因:写redo log和写binlog,正常情况下不是原子操作的。是两个阶段,所以要想保持一致,就必须将它作为分布式事务来考虑,这就是innodb_support_xa参数的作用。innodb_support_xa在设为ON时,会使用二阶段提交协议来保证binlog和innodb的一致
  • [技术干货] MySQL空连接占用内存
    我这里做了一个简单的实验。过程如下:1、 首先调整好参数set global max_connections=100000;set globalsort_buffer_size=104857600;set globaljoin_buffer_size=104857600;set globalread_buffer_size=104857600;set globalread_rnd_buffer_size=104857600;set global net_buffer_length=1048576; (除了这个最大值为1048576外,剩下的都取为100M)set globalbulk_insert_buffer_size=104857600;set globaltmp_table_size=104857600;2、然后模拟N多个空连接#!/bin/shi=0while true;domysql -uroot -p**D -A -NBe"select sleep(1000000)" &    //这里只能模拟执行一个select sleep(1000000),如果什么都不做貌似不好模拟出场景,基本每秒进行一个连接sleep 1((i= $i + 1))done3、运行一个内存监控小程序#!/bin/shwhile true;dofree -k >> mem.txtsleep 1done4 、最后的结果大概是这样的[root@dtvlan15 software]# head -20mem.txt total used free shared buff/cache availableMem: 7471604 1259920 5657644 8908 554040 5933408Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1260660 5656900 8908 554044 5932668Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1260800 5656760 8908 554044 5932528Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1261220 5656340 8908 554044 5932108Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1261948 5655612 8908 554044 5931380Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1262600 5654960 8908 554044 5930728Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1265540 5652012 8908 554052 5927784…[root@dtvlan15 software]# tail -20mem.txt --大概进行了800多个连接的时候的内存使用情况Mem: 7471604 1942528 4948312 8908 580764 5225908Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1943420 4947412 8908 580772 5225008Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1943912 4946860 8908 580832 5224456Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1946184 4944556 8908 580864 5222156Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1946124 4944456 8908 581024 5222064Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1946900 4943672 8908 581032 5221284Swap: 2097148 0 2097148 total used free shared buff/cache availableMem: 7471604 1947612 4942944 8908 581048 5220556Swap: 2097148 0 2097148 5、大概结论貌似一个空连接仅仅分配几百K~1M左右的样子(网上有人说256K)。即使参数设置很大。
  • [技术干货] MySQL的内存使用分析
    这个公式只能计算出应用并发跑满的最极端情况下,mysql使用的内存的最大值。这个值的意义不大。我其实是想弄明白一个连接进来不执行任何操作(空连接),mysql或者操作系统会为这个连接分配多少内存,分配的内存包含哪些部分。 Memories for variablesread_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size areallocated as & when required.根据上边这段话的意思,建立一个空连接只需要分配 thread_stack+net_buffer_length这么多空间。我怎么去验证这个问题?从内存的使用方式MySQL 数据库的内存使用主要分为以下两类:线程独享内存 和 全局共享内存 Mysql Server Memory Usage= Sum of Global Buffers + (number of Connection * Per thread memory variables). 线程独享内存上面所列举的 MySQL 线程独享内存仅仅只是所有线程独享内存中的部分,并不是全部,选择的原则是可能对MySQL 的性能产生较大的影响,且可以通过系统参数进行调节。由于以上内存都是线程独享,极端情况下的内存总体使用量将是所有连接线程的总倍数。所以各位朋友在设置过程中一定要谨慎,切不可为了提升性能就盲目的增大各参数值,避免因为内存不够而产生Out Of Memory 异常或者是严重的 Swap 交换反而降低整体性能。 全局共享内存这里所列举的各种共享内存,是我个人认为对 MySQL 性能有较大影响的集中主要的共享内存。实际上,除了这些共享内存之外,MySQL还存在很多其他的共享内存信息,如当同时请求连接过多的时候用来存放连接请求信息的back_log队列等。
  • [技术干货] root密码忘记如何处理
    一、MySQL5.6方面:当最高权限的用户密码root备忘记后,可以按如下方式来处理1. 关闭mysql进程2. 在/etc/my.cnf配置文件中加上 skip-grant-tables 3. 启动mysql进程4. 修改密码 3. 将skip-grant-tables从配置文件中删除4. 重启mysql进程二、MySQL5.7方面:如果还是按同样的方法,则会报错...需要使用下面的方法:
  • [技术干货] MySQL中的Federated引擎
    一、基本原理它严格讲不是一种新的存储引擎(不像MyISAM、InnoDB那样),它是类似于oracle的database link。 简单讲:它本身不存储数据,是用来做数据链接的。 最典型的用法就是在A实例上有一个表,正常情况下是没有办法在B实例里访问到这个表的,如果想在B中访问A实例的这个表,就可以使用FEDERATED达到目的。 1. 默认官网版本已经支持federated引擎了,但是还没有打开2. 打开方法 在/etc/my.cnf加上 federated3. 重启mysql4. 创建表 5. 实际例子
总条数:576 到第
上滑加载中