Category Archives: MySQL

学一点 mysql 双机异地热备份——快速理解mysql主从，主主备份原理及实践

双机热备的概念简单说一下，就是要保持两个数据库的状态自动同步。对任何一个数据库的操作都自动应用到另外一个数据库，始终保持两个数据库数据一致。 这样做的好处多。 1. 可以做灾备，其中一个坏了可以切换到另一个。 2. 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。  对于异地热备，尤其适合灾备。废话不多说了。我们直接进入主题。 我们会主要介绍两部分内容： 一， mysql 备份工作原理 二， 备份实战   我们开始。 我使用的是mysql 5.5.34，     一， mysql 备份工作原理 简单的说就是把 一个服务器上执行过的sql语句在别的服务器上也重复执行一遍， 这样只要两个数据库的初态是一样的，那么它们就能一直同步。   当然这种复制和重复都是mysql自动实现的，我们只需要配置即可。   我们进一步详细介绍原理的细节， 这有一张图： 上图中有两个服务器， 演示了从一个主服务器（master） 把数据同步到从服务器（slave）的过程。 这是一个主-从复制的例子。 主-主互相复制只是把上面的例子反过来再做一遍。 所以我们以这个例子介绍原理。   对于一个mysql服务器， 一般有两个线程来负责复制和被复制。当开启复制之后。   1. 作为主服务器Master，  会把自己的每一次改动都记录到 二进制日志 Binarylog 中。 （从服务器会负责来读取这个log， 然后在自己那里再执行一遍。）   2. 作为从服务器Slave， 会用master上的账号登陆到 master上， 读取master的Binarylog,  写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。  到这里主服务器上的更改就同步到从服务器上了。   在mysql上可以查看当前服务器的主，从状态。 其实就是当前服务器的 Binary（作为主服务器角色）状态和位置。 以及其RelayLog（作为从服务器）的复制进度。   例如我们在主服务器上查看主状态：  

稍微解释一下这几行的意思： 1. 第一行表明 当前正在记录的 binarylog文件名是： mysql-bin.000014. 我们可以在mysql数据目录下，找到这个文件：   2.  第二行， 107. 表示当前的文件偏移量， 就是写入在mysql-bin.000014 文件的记录位置。 这两点就构成了 主服务器的状态。  配置从服务器的时候，需要用到这两个值。 告诉从服务器从哪读取主服务器的数据。 （从服务器会登录之后，找到这个日志文件，并从这个偏移量之后开始复制。）   3. […]

mysql通过binlog恢复数据

如果mysql不小心操作失误导致数据错误或者丢失这时候binlog起到了很大的作用 恢复有几种方式 1.按时间恢复--start-datetime 如果确定了时间点，那么按时间恢复是一个再好不过的事，一般是通过日常的定期备份+差异备份（日志） 如果日常备份在4点，出错的时间在12：00点，12：30发现的，首先我们要确认12点出了什么错，12点以后的数据还能不能继续使用，如果不影响，那么我们只需跳过12：00执行的数据即可 首先，恢复4点的备份 然后，通过binlog获取4点到11：59的数据，获取的数据可以直接作用到数据库中，避免二次污染，最好先生成sql文件  mysqlbinlog --start-datetime="2014-11-07 04:01:00"  --stop-datetime="2014-11-07 11:59:00" /data/mysql/data/mysql-bin.000020 >/home/madong/aa.sql 在获取12：01-12：30的数据  mysqlbinlog --start-datetime="2014-11-07 12:01:00"  --stop-datetime="2014-11-07 12:30:00" /data/mysql/data/mysql-bin.000020 >>/home/madong/aa.sql 最后通过mysql命令行 source   /home/madong/aa.sql 如果12点以后的数据还不能继续使用 那么我们只需要恢复到12点，12：00-12：30的数据就不需要恢复 2.通过--start-position节点 思路和上面的一样，首先要确定节点的id，在恢复 例： 描述：2014-11-07 13:50 游戏数据库数据全部清空 故障发现时间：13：50 故障发生时间：13：40左右 故障结束时间：15：55 恢复过程： 故障触发，游戏马上关服，进行维护，故障原因已知（数据没了） 1.首先确认恢复时间点 恢复带2014-11-07 13:30 2.因为刚开服不久，所以准备从最原始开始恢复，不从4点的完整备份节点恢复 准备不直接操作，先生成sql，  mysqlbinlog --start-datetime="2014-11-06 09:50:00"  --stop-datetime="2014-11-07 13:30:00" /data/mysql/data/mysql-bin.000020 >/home/madong/aa.sql （aa.sql 500M左右） 3.因数据库重置之后又有玩家注册，如果导入数据会出现主键冲突，所以再次清空数据库（保留基表数据） 4.导入sql 进入mysql命令行，并切换数据库（use DBNAME） source /home/madong/aa.sql 执行开始时间14：57 结束时间15：54  大约一个小时 from:http://www.cnblogs.com/liuyisai/p/5621909.html

MySQL字符串函数substring：字符串截取

MySQL 字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。 1. 字符串截取：left(str, length) mysql> select left('example.com', 3); +————————-+ | left('example.com', 3) | +————————-+ | exa                     | +————————-+ 2. 字符串截取：right(str, length) mysql> select right('example.com', 3); +————————--+ | right('example.com', 3) | +————————--+ | com                      | +————————--+ 实例： #查询某个字段后两位字符 select right(last3, 2) as last2 from historydata limit 10; #从应该字段取后两位字符更新到另外一个字段 update historydata set last2=right(last3, 2); 3. 字符串截取：substring(str, pos); substring(str, pos, len) 3.1 从字符串的第 4 个字符位置开始取，直到结束。 mysql> select substring('example.com', 4); +——————————+ […]

MySql 里的IFNULL、NULLIF和ISNULL用法

今天用到了MySql里的isnull才发现他和MSSQL里的还是有点区别，现在简单总结一下： mysql中isnull,ifnull,nullif的用法如下： isnull(expr) 的用法： 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。 mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的。 isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。 IFNULL(expr1,expr2)的用法： 假如expr1   不为   NULL，则   IFNULL()   的返回值为   expr1; 否则其返回值为   expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。 mysql>   SELECT   IFNULL(1,0); ->   1 mysql>   SELECT   IFNULL(NULL,10); ->   10 mysql>   SELECT   IFNULL(1/0,10); ->   10 mysql>   SELECT IFNULL(1/0,’yes'); ->   'yes' IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、   REAL或 INTEGER。假设一个基于表达式的表的情况，     或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值： CREATE   TABLE   tmp   SELECT   IFNULL(1,’test')   AS   test； 在这个例子中，测试列的类型为   CHAR(4)。 NULLIF(expr1,expr2)  的用法：  如果expr1 =   expr2     成立，那么返回值为NULL，否则返回值为   expr1。这和CASE   WHEN   expr1   =   expr2 THEN   NULL   ELSE   expr1   END相同。 mysql>   SELECT NULLIF(1,1); […]

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

一.问题描述  014-12-15 20:00:29 4398 [Warning] Unsafe statement written to the binary log using statement format since  BINLOG_FORMAT = STATEMENT. INSERT… ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO longxibendi (lxid, l_id, l_aplply, l_types, ctimes) VALUES  (75692, 218, 8, 2, 1418644829) ON DUPLICATE KEY UPDATE ctimes=1418644829 一个mysql master 的err log 报上面的错误。 二.问题原因 查了下原因，longxibendi这个表上有2个唯一键。则使用 INSERT… ON DUPLICATE KEY UPDATE   ，且当前数据库binlog_format是statement格式，这种sql语句就会报unsafe。 查了下手册 INSERT … ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys.  When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends. An INSERT … ON DUPLICATE KEY UPDATE statement against a […]

mysql报错IP address could not be resolved解决方法

这个警告不会影响数据库的访问 但是当有大量的这种日志产生的时候，数据库之前的错误信息 就会很难去查询了。连接数越多，产生报警日志的频率越高。 一、错误描述 数据库的alert.log中，我们经常会出现下面的警告:

二、问题产生的原因 出现错误的原因是MYSQL Server在本地内存中维护了一个非本地的Client TCP cache，这个cache中包含了远程Client的登录信息，比如IP地址，hostname等信息。 如果Client连接到服务器后，Mysql首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析，如果解析不到，就会去DNS中进行解析，如果还是解析失败 就是在error log中写入这样的警告信息。 三、解决的办法： 1、修改配置文件 可以通过两个参数来disable这个功能，在MYSQL的配置文件中[mysqld]中加入下面的参数:

重新授权，将所有访问数据库服务器的授权方式都改成IP形式的。

2、添加授权。 将所有访问数据库服务器的授权方式都改成IP形式。 不同的用户用不同的用户名和密码。

然后去 mysql数据库下面的 user表  和db表 下面删除掉那些含有含有主机名字的权限记录。 四、总结 1、要么加上

使得MySQL将不再通过DNS解析地址。 2、赋予权限 要么在赋予权限的时候 直接用ip地址，去掉那些用主机名字的权限。 from:http://www.uedsc.com/ip-address-could-not-be-resolved.html

MySQL DATEDIFF() 函数

定义和用法 DATEDIFF() 函数返回两个日期之间的天数。 语法

date1 和 date2 参数是合法的日期或日期/时间表达式。 注释：只有值的日期部分参与计算。 实例 例子 1 使用如下 SELECT 语句：

结果： DiffDate 1 例子 2 使用如下 SELECT 语句：

结果： DiffDate -1   from:http://www.w3school.com.cn/sql/func_datediff_mysql.asp

MySql四舍五入

FLOOR(X) 返回不大于X的最大整数值。 mysql> select FLOOR(1.23); -> 1 mysql> select FLOOR(-1.23); -> -2 注意返回值被变换为一个BIGINT！ CEILING(X) 返回不小于X的最小整数值。 mysql> select CEILING(1.23); -> 2 mysql> select CEILING(-1.23); -> -1 注意返回值被变换为一个BIGINT！ ROUND(X) 返回参数X的四舍五入的一个整数。 mysql> select ROUND(-1.23); -> -1 mysql> select ROUND(-1.58); -> -2 mysql> select ROUND(1.58); -> 2 注意返回值被变换为一个BIGINT! ROUND(X,D) 返回参数X的四舍五入的有D为小数的一个数字。如果D为0，结果将没有小数点或小数部分。 mysql> select ROUND(1.298, 1); -> 1.3 mysql> select ROUND(1.298, 0); -> 1 注意返回值被变换为一个BIGINT! from:http://www.cnblogs.com/youyou/archive/2005/11/07/270783.html

Asp.net+Mysql,查询出错:由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败

在网上看到的解决方案基本都是说host文件中127.0.0.1 localhost 的对应， 但我查看了host文件，并没有问题，纠结半天，拿查询语句在Mysql Workbench上执行，报了一个错，说是子查询包含多个结果，我去~！这很简单的一个错误asp.net竟然没能准确反馈。 于是在子查询语句后面加limit 1，Ok~ 所以，出现“由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败”这个错误时，有可能是查询语句有问题，而不是连接有问题。 from:http://blog.csdn.net/idoiknow/article/details/8923553