存档

‘Mysql’ 分类的存档

select *导致的mysql线程sending data

2017年6月17日 没有评论

今天因为公司618活动到公司值班,早上10点钟左右的时候,领导突然跑过来说”select * from xxx”导致数据库服务器负载飙升,让我看一下什么问题。当时由于紧张还看错了表名,看成另外一张表,后来联系公司dba,又让他发了一遍引起数据库负载飙升的sql,发现原来是活动开始以后,大量查询活动信息引起的问题。

我们的活动信息表里面有大量的城市id和活动的表述信息,而当有大量的”select * from xxx”存在时,虽然这个表的数据量不是很大,只有区区几千条记录记录,但是大量的查询引起mysql线程状态卡在”sending data”时,服务器的负载就上来了。

那么什么是”sending data”状态哪?其实这是一个很容易引起误导的状态说明,”sending data”是包含读取数据+发送数据的。这里以innodb存储引擎来说,我们在使用索引找到我们所需的记录时,期初得到是索引列信息和主键信息,如果我们查询的信息索引列中已经包含,那么万事大吉,mysql会把这些信息发送给客户端。但是如果像我的例子中的是使用”select *”这种情况,或者要索引列中未包含我们需要需要的更多信息,那么这时mysql就会拿着主键id去数据行获取信息,然后再把些信息发送给客户端。

现在回到上面的问题,我们数据表记录数不多,为什么会引起”sending data”哪?我们活动信息表有几个字段是MEDIUMTEXT或者VARCHAR(3000)类似这种要存储比较长字符串内容的字段,所以在使用”select *”时我们把本来不需要,但是却占用很大空间的字段也返回了,造成了大量无用的IO操作,这里包含读取数据和发送数据。由于接口我们使用的是被动缓存,所以活动刚开始时这些请求都打到了数据库,后面接口缓存生效以后数据库压力就降下来了。

开发框架使用的是Lumen,而且我们主要使用的是里面的ORM-Elopment,有些地方没有注意查询时设定字段就引起了这个问题,说句实话以前在使用ORM时,我一直没有养成限制返回字段的习惯。希望看到这篇文章的同学也能引以为戒,以后尽量不要使用”seleect *”这样粗暴的查询方式。

mysql使用NULL值问题

2017年5月3日 没有评论

Mysql中NULL值的概念会让刚接触MySql的用户引起混乱,他们经常会认为NULL值和空字符串”是相等的。其实他们是不同的,我在接触MySql一段时间以后,还是把这2种情况混为一谈。我们来看一下下面2个插入语句:


mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

2个SQL语句都是向my_table表中的phone字段插入数据,但是第1个SQL插入1个空值(NULL值),而第2个插入的是1个空字符串。第1个可以理解为“我们还不知道用户的电话号码”,第2个SQL我们可以认为“我们知道这个人没有电话,所以并没有电话号码”,我们这是把它们附加到现实中可能会出现的2种业务情景中去。
为了便于处理空值(NULL值),我们可以使用”IS NULL”和”IS NOT NULL”操作符,或者使用IFNULL()函数。
在SQL使用的时候,在空值与其他任何其他值比较时都不会为真,即使比较的值也是1个空值。包含NULL的表达式总会产生一个NULL,除非是文档中另有说明的运算符和函数设计的表达式。下面的这些例子都会返回NULL:

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

如果要搜索列中为NULL的值,你不能使用 expr=NULL.下面的语句不会返回任何记录,因为expr=NULL对任何表示永远都不会返回真。

mysql> SELECT * FROM my_table WHERE phone = NULL;

要搜索列中为NULL的值,你必须要使用IS_NULL来匹配。下面的语句展示了如何寻找为NULL的手机号码和为空的手机号码。


mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

要了解更多NULL相关的信息和例子,请查看 Section 4.3.4.6, “Working with NULL Values”
如果你在使用MyISAM,InnoDB,或者MEMORY存储引擎,你可以在包含NULL的列上加索引。除此以为你必须把索引列声明为NOT NULL,而且你也不能插入NULL到这个字段。
当你使用LOAD DATA INFILE来读取数据时,空或者缺失的列会被当做”处理。要加载NULL值到1个列中,请在你的数据文件中使用\N。字面意思的NULL也可能在某些特殊情况下使用。具体请查看14.2.6节”LOAD DATA INFILE语法”
当使用DISTINCT,GROUP BY,或ORDER BY,所用NULL被认为是相同的。
当使用ORDER BY时,NULL值会被排在第一个,如果你指出desc倒序排序时会排在最后一个。
统计函数如COUNT(),MIN(),和SUM()会忽略NULL值。表达式COUNT(*)会统计行数而不是单个列的值。例如,下面的语句会产生2次计数统计。第一个语句统计这个表中所用的行数,第二格语句统计age列不是NULL值的数量。

mysql> SELECT COUNT(*), COUNT(age) FROM person;

对某些数据类型,MySQL会特殊处理其中的NULL值。如果你要插入NULL到1个TIMESTAMP列,当前的时间会被插入进去(手册版本为MySQL5.7)。如果你要插入NULL值到整形或者浮点型的带有AUTO_INCREMENT属性自增列,下1个自增的数字会被插入。

本文是对MySQL5.7中手册的翻译,原文地址https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html,如有错误请指正。

ubuntu mysql5.7无法远程连接问题

2016年10月31日 没有评论

晚上在做联系xtrabackup时,实验环境是一台centos7和一台ubuntu 16.04的虚拟机,centos7上面安装的mysql在创建完远程连接账号以后可以正常登陆,但是ubuntu却无法登录,提示“ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.99.88’ (61)”。其实问题很简单,这里主要记录一下当时我解决问题的思路,当然当时思路不是很正确,以前也没有遇到过类似的问题。

第一时间我想到的是自己是不是把账号弄错了,ubuntu上面的mysql没有对应的账号(带上本机3台mysql难免会弄错),就用一个root账号在ubuntu上本地登录,然后查看当前的系统有哪些用户,发现自己登录用的远程账号和权限没有问题。


mysql -uroot -p

use mysql;

select * from user\G;

然后就是防火墙,是不是防火墙没有开启。那么就查看ubuntu的防火墙。


sudo iptables -L #查看防火墙规则列表

sudo iptables -F  #清空防火墙列表

做完这些还是无法登录,google了一下,发现是ubuntu系统下mysql的bind问题。ubuntu中mysql默认绑定的是127.0.0.1,这样就只允许本机登录,想要允许其他地方登录只需要把bind改成公网ip或者任何ip均可。

编辑mysql的配置文件”/etc/mysql/mysql.conf.d/mysqld.cnf”


#bind-address = 127.0.0.1
bind-address = 0.0.0.0

然后重启mysql,问题就解决了!

mysql禁用validate-password插件

2016年8月17日 没有评论

近些版本的mysql做了很大的改动,在5.6.6以后的版本会开启validate-password插件。validate-password插件会对密码进行评估,如果不达标会提示你去进行修改.在生产环境这是非常不错的插件,弱口令就会消失,但是在开发环境就有些烦人了,谁都不想在每次等级都要输入一大串复杂的密码。所以我们可以选择降低validate-password对密码安全性的策略,或者直接就禁用它。

1 禁用validate-password

编辑my.cnf配置文件,在mysqld下面加入“validate-password=OFF”,然后重启mysql即可。

2 降低安全策略级别

首相我们来看一下validate-password相关的参数:

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 决定是否使用该插件(及强制/永久强制使用)。

validate_password_dictionary_file:插件用于验证密码强度的字典文件路径。

validate_password_length:密码最小长度。

validate_password_mixed_case_count:密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count:密码至少要包含的数字个数。

validate_password_policy:密码强度检查等级,0/LOW、1/MEDIUM、2/STRONG。

validate_password_special_char_count:密码至少要包含的特殊字符数。

其中,关于validate_password_policy-密码强度检查等级:

0/LOW:只检查长度。

1/MEDIUM:检查长度、数字、大小写、特殊字符。

2/STRONG:检查长度、数字、大小写、特殊字符字典文件。

我们可以将安全策略降低为LOW,相信这样虽然还会有长度限制,但是已经足够简单了。

编辑my.cnf文件,在mysqld下面加入:


[mysqld]

validate_password_policy=LOW

然后重启mysql即可!

mysql重置root密码

2016年4月14日 没有评论

今天在mac上面安装mysql,安装之后用root登录,发现进不去,说需要密码,使用DMG包安装时提示我生成的随机密码也尝试了,依旧不行!不过生成root密码页面提示如果无法登录可以尝试查看How to Reset the Root Password,进行重置root密码工作。如果你觉得自己英文还行可以直接参考前面那个连接去查看,好了,下面进入正题重置密码。

我是在mac下面做的重置root操作,不过windows下面的很类似,本文的必要条件是你有管理mysql服务器账号或者在本机上面,如果上面2个都不满足,那么这篇文章就不适合你了。

第一步关闭正在运行mysql服务器。

官网手册的命令是这样的,不过你可以像我这样通过ps查看mysql的进程id,然后kill它,当然这里最好把ps中启动mysql的命令先记录下来,后面会使用的。

kill `cat /mysql-data-directory/host_name.pid`
#ps方法
ps aux|grep 'mysql'
#这里把%pid%替换为上面ps给的进程id
sudo kill %pid%

第二步建立mysql init文件
这一步是要建立一个mysql init文件,配合mysqld的–init-file参数来使用的.init-file参数英文说明是”Read SQL commands from this file at startup”,我的个人理解是读取指定文件中的sql语句,并在mysql启动的时候执行它们。
mysql版本5.7.6和以后使用”ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;”,如果你的版本是其他版本可以使用”SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’);”尝试一下。

#5.7.6和以后版本
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
#5.7.6以前版本
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

保存文件。
第三步重启mysql
这时候我们来重启我们的mysql,不过是加上–init-file参数的命令。还记得我上面让你记住的ps下面的mysql启动命令吧,就是这里用的。官方手册是”mysqld_safe –init-file=/home/me/mysql-init &”,也许在其他类*unix系统上面可以,但是我在mac下面尝试了,启动不起来!如果你是centos或者ubuntu用户可以尝试一下官方手册的命令,不过mac下面还是建议使用ps中显示的命令,再后面追加–init-file参数。


#官网手册命令,mac用户不推荐

mysqld_safe --init-file=/home/me/mysql-init &

#mac用户推荐命令

sudo /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --init-file=/Users/king/sql/alter_root.sql &

“/Users/king/sql/alter_root.sql”为你第二步是保存的sql文件.此时再使用root用户用你重置的命令登录就可以成功登录了!

windows下面的重置root密码功能应该和mac下面类似,只要把shell命令换成dos命令即可,具体的可以参考上面的官网手册地址。

==2016-9-26号补更============================================================================

前几天在虚拟机里面装了mysql5.7.15,装完以后用root空密码登录失败.于是就用上面的方法重置密码,发现它并没有mysqld_safe文件,于是上面的方法就不是很适用了,就把重新查找了些资料,花了好久才解决!

我在stackoverflow上面找到了一种解决办法:

1. 停止mysql服务:
systemctl stop mysqld

2. 设置mysql的环境变量参数 
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. 启动mysql服务
systemctl start mysqld

4. root无密码登录
mysql -u root

5. 更新root密码
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

6. 关闭mysql服务
systemctl stop mysqld

7. 删除mysql环境参数
systemctl unset-environment MYSQLD_OPTS

8. 再次启动:
systemctl start mysqld

9. 用root登录:
. mysql -u root -p
当然可以把

skip-grant-tables写到”my.cnf”中重启mysql也可达到同样的目的!

利用crontab和mysqldump定时备份MySQL数据库

2014年3月11日 没有评论

前面的2篇文章,《Mysqldump使用介绍》和《CentOS安装crontab及使用方法》其实就是为了这篇文章做铺垫的,至于为什么要写这篇文章,还有从前2天工作中一次小事故说起。前2天同时在进行update时,没有注意加条件,把所有的数据表都更新了,更要命的是没办法恢复,而运维同学除了前一段时间我修改数据库时让他做的数据备份他竟然没有任何备份。从运维的角度来说,我觉得有些失职了,数据的备份应该也算是运维份内的工作吧,所以才有今天博主参考资料,并且自己动手写下这篇博客的原因!

博主的shell水平有限,这些大部分还是在网络上面参考别人的结果。下面我就分享一下自己的实验步骤和结果吧,希望对看到这篇文章的同学有所帮助!
创建保存备份文件的路径

[root@king-centos mysql]# mkdir /backup/mysql/

创建mysqldump备份脚本文件

[root@king-centos mysql]# vim /usr/sbin/mysqlbak

下面是脚本内容:
[/shell]
#!/bin/bash
# this is shell for auto db backup

# database name
dbname=mytest

dbuser=king

dbpwd=123456

backupdir=/backup/mysql/

backupfile=”$backupdir$dbname”_.$(date +%Y%m%d%M).sql

mysqldump -u$dbuser -p$dbpwd $dbname > $backupfile

echo ‘ok’;
[/shell]
保存脚本内容,并赋予其可执行权限

[root@king-centos mysql]# chmod +x /usr/sbin/mysqlbak

最要要做就是将其放入crontab啦

[root@king-centos mysql]# vim /etc/crontab

写入:

01 22 * * * root /usr/sbin/mysqlbak

上面的意思就是每天22:01用root的身份来执行这个脚本备份文件。
最后重启crond服务器即可:

[root@king-centos mysql]# service crond restart

当然博主的shell能力有限,大家能力强的可以把shell脚本写的更复杂一些!

PS: Only experienced accidents,we realized the importance of backups!
下面是博主在写这篇文章参考的资料地址,感谢了!
https://sites.google.com/site/polarisnotme/mysql/mysqlmysqldump
http://www.51cto.com/html/2005/1019/8433.htm

分类: Mysql 标签:

Mysqldump使用介绍

2014年3月11日 没有评论

其实博主在写这一篇博文之前也是没有用过mysql这个强大的命令的,不过为了下面的一篇文章,博主还是到mysql手册查找了一下mysqldump的相关解释和用法。当然其实如果我们善用google的话,也会找到很多相关的文章,不过我比较喜欢以前我的一位同事对我说的话,找一些资料最好去官网,因为一般他们是最权威的,也可能是最准确的!
那么,mysqldump是用来做什么的?我们看一下手册上面的怎么说的吧。那我就引用mysql手册的原话来回答吧,“mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句”。
下面这些内容摘自mysql手册关于mysqldump的使用,如果你能耐着心把这些东西看完,我相信你觉得像我一样收益匪浅的感叹“哇,原来还有这么牛X的工具啊”,却是以前博主从来没有用mysqldump进行过备份数据库,都是启用其他客户端使用的工具进行备份的!以下内容来源自mysql中文手册,有兴趣的同学可以认真看一下,我会对当中的例子加粗加黑,如果大家没有耐心可以看一下那些例子,也会对mysqldump有个大致的了解:
有3种方式来调用mysqldump:

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3...]
shell> mysqldump [options] –all–database

如果没有指定任何表或使用了—database或–all–database选项,则转储整个数据库。
要想获得你的版本的mysqldump支持的选项,执行mysqldump —help。
如果运行mysqldump没有–quick或–opt选项,mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用,但可以用–skip-opt禁用。
如果使用最新版本的mysqldump程序生成一个转储重装到很旧版本的MySQL服务器中,不应使用–opt或-e选项。
mysqldump支持下面的选项:
· —help,-?
显示帮助消息并退出。
· –add-drop–database
在每个CREATE DATABASE语句前添加DROP DATABASE语句。
· –add-drop-tables
在每个CREATE TABLE语句前添加DROP TABLE语句。
· –add-locking
用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。参见7.2.16节,“INSERT语句的速度”。
· –all–database,-A
转储所有数据库中的所有表。与使用—database选项相同,在命令行中命名所有数据库。
· –allow-keywords
允许创建关键字列名。应在每个列名前面加上表名前缀。
· —comments[={0|1}]
如果设置为 0,禁止转储文件中的其它信息,例如程序版本、服务器版本和主机。–skip—comments与—comments=0的结果相同。 默认值为1,即包括额外信息。
· –compact
产生少量输出。该选项禁用注释并启用–skip-add-drop-tables、–no-set-names、–skip-disable-keys和–skip-add-locking选项。
· –compatible=name
产生与其它数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用几个值,用逗号将它们隔开。这些值与设置服务器SQL模式的相应选项有相同的含义。参见5.3.2节,“SQL服务器模式”。
该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储输出更兼容的SQL模式值。例如,–compatible=oracle 不映射Oracle类型或使用Oracle注释语法的数据类型。
· –complete-insert,-c
使用包括列名的完整的INSERT语句。
· –compress,-C
压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。
· –create-option
在CREATE TABLE语句中包括所有MySQL表选项。
· —database,-B
转储几个数据库。通常情况,mysqldump将命令行中的第1个名字参量看作数据库名,后面的名看作表名。使用该选项,它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name语句包含在每个新数据库前的输出中。
· —debug[=debug_options],-# [debug_options]
写调试日志。debug_options字符串通常为’d:t:o,file_name‘。
· –default-character-set=charset
使用charsetas默认字符集。参见5.10.1节,“数据和排序用字符集”。如果没有指定,mysqldump使用utf8。
· –delayed-insert
使用INSERT DELAYED语句插入行。
· –delete-master-logs
在主复制服务器上,完成转储操作后删除二进制日志。该选项自动启用–master-data。
· –disable-keys,-K
对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地装载转储文件,因为在插入所有行后创建索引。该选项只适合MyISAM表。
· –extended-insert,-e
使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小,重载文件时可以加速插入。
· –fields-terminated-by=…,–fields-enclosed-by=…,–fields-optionally-enclosed-by=…,–fields-escaped-by=…,–行-terminated-by=…
这些选项结合-T选项使用,与LOAD DATA INFILE的相应子句有相同的含义。参见13.2.5节,“LOAD DATA INFILE语法”。
· –first-slave,-x
不赞成使用,现在重新命名为–lock-all-tables。
· –flush-logs,-F
开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。请注意如果结合–all–database(或-A)选项使用该选项,根据每个转储的数据库刷新日志。例外情况是当使用–lock-all-tables或–master-data的时候:在这种情况下,日志只刷新一次,在所有 表被锁定后刷新。如果你想要同时转储和刷新日志,应使用–flush-logs连同–lock-all-tables或–master-data。
· –force,-f
在表转储过程中,即使出现SQL错误也继续。
· –host=host_name,-h host_name
从给定主机的MySQL服务器转储数据。默认主机是localhost。
· –hex-blob
使用十六进制符号转储二进制字符串列(例如,’abc’ 变为0×616263)。影响到的列有BINARY、VARBINARY、BLOB。
· –lock-all-tables,-x
所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭–single-transaction和–lock-tables。
· –lock-tables,-l
开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表例如InnoDB和BDB,–single-transaction是一个更好的选项,因为它不根本需要锁定表。
请注意当转储多个数据库时,–lock-tables分别为每个数据库锁定表。因此,该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转储状态可以完全不同。
· –master-data[=value]
该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限,并且必须启用二进制日志。如果该选项值等于1,位置和文件名被写入CHANGE MASTER语句形式的转储输出,如果你使用该SQL转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2,CHANGE MASTER语句被写成SQL注释。如果value被省略,这是默认动作。
–master-data选项启用–lock-all-tables,除非还指定–single-transaction(在这种情况下,只在刚开始转储时短时间获得全局读锁定。又见–single-transaction。在任何一种情况下,日志相关动作发生在转储时。该选项自动关闭–lock-tables。
· –no-create-db,-n
该选项禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name语句,如果给出—database或–all–database选项,则包含到输出中。
· –no-create-info,-t
不写重新创建每个转储表的CREATE TABLE语句。
· –no-data,-d
不写表的任何行信息。如果你只想转储表的结构这很有用。
· –opt
该选项是速记;等同于指定 –add-drop-tables–add-locking –create-option –disable-keys–extended-insert –lock-tables –quick –set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启,但可以用–skip-opt禁用。要想只禁用确信用-opt启用的选项,使用–skip形式;例如,–skip-add-drop-tables或–skip-quick。
· –password[=password],-p[password]
连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了–password或-p选项后面的 密码值,将提示你输入一个。
· –port=port_num,-P port_num
用于连接的TCP/IP端口号。
· –protocol={TCP | SOCKET | PIPE | MEMORY}
使用的连接协议。
· –quick,-q
该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。
· –quote-names,-Q
用‘`’字符引用数据库、表和列名。如果服务器SQL模式包括ANSI_QUOTES选项,用‘”’字符引用名。默认启用该选项。可以用–skip-quote-names禁用,但该选项应跟在其它选项后面,例如可以启用–quote-names的–compatible。
· –result-file=file,-r file
将输出转向给定的文件。该选项应用在Windows中,因为它禁止将新行‘\n’字符转换为‘\r\n’回车、返回/新行序列。
· –routines,-R
在转储的数据库中转储存储程序(函数和程序)。使用—routines产生的输出包含CREATE PROCEDURE和CREATE FUNCTION语句以重新创建子程序。但是,这些语句不包括属性,例如子程序定义者或创建和修改时间戳。这说明当重载子程序时,对它们进行创建时定义者应设置为重载用户,时间戳等于重载时间。
如果你需要创建的子程序使用原来的定义者和时间戳属性,不使用–routines。相反,使用一个具有mysql数据库相应权限的MySQL账户直接转储和重载mysql.proc表的内容。
该选项在MySQL 5.1.2中添加进来。在此之前,存储程序不转储。
· –set-charset
将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SET NAMES语句,使用–skip-set-charset。
· –single-transaction
该选项从服务器转储数据之前发出一个BEGIN SQL语句。它只适用于事务表,例如InnoDB和BDB,因为然后它将在发出BEGIN而没有阻塞任何应用程序时转储一致的数据库状态。
当使用该选项时,应记住只有InnoDB表能以一致的状态被转储。例如,使用该选项时任何转储的MyISAM或HEAP表仍然可以更改状态。
–single-transaction选项和–lock-tables选项是互斥的,因为LOCK TABLES会使任何挂起的事务隐含提交。
要想转储大的表,应结合–quick使用该选项。
· –socket=path,-S path
当连接localhost(为默认主机)时使用的套接字文件。
· –skip–comments
参见—comments选项的描述。
· –tab=path,-T path
产生tab分割的数据文件。对于每个转储的表,mysqldump创建一个包含创建表的CREATE TABLE语句的tbl_name.sql文件,和一个包含其数据的tbl_name.txt文件。选项值为写入文件的目录。
默认情况,.txt数据文件的格式是在列值和每行后面的新行之间使用tab字符。可以使用–fields-xxx和–行–xxx选项明显指定格式。
注释:该选项只适用于mysqldump与mysqld服务器在同一台机器上运行时。你必须具有FILE权限,并且服务器必须有在你指定的目录中有写文件的许可。
· –tables
覆盖—database或-B选项。选项后面的所有参量被看作表名。
· –triggers
为每个转储的表转储触发器。该选项默认启用;用–skip-triggers禁用它。
· –tz-utc
在转储文件中加入SET TIME_ZONE=’+00:00′以便TIMESTAMP列可以在具有不同时区的服务器之间转储和重载。(不使用该选项,TIMESTAMP列在具有本地时区的源服务器和目的服务器之间转储和重载)。–tz-utc也可以保护由于夏令时带来的更改。–tz-utc默认启用。要想禁用它,使用–skip-tz-utc。该选项在MySQL 5.1.2中加入。
· –user=user_name,-u user_name
连接服务器时使用的MySQL用户名。
· –verbose,-v
冗长模式。打印出程序操作的详细信息。
· –version,-V
显示版本信息并退出。
· –where=’where-condition‘, -w ‘where-condition‘
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将条件引用起来。

例如:
“–where=user=’jimf’”
“-wuserid>1″
“-wuserid<1″
–xml,-X
将转储输出写成XML。
还可以使用–var_name=value选项设置下面的变量:
· max_allowed_packet
客户端/服务器之间通信的缓存区的最大大小。最大为1GB。
· net_buffer_length
客户端/服务器之间通信的缓存区的初始大小。当创建多行插入语句时(如同使用选项–extended-insert或–opt),mysqldump创建长度达net_buffer_length的行。如果增加该变量,还应确保在MySQL服务器中的net_buffer_length变量至少这么大。
还可以使用–set-variable=var_name=value或-O var_name=value语法设置变量。然而,现在不赞成使用该语法。
mysqldump最常用于备份一个整个的数据库:

shell> mysqldump –opt db_name &gt; backup-file.sql

你可以这样将转储文件读回到服务器:

shell> mysql db_name < backup-file.sql
或者为:
shell> mysql -e “source /path-to–backup/backup-file.sql” db_name
mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库:
shell> mysqldump –opt db_name | mysql –host=remote_host -C db_name
可以用一个命令转储几个数据库:
shell> mysqldump —database db_name1 [db_name2 …] > my_databases.sql

如果你想要转储所有数据库,使用–all–database选项:
shell> mysqldump –all-databases > all_databases.sql
如果表保存在InnoDB存储引擎中,mysqldump提供了一种联机备份的途径(参见下面的命令)。该备份只需要在开始转储时对所有表进行全局读锁定(使用FLUSH TABLES WITH READ LOCK)。获得锁定后,读取二进制日志的相应内容并将锁释放。因此如果并且只有当发出FLUSH…时正执行一个长的更新语句,MySQL服务器才停止直到长语句结束,然后转储则释放锁。因此如果MySQL服务器只接收到短(“短执行时间”)的更新语句,即使有大量的语句,也不会注意到锁期间。
shell> mysqldump –all-databases –single-transaction > all_databases.sql
对于点对点恢复(也称为“前滚”,当你需要恢复旧的备份并重放该备份以后的更改时),循环二进制日志(参见5.11.3节,“二进制日志”)或至少知道转储对应的二进制日志内容很有用:
shell> mysqldump –all-databases –master-data=2 > all_databases.sql

shell> mysqldump –all-databases –flush-logs –master-data=2 > all_databases.sql
如果表保存在InnoDB存储引擎中,同时使用–master-data和–single-transaction提供了一个很方便的方式来进行适合点对点恢复的联机备份。
参考资料来源:http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html#mysqldump

分类: Mysql 标签:

Mysql使用别名多表查询join时问题

2014年3月11日 没有评论

今天在写一个sql语句时遇到一个问题,问了好多人才给出了一个答案,当然以我们目前对mysql的了解也不知道这个答案到底是对错,不过此处做为唯一合理的答案,我就当做正确答案了,如果博主下面的说法是错误的,欢迎大家及时指出!

下午在对t1,t2,t3这3个表进行多表管理查询时,爆出了一个让我“莫名其妙”的错误。最初的时候我的sql语句是这样书写的:

SELECT * FROM t1, t2 LEFT JOIN t3 ON (t1.id=t3.id) WHERE t1.id=t2.id

但是在执行的时候报错“Unknown column t1.id”,以前博主习惯利用AS 表别名来进行多表关联查询,这让博主很凌乱,一时不知道如何下手解决了!

后来博主把自己的sql语句写的正规了一点,如下:

SELECT * FROM t1 INNER JOIN t2 LEFT JOIN t3 ON (t1.id=t3.id) WHERE t1.id=t2.id

然后再次执行,发现ok了!不再报错了,博主顿时欣喜若狂啊,但是博主怀着剖根问底的精神,对前者报错很好奇错在了那里!对mysql有了解的人都知道用“,”把表名分开和INNER JOIN是等同的,那么为什么我这里报错了那?

博主动用了自己所有的关系也查看了一些资料(都不知道咋问谷歌啊),最终一个QQ群的叫做“蜀都-Rory”的同学给博主解释了一下,原来前者被mysql解释器解释以后实际执行时是这样子的:

SELECT * FROM t1,( t2 LEFT JOIN t3 ON (t1.id=t3.id)) WHERE t1.id=t2.id

这就把中间部分被当做一个子查询了,而子查询在mysql执行的时候是优先执行的,所以就没有和t1关联,所以自然就报前面提到那个错误了!

分类: Mysql 标签:

Mysql Master&Slave 实战

2014年3月11日 没有评论

从开始着手做Mysql的Master&Slave模式到现在好长一段时间了,中间由于遇到问题也停了一段时间,今天早上总算配好了,这里就分享一下自己的经验和其中遇到的问题!
刚开始的时候我也没有直接去Mysql的官网去查看手册,而且用google搜索了2篇网上其他同学的教程。如果第一次接触mysql的主从,可以去这里,看看这位同学写的东西吧,里面包含了Mysql主从的工作原理,是一个不错的入门教程。下面我们就进入正题吧!
实验环境
服务器A(master):192.168.10.8
服务器B(Slave):192.168.10.10
服务器A、B系统环境:CentOS 6.3
Mysql版本:.5.5.31
配置Master主机
编辑A服务器上面mysql的配置文件,开启Mysql的bin-log功能和设置Master相关参数和被复制数据库的相关参数。

[root@king-centos ~]# vim /etc/my.cnf

##主从配置服务器ID号,保持主从服务器不要一致

server-id=1
## Mysql开启bin-log功能
##log-bin=/data/mysql/binlog/mysql-bin.log
log-bin=mysql-bin.log
## Multiple db can use ”,” separation
## 设置被从服务器复制的数据库名称
binlog-do-db=master

Notice: 这里需要注意的是,像博主使用的是yum第三方源的mysql版本,在配置bin-log时,自定义目录时,mysqld无法启动,但是不适用自定义目录,在mysql默认目录一切正常。具体原因博主亦是不是十分清楚,把它归在于mysql编译时的配置问题上了。我用一个二进制版本的5.7的就可以自定义bin-log的路径。

然后下面就是创建用于从服务器访问的用户:

mysql> GRANT REPLICATION SLAVE ON *.* TO ’slave10′@”192.168.10.%” IDENTIFIED BY ’123456′;
mysql> FLUSH PRIVILEGES;

下面就是记录当前master服务器的状态:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS\G;

*************************** 1. row ***************************
File: mysql-bin.000007
Position: 107
Binlog_Do_DB: master
Binlog_Ignore_DB:
1 row in set (0.00 sec)
mysql> UNLOCK TABLES;

配置Slave主机

编辑从服务器配置文件my.cnf,命令和上面相同。

server-id = 3
log-bin = mysql-bin-log
## 从服务器从主服务器复制数据库名称,多个数据库可以用”,”分割
replicate-do-db = master

Notice:这里需要注意的是如果你把主从服务的server-id配成一样的,会爆下面这个错误:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

即IO相关进程无法启动。

此时需要同步Mysql进行主从复制的数据,我的方法是使用mysqldump把主服务器的数据库导出然后使用mysql进行同步。

同步完数据库以足够权限的用户登录mysql,不熟悉的话就使用root就好!

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.10.8′,
-> MASTER_USER=’slave10′,
-> MASTER_PASSWORD=’123456′,
-> MASTER_LOG_FILE=’mysql-bin.000007′,
->MASTER_LOG_POS=107;

这里需要注意的一下是,针对mysql5.1版本或者以前版本也可以通过配置my.cnf文件达到相同的目的.
例如:

server-id               = 2
log_bin                 = mysql-bin
master-host     =192.168.10.10
master-user     =slave10
master-pass     =123456
master-port     =3306
master-connect-retry=60 #如果从服务器发现主服务器断掉,重新连接的时间差(秒)
replicate-do-db =master#只复制某个库
replicate-ignore-db=mysql #不复制某个库

但是如果在mysql的5.5中做这种配置就会出现如下错误:
130620 6:04:52 InnoDB: 5.5.31 started; log sequence number 1609072
130620 6:04:52 [ERROR] /usr/libexec/mysqld: unknown variable ‘master-host=192.168.10.10′
下面就是启动mysql的slave进程了:

mysql> start slave;
mysql> show slave status \G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

找到上面的内容,那么我就可以去试试看看,目前来说,我们的主从已经配置好了!
我们可以到master服务器上面的表中添加一条记录,然后看看从服务器会不会自动复制,如果自动复制了。那么,Congratulations,我们的mysql master&slave配置好了!
最后附上mysql手册中相关资料地址:http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-howto

Windows服务方式启动MySQL

2014年3月11日 没有评论

最近在MySQL官网上面下载了最新版本的MySQL,没有选择msi安装包进行下载,选择普通的zip的下载,而非msi安装包中,是不提供mysql的config配置向导的,只有一个my-default.ini文件,那么MySQL的启动和配置我们就需要自己手动来进行配置了。

环境:
OS : Win7旗舰版_X64位
MySQL : mysql-5.6.14-winx64

1 安装配置MySQL
将MySQL安装包解压到D:\mysql中,然后复制个my-default.ini并将其重命名为my.ini,然后对其进行简单的配置就可以达到启动Mysql的目的。
my.ini文件:

[mysqld]
basedir = D:\mysql\
datadir = D:\mysql\data
port = 3306

接下来将MySQL的bin目录加入到系统环境变量PATH中:
计算机->属性->高级->环境变量->系统变量->PATH,在PATH的最后加入”;D:\mysql\bin”。
打开MSDOS界面,输入

mysqld --verbose --help

可以查看mysqld的各项参数。

2 安装MySQL启动服务
打开MSDOS界面输入

D:\mysql\bin\mysqld --install MySQL --defaults-file=D:\mysql\my.ini

–install 参数后面跟的是服务名称
–defaults-file 参数后面制定mysql运行时使用的配置文件
看到Service successfully installed表明服务安装成功。

注意事项:
如果dos命令这样“mysqld –install MySQL –defaults-file=D:\mysql\my.ini”,服务的路径前缀会默认使用C:\Program Files (x86)\mysql\bin这些内容,这样服务就无法正常启动了!

3 管理服务命令

NET START MySQL #启动mysql
NET STOP MySQL #停止mysql
NET RESTART MySQL #重启MySQL

至此,我们的MySQL配置安装基本成功,此时在MSDOS界面输入

mysql
就可以登陆到mysql服务器了,默认使用的是root用户,密码为空。
当然您也可以通过配置my.ini来打造自己的个性的mysql服务器,这里就不做多讲了!

分类: Mysql, windows 标签: