静怡家园

MySQL易学易用，且附带丰富的技术文档，这二个因素使之被广泛应用。然而，随着MySQL发展之迅速，即使一个MySQL老手有时也会为该软件出其不意的功能感叹。本文将为你介绍这些不为人知的特性。

以XML格式查看查询结果

通过使用传统—xml 选项调用MySQL命令行客户程序，你可以以XML格式(而不是传统的列表形式)来查看MySQL查询结果。如果你打算将查询输出与其它程序集成在一起，这一技巧非常有用，这里是一个例子：

表A

shell> mysql --xml

mysql> Sel�ct * FROM test.stories;


This is a test
2005-07-28 00:4:57

2
This is the second test
2005-07-28 00:5:


2 rows in set (0. sec)

快速重建索引

通常情况下，如果你想改变服务器的全文搜索变量，你需要在表格中重新建立全文索引，以确保你的更新得到映射。这一操作将会花费大量的时间，特别是如果你需要处理很多数据的时候。一种快速的解决方法是使用REPAIR TABLE命令，以下为演示过程：

表B

mysql> REPAIR TABLE content QUICK;
+-----------+--------+----------+----------+
| Table| Op| Msg_type | Msg_text |
+-----------+--------+----------+----------+
| content| repair | status| OK|
+-----------+--------+----------+----------+
row in set (0.05 sec)

压缩一定的表格类型

如果你处理的是只读MyISAM表格，MySQL允许你将其压缩以节省磁盘空间。对此可以使用包括myisampack，如下所示：

表C

shell> myisampackmovies.MYI
Compressing movies.MYD: (46 records)
- Calculating statistics
- Compressing file
4.05%

使用传统SQL

MySQL支持SQL查询中的传统用法，支持IF与CASE结构。以下是一个简单的例子：

表D

mysql> Sel�ct IF (priv=, 'admin', 'guest') As usertype FROM privs Wh�re username = 'joe';
+----------+
| usertype |
+----------+
| admin|
+----------+
row in set (0.00 sec)

以CSV格式输出表格数据

MySQL输出文件包含一个全部SQL命令列表。如果你想将输出文件导入到MySQL，这一功能非常实用，但如果目标程序(比如Excel)不能与SQL相互通讯，这一方法将行不通。在这种情况下，可以通过告诉MySQL以CSV格式建立输出文件，这种CSV格式很方便地导入到绝大部分的程序。这里演示了mysqldump的操作过程：

shell> mysqldump -T . --fields-terminated-by=", " mydbmytable

这将在当前目录中生成一个文本文件，包含来自mydb.mytable列表中以逗号为间隔符的记录。

以激活strict模式减少“bad”数据的出现

MySQL服务器能够以多种不同的模式运行，而每一种都针对于特定的目的而优化。在默认情况下，没有设置模式。然而，通过在服务器命令行中添加以下选项可以很容易地改变模式的设置并将MySQL以“strict”模式运行：

shell> mysqld --sql_mode="STRICT_ALL_TABLES" &

在“strict”模式下，通过MySQL的中止查询执行并返回一个错误，服务器的很多自动修正功能都被无效化。同样，该模式下也将会执行更为严格的时间检查。

监视服务器

你可以通过运行SHOW STATUS命令获得一份服务器运行与统计的报告，包括打开连接的次数，激活查询次数，服务器正常运行时间等等。例如：

表 E

mysql> SHOW STATUS;
+------------------+-------+
| Variable_name| Value |
+------------------+-------+
| Aborted_clients| 0|
| Aborted_connects | 0|
...
| Uptime| 85|
+------------------+-------+
56 rows in set (0.6 sec)

自动返回Cr�ate TABLE代码

MySQL允许你自动获得SQL命令重新建立一个特定的表格。只简单地运行SHOW Cr�ate TABLE命令，并查看表格建立代码，如下所示：

表 F

mysql> SHOW Cr�ate TABLE products;
-----------------------------------------------------
| Table| Cr�ate Table
+----------+-----------------------------------------
| products | Cr�ate TABLE `products` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`price` int(0) default NULL,
PRIMARY KEY(`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin |
+----------+-----------------------------------------
row in set (0.27 sec)

建立一个更为有用的命令提示：

在缺省情况下，MySQL命令行客户程序显示一个简单的mysql>提示符。然而，你可以使用特定的修改内容来改变这一提示符使之变得更为有效，这些内容包括：当前用户名称，主机名称，以及当前选择的数据库。如下所示：

表 G

mysql> prompt \U:/\d>
PROMPT set to '\U:/\d>'
root@localhost:/db>

从这里的文档文件你可以获得支持MySQL客户程序更改的一个完整列表。

　　1、mysql 数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好 mysql 数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份数据库中。实现 mysql 数据库的热备份。

　　2、要想实现双机的热备首先要了解主从数据库服务器的版本的需求。要实现热备 mysql 的版本都要高于3.2，还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。

　　3、设置主数据库服务器：

　　a. 首先查看主服务器的版本是否是支持热备的版本。然后查看 my.cnf(类 unix)或者 my.ini(windows)中 mysqld 配置块的配置有没有 log-bin (记录数据库更改日志)，因为 mysql 的复制机制是基于日志的复制机制，所以主服务器一定要支持更改日志才行。然后设置要写入日志的数据库或者不要写入日志的数据库。这样只有您感兴趣的数据库的更改才写入到数据库的日志中。

　　server-id= //数据库的 id 这个应该默认是就不用改动

　　log-bin=log_name //日志文件的名称，这里可以制定日志到别的目录 如果没有设置则默认主机名的一个日志名称

　　binlog-do-db=db_name //记录日志的数据库

　　binlog-ignore-db=db_name //不记录日志的数据库

　　以上的如果有多个数据库用","分割开，然后设置同步数据库的用户帐号

　　mysql> GRANT REPLICATION SLAVE ON *.*

　　-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

　　4.0.2 以前的版本, 因为不支持 REPLICATION 要使用下面的语句来实现这个功能

　　mysql> GRANT FILE ON *.*

　　-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

　　设置好主服务器的配置文件后重新启动数据库

　　b.锁定现有的数据库并备份现在的数据

　　锁定数据库

　　mysql> FLUSH TABLES WITH READ LOCK;

　　备份数据库有两种办法一种是直接进入到 mysql 的 data 目录然后打包你需要备份数据库的文件夹，第二种是使用 mysqldump 的方式来备份数据库但是要加上"--master-data " 这个参数，建议使用第一种方法来备份数据库

　　c.查看主服务器的状态

　　mysql> show master status\G;

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

　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

　　| mysql-bin.003 | 73 | test | manual,mysql |

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

　　记录 File 和 Position 项目的值，以后要用的。

　　d.然后把数据库的锁定打开

　　mysql> UNLOCK TABLES;

　　4、设置从服务器

　　a.首先设置数据库的配置文件

　　server-id=n //设置数据库 id 默认主服务器是可以随便设置但是如果有多台从服务器则不能重复。

　　master-host=db-master.mycompany.com //主服务器的IP地址或者域名

　　master-port=3306 //主数据库的端口号

　　master-user=pertinax //同步数据库的用户

　　master-password=freitag //同步数据库的密码

　　master-connect-retry=60 //如果从服务器发现主服务器断掉，重新连接的时间差

　　report-host=db-slave.mycompany.com //报告错误的服务器

　　b.把从主数据库服务器备份出来的数据库导入到从服务器中

　　c.然后启动从数据库服务器，如果启动的时候没有加上 "--skip-slave-start" 这个参数则进入到 mysql 中

　　mysql> slave stop; //停止 slave 的服务

　　d.设置主服务器的各种参数

　　mysql> CHANGE MASTER TO

　　-> MASTER_HOST='master_host_name', //主服务器的IP地址

　　-> MASTER_USER='replication_user_name', //同步数据库的用户

　　-> MASTER_PASSWORD='replication_password', //同步数据库的密码

　　-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名，前面要求记住的参

　　-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记住的参数)

　　e.启动同步数据库的线程

　　mysql> slave start;

　　查看数据库的同步情况吧。如果能够成功同步那就恭喜了！

　　查看主从服务器的状态

　　mysql> SHOW PROCESSLIST\G //可以查看 mysql 的进程看看是否有监听的进程

　　如果日志太大清除日志的步骤如下

　　.锁定主数据库

　　mysql> FLUSH TABLES WITH READ LOCK;

　　2.停掉从数据库的 slave

　　mysql> slave stop;

　　3.查看主数据库的日志文件名和日志文件的 position

　　show master status;

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

　　| File | Position | Binlog_do_db | Binlog_ignore_db |

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

　　| louis-bin.00 | 79 | | mysql |

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

　　4.解开主数据库的锁

　　mysql> unlock tables;

　　5.更新从数据库中主数据库的信息

　　mysql> CHANGE MASTER TO

　　-> MASTER_HOST='master_host_name', //主服务器的IP地址

　　-> MASTER_USER='replication_user_name', //同步数据库的用户

　　-> MASTER_PASSWORD='replication_password', //同步数据库的密码

　　-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名，前面要求记住的参数

　　-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记住的参数)

　　6.启动从数据库的 slave

　　mysql> slave start;

有2个不同的方法增加用户：通过使用GRANT语句或通过直接操作MySQL授权表。比较好的方法是使用GRANT语句，因为他们是更简明并且好像错误少些。

下面的例子显示出如何使用MySQL客户安装新用户。这些例子假定权限已缺省被安装。这意味着为了改变，你必须在MySQL正在运行同一台机器上，你必须作为MySQL root用户连接，并且root用户必须对MySQL数据库有ins�rt权限和reload管理权限。另外，如果你改变了root用户口令，你必须如下的MySQL命令指定它。

你可以通过发出GRANT语句增加新用户：

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
      IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
      IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

这些GRANT语句安装3个新用户：

monty：可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令('something'做这个。注意，我们必须对monty@localhost和monty@"%"发出GRANT语句。如果我们增加localhost条目，对localhost的匿名用户条目在我们从本地主机连接接时由mysql_install_db创建的条目将优先考虑，因为它有更特定的Host字段值，所以以user表排列顺序看更早到来。

admin：可以从localhost没有一个口令进行连接并且被授予reload和process管理权限的用户。这允许用户执行mysqladmin reload、mysqladmin refresh和mysqladmin flush-*命令，还有mysqladmin processlist。没有授予数据库有关的权限。他们能在以后通过发出另一个GRANT语句授权。

dummy：可以不用一个口令连接的一个用户，但是只能从本地主机。全局权限被设置为'N'--USAGE权限类型允许你无需权限就可设置一个用户。它假定你将在以后授予数据库相关的权限。

你也可以直接通过发出Ins�rt语句增加同样的用户存取信息，然后告诉服务器再次装入授权表：

shell> mysql --user=root mysql
mysql> Ins�rt INTO user VALUES('localhost','monty',PASSWORD('something'),
        'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> Ins�rt INTO user VALUES('%','monty',PASSWORD('something'),
        'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> Ins�rt INTO user SET Host='localhost',User='admin',
         Reload_priv='Y', Process_priv='Y';
mysql> Ins�rt INTO user (Host,User,Password)
            VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

取决于你的MySQL版本，对上述，你可能必须使用一个不同数目'Y'值(在3.22.以前的版本有更少的权限列)。对admin用户，只用在3.22.开始的版本具有的更加可读的Ins�rt扩充的语法。

注意，为了设置一个超级用户，你只需创造一个user表条目，其权限字段设为'Y'。不需要db或host表的条目。

在user表中的权限列不是由最后一个Ins�rt语句明确设置的(对dummy用户)，因此那些列被赋予缺省值'N'。这是GRANT USAGE做的同样的事情。

下列例子增加一个用户custom，他能从主机localhost、server.domain和whitehouse.gov连接。他只想要从localhost存取bankaccount数据库，从whitehouse.gov存取expenses数据库和从所有3台主机存取customer数据库。他想要从所有3台主机上使用口令stupid。

为了使用GRANT语句设置个用户的权限，运行这些命令：

shell> mysql --user=root mysql
mysql> GRANT Sel�ct,Ins�rt,Up�ate,Del�te,Cr�ate,Dro
      ON bankaccount.*
      TO custom@localhost
      IDENTIFIED BY 'stupid';
mysql> GRANT Sel�ct,Ins�rt,Up�ate,Del�te,Cr�ate,Dro
      ON expenses.*
      TO [email protected]
      IDENTIFIED BY 'stupid';
mysql> GRANT Sel�ct,Ins�rt,Up�ate,Del�te,Cr�ate,Dro
      ON customer.*
      TO custom@'%'
      IDENTIFIED BY 'stupid';

通过直接修改授权表设置用户权限，运行这些命令(注意，在结束时FLUSH PRIVILEGES)：

shell> mysql --user=root mysql
mysql> Ins�rt INTO user (Host,User,Password)
    VALUES('localhost','custom',PASSWORD('stupid'));
mysql> Ins�rt INTO user (Host,User,Password)
    VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> Ins�rt INTO user (Host,User,Password)
    VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> Ins�rt INTO db
    (Host,Db,User,Sel�ct_priv,Ins�rt_priv,Up�ate_priv,Del�te_priv,
    Cr�ate_priv,Dro_priv)
    VALUES
    ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> Ins�rt INTO db
    (Host,Db,User,Sel�ct_priv,Ins�rt_priv,Up�ate_priv,Del�te_priv,
    Cr�ate_priv,Dro_priv)
    VALUES
    ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> Ins�rt INTO db
    (Host,Db,User,Sel�ct_priv,Ins�rt_priv,Up�ate_priv,Del�te_priv,
    Cr�ate_priv,Dro_priv)
    VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

头3个Ins�rt语句增加user表条目，允许用户custom用给定口令从不同的主机进行连接，但是没有授予任何许可(所有权限被设置为缺省值'N')。后3个Ins�rt语句增加db表条目，授予custom以bankaccount、expenses和customer数据库权限，但是只能在从正确的主机存取时。通常，在授权表直接被修改时，服务器必须被告知再次装入他们(用FLUSH PRIVILEGES)以便使权限修改生效。

如果你想要给特定的用户从一个给定的域上的任何机器上存取权限，你可以发出一个如下的GRANT语句：

mysql> GRANT ...
      ON *.*
      TO myusername@"%.mydomainname.com"
      IDENTIFIED BY 'mypassword';

为了通过直接修改授权表做同样的事情，这样做：

mysql> Ins�rt INTO user VALUES ('%.mydomainname.com', 'myusername',
      PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

你也可以使用xmysqladmin、mysql_webadmin甚至xmysql在授权表中插入、改变和更新值。你可以在MySQL的Contrib目录找到这些实用程序。

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog：用于处理二进制日志文件的实用工具”。

要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf o my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为--log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：
SHOW BINLOG EVENTS \G
你还可以从命令行输入下面的内容：
mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS \G'
将密码my_pwd替换为服务器的root密码。

. 指定恢复时间

对于MySQL 4..4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午0:00(今天是2006年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.23456 \
  | mysql -u root -pmypwd
该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：
mysqlbinlog --start-date="2005-04-20 0:0:00" /var/log/mysql/bin.23456 \
  | mysql -u root -pmypwd \
在该行中，从上午0:0登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午0:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2. 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 0:05:00" \
  /var/log/mysql/bin.23456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：
mysqlbinlog --stop-position="36832" /var/log/mysql/bin.23456 \
| mysql -u root -pmypwd
mysqlbinlog --start-position="36835" /var/log/mysql/bin.23456 \
| mysql -u root -pmypwd \
上面的第行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

当你连接一个MySQL服务器时，你通常应该使用一个口令。口令不以明文在连接上传输。所有其它信息作为能被任何人读懂的文本被传输。如果你担心这个，你可使用压缩协议(MySQL3.22和以上版本)使事情变得更难。甚至为了使一切更安全，你应该安装ssh。用它，你能在一个MySQL服务器与一个MySQL客户之间得到一个加密的tcp/ip连接。
为了使一个MySQL系统安全，强烈要求你考虑下列建议：
对所有MySQL用户使用口令。记住，如果other_user没有口令，任何人能简单地用Mysql -u other_user db_name作为任何其它的人登录。对客户机/服务器应用程序，客户可以指定任何用户名是常见的做法。在你运行它以前，你可以通过编辑MySQL_install_db脚本改变所有用户的口令，或仅仅Mysql root的口令，象这样：
shell> mysql -u root mysql mysql> up�ate user
set password=password("new_password")
wh�re user="root"; mysql> flush privileges;

不要作为Unix的root用户运行MySQL守护进程。mysqld能以任何用户运行，你也可以创造一个新的Unix用户MySQL使一切更安全。如果你作为其它Unix用户运行mysqld，你不需要改变在user表中的root用户名，因为Mysql用户名与unix 用户名没关系。你可以作为其它unix用户编辑mysql.server启动脚本mysqld。通常这用su命令完成。

如果你把一个Unix root用户口令放在mysql.server脚本中，确保这个脚本只能对root是可读的。
检查那个运行Mysqld的Unix用户是唯一的在数据库目录下有读/写权限的用户。

不要把process权限给所有用户。mysqladmin processlist的输出显示出当前执行的查询正文，如果另外的用户发出一个up�ate user set password=password("not_secure")查询，被允许执行那个命令的任何用户可能看得到。mysqld为有process权限的用户保留一个额外的连接, 以便一个mysql root用户能登录并检查，即使所有的正常连接在使用。不要把file权限给所有的用户。

有这权限的任何用户能在拥有mysqld守护进程权限的文件系统那里写一个文件！为了使这更安全一些，用sel�ct ... into outfile生成的所有文件对每个人是可读的，并且你不能覆盖已经存在的文件。
file权限也可以被用来读取任何作为运行服务器的unix用户可存取的文件。这可能被滥用，例如，通过使用load data装载“/etc/passwd”进一个数据库表，然后它能用sel�ct被读入。

如果你不信任你的dns，你应该在授权表中使用IP数字而不是主机名。原则上讲，--secure选项对mysqld应该使主机名更安全。在任何情况下，你应该非常小心地使用包含通配符的主机名。