Quick HOWTO : Ch34 : Basic MySQL Configuration/zh
 |
文章出处: |
{{#if: | {{{2}}} | http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch34_:_Basic_MySQL_Configuration }} |
|
翻译人员: |
yiding.he |
|
点击翻译: |
English {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/af | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Afrikaans| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/af|Afrikaans]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ar | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|العربية| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ar|العربية]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/az | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|azərbaycanca| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/az|azərbaycanca]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/bcc | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|جهلسری بلوچی| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/bcc|جهلسری بلوچی]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/bg | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|български| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/bg|български]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/br | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|brezhoneg| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/br|brezhoneg]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ca | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|català| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ca|català]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/cs | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|čeština| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/cs|čeština]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/de | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Deutsch| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/de|Deutsch]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/el | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Ελληνικά| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/el|Ελληνικά]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/es | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|español| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/es|español]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/fa | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|فارسی| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/fa|فارسی]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/fi | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|suomi| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/fi|suomi]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/fr | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|français| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/fr|français]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/gu | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|ગુજરાતી| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/gu|ગુજરાતી]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/he | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|עברית| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/he|עברית]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/hu | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|magyar| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/hu|magyar]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/id | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Bahasa Indonesia| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/id|Bahasa Indonesia]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/it | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|italiano| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/it|italiano]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ja | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|日本語| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ja|日本語]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ko | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|한국어| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ko|한국어]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ksh | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Ripoarisch| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ksh|Ripoarisch]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/mr | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|मराठी| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/mr|मराठी]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ms | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Bahasa Melayu| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ms|Bahasa Melayu]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/nl | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Nederlands| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/nl|Nederlands]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/no | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|norsk| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/no|norsk]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/oc | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|occitan| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/oc|occitan]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/pl | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|polski| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/pl|polski]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/pt | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|português| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/pt|português]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ro | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|română| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ro|română]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/ru | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|русский| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/ru|русский]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/si | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|සිංහල| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/si|සිංහල]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/sq | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|shqip| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/sq|shqip]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/sr | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|српски / srpski| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/sr|српски / srpski]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/sv | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|svenska| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/sv|svenska]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/th | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|ไทย| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/th|ไทย]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/tr | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Türkçe| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/tr|Türkçe]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/vi | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|Tiếng Việt| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/vi|Tiếng Việt]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/yue | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|粵語| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/yue|粵語]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/zh | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|中文| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/zh|中文]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/zh-hans | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|中文（简体）| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/zh-hans|中文（简体）]]}}|}} {{#ifexist: {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration | Quick HOWTO : Ch34 : Basic MySQL Configuration | {{#if: | :}}Quick HOWTO : Ch34 : Basic MySQL Configuration/zh}}/zh-hant | • {{#if: Quick HOWTO : Ch34 : Basic MySQL Configuration|中文（繁體）| [[::Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/zh-hant|中文（繁體）]]}}|}} |
{{#ifeq:Quick HOWTO : Ch34 : Basic MySQL Configuration|:Quick HOWTO : Ch34 : Basic MySQL Configuration/zh|请不要直接编辑翻译本页，本页将定期与来源同步。}} |
{{#ifexist: :Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/zh | | {{#ifexist: Quick HOWTO : Ch34 : Basic MySQL Configuration/zh/zh | | {{#ifeq: {{#titleparts:Quick HOWTO : Ch34 : Basic MySQL Configuration/zh|1|-1|}} | zh | | }} }} }} {{#ifeq: {{#titleparts:Quick HOWTO : Ch34 : Basic MySQL Configuration/zh|1|-1|}} | zh | | }}
介绍
大部分居家工作的管理员不会接触到数据库编程，但是他们有时候会需要安装一些程序，而这些程序需要 MySQL 数据库支持。本文将介绍如何对 MySQL 做基本的配置，使得那些需要 MySQL 的应用程序能够正常工作。本文假设 MySQL 数据库和应用程序运行在同一台服务器上。
为应用程序准备 MySQL
大多数情况下，开发人员希望管理员为他们预先准备好一个数据库，以便他们能够进行开发。这些准备步骤包括：
- 安装并启动 MySQL；
- 创建一个 "root" 用户；
- 创建一个普通 MySQL 用户，应用程序将通过它来访问数据库；
- 为应用程序创建数据库；
- 在数据库中建立表；
- 对数据库架构做一些基本测试。
在接下来的几个章节当中，我们贯穿一个示例场景。假设系统里面安装了一个叫做 sales-test 的基于 Linux 的应用。它的安装手册上说，启动这个应用之前，你必须准备好数据库，创建好一系列的表，并提供一个用户。好在 sales-test 附带了一个创建数据库表的脚本，所以你要做的就是剩下的两步。你的决定是，创建一个名为 salesdata 的数据库，和一个名为 mysqluser 的用户。
那么接下来我们开始介绍如何完成这些工作。
安装 MySQL
通常你会需要 MySQL 的服务器端和客户端的 RPM 安装包（对于 Ubuntu 来说是 DEB 包。如果你是 Ubuntu 用户，你可以跳过本节，在 WIKI 中通过关键字“安装 MySQL”来找到替代方案。——译者注）。客户端可以用来测试数据库连接，并可以用来访问数据库服务器（包括本地的服务器）。
译者补充：在 Ubuntu 下安装 MySQL 服务器和客户端的方法：
- 打开“新立得软件包管理器”，搜索“mysql”；
- 找到“mysql-server-5.0”，双击它。这时会弹出对话框，单击“标记”表示同意安装这些依赖包；
- 同样标记安装“mysql-client-5.0”；
- 单击工具栏上的“应用”，即可开始安装。
以上这些步骤可以简化为命令
sudo apt-get install mysql-server-5.0 mysql-client-5.0
注意：在安装过程中会要求你输入 MySQL root 用户的密码。请务必要记得这个密码。
启动 MySQL
在创建数据库之前，我们先要启动数据库服务器。想要在系统启动的时候自动启动数据库服务器，可以使用 chkconfig 命令：
[root@bigboy tmp]# chkconfig mysqld on (译者注：这个命令在 Ubuntu 中无效。在 Ubuntu 中安装完 MySQL 之后，你可以在“系统”—“系统管理”—“服务”中找到 MySQL 的服务。)
在系统运行过程中启动、关闭、重启 MySQL 的命令如下：
[root@bigboy tmp]# service mysqld start [root@bigboy tmp]# service mysqld stop [root@bigboy tmp]# service mysqld restart
(译者注：service 命令在 Ubuntu 中无效。Ubuntu 中相应的命令为：) # /etc/init.d/mysql start # /etc/init.d/mysql stop # /etc/init.d/mysql restart
当你修改了配置文件後，你需要重启 mysqld 才能使这些修改生效。
要想检查 mysqld 进程是否已经开启，可以使用下面的命令：
[root@bigboy tmp]# pgrep mysqld
如果进程开启，这个命令将会返回该进程的 id。
/etc/my.cnf 配置文件
/etc/my.cnf 是 MySQL 的主要配置文件，它保存 MySQL 的数据文件位置和其他参数。普通用户无需修改这个文件。(在ubuntu中，my.cnf文件位于/etc/mysql/my.cnf)
MySQL 数据文件的存放位置
MySQL 数据文件的存放位置保存在 /etc/my.cnf 文件中，这个值通常是 /var/lib/mysql。如果你创建了一个名为 test 的数据库，那么这个数据库的数据会存放到 /var/lib/mysql/test 目录下。
为 MySQL 创建一个 "root" 帐户
(译者注：本节中的操作不是必须的，因为 root 用户的密码在安装时就已经设定了) MySQL 的所有用户名和密码都保存在自身的一个名为 mysql 的数据库中。新建的用户也会保存到这里。多数用户的权限是可变更的，但 root 用户（或“超级用户”）是个例外。root 用户还可以创建和删除数据库。要设置 root 用户的密码，你可以使用 mysqladmin 命令：
- 确认 MySQL 数据库正在运行；
- 使用 mysqladmin 命令来设置 MySQL root 密码：
[root@tmp bigboy]# mysqladmin -u root password new-password
If you want to change your password later, you will probably have to do a root password recovery.
使用 MySQL 命令行
MySQL 有自己的命令行编译器(CLI)。通过 mysql 命令，你可以进入 MySQL 命令行。mysql 命令有两个参数用于登录：-u 参数指定登录用户名，-p 参数指定登录时输入密码。下面是 root 用户登录的例子：
[root@bigboy tmp]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
注意：几乎所有的 MySQL 命令都以分号结束，包括 exit 退出命令！
创建和删除 MySQL 数据库
很多基于 MySQL 的应用都要求创建一个单独的数据库。创建数据库的过程很简单：在 MySQL 命令行下键入：
mysql> create database salesdata; Query OK, 1 row affected (0.00 sec) mysql>
如果创建过程中出现问题，你想要重新来过的话，可以使用 drop 命令删除指定的数据库。下面是删除 salesdata 数据库例子：
mysql> drop database salesdata; Query OK, 0 rows affected (0.00 sec) mysql>
注意：有时候用 drop 命令删除了数据库之后，通过 show databases; 命令仍然能看到刚才删除的数据库。这通常是因为数据库目录中存在残留的文件。如果出现这种情况，你需要先停止 MySQL 服务器，然后手动将 /var/lib/mysql 下指定数据库的目录删除。
[root@bigboy tmp]# /etc/init.d/mysql stop
为数据库用户设置权限
很多情况下，你不但要创建数据库，你还要创建用来访问这个数据库的用户及其密码。因为 root 拥有全局的权限，所以通过 root 来执行数据库不是个好主意。
MySQL 将其所有的用户名和密码保存在一个叫 mysql 的特殊数据库中。你可以通过 grant 命令对用户赋权，使其拥有访问该数据库的能力，格式如下：
sql> grant all privileges on database.* to username@"servername" identified by 'password';
假如本地有一个名为 salesdata 的数据库。你现在要创建一个名为 mysqluser，密码为 pinksl1p 的用户，并让其拥有对 salesdata 进行所有操作的权限，你可以执行下面的命令。如果该数据库不在本地，你就需要将“localhost”改为实际的 IP 地址。
sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';
接下来就是执行 flush privileges 命令，将权限的变更写入 mysql.sql 数据库。
sql> flush privileges;
执行批处理脚本文件
Another common feature of prepackaged applications written in MySQL is that they may require you to not only create the database, but also to create the tables of data inside them as part of the setup procedure. Fortunately, many of these applications come with scripts you can use to create the data tables automatically.
Usually you have to run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a < on the command line.
The example runs a script named create_mysql.script whose commands are applied to the newly created database named salesdata. MySQL prompts for the MySQL root password before completing the transaction. (You have to create the database first, before you can run this command successfully.)
[root@bigboy tmp]# mysql -u root -p salesdata < create_mysql.script Enter password: [root@bigboy tmp]#
查看你新建的 MySQL 数据库
A number of commands can provide information about your newly created database. Here are some examples:
- Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.
[root@bigboy tmp]# mysql -u mysqluser -p salesdata
- List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:
mysql> show databases; +-----------+ | Database | +-----------+ | salesdata | +-----------+ 1 row in set (0.00 sec) mysql>
列出你的 MySQL 数据库中的所有表
The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.
The example uses the salesdata database; notice that it has a table named test.
mysql> use salesdata; 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_salesdata | +---------------------+ | test | +---------------------+ 1 row in set (0.00 sec) mysql>
查看指定的表结构
The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.
mysql> describe test; +---------------+--------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+------------+----------------+ | num | int(11) | | PRI | NULL | auto_increment | | date_modified | date | | MUL | 0000-00-00 | | | name | varchar(50) | | MUL | | | | description | varchar(75) | YES | | NULL | | +---------------+--------------+------+-----+------------+----------------+ 6 rows in set (0.00 sec) mysql>
查看数据表的内容
You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.
mysql> select * from test limit 1;
With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.
配置你的应用程序
After creating and testing the database, you need to inform your application of the database name, the IP address of the database client server, and the username and password of the application's special MySQL user that will be accessing the data.
Frequently this registration process is done by the editing of a special application-specific configuration file either via a Web GUI or from the command line. Read your application's installation guide for details.
You should always remember that MySQL is just a database that your application will use to store information. The application may be written in a variety of languages with Perl and PHP being the most popular. The base PHP and Perl RPMs are installed with Fedora Linux by default, but the packages used by these languages to talk to MySQL are not. You should also ensure that you install the RPMs listed in Table 34.1 on your MySQL clients to ensure compatibility. Use the yum utility discussed in Chapter 6, "Installing Linux Software", if you are uncertain of the prerequisite RPMs needed.
Table 34.1 Required PHP and Perl RPMs for MySQL Support
| RPM | RPM |
| php-mysql | MySQL database specific support for PHP |
| perl-DBI | Provides a generic Perl interface for interacting with relational databases |
| perl-DBD-MySQL | MySQL database specific support for Perl |
恢复／变更你的 MySQL Root 密码
Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:
1) Stop MySQL
[root@bigboy tmp]# service mysqld stop Stopping MySQL: [ OK ] [root@bigboy tmp]#
2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.
[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking & [1] 13007 [root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql [root@bigboy tmp]#
Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different. This difference is outlined in Appendix III, "Fedora Version Differences".
3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.
[root@bigboy tmp]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.16 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.
mysql> use mysql;
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> UPDATE user SET Password=PASSWORD("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql>
5) Exit MySQL and restart the mysqld daemon.
mysql> exit Bye [root@bigboy tmp]# service mysqld restart STOPPING server from pid file /var/run/mysqld/mysqld.pid 051224 17:24:56 mysqld ended Stopping MySQL: [ OK ] Starting MySQL: [ OK ] [1]+ Done mysqld_safe --skip-grant-tables --skip-networking [root@bigboy tmp]#
The MySQL root user will now be able to manage MySQL using this new password.
MySQL 数据库备份
The syntax for backing up a MySQL database is as follows:
mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]
In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command
[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \ -ppinksl1p salesdata > /tmp/salesdata-backup.sql
Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.
Note: Always backup the database named mysql too, because it contains all the database user access information.
MySQL 数据库恢复
The syntax for restoring a MySQL database is:
mysql -u [username] -p[password] [database] < [backup_file]
So, using the previous example, you can restore the contents of the database with
[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \ < /tmp/salesdata-backup.sql
Note: You may have to restore the database named mysql also, because it contains all the database user access information.
MySQL 表数据的备份与恢复
Sometimes you may want to backup only one or more tables from a database. There are some practical reasons for wanting to do this. You may have a message board / forums application that uses MySQL to store its data and you want to create a brand new forum with the same users as the old one so that the users don't have to register all over again.
The MySQL SELECT statement can be used to export the data to a backup file and the LOAD command can be used to import the data back into the new database used by the new forum. In this example the data in the phpbb_users and phpbb_themes tables of the forums-db-old database are exported to files named /tmp/forums-db-users.sql and /tmp/forums-db-themes.sql respectively. The data is then imported into tables of the same name in the forums-db-new database.
mysql> use forums-db-old; 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> SELECT * INTO OUTFILE '/tmp/forums-db-users.sql' FROM phpbb_users; Query OK, 1042 rows affected (0.03 sec) mysql> SELECT * INTO OUTFILE '/tmp/forums-db-themes.sql' FROM phpbb_themes; Query OK, 1038 rows affected (0.03 sec) mysql> use forums-db-new; Database changed mysql> load data infile '/tmp/forums-db-users.sql' replace into table forums-db.phpbb_users ; Query OK, 1042 rows affected (0.06 sec) Records: 1042 Deleted: 0 Skipped: 0 Warnings: 0 mysql> load data infile '/tmp/forums-db-themes.sql' replace into table forums-db.phpbb_themes ; Query OK, 1038 rows affected (0.04 sec) Records: 1038 Deleted: 0 Skipped: 0 Warnings: 0 mysql>
As you can see, the syntax is fairly easy to understand. The REPLACE directive will overwrite any previously existing records with the same unique, or primary, key in the source and destination tables. The IGNORE directive will only insert records where the primary keys are different.
基础的 MySQL 网络安全
By default MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.
[root@bigboy tmp]# netstat -an Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State ... ... tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN ... ... [root@bigboy tmp]#
The problem with this is that it exposes your database to MySQL queries from the Internet. If your SQL database is going to be accessed only by applications running on the server itself, then you can force it to listen only to the equivalent of its loopback interface. Here's how.
1) Edit the /etc/my.cnf file and use the bind-address directive in the [mysqld] section to define the specific IP address on which MySQL listens for connections.
[mysqld] bind-address=127.0.0.1
2) Restart MySQL. The netstat -an command will show MySQL listening on only the loopback address on TCP port 3306, and your application should continue to work as expected.
Basic MyQL Troubleshooting
You can confirm whether your MySQL installation has succeeded by performing these few simple steps.
Connectivity Testing
In the example scenario, network connectivity between the database and the application will not be an issue because they are running on the same server.
In cases where they are not, you have to use the troubleshooting techniques in Chapter 4, "Simple Network Troubleshooting", to test both basic connectivity and access on the MySQL TCP port of 3306.
Test Database Access
The steps outlined earlier are a good test of database access. If the application fails, then retrace your steps to create the database and register the database information into the application. MySQL errors are logged automatically in the /var/log/mysqld.log file; investigate this file at the first sign of trouble.
Sometimes MySQL will fail to start because the host table in the mysql database wasn't created during the installation, this can be rectified by running the mysql_install_db command.
[root@bigboy tmp]# service mysqld start Timeout error occurred trying to start MySQL Daemon. Starting MySQL: [FAILED] [root@bigboy tmp]# tail /var/log/mysql.log ... ... 050215 19:00:33 mysqld started 050215 19:00:33 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 050215 19:00:33 mysqld ended ... ... [root@bigboy tmp]# mysql_install_db ... ... [root@bigboy tmp]# service mysqld start Starting MySQL: [ OK ] [root@bigboy tmp]#
A Common Fedora Core 1 MySQL Startup Error
You may notice that you can start MySQL correctly only once under Fedora Core 1. All subsequent attempts result in the message "Timeout error occurred trying to start MySQL Daemon.".
[root@bigboy tmp]# /etc/init.d/mysqld start Timeout error occurred trying to start MySQL Daemon. Starting MySQL: [FAILED] [root@bigboy tmp]#
This is caused by the MySQL startup script incorrectly attempting to do a TCP port ping to contact the server. The solution is:
1) Edit the script /etc/rc.d/init.d/mysqld.
2) Search for the two mysqladmin lines with the word ping in them and insert the string "-u $RANDOM" before the word "ping":
if [ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]; then if !([ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]); then
3) Restart MySQL.
After doing this MySQL should function correctly even after a reboot.
Conclusion
MySQL has become one of the most popular Linux databases on the market and it continues to improve each day. If you have a large project that requires the installation of a database, then I suggest seeking the services of a database administrator (DBA) to help install and fine-tune the operation of MySQL. I also suggest, no matter the size of the project, that you practice an application installation on a test Linux system to be safe. It doesn't necessarily have to be the same application. You can find free MySQL-based applications using a Web search engine, and you can use these to be come familiar with the steps outlined in this chapter before beginning your larger project.
