windows下安装zip压缩版的mysql服务器端v5.7.16

环境: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数据文件目录。

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

1. 主库服务器配置

主服务器MySQ配置参数,参数主要依据《高性能MySQL 第三版》

#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

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
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
expire_logs_days=30
read_only=1

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

完成

x. 如果异常,复制失败时,或许需要重配置slave才可以解决问题

 

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

附记注意事项:

  • 关于binlog_format 参数。在mysql 5.5(win32)版下,该参数默认值为STATEMENT。于是,至少有以下问题insert into tablename select ... limit n这样的语句,执行时会报warning错误,原因是limit结果在一致性上是非安全的。

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.73

这里有mysql配置文件,分别是实体机生产环境下与vps下的两个版本;后面有二进制日志相关的配置。

注意事项:如果修改了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下配置参考

因为开启了主从复制,会产生大量二进制日志占用磁盘空间。如不需要,可以删除复制相关行。

[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

#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
#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


[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官方文档相关章节

ms sql server下unix时间戳函数unix_timestamp, from_unixtime(与mysql兼容)

直接上代码:

CREATE FUNCTION UNIX_TIMESTAMP (@ctimestamp datetime) RETURNS integer 
AS
BEGIN
  /* Function body */
  declare @return integer
  SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)
  return @return
END


CREATE FUNCTION from_unixtime (@ts integer) RETURNS datetime 
AS
BEGIN
  /* Function body */
  declare @return datetime
  select @return = DATEADD(second, @ts, {d '1970-01-01'})
  return @return
END

用法

跟MySQL下的一样类似:

select dbo.UNIX_TIMESTAMP('2013-1-1')
select dbo.from_unixtime(2145000000)

感谢参考 http://skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/

mysqld 配置优化,请一定要加 skip-name-resolve,否则将被show processlist 里的 unauthenticated user 搞死

把mysql迁移到单独的服务器,与web服务器分离;但似乎不怎么稳定,经常无法连接,报错是连接数过多;登录到服务器上命令行连接也是无法连接。这情况很频繁,今天竟然出现了两次。之前一直是重启mysql解决问题,但如此频繁的故障,就有十分的必要去查找原因了。

在服务器上命令行登录,mysql -uroot -upassword,把密码写到命令行里,不用每次再输入,按ctrl+p 然后回车,尝试N次,终于登录进去了;连接数过多,终究还是可以找到空子进去的。赶快查看并保留运行状态的重要参数:

show variables;
show status;

show processlist;

好家伙,这么多连接:

| 10520 | unauthenticated user | 192.168.0.109:41063 | NULL  | Connect | NULL | login | NULL             |
| 10521 | unauthenticated user | 192.168.0.109:41065 | NULL  | Connect | NULL | login | NULL             |
| 10522 | unauthenticated user | 192.168.0.109:41067 | NULL  | Connect | NULL | login | NULL             |
| 10523 | unauthenticated user | 192.168.0.109:41068 | NULL  | Connect | NULL | login | NULL             |
| 10524 | unauthenticated user | 192.168.0.109:41069 | NULL  | Connect | NULL | login | NULL             |
| 10525 | unauthenticated user | 192.168.0.109:41070 | NULL  | Connect | NULL | login | NULL             |
| 10526 | unauthenticated user | 192.168.0.109:41071 | NULL  | Connect | NULL | login | NULL             |

如些上千行。先不管,这里肯定是问题突破口。

set global max_connections=4000;

增加允许的最大连接数,先让前台网站可以正常工作。

回过头google :mysql unauthenticated user

果然,遇到此类问题的人很多,问题在于mysql的反向ip地址解析,配置参数里加上skip-name-resolve就可以。

总结一下原因,大概如下:

因为mysql默认会根据客户端的ip地址反向解析,用于用户登录授权之用。不过正常情况下,很少会有人这样用。ip地址反向解析是很慢的,尤其是高负荷的mysql,每秒种几百次甚至更高的请求,这个请求压到本地的dns服务器上,dns服务器说不定会怀疑你在恶意请求,然后不理你了,然后这些登录请求就挂在那里,后面的连接还持续,然后越积越多,然后就达到mysql的最大连接数据限制了,然后新的连接就直接被拒,得到连接数过多的消息。

因为mysql配置文件使用的之前的配置文件,当时跟web同服务器,所以不存在这个问题。

这也正好解释了为什么phpMyAdmin里看mysqld状态时,有很多失败的连接,它们应该就是因反解析失败而被拒的。

 

mysql对大表执行update速度慢时,试试改用insert可能会有意想不到的发现

实例:需要根据用户日志的ip地址计算出其地理地址

表结构:

用户日志表(200万条记录),其中address是待填充的字段:

CREATE TABLE `tmp_open_ip` (
  `email` varchar(60) NOT NULL DEFAULT '',
  `address` varchar(50) NOT NULL DEFAULT '',
  `ip` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `email` (`email`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

另ip地址数据库表(44万条记录)

CREATE TABLE `ip` (
`s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '开始ip',
`e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '结束ip',
`a` varchar(50) NOT NULL DEFAULT '',
KEY `s` (`s`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

需要根据用户日志表 tmp_open_ip 里的 ip字段到ip地址数据库表里查询出对应的地理地址,将地址填充到address字段。

使用如下update语句执行:

UPDATE tmp_open_ip AS u 
INNER JOIN ip 
ON u.ip BETWEEN ip.s AND ip.e 
SET u.address = ip.a

在笔者的电脑上运行了速度非常之慢,执行了一个多小时(4500s)都没有完,也不知道还要多久。

实在看不过去,于是想到使用insert 是否会快一些,于是重新导一张表 tmp_open_log 与tmp_open_log完全一致。

创建一张表 tmp_open_address,是insert的目标表,为了速度更快,没建索引:

CREATE TABLE `tmp_open_address` (
`email` varchar(60) NOT NULL DEFAULT '',
`address` varchar(50) NOT NULL DEFAULT '',
`ip` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8

执行insert 语句

insert into tmp_open_address (email,address,ip)
select l.email,ip.a,l.ip
 from  tmp_open_log as l inner join ip on l.ip between ip.s and ip.e ;
/* Affected rows: 2,543,124  Found rows: 0  Warnings: 0  Duration for 3 queries: 16.922 sec. */

不到17s!本来还想去倒杯水、稍事休息一下,结果已经执行完毕。

到本文写完时,前面的update语句已经执行了5000s,结束仍是遥遥无期。

所以,对于大数据量执行update时,可以考虑改用insert 语句实现,可能麻烦一些,但高速带来的收益远大于麻烦!

后记:

直接杀死了update进程,去看看update执行了多少:运行

SELECT * FROM `tmp_open_ip` where address!=''

结果只有 11,373 ,照这个速度,要运行N天....

mysql join查询时,参与join的字段字符集编码不同,对性能影响是巨大的

本文一直以来都只是个标题,在三个月后的今天,熬夜补上内容。

直接上代码:

建utf-8编码的表 t1:

CREATE TABLE IF NOT EXISTS `t1` (
  `name` varchar(50) NOT NULL DEFAULT '',
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

随便插入些数据,数量大一点,后面实验结果更清晰,偷个懒,构造随机字符串插入语句

insert into t1(name) 
select concat(
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65)
)

每次执行插入一条记录,用你熟悉的脚本(python,php,shell等都行)写个循环,执行一万次以上。

将该表复制成一个新表t2,删除一部分数据,1000条左右即可。(推荐使用phpMyAdmin)

再将t2复制为t3,并将字段改为gb2312编码。

使用一个left join语句,写一个语句,查出t2/t3比t1少了哪些记录。

语句很简单,如下:

SELECT SQL_NO_CACHE t1.name, t2.name
FROM t1
LEFT JOIN t2 ON t1.name = t2.name
WHERE t2.name IS NULL 
LIMIT 0 , 30

注意加入 SQL_NO_CACHE ,禁用mysql缓存。

先看编码一致的t2表,phpMyAdmin里执行结果:

显示行 0 - 29 ( 1,129 总计, 查询花费 0.0010 秒)

平均耗时大概为0.0010秒

SELECT SQL_NO_CACHE t1.name, t3.name
FROM t1
LEFT JOIN t3 ON t1.name = t3.name
WHERE t2.name IS NULL 
LIMIT 0 , 30

phpMyAdmin执行结果:

显示行 0 - 29 ( 30 总计, 查询花费 0.1871 秒)

差两个数量级!

查询语句解释:

t2

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL name 152 NULL 11129 Using index
1 SIMPLE t2 ref name name 152 test.t1.name 1 Using where; Using index; Not exists

t3

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL name 152 NULL 11129 Using index
1 SIMPLE t3 index NULL name 102 NULL 10000 Using where; Using index; Not exists

附带上本次实验的三个表及数据
实验用表导出备份test.sql

 

Microsoft SQL server数据库转换为MySQL方法

有时需要把MS SQL数据库转换成MySQL,但苦于没有好用的工具,个人使用一个手工转换的方法,相对来说还是比较方便的,对于只导一两个表的时候。

思想:
通过mssql构造出MySQL的insert into 语句,保存到一个文本文件里,然后导入MySQL数据库。

方法扼要如下:

  1. 根据ms sql表结构同步创建出mysql表,可以使用mssql客户端工具生成mssql的建表语句,里面一般来说会带上COLLATE Chinese_PRC_CI_AS 及 CONSTRAINT [DF_xxx_xxx] 这样的部分,对mysql是没有用的,使用ue的查找替换功能将其删掉,替换“CONSTRAINT [DF_xxx_xxx] ”要使用正则表达式匹配 CONSTRAINT.*?\]
  2. 将简化后的create table ... 语句改写成适合MySQL的建表语句,主要是字段类型的相应修改(mysql里没有nvarchar等以n打头的类型)、方括号替换成反引号、删掉mssql标识列定义等。
  3. MySQL里执行create table建表。
  4. 回到mssql里,对每个文本型字段执行一次字符串替换,将单引号替换成mysql风格的转义符( \ '),语句大致如下:
    update you_table_name set [field_name_1]=replace([field_name_1],'''','\''')
    里面mssql的单引号转义是两个单引号,所以看起来相当别扭,顺便逼视一下MS的脑残转义约定。
  5. 将mssql表里的非字符型字段改成字符型,当然不改也行,只是下面一步会很痛苦。
  6. 按下来我们要构造一系列用于把mssql记录插入MySQL表的insert 语句,
  7. 我们要在mssql里拼接字符串的形式构造该查询语句 select top 100 'inset into `you_table_name`(`field_name_1`,`field_name_2`,...)values('''+[field_name_1]+''','''+convert(varchar(10),isnull([field_name_2],0))+''',...);' from you_mssql_table
    从这个语句里可以看出,如果是字符型字段,像field_name_1,直接使用mssql拼接符(+)连起来就可以了,但如果是数字型或日期型字段,像`field_name_2`,就要convert做转换了,日期型还要加上第三个参数,如convert(varchar(10),time,120)这样写。
    我们可以先对前100条构造出适合MySQL的insert语句,随机复制几条到MySQL里测试是否可以正常插入。
  8. 确认无误后,从mssql里导出数据的方式,将上面的查询导出成access数据库文件,再从access中导出文本文件(也可以直接从mssql里导出文本),通常mssql导出的文本文件是ansi编码的,我们可以使用editplus之类工具打开并另存为utf-8编码。不推荐使用ue转换,经验教训表明ue转换编码容易出错。
  9. 进入mysql命令行客户端里,注意加上--default-character-set=utf8参数,否则很可能导入一团乱码
    >mysql -uroot -p --default-character-set=utf8
  10. 进入相应数据库use you_database
  11. 执行source X:\path\to\your\sql\file.sql
    应该很快可以完成,导入时可以注意一下是否有错误提示。通常不会有的。
  12. 至此转换完成。
Pages: 1 2 3 Next