存档

文章标签 ‘mysql’

Centos6 yum安装MariaDB

2014年9月18日 没有评论

MaiaDB介绍

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。

MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB(名称代号为Aria)来代替MySQL的InnoDB。

阅读全文…

分类: DB, Mariadb, Mysql 标签: ,

关系数据库还是NoSQL数据库

2011年7月4日 没有评论

转载:http://www.infoq.com/cn/news/2011/01/relation-db-nosql-db

作者 孙立 发布于 2011年1月22日

上一篇简单的说明了为什么要使用NoSQL。接下来我们看下如何把NoSQL引入到我们的项目中,我们到底要不要把NoSQL引入到项目中。

在过去,我们只需要学习和使用一种数据库技术,就能做几乎所有的数据库应用开发。因为成熟稳定的关系数据库产品并不是很多,而供你选择的免费版本就 更加少了,所以互联网领域基本上都选择了免费的MySQL数据库。在高速发展的WEB2.0时代,我们发现关系数据库在性能、扩展性、数据的快速备份和恢 复、满足需求的易用性上并不总是能很好的满足我们的需要,我们越来越趋向于根据业务场景选择合适的数据库,以及进行多种数据库的融合运用。几年前的一篇文 章《One Size Fits All – An Idea Whose Time Has Come and Gone》就已经阐述了这个观点。

当我们在讨论是否要使用NoSQL的时候,你还需要理解NoSQL也是分很多种类的,在NoSQL百花齐放的今天,NoSQL的正确选择比选择关系数据库还具有挑战性。虽然NoSQL的使用很简单,但是选择却是个麻烦事,这也正是很多人在观望的一个原因。

NoSQL的分类

NoSQL仅仅是一个概念,NoSQL数据库根据数据的存储模型和特点分为很多种类。

类型 部分代表 特点
列存储 Hbase 

Cassandra

Hypertable

顾名思义,是按列存储数据的。最大的特点是方便存储结构化和半结构化数据,方便做数据压缩,对针对某一列或者某几列的查询有非常大的IO优势。
文档存储 MongoDB 

CouchDB

文档存储一般用类似json的格式存储,存储的内容是文档型的。这样也就有有机会对某些字段建立索引,实现关系数据库的某些功能。
key-value存储 Tokyo Cabinet / Tyrant 

Berkeley DB

MemcacheDB

Redis

可以通过key快速查询到其value。一般来说,存储不管value的格式,照单全收。(Redis包含了其他功能)
图存储 Neo4J 

FlockDB

图形关系的最佳存储。使用传统关系数据库来解决的话性能低下,而且设计使用不方便。
对象存储 db4o 

Versant

通过类似面向对象语言的语法操作数据库,通过对象的方式存取数据。
xml数据库 Berkeley DB XML 

BaseX

高效的存储XML数据,并支持XML的内部查询语法,比如XQuery,Xpath。

以上NoSQL数据库类型的划分并不是绝对,只是从存储模型上来进行的大体划分。它们之间没有绝对的分界,也有交差的情况,比如Tokyo Cabinet / Tyrant的Table类型存储,就可以理解为是文档型存储,Berkeley DB XML数据库是基于Berkeley DB之上开发的。

NoSQL还是关系数据库

虽然09年出现了比较激进的文章《关系数据库已死》,但是我们心里都清楚,关系数据库其实还活得好好的,你还不能不用关系数据库。但是也说明了一个事实,关系数据库在处理WEB2.0数据的时候,的确已经出现了瓶颈。

那么我们到底是用NoSQL还是关系数据库呢?我想我们没有必要来进行一个绝对的回答。我们需要根据我们的应用场景来决定我们到底用什么。

如果关系数据库在你的应用场景中,完全能够很好的工作,而你又是非常善于使用和维护关系数据库的,那么我觉得你完全没有必要迁移到NoSQL上面, 除非你是个喜欢折腾的人。如果你是在金融,电信等以数据为王的关键领域,目前使用的是Oracle数据库来提供高可靠性的,除非遇到特别大的瓶颈,不然也 别贸然尝试NoSQL。

然而,在WEB2.0的网站中,关系数据库大部分都出现了瓶颈。在磁盘IO、数据库可扩展上都花费了开发人员相当多的精力来优化,比如做分表分库 (database sharding)、主从复制、异构复制等等,然而,这些工作需要的技术能力越来越高,也越来越具有挑战性。如果你正在经历这些场合,那么我觉得你应该尝 试一下NoSQL了。

选择合适的NoSQL

如此多类型的NoSQL,而每种类型的NoSQL又有很多,到底选择什么类型的NoSQL来作为我们的存储呢?这并不是一个很好回答的问题,影响我们选择的因素有很多,而选择也可能有多种,随着业务场景,需求的变更可能选择又会变化。我们常常需要根据如下情况考虑:

  1. 数据结构特点。包括结构化、半结构化、字段是否可能变更、是否有大文本字段、数据字段是否可能变化。
  2. 写入特点。包括insert比例、update比例、是否经常更新数据的某一个小字段、原子更新需求。
  3. 查询特点。包括查询的条件、查询热点的范围。比如用户信息的查询,可能就是随机的,而新闻的查询就是按照时间,越新的越频繁。

NoSQL和关系数据库结合

其实NoSQL数据库仅仅是关系数据库在某些方面(性能,扩展)的一个弥补,单从功能上讲,NoSQL的几乎所有的功能,在关系数据库上都能够满足,所以选择NoSQL的原因并不在功能上。

所以,我们一般会把NoSQL和关系数据库进行结合使用,各取所长,需要使用关系特性的时候我们使用关系数据库,需要使用NoSQL特性的时候我们使用NoSQL数据库,各得其所。

举个简单的例子吧,比如用户评论的存储,评论大概有主键id、评论的对象aid、评论内容content、用户uid等字段。我们能确定的是评论内 容content肯定不会在数据库中用where content=’’查询,评论内容也是一个大文本字段。那么我们可以把 主键id、评论对象aid、用户id存储在数据库,评论内容存储在NoSQL,这样数据库就节省了存储content占用的磁盘空间,从而节省大量IO, 对content也更容易做Cache。

NoSQL代替MySQL

在某些应用场合,比如一些配置的关系键值映射存储、用户名和密码的存储、Session会话存储等等,用NoSQL完全可以替代MySQL存储。不但具有更高的性能,而且开发也更加方便。

NoSQL作为缓存服务器

MySQL+Memcached的架构中,我们处处都要精心设计我们的缓存,包括过期时间的设计、缓存的实时性设计、缓存内存大小评估、缓存命中率等等。

NoSQL数据库一般都具有非常高的性能,在大多数场景下面,你不必再考虑在代码层为NoSQL构建一层Memcached缓存。NoSQL数据本身在Cache上已经做了相当多的优化工作。

Memcached这类内存缓存服务器缓存的数据大小受限于内存大小,如果用NoSQL来代替Memcached来缓存数据库的话,就可以不再受限于内存大小。虽然可能有少量的磁盘IO读写,可能比Memcached慢一点,但是完全可以用来缓存数据库的查询操作。

规避风险

由于NoSQL是一个比较新的东西,特别是我们选择的NoSQL数据库还不是非常成熟的产品,所以我们可能会遇到未知的风险。为了得到NoSQL的好处,又要考虑规避风险,鱼与熊掌如何兼得?

现在业内很多公司的做法就是数据的备份。在往NoSQL里面存储数据的时候还会往MySQL里面存储一份。NoSQL数据库本身也需要进行备份(冷 备和热备)。或者可以考虑使用两种NoSQL数据库,出现问题后可以进行切换(避免出现digg使用Cassandra的悲剧)。

总结

本文只是简单的从MySQL和NoSQL的角度分析如何选择,以及进行融合使用。其实在选择NoSQL的时候,你可能还会碰到关于CAP原则,最终一致性,BASE思想的考虑。因为使用MySQL架构的时候,你也会碰到上面的问题,所以这里没有阐述。

关于作者

孙立,目前在凤凰网负责底层组的研发工作。曾就职于搜狐和ku6。多年互联网从业经验和程序开发,对分布式搜索引擎的开发,高并发,大数据量网站系 统架构优化,高可用性,可伸缩性,分布式系统缓存,数据库分表分库(sharding)等有丰富的经验,并且对运维监控和自动化运维控制有经验。开源项目 phplock,phpbuffer的作者。近期开发了一个NOSQL数据库存储INetDB,是NoSQL数据库爱好者。他的新浪微博是:http://t.sina.com.cn/sunli1223

分类: DB 标签: ,

创建MySQL用户 赋予某指定库表的权限

2011年3月28日 没有评论

mysql安装详解 

本文实例,运行于 MySQL 5.0 及以上版本。

MySQL 赋予用户权限命令的简单格式可概括为:

一、grant 普通数据用户,查询、插入、更新、删除 数据库中所有表数据的权利。

或者,用一条 MySQL 命令来替代:

二、grant 数据库开发人员,创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant 操作 MySQL 外键权限。

grant 操作 MySQL 临时表权限。

grant 操作 MySQL 索引权限。

grant 操作 MySQL 视图、查看视图源代码 权限。

grant 操作 MySQL 存储过程、函数 权限。

三、grant 普通 DBA 管理某个 MySQL 数据库的权限。

其中,关键字 “privileges” 可以省略。

四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。

五、MySQL grant 权限,分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上:

2. grant 作用在单个数据库上:

3. grant 作用在单个数据表上:

4. grant 作用在表中的列上:

5. grant 作用在存储过程、函数上:

六、查看 MySQL 用户权限

查看当前用户(自己)权限:

查看其他 MySQL 用户权限:

七、撤销已经赋予给 MySQL 用户权限的权限。

revoke 跟 grant 的语法差不多,只需要把关键字 “to” 换成 “from” 即可:

八、MySQL grant、revoke 用户权限注意事项

1. grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。

2. 如果想让授权的用户,也可以将这些权限 grant 给其他用户,需要选项 “grant option“

这个特性一般用不到。实际中,数据库权限最好由 DBA 来统一管理。

 ———————————————————————————————-

授权命令GRANT 语句的语法如下:
GRANT privileges (columns)
ON what
TO user IDENTIFIEDBY “password”
WITH GRANT OPTION
对用户授权
mysql>grant rights on database.* to user@host identified by “pass”;
例1:
增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。
grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;
ON 子句中*.* 说明符的意思是“所有数据库,所有的表”
例2:
增加一个用户test2密码为abc, 让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作。
grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;
例子3
增加一个用户custom,他能从主机localhost、server.domain和whitehouse.gov连接。他只想要从 localhost存取bankaccount数据库,从whitehouse.gov存取expenses数据库和从所有3台主机存取customer 数据库。他想要从所有3台主机上使用口令stupid。

为了使用GRANT语句设置个用户的权限,运行这些命令:
shell> mysql –user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.* TO custom@localhost IDENTIFIED BY ‘stupid’;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.* TO custom@whitehouse.gov IDENTIFIED BY ‘stupid’;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.* TO custom@’%’ IDENTIFIED BY ‘stupid’;
==============================================
权限信息用user、db、host、tables_priv和columns_priv表被存储在mysql数据库中(即在名为mysql的数据库中)。
权限 列 Context
select Select_priv 表
insert Insert_priv 表
update Update_priv 表
delete Delete_priv 表
index Index_priv 表
alter Alter_priv 表
create Create_priv 数据库、表或索引
drop Drop_priv 数据库或表
grant Grant_priv 数据库或表
references References_priv 数据库或表
reload Reload_priv 服务器管理
shutdown Shutdown_priv 服务器管理
process Process_priv 服务器管理
file File_priv 在服务器上的文件存取

1.select、insert、update和delete权限 允许你在一个数据库现有的表上实施操作,是基本权限
2.alter权限允许你使用ALTER TABLE
3.create和drop权限允许你创建新的数据库和表,或抛弃(删除)现存的数据库和表 如果你将mysql数据库的drop权限授予一个用户,该用户能抛弃存储了MySQL存取权限的数据库!
4.grant权限允许你把你自己拥有的那些权限授给其他的用户。
你不能明显地指定一个给定用户应该被拒绝存取。即,你不能明显地匹配一个用户并且然后拒绝连接。你不能指定一个用户有权创建立或抛弃一个数据库中的表,也不能创建或抛弃数据库本身。 可以同时列出许多被授予的单个权限。
例如,如果想让用户能读取和修改已有表的内容,但又不允许创建新表或删除表,可按如下授权:
GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db.* TO ‘user’@’%’ IDENTIFIEDBY “pass”

以上是我从别的地方拷贝过来后稍作修改的文字,下面自己写一些需要注意的东西。

为什么使用了Grant all on db.* to user identified by “pass”后,在主机上访问数据库还会出现ERROR 1045 (28000): Access denied for user ‘user’@’localhost’ (using password: YES) 的错误提示?

解答方法如下:运行命令 Grant all on db.* to ‘user’@’localhost’ identified by “pass”

原因是:当不加@选项时,效果与加@’%’是一样的,’%’从名义上包括任何主机,(%必须加上引号,不然与@放在一起可能不会被辨认出。)不过有些时候(有些版本)’%’不包括localhost,要单独对@’localhost’进行赋值

分类: DB 标签:

mysql安装-配置-优化-备份-主从

2011年3月25日 没有评论

mysql安装详解 

     

第1章       安装… 4

1.1.     LINUX安装… 4

1.2.     复制(Replication)设置… 4

第2章       维护管理… 7

2.1.     常用系统命令… 7

2.2.     常用MySQL命令… 7

2.3.     密码和权限… 8

2.4.     修改连接方式… 8

2.5.     连接管理… 9

2.6.     备份恢复… 9

2.7.     导入导出… 10

2.8.     日志… 11

2.9.     管理MyISAM数据表… 11

2.10.       管理InnoDB数据表… 12

2.11.       索引… 12

2.12.       存储过程… 12

2.13.       优化… 13

第3章       FAQ.. 14

3.1.     连接错误… 14

3.2.     读写优化… 14

 第1章     安装

1.1.   LINUX安装

       从http://dev.mysql.com/downloads/mysql/5.0.html 下载5.0.x最新版本,可以选择专用于RHEL AS 4的RPM包:

# rpm -ivh MySQL-server-community-5.0.51a-0.rhel4.i386.rpm  (自动设置自启动,service mysqld start)# rpm -ivh MySQL-client-community-5.0.51a-0.rhel4.i386.rpm# rpm -ivh MySQL-devel-community-5.0.51a-0.rhel4.i386.rpm (安装开发头文件等,第三方程序需要,比如要编译PHP支持MYSQL就需要)

# MySQL-shared-compat-5.0.51a-0.rhel4.i386.rpm (选择安装,不一定必须)

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf    (拷贝配置文件样本)

 

       有时候安装时会遇到包依赖的问题,比如安装server可能会提示需要依赖perl-DBI-1.40-5.i386.rpm包,这时可以使用参数不理会依赖性:

# rpm -ivh –nodeps MySQL-server-community-5.0.51a-0.rhel4.i386.rpm

 

1.2.   复制(Replication)设置

Mysql的Replication复制有两个模式:主从(Master->Slave)和双机互相备份。

主从(Master->Slave)模式安装,SrvA为主SrvB为辅,两者MYSQL版本最好一致或者Slave版本比Master新:

SrvA MYSQL> create database shopdb;SrvA MYSQL> GRANT FILE ON *.* TO repuser@’192.168.1.11′ IDENTIFIED BY ‘reppass’;添加Slave连接Master的MYSQL帐号)SrvA MYSQL> GRANT REPLICATION SLAVE ON *.* TO repuser@’192.168.1.11′ IDENTIFIED BY ‘reppass’;更加严格更小权限的一种设置 

SrvA MYSQL> flush privileges;

SrvA # vi /etc/my.cnf

server-id = 1

log-bin=xxx(这里最好指定文件名,否则会自动使用主机名,而将来主机名如果被修改,将导致复制错误

binlog-do-db=shopdb(指定具体需要binlog的数据库名称

#binlog-ignore-db=mysql

…….重启SrvA MYSQL………

SrvA MYSQL> show master status;查看是否运行在Master模式

SrvA MYSQL> flush tables with read lock;锁住表禁止写入后拷贝数据,也可以停止MYSQL再拷贝数据

SrvA # cp -r shopdb shopdbbackup拷贝MYSQL的shopdb库的数据文件

SrvA MYSQL> unlock tables;拷贝数据完成后解除表锁定

SrvB # cp -r shopdbbackup shopdb将SrvA上的shopdb库数据文件覆盖到SrvB 

SrvB # chown -Rf mysql.mysql shopdb设置数据文件正确的宿主属性 

SrvB # vi /etc/my.cnf

server-id = 2

master-host = 192.168.1.10

master-user = repuser

master-password = reppass

master-port = 3306

master-connect-retry = 10

replicate-do-db = shopdb

…….重启SrvB MYSQL………

SrvB MYSQL> show slave status;查看是否运行在Slave模式

       主从(Master->Slave)模式中,Slave可多个。整个复制过程由三个进程控制,分别是Master上的IO进程、Slave上的IO进程和Slave上的SQL进程。Slave上的IO进程连接上Master请求日志文件指定位置(或最开始)之后的日志内容;Master上的IO进程返回给Slave的IO进程包括bin-log文件名称、本次读取到bin-log的最后位置、bin-log的内容;Slave的IO进程接收数据后依次添加到Slave端的relay-log文件最末端,并将取到的Master端bin-log文件名和最后位置记录到master-info文件中;Slave上的SQL进程检测到relay-log新增内容后,解析执行。

       双机互备模式,比如SrvC既是SrvA的Slave,又作为SrvD的Master,异地两套应用系统备份可以使用这种模式:

SrvC # vi /etc/my.cnfserver-id = 3master-host = 192.168.1.10

master-user = repuser

master-password = reppass

master-port = 3306

master-connect-retry = 10

replicate-do-db = shopdb

log-bin

log-slave-updates(将作为Slave时更新的数据写入到bin-log中,否则SrvD将丢失数据

slave-skip-errors=all

       复制Replicate可能会发生同步错误,如下:

ERROR: 1062  Duplicate entry ‘1’ for key 1091125  9:52:03 Slave: Error ‘Duplicate entry ‘1’ for key 1′ on query. Default database: ‘yyyy’. Query: ‘INSERT INTO xxxxdetail_20090801 VALUES(1, ‘Îߺþ’, ‘г¿Íø°É’, ‘aaaaa’, ‘7777’, ‘212.102.27.210’)’, Error_code: 1062091125  9:52:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.008’ position 78361623

       两种方法解决以上报错:

# vi /etc/my.cnf   Slave上,需要重启MYSQL)slave-skip-errors=1062(忽略此报错,跳过后继续同步,不会因为此错误使同步停下来)MYSQL > set global sql_slave_skip_counter=1; (忽略这一句同步语句,可执行多次,好处是不像上面方法一需要重启MYSQL

      

第2章     维护管理

2.1.   常用系统命令

# mysql -uroot   (空密码本地连接)# mysql -uroot -p   (交互方式输入密码) # mysql -uroot –password=xxx  (命令行中输入密码)

# mysql -uroot -S /var/lib/mysql/mysql.sock  (指定套接字,不在默认/tmp/mysql.sock或者mysql_multi情况下)

# mysqladmin ping   (数据库的PING)

mysqld is alive

# mysql -e ‘show processlist’   (-e无交互执行SQL语句)

+——+——-+—————–+———+———+——+——-+——————+

| Id   | User  | Host            | db      | Command | Time | State | Info             |

+——+——-+—————–+———+———+——+——-+——————+

|    2 | green | localhost:43789 | greendb | Sleep   |    7 | NULL  | NULL             |

| 7141 | root  | localhost       | NULL    | Query   |    0 | NULL  | show processlist |

+——+——-+—————–+———+———+——+——-+——————+

# mysql -e ‘show full processlist’   (更详细?)

# mysqladmin processlist   (与上面命令效果相同)

2.2.   常用MySQL命令

mysql> show variables like version; (版本信息)mysql> select @@version; (版本信息)mysql> show variables like ‘have%’; (支持哪些功能)

mysql> status; (查询MySQL状态)

mysql> show processlist; (查询MySQL的连接状态)

mysql> tee /tmp/mysql.log; (将屏幕输出到文件中)

mysql> notee; (关闭上面的功能)

mysql> source /tmp/x.sql; (执行文件中的SQL语句)

# mysql dbname < /tmp/x.sql (跟上面这句同义)

mysql> show warnings(显示最后一次的报警信息)

mysql> show databases(显示本用户可访问的所有数据库清单)

mysql> show tables(显示本用户可访问的所有数据表清单)

mysql> show columns from mysql.user(显示本用户可访问的所有数据列清单)

mysql> show index from mysql.user(显示本用户可访问的所有索引清单)

2.3.   密码和权限

# mysql -urootmysql> use mysqlmysql> delete from user where user=”;   (把空用户名的删除)

mysql> update user set password=password(‘1234!@#$’) where user=’root’;   (把root默认空口令修改掉)

mysql> GRANT ALL ON dbname.* TO dbuser@localhost IDENTIFIED BY ‘1234!@#$’;   (新建用户并赋予权限,ALL是指GRANT之外的所有权限)

mysql> GRANT USAGE ON dbname.* TO(拒绝所有权限) 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON dbname.* TO

mysql> GRANT LOCK TABLES,CREATE TEMPORARY TABLES,EXECUTE ON dbname.* TO(更细分的一些权限) 

mysql> GRANT FILE ON dbname.* TO(INTO OUTFILE,LOAD DATA等需要) 

mysql> GRANT PROCESS ON dbname.* TO(show processlist时可以看到其他用户的进程,否则只能对自己) 

mysql> GRANT SUPER ON dbname.* TO(可以KILL其他用户的进程,否则只能对自己) 

mysql> flush privileges;

# mysqladmin -uroot password ‘1234!@#$’

 

[mysqld]

old-passwords(4.1版本之后采用了新的加密算法,此选项沿用老的算法)

C:\> mysqld-nt.exe –skip-grant-tables  (通过此参数运行mysql无需认证即可登录,通过此方法找回root的密码)

[mysqld]

skip-grant-tables

2.4.   修改连接方式

可以将MYSQL的监听IP修改为LOCALHOST,修改my.ini或者/etc/my.cnf文件:

[mysqld]port = 3306bind = 127.0.0.1(仅监听本地TCP 3306端口

#skip-networking(禁用TCP/IP 3306

socket = /var/lib/mysql/mysql.sock(LINUX下可通过套接字文件连接

#enable-named-pipes(WINDOWS下可用命名管道连接

#socket = MySQL(WINDOWS下命名管道的名字

#shared_memory(WINDOWS下可用共享内存连接

#shared_memory_base_name = MYSQL(WINDOWS下共享内存的名字

2.5.   连接管理

查看当前的MYSQL Session连接,然后kill断开连接:

mysql> SELECT concat(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE user=’root’;+————————+| concat(‘KILL ‘,id,’;’) |

+————————+

| KILL 3101;             |

| KILL 2946;             |

+————————+

2 rows IN SET (0.00 sec)

mysql> SELECT concat(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE user=’root’ INTO OUTFILE ‘/tmp/a.txt’;

Query OK, 2 rows affected (0.00 sec)

mysql> source /tmp/a.txt;

Query OK, 0 rows affected (0.00 sec)

2.6.   备份恢复

# mysqldump dbname > backupfile.sql# mysqldump dbname tablesname > backupfile.sql# mysqldump –all-databases > backupfile.sql备份所有数据库 

# mysqldump –lock-all-tables dbname > backupfile.sql针对MyISAM,由于mysqldump备份前对表逐个读操作锁定,表A与表B备份时间不同而导致其中一条关联数据被修改,最好的方法是备份前锁定所有的表

# mysqldump –single-transaction dbname > backupfile.sql针对InnoDB,LOCK表解决不了任何问题,必须使用事务

# mysqldump mysql proc > backupproc.sqlmysqldump无法对指定的单个数据库备份其存储过程,要么就备份所有数据库的存储过程

# mysqldump “–where=db=’dbname'” –no-create-info mysql proc > backupproc.sql可以用where细分出来,必须禁用CREATE TABLES命令,否则会导致恢复是破坏

# myback.pl

调用mysqldump的PERL脚本http://www.mswanson.com/跟mysqlhotcopy类似

# mysql dbname < backupfile.sql恢复数据库

mysql> use dbname

mysql> source /tmp/backupfile.sql;这种方式需要权限,并事先指定DBNAME

# mysql –default-character-set=gbk dbname < backupfile.sql

新版本mysqldump使用UTF-8字符集来生成备份文件,如果是老版本mysqldump生成的备份文件,在迁移到新系统中需要明确指定字符集

# mysqlhotcopy dbname1 dbname2 dbname3 /backup 工作原理是先给数据库加一个读操作锁,再FLUSH TABLES把内存中的修改写入硬盘,再把数据库文件复制;仅适用于MyISAM不适用于InnoDB

# mysqlhotcopy –noindices dbname1 dbname2 dbname3 /backup只复制MyISAM的数据文件,索引文件只复制前2KB内容,恢复时myisamchk -r通过这2KB内容可以把索引文件重新修复出来;备份加快了,恢复变慢了

2.7.   导入导出

mysql> load data infile ‘/tmp/x.txt’ into table tablename fields terminated by ‘\t’ enclosed by ‘\”‘ lines terminated by ‘\r\n’;分别为字段间隔符、数据前缀后缀符、换行符)mysql> load data local infile ‘/tmp/x.txt’ into table tablename;

远程连接,需要导入的文件在本地

mysql> load data local infile ‘/tmp/x.txt’ replace into table tablename;

用新导入的记录替换掉数据表里的现有数据记录

# mysqlimport dbanme tablename /tmp/x.txt效果跟上面一样

# mysqlimport –local dbanme tablename /tmp/x.txt

# mysqlimport –local “–fields-enclosed-by=\”” “–lines-terminated-by=’\r\n'” dbanme tablename /tmp/x.txt

 

mysql> select * into outfile ‘/tmp/y.txt’ from tablename;

mysql> select * into outfile ‘/tmp/y.txt’ fields terminated by ‘;’ enclosed by ‘\”‘ from tablename;效果跟上面一样 

 

# mysqldump –tab=/tmp “–fields-enclosed-by=\”” dbanme tablename

生成两个文件tablename.txt和tablename.sql,前者是数据跟select …into outfile一样,后者是CREATE TABLE命令

# mysqldump –xml mylibrary > /tmp/mylibrary.xml导出XML格式

# mysql -e ‘select * from mysql.user’ >/tmp/log.txt

# mysql –html -e ‘select * from mysql.user’ >/tmp/log.html

# mysql –xml -e ‘select * from mysql.user’ >/tmp/log.xml

2.8.   日志

MySQL日志包括变更日志(update log)、出错日志(error log)、常规查询日志(general query log)、慢查询日志(slow query log),默认仅启用出错日志:

[mysqld]log-bin=xxx(不指定的话自动用主机名代替,记录每次修改时间、客户线程ID、修改内容等,不记录SELECT命令和没有改变任何数据的UPDATE命令;另外还可以指定目录防止磁盘满;官方文档称对MySQL性能影响仅%1;默认文件名hostname-bin.log)#max_binlog_size(日志文件超过此阀值就会新建一个,默认1G

log=/tmp/yyy.txt(常规查询日志记录所有登录和操作日志,log-bin中的客户线程ID只有配合本日志才能查询对应哪位用户;默认文件名hostname.log

log-error=/backup/logs/mysqlerrorlog(出错日志无法禁用,但可以移位置

log-slow-queries(默认文件名hostname-slow.log;用于数据库优化

long_query_time=15(执行超过15秒,默认10秒

#log-queries-not-using-indexes

 

# mysqlbinlog hostname-bin.002 | less查看二进制变更日志文件

mysql> flush logs;运行此命令、MYSQL重启、超过阀值都会导致新生成BIN日志

mysql> set sql_log_bin=0(实时禁用log-bin

mysql> set sql_log_bin=1(实时启用log-bin

# mysqlbinlog name-bin.001 | mysql -u root -p

# mysqlbinlog name-bin.002 | mysql -u root –p

按照时间顺序,利用某次完整备份之后的bin日志恢复数据 

2.9.   管理MyISAM数据表

MyISAM数据表对应着三个硬盘文件*.frm(定义)、*.MYD(存放数据)、*.MYI(存放索引)。有两种管理方法, myisamchk命令和SQL命令,前者可直接对MYI(命令参数中只能使用MYI但实际也对MYD进行操作)文件进行操作,也可以自动连接到MySQL执行SQL命令;后者只能在MySQL服务运行时有效。

# mysqlcheck -Aa -uroot 检查所有数据库的数据表)# mysqladmin flush-tables下面的myisamchk是直接对MYI文件进行操作,所以在那之前需要先FLUSH TABLES一下)# myisamchk /var/lib/mysql/efront/*.MYI检查所有数据库的数据表

MySQL> LOCK TABLE;下面的myisamchk将修改MYI文件,所以在那之前需要先LOCK TABLE)

# myisamchk –recover –check-only-changed –sort-index –analyze /var/lib/mysql/efront/*.MYI按照存取速度最快的要求对索引文件进行优化

# myisamchk –recover /var/lib/mysql/efront/*.MYI修复受损的MyISAM数据表

# myisamchk –recover –quick /var/lib/mysql/efront/*.MYI只修复索引文件

# myisamchk –safe-recover /var/lib/mysql/efront/*.MYI比–recover更长时间的修复

MySQL> UNLOCK TABLE;解锁

# myisamchk -O key_buffer_size=64M -O sort_buffer_size=64M -O read_buffer_size=2M -O write_buffer_size=2M ……设置内存变量,对大数据表很有效

# myisampack /var/lib/mysql/efront/*.MYI压缩数据表后读取速度更快,体积庞大但仅用于查询不需要再修改的数据表

# mysqlcheck -u root -p –auto-repair –check –optimize –all-databases检查分析优化一条龙

2.10.       管理InnoDB数据表

2.11.       索引

索引可以加快数据检索操作,但会使数据修改操作变慢,因为每修改一条数据记录,索引就必须刷新一次。如果有大量新数据需要插入,建议在每条SQL语句后面设置DELAY_KEY_WRITE,对索引的刷新等到全部记录插入完毕后再进行,这将大大提升修改操作的效率。

唯一(Unique)索引和主(Primary)索引基本相同,唯一区别是定义时使用的关键字一个是PRIMARY一个是UNIQUE。

2.12.       存储过程

mysql> show procedure status;过程清单)mysql> show function status;函数清单)mysql> show create procedure spname;查看过程的代码

mysql> show create function spname;查看函数的代码

# mysqladmin -uroot -p mysql proc >backupsp.sql备份所有SP

2.13.       优化

[mysqld]key_buffer_size = 1024M(索引缓冲区,默认8M,建议为物理内存的四分之一)table_cache = 512 (同时打开数据表个数,show status like ‘open_tables’可查看当前打开的个数,建议在高峰时查看此值后进行调整,打开的数据表需占用内存的

sort_buffer_size = 2M(排序缓冲区,没有索引可用时ORDER BY就用到它

read_buffer_size = 2M(每个线程需要的排序缓冲区,不必太大因每个线程都独自拥有

read_rnd_buffer_size =

bulk_insert_buffer_size =

join_buffer_size =

tmp_table_size =

max­_connections = 2048

同时打开的数据库连接的最大个数,show status like ‘max_used_connections’查看当前值,对永久性连接比较有效减少刚关闭一条连接就不得不再次打开一条同样的连接的现象,但也不必太大,因为每个连接都消耗内存和文件描述符

max_allowed_packet = 16M(影响客户端和服务器最大BLOB块的长度

query_cache_size = 128M

query_cache_type = 1(默认为ON

query_cache_limit = 500K(避免少数大数据量的查询结果把其他查询“排挤”出缓冲区

skip-name-resolve(不解析IP的主机名,mysql.users里必须设置IP而非主机名

skip-innodb(不启用innodb

      

第3章     FAQ

3.1.   连接错误

MySQL客户端(常见如PHP)报以下连接错误:

MySQL server has gone awayLost connection to server during query

       可以从以下方面进行调查:

  1. MySQL对永久性连接也是有timeout的,默认是8小时。比如php的mysql_pconnect()或者其他连接池创建的长连接超时了。可通过增加MySQL的wait_timeout变量的值来解决;
  2. 客户端多线程共享某个连接时,在断开的连接上做查询也会报以上错误;
  3. 客户端达到timeout值了,PHP可以通过修改默认timeout解决mysql_options(…, MYSQL_OPT_READ_TIMEOUT,…)和mysql_options(…, MYSQL_OPT_WRITE_TIMEOUT,…);
  4. 客户端传送给服务端的BLOB超过1M了,修改MySQL的max_allowed_packet为更大值;

3.2.   读写优化

MySQL服务器有读有写,在低负载LOAD情况下CPU使用率达100%, 估计下来可能是:在一个读站主体的数据库中,忽然发生大量的读写并发,由于INSERT和UPDATE优先级比SELECT高,而MyISAM数据表在INSERT和UPDATE时必须LOCK表,导致SELECT必须等待INSERT和UPDATE执行完毕才能操作,如果这个时候SELECT等待累积过大,会造成MySQL瓶颈,通过以下三个方法修复:

max_write_lock_count = 1(一个写后就执行读,相当于增加了读的优先级)low-priority-updates=1(降低写的优先级,让读先执行)concurrent_insert=2(并发查询插入的一个设置,会产生大量碎片,定期使用OPTIMIZE TABLE语法优化
分类: safe 标签:

my.cnf配置文件详解|mysql优化

2011年3月25日 没有评论

 

分类: DB 标签: ,

CentOs5.5+Apache2.2.17+MySQL5.5.8+PHP5.3.5环境搭建

2011年3月14日 1 条评论

Linux + Apache + MySQL + PHP环境搭建

友情链接:centos5.5+nginx0.8.54+mysql5.5.8+php5.3.5 最新详细安装手册

             搭建环境:CentOs 5.5 +Apache 2.2.17+MySQL5.58+PHP5.35,使用编译安装,关闭防火墙、selinux
所须软件下载:

Apache(httpd)
www.apache.org
PHP
www.php.net
CMAKE
http://www.cmake.org/files/v2.8/cmake-2.8.3.tar.gz

假定安装包都在/tmp目录。

1.使用 yum 程序安装所需开发包

不多讲出错的问题,安装了这些库文件,基本不会有出错信息了。

2.安装MySQL 5.5.8
Mysql 5.5.8需要cmake编译工具

①安装cmake

②编译安装MySQL
先检查是否安装了MySQL

③配置MySQL
添加mysql用户,如果之前安装过mysql已经存在mysql用户,掠过此步

复制mysql配置文件:

生成mysql用户数据库和表文件:

把mysql加入到系统服务中

3.安装配置Apache
1.编译安装Apache

注意:./configure –help可以查看参数,根据实际需要配置。这里的configure后面跟的目录参数,最后不要跟/,否则有的地方目录会出现//的子目录问题。
# echo $?
显示为:0,表示编译过程中没有错误,显示其它都有错 这一步后面的编译过程都会用到,如果在这出错了,就不要MAKE了。得查找原因。

#make
#make install

2.设置Apache自启动

然后

4.编译安装PHP
PHP5.3.5的bug,需要安装 autoconf为2.13版本:

php 5.3.0 在默认状态下,已经开始支持 fastcgi了,不用再费劲再写配置参数了,如果写了,会提示错误信息,这个错误也可以忽略。
# make
提示出错
/usr/local/mysql/include/mysql/my_sys.h:964:27: error: mysql/psi/psi.h: No such file or directory
/usr/local/mysql/include/mysql/m_string.h:219:26: error: mysql/plugin.h: No such file or directory
/usr/local/mysql/include/plugin.h:51:28: 错误:mysql/services.h:没有那个文件或目录
/usr/local/mysql/include/services.h:21:39: 错误:mysql/service_my_snprintf.h:没有那个文件或目录
/usr/local/mysql/include/services.h:22:37: 错误:mysql/service_thd_alloc.h:没有那个文件或目录
/usr/local/mysql/include/services.h:23:36: 错误:mysql/service_thd_wait.h:没有那个文件或目录
/usr/local/mysql/include/services.h:24:44: 错误:mysql/service_thread_scheduler.h:没有那个文件或目录
修复MySQL的BUG
# mkdir /usr/local/mysql/include/mysql
# ln -s /usr/local/mysql/include/* /usr/local/mysql/include/mysql
# vi /usr/local/mysql/include/my_sys.h
找到
#include
修改为
#include  

# vi /usr/local/mysql/include/m_string.h
找到
#include < mysql/plugin.h>
修改为
#include # vi /usr/local/mysql/include/plugin.h
找到
#include
修改为
#include
# make clean
# make
# make install

# cp /tmp/php-5.3.5/php.ini-production /usr/local/php/etc/php.ini
# vi /usr/local/apache2/conf/httpd.conf
找到,在下面添加两行
AddHandler php5-script .php
AddType text/html .php

# vi /usr/local/apache2/htdocs/test.php
输入

在ie输入主机ip/test.php能看到phpinfo信息。

分类: webapp 标签: , , ,

make: *** [ext/mysqli/mysqli.lo] Error 1

2011年3月14日 没有评论

这个是在编译php的时候执行make的时候出现的,前面./configure是没有问题的

解决方法:

分类: DB 标签: ,

linux上mysql5.5.8安装详解

2011年3月14日 没有评论

MySQL5.5源码安装和以前的5.x有区别,5.x使用autotool编译,新版的5.5使用CMake编译

1.1. Cmake的安装

http://www.cmake.org/cmake/resources/software.html

下载源码包,安装cmake方法和其他的安装相同,解压,配置,编译,安装
cmake安装

建立用户和目录

注意:

下面的配置过程中,ls一下你会看到有下面的文件: my-huge.cnf 【对应1-2G内存】、my-large.cnf 【对应512M内存】、my-medium.cnf 【对应128M内存】、my-small.cnf 【对应64M或更低】,你可以查看这些文件,参照你的电脑配置,选择一个文件

mysql开始安装

服务相关

分类: DB 标签: ,

phpmyadmin 提示 PHP 5.2+ is required 的解决办法

2011年3月7日 没有评论

phpmyadmin 提示 PHP 5.2+ is required 的解决办法:

今天想安装个phpmyadmin于是去官网看了一下,看到有phpMyAdmin 3.3.3 于是就下了,安装,访问。出错了提示 PHP 5.2+ is required

应该是说要求php 5.2以上吧

系统装是提CentOS 5.5 因为只是玩玩,所以图省事,apache,php,mysql都直接选装了。

建个phpinfo.php来看一下自己的php版本情况

原来CentOS 5.5自带的php是5.1.6

两个方法:

1 升级php
2使用低版本的phpmyadmin

分类: DB 标签: ,

mysql备份脚本

2011年3月7日 没有评论

分类: DB 标签: ,

MySQL衍生数据库 MepSQL

2011年2月28日 没有评论

MepSQL 是一个 MySQL 的衍生版本。MepSQL 初始基于 facebook 补丁的 MySQL 代码,增加了用户友好的安装包和文档。MepSQL 主要侧重于高性能和来自社区开发的新特性、工具和文档。

安装方法:

Ubuntu 10.10

分类: DB 标签: ,

MySQL5.5.8安装手册 (完整版)

2011年2月15日 没有评论

MySQL5.5安装

1.安装cmake

2.安装mysql

编译选项说明:

3.新建用户

# useradd mysql -d /dev/null -s /sbin/nologin

4.配置

若提示/etc/init.d/mysqld: line 256: my_print_defaults: command not found

MySQL server PID file could not be found!

编辑/etc/init.d/mysqld 46,47行basedir和datadir变量。

5.其他:

MySQL5.5将InnoDB作为默认的存储引擎。Oracle收购InnoBase后,将自家的InnoDB存储引擎扶上正统地位。

分类: DB 标签:

CentOS 5.5 安装MySQL 5.5.8初体验

2011年2月14日 没有评论

http://dev.mysql.com/doc/refman/5.5/en/installing-source-distribution.html
参考文章MySQL官方教程
安装后使用supersmack测试记录 对MYSQL进行压力测试 http://blog.csdn.net/pengyouchuan/archive/2010/12/18/6084007.aspx

MySQL5.5源码安装和以前的5.1有区别,5.1使用autotool编译,新版的5.5使用CMake编译

需要先安装cmake

http://www.cmake.org/cmake/resources/software.html

下载源码包,安装cmake方法和其他的安装相同,解压,配置,编译,安装

按照MySQL官方文档进行,顺利完成,根据自己的datadir安装数据库,我的安装/home/mysql/data下

# Preconfiguration setup
shell> groupadd mysql
shell> useradd -r -g mysql mysql
# Beginning of source-build specific instructions
shell> tar zxvf mysql-VERSION

.tar.gz
shell> cd mysql-VERSION
shell> cmake .
shell> make

shell> make install

# End of source-build specific instructions
# Postinstallation setup
shell> cd /usr/local/mysql
shell> chown -R mysql .

shell> chgrp -R mysql .
shell> scripts/mysql_install_db –user=mysql
shell> chown -R root .
shell> chown -R mysql data
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe –user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysqld
shell> chkconfig –add mysqld

shell> chkconfig –level 2345 mysqld on
shell> service mysqld start

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/pengyouchuan/archive/2010/12/18/6084128.aspx

分类: DB 标签: , , ,