多主一从部分从库重新同步&从库常见异常处理

主机已经运行一段时间,从机重新配置,我们的主从架构是多主一从,从机已经有了其他主机正在同步,所有表都是innodb。

1、停掉主服务器

包括mysql服务,web服务,最主要的mysql服务器一定要关闭

2、  将主服务器ibdata1以及库文件夹整体打包

ibdata1压缩打包,数据库文件压缩打包(tar -zcvf dir_name.tar.gz  dir_name 注:dir_name为被打包的文件目录)

3、  主服务器开启bin-log(my.cnf里增加配置log-bin=mysql-bin),启动,记录binlog的文件初始位置,恢复主服务器环境(需要一个小时)

4、  把打包文件送到从服务器

rsync 例如:rsync ibdata1 10.10.12.xxx:/data 是将ibdata1 传送到10.10.12.xxx的data目录,这时会提示输入10.10.12.xxx的用户名和密码

5、  从服务器同步全部停掉(stop slave),web关掉,mysql服务关闭(service mysqld stop)

6、  备份从服务器ibdata1文件(重命名),当复制过来的数据库表引擎转换成myisam引擎后,可重新恢复此文件

7、  将主服务器打包的数据文件(第2部打包的文件),移到从服务器数据库数据目录(压缩的文件记得解压);复制过来的ibdata1要有读取和写入的权限

8、  将要同步的库(bala_ec)所有表全部转成myisam引擎,这时复制过来的ibdata1文件可以删除了

转化数据库表引擎的方法参考后面语句

9、  将第4部重命名的ibdata1恢复,这个时候恢复到从库原先的数据状态

10、              再把要同步的库所有表全部转成innodb引擎

11、              设置从服务器的从库配置,启动主从同步—命令2

12、              在从库中启动主从同步 start slave

 

附:

命令1

show master status;

记录master_log_file、master_log_pos

命令2

Change master to master_host=’10.10.12.yy’, master_user=’yyyyy’, master_password=’yyyyy’, master_log_file=’主服同步bin文件名’, master_log_pos=主服同步bin文件的位置 for channel “xxxxx”;

在从库启动主从同步

Start slave

查看从库状态

Show slave status [for channel “”]

改变数据库表引擎:

alter table `acl_level` engine=myisam;

查看数据库中所有的表示例:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yyyy’;

查看数据库中表引擎为MyISAM的表示例:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yyyy’ AND ENGINE = ‘MyISAM’;

把sql语句执行结果写入到文件

mysql -u root -p -e “use bala_ec;show table status” > /data/1.sql

从库常见异常处理

1、因idbdata1文件里有垃圾表结构,导致myisam无法转innodb,(报表已存在,重启后会在数据目录自动生成idb文件)

先删除表,再重建表

2、同样idbdata1里有垃圾表结构,导致truncate myisam表后表无法创建也无法删除,拷贝一个frm文件过来,然后再删除表,再重建表

3、从库无法启动,报当前主库日志文件当前位置的sql无法执行,在主机使用

mysqlbinlog –start-position=报出问题的位置 –stop-position=往后加几十行 -d 主库名 binlog文件名

看一下指定位置是什么语句,如果不是正常语句位置,可以过去,用change master往后跳到下一条语句的位置

 

尝鲜mysql5.7.10以及多主一从

因数据统计的需要,要将多台服务器上的数据汇总到一台,mysql5.7支持多源同步,也就是多主一从设置。具体配置如下:

安装mysql5.7.10就不详述了,和5.6过程没什么区别,但是千万不要直接在5.6上升级,系统库里的很多表结构都不一样了,包括user表以及几个主从表。

主服设置,可以参考之前的文章http://www.hao535.com/archives/642,略,主服唯一的要求是至少5.6版本,原因是从服的5.7和主服通信时要取主服的uuid,之前版本只用serverid就可以了,5.6以下版本是取不到uuid的,所以至少要5.6版本才能和5.7版本做主从。

关键是从服设置,就是5.7.10版本这台,首先配置文件my.cnf

server-id=2 // 不要重复,虽然serverid不重要了,还是要设置
master_info_repository=TABLE// 包括下面的信息,默认是存文件的,多个主库放文件里会有冲突,所以必须要存表里
relay_log_info_repository=TABLE

然后就是在mysql命令行里配置连接主服的参数,这些参数会写入到上面配置文件里指定的表里

change master to master_host=’主服ip’, master_user=’主服同步账号’, master_password=’同步账号密码’, master_log_file=’主服同步bin文件名’, master_log_pos=主服同步bin文件的位置 for channel “一个不重复的名称”;

这里有个很关键的东西for channel “xxx”,这个就是用来设置不同的主服,给每个主服都要命名,接下来启动从服,也要指定这个名称。

启动从服

start slave for channel “xxx”

查看从服状态

show slave status for channel “xxx”

配置多个主服,只要重复执行change master to 就可以了

 

mysql整形转换的坑

select * from table where id = '$id'

 

id如果输入12abc会怎样?,会把id=12的记录匹配出来,这是因为mysql会自动把12abc转换成整数12,当然前提是id是数值类型。

在有些时候这会误匹配,比如,用户登录,既可以输入用户名,又可以输入手机号码,还可以输入id,还可以输入邮箱,等等,sql恐怕会是这样的:

select * from users where name = '$name' or phone ='$name' or id = '$name' or ....

如果输入的字符串能被转换成非0整数,那就会造成误匹配。这种逻辑应该怎么处理呢,理清一下,首先要避免容易混淆的输入选项,比如id和手机号码,谁能确保13888888888是一个id还是一个手机号码,业务逻辑本身就存在误匹配的隐患。接下来要看的就是用户名这个字段,如果非要使用这个字段登陆,一定要确保用户名不能像email,不能纯数字,这样才能和email以及id区分开来。

根据用户输入,进行判断,如果是email格式,只匹配email,如果是纯数字,只匹配id,其他情况匹配用户名。

mysql和redis主从配置

mysql主服配置:

开启二进制日志,这是必须的

log-bin=mysql-bin //开启MYSQL二进制日志
server-id=1 //服务器ID不能重复
binlog-do-db=db1 //需要做主从备份的数据库名字,多个库只能定义多个属性,不能定义在一个属性里
binlog-do-db=db2
binlog-do-db=db3

添加一个供从服同步数据的账号

GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP' IDENTIFIED BY '密码';

重启主服

重启后,设为只读

FLUSH TABLES WITH READ LOCK;

查看并记录二进制文件和位置

show master status;

解除只读状态

UNLOCK TABLES;

开始配置从服务器

server-id=2 //服务器ID不能重复
master-port=3306 //主库的端口
replicate-do-db=db1 //需要做复制的数据库名,同样,多库要设置多个属性
replicate-ignore-table=db1.pre_common_session //自动跳过的表,session表没必要做复制
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396 //自动跳过的错误代码,以防复制出错被中断

手动将主服上数据同步到从服

重启从服

mysql命令行执行

change master to master_host='主服ip', master_user='主服上同步账号', master_password='同步账号密码', master_log_file='主服二进制文件名', master_log_pos=position的值;

 

启动从服
start slave;

查看从服状态

show slave status;
 Slave_IO_Running | Slave_SQL_Running,这两个yes状态就是正常了,否则的话,查看具体error信息

编辑从服配置

master-host=192.168.1.2 //主库A的IP
master-user=rep //刚才在主库创建的帐号
master-password=123 //密码

redis主从设置

主服务器不需要做什么调整,直接在从服务器配置文件里,加上

slaveof 192.168.0.2  6379

从属于 主机ip 端口

php从mysql里读出来的数据默认什么类型?

php是弱类型语言,但是不代表没有类型,或者随便什么类型。

一个典型的例子就是从mysql数据库里读出来的数据,其类型会和数据库里定义的类型一致吗?

$DB_CONN = mysql_connect("localhost", "user", "password");
mysql_select_db("database", $DB_CONN) ;
$result = mysql_query("select * from mytable limit 1",$DB_CONN);
$row = mysql_fetch_assoc($result);
var_dump($row["id"]);// id长整型主键

结果输出什么?

string(9) “647333052”

是字符类型,这在php里面并不会有什么问题,对id进行任何数值运算,都会自动转成整形。但是有些情景下就不一定了。

最简单的例子是转换成json数据给其他语言的系统用,就要注意这点,比如返回json格式的翻页数据:总行数、总页数、当前页、每页行数,显而易见,总行数是用count(*)从数据库直接输出的,而其他几个数值都是通过数值运算计算得来。最后转换的json数据可能是这样:

"pagesize":50,"recordcount":"11","totalpages":1,"nowpage":1

看到细微的区别没有?

mysql的rand()函数

mysql随机数函数rand(),返回0-1之间的随机数

用法:

select rand();

返回10以内的随机数:

select rand()*10

返回10以内的整数

select floor(rand()*10)

返回8-10以内的随机数

select 8+rand()*2

返回N-M的随机整数

select floor(N+rand()*(M-N))

初始化一个浮点字段,要求值0-5

update table set field=rand()*5

 

 

 

mysql优化器可以执行的优化

mysql优化器会对sql进行优化,到底会执行哪些优化呢?换句话说哪些不经意写出来的sql会被优化呢?

1、对联接表重新排序,并不按照sql中的顺序进行联接,mysql自己会做优化,所以糟糕的顺序影响并不大

2、将外联转换成内联,mysql会将外联转换成等价的内联,如果可能的话

3、代数等价法则,主要指where里用到的一些逻辑表达式,会转换成等价逻辑表达式,最常见的是1=1,会被优化掉

4、优化count min max等函数,比如myisam的无where的count,可以用到索引的min max实际上就是最左端和最右端的记录

5、计算和减少常量表达式,主要是在优化联接语句时,使用using联接的条件是常量,就可以从a表传递到b表

6、覆盖索引,当索引包含所查询的字段时,可以直接使用索引,避免读行数据。google:覆盖索引的概念

7、子查询优化,某些类型的子查询会优化成索引查找,哪些子查询并没有给出具体说明和例子,所以还是尽量避免子查询

8、早期终结,主要指limit或者where里使用了不可能的条件等等

9、相等传递,还是在联接查询中,联接字段使用了某条件,mysql会自动传递到另外一个表,无需在sql里显式申明,如Ta left join Tb using (Fc) where Ta.Fc > 1,mysql会自动优化并加上 and Tb.Fc>1

10、in(非子查询),mysql的in不等同与多个0r,mysql会对in里的项进行排序,如果in的字段可以用到索引,是相当快的,千万不要反过来,将多个in的选项改写成多个or

 

 

mysql查询sql优化

1、缩短锁时间,批量delete,加limit多次执行会快于一次delete

2、分解联接,将一个表联接多表,拆分为多个sql,然后用in()替代

分解联接的优点:

一、分拆sql,更容易匹配查询缓存

二、myisam表锁,分拆sql,每次只锁一个表,而联接要同时锁多个表

三、方便数据库扩展,可以将表分拆在多个库上

四、in的效率比join效率更高

五、减少多余行的访问,联接的行数会更多

六、分拆sql等同于手工执行哈希连接,而mysql内部执行联接用的是嵌套循环算法,哈希联接效率更高

问题:什么时候再应用程序端进行联接(分拆sql)效率更高?

一、可以缓存早期查询的大量数据

二、使用了多个myisam表

三、数据分布在不同的服务器上

四、对于大表使用in替换连接

五、一个联接引用了同一个表很多次

mysql执行查询的一般过程

一、客户端将查询发送到服务器

二、服务器检查查询缓存,如果找到了,就从缓存中返回结果,否则进行下一步(这里采用的是哈希sql匹配的方式,所以要想能准确匹配到查询缓存,sql的大小写,查询字段和条件等顺序一定要统一)

三、服务器解析,预处理和优化查询,生成执行计划

四、执行引擎调用存储引擎API执行查询(注意是存储引擎通过API调用执行具体的查询,所以同一条语句对于不同的引擎,会有不同的效率和行为)

五、服务器将结果发送回客户端

MAX_PACKET_SIZE配置对大数据查询效率的影响,mysql的c/s协议是半双工的,同一时刻要么是发送要么是接收,不能做到全双工,服务器端返回查询结果,分开发送的数据包必须全部返回完毕才放开控制权,所以这个最大包的size很重要。

待续

mysql两大引擎比较-比较官方的说法

这也是经常遇到的面试笔试题,请比较mysql两大引擎的优缺点或者特点等等,以下摘自高性能mysql第二版。

1、myisam

表锁,可能成为性能瓶颈。

不支持自动数据恢复,服务器宕机、异常停电等容易造成表损坏

不支持事务

只有索引被缓存在内存中,只缓存了mysql进程内部的索引,并保存在键缓冲区。操作系统缓存了表的数据。

紧密存储,行被紧紧地保存在一起,这样磁盘上的数据就能得到小的磁盘占用和快速的全表扫描。

2、innodb

事务性,支持事务和四种事务隔离级别。(哪四种,请google)

外键

行级锁

多版本,innodb使用多版本并发控制,这样在默认情况下可能会选择读取陈旧的数据,事实上,它的MVCC架构增添了很多复杂的和意料之外的行为。

按主键聚集

所有索引包含主键列,就算申明索引里不包含主键,也会自动加上主键

未压缩的索引,索引没有使用前缀压缩,索引会比myisam大很多

数据装载缓慢

阻塞AUTO_INCREMENT,新的自增,使用表级锁产生

没有缓存的count(*),没有where子句的count(*)查询,需要全表或索引扫描,没有表级计数器