MySQL的mysqldump工具的基本用法

导出要用到MySQL的mysqldump工具,基本用法是:   

shell> mysqldump [OPTIONS] database [tables]  

如果你不给定任何表,整个数据库将被导出。   

通过执行mysqldump –help,你能得到你mysqldump的版本支持的选项表。  

注意,如果你运行mysqldump没有–quick或–opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。  

mysqldump支持下列选项:   

–add-locks   
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。   
–add-drop-table   
在每个create语句之前增加一个drop table。   
–allow-keywords   
允许创建是关键词的列名字。这由表名前缀于每个列名做到。   
-c, –complete-insert   
使用完整的insert语句(用列名字)。   
-C, –compress   
如果客户和服务器均支持压缩,压缩两者间所有的信息。   
–delayed   
用INSERT DELAYED命令插入行。   
-e, –extended-insert   
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)   
-#, –debug[=option_string]   
跟踪程序的使用(为了调试)。   

–help  

显示一条帮助消息并且退出。   
–fields-terminated-by=…   
   
–fields-enclosed-by=…   
   
–fields-optionally-enclosed-by=…   
   
–fields-escaped-by=…   
   
–fields-terminated-by=…   


这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。   
LOAD DATA INFILE语法。   
-F, –flush-logs   
在开始导出前,洗掉在MySQL服务器中的日志文件。   
-f, –force,   
即使我们在一个表导出期间得到一个SQL错误,继续。   
-h, –host=..   
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。   
-l, –lock-tables.   
为开始导出锁定所有表。   
-t, –no-create-info   
不写入表创建信息(CREATE TABLE语句)   
-d, –no-data   
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!   
–opt   
同–quick –add-drop-table –add-locks –extended-insert –lock-tables。   
应该给你为读入一个MySQL服务器的尽可能最快的导出。   
-pyour_pass, –password[=your_pass]   
与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。   
-P port_num, –port=port_num   
与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)   
-q, –quick   
不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。   
-S /path/to/socket, –socket=/path/to/socket   
与localhost连接时(它是缺省主机)使用的套接字文件。   
-T, –tab=path-to-some-directory   
对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。 注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和–lines–xxx选项来定。   
-u user_name, –user=user_name   
与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。   
-O var=option, –set-variable var=option设置一个变量的值。可能的变量被列在下面。   
-v, –verbose   
冗长模式。打印出程序所做的更多的信息。   
-V, –version   
打印版本信息并且退出。   
-w, –where=\’where-condition\’   
只导出被选择了的记录;注意引号是强制的!   
“–where=user=\’jimf\'” “-wuserid>1” “-wuserid<1” 

最常见的mysqldump使用可能制作整个数据库的一个备份:  
mysqldump –opt database > backup-file.sql   
但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:   
mysqldump –opt database | mysql –host=remote-host -C database   
由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:   
shell> mysqladmin create target_db_name   
shell> mysql target_db_name < backup-file.sql  
就是  
shell> mysql 库名 < 文件名 

================================

几个常用用例:

1.导出整个数据库
 mysqldump -u 用户名 -p 数据库名 > 导出的文件名    
 mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
  mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql
 -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 
4.导入数据库
  常用source 命令
  进入mysql数据库控制台,
  如mysql -u root -p 
  
  mysql>use 数据库
  然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
  mysql>source d:\wcnc_db.sql

load data infile 导入数据 InnoDB 外键约束

用户表:user
主键:id

内容表:post
主键:id
外键:user_id(关联到 user 表的主键 id)

post 表的数据文件存在文本文件中,字段之间用 tab 分隔,当要把文件导入到 post 表时,因 user 和 post 都是 InnoDB 引擎的,有外键约束,运行导入语句:

load data infile '/home/leakon/post.sql' ignore into table post ignore 1 lines ( user_id, title, unixtime );

MySQL 报错,信息如下:

Cannot add or update a child row: a foreign key constraint  fails (`leakon/post`, CONSTRAINT `post_FK_1` FOREIGN KEY (`user_id`)  REFERENCES `user` (`id`))

反复尝试,总是失败,后来查了一下解决办法,只要在装载数据之前,插入下述语句,即可暂时关闭外键约束检查:

SET FOREIGN_KEY_CHECKS = 0;

然后再次运行 load data infile 语句,即可立即导入数据。

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

今天服务器遇到了一个很熟悉的问题输入#mysql -u root -pERROR 2002 (HY000):Can’t connect to local MySQL server随即上网找寻答案根据大家提供的方法我逐一尝试
方案1.
1.#ps -A|grep mysql 显示类似:1829 ? 00:00:00 mysqld_safe
1876 ? 00:00:31 mysqld
2.#kill -9 1829
3.#kill -9 1876
4.#/etc/init.d/mysql restart
5.#mysql -u root -p 他的麻烦解决了,我的还没解决!
继续找方案2
先查看 /etc/rc.d/init.d/mysqld status 看看m y s q l 是否已经启动.
另外看看是不是权限问题.
————————————————————————————
[root@localhost beinan]#chown -R mysql:mysql /var/lib/mysql
[root@localhost beinan]# /etc/init.d/mysqld start
启动 MySQL: [ 确定 ]
[root@localhost lib]# mysqladmin -uroot password ‘123456’
[root@localhost lib]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffe他的也解决了,我的麻烦还在继续,依然继续寻找
方案3
问题解决了,竟然是max_connections=1000 他说太多了,然后改成500也说多,无奈删之问题解决了。还是不行
方案4 /var/lib/mysql 所有文件权限 改成mysql.mysql 不行不行方案5
摘要:解决不能通过mysql.sock连接MySQL问题 这个问题主要提示是,不能通过 ‘/tmp/mysql.sock’连到服务器,而php标准配置正是用过’/tmp/mysql.sock’,但是一些mysql安装方法将 mysql.sock放在/var/lib/mysql.sock或者其他的什么地方,你可以通过修改/etc/my.cnf文件来修正它,打开文件,可 以看到如下的东东:
[mysqld]
socket=/var/lib/mysql.sock
改一下就好了,但也会引起其他的问题,如mysql程序连不上了,再加一点:
[mysql]
socket=/tmp/mysql.sock
或者还可以通过修改php.ini中的配置来使php用其他的mysql.sock来连,这个大家自己去找找

或者用这样的方法:
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

成功了,就是这样ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

OK!

以上资料是网上的资料
我今天遇到的情况是我使用的mysqld_safe –user=mysql &重启mysql发现无法启动mysql,最后用/etc/init.d/mysqld start启动成功

关于MySQL中TPS和QPS的计算方式

TPS – Transactions Per Second(每秒传输的事物处理个数),即服务器每秒处理的事务数,如果是InnoDB会显示,没有InnoDB就不会显示。
TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME

use information_schema;
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME =’COM_COMMIT’;
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME =’COM_ROLLBACK’;
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =’UPTIME’;
select (@num_com+@num_roll)/@uptime;

QPS – Queries Per Second(每秒查询处理量)MyISAM 引擎
QUESTIONS/UPTIME

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME =’QUESTIONS’;
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =’UPTIME’;
select @num_queries/@uptime;

如何实现MySQL远程访问?

  以下的文章主要向大家介绍的是实现MySQL程访问的实际操作流程,以及在实现MySQL远程访问的过程中哪些的相关事项是十分重要的,以下就是文章的具体内容讲述,望你浏览之后会对其有更深的了解。

  MySQL远程访问 :

  1、改表法。可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 “mysql” 数据库里的 “user” 表里的 “host” 项,从”localhost”改称”%”

  mysql -u root -pvmwaremysql>use mysql;

  mysql>update user set host = ‘%’ where user = ‘root’;

  mysql>select host, user from user;

  2、授权法。例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。

  GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’IDENTIFIED BY ‘mypassword’ WI

  TH GRANT OPTION;

  在MySQL远程访问中如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码

  GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’192.168.1.3’IDENTIFIED BY

  ’mypassword’ WITH GRANT OPTION;

  我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES

  使修改生效,就可以了

  另外一种方法:

  在安装mysql的机器上运行:

  1、d:\mysql\bin\>mysql -h localhost -u root

  这样应该可以进入MySQL服务器

  2、mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’WITH GRANT OPTION

  赋予任何主机访问数据的权限

  3、mysql>FLUSH PRIVILEGES

  修改生效

  4、mysql>EXIT

  退出MySQL服务器

  这样就可以在其它任何的主机上以root身份登录啦。

MySQL日期函数From_unixtime及UNIX_TIMESTAMP及DATE_FORMAT

后者只能格式化标准日期格式,时间戳的不行
 

from_unixtime()是MySQL里的时间函数
date为需要处理的参数(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串
后面的 ‘%Y%m%d’ 主要是将返回值格式化
例如:
mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y%m%d’ )  
->20071120
mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y年%m月%d’ )
->2007年11月20
UNIX_TIMESTAMP() 是与之相对正好相反的时间函数

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

   若无参数调用,则返回一个 Unix timestamp (‘1970-01-01 00:00:00′ GMT 之后的秒数) 作为无符号整数。若用date 来调用 UNIX_TIMESTAMP(),它会将参数值以’1970-01-01 00:00:00’ GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

例如:

mysql> SELECT UNIX_TIMESTAMP() ; (执行使得时间:2009-08-06 10:10:40)
->1249524739
mysql> SELECT UNIX_TIMESTAMP(‘2009-08-06’) ;
->1249488000

SELECT *
FROM student
WHERE regTime > UNIX_TIMESTAMP( curdate( ) ) //
今天所有学生注册记录。

MySQL将null转代为0

1、如果为空返回0

select ifnull(null,0)
 

2、如果为空返回0,否则返回1
select if(isnull(col),0,1) as col.
MYSQL 中的IFNULL函数
IFNULL(expr1,expr2)
如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。
mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,’yes’);
-> ‘yes’

IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>0且expr1<>NULL),那么IF()返回expr2,否则它返回expr3。IF()返回一个数字或字符串值,取决于它被使用的上下文。
mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,’yes’,’no’);
-> ‘yes’
mysql> select IF(strcmp(‘test’,’test1′),’yes’,’no’);
-> ‘no’
expr1作为整数值被计算,它意味着如果你正在测试浮点或字符串值,你应该使用一个比较操作来做。
mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1
在上面的第一种情况中,IF(0.1)返回0,因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中,比较测试原来的浮点值看它是否是非零,比较的结果被用作一个整数。
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END
 
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
第一个版本返回result,其中value=compare-value。第二个版本中如果第一个条件为真,返回result。如果没有匹配的result值,那么结果在ELSE后的result被返回。如果没有ELSE部分,那么NULL被返回。
mysql> SELECT CASE 1 WHEN 1 THEN “one” WHEN 2 THEN “two” ELSE “more” END;
-> “one”
mysql> SELECT CASE WHEN 1>0 THEN “true” ELSE “false” END;
-> “true”
mysql> SELECT CASE BINARY “B” when “a” then 1 when “b” then 2 END;

-> NULL
 

浅析MySQL数据碎片的产生

MySQL列表,包括MyISAM和InnoDB这两种最常见的类型,而根据经验来说,其碎片的产生及消除都是随机的。碎片会在你的表格中留下明显的空白,而这会给列表扫描工作带来相当大的困扰。对你的列表进行优化,这样会使列表的全面及分区扫描工作进行得更有效率。

碎片——实例

MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。 每当MySQL从你的列表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更 大。当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操 作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用。

这种额外的破碎的存储空间在读取效率方面比正常占用的空间要低得多。让我们看一个实例。

我们将创建一个数据库(有时也称其为大纲)及一个测试用的列表:

现在让我们在列表中加入如下几行:

现在我们进行碎片查看:

现在我们删除一行,并再次检测:

需要注意的是,“data_free”一栏显示出了我们删除第二行后所产生的留空空间。想象一下如果你有两万行指令的话,结果是什么样的。以此推 算,它们将耗费四十万字节的存储空间。现在如果你将两万条命令行删到只剩一行,列表中有用的内容将只占二十字节,但MySQL在读取中会仍然将其视同于一 个容量为四十万字节的列表进行处理,并且除二十字节以外,其它空间都被白白浪费了。

清理碎片

幸运的是一旦你锁定了这一问题,MySQL提供了一种简便的修正方法。这就是所谓的优化列表,具体内容如下:

性能考量

“优化列表”功能在进行中会对整个列表进行锁定。对于小型列表,这一功能的效果非常好,因为整个列表的读取和修改速度都会很快。但对于那些体积巨大的列表来说,这一过程将消耗很长时间,并且其间会中断或减少可用的应用程序数量。怎么办?

再一次,MySQL幸运地提供了一项堪称伟大的功能,名为“主-主复制”。 在这种配置之下,你的后台数据库实际上成为两个单独的数据库,一个主动可调用的,一个被动可调整的。这两个数据库在各方面来说都是完全相同的。要实现各种 在线操作——包括“优化列表”操作——只需在你的被动数据库中即可进行。这将不会对你的应用程序造成丝毫影响。一旦优化操作完成,主、被动数据库将互相转 换,以便应用程序直接指向二号数据库,对还未进行优化的主动数据库部分自动开始优化工作。

这时,两套数据库的角色已经互换,而应用程序也将顺利指向二号数据库,执行与在一号数据库上相同的列表优化。而现在主动已经转换为被动,因此不会中断主要任务处理。

其它命令

显示你数据库中存在碎片的全部列表:

如果你更改了某个列表的存储引擎,你也应该对这一列表进行碎片清理。这是因为MySQL的工作原理导致其必须读取整个列表,然后利用新的存储引擎将内容写回磁盘,而在此过程中碎片所在的位置及影响到的数据都对执行效率造成了严重的不良影响。

上述情况如下所示:

结论

如果你发现一些列表中包含了大量的数据留空现象,那么对其进行优化是绝对值得的,因为这一过程会大大提升列表的读取性能及应用表现。

mysql where id in 排序 按in的顺序排序

select id fromtablewhere id in(2,1,3,5)order bysubstring_index(‘2,1,3,5’,id,1);
substring_index(str,delim,count) 字符串截取函数
str 要截取的字符串 delim 截取的分割符 count 截取的数量
返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。
那 order by substring_index(‘2,1,3,5′,id,1) 这个啥意思呢
这个是在字符串’2,1,3,5′里查找id,如果找不到,就返回整个字符串select id fromtablewhere id in(2,1,3,5)order by find_in_set(id,‘2,1,3,5’)
find_in_set(str,strlist)
字符串函数
如果字符串str在由N子串组成的表strlist之中,返回一个1到N的值。如果str不是在strlist里面或如果strlist是空字符串,返回0。

SELECT FIND_IN_SET(‘b’,‘a,b,c,d’);
->2
所以 find_in_set返回的是一个1到N的值,在order by的话,就是按顺序排了。

此句测试中id为主键,type一直为all,不知是有优化方法?

 

另一种,没有试过(收藏一下)

SQL: select * from table where id IN (3,6,9,1,2,5,8,7);
这样的情况取出来后,其实,id还是按1,2,3,4,5,6,7,8,9,排序的,但如果我们真要按IN里面的顺序排序怎么办?SQL能不能完成?是否需要取回来后再foreach一下?
其实可以这样
sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
出来的顺序就是指定的顺序了

 

 

项目 中用到

$order = “order by substring_index(‘$ids’,g.goods_id,1)”;//按in 排序用

MySQL不能启动 Can’t start server : Bind on unix socket: Permission denied

mysql突然不能启动,查看最后的启动日志如下:

080825 09:38:04 mysqld started
080825 9:38:04 [ERROR] Can’t start server : Bind on unix socket: Permission denied
080825 9:38:04 [ERROR] Do you already have another mysqld server running on socket: /var/mysql.sock ?
080825 9:38:04 [ERROR] Aborting

080825 9:38:04 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

080825 09:38:04 mysqld ended
 

my.cnf配置如下:
[mysqld]
datadir=/usr/local/mysql/data
socket=/var/mysql.sock

[mysql]
socket=/tmp/mysql.sock
[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/usr/local/mysql/mysqld.log

pid-file=/usr/local/mysql/mysqld.pid
 

根据日志文件显示,首先检查运行权限,再看一下/var/mysql.sock,发现/var/mysql.sock目录下并没有该文件,是否是没有权限 写/var目录?OK,那就用root用户运行一下safe_mysqld,启动正常。使用mysql连接数据库,提示错误,连接不到数据库服务器, /tmp目录下没有mysql.sock这个文件,到这里,基本清楚了Mysql客户端和服务器端在本机的通信方式了,通过一个名为mysql.sock 文件来进行初始化通信的,因此该文件所存放的目录必须双方都有权限可以访问到,而且服务器在启动时需要写该文件,如果my.cnf中这两项配置不正确,就 会出现Permission denied等类似错误。将配置文件修改如下,就没有问题了。

 

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock
[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid