MySQL数据库知识详解

MySQL数据库知识详解

1、安装

1.1Windows下安装:

下载mysql-essential-5.1.40-win32.zip，解压后运行mysql-essential-5.1.40-win32.msi

选择安装类型，有3种安装类型:Typical(典型安装)、Complete(完全安装)和Custom(定制安装)。

Typical(典型安装)安装只安装MySQL服务器、mysql命令行客户端和命令行实用程序。命令行客户端和实用程序包括mysqldump、myisamchk和其它几个工具来帮助你管理MySQL服务器。

Complete(完全安装)安装将安装软件包内包含的所有组件。完全安装软件包包括的组件包括嵌入式服务器库、基准套件、支持脚本和文档。

Custom(定制安装)安装允许你完全控制你想要安装的软件包和安装路径。

这里如果选择Typical或Complete安装类型，点击Next按钮，进入确认窗口确认选择并开始安装。如果选择Custom安装类型并点击Next按钮，则进入定制安装对话框。在定制安装对话框中，你可以选择需要安装的组建，并可以通过点击安装路径右侧的Change...按钮来更改默认安装路径，之后点击Next按钮，进入确认窗口确认选择并开始安装。如果觉得使用命令行工具不方便的话，可以安装图形化软件如phpMyAdmin等。

1.2Linux下安装:

下载对应Linux版本的安装文件，这里下载MySQL-server-5.1.7-0.i386.rpm和MySQL-client-5.1.7-0.i386.rpm，在有两个rpm文件的目录下运行如下命令: #rpm –ivh MySQL-server-5.1.7-0.i386.rpm MySQL-client-5.1.7-0.i386.rpm

测试是否成功可运行netstat看Mysql端口是否打开，如打开表示服务已经启动，安装成功。Mysql默认的端口是3306。命令如下:# netstat –nat，MySQL默认用户是root，由于初始没有密码，第一次进时只需键入mysql即可。# mysql

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 to server version: 4.0.16-standard

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

出现了“mysql>”提示符，恭喜你，安装成功，增加了密码后的登录格式如下: mysql -u root -p

Enter password: (输入密码)

其中-u后跟的是用户名，-p要求输入密码，回车后在输入密码处输入密码。(注意:安装之前可以用rpm -qa|grep mysql 查看是否安装有mysql)

2、配置

2.1使用配置向导

安装完成后，可以启动MySQL Configuration Wizard(配置向导)，用来创建配置文件，安装MySQL服务并进行安全配置。

启动配置向导后，点击Next进入选择维护选项对话框，要想重新配置已有的服务器，选择Re-configure Instance选项并点击Next按钮。已有的my.ini文件重新命名为mytimestamp.ini.bak，其中timestamp是my.ini文件创建是的日期和时间。要想卸载已有的服务器实例，选择Remove Instance选项并点击Next按钮。

如果选择了Remove Instance选项，则进入确认窗口。点击Execute按钮:MySQL Configuration Wizard(配置向导)停止并卸载MySQL服务，然后删除my.ini文件。服务器安装和自己的data文件夹不删除。

如果选择了Re-configure Instance选项，则进入配置类型对话框，可以选择你想要配置的安装类型。

在配置类型对话框中，可以选择两种配置类型:Detailed Configuration(详细配置)和Standard Configuration(标准配置)。Details Configuration适合想要更加细粒度控制服务器配置的高级用户，而Standard Configuration则适合想要快速启动MySQL而不必考虑服务器配置的新用户，选择后，MySQL Configuration Wizard(配置向导)自动设置所有配置选项，但不包括服务选项和安全选项。

选择配置类型后，点击Next进入服务器类型选择对话框，可以选择3种服务器类型，其中Developer Machine(开发机器):该选项代表典型个人用桌面工作站。假定机器上运行着多个桌面应用程序。将MySQL服务器配置成使用最少的系统资源。 Server Machine(服务器):该选项代表服务器，MySQL服务器可以同其它应用程序一起运行，例如FTP、email和web服务器。MySQL服务器配置成使用适当比例的系统资源。Dedicated MySQL Server Machine(专用MySQL服务器):该选项代表只运行MySQL服务的服务器。假定运行没有运行其它应用程序。MySQL服务器配置成使用所有可用系统资源。

选择服务器类型后，点击Next按钮进入数据库使用情况对话框，可以选择3种使用情况，其中Multifunctional Database(多功能数据库):选择该选项，则同时使用InnoDB和MyISAM储存引擎，并在两个引擎之间平均分配资源。建议经常使用两个储存引擎的用户选择该选项。

Transactional Database Only(只是事务处理数据库):该选项同时使用InnoDB和MyISAM 储存引擎，但是将大多数服务器资源指派给InnoDB储存引擎。建议主要使用InnoDB只偶尔使用MyISAM的用户选择该选项。

Non-Transactional Database Only(只是非事务处理数据库):该选项完全禁用InnoDB储存引擎，将所有服务器资源指派给MyISAM储存引擎。建议不使用InnoDB的用户选择该选项。

InnoDB表空间对话框，如果用户想要将InnoDB表空间文件放到不同的位置，而不放到MySQL服务器数据目录。如果你的系统有较大的空间或较高性能的储存设备(例如RAID储存系统)，则最好将表空间文件单独放到一个位置。在这里可以重新指定InnoDB表空间的位置。

之后进入并发连接对话框，限制所创建的与MySQL服务器之间的并行连接数量很重要，以便防止服务器耗尽资源。

Decision Support(决策支持)(DSS)/OLAP:如果服务器不需要大量的并行连接可以选择该选项。假定最大连接数目设置为100,平均并行连接数为20。

Online Transaction Processing(联机事务处理)(OLTP):如果你的服务器需要大量的并行连接则选择该选项。最大连接数设置为500。

Manual Setting(人工设置):选择该选项可以手动设置服务器并行连接的最大数目。从前面的下拉框中选择并行连接的数目，如果你期望的数目不在列表中，则在下拉框中输入最大连接数。

选择并发连接使用情况后，进入联网选择对话框，默认情况启用TCP/IP网络。要想禁用TCP/IP网络，取消选择Enable TCP/IP Networking选项旁边的检查框。默认使用3306端口。要想更访问MySQL使用的端口，从下拉框选择一个新端口号或直接向下拉框输入新的端口号。如果你选择的端口号已经被占用，将提示确认选择的端口号。

选择好联网方式后，进入字符选择对话框，有3个选项，其中Standard Character Set(标准字符集):如果想要使用Latin1做为默认服务器字符集，则选择该选项。Latin1用于英语和许多西欧语言。

Best Support For Multilingualism(支持多种语言):如果想要使用UTF8做为默认服务器字符集，则选择该选项。UTF8可以将不同语言的字符储存为单一的字符集。 Manual Selected Default Character Set/Collation(人工选择的默认字符集/校对规则):如果想要手动选择服务器的默认字符集，请选择该项。从下拉列表中选择期望的字符集。

设置好字符集后，进入服务选择对话框，默认情况，MySQL Configuration Wizard(配置向导)将MySQL服务器安装为服务，服务名为MySQL。安装成服务，系统启动时可以自动启动MySQL服务器，甚至出现服务故障时可以随Windows自动启动。如果你不想安装服务，取消Install As Windows Service选项旁边的选择框。可以从下拉框选择新的服务名或在下拉框输入新的服务名来更改服务名。

要想将MySQL服务器安装为服务，但是不自动启动，不选中Launch the MySQL Server

Automatically选项旁边的检查框。

之后进入安全选择对话框，默认情况MySQL Configuration Wizard(配置向导)要求你设置一个root密码。如果你不想设置root密码，不选中Modify Security Settings(修改安全设定值)选项旁边的选择框。

要想设置root密码，在New root password(输入新密码)和Confirm(确认)两个框内输入期望的密码。如果重新配置已有的服务器，你还需要Current root password(当前root密码)框内输入已有的root密码。

要想防止通过网络以root登录，选中Root may only connect from localhost(只允许从本机登陆连接root)选项旁边的框。这样可以提高root账户的安全。

要想创建一个匿名用户账户，选中Create An Anonymous Account(创建匿名账户)选项旁边的框。创建匿名账户会降低服务器的安全，并造成登录和许可困难。因此不建议。

点击Next，进入配置执行框，点击execute按钮，当对话框中的4项服务前都打，便证明配置执行成功，否则配置不成功。

2.2手动配置

Windows下在安装目录下找到my.ini文件，进行编辑，Linux下在安装目录下找到my.cnf文件进行编辑，各参数意义如下:

[client]

password = [your_password]

port = @MYSQL_TCP_PORT@

socket = @MYSQL_UNIX_ADDR@

MySQL 服务端

[mysqld]

port = @MYSQL_TCP_PORT@

socket = @MYSQL_UNIX_ADDR@

back_log是操作系统在监听队列中所能保持的连接数,队列保存了在MySQL连接管理器线程处理之前的连接。如果你有非常高的连接率并且出现"connection refused" 报错,你就应该增加此处的值。检查你的操作系统文档来获取这个变量的最大值。如果将back_log设定到比你操作系统限制更高的值,将会没有效果。

skip-networking不在TCP/IP端口上进行监听, 如果所有的进程都是在同一台服务器连接到本地的mysqld, 这样设置将是增强安全的方法, 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的。注意在windows下如果没有打开命名管道选项而只是用此项，(通过 "enable-named-pipe" 选项) 将会导致mysql服务没有任何作用! max_connections MySQL 服务所允许的同时会话数的上限，其中一个连接将被SUPER权限保留作为管理员登录(即便已经达到了连接数的上限)。

max_connect_errors每个客户端连接最大的错误允许数量,如果达到了此限制，这个客户端将会被MySQL服务阻止直到执行了"FLUSH HOSTS" 或者服务重启非法的密码以及其他在链接时的错误会增加此值，查看 "Aborted_connects" 状态来获取全局计数器。 table_cache所有线程所打开表的数量，增加此值就增加了mysqld所需要的文件描述符的数量，这样你需要确认在[mysqld_safe]中 "open-files-limit" 变量设置打开文件数量允许至少4096

external-locking允许外部文件级别的锁. 打开文件锁会对性能造成负面影响，所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)，或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表。

max_allowed_packet服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)，每个连接独立的大小动态增加 binlog_cache_size在一个事务中binlog为了记录SQL状态所持有的cache大小，如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能。所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中，如果事务比此值大, 会使用磁盘上的临时文件来替代，此缓冲在每个连接的事务第一次更新状态时被创建。

max_heap_table_size独立的内存表所允许的最大容量，此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源。

sort_buffer_size排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序，如果排序后的数据无法放入排序缓冲，一个用来替代的基于磁盘的合并分类会被使用，查看 "Sort_merge_passes" 状态变量，在排序发生时由每个线程分配。 join_buffer_size此缓冲被使用来优化全联合(full JOINs 不带索引的联合)，类似的联合在极大多数情况下有非常糟糕的性能表现，但是将此值设大能够减轻性能影响。通过 "Select_full_join" 状态变量查看全联合的数量，当全联合发生时,在每个线程中分配。 thread_cache_size我们在cache中保留多少线程用于重用，当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size, 则客户端线程被放入cache中。这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,这不会有明显的性能提升)。

thread_concurrency此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量，此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris)，你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值。 query_cache_size查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果，打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表，查看 "Qcache_lowmem_prunes" 状态变量来检查是否当前值对于你的负载来说是否足够高，注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同，查询缓冲也许引起性能下降而不是性能提升。

query_cache_limit只有小于此设定值的结果才会被缓冲，此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖。

ft_min_word_len被全文检索索引的最小的字长，你也许希望减少它,如果你需要搜索更短字的时候，注意在你修改此值之后，你需要重建你的 FULLTEXT 索引。 memlock如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out，此选项对于性能有益。

default_table_type当创建新表时作为默认使用的表类型，如果在创建表示没有特别执行表类型,将会使用此值。

thread_stack线程使用的堆大小，此容量的内存在每次连接时被预留，MySQL 本身常不会需要超过64K的内存，如果你使用你自己的需要大量堆的UDF函数，或者你的操作系统对于某些操作需要更多的堆，你也许需要将其设置的更高一点。

transaction_isolation设定默认的事务隔离级别，可用的级别如下: READ-UNCOMMITTED,

READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

tmp_table_size内部(内存中)临时表的最大大小，如果一个表增长到比此值更大,将会自动转换为基于磁盘的表，此限制是针对单个表的，而不是总和。

log-bin打开二进制日志功能，在复制(replication)配置中，作为MASTER主服务器必须打开此项，如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志。

log_slave_updates如果你在使用链式从服务器结构的复制模式 (A->B->C)，你需要在服务器B上打开此项，此选项打开在从线程上重做过的更新的日志，并将其写入从服务器的

二进制日志。

log打开全查询日志，所有的由服务器接收到的查询 (甚至对于一个错误语法的查询) 都会被记录下来.，这对于调试非常有用，在生产环境中常常关闭此项。

log_warnings将警告打印输出到错误log文件，如果你对于MySQL有任何问题，你应该打开警告log并且仔细审查错误日志,查出可能的原因。

log_slow_queries记录慢速查询，慢速查询是指消耗了比 "long_query_time" 定义的更多时间的查询，如果 log_long_format 被打开，那些没有使用索引的查询也会被记录，如果你经常增加新查询到已有的系统内的话，一般来说这是一个好主意。

long_query_time所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询，不要在这里使用"1"，否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别)。

log_long_format在慢速日志中记录更多的信息，一般此项最好打开，打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里。

tmpdir此目录被MySQL用来保存临时文件，例如，它被用来处理基于磁盘的大型排序，和内部排序一样，以及简单的临时表，如果你不创建非常大的临时文件，将其放置到 swapfs/tmpfs 文件系统上也许比较好，另一种选择是你也可以将其放置在独立的磁盘上，你可以使用";"来放置多个路径，他们会按照roud-robin方法被轮询使用。

复制有关的设置

server-id唯一的服务辨识号，数值位于 1 到 2^32-1之间，此值在master和slave上都需要设置，如果 "master-host" 没有被设置，则默认为1，但是如果忽略此选项，MySQL不会作为master生效。

复制的Slave (去掉master段的注释来使其生效)，为了配置此主机作为复制的slave服务器,你可以选择两种方法:

1) 使用 CHANGE MASTER TO 命令 (在我们的手册中有完整描述) -语法如下:

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT= ,MASTER_USER=,

MASTER_PASSWORD= ;你需要替换掉相应字段以及使用master的端口号替换 (默认3306)。

例子: CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

MASTER_USER='joe', MASTER_PASSWORD='secret'; 2) 设置以下的变量，不论如何，在你选择这种方法的情况下，然后第一次启动复制(甚至不成功的情况下，例如如果你输入错密码在master-password字段并且slave无法连接)， slave会创建一个 master.info 文件，并且之后任何对于包含在此文件内的参数的变化都会被忽略并且由 master.info 文件内的内容覆盖，除非你关闭slave服务，删除 master.info 并且重启slave 服务。由于这个原因,你也许不想碰一下的配置(注释掉的) 并且使用 CHANGE MASTER TO (查看上面) 来代替。

server-id所需要的唯一id号位于 2 和 2^32 - 1之间，(并且和master不同)，如果master-host被设置了，则默认值是2，但是如果省略,则不会生效。

master-host复制结构中的master - 必须。

master-user当连接到master上时slave所用来认证的用户名 - 必须。

master-password当连接到master上时slave所用来认证的密码 - 必须。

master-port master监听的端口，可选 - 默认是3306。

read_only使得slave只读，只有用户拥有SUPER权限和在上面的slave线程能够修改数据，你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据。

MyISAM 相关选项

key_buffer_size关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块，不要将其设置大于你可用内存的30%，因为一部分内存同样被OS用来缓冲行数据，甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用。

read_buffer_size用来做MyISAM表全表扫描的缓冲大小，当全表扫描需要时,在对应线程中分配。

read_rnd_buffer_size当在排序之后，从一个已经排序好的序列中读取行时，行数据将从这个缓冲中读取来防止磁盘寻道，如果你增高此值,可以提高很多ORDER BY的性能，当需要时由每个线程分配。

bulk_insert_buffer_size MyISAM 使用特殊的类似树的cache来使得突发插入，(这些插入是,INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., 以及 LOAD DATA INFILE) 更快. 此变量限制每个进程中缓冲树的字节数，设置为 0 会关闭此优化，为了最优化不要将此值设置大于 "key_buffer_size"，当突发插入被检测到时此缓冲将被分配。

myisam_sort_buffer_size此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配，这在每个线程中被分配.所以在设置大值时需要小心。

myisam_max_sort_file_size MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)，如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)。

myisam_max_extra_sort_file_size如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法，这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引。

myisam_repair_threads如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们，这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.。

myisam_recover自动检查和修复没有适当关闭的 MyISAM 表。

skip-federated默认关闭 Federated。

BDB 相关选项

skip-bdb如果你运行的MySQL服务有BDB支持但是你不准备使用的时候使用此选项. 这会节省内存并且可能加速一些事。

INNODB 相关选项

skip-innodb如果你的MySQL服务包含InnoDB支持但是并不打算使用的话, 使用此选项会节省内存以及磁盘空间,并且加速某些部分。

innodb_additional_mem_pool_size附加的内存池被InnoDB用来保存 metadata 信息，如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存，由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值。SHOW INNODB STATUS 命令会显示当先使用的数量。

innodb_buffer_pool_size InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM，这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少，在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%，不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸，注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制，所以不要设置的太高。

innodb_data_file_path InnoDB 将数据保存在一个或者多个数据文件中成为表空间，如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了，其他情况下.每个设备一个文件一般都是个好的选择，你也可以配置InnoDB来使用裸盘分区 - 请参考手册来获取更多相关内容。

innodb_data_home_dir设置此选项如果你希望InnoDB表空间文件被保存在其他分区，默认保存在MySQL的datadir中。

innodb_file_io_threads用来同步IO操作的IO线程的数量，此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好。

innodb_force_recovery如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表，从1开始并且增加此值直到你能够成功的导出表。

innodb_thread_concurrency在InnoDb核心内的允许线程数量，最优值依赖于应用程序,硬件以及操作系统的调度方式，过高的值可能导致线程的互斥颠簸。

innodb_flush_log_at_trx_commit如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上, 这提供了完整的ACID行为，如果你愿意对事务安全折衷, 并且你正在运行一个小的事务, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O，0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘，2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上。

innodb_fast_shutdown加速InnoDB的关闭。这会阻止InnoDB在关闭时做全清除以及插入缓冲合并，这可能极大增加关机时间, 但是取而代之的是InnoDB可能在下次启动时做这些操作。

innodb_log_buffer_size用来缓冲日志数据的缓冲区的大小，当此值快满时, InnoDB将必须刷新数据到磁盘上，由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)。

innodb_log_file_size在日志组中每个日志文件的大小，你应该设置日志文件总合大小到你缓冲池大小的25%~100%，来避免在日志文件覆写上不必要的缓冲池刷新行为，不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间。

innodb_log_files_in_group在日志组中的文件总数，通常来说2~3是比较好的。

innodb_log_group_home_dir InnoDB的日志文件所在位置，默认是MySQL的datadir，你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能。

innodb_max_dirty_pages_pct在InnoDB缓冲池中最大允许的脏页面的比例，如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面，这是一个软限制，不被保证绝对执行。

innodb_flush_method InnoDB用来刷新日志的方法，表空间总是使用双重写入刷新方法，默认值是 "fdatasync", 另一个是 "O_DSYNC"。

innodb_lock_wait_timeout在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久，InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务，如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎，那么一个死锁可能发生而InnoDB无法注意到，这种情况下这个timeout值对于解决这种问题就非常有帮助。

[mysqldump]

quick不要在将内存中的整个结果写入磁盘之前缓存，在导出非常巨大的表时需要此项 max_allowed_packet = 16M

[mysql]

no-auto-rehash

safe-updates仅仅允许使用键值的 UPDATEs 和 DELETEs

[isamchk]

key_buffer = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[myisamchk]

key_buffer = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit增加每个进程的可打开文件数量，警告: 确认你已经将全系统限制设定的足够高! 打开大量表需要将此值设高。

3.日志模式

4.表空间规划

分区表

在MySQL5之后，逐渐有了分区表的功能。

MySQL 目前支持水平分区，也就是针对行的分区，主要有四种分区类型: * RANGE 分区

* LIST 分区

* HASH 分区

* KEY 分区 --类似 HASH 分区,只是要根据用户定义的表达式来进行分区。 MySQL 的 Key 分区类型，和微软的 Yukon (SQL Server 2005) 的分区方式很类似的，相对灵活一些，而 Oracle 的复合分区则为范围分区与 HASH 的结合体，这样略失灵活，但是便于管理。

目前的 MySQL 分区实现上有限制:分区和索引必须是分区的。也就是说，MySQL 目前还没有类似 Oracle 全局索引(Global Index)的概念，而只支持 Local Index 。无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确

的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的哈希函数自动进行处理。

分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。

每个分区只能使用一种数据引擎。

分区表只有在多磁盘(尤其是raid)多cpu的服务器上才有性能提升，它是利用并行处理来提高性能，普通机器只会更慢。

5.权限管理

MySQL 存取控制包含2个阶段:

阶段1:服务器检查是否允许你连接。

阶段2:假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。 mysql数据库中的3个权限表:user 、db、 host

权限表的存取过程是:

1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证;

2)通过权限验证，进行权限分配时，按照userdbtables_privcolumns_priv的顺序进行分配。即先检查全局权限表 user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv;如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限;如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

MySQL各种权限(共27个)

(以下操作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。) 1. usage

连接(登陆)权限，建立一个用户，就会自动授予其usage权限(默认授予)。 mysql> grant usage on *.* to p1′@‘localhost‘ identified by 123′;

该权限只能用于数据库登陆，不能执行任何操作;且usage权限不能被回收，也即REVOKE用户并不能删除用户。

2. select

必须有select的权限，才可以使用select table

mysql> grant select on pyt.* to p1′@‘localhost‘;

mysql> select * from shop;

3. create

必须有create的权限，才可以使用create table

mysql> grant create on pyt.* to p1′@‘localhost‘;

4. create routine

必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}

mysql> grant create routine on pyt.* to p1′@‘localhost‘;

当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者: mysql> show grants for p1′@‘localhost‘;

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON *.* TO p1′@‘localhost‘ IDENTIFIED BY PASSWORD

*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO p1′@‘localhost‘|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO

p1′@‘localhost‘ |

+————————————————————————————-+

5. create temporary tables(注意这里是tables，不是table)

必须有create temporary tables的权限，才可以使用create temporary tables. mysql> grant create temporary tables on pyt.* to p1′@‘localhost‘;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt mysql> create temporary table tt1(id int); 6. create view

必须有create view的权限，才可以使用create view

mysql> grant create view on pyt.* to p1′@‘localhost‘;

mysql> create view v_shop as select price from shop; 7. create user

要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。

mysql> grant create user on *.* to p1′@‘localhost‘;

或:mysql> grant insert on *.* to p1@localhost; 8. insert

必须有insert的权限，才可以使用insert into ….. values….

9. alter

必须有alter的权限，才可以使用alter table

alter table shop modify dealer char(15); 10. alter routine

必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function} mysql>grant alter routine on pyt.* to p1′@‘ localhost ;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke alter routine on pyt.* from p1′@‘localhost‘;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user p1′@‘localhost‘ for routine

pyt.pro_shop‘

11. update

必须有update的权限，才可以使用update table

mysql> update shop set price=3.5 where article=0001 and dealer=‘A‘;

12. delete

必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)

13. drop

必须有drop的权限，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name; 14. show database

通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。

对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库:

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必须拥有show view权限，才能执行show create view。

mysql> grant show view on pyt.* to p1@localhost;

mysql> show create view v_shop; 16. index

必须拥有index权限，才能执行[create |drop] index mysql> grant index on pyt.* to p1@localhost; mysql> create index ix_shop on shop(article); mysql> drop index ix_shop on shop; 17. excute

执行存在的Functions,Procedures

mysql> call pro_shop1(0001,@a);

+———+

| article |

+———+

| 0001 |

+———+

mysql> select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必须拥有lock tables权限，才可以使用lock tables

mysql> grant lock tables on pyt.* to p1@localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。

20. reload

必须拥有reload权限，才可以执行flush [tables | logs | privileges] mysql> grant reload on pyt.* to p1@localhost; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on *.* to p1′@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

拥有此权限可以查询master server、slave server状态。

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege

for this operation

mysql> grant Replication client on *.* to p1@localhost; 或:mysql> grant super on *.* to p1@localhost; mysql> show master status;

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

| mysql-bin.000006 | 2111 | | |

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

mysql> show slave status;

22. replication slave

拥有此权限可以查看从服务器，从主服务器读取二进制日志。

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this

operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this

operation

mysql> grant replication slave on *.* to p1@localhost; mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;

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

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 |

|mysql-bin.000005|98|Query|1|197|use `mysql`; create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

关闭MySQL:

[mysql@mydev ~]$ mysqladmin shutdown

重新连接:

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket

/tmp/mysql.sock‘ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

拥有grant option，就可以将自己拥有的权限授予其他用户(仅限于自己已经拥有的权限) mysql> grant Grant option on pyt.* to p1@localhost; mysql> grant select on pyt.* to p2@localhost; 25. file

拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。

mysql> grant file on *.* to p1@localhost; mysql> load data infile /home/mysql/pet.txt‘ into table pet;

26. super

这个权限允许用户终止任何查询;修改全局变量的SET语句;使用CHANGE MASTER，PURGE MASTER LOGS。

mysql> grant super on *.* to p1@localhost; mysql> purge master logs before mysql-bin.000006′;

27. process

通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。

mysql> show processlist;

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

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

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

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

另外，

管理权限(如 super， process， file等)不能够指定某个数据库，on后面必须跟*.* mysql> grant super on pyt.* to p1@localhost; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant super on *.* to p1@localhost; Query OK, 0 rows affected (0.01 sec)

权限的回收用revoke命令，只用把以上grant语句中的grant改为revoke，to改为from即可。

6(SQL语法

6.1DDL(data definition language) 1)连接到mysql服务器

mysql -u user -p

2)创建数据库

create database dbname;

3)选择数据库

use dbname;

4)查看数据库test里的数据表

show tables;

show databases;

5)删除数据库

drop database dbname;

6)创建表

create table emp(ename varchar(10),hiredata varchar(10));

7)看表的内容

desc tablename;

8)删除表

drop table tablename;

9)修改表

alter table emp modify ename varchar(20); 10)增加表字段

alter table emp add column age int(3); 11)删除表字段

alter table emp drop column age;

12)字段改名

alter table emp change column age age1 int(3); 13)修改字段顺序

alter table emp modify age int(3) frist|after age; 14)更改表名

alter table emp rename emp1;

6.2DML(data mnipulation language)

1)插入记录

insert into emp(ename,hiredate,sal) values (value1,value2,value3);

insert into emp values(value1,value2,value3); 2)更新记录

update emp set sal=‘5000‘ where ename='dony';

3)删除记录

delete from emp where ename='dony';

4)查询记录

select * from emp;

select ename,hiredate,sal from emp;

select * from where deptno=1 and sal<300; select * from emp order by deptno;//根据deptno排序

select * from emp order by sal limit 3//显示emp表中按照sal排序后显示前三条

select count(1) from emp;

5)表连接

select ename,deptname from emp,dept where emp.deptno=dept.deptno;

select ename,deptname from emp left join dept on emp.deptno=dept.deptno;

6)左连接和右连接

select * from emp left join dept on emp.deptno=dept.deptno;

6.3MySQL的Function

1)创建函数

例如

CREATE FUNCTION cust_status(in_status CHAR(1))

RETURNS VARCHAR(20)

BEGIN

DECLARE long_status VARCHAR(20);

IF in_status = 'O' THEN SET long_status='Overdue';

ELSEIF in_status = 'U' THEN SET long_status='Up to date';

ELSEIF in_status = 'N' THEN SET long_status='New';

END IF;

RETURN(long_status);

END

2)调用函数

通过函数名直接调用，如果函数有参数，需要传递相应类型参数。

3)删除函数

Drop function 函数名;

6.4常用函数

1)数学函数

ABS(x) 返回x的绝对值

BIN(x) 返回x的二进制(OCT返回八进制，HEX返回十六进制) CEILING(x) 返回大于x的最小整数值

EXP(x) 返回值e(自然对数的底)的x次方

FLOOR(x) 返回小于x的最大整数值

GREATEST(x1,x2,...,xn) 返回集合中最大的值

LEAST(x1,x2,...,xn) 返回集合中最小的值

LN(x) 返回x的自然对数

LOG(x,y) 返回x的以y为底的对数

MOD(x,y) 返回x/y的模(余数)

PI() 返回pi的值(圆周率)

RAND() 返回,到,内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

ROUND(x,y) 返回参数x的四舍五入的有y位小数的值 SIGN(x) 返回代表数字x的符号的值

SQRT(x) 返回一个数的平方根

TRUNCATE(x,y) 返回数字x截短为y位小数的结果

2)聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col) 返回指定列的平均值

COUNT(col) 返回指定列中非NULL值的个数 MIN(col) 返回指定列的最小值

MAX(col) 返回指定列的最大值

SUM(col) 返回指定列的所有值之和

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 3)字符串函数

ASCII(char) 返回字符的ASCII码值

BIT_LENGTH(str) 返回字符串的比特长度

CONCAT(s1,s2...,sn) 将s1,s2...,sn连接成字符串

CONCAT_WS(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔 INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置

LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果 LEFT(str,x) 返回字符串str中最左边的x个字符 LENGTH(s) 返回字符串str中的字符数

LTRIM(str) 从字符串str中切掉开头的空格 POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置 QUOTE(str) 用反斜杠转义str中的单引号

REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果

REVERSE(str) 返回颠倒字符串str的结果

RIGHT(str,x) 返回字符串str中最右边的x个字符 RTRIM(str) 返回字符串str尾部的空格

STRCMP(s1,s2) 比较字符串s1和s2

TRIM(str) 去除字符串首部和尾部的所有空格 UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果 4)日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须

按照关键字进行格式化),如:SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);

DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值

DATE_SUB(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须

按照关键字进行格式化),如:SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date) 返回date是一个月的第几天(1~31)

DAYOFYEAR(date) 返回date是一年的第几天(1~366)

DAYNAME(date) 返回date的星期名，如:SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

HOUR(time) 返回time的小时值(0~23)

MINUTE(time) 返回time的分钟值(0~59)

MONTH(date) 返回date的月份值(1~12)

MONTHNAME(date) 返回date的月份名，如:SELECT MONTHNAME(CURRENT_DATE);

NOW() 返回当前的日期和时间

QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

WEEK(date) 返回日期date为一年中第几周(0~53)

YEAR(date) 返回日期date的年份(1000~9999)

一些示例:

获取当前系统时间:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE); SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE); SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE); 返回两个日期值之间的差值(月数):SELECT PERIOD_DIFF(200302,199802); 在Mysql中计算年龄:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),

'%Y')+0 AS age FROM employee;

这样，如果Brithday是未来的年月日的话，计算结果为0。

下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -

(DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age

from employee

5)加密函数

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结

果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储

AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结

果

DECODE(str,key) 使用key作为密钥解密加密字符串str

ENCRYPT(str,salt) 使用UNIX crypt()函数，用关键词salt(一个可以惟一确定口令的字符

串，就像钥匙一样)加密字符串str

ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

MD5() 计算字符串str的MD5校验和

PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

SHA() 计算字符串str的安全散列算法(SHA)校验和

示例:

SELECT ENCRYPT('root','salt'); SELECT ENCODE('xufeng','key'); SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起

SELECT AES_ENCRYPT('root','key'); SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');

SELECT MD5('123456');

SELECT SHA('123456');

6)控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

MySQL控制流函数:

CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真，则返回resultN，否则返回default

CASE [test] WHEN[val1] THEN [result]...ELSE [default] END 如果test和valN相等，则返回resultN，否则返回default

IF(test,t,f) 如果test是真，返回t;否则返回 f

IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否则返回arg1

这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数;如果是NULL,将返回第二个参数。

如:SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(4*NULL,'false');

NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。

如:SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);

和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。

如:SELECT IF(1<10,2,3),IF(56>100,'true','false');

IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。

CASE函数的格式有些复杂，通常如下所示:

CASE [expression to be evaluated] WHEN [val 1] THEN [result 1]

WHEN [val 2] THEN [result 2]

WHEN [val 3] THEN [result 3]

......

WHEN [val n] THEN [result n]

ELSE [default result]

END

这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的 WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。

CASE函数还有另外一种句法，有时使用起来非常方便，如下:

CASE

WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result]

END

这种条件下，返回的结果取决于相应的条件测试是否为真。

示例:

mysql>SELECT CASE 'green'

WHEN 'red' THEN 'stop'

WHEN 'green' THEN 'go' END;

SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;

SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN (2+2)<>4 THEN 'not OK' END AS

STATUS;

SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROM UserLoginInfo;

SELECT fname,lname,(math+sci+lit) AS total, CASE WHEN (math+sci+lit) < 50 THEN 'D' WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C' WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B' ELSE 'A' END

AS grade FROM marks;

SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResult FROM users WHERE

uname = 'sue';#一个登陆验证

7)格式化函数

DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值

FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数

INET_ATON(ip) 返回IP地址的数字表示

INET_NTOA(num) 返回数字所代表的IP地址

TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值

其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。示例:

SELECT FORMAT(34234.34323432,3);

SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); SELECT DATE_FORMAT(19990330,'%Y-%m-%d');

SELECT DATE_FORMAT(NOW(),'%h:%i %p'); SELECT INET_ATON('10.122.89.47'); SELECT INET_NTOA(175790383);

8)类型转化函数

为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

示例:

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;

SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY); 9)系统信息函数

DATABASE() 返回当前数据库名

BENCHMARK(count,expr) 将表达式expr重复运行count次

CONNECTION_ID() 返回当前客户的连接ID

FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数

USER()或SYSTEM_USER() 返回当前登陆用户名

VERSION() 返回MySQL服务器的版本

示例:

SELECT DATABASE(),VERSION(),USER(); SELECT BENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。

6.5MySQL的Trigger

mysql 5开始支持trigger，mysql提供的trigger可以让你在对某个表进行操作的时候同时触发其他的表发生自己预定的操作，这也是与视图不同的。

CREATE TRIGGER语法

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt;

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。

trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。

trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一: INSERT:将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

UPDATE:更改某一行时激活触发程序，例如，通过UPDATE语句。

DELETE:从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。

可能会造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...语法:BEFORE INSERT触发程序对于每一行将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于行上是否有重复键。

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

trigger_stmt是当触发程序激活时执行的语句。

例子如下:

例1

use facebook_aboutme;

drop trigger facebook_aboutme.aboutme_insertopinion;

delimiter |

create trigger aboutme_insertopinion after insert

on facebook_aboutme.opinion

for each row

begin

update users set eye=eye+NEW.eye where userid=NEW.owner;

end|

delimiter ;

例2

delimiter

CREATE TRIGGER upd_check BEFORE UPDATE ON account

FOR EACH ROW

BEGIN

IF NEW.amount < 0 THEN

SET NEW.amount = 0;

ELSEIF NEW.amount > 100 THEN

SET NEW.amount = 100;

END IF;

END;//

delimiter ;

例3

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END;|

DELIMITER ;

里面还可以复杂的if判断语句及相应的函数过程。

新的MySQL手册中把触发器和事务处理，存储过程等项完全独立开，目前trigger只能触发SQL语句。这个SQL语句受一定的语法限制:禁止使用下述语句(对存储函数的的所有限制也适用于触发程序)。

限制

* CHECK TABLES

LOCK TABLES, UNLOCK TABLES

* LOAD DATA, LOAD TABLE

* SQL预处理语句(PREPARE、EXECUTE、DEALLOCATE PREPARE)。隐含意义:不能在存储子程序中使用动态SQL语句(其中，能够以字符串形式构造动态语句，然后执行它们)。从MySQL 5.0.13开始，对于存储程序放宽了该限制，但该限制仍适用于存储函数和触发程序。

* OPTIMIZE TABLE

* 执行显式或隐式提交或回滚操作的语句。

* 返回结果集的语句。包括没有INFO子句的SELECT语句，以及SHOW语句。能够用SELECT „ INTO，或使用光标和FETCH语句处理结果集的函数。

* FLUSH语句。注意，尽管能够在存储程序中使用FLUSH，但不能从存储函数或触发程序调用这类存储程序。

注意，尽管某些限制在正常情况下适用于存储函数和触发程序，不适用于存储程序，如果它们是从存储函数或触发程序中调用的，这些限制也适用于存储程序。使用存储子程序会导致复制问题。

INFORMATION_SCHEMA尚不包含PARAMETERS表，因此，对于需要在运行时获取子程序参数信息的应用程序来说，必须采用相应的规避错误，如解析SHOW CREATE语句的输出。

没有存储子程序调试工具。

存储子程序使用了具体化的光标，而不是固有光标(在服务器端生成结果集并对结果集进行高速缓冲处理，然后在客户端获取结果集时按行返回)。

不能提前处理CALL语句。无论是对服务器端预处理语句还是SQL预处理语句，均成立。为了防止服务器线程间的交互问题，当客户端发出语句时，服务器将使用可用的、用于语句执行的子程序和触发程序快照。也就是说，服务器将计算出可在语句执行期间使用的存储程序、函数和触发程序的列表，加载它们，然后进入语句执行。这意味着，在语句执行的同时，它不会看到其他线程对子程序所作的变更。

DROP TRIGGER语法

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

总之，mysql 的trigger 还是过于孱弱，使用不当还能造成数据库性能下降。 6.6MySQL Procedure

1)创建程序实例

创建存储过程之前，使用delimiter命令来把语句定界符从 ;变为//，创建完成后，还原语句结束符 delimiter ; 由于MySQL默认以";"为分隔符，则过程体的每一句都被MySQL以存储过程编译，则编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符用完了就把分隔符还原。

过程的开始与结束使用BEGIN.....END组合。例如:

DELIMITER //

DROP PROCEDURE IF EXISTS `firstdb`.`getCount` $$

CREATE PROCEDURE `getCount`(OUT totalCount INT)

DETERMINISTIC

BEGIN

SELECT COUNT(*) INTO totalCount FROM users;

select totalCount;

END //

DELIMITER ;

使用show procedure status函数来获得存储过程的信息, 它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储程序和所有存储函数的信息都被列出，格式为:

SHOW FUNCTION STATUS [LIKE 'pattern'];

2)调用程序实例

使用CALL sp_name([parameter[,...]]);语句调用一个先前用CREATE PROCEDURE创建的程序。

3)删除程序实例

DROP PROCEDURE sp_name

4) Parameters参数

create procedure p5()------; //参数列表是空的

create procedure p5([IN] name data-type)---- //输入参数in可选，默认为参数为in

create procedure p5(out name data-type)----- //输出参数out

create procedure p5(inout 那么data-type)----- //即可以做输入参数也可以做输出参数

----输入参数in 例子。

create procedure p5(p int) set @x=p;

call p5(12345);

select @x;

----输出参数out 例子

create procedure p6(out p int)set p=-5;

call p6(@y);

select @y;

5)复合语句:如果你的过程中有多条语句，那么你需要begin/end块。在这里你可以进行变量的定义和流程的控制

首先执行命令 delimiter //

create procedure p7()

BEGIN

set @a=6;

set @b=5;

insert into t values (@a);

select s1 * @a from t where s1>= @b;

END;//

-----在复合语句中申明变量

create procedure p8()

begin

DECLARE a INT;

DECLARE b INT;

SET a=5;

SET b=5;

insert into t values (a);

select s1*a from t where s1>=b;

end;//

含有default默认语句舌设定语句的例子

create procedure p9()

begin

declare a ,b int default 5;

insert into t values(a);

select s1*a from t where s1>=b;

end;//

6)scope作用域的问题:内部的变量在其作用域范围内享有更高的优先权，当执行到end

变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储

过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派

给会话变量来保存其值。

create procedure p11()

begin

declare x1 char(5) default 'outer';

begin

declare x1 char(5) default 'inner';

select x1;

end;

select x1;

end;//

/*************存储过程中的条件式语句 ***********************************/

if-then -else语句

create procedure p12(in parameter int)

begin

declare var int;

set var=parameter+1;

if var=0 then

insert into t values(17);

end if;

if parameter=0 then

update t set s1=s1+1;

else

update t set s1=s1+2;

end if;

end;//

case指令:如果需要进行更多条件真假的判断我们可以使用case语句

create procedure p13(in parameter int)

begin

declare var int;

set var=parameter+1;

case var

when 0 then insert into t values(17);

when 1 then insert into t values(18);

else insert into t values(19);

end case;

end;//

/***************************** 循环语句 ************************************/

while end while; 循环语句

create procedure p14()

begin

declare var int;

set var=0;

while var<6 do

insert into t values(var);

set var=var+1;

end while;

end;//

repeat end repeat ;它在执行操作后检查结果，而while则是执行前进行检查

create procedure p15()

begin

declare v int;

set v=0;

repeat

insert into t values(v);

set v=v+1;

until v>=5

end repeat;

end;//

loop end loop; loop 循环不需要初始条件，这点和while 循环相似，同时和repeat

循环一样不需要结束条件, leave语句的意义是离开循环，

create procedure p16()

begin

declare v int;

set v=0;

LOOP_LABLE:loop

insert into t values(v);

set v=v+1;

if v >=5 then

leave LOOP_LABLE;

end if;

end loop;

end;//

LABLES 标号:标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。 /**********************I TERATE迭代 ***************************************/

ITERATE:通过引用复合语句的标号,来从新开始复合语句

create procedure p20()

begin

declare v int;

set v=0;

LOOP_LABLE:loop

if v=3 then

set v=v+1;

ITERATE LOOP_LABLE;

end if;

insert into t values(v);

set v=v+1;

if v>=5 then

leave LOOP_LABLE;

end if;

end loop;

end;//

6.7基本函数

1) 16进制，2进制，8进制互转

16进制x->2进制 conv(―x‖,16,2)

2进制x->16进制 conv(―x‖,2,16)

16进制x->8进制conv(―x‖,16,8)

8进制x->16进制conv(―x‖,8,16)

2进制x->8进制conv(―x‖,2,8)

8进制x->2进制 conv(―x‖,8,2)

2) 通过身份证识别生日，年龄

3) IP地址转换

IP 由点分格式，转换为数字格式。

mysql> select inet_aton('202.96.128.68');

+----------------------------+ | inet_aton('202.96.128.68') | +----------------------------+ | 3395321924 | +----------------------------+ IP 由数字格式转换为点分格式。

mysql> select inet_ntoa(3395321924);

+-----------------------+ | inet_ntoa(3395321924) | +-----------------------+ | 202.96.128.68 | +-----------------------+

7.MySQL性能监控

1)获取mysql用户下的进程总数

ps -ef | awk '{print $1}' | grep "mysql" | grep -v "grep" | wc-1 2)主机性能状态

# uptime

[root@ ~]# uptime

13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00 3)CPU使用率

# top

或

# vmstat

4)磁盘IO量

# vmstat 或 # iostat

5)swap进出量[内存]

# free

6)数据库性能状态

(1)QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql > show /*50000 global */ status like 'Question'; (2)TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show status like 'Com_commit';

mysql > show status like 'Com_rollback';

(3)key Buffer 命中率

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% mysql> show status like 'Key%';

(4)InnoDB Buffer命中率

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

* 100%

mysql> show status like 'innodb_buffer_pool_read%'; (5)Query Cache命中率

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

mysql> show status like 'Qcache%';

(6)Table Cache状态量

mysql> show status like 'open%';

(7)Thread Cache 命中率

Thread_cache_hits = (1 - Threads_created / connections ) * 100% mysql> show status like 'Thread%';

mysql> show status like 'Connections';

(8)锁定状态

mysql> show status like '%lock%';

(9)复制延时量

mysql > show slave status (10) Tmp Table 状况(临时表状况)

mysql > show status like 'Create_tmp%';

(11) Binlog Cache 使用状况

mysql > show status like 'Binlog_cache%';

(12) Innodb_log_waits 量

mysql > show status like 'innodb_log_waits';

开源监控软件

1)RRDTool

2)Nagios

3)MRTG

4)Cacti

使用mysql profiles 来查看sql 语句执行计划

要使用该功能，mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检

查。

profiling 功能可以了解到cpu io 等更详细的信息。 show profile 的格式如下:

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL

| BLOCK IO

| CONTEXT SWITCHES

| CPU

| IPC

| MEMORY

| PAGE FAULTS

| SOURCE

| SWAPS

默认方式下该功能是关闭的:

mysql>select @@profiling; +-------------+

| @@profiling |

+-------------+

| 0 |

+-------------+

1 row in set (0.00 sec) 打开功能

mysql>set profiling=1; +-------------+

| @@profiling |

+-------------+

| 1 |

+-------------+

1 row in set (0.00 sec)

输入需要执行的sql 语句:

mysql>select count(*) from sysuser;

mysql> show profilesG;

*************************** 1. row *************************** Query_ID: 1

Duration: 0.00007550

Query: select count(*) from sysuser

1 row in set (0.00 sec)

通过指定的Query_ID 来查询指定的sql语句的执行信息:

mysql> show profile for query 1;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000028 |

| checking query cache for query | 0.000008 |

| checking privileges on cached | 0.000009 |

| sending cached result to clien | 0.000023 |

| logging slow query | 0.000004 |

| cleaning up | 0.000003 |

+--------------------------------+----------+

6 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 1;

+--------------------------------+----------+----------+------------+--------------+---------------+

+--------------------------------+----------+----------+------------+--------------+---------------+

NULL |

NULL |

NULL |

+--------------------------------+----------+----------+------------+--------------+---------------+

6 rows in set (0.00 sec)

如果不带for 参数则指列出最后一条语句的profile 信息:

mysql> show profile cpu,block io for query 1;

+--------------------------------+----------+----------+------------+--------------+---------------+ 6 rows in set (0.00 sec)

关闭参数:

mysql> set profiling=0

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| profiling | OFF |

+---------------+-------+

1 row in set (0.00 sec)

使用工具软件，比如phpMyAdmin、mysqlreport等。

8.MySQL备份与恢复

1)使用mysql相关命令进行简单的本地备份

1 mysqlldump命令

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同

MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

使用 mysqldump进行备份非常简单，如果要备份数据库‖ db_backup ‖,使用命令:

#mysqldump –u -p phpbb_db_backup > /usr/backups/mysql/db_backup2008-1-6.sql

还可以使用gzip命令对备份文件进行压缩:

#mysqldump db_backup | gzip > /usr/backups/mysql/ db_backup2008-1-6.sql.gz

只备份一些频繁更新的数据库表:

## mysqldump sample_db articles comments links >

/usr/backups/mysql/sample_db.art_comm_lin.2008-1-6.sql

上面的命令会备份articles, comments, 和links 三个表。

恢复数据使用命令:

#mysql –u -p db_backup </usr/backups/mysql/ db_backup2008-1-6.sql

注意使用这个命令时必须保证数据库正在运行。

2 使用 SOURCE 语法

其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如:

# SOURCE /tmp/db_name.sql;

这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。

3 mysqlhotcopy备份

mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 linux 和Unix 和 NetWare 系统上。mysqlhotcopy 支持一次性拷贝多个数据库，同时还支持正则表达。以下是几个例子: #mysqlhotcopy -h=localhost -u=goodcjh -p=goodcjh db_name /tmp

(把数据库目录 db_name 拷贝到 /tmp 下)

注意，想要使用 mysqlhotcopy，必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限，并且还必须要能够有读取 datadir/db_name 目录的权限。

还原数据库方法:

mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的目录 (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，另外首先应当删除数据库旧副本如下例:

# /bin/rm -rf /mysql-backup/**//*old

关闭mysql 服务器、复制文件、查询启动mysql服务器的三个步骤: # /etc/init.d/mysqld stop

Stopping MySQL: [ OK ]

# cp -af /mysql-backup/**//* /var/lib/mysql / # /etc/init.d/mysqld start

Starting MySQL: [ OK ]

#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)

2)使用网络备份

将MYSQL数据放在一台计算机上是不安全的，所以应当把数据备份到局域网中其他Linux计算机中。假设Mysql服务器IP地址是:192.168.1.3。局域网使用Linux的远程计算机IP地址是192.168.1.4;类似于windows的网络共享，UNIX(Linux)系统也有自己的网络共享，那就是NFS(网络文件系统)，在linux客户端挂接(mount)NFS磁盘共享之前，必须先配置好NFS服务端。linux系统NFS服务端配置方法如下:

(1)修改 /etc/exports,增加共享目录

/export/home/sunky 192.168.1.4(rw) /export/home/sunky1 *(rw)

/export/home/sunky2 linux-client(rw)

注:/export/home/目录下的sunky、sunky1、sunky2是准备共享的目录，10.140.133.23、*、linux-client是被允许挂接此共享linux客户机的IP地址或主机名。如果要使用主机名linux-client必须在服务端主机/etc/hosts文件里增加linux-client主机ip定义。格式如下:

192.168.1.4 linux-client

若修改/etc/export文件增加新的共享，应先停止NFS服务，再启动NFS服务方能使新增加的共享起作用。使用命令exportfs -rv也可以达到同样的效果。linux客户端挂接(mount)其他linux系统或UNIX系统的NFS共享。这里我们假设192.168.1.4是NFS服务端的主机IP地址，当然这里也可以使用主机名，但必须在本机/etc/hosts文件里增加服务端ip定义。/export/home/sunky为服务端共享的目录。如此就可以在linux客户端通过/mnt/nfs来访问其它linux系统或UNIX系统以NFS方式共享出来的文件了。

把MYSQL数据备份到使用Linux的远程计算机需要在两端都安装NFS协议(Network File System)，远程NFS计算机安装NFS协议后还要修改配置文件:/etc/exports,加入一行: /usr/backups/mysql/ 192.168.1.4 (rw, no_root_squash)

表示将/usr/backups/mysql/目录共享。这个目录具有远程root用户读写权限。保存NFS配置文件，然后使用命令:

#exportfs -a –r

然后重新启动NFS服务:

#service nfsd start

远程计算机设定后，在MYSQL服务器/mnt 目录下建立一个backup_share目录: #mkdir /mnt/backup_share

将远程的Linux计算机的/usr/backups/mysql/目录挂载到MYSQL服务器的/mnt/backup_share目录下:

# mount -t nfs 192.168.1.4:/usr/backups/mysql /mnt/backup_share

将目录挂载进来后，只要进入/mnt/backup_share 目录，就等于到了IP地址:192.168.1.4那部NFS 计算机的/usr/backups/mysql 目录中。下面使用mysqldump把―phpbb_db_backup‖备份到远程计算机:

# mysqldump db_backup > /mnt/backup_share/ db_backup2008-1-6.sql

自动完成网络备份的方法:

Linux 服务器上的程序每天都在更新 MySQL 数据库，于是就想起写一个 shell 脚本，结合 crontab，定时备份数据库。建立一个shell脚本:sample_db_backup.sh # At the very end the $(date +%F) 自动添加备份日期

mysqldump -u <username> -p <password> -h <hostname> sample_db >

/mnt/backup_share/sample_db.$(date +%F) #un-mount the filesystem

umount /mnt/backup_share

# mount u2013o soft 192.168.1.4:/archive /mnt/backup_share

说明:mount NFS服务器的一个重要参数:hard (硬) mount或soft(软)mount。

硬挂载: NFS客户机会不断的尝试与NFS服务器的连接(在后台，一般不会给出任何提示信息)，直到挂载上为止。

软挂载:会在前台尝试与NFS服务器的连接，是默认的连接方式。当收到错误信息后终止mount尝试，并给出相关信息。

对于到底是使用硬挂载还是软挂载的问题，这主要取决于你访问什么信息有关。例如你是想察看NFS服务器的视频文件时，你绝对不会希望由于一些意外的情况(如网络速度一下子变的很慢)而使系统输出大量的错误信息，如果此时你用的是硬挂载方式的话，系统就会等待，直到能够重新与NFS 服务器建立连接传输信息。另外如果是非关键数据的话也可以使用软挂载方式，如FTP一些数据等，这样在远程机器暂时连接不上或关闭时就不会挂起你的会话过程。

下面建立脚本文件权限:chmod +x ./sample_db_backup.sh

然后使用将此脚本加到 /etc/crontab 定时任务中:

01 5 * * 0 mysql /home/mysql/ sample_db_backup.sh

好了，每周日凌晨 5:01 系统就会自动运行 sample_db_backup.sh 文件通过网络备份 MySQL 数据库了。

3)实时恢复M y S Q L数据方法

在对MySQL数据和表格结构进行备份时，mysqldump是一个非常有用的工具。然而，通常情况下，一般一天只备份一次，或者在一个特定的间隔备份一次。如果在刚备份完成的一段时间以内数据丢失，那么这些数据很有可能无法恢复。有什么方法可以对数据进行实时性地保护呢事实上，现在有几种方法都可以实现MySQL数据库的实时保护。这里介绍其中一种，即使用二进制日志进行数据恢复。

1 设置二进制日志方法

要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为-- log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句:

# SHOW BINLOG EVENTS G

2 最简单的数据恢复

每天备份和运行二进制日志的确是一个在MySQL服务器中恢复数据的不错方法。比如，可以每天在深夜使用mysqldump对数据进行备份，如果某天在数据备份完成后的一段时间里，由于某种原因数据丢失，可以使用以下方法来对其进行恢复。首先，停止MySQL服务器，然后使用以下命令重新启动MySQL服务器。该命令将保证是惟一可以访问该数据库服务器的人:

# /etc/init.d/mysqld stop

Stopping MySQL: [ OK ]

# mysqld --socket=/tmp/mysql_restore.sock --skip-networking

这里，一socket选项将为U n i x 系统命名一个不同的Socket文件。一旦服务器处于独占控制之下，就可以放心地对数据库进行操作，而不用担心在进行数据恢复的过程中有用户尝试访问数据库而导致更多的麻烦。进行恢复的第一个步骤是恢复晚上备份好的dump文件: #mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock < /var/backup/20080120.sql

该命令可以将数据库的内容恢复至晚上刚刚完成备份的内容。要恢复dump文件创建后的数据库事务处理，可以使用mysqlbinlog工具。如果每天晚上进行备份操作时都对日志进行flush操作，则可以使用以下命令行工具将整个二进制日志文件进行恢复: mysqlbinlog /var/log/mysql/bin.123456 | mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock

3 针对某一时问点的恢复

对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。假设用户在2008-1-22上午10点执行的SQL语句删除了一个大的数据表，则可以使用以下命令进行恢复:要想恢复表和数据，你可以恢复前晚上的备份，并输入:

#mysqlbinlog --stop-date="2008-1-22 9:59:59" /var/log/mysql/bin.123456 |

mysql -u root -pmypwd

--socket=/tmp/mysql_restore.sock

#mysql -u root -pmypwd

该语句将恢复所有给定一stop-date日期之前的数据。如果在执行某SQL语句数小时之后才发现执行了错误操作，那么可能还需要恢复之后输入的一些数据。这时，也可以通过mysqlbinlog来完成该功能:

#mysqlbinlog --start-date="2008-1-22 10:01:00"

/var/log/mysql/bin.123456

| mysql -u root -pmypwd

--socket=/tmp/mysql_restore.sock #mysql -u root -pmypwd

在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。

4 使用Position进行恢复

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作命令为:

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用vi或者gedit文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容:

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456

| mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456

| mysql -u root -pmypwd

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

5 其他方法

对于一个标准安装的MySQL，通过二进制日志完全恢复任何时刻丢失的数据是一件非常简单、快捷的事情。当然，如果无法忍受使用该方法的要求，比如在进行恢复操作时要锁住其他用户等，也可以使用其他方法来保护数据:

4)定时备份

利用系统crontab来定时执行备份文件，按日期对备份结果进行保存，达到备份的目的。 1、创建保存备份文件的路径/mysqldata

#mkdir /mysqldata

2、创建/usr/sbin/bakmysql文件

#vi /usr/sbin/bakmysql

输入

rq=` date +%Y%m%d `

tar zcvf /mysqldata/mysql$rq.tar.gz /var/lib/mysql

或者写成

rq=` date +%Y%m%d `

mysqldump --all-databases -u root -p980405 > /mysqldata/mysql$rq.tar.gz

/var/lib/mysql是你数据库文件的目录，部分用户是/usr/local/mysql/data，每个人可能不同 /mysqldata/表示保存备份文件的目录，这个每个人也可以根据自己的要求来做。 3、修改文件属性，使其可执行

# chmod +x /usr/sbin/bakmysql 4、修改/etc/crontab

#vi /etc/crontab

在下面添加

01 3 * * * root /usr/sbin/bakmysql 表示每天3点钟执行备份

5、重新启动crond

# /etc/rc.d/init.d/crond restart 完成。

这样每天你在/mysqldata可以看到这样的文件

mysql20040619.tar.gz

你直接下载就可以了。

在tar命令执行前，停止数据库服务进程或锁定数据库，

否则恢复数据时，会出现数据库损坏的情形，运气好时可修复，运气不好时就不可以了。

9.排错日志

MySQL的错误信息是在data目录下的，且文件名为<hostname>.err(<hostname>指的是主机名)，可以打开进行查看。

另外，MySQL提供了一个方便查看错误代码的命令-perror。该工具可以快速的定位错误代码发生了什么问题。

# perror 12 默认会显示哪种错误代码和错误信息

OS error code 12: Not enough space # perror 12 13 14 可以同时显示多个错误代码消息

OS error code 12: Not enough space OS error code 13: Permission denied OS error code 14: Bad address # perror -s 12 13 14 通过-s参数只显示错误信息

Not enough space

Permission denied

Bad address

10.DB Link

1)MySQL中没有dblink

2)如果数据库在同一台服务器上，你可以使用

select a.col from db1.table1 a, db2.table2 b where a.col = b.col

3)如果要从oracle导出数据到MySQL可以使用工具软件。

11.字符集

MySQL的字符集支持(Character Set Support)有两个方面:字符集(Character set)和排序

方(Collation)。对于字符集的支持细化到四个层次:

服务器(server)，数据库(database)，数据表(table)和连接(connection)。

1.MySQL默认字符集:MySQL对于字符集的指定可以细化到一个数据库，一张表，一

列.传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配

置.

(1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1;

(2)安装MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没

指定，这个值继承自编译时指定的;(

3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这

个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符

集;

(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为

character_set_server;

(5)当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符

集;

(6)在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，

也就是这个数据库默认的字符集;

(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符

集;如果什么地方都不修改，那么所有的数据库的所有表的所有栏位的都用 latin1 存

储，不过我们如果安装 MySQL，一般都会选择多语言支持，也就是说，安装程序会

自动在配置文件中把 default_character_set 设置为 UTF-8，这保证了缺省情况下，

所有的数据库的所有表的所有栏位的都用 UTF-8 存储。

2.查看默认字符集(默认情况下，mysql的字符集是latin1(ISO_8859_1)通常，查看系

统的字符集和排序方式的设定可以通过下面的两条命令:

mysql> SHOW VARIABLES LIKE 'character%';

| character_sets_dir | D:"mysql-5.0.37"share"charsets" | +--------------------------+---------------------------------+

mysql> SHOW VARIABLES LIKE 'collation_%';

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

+----------------------+-----------------+

3.修改默认字符集

(1) 最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，

如 default-character-set = utf8

character_set_server = utf8

修改完后，重启mysql的服务，service mysql restart

使用 mysql> SHOW VARIABLES LIKE 'character%';查看，发现数据库编码均已改成utf8

+--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | D:"mysql-5.0.37"share"charsets" | +--------------------------+---------------------------------+

(2) 还有一种修改字符集的方法，就是使用mysql的命令

mysql> SET character_set_client = utf8 ;

mysql> SET character_set_connection = utf8 ;

mysql> SET character_set_database = utf8 ;

mysql> SET character_set_results = utf8 ;

mysql> SET character_set_server = utf8 ;

mysql> SET collation_connection = utf8 ;

mysql> SET collation_database = utf8 ;

mysql> SET collation_server = utf8 ;

一般就算设置了表的默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数

据库的仍然是乱码。问题就出在这个connection连接层上。解决方法是在发送查询前执行

一下下面这句:

SET NAMES 'utf8';

它相当于下面的三句指令:

SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8; 12.高可用

1) 简单方案

实现原理:

通过Linux HA 软件 heartbeat 实现IP的自动漂移，即当一台服务器宕机后，浮动IP(整个cluster的对外IP )自动漂移到另外一台服务器。通过Mysql自身的replication 实现不同机器上多个数据库的同步。

整体性能:

此方案将会降低MYSQL 1,左右的性能，可用性及数据安全性将大有提高，同时服务器的切换对终端使用者是透明的，终端应用不需要进行更改。

所需硬件:

安装有双网卡的配置大致相同的服务器或工作机两台(测试而已，用Vmware虚拟吧)，一条交叉网线(用于双机对连的心跳线)。

所需软件:

Linux HA 软件 heartbeat (只支持两个节点)和MySQL

硬件设置:

Node1:

计算机名:RHEL1

eth0 : 192.168.168.1/255.255.255.0

eth1 : 10.0.0.1/255.0.0.0 (用于心跳)

Node2:

计算机名:RHEL2

eth0 : 192.168.168.2/255.255.255.0

eth1 : 10.0.0.2/255.0.0.0 (用于心跳)

Node1和Node2的eth0分别连接到你的交换机

Node1和Node2的eth1之间用心跳线直连起来，做心跳

安装设置MySQL:

安装过程如前所述，安装后，设置mysql root用户密码

mysql -u root (登入mysql,以下操作在mysql命令行下进行)

mysql>set password forroot@localhost=password('yourpassword'); (设置从本地主机登录的 root用户密码)

mysql>use mysql;

mysql>delete from user where user='' ; (删除匿名用户)

mysql>delete from user where host<>'localhost' ; (只充许从localhost登录)

mysql>flush privileges ; (使更改生效)

mysql>q (退出mysql命令行)

设置Mysql Replication:

在Node1上采取下述方法增加一用于数据库同步的用户replicate mysql -u root -p (执行后提示输入密码，正确输入mysql root用户密码后，登入mysql，以

下操作在mysql命令行下进行)

mysql>GRANT REPLICATION SLAVE ON *.* TOreplicate@10.0.0.2IDENTIFIED BY

'password' ;

(授与从10.0.0.2主机上登录用户replicate数据复制权限,4.02版本以前用: GRANT FILE ON *.* TOreplicate@10.0.0.2IDENTIFIED BY 'password';)

mysql>flush privileges; (使权限生效，这步可不做，因为过会要重启mysql)

mysql>q (退出mysql命令行界面)

停止mysql服务service mysqld stop

更改Mysql配置文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]一节中增加以下内容

server-id=1 #服务器的标识 1~2^32-1，不同主机设为不同的值，否则可能会出现不可预料的错误

#以下内容为作为Master的配置，如果此服务器只作为Slave，注释掉以下各行

log-bin #允许二进制更新日志

binlog-do-db=dbname #指定做二进制更新日志的数据库,多个数据库用多条此语句, 如,还有anotherdb,去掉下行的注释

#binlog-do-db=anotherdb

binlog-ignore-db=dbname #指明数据库dbname不做二进制更新日志，没有则注释掉此行，多个数据库用多条此语句,如,还有anotherigdb,去掉下行的注释

#binlog-ignore-db=anotherigdb

#以下内容为作为Slave的配置，如果此服务器只作为Master，注释掉以下各行

master-host=10.0.0.2 #Master服务器地址

master-user=replicate #Master服务器上用于replication的用户，必须有replication slave权限

master-password=replicatepass #Master服务器上用于replication的用户的密码

master-port=3306 #Master服务器的TCP端口

master-connect-retry=60 #如果连接Master服务器失败，重试连接的间隔，以秒为单位

replicate-do-db=dodbname #指明要复制的数据库,多个数据库用多条此语句，如，还有anotherdb,去掉下行的注释

#replicate-do-db=anotherdb

replicate-ignore-db=igdbname #指明不做复制的数据库，多个数据库用多条此语句，如，还有anotherigdb,去掉下行的注释

#replicate-ignore-db=anotherigdb

将要进行复制的数据库打包传到Node2

tar -cvf /tmp/mysql-thisdb-snapshot.tar /var/lib/mysql/thisdb (将要进行复制的thisdb数据库打包)

scp /tmp/mysql-thisdb-snapshot.tarroot@10.0.0.2:/tmp/ (将mysql-thisdb-snapshot.tar复制到Node2的/tmp目录)

启动Mysql

service mysqld start

至此，Node1上的Mysql设置完毕，以下操作在Node2上完成

增加一用于数据库同步的用户replicate

mysql -u root -p (执行后提示输入密码，正确输入mysql root用户密码后，登入mysql，以下操作在mysql命令行下进行)

mysql>GRANT REPLICATION SLAVE ON *.* TOreplicate@10.0.0.1IDENTIFIED BY

'password' ;

(授与从10.0.0.1主机上登录用户replicate数据复制权限,4.02版本以前用:GRANT FILE ON *.* TOreplicate@10.0.0.1IDENTIFIED BY 'password';)

mysql>flush privileges; (使权限生效，这步可不做，因为过会要重启mysql)

mysql>q (退出mysql命令行界面)

停止mysql服务

service mysqld stop

更改Mysql配置文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]一节中增加以下内容

server-id=2 #服务器的标识 1~2^32-1，不同主机设为不同的值，否则可能会出现不可预料的错误

#以下内容为作为Master的配置，如果此服务器只作为Slave，注释掉以下各行

log-bin #允许二进制更新日志

binlog-do-db=dbname #指定做二进制更新日志的数据库,多个数据库用多条此语句,如,还有anotherdb,去掉下行的注释

#binlog-do-db=anotherdb

binlog-ignore-db=dbname #指明数据库dbname不做二进制更新日志，没有则注释掉此行，多个数据库用多条此语句,如,还有anotherigdb,去掉下行的注释

#binlog-ignore-db=anotherigdb

#以下内容为作为Slave的配置，如果此服务器只作为Master，注释掉以下各行

master-host=10.0.0.1 #Master服务器地址

master-user=replicate #Master服务器上用于replication的用户，必须有replication slave权限

master-password=replicatepass #Master服务器上用于replication的用户的密码

master-port=3306 #Master服务器的TCP端口

master-connect-retry=60 #如果连接Master服务器失败，重试连接的间隔，以秒为单位

replicate-do-db=dodbname #指明要复制的数据库,多个数据库用多条此语句，如，还有anotherdb,去掉下行的注释

#replicate-do-db=anotherdb

replicate-ignore-db=igdbname #指明不做复制的数据库，多个数据库用多条此语句，如，还有anotherigdb,去掉下行的注释

#replicate-ignore-db=anotherigdb

将先前从Node1上发过来的数据库的打包文件解压并替换掉原有文件,并确保属主及权限正确

cd /var/lib/mysql

tar -xvf /tmp/mysql-thisdb-snapshot.tar

chown -R mysql.mysql thisdb

chmod 700 thisdb

chmod 660 thisdb/*

启动Mysql

service mysqld start

至此Node2上的Mysql设置完毕

在Mysql中可通过以下命令来查看主从状态

show master status 查看master状态

show slave status 查看slave状态

show processlist G 查看当前进程

stop slave 暂时停止slave进程

start slave 开始slave进程

其它相关命令及详细配置参数请自行查阅Mysql在线文档

安装配置Linux HA软件heartbeat:

在每个节点上分别安装heartbeat

1.安装支持包(linux-ha.org站点提供下载)

Linux的版本不同，RPM文件名可能会不同，请选择相应版本的RPM

rpm -iUvh libnet-1.1.0-1.rh.el.1.i386.rpm

rpm -iUvh ipvsadm-1.21-1.rh.el.1.i386.rpm

rpm -iUvh perl-Parse-RecDescent-1.80-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-Mail-IMAPClient-2.2.7-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-Net-SSLeay-1.23-1.rh.el.um.1.i386.rpm

rpm -iUvh perl-Digest-SHA1-2.01-10.i386.rpm

rpm -iUvh perl-Digest-HMAC-1.01-11.noarch.rpm

rpm -iUvh perl-Authen-SASL-2.03-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-Convert-ASN1-0.16-2.rh.el.um.1.noarch.rpm

rpm -iUvh perl-IO-Socket-SSL-0.92-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-XML-NamespaceSupport-1.08-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-XML-SAX-0.12-1.rh.el.um.1.noarch.rpm

rpm -iUvh perl-ldap-0.2701-1.rh.el.um.1.noarch.rpm

2.按装heartbeat

以RPM方式按装(推荐)，Linux的版本不同，RPM文件名可能会不同，请选择相应

版本的RPM

rpm -iUvh heartbeat-pils-1.3.0-1.rh.el.3.0.i386.rpm

rpm -iUvh heartbeat-stonith-1.3.0-1.rh.el.3.0.i386.rpm

rpm -iUvh heartbeat-ldirectord-1.3.0-1.rh.el.3.0.i386.rpm

rpm -iUvh heartbeat-1.3.0-1.rh.el.3.0.i386.rpm

3.Copy配置文件到/etc/ha.d目录 (只在一个节点上做即可，所有配置完成后copy到其

它节点)

cp /usr/share/doc/heartbeat-1.3.0/ha.cf /etc/ha.d/

cp /usr/share/doc/heartbeat-1.3.0/haresources /etc/ha.d/

cp /usr/share/doc/heartbeat-1.3.0/authkeys /etc/ha.d/

4.配置/etc/ha.d/ha.cf (只在一个节点上配即可，配好后copy到其它节点)

vi /etc/ha.d/ha.cf

按如下内容进行修改(文件中#开头的行为注释行,更多的选项及选项的详细说明请参考

文件中的注释及相关文档)

debugfile /var/log/ha-debug #写debug信息到这个文件中

logfile /var/log/ha-log #写运行日志到这个文件中

keepalive 2 #设置心跳时间为2秒

deadtime 30 #设置离最近一次心跳多长时间没有心跳时表明节点失败

warntime 10 #设置离最近的一次心跳多长时间没有心跳时发出警告

initdead 120 #机器重启动或是刚开机时，网络能正确开始工作的时间，最小设置为deadtime的两倍

udpport 694 #bcast/ucast方式心跳通讯所用的UDP端口

#baud 19200 #串口的波特率，使用串口作心跳时需设置

#serial /dev/ttyS0 #串口设备名，使用串口作心跳时需设置

bcast eth1 #使用哪一个设备(网卡)做心跳

#bcast eth1 eth2 #使用多个设备(网卡)做心跳时的设置

auto_failback on #当主节点从失败状态恢复时是否恢复其主节点的身份，即应用是否返回到其自身运行

#on 返回

#off 不返回，其它节点继续作为主节点运行

#legacy 当所有节点都不支持自动返回时，将自身设为自动返回

node RHEL1 #节点的主机名或域名，需在/etc/hosts文件或

DNS服务器中设置

node RHEL2 #所有节点都以node开始的行列出

ping 192.168.168.254 #ping节点，不属于cluster内的节点，通常选一台路由器

或交换机作为Ping节点，

#ipfail模块通过此节点来验证网络的连通性，可指定多个Ping节点，

如:

#ping 192.168.168.253 192.168.0.254 ns1.rhel.com ping.linux-ha.org

respawn hacluster /usr/lib/heartbeat/ipfail #由heartbeat调用并监视ipfail模块

5.配置/etc/ha.d/haresources (只在一个节点上配即可，配好后copy到其它节点)

vi /etc/ha.d/haresources

按如下内容进行修改(文件中#开头的行为注释行,更多的选项及选项的详细说明请参考文件中的注释及相关文档)

RHEL1 192.168.168.103 mysqld #设置heartbeat管理的资源或服务

#格式为:主节点主机名或域名浮动IP 服务名

#主节点主机名或域名为ha.cf中node行中指定的任一节点

#浮动IP 为对外提供访问的IP,主节点失败后会自动漂移到其它节点，继续对外提供服务.

#服务名为 heartbeat管理的服务

#注意，此文件内容所有节点必须保持一致。

6.配置/etc/ha.d/authkeys (只在一个节点上配即可，配好后copy到其它节点)

vi /etc/ha.d/authkeys

按如下内容进行修改(文件中#开头的行为注释行,更多的选项及选项的详细说明请参考文件中的注释及相关文档)

auth 1 #设置认证方式

1 crc #格式为:auth <number>

#auth 2 # <number> <authmethod>

[<authkey>]

#2 md5 Hello! #如果是用心跳是用交叉线直接对连两个节点，用crc方式即可，CPU占用最少

#auth 3 #如果心跳位于不安全的网络，如通过共用交换机连接节点，则选用sha1或md5

#3 sha1 HI! #sha1占用CPU资源更多，但是更安全，md5安全性及CPU占用率居中

#sha1和md5都需要提供认证KEY，即用来加密的KEY

#注意，此文件权限必须设置为600

7.copy配置文件到其它节点

scp /etc/ha.d/ha.cf /etc/ha.d/haresources /etc/ha.d/authkeysroot@RHEL2:/etc/ha.d/

8.分别设置各节点/etc/ha.d/authkeys文件权限

chmod 600 /etc/ha.d/authkeys (文件权限必须设置为600,否则heartbeat不能正常启动)

9.在每个节点上启动heartbeat

service heartbeat start

2)采用mysql cluster

安装环境及软件包:

2台PC

mysql-6.0.0-alpha.tar.gz

Centos5

SerA:192.168.1.50

SerB:192.168.1.8

在SerA和SerB上安装MySQL

以下步骤需要在SerA和SerB上各做一次:

# mv mysql-6.0.0-alpha.tar.gz /tmp/package

# cd /tmp/package

# groupadd mysql

# useradd -g mysql mysql

# tar -zxvf mysql-6.0.0-alpha.tar.gz

# rm -f mysql-6.0.0-alpha.tar.gz

# mv mysql-6.0.0-alpha mysql

# cd mysql

# ./configure --prefix=/usr/local --with-extra-charsets=complex --with-plugin-ndbcluster

--with-plugin-partition --with-plugin-innobase

--with-unix-socket-path=/usr/local/var/mysql.sock

# make && make install

#ln -s /usr/local/libexec/ndbd /usr/bin

#ln -s /usr/local/libexec/ndb_mgmd /usr/bin

#ln -s /usr/local/libexec/ndb_cpcd /usr/bin

#ln -s /usr/local/libexec/mysqld /usr/bin

#ln -s /usr/local/libexec/mysqlmanager /usr/bin

#mysql_install_db --user=mysql

安装并配置节点:

以下步骤需要在SerA和SerB上各做一次

1.配置管理节点配置文件:

# mkdir /var/lib/mysql-cluster # cd /var/lib/mysql-cluster # vi config.ini

在config.ini中添加如下内容:

[ndbd default]

NoOfReplicas= 2

MaxNoOfConcurrentOperations= 10000 # Amount of memory required=(SizeofDatabase * NumberOfReplicas * 1.1 ) /

NumberOfDataNodes

DataMemory= 128M

IndexMemory= 24M

TimeBetweenWatchDogCheck= 30000 DataDir=/var/lib/mysql-cluster MaxNoOfOrderedIndexes= 512 StartPartialTimeout=100

StartPartitionedTimeout=100 ArbitrationTimeout=5000

TransactionDeadlockDetectionTimeout=5000

HeartbeatIntervalDbDb=5000 StopOnError=0

[ndb_mgmd default]

DataDir=/var/lib/mysql-cluster [ndb_mgmd]

Id=1

HostName= 192.168.1.50

[ndb_mgmd]

Id=2

HostName= 192.168.1.8

[ndbd]

Id= 3

HostName= 192.168.1.50

[ndbd]

Id= 4

HostName= 192.168.1.8

[mysqld]

ArbitrationRank=2 (非常重要,全靠有它,才可以形成仲裁竞争,从而当另一个机子当了时,

此机还可以有知道partion完整的节点)

[mysqld]

ArbitrationRank=2

[mysqld] (多出的这项是留给恢复时使用的.)

[tcp default]

PortNumber= 63132

[separator]

2.配置通用my.cnf文件，mysqld及ndbd,ndb_mgmd均使用此文件.

# vi /etc/my.cnf

在my.cnf中添加如下内容:

[mysqld]

datadir=/usr/local/var

socket=/usr/local/var/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

default-storage-engine=ndbcluster

ndbcluster

ndb-connectstring=192.168.1.50,192.168.1.8 [ndbd]

connect-string=192.168.1.50,192.168.1.8 [ndb_mgm]

connect-string=192.168.1.50,192.168.1.8 [ndb_mgmd]

config-file=/var/lib/mysql-cluster/config.ini [mysql_cluster]

ndb-connectstring=192.168.1.50,192.168.1.8 [mysql.server]

user=mysql

basedir=/usr/local/

[mysqld_safe]

log-error=/var/log/mysqld.log

#pid-file=/var/run/mysqld/mysqld.pid

[mysql]

#socket=/usr/local/var/mysql.sock

[mysqladmin]

#socket=/usr/local/var/mysql.sock

[ndb_restore default]

保存退出后

.启动管理节点SerA为:

[root@SerA ~]# ndb_mgmd --ndb_nodeid=1 Cluster configuration warning:

arbitrator with id 1 and db node with id 3 on same host 192.168.1.50

arbitrator with id 2 and db node with id 4 on same host 192.168.1.8

arbitrator with id 5 has no hostname specified arbitrator with id 6 has no hostname specified Running arbitrator on the same host as a database node may

cause complete cluster shutdown in case of host failure.

注:在启动时有一个警告提示

说节点1和3，2和4的arbitrator一样，可能引起整个集群失败。(可以不用放在心上)

启动管理节点SerB为:

[root@SerB ~]# ndb_mgmd --ndb_nodeid=2

初始化集群

在SerA中

[root@SerA ~]# ndbd --ndb_nodeid=3 --initial 在SerB中

[root@SerB ~]# ndbd --ndb_nodeid=4 --initial 注:只有在第一次启动ndbd时或者对config.ini进行改动后才需要使用--initial参数～(在

下面为了进行恢复实验时还再次使用到)

检查工作状态

在任意一台机子上启动管理终端:

[root@SerA ~]# ndb_mgm -e show

Connected to Management Server at: 192.168.1.50:1186 Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=3 @192.168.1.50 (Version: 6.0.0, Nodegroup: 0, Master) id=4 @192.168.1.8 (Version: 6.0.0, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s)

id=1 @192.168.1.50 (Version: 6.0.0)

id=2 @192.168.1.8 (Version: 6.0.0)

[mysqld(API)] 3 node(s)

id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) id=7 (not connected, accepting connect from any host) 如果上面没有问题，现在开始加入mysqld(API)

加入mysqld(API)

注意，这篇文档对于MySQL并没有设置root密码，推荐你自己设置SerA和SerB的

MySQL root密码。

在SerA 中:

[root@SerA ~]# mysqld_safe --ndb_nodeid=5 --user=mysql & 在SerB 中:

[root@SerB ~]# mysqld_safe --ndb_nodeid=6 --user=mysql & 再次检查工作状态,看mysql节点是否加入成功

[root@SerA ~]# ndb_mgm -e show

Connected to Management Server at: 192.168.1.50:1186 Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=3 @192.168.1.50 (Version: 6.0.0, Nodegroup: 0, Master) id=4 @192.168.1.8 (Version: 6.0.0, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s)

id=1 @192.168.1.50 (Version: 6.0.0)

id=2 @192.168.1.8 (Version: 6.0.0)

[mysqld(API)] 3 node(s)

id=5 @192.168.1.50 (Version: 6.0.0) id=6 @192.168.1.8 (Version: 6.0.0) id=7 (not connected, accepting connect from any host)

测试:

在SerA 中

[root@SerA ~]# mysql -uroot

>create databases backup;

>use backup;

>create table dog (name varchar(10)); >create table pig (name varchar(10)); 退出终端, 使用下面的命令往上面两个表内批量插入数据.

[root@SerA ~]# mysql -uroot<sql.txt [root@SerB ~]# mysql -uroot<sqltest.txt 这里要等上几分钟, 应为sql.txt 里有20W行记录, 而且sqltest.txt也用10W行. 数据插入完毕再回到终端中检查是否有新增的数据库和表以及数据. [root@SerB ~]# mysql -uroot

>show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| backup |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use backup

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+------------------+

| Tables_in_backup |

+------------------+

| dog |

| pig |

+------------------+

2 rows in set (0.00 sec)

mysql> select * from pig;

...............

| 144163 |

| 173821 |

| 188584 |

| 45860 |

+--------+

200000 rows in set (1.66 sec)

mysql> select * from pig;

...............

| 27580 |

| 83268 |

| 47744 |

| 97018 |

+--------+

100000 rows in set (0.83 sec)

可以看到mysql能正常工作.

ndb下数据备份和恢复:

备份很简单:

在任意的一台机子上,只需通过ndb_mgm，运行start backup

[root@SerB zman]# ndb_mgm

-- NDB Cluster -- Management Client --

ndb_mgm> start backup

Connected to Management Server at: 192.168.1.50:1186 Waiting for completed, this may take several minutes Node 3: Backup 1 started from node 1

Node 3: Backup 1 started from node 1 completed StartGCP: 515 StopGCP: 518

#Records: 302059 #LogRecords: 0

Data: 8427304 bytes Log: 0 bytes

这个备份很快，备份的结果是在每个数据节点上都生成一个备份. 用ll命令分别在两台

机子上查看是否生成备份文件.

[root@SerA mysql-cluster]# ll /var/lib/mysql-cluster/BACKUP/BACKUP-1/

total 4116

-rw-r--r-- 1 root root 4194172 Aug 22 02:16 BACKUP-1-0.3.Data -rw-r--r-- 1 root root 8580 Aug 22 02:16 BACKUP-1.3.ctl -rw-r--r-- 1 root root 44 Aug 22 02:16 BACKUP-1.3.log [root@SerB conf]# ll /var/lib/mysql-cluster/BACKUP/BACKUP-1/ 总计 4156

-rw-r--r-- 1 root root 4233956 08-22 14:15 BACKUP-1-0.4.Data -rw-r--r-- 1 root root 8580 08-22 14:15 BACKUP-1.4.ctl -rw-r--r-- 1 root root 44 08-22 14:15 BACKUP-1.4.log 恢复

恢复要通过以下几个步骤完成:

1. 测试删除数据表，至少1个节点重新建个空的;

2. 停止sql节点的运行，或者在配置文件中增加1个空的sql节点标志(config.ini文件里

多出的一个[mysqld]项就是留个这里用的)，否则会出现 No free node id found for

mysqld(API) 错误;

3. 首先在任意一个节点上恢复表结构，然后在每个数据节点上恢复数据

ndb_restore -n 4 -b 1 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-1/

没有-m 这个恢复过程，会出现Unable to find table错误

-n 和 -b 的数值，对应备份文件 BACKUP-n.b.ctl，这里是 BACKUP-1.4.ctl

ndb_restore -n 3 -b 1 -r /var/lib/mysql-cluster/BACKUP/BACKUP-1/ 先关闭集群.

[root@SerA ~]# mysqladmin shutdown

[root@SerB ~]# mysqladmin shutdown

[root@SerA ~]# ndb_mgm -e shutdown

Connected to Management Server at: 192.168.1.50:1186 2 NDB Cluster node(s) have shutdown.

Disconnecting to allow management server to shutdown. 重复上面的四,六 (主要是想通过 ndbd --ndb_nodeid=3 --initial 和 ndbd

--ndb_nodeid=4 --initial 將数据库初始化)

[root@SerA ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2

Server version: 6.0.0-alpha Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use backup

Database changed

mysql> show tables; (可以看到数据库是空的)

Empty set (0.00 sec)

进行恢复

在SerB上执行:

[root@SerB ~]# ndb_restore -n 4 -b 1 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-1

Nodeid = 4

Backup Id = 1

backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1 Ndb version in backup files: Version 6.0.0

Connected to ndb!!

Successfully restored table backup/def/pig

Successfully restored table event REPL$backup/pig Successfully restored table backup/def/dog

Successfully restored table event REPL$backup/dog _____________________________________________________ Processing data in table: backup/def/pig(6) fragment 1 _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1 _____________________________________________________

Processing data in table: sys/def/SYSTAB_0(0) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(4) fragment 1 _____________________________________________________ Processing data in table: backup/def/dog(5) fragment 1 Restored 150727 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

在SerA上执行:

[root@SerA ~]# ndb_restore -n 3 -b 1 -r /var/lib/mysql-cluster/BACKUP/BACKUP-1

Nodeid = 3

Backup Id = 1

backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1 Ndb version in backup files: Version 6.0.0

Connected to ndb!!

_____________________________________________________ Processing data in table: backup/def/pig(6) fragment 0 _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(0) fragment 0 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(2) fragment 0 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(4) fragment 0 _____________________________________________________ Processing data in table: backup/def/dog(5) fragment 0 Restored 149273 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

回到终端里查看恢复情况

[root@SerA ~]# mysql -uroot

mysql> use backup

mysql> show tables;

+------------------+

| Tables_in_backup |

+------------------+

| dog |

| pig |

+------------------+

2 rows in set (0.00 sec)

mysql> select * from pig;

...............

| 144163 |

| 173821 |

| 188584 |

| 45860 |

+--------+

200000 rows in set (1.66 sec) mysql> select * from pig;

...............

| 27580 |

| 83268 |

| 47744 |

| 97018 |

+--------+

100000 rows in set (0.83 sec) OK, 数据库恢复成功.

cluster目前，只能对所有数据进行备份和恢复，不能选择数据库，也不能进行差量备份，不知如果对应上G的数据会怎样，目前想到的是打开log-bin手工进行差量数据恢复。

13.文件结构

1)日志文件

Mysql提供了一下几组日志，用来帮助你找出mysqld 内部出现的原因

错误日志(The error log):记录了数据库启动、运行以及停止过程中错误信息; 1.

2.ISAM操作日志(The isam log):记录了所有对ISAM表的修改，该日志仅仅用于

调试ISAM模式;

3.SQL执行日志(The query log):记录了客户端的连接以及所执行的SQL语句;

4.更新日志(The update log):记录了改变数据的语句，已经不建议使用，由二进制

日志替代;

5.二进制日志(The binary log):记录了所有对数据库数据的修改语句;

6.超时日志(The slow log):记录所有执行时间超过最大SQL执行时(long_query_time)

或未使用索引的语句;

错误日志(err-log)

错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何

严重错误时的相关信息。如果mysqld莫名其妙地死掉并且mysqld_safe需要重新启

动它，mysqld_safe在错误日志中写入一条restarted mysqld消息。如果mysqld注意

到需要自动检查或着修复一个表，则错误日志中写入一条消息。

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。

通用查询日志(query-log)

如果你想要知道mysqld内部发生了什么，你应该用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值，默认名是host_name.log。所有连接和语句被记录到日志文件。当你怀疑在客户端发生了错误并想确切地知道该客户端发送给mysqld的语句时，该日志可能非常有用。

mysqld按照它接收的顺序记录语句到查询日志。这可能与执行的顺序不同。这与更新日志和二进制日志不同，它们在查询执行后，但是任何一个锁释放之前记录日志。(查询日志还包含所有语句，而二进制日志不包含只查询数据的语句)。服务器重新启动和日志刷新不会产生新的一般查询日志文件(尽管刷新关闭并重新打开一般查询日志文件)。在Unix中，你可以通过下面的命令重新命名文件并创建一个新文件:

shell> mv hostname.log hostname-old.log shell> mysqladmin flush-logs

shell> cp hostname-old.log to-backup-directory

shell> rm hostname-old.log

更新日志(The update log)

当你在执行 mysqld时，如果有加上 --log-update[=file_name]选项的话， mysqld会将所有关于更新数据的 SQL commands写入纪录文件中。在没有指定 file_name的情况下，默认会用主机名当作纪录文件的文件名。如果你指定的文件名不包含完整路径的话，纪录文件会放置在数据目录中。如果文件名没有指定扩展名的话， mysqld会以 "file_name.###"作为纪录文件的文件名，其中 ###是阿拉伯数字，每当下列状况发生时，这个数字就会加一。

1. 执行 mysqladmin refresh

2. 执行 mysqladmin flush-logs

3. 执行 FLUSH LOGS sql指令

4. 重新启动 mysqld

update logging还有一项特点就是它只会纪录 "真正"改变数据的动作。因此，带有 where选项的 updata或 delete指令如果没有更动任何数据的话，这个动作就不会被写入纪录文件。甚至在 updata指令没有变更域值的情况下，该指令也不会被纪录下来。

update logging执行的时机是介于 sql指令结束以及 lock解开以前。这样可以确保所有的变动纪录是依照实际执行的顺序写入纪录文件中。

你可以执行下列指令来用 update log file更新数据库。 (假设你的 update log files

的档名格式为 file_name.###)

shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls这个指令加上 -1 -t -r三个选项后，便能按时间顺序排列所有的纪录文件。

二进制日志(Bin-log)

这个不需要介绍了吧。它记录所有对数据操作语句。

官方的说法是:二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如，

没有匹配任何行的一个DELETE)的所有语句。语句以―事件‖的形式保存，它描述

数据更改。

开启BIN-log对服务器的影响:运行服务器时若启用二进制日志则性能大约慢1%。

但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。

由此可见，二进制日志的主要作用是在恢复使能够最大可能地更新数据库，因为二

进制日志包含备份后进行的所有更新。

二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句

慢查询日志(slow-queries -log)

慢查询日志记录所有执行时间超过long-query-time秒的SQL语句，但获得初始表

锁定的时间不算做执行时间，这也就意味着慢查询日志里不能查出你的SQL语句

是否造成了表锁定。

慢查询日志可以用来找到执行时间长的语句，对其进行优化。也就是说，在数据库

应用层的优化需要使用到慢查询日志

日志的查看很简单，大部分都是文本，直接用vim、less、more之类的工具看就可

以了，值得说明的是二进制文件的查看:

1)首先确定是否开启了二进制文件记录功能

mysql>show variables like 'log_bin';

2)如果你想知道现在记录二进制数据的文件具体信息，你可以通过下列语句看到现

在正在记录哪个文件，以及记录的当前位置:

mysql>show master status;

3)查看二进制数据需要借助程序mysqlbinlog，看看它支持哪些选项，根据自己需

要来使用。

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040;

查询某个时间范围的可以执行下列语句，如果记录很多可以将结果定向到一个文件

里自己慢慢看:-) :

mysql>mysqlbinlog --start-datetime='2008-01-01 00:00:00'

--stop-datetime='2008-08-08 00:00:00' /var/log/mysql/mysql-bin.000040 > ./tmp.log

2)参数文件

Windows下安装目录下的my.ini文件

Linux下的my.cnf文件

3)数据文件

MyISAM表:有三个相关的文件，数据文件.MYD，索引文件.MYI，目标文件(定

义文件).frm

BDB表:包含两个文件，一个.db数据文件和一个.frm目标文件

InnoDB表:有自己的目标文件.frm，但实际的数据存放在上一级目录中，与数据

库属于同一级目录

4)密码文件

没有专门的密码文件，用户名和密码的存放在参数文件中

5)监听文件

没有专门的监听文件，监听的配置在参数文件中

14.审计

mysql本身并没有操作审计的功能，但可以通过一种简单的方式实现。其实mysql本身已经提供了详细的sql执行记录–general log ，但是开启它有以下几个缺点: 无论sql有无语法错误，只要执行了就会记录，导致记录大量无用信息，后期的筛选有难度。

sql并发量很大时，log的记录会对io造成一定的印象，是数据库效率降低。日志文件很容易快速膨胀，不妥善处理会对磁盘空间造成一定影响。

这里考虑使用init-connect + binlog的方法进行mysql的操作审计。

由于mysql binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id，但是却没有记录connection_id对应的详细用户信息。因此本文将通过init-connect，在每次连接的初始化阶段，记录下这个连接的用户，和connection_id信息。在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id 结合之前连接日志记录进行分析，得出最后的结论。

1) 设置init-connect

创建用于存放连接日志的数据库和表

create database accesslog;

drop table if exists accesslog.accesslog; CREATE TABLE accesslog.`accesslog` ( `id` int(11) NOT NULL,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP,

`localname` varchar(30) DEFAULT NULL, `matchname` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`,time)

) ENGINE=InnoDB DEFAULT CHARSET=utf8; 创建用户权限

可用现成的root用户用于信息的读取,

grant read on accesslog.* to root@localhost identified by password‘;

如果存在具有to *.* 权限的用户需要进行限制。

设置init-connect

在[mysqld]下添加以下设置:

init-connect='insert into accesslog.accesslog(id,localname,matchname)

values(connection_id(),user(),current_user());'

log-bin

重启数据库生效

shell> service mysqld restart;

2) 记录追踪

thread_id确认

假设想知道在2009年11月25日，上午9点多的时候，是谁吧test.dummy这个表给删了。可以用以下语句定位

mysqlbinlog –-start-datetime=‘2009-11-25 09:00:00‘ –-stop-datetime=‘2009-11-25

09:00:00‘ binlog.xxxx | grep dummy‘ -B 5

会得到如下结果(可见thread_id为5):

# at 300777

#091124 16:54:00 server id 10 end_log_pos 301396 Query thread_id=5

exec_time=0 error_code=0

SET TIMESTAMP=1259052840;

drop table test.dummy;

用户确认

thread_id 确认以后，找到元凶就只是一条sql语句的问题了。

select * from accesslog.`accesslog` limit 2 ;

就能发现是testuser2@localhost干的了。

理论上，只会在用户每次连接时往数据库里插入一条记录，不会对数据库产生很大影响。

除非连接频率非常高(当然，这个时候需要注意的就是如何进行连接复用和控制，而非

是不是要用这种方法的问题了)。

由于是一个log系统，推荐使用archive存储引擎，有利于数据厄压缩存放。

如果数据库连接数量很大的话，建议一定时间做一次数据导出，然后清表。

access-log表当然不只用于审计，当然也可以用于对于数据库连接的情况进行数据分析，

例如每日连接数分布图等等，但init-connect 是不会在super用户登录时执行的。

可以用下面的语句移除super权限，

REVOKE SUPER ON *.* FROM 'admin'@'%';

15.导入、导出文件

数据导出

数据导出主要有以下几种方法:

使用select into outfile "filename"语句，例如:

mysql> select * from driver into outfile "a.txt";

使用mysqldump实用程序

使用phpmyadmin导出

导入

同导出相类似，导入也有四种方法:

使用LOAD DATA INFILE "filename"命令

使用mysqlimport实用程序

使用phpmyadmin导入

使用sql文件

mysqldump使用方法如下:

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

可参看 MySQL数据的导出和导入工具:mysqldump

mysqlimport

1)mysqlimport的语法介绍:

mysqlimport位于mysql/bin目录中，是mysql的一个载入(或者说导入)数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可供选择。这个工具把一个文本文件(text file)导入到你指定的数据库和表中。比方说我们要从文件Customers.txt中把数据导入到数据库Meet_A_Geek中的表Custermers中:

mysqlimport Meet_A_Geek Customers.txt

注意:这里Customers.txt是我们要导入数据的文本文件，而Meet_A_Geek是我们要操作的数据库，

数据库中的表名是Customers，这里文本文件的数据格式必须与Customers表中的记录格式一致，否则mysqlimport命令将会出错。其中表的名字是导入文件的第一个句号(.)前面文件字符串，另外一个例子:

mysqlimport Meet_A_Geek Cus.to.mers.txt

那么我们将把文件中的内容导入到数据库Meet_A_Geek 中的Cus表中。

上面的例子中，都只用到两个参数，并没有用到更多的选项，下面介绍mysqlimport的选项

2)mysqlimport的常用选项介绍:

选项功能

-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息

-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据

-i or --ignore mysqlimport跳过或者忽略那些有相同唯一关键字的行，导入文件中的数据将被忽略。

-l or -lock-tables 数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。

-r or -replace 这个选项与,i选项的作用相反;此选项将替代表中有相同唯一关键字的记录。

--fields-enclosed- by= char

指定文本文件中数据的记录时以什么括起的，很多情况下数据以双引号括起。默认的情况下数据是没有被字符括起的。

--fields-terminated- by=char

指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。您可以用此选项指定数据之间的分隔符。默认的分隔符是跳格符(Tab)

--lines-terminated- by=str

此选项指定文本文件中行与行之间数据的分隔字符串或者字符。默认的情况下mysqlimport以newline为行分隔符。您可以选择用一个字符串来替代一个单个的字符: 一个新行或者一个回车。

mysqlimport命令常用的选项还有-v 显示版本(version)， -p 提示输入密码(password)等。

3)例子:导入一个以逗号为分隔符的文件

文件中行的记录格式是这样的:

"1", "ORD89876", "1 Dozen Roses", "19991226"

我们的任务是要把这个文件里面的数据导入到数据库Meet_A_Geek中的表格Orders中，我们使用这个命令:

bin/mysqlimport –prl –fields-enclosed-by=" –fields-terminated-by=, Meet_A_Geek Orders.txt

这个命令可能看起来很不爽，不过当你熟悉了之后，这是非常简单的。第一部分， bin/mysqlimport ,告诉操作系统你要运行的命令是mysql/bin目录下的mysqlimport，选项p是要求输入密码，这样就要求你在改动数据库之前输入密码，操作起来会更安全。我们用了r选项是因为我们想要把表中的唯一关键字与文件记录中有重复唯一关键字的记录替换成文件中的数据。我们表单中的数据不是最新的，需要用文件中的数据去更新，因而就用r这个选项，替代数据库中已经有的记录。l选项的作用是在我们插入数据的时候锁住表，这样就阻止了用户在我们更新表的时候对表进行查询或者更改的操作。

采用批处理方式

批处理是一种非交互式运行mysql程序的方法，如同您在mysql中使用的命令一样，你

仍然将使用这些命令。为了实现批处理，您重定向一个文件到 mysql程序中，首先我

们需要一个文本文件，这个文本文件包含有与我们在mysql中输入的命令相同的文本。

比如我们要插入一些数据，使用包含下面文本的文件(文件名为New_Data.sql,当然我们

也可以取名为New_Data.txt及任何其他的合法名字，并不一定要以后缀sql结尾):

USE Meet_A_Geek;

INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Block");

INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Newton");

INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Simmons");

注意上面的这些句子的语法都必须是正确的，并且每个句子以分号结束。上面的USE

命令选择数据库，INSERT命令插入数据。

下面我们要把上面的文件导入到数据库中，导入之前要确认数据库已经在运行，即是

mysqld进程(或者说服务，Windows NT下面称为‖服务―，unix下面为‖进程―)已经在

运行。然后运行下面的命令:

bin/mysql –p < /home/mark/New_Data.sql

接着按提示输入密码，如果上面的文件中的语句没有错误，那么这些数据就被导入到了数据库中。

命令行中使用LOAD DATA INFILE 从文件中导入数据到数据库:

现在您可能会问自己，"究竟为什么我要输入所有的这些SQL语句到文件中，然后通过程序运行它们呢,‖这样看起来好像需要大量的工作。很好，你这样想很可能就对了。但是假如你有从所有这些命令中产生的log记录呢,现在这样就很棒，嗯，大多数数据库都会自动产生数据库中的事件记录的log。而大部分log 都包含有用过的原始的SQL命令。因此，如果您不能从您现在的数据库中导出数据到新的mysql数据库中使用，那么您可以使用log和mysql的批处理特性，来快速且方便地导入您地数据。当然，这样就省去了打字的麻烦。

LOAD DATA INFILE

这是我们要介绍的最后一个导入数据到MySQL数据库中的方法。这个命令与mysqlimport非常相似，但这个方法可以在mysql命令行中使用。也就是说您可以在所有使用API的程序中使用这个命令。使用这种方法，您就可以在应用程序中导入您想要导入的数据。

使用这个命令之前，mysqld进程(服务)必须已经在运行。启动mysql命令行: bin/mysql –p

按提示输入密码，成功进入mysql命令行之后，输入下面的命令:

USE Meet_A_Geek;

LOAD DATA INFILE "/home/mark/data.sql" INTO TABLE Orders;

简单的讲，这样将会把文件data.sql中的内容导入到表Orders中，如mysqlimport工具一样，这个命令也有一些可以选择的参数。比如您需要把自己的电脑上的数据导入到远程的数据库服务器中，您可以使用下面的命令:

LOAD DATA LOCAL INFILE "C:MyDocsSQL.txt" INTO TABLE Orders;

上面的LOCAL参数表示文件是本地的文件，服务器是您所登陆的服务器。这样就省去了使用ftp来上传文件到服务器，MySQL替你完成了.

您也可以设置插入语句的优先级，如果您要把它标记为低优先级(LOW_PRIORITY)，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令:

LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" INTO TABLE Orders;

您也可以指定是否在插入数据的时候，取代或者忽略文件与数据表中重复的键值。替代重复的键值的语法:

LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" REPLACE INTO TABLE

Orders;

上面的句子看起来有点笨拙，但却把关键字放在了让您的剖析器可以理解的地方。下面的一对选项描述了文件的记录格式，这些选项也是在mysqlimport工具中可以用的。他们在这里看起来有点不同。首先，要用到FIELDS关键字，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项:

TERMINATED BY character

ENCLOSED BY character

ESCAPED BY character

这些关键字与它们的参数跟mysqlimport中的用法是一样的. The TERMINATED BY 描述字段的分隔符，默认情况下是tab字符( ) ENCLOSED BY描述的是字段的括起字符。比方以引号括起每一个字段。

ESCAPED BY 描述的转义字符。默认的是反些杠(backslash: ).

下面仍然使用前面的mysqlimport命令的例子，用LOAD DATA INFILE语句把同样的文件导入到数据库中:

LOAD DATA INFILE "/home/mark/Orders.txt" REPLACE INTO TABLE Orders FIELDS

TERMINATED BY ','

ENCLOSED BY '"';

LOAD DATA INFILE语句中有一个mysqlimport工具中没有特点:LOAD DATA INFILE 可以按指定的列把文件导入到数据库中。

当我们要把数据的一部分内容导入的时候，这个特点就很重要。比方说，我们要从Access数据库升级到MySQL数据库的时候，需要加入一些栏目(列/字段 /field)到MySQL数据库中，以适应一些额外的需要。这个时候，我们的Access数据库中的数据仍然是可用的，但是因为这些数据的栏目 (field)与MySQL中的不再匹配，因此而无法再使用mysqlimport工具。尽管如此，我们仍然可以使用LOAD DATA INFILE，下面的例子显示了如何向指定的栏目(field)中导入数据:

LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date,

Customer_ID);

如您所见，我们可以指定需要的栏目(fields)。这些指定的字段依然是以括号括起，由逗号分隔的，如果您遗漏了其中任何一个，MySQL将会提醒您

16.基本查询

1)lock

LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

UNLOCK TABLES

LOCK TABLES可以锁定用于当前线程的表。如果表被其它线程锁定，则造成堵塞，直到可以获取所有锁定为止。UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。

表锁定只用于防止其它客户端进行不正当地读取和写入。保持锁定(即使是读取锁定)的客户端可以进行表层级的操作，比如DROP TABLE。

注意，下面是对事务表使用LOCK TABLES的说明:

在尝试锁定表之前，LOCK TABLES不是事务安全型的，会隐含地提交所有活性事务。同时，开始一项事务(例如，使用START TRANSACTION)，会隐含地执行UNLOCK TABLES

对事务表(如InnoDB)使用LOCK TABLES的正确方法是，设置AUTOCOMMIT=0并且不能调用UNLOCK TABLES，直到您明确地提交事务为止。当您调用LOCK TABLES时，InnoDB会内部地取其自己的表锁定，MySQL取其自己的表锁定。InnoDB在下一个提交时释放其表锁定，但是，对于MySQL，要释放表锁定，您必须调用UNLOCK TABLES。您不应该让AUTOCOMMIT=1，因为那样的话，InnoDB会在调用LOCK TABLES之后立刻释放表锁定，并且很容易形成死锁定。注意，如果

AUTOCOMMIT=1，我们根本不能获取InnoDB表锁定，这样就可以帮助旧的应用软件避免不必要的死锁定。

ROLLBACK不会释放MySQL的非事务表锁定。

要使用LOCK TABLES，您必须拥有相关表的LOCK TABLES权限和SELECT权限。使用LOCK TABLES的主要原因是仿效事务，或在更新表时加快速度。这将在后面进行更详细的解释。

如果一个线程获得对一个表地READ锁定，该线程(和所有其它线程)只能从该表中读取。如果一个线程获得对一个表的WRITE锁定，只有保持锁定的线程可以对表进行写入。其它的线程被阻止，直到锁定被释放时为止。

READ LOCAL和READ之间的区别是，READ LOCAL允许在锁定被保持时，执行非冲突性INSERT语句(同时插入)。但是，如果您正打算在MySQL外面操作数据库文件，同时您保持锁定，则不能使用READ LOCAL。对于InnoDB表，READ LOCAL与READ相同。

当您使用LOCK TABLES时，您必须锁定您打算在查询中使用的所有的表。虽然使用LOCK TABLES语句获得的锁定仍然有效，但是您不能访问没有被此语句锁定的任何的表。同时，您不能在一次查询中多次使用一个已锁定的表——使用别名代替，在此情况下，您必须分别获得对每个别名的锁定。

mysql> LOCK TABLE t WRITE, t AS t1 WRITE; mysql> INSERT INTO t SELECT * FROM t; ERROR 1100: Table 't' was not locked with LOCK TABLES

mysql> INSERT INTO t SELECT * FROM t AS t1; 如果您的查询使用一个别名引用一个表，那么您必须使用同样的别名锁定该表。如果没有指定别名，则不会锁定该表。

mysql> LOCK TABLE t READ;

mysql> SELECT * FROM t AS myalias; ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反的，如果您使用一个别名锁定一个表，您必须使用该别名在您的查询中引用该表。 mysql> LOCK TABLE t AS myalias READ; mysql> SELECT * FROM t;

ERROR 1100: Table 't' was not locked with LOCK TABLES

mysql> SELECT * FROM t AS myalias; WRITE锁定通常比READ锁定拥有更高的优先权，以确保更新被尽快地处理。这意味着，如果一个线程获得了一个READ锁定，则另一个线程会申请一个WRITE锁定，后续的READ锁定申请会等待，直到WRITE线程获得锁定并释放锁定。您可以使用LOW_PRIORITY WRITE锁定来允许其它线程在该线程正在等待WRITE锁定时获得READ锁定。只有当您确定最终将有一个时机，此时没有线程拥有READ锁定时，您才应该使用LOW_PRIORITY WRITE锁定。

LOCK TABLES按照如下方式执行:

按照内部定义的顺序，对所有要被锁定的表进行分类。从用户的角度，此顺序是未经定义的。

如果使用一个读取和一个写入锁定对一个表进行锁定，则把写入锁定放在读取锁定之前。

一次锁定一个表，直到线程得到所有锁定为止。

该规则确保表锁定不会出现死锁定。但是，对于该规则，您需要注意其它的事情:

如果您正在对一个表使用一个LOW_PRIORITY WRITE锁定，这只意味着，MySQL等待特定的锁定，直到没有申请READ锁定的线程时为止。当线程已经获得WRITE锁定，并正在等待得到锁定表清单中的用于下一个表的锁定时，所有其它线程会等待WRITE锁定被释放。如果这成为对于应用程序的严重的问题，则您应该考虑把部分表转化为事务安全型表。

您可以安全地使用KILL来结束一个正在等待表锁定的线程。

注意，您不能使用INSERT DELAYED锁定任何您正在使用的表，因为，在这种情况下，INSERT由另一个线程执行。

通常，您不需要锁定表，因为所有的单个UPDATE语句都是原子性的;没有其它的线程可以干扰任何其它当前正在执行的SQL语句。但是，在几种情况下，锁定表会有好处:

如果您正在对一组MyISAM表运行许多操作，锁定您正在使用的表，可以快很多。锁定MyISAM表可以加快插入、更新或删除的速度。不利方面是，没有线程可以更新一个用READ锁定的表(包括保持锁定的表)，也没有线程可以访问用WRITE锁定的表(除了保持锁定的表以外)。

有些MyISAM操作在LOCK TABLES之下更快的原因是，MySQL不会清空用于已锁定表的关键缓存，直到UNLOCK TABLE被调用为止。通常，关键缓存在每个SQL语句之后被清空。

如果您正在使用MySQL中的一个不支持事务的存储引擎，则如果您想要确定在SELECT和UPDATE之间没有其它线程，您必须使用LOCK TABLES。本处所示的例子要求LOCK TABLES，以便安全地执行:

mysql> LOCK TABLES trans READ, customer WRITE;

mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;

mysql> UPDATE customer

-> SET total_value=sum_from_previous_statement

-> WHERE customer_id=some_id;

mysql> UNLOCK TABLES; 如果没有LOCK TABLES，有可能另一个线程会在执行SELECT和UPDATE语句之间在trans表中插入一个新行。

通过使用相对更新(UPDATE customer SET value=value+new_value)或

LAST_INSERT_ID()函数，您可以在许多情况下避免使用LOCK TABLES。通过使用用户层级的顾问式锁定函数GET_LOCK()和RELEASE_LOCK()，您也可以在有些情况下避免锁定表。这些锁定被保存在服务器中的一个混编表中，使用pthread_mutex_lock() 和pthread_mutex_unlock()，以加快速度。

要了解更多有关锁定规则的说明

您可以使用FLUSH TABLES WITH READ LOCK语句锁定位于所有带有读取锁定的数据库中的所有表。如果您有一个可以及时拍摄快照的文件系统，比如Veritas，这是获得备份的一个非常方便的方式。

注释:如果您对一个已锁定的表使用ALTER TABLE，该表可能会解锁。

2)Index

索引是加速表内容访问的主要手段，特别对涉及多个表的连接的查询更是如此, MySQL对构造索引提供了很大的灵活性。可以规定索引能否包含重复的值。如果不包含，则索引应该创建为PRIMARY KEY 或UNIQUE 索引。对于单列惟一索引，这保证了列不

包含重复的值。对于多列惟一索引，它保证值的组合不重复。

创建索引

在执行CREATE TABLE 语句时，可为新表创建索引，也可以用CREATE INDEX 或ALTER TABLE 来为一个已有的表增加索引。ALTER TABLE 最常用，因为可用它来创建普通索引、UNIQUE 索引或PRIMARY KEY 索引，如:

ALTER TABLE tal_name ADD INDEX index_name(column_list)

ALTER TABLE tal_name ADD UNIQUE index_name(column_list)

ALTER TABLE tal_name ADD PRIMARY KEY(column_list)

其中tbl_name 是要增加索引的表名，而column_list 指出对哪些列进行索引。如果索引由不止一列组成，各列名之间用逗号分隔。索引名index_name 是可选的，因此可以不写它，MySQL将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。

CREATE INDEX 可对表增加普通索引或UNIQUE 索引，如:

CREATE UNIQUE INDEX index_name ON tbl_name(column_list)

CREATE INDEX index_name ON tbl_name(column_list)

tbl _ name、index_name 和column_list 具有与ALTER TABLE 语句中相同的含义。这里索引名不可选。不能用CREATE INDEX 语句创建PRIMARY KEY 索引。要想在发布CREATE TABLE 语句时为新表创建索引，所使用的语法类似于ALTER TABLE 语句的语法，但是应该在您定义表列的语句部分指定索引创建子句，如下所示: CREATE TABLE tbl_name

(

….

INDEX index_name(column_list),

UNIQUE index_name(column_list),

PRIMARY KEY(column_list),

…..

)

如果对某个串列的前缀进行索引(列值的最左边n 个字符)，应用column_list 说明符表示该列的语法为col_name(n) 而不用c o l _ name。例如，下面第一条语句创建了一个具有两个CHAR 列的表和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引:

CREATE TABLE my_tbl

(

name CHAR(30),

address CHAR(60),

INDEX(name,address)

)

CREATE TABLE my_tbl

(

name CHAR(30),

address CHAR(60),

INDEX(name(10),address(20))

)

删除索引

可利用DROP INDEX 或ALTER TABLE 语句来删除索引。类似于CREATE INDEX 语句，DROP INDEX 通常在内部作为一条ALTER TABLE 语句处理，并且DROP INDEX 是在MySQL3.22 中引入的。删除索引语句的语法如下:

DROP INDEX index_name ON tbl_name ALTER TABLE tbl_name DROP INDEX index_name ALTER TABLE tbl_name DROP PRIMARY KEY 前两条语句是等价的。第三条语句只在删除PRIMARY KEY 索引时使用;在此情形中，不需要索引名，因为一个表只可能具有一个这样的索引。如果没有明确地创建作为PRIMARY KEY 的索引，但该表具有一个或多个UNIQUE 索引，则MySQL将删除这些UNIQUE 索引中的第一个。

如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

3)constraint

约束(Constraint)是Microsoft SQL Server 提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件(有关数据完整性的介绍请参见第9 章)。在SQL Server 中有5 种约束:主关键字约束 (Primary Key Constraint)、外关键字约束(Foreign Key Constraint)、惟一性约束 (Unique Constraint)、检查约束(Check Constraint)和缺省约束(Default Constraint)。

主关键字约束

主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。每个表中只能有一列被指定为主关键字，且IMAGE 和 TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。

定义主关键字约束的语法如下:

CONSTRAINT constraint_name

PRIMARY KEY [CLUSTERED | NONCLUSTERED] (column_name1[, column_name2,„,column_name16])

各参数说明如下:

constraint_name

指定约束的名称约束的名称。在数据库中应是惟一的。如果不指定，则系统会自动生成一个约束名。

CLUSTERED | NONCLUSTERED

指定索引类别，CLUSTERED 为缺省值。其具体信息请参见下一章。 column_name

指定组成主关键字的列名。主关键字最多由16 个列组成。

例: 创建一个产品信息表，以产品编号和名称为主关键字

create table products (

p_id char(8) not null,

p_name char(10) not null ,

price money default 0.01 ,

quantity smallint null ,

constraint pk_p_id primary key (p_id, p_name)

) on [primary]

外关键字约束

外关键字约束定义了表之间的关系。当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字，并设定它适合哪个表中哪些列相关联。这样，当在定义主关键字约束的表中更新列值，时其它表中有与之相关联的外关键字约束的表中的外关键字列也将被相应地做相同的更新。外关键字约束的作用还体现在，当向含有外关键字的表插入数据时，如果与之相关联的表的列中无与插入的外关键字列值相同的值时，系统会拒绝插入数据。与主关键字相同，不能使用一个定义为 TEXT 或IMAGE 数据类型的列创建外关键字。外关键字最多由16 个列组成。

定义外关键字约束的语法如下:

CONSTRAINT constraint_name

FOREIGN KEY (column_name1[, column_name2,„,column_name16]) REFERENCES ref_table [ (ref_column1[,ref_column2,„, ref_column16] )] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] ] [ NOT FOR REPLICATION ]

各参数说明如下:

REFERENCES

指定要建立关联的表的信息。

ref_table

指定要建立关联的表的名称。

ref_column

指定要建立关联的表中的相关列的名称。

ON DELETE {CASCADE | NO ACTION} 指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表数据行时会将子表中对应的数据行删除;如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的删除操作回滚。 NO ACTION 是缺省值。

ON UPDATE {CASCADE | NO ACTION} 指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在更新父表数据行时会将子表中对应的数据行更新;如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的更新操作回滚。 NO ACTION 是缺省值。

NOT FOR REPLICATION

指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。例:创建一个订货表，与前面创建的产品表相关联

create table orders(

order_id char(8),

p_id char(8),

p_name char(10) ,

constraint pk_order_id primary key (order_id) ,

foreign key(p_id, p_name) references products(p_id, p_name)

) on [primary]

注意:临时表不能指定外关键字约束。

惟一性约束

惟一性约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值。惟一性约束指定的列可以有NULL 属性。由于主关键字值是具有惟一性的，因此主关键字列不能再设定惟一性约束。惟一性约束最多由16 个列组成。

定义惟一性约束的语法如下:

CONSTRAINT constraint_name

UNIQUE [CLUSTERED | NONCLUSTERED] (column_name1[, column_name2,„,column_name16])

例:定义一个员工信息表，其中员工的身份证号具有惟一性。

create table employees (

emp_id char(8),

emp_name char(10) ,

emp_cardid char(18),

constraint pk_emp_id primary key (emp_id), constraint uk_emp_cardid unique (emp_cardid) ) on [primary]

检查约束

检查约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。可以对每个列设置符合检查。

定义检查约束的语法如下:

CONSTRAINT constraint_name

CHECK [NOT FOR REPLICATION]

(logical_expression)

各参数说明如下:

NOT FOR REPLICATION

指定检查约束在把从其它表中复制的数据插入到表中时不发生作用。 logical_expression

指定逻辑条件表达式返回值为TRUE 或FALSE。

例: 创建一个订货表其中定货量必须不小于10。

create table orders(

order_id char(8),

p_id char(8),

p_name char(10) ,

quantity smallint,

constraint pk_order_id primary key (order_id),

constraint chk_quantity check (quantity>=10) ,

) on [primary]

注意:对计算列不能作除检查约束外的任何约束。

缺省约束

缺省约束通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。SQL Server 推荐使用缺省约束，而不使用定义缺省值的方式来指定列的缺省值。有关绑定缺省约束的方法请参见“数据完整性”章节。

定义缺省约束的语法如下:

CONSTRAINT constraint_name

DEFAULT constant_expression [FOR column_name]

例:

constraint de_order_quantity default 100 for order_quantity

注意:不能在创建表时定义缺省约束，只能向已经创建好的表中添加缺省约束。列约束和表约束

对于数据库来说，约束又分为列约束(Column Constraint)和表约束(Table Constraint)。列约束作为列定义的一部分只作用于此列本身。表约束作为表定义的一部分，可以作用于多个列。

下面举例说明列约束与表约束的区别。

例:

create table products (

p_id char(8) ,

p_name char(10) ,

price money default 0.01 ,

quantity smallint check (quantity>=10) , /* 列约束 */

constraint pk_p_id primary key (p_id, p_name) /* 表约束 */

试论建设方对弱电工程的管理
论工程项目投标报价的策略与技巧
时评：城市规划沦为“纸上画画”
滕奇龙：室内设计最重要的是用心感受
试探水利工程建设项目审批制的执行
一级建造师专业实务《机电工程》精华辅导资料（4）
建筑工程：制约代建制企业发展的问题
合同与招标基础知识《法律概述》

信息发布：名易软件http://www.myidp.net