MySQL/MariaDB已被锁表运行中热复制为副本/innodb表错误Table xx doesn't exist in engine处理

从数据表热复制说起。

在执行特别慢的语句时,mysql经常会锁表。这时如果想并行执行另一个语句,但表被锁而该语句只能排队。这种情况下,希望能将被锁的表复制个副本,就可以“假”并行执行;然而杀死前语句又太可惜。所以,运行中热复制的技巧(奇技淫巧)还是有用的。

对于MyISAM表,直接复制三个文件到另一数据库中,即时生效,很方便。为了稳妥起见,最好先检查表看是否有异常;有时会因为写入而提醒表损坏,repair table 修复一下就好。

但对于InnoDB表,就要复杂一些。首先,step-0) 确保已通过MySQL服务的匹配文件,启用单文件选项 innodb_file_per_table=1 ,这样每个表会生成两个文件。但你并不能直接复制它们了,因为你将在新表上得到一条错误 Table xx doesn't exist in engine,所以这样不行。而是,step-1) 你应该在目标数据库里创建一张完全相同的表,然后 step-2) 修改新表丢弃其表空间,语句如 ALTER TABLE `xx` discard tablespace; 之后,step-3) 把源表的 .ibd文件复制过来覆盖新表的同名文件。step-4) 重新导入新表空间 ALTER TABLE `xx` import tablespace;  step-5) 最好也检查下表,至此新表正常可用。

MySQL/MariaDB下索引基数cardinality的更新问题

起因与问题

使用MySQL做数据,有时会隐约感觉到一些语句执行速度极其慢,而理论上应该是很快的。通常使用phpMyAdmin作为客户端,在表结构页里可以方便的看到索引状态,对基数cardinalyty一知半解,隐约理解为惟一值个数。

但前两天写一条查询语句执行速度非常非常慢,看到一个索引的基数竟然是空的,而且明明应该有很多值。猜测MySQL出bug了,于是删除并重建了索引,基数正常了,语句也飞快跑完。于是稍多留意了一下索引基数。同一天,看到一个基数为1的索引,也是很多惟一值的字段,这也不正常。因为是MyISAM表,直接打包了对应的.frm, .MYD, MYI 三个文件,保留一个现场,有时间再做研究。

回顾了问题表生成语句,整理出一个简单化的重现过程,下面讨论。

问题重现

测试平台为 MySQL 5.5, MariaDB 10.3,其它版本应该也是类似。直接上语句

/* ************************************************* */
-- 一张数据表,并填充一些数据,稍微大一些
DROP  TABLE IF EXISTS `d_src` ;
CREATE TABLE `d_src` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `org` char(32) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `org` (`org`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

INSERT INTO `d_src`(org) VALUES (md5('abcdefg'));
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;

-- 新建一张表,这里先使用 MyISAM 存储引擎,用来重现问题。插入数据,加字段及索引,UPDATE
DROP TABLE IF EXISTS `d_making`;
CREATE TABLE `d_making` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `org` varchar(50) NOT NULL DEFAULT '',
 `cnt` int(10) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `org` (`org`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `d_making`(`org`)
SELECT `org` FROM `d_src` 
ORDER BY id desc;

ALTER TABLE `d_making` ADD org_fix varchar(50) NOT NULL DEFAULT '', ADD INDEX (org_fix);

UPDATE `d_making` SET org_fix=left(org,2 );

-- watch the index status, the Cardinality of Key org_fix is 1
SHOW index FROM d_making;

此时查看索引状态,注意org_fix索引的基数,竟然是1 !  删除并重建索引,再看

-- rebuild index and show key again, the Cardinality NOT 1 
ALTER TABLE `d_making` DROP INDEX `org_fix`, ADD INDEX `org_fix` (`org_fix`); 
SHOW index FROM d_making;

org_fix的基数应该是256左右(因为是原始数据是随机数,大概在256的附近)。

按上脚本,只d_making表改用InnoDB存储引擎,结果也类似,也许不是1,但也明显不对。

不过,在MySQL 8.0 (CentOS 8默认配置)下,得到了 258,比真实值256要大。

原因探求

最早注意到索引基数时,搜索过相关资料,但仍是一知半解。这次通过 "mysql cardinality rebuild index" 查询,找到stackoverflow上有类似问题,最终找到mysql官方手册的一段说明

Cardinality

An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

理解下来,说是索引基数只是个估算值,并不一定可靠,可以用 ANALYZE TABLE 表 更新。事实上,删除并重建索引并不一定奏效。SQL语句执行时,优化器会依据索引基数决定是否使用索引,于是,基数不正确(过小)的索引,就不能被选用,于是这个索引事实上无效了。

结论

  1.  MySQL索引基数,可能不准确(过大过小都有可能),从而可能造成语句执行时忽略该索引(索引失效)。
  2.  要想让该值准确,得在表数据有改动后跑一遍 ANALYZE TABLE table_name

对使用者来说,这是个潜在的坑。手工运行SQL语句前,可以事先关注一下索引基数是否正常(或先 explain一下)。如果是在线生产环境,可能就要自求多福了,或者搞一大群的计划任务,跑ANALYZE TABLE. 不过,理论上,没有大面积UPDATE的字段的索引,这个问题不大。

后记

再深入一点,索引基数即是MySQL统计信息,关系型数据库的查询优化器要依据统计信息确定是否使用某个索引;而统计信息并非实时更新的。本问题的即是统计信息未及时更新的一个极端案例。也就是说,这个问题并非MySQL本身的缺陷,应该在关系型数据库中普遍存在。

MySQL的缺陷/Bug/异常/陷阱/注意事项

MySQL的缺陷/Bug/异常/陷阱/注意事项

这里只是个记录,踩过的坑

REGEXP的中文支持

REGEXP 对中文的错误识别,如下语句,结果竟然是1,在 MySQL 5.5.53, MariaDB 5.5.60, MySQL 5.7.24 下测试结果一致.

SELECT '区中医院'  regexp '[一二三四五六七八九十〇]{6,}'  as mt

这个问题是在utf8-general-ci 数据表上做regexp匹配连续的数字汉字时发现,暂时没测试否与字符集的选择相关,猜测是regexp本身行为对宽字符集支持的问题。

已确认 MySQL 8.0.4 以后解决了该 bug

变量/设置选项:sql-mode相关

sql-mode变量默认是空的,这里造成很多问题,强烈建议,至少加入如下的设置。

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

否则,下面的一系列陷阱在等着你...

超过字段长度的字符串会自动截断

插入数据时超过字段长度的字符串会自动截断。这是MySQL的默认行为,很坑很坑;即使这对于初学者来说显得“友好”些,然而它还是个大坑。而且还会因此带来更多诡异行为,比如:按用户名汇总(group by)的统计结果插入另一表,如果不小心目标表用户名字段长度不足、并且设置了惟一键,可能收到报错说重复值(举例统计结果里“阮小二”、“阮小五”、“阮小七”都被截断成“阮小”,就是三条重复数据)。

往表中写入数据中途出错后竟然保留不完整的数据

 

关于sql-mode的后记

设置了sql-mode,你会用得很开心的。然而,一旦切换到其它环境时,更大的麻烦也可能随之而来。或许只有你把时时刻刻记得这些缺陷,并在每行代码里规避它们。

sql-mode有很多选项,请参考官方手册。

索引统计信息非实时更新,从而会造成索引无效

即索引基数 cardinality ,尤其在对表有大规模写入后容易出现。这个问题比较复杂,在不同版本不同存储引擎下的表现并不一致,8.x的新版本表现似乎明显更好。参看MySQL/MariaDB下索引基数cardinality的更新问题

convert/cast做数据类型转换后结果无法按预期写入

希望从字符串中提取一段数字,如果提取结果为非法数字,使用convert或cast函数强制转换为数字;在select结果记录集里,非数字被转换为0,这是符合预期的;但如果把结果写入字段中示例写入表中,非法数据的写入仍然是失败的,就像写入的是未做过转换的原始值一样。如下示例表,希望从birth字段中提取年份数字,写入到y字段中。

use `test`;
CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(6) NOT NULL,
  `birth` varchar(20) NOT NULL DEFAULT '',
  `y` smallint(6) NOT NULL DEFAULT 0,
  `yt` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `foo` (`id`, `birth`, `y`, `yt`) VALUES
(1, '1945年11月', 0, ''),
(2, 'x年x月', 0, ''),
(3, '1940年', 0, ''),
(4, '1962年12月', 0, '');

-- 在select中非法数字转换为0,是符合预期的。
SELECT *,cast(left(birth,locate('年',birth)-1) as UNSIGNED) as x FROM `foo`;

-- 但update到y字段中,就报错了
update foo set y=cast(left(birth,locate('年',birth)-1) as UNSIGNED) ;
#1292 - Truncated incorrect INTEGER value: 'x'

-- 如果不转换直接写入,报错消息是有所不同的
update foo set y=left(birth,locate('年',birth)-1) ;
#1366 - Incorrect integer value: 'x' for column `test`.`foo`.`y` at row 2

Windows下的安装

注册为windows服务时, --install 参数必须写在其他参数前面。如果指定defaults-file 参数,那么 --install 参数后要跟个服务名,可以写成MySQL、MariaDB10或其他名字。

通过zip包升级安装新版本,升级后,要运行 mysql_upgrade.exe 让它升级系统表。如果mysql的root用户有登录密码,需要带上-u -p参数, mysql_upgrade.exe -uroot -pyourpassword

8.x 以后用户授权与此前有重大改变,全新的手工安装后设置root密码将麻烦很多,旧的方式多半已无效。

混用left/right/inner join的查询结果很可能非预期

<TODO>{本节内容似乎有误,未核实}严格来说,这不是mysql的问题。查询优化器会在参与join的表中排出先后次序,这个次序很可能并不是它们在where子句中出现的前后次序,inner join会丢弃无完全匹配的行,而left/right则不是;如果比预期丢早或丢晚了,就很可能造成结果非预期。回想mysql文档中的join都是 xx join (a, b) ON ... 这样的写法,甚至较早版本并不支持 xx join a ... xx join b... 的写法,可在一定程度上避免类似问题。

暂不举实例了。实际应用中已遇到这个问题,实际问题太复杂,等以后有时间再编个小的示例。

存储过程实现MySQL表数据变更记录追踪(含历史快照)

直接上代码及调用示例,调用示例:

假设有一张表user_status_in_playground,存储了用户在每个游戏场的统计数据,如总积分 、玩过次数、最近玩的时间、最近次得分。逻辑上 user_id + playground_id 是个惟一键,我们希望按照这个惟一键追踪其它字段的数据变更记录(建数据快照)。需要快照时执行一次存储过程,快照数据存到表archive_user_status_in_playground里。其中归档表只需要指定表名即可,将由存储过程自动创建。

SET @data_source='source_db.`user_status_in_playground`';
SET @archive_target='archive_db.archive_user_status_in_playground';
SET @identify_columns='user_id,playground_id';
SET @track_columns='score,paly_times,last_play_time,last_score';

call revision_archive(@data_source, @archive_target, @identify_columns, @track_columns, @dmesg);

如果我想按指定的时间点倒出当时的完整快照,其中revision是时间点(年月日时分秒),快照表也是自动创建的,指定表名即可。

SET @archive_location='archive_db.archive_user_status_in_playground';
SET @dump_location='`tmp`.`user_status_in_playground__dump`';
SET @identify_columns='user_id,playground_id';
SET @track_columns='score,paly_times,last_play_time,last_score';
SET @revision=20190603100255;

call revision_dump(@archive_location, @dump_location, @identify_columns, @track_columns, @revision, @dmesg);

(原始存储过程见附件)

db_revision_archive.mysql.sql

MySQL语句实现字段拆分成多行

问题简述

如上图,拆分字段为多行,只使用MySQL本身功能,不借助其它工具。

下页结合实例详述原理,最后是完整的拆分语句。实际使用中,只需把最后一节的语句当作模板,修改字段名、表名即可。

核心知识点

    • 表连接关系 JOIN... ON 中使用小于号,而非通常用的等号
    • MySQL函数 SUBSTRING_INDEX() 详参MariaDB文档MySQL文档

示例数据表article_tags

文章标签表,文章编号aid,及文章标签`tags` ;即待拆分tags数据,分隔符为 " | ",竖线及前后空格共计三个字符。

CREATE TABLE `article_tags` (
  `aid` int(11) NOT NULL COMMENT 'article id',
  `tags` varchar(255) NOT NULL DEFAULT '',
  KEY `aid` (`aid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

辅助表sp_idx

拆分过程需要一张辅助表,包含了正整数的序列的一列数据。可按下页语句构造1024行。

CREATE TABLE `sp_idx` (
 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `x` bit(1) NOT NULL DEFAULT b'0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='拆分辅助表(正整数序列)';
INSERT into `sp_idx`(`id`,`x`) values(1,0);
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;
INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;

计算每条记录拆分条数

计算每行要拆分成的行数据,即有多少条数据通过分隔符拼接在一起。即计算字符串中删除分隔符后长度的减少量,即是 分隔符的个数*分隔符长度。分隔符个数+1 即是拆分条数。

这里使用char_length()计算长度;因为分隔符可能包含ASCII码,故不用length()。

SELECT r.`aid`, `tags`,
 (char_length(`tags`)-char_length(REPLACE(`tags`,' | ',''))) DIV char_length(' | ') + 1 as kw_count 
FROM `article_tags` r ;

联入入辅助表

按上步计算出的kw_count,联入相应条数的序列数据,从1 到 kw_count,序列数据从辅助表取。联表关系使用 < ,而不是通常使用的等于。

SELECT r.`aid`, `tags`
 ,x.`id`
FROM `article_tags` r INNER JOIN `sp_idx` x 
ON x.`id` <= (char_length(tags)-char_length(REPLACE(tags,' | ',''))) DIV char_length(' | ') + 1
;

按序号取子串

结果记录集最后一列id,事实上就是原始字符的排序号。每条记录按该序号截取子串即是结果了。这里使用 SUBSTRING_INDEX() 函数,截取前N段或后N段子串。

第一步,如下,截前N段子串的结果,再截最后一段即是每行所需结果。

SELECT r.`aid`, `tags`
 ,x.`id`, substring_index(`tags`,' | ',x.`id`) as step_1
 ,substring_index(substring_index(`tags`,' | ',x.`id`),' | ',-1) as step_2
FROM `article_tags` r
INNER JOIN `sp_idx` x 
on x.`id` <= (char_length(tags)-char_length(REPLACE(tags,' | ',''))) DIV char_length(' | ') + 1
;

总结整个过程

SELECT r.`aid`, `tags`
,x.`id` as idx, substring_index(substring_index(`tags`,' | ',x.`id`),' | ',-1) as piece
FROM `article_tags` r INNER JOIN `sp_idx` x 
ON x.`id` <= (char_length(`tags`)-char_length(REPLACE(`tags`,' | ',''))) DIV char_length(' | ') + 1 
WHERE r.`tags`!='' HAVING `piece` !='';

该语句,可以当作模板使用,其中:

  • `tags` 替换为待拆分字段(共4处),' | ' 为分隔字符串(共4处);
  • `piece`为拆分结果列,`idx` 为 `piece`在原始`tags`中的序号;
  • 比前面步骤增加了WHERE及HAVING子句,用来排除原表tags为空字符串的行、及拆分piece为空字符串的行。

辅助表`sp_idx`需要事先建好,事实上只要从是从1开始的整数序列即可;如果待拆分子串数超过1024,则要扩充本表,多跑几次该句即可:INSERT into `sp_idx`(`x`) SELECT `x` FROM `sp_idx`;

如果实在不方便建辅助表,可以使用`mysql`. `help_topic`表的help_topic_id字段(不推荐)。要能保证其完整性,而且最大拆分子串数不大于`help_topic`的总行数。而且还要多加个where条件 help_topic_id>0,否则会拆分出多余的空串行,因为help_topic_id字段是从0开始编号的。

附件:

示例数据及SQL脚本 split_field_to_lines_in_mysql.zip

windows下安装zip压缩版的mysql服务器端v5.7.16(另MariaDB 10.4.10)

环境:windows 10某个版本(写本文的最新版本)

目的:安装个mysql服务端,从官方下载的zip压缩版5.7.16,而不是msi版本。

操作过程折腾了,以前安装过多次,不过好像不是真正意义上的从zip安装,而是使用之前通过msi安装过的版本,后面只是注册成系统服务一项。

本次折腾的的操作,主要从官方是下载.zip版本压缩包,选择的是x64版,解压缩到合适的目录里,本文以 C:\Program Files\mysql\  为例。

step 0. 准备配置文件:复制my-default.ini得到my.ini ,修改其中的basedir, datadir两个参数,示例如下

basedir = C:\Program Files\mysql
datadir = D:\data\mysql-data

step 1. 安装为windows服务:管理员身份启动cmd,进入mysql/bin/目录,执行如下安装命令:

mysqld --install MySQL --defaults-file="C:\Program Files\mysql\my.ini"

注意:--install 参数必须在前,否则mysqld会认为你要运行mysqld进程,然后启动失败)

setp 2. 初始化数据目录:创建 D:\data\ ,注意不需要创建mysql-data子目录;运行 mysqld --initialize

step 3. 查root用户初始密码:查windows日志-应用程序日志,里面应该有类似如下一条

A temporary password is generated for root@localhost: 5cuiMezaNd>QFor more information....

其中5cuiMezaNd>Q即是默认密码,到命令行里修改一下吧,如下一行,将root密码改为000111, 会有交互提示输入旧密码的。

mysqladmin.exe -uroot -p password "000111"

step 4. 启动windows服务:net start MySQL. 完工。

Addition. 如需要卸载服务,到mysql/bin/目录里执行 mysqld.exe --remove 即可。需要的话删除mysql数据文件目录。

附一篇:Windows 下安装MariaDB 10.4.10

1. 下载windows版的zip包,解压缩到合适位置,以下假定为 C:\Program Files\MySQL\mariadb-10.4.10-winx64

2. 初始化数据目录,使用 mysql_install_db.exe 命令,(这与mysql的 mysqld --initialize 命令不同),最主要的是指定数据目录参数datadir、root用户密码

mysql_install_db.exe --datadir="D:/data/mysql-data/" -p 000111 -P 3306

3. 准备配置文件。把datadir下生成的my.ini剪切到mariadb-10.4.10-winx64下,按需要修改配置参数,如加入复制、innodb优化等参数。

4. 注册服务,完成。mysqld --install MySQL --defaults-file="...ini"

 

MySQL复制设置及相关维护操作

主从复制配置步骤

1. 主库服务器配置

主服务器MySQ配置参数,参数主要依据《高性能MySQL 第三版》;从服务器配置与此稍有不同,主要是增加几个参数,如后节。

#replication
server-id=100
log-bin=mysql-bin
expire_logs_days=30
sync_binlog=1
relay_log=mysql-relay-bin
log_slave_updates=1

#replication safe for innodb engine
innodb_flush_logs_at_commit
innodb_support_xa=1

服务器id号;设置二进制日志文件名(隐含启用);binlog文件过期天数(最大允许99,设为0则不过期);每次写入binlog后同步到磁盘。接下几个参数实际是slave上使用的(参看文slave的配置所述),写在这里可以让主从配置参数都一致,方便实际使用时少出错,也方便多级式的复制拓扑。

2. 添加复制用户

只需要赋两个全局的权限: REPLICATION SLAVE, REPLICATION CLIENT

CREATE USER 'repl'@'192.168.10.132' IDENTIFIED BY '***';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'repl'@'192.168.10.132' IDENTIFIED BY '***';

3. 备份主库,备份中加入binlog及位置

全面备份主库,备份前需要已经启用二进制日志。

mysqldump -hlocalhost --opt --master-data=1 --all-databases --max_allowed_packet=8M --net_buffer_length=128K -uroot -pyour-password >all.sql

为了获得一致性的备份,考虑加入参数--single-transaction 或 --lock-all-tables,分别针对事务型引擎及非事务型引擎,但它们不能同时使用。

注意其中--master-data=1参数,它将binlog及位置信息生成到备份文件中,大概如下一行

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=8747725;

4.iptables放行备库连入

允许从服务器ip到主服务器3306端口的连接

iptables -I INPUT 7 -s 192.168.10.132/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

5. 配置从服务器

server-id=201
log-bin=mysql-bin
expire_logs_days=30
sync_binlog=1
relay_log=mysql-relay-bin
log_slave_updates=1     # actually only for B in A->B->C 
read_only=1

前四个与主配置一样;中继日志写入binlog (默认是不写的,这实在有点出乎意料);中继日志文件名;数据只读(root用户不在此限)

6. 还原到从库服务器上

SOURCE /path/to/bakup.sql;

6. 配置从库,连接到主库

事实上下面的log_file,log_pos可以不用再指定,因为当初的备份里已经有该信息。当然这里再指定一次也没负作用。

change master to master_host='192.168.10.100', 
master_user='repl',
master_password='repl-password', 
master_log_file='mysql-bin.000004', 
master_log_pos=8747725;

7. 启动slave

start slave

顺利的话即完成。

如报 Failed to open the relay log 的错误,先停止slave的mysql服务,删除 /var/lib/mysql/ 下的中继日志相关文件,主要是  *-relay-bin.* 及 relay-log.info,再启动重新start slave

配置中的一些注意事项

  • 关于binlog_format 参数。在mysql 5.5(win32)版下,该参数默认值为STATEMENT。于是,至少有以下问题insert into tablename select ... limit n这样的语句,执行时会报warning错误,原因是limit结果在一致性上是非安全的。所以可以加入 binlog_format=MIXED 的配置参数。
  • 至少在 Mariadb 5.5 上,RESET SLAVE 似乎并不起效,而要写成RESET SLAVE ALL; 才行。
  • 如果是同时是主与从的服务器上,必须加入参数 log_slave_updates ,因为,MySQL默认不把中继日志写入二进制日志里。这好像有点出乎意料。

日常管理维护相关

查看二进制日志文件 SHOW BINARY LOGS;SHOW MASTER LOGS; ,二者等价。

刷新二进制日志。刷新并终结当前的二进制日志文件的写入,并开启一个新的二进制日志文件  FLUSH BINARY LOGS;   如果要刷新中继日志,则 FLUSH RELAY LOGS;(中继日志,一般不需要理它,事实上它主要由 chagne master ... 时会自动重设,一般不要手工指定)

自动定时清理。通过参数 expire_logs_days 指定过期天数,过期自动清理;具体天数据按实际情况定(空间与同步延迟等)。该参数可运行时设定。

expire_logs_days = 10 

手工清理。root登录mysql客户端,执行purge命令。

> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

其中 log_name是show master logs; 显示的日志文件名,如 mysql-bin.000001. 应用举例:

> PURGE MASTER LOGS TO 'mysql-bin.000003';  //清除mysql-bin.000003(含)之前的日志
> PURGE MASTER LOGS BEFORE '2016-11-05 10:00:00';   //清除2016-11-05 10:00:00前的日志
> PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 DAY);
  //清除3天前日志,使用BEFORE函数计算日期,变量的date自变量还可以为'YYYY-MM-DD hh:mm:ss'格式。详查手册

清理二进制日志的影响。如果当前服务器有一个活跃的从属服务器,该从服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。(参考

正常运行若干时间后的复制失败后的维护

如果在运行中,出现了异常,复制失败了(通常时进程异常结束、断电等,slave状态里有报执行错误的语句)。这时或许需要重配置slave才可以解决问题

stop slave;
SOURCE /path/to/bakup.sql;
RESET SLAVE ALL;
change master to ...
show slave status; #检查状态,确认主库及连接账号等
START SLAVE;

end

MySQL(csv,text)导入mssql方法参考

分两步处理,第一步是将csv导入到mysql。没有使用mssql自带客户端的导入功能,因为这个功能太坑,尝试N多种方法仍然是报错,因此选择先转换成mysql做中间步骤。

csv/text导入MySQL

一批很大的csv数据(其实并非csv,而是制表符分隔字段),要导入到mssql数据库里。

首先在cygwin下使用awk对数据做预处理,只筛选出字符数正确的行。并且在首行加入列名,以便导入工具自动做列对应。

然后计算各字段最大长度;因为字段数太多(63个),所以计算一下,用于构造create table语句。对于最大长度在255以下的字段使用varchar(n)型,255以上使用longtext类型。

#参考下面语句
$ awk -F "\t" 'NF==63 {print $0}' all.csv >all_63f.txt
$ head -1 origin_text_file.txt >h.txt
$ dos2unix h.txt       #注意最好不要带BOM,否则自行另存一下,避免麻烦
$ cat h.txt all_63f.txt >a63_with_head.txt

#最大计算字符长度,本身并不复杂,只是写在一行里,阅读不太方便
$ awk -F "\t" 'BEGIN{for(i=1;i<=63;i++){xcount[i]=0}} {for(i=1;i<=63;i++){if(xcount[i]<length($i) ){xcount[i]=length($i)}}} END{for(i=1;i<=63;i++){print i,xcount[i]}}' all_63f.txt

接下来使用Navicat for MySQL将导入csv。navcate可以试用,功能足够用;如果使用频繁,建议购买授权。

MySQL导入mssql

以下win2008 x64下的mssql 2012为例。

安装mysql的.net驱动 http://dev.mysql.com/downloads/connector/

create table,建表,与mysql结构一致。

使用mssql自带导入功能导入数据,导入数据源选择 .Net Framework Data Provider for MySQL,填写必要的主机名、登录名、口令,如下

mysql_import_into_mssql_via_nf

不能选择源数据表,必须写select 语句,如: select f1,f2,f3... from `db`.`table` where 1

后面应该没有什么难度了。

生产环境下MySQL服务配置优化参考样本/CentOS 6.x MySQL 5.1/CentOS 7.x MariaDB 5.5

两份mysql配置文件,分别是实体机生产环境下与vps下的两个版本,MySQL5.1与5.5差异不大,常规设置可以通用。后面另有二进制日志相关的配置。

注意事项:如果修改了innodb_*参数,尤其是修改了innodb_log_file_size ,启动前要删除/var/lib/mysql/ib_logfile{0,1}两个文件,启动后一定要检查一下mysql日志,是否有相关错误或警告消息。

环境 CentOS 6.x 自带的MySQL 5.1.73

在原始配置文件  /etc/my.cnf 基础上,在 [mysqld] 节内增加配置参数。实际应用中,请按硬件及负载酌情修改。

#add by feng 120418  --------------------------
#skip-locking
skip-name-resolve
skip-external-locking
key_buffer_size = 256M
#table_cache = 3072
table_open_cache = 3072
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size= 512M
query_cache_limit= 5M
tmp_table_size=1024M
max_heap_table_size=3000M
max_allowed_packet = 16M
innodb_buffer_pool_size = 512M
innodb_log_file_size = 512M
innodb_additional_mem_pool_size=512M
innodb_log_buffer_size=64M
max_connections=2000
max_user_connections=800
join_buffer_size = 8M
open_files_limit = 65535
#tmpdir=/dev/shm
max_connect_errors=1000
#add by feng 120418  end ---------------------

1-2G内存的个人VPS下配置参考

因为开启了主从复制,会产生大量二进制日志占用磁盘空间。如不需要,可以删除复制相关行。其中key_buffer_size, innodb_buffer_pool_size 设置为32M与 64M,事实上这都嫌大浪费资源了,看实际需要吧,内存资源紧张可酌情改小点。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#add by fengyqf start
skip-name-resolve

innodb_log_buffer_size=32M
innodb_buffer_pool_size=64M
innodb_log_file_size=16M
innodb_additional_mem_pool_size=16M
key_buffer_size=32M

#replication safe for innodb engine
innodb_flush_logs_at_commit
innodb_support_xa=1


#replication
server-id=100
log-bin=mysql-bin
log-error=mysql-bin.err
expire_logs_days=30
sync_binlog=1
binlog_format=MIXED
#add by fengyqf end

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从服务器配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

skip-name-resolve

server-id=201
log-bin=mysql-bin
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
read_only=1
binlog_format=MIXED


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

二进制日志的定期清理或手工清理

定时清理。通过参数 expire_logs_days 指定过期天数,过期自动清理;具体天数据按实际情况定(空间与同步延迟等)。该参数可运行时设定。

expire_logs_days = 10 

手工清理。root登录mysql客户端,执行purge命令。

> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

其中 log_name是show master logs; 显示的日志文件名,如 mysql-bin.000001. 应用举例:

> PURGE MASTER LOGS TO 'mysql-bin.000003';  //清除mysql-bin.000003(含)之前的日志
> PURGE MASTER LOGS BEFORE '2016-11-05 10:00:00';   //清除2016-11-05 10:00:00前的日志
> PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 DAY);
  //清除3天前日志,使用BEFORE函数计算日期,变量的date自变量还可以为'YYYY-MM-DD hh:mm:ss'格式。详查手册

清理二进制日志的影响。如果当前服务器有一个活跃的从属服务器,该从服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。(参考

 

MySQL服务器设置max_user_connections防止连接耗尽,以提高可用性

问题简述/现象及原因

一台MySQL服务器上,有多个数据库,由不同用户使用,相互之间没有或很少关联性。典型的实例是虚拟主机,或者有N多个小网站的某些低频企业应用。

这种环境下,难免有部分应用的质量不高:

出现效率极低的慢查询 -> 后续请求大量被locked排队 -> MySQL服务实时连接数达到最大连接数限制  ->  无法建立新连接

so, 所有相关应用全部挂掉

应对方案

为防止上述情况发生,要为MySQL配置max_user_connections参数。该参数作用是设置单个用户最大连接数限制。具体设置多少,要根据实际情况再裁定。

需要引起注意的是,这个参数是针对所有用户的限制,所以要考察正常情况下连接数最多的用户。可以使用下面语句实时查询各用户连接数。

select `USER`,COUNT(`USER`) AS CNT from information_schema.PROCESSLIST GROUP BY `USER` ORDER BY CNT DESC;

一个参考数值,将 max_user_connections 设置为正常情况下单用户最大连接数的3-5倍。

max_user_connections参数可以在MySQL运行时动态设置 set global .... 当然,也要同步写到my.ini配置参数里。

方案评估

max_user_connections 是个一刀切的配置参数,好像MySQL不能对每个用户设置连接数,并发查询数,io,cpu占用或其它什么什么的细粒度参数。或许并非一定有效。

与max_connections参数对比

max_connections 是指整个mysql服务器的最大连接数;

max_user_connections 是指每个MySQL用户的最大连接数

MySQL官方文档相关章节