在CentOS上MySQL数据库服务器配置方法

Setting up Install Process
Setting up repositories
dag 100% |=========================| 1.1 kB 00:00
update 100% |=========================| 951 B 00:00
base 100% |=========================| 1.1 kB 00:00
addons 100% |=========================| 951 B 00:00
extras 100% |=========================| 1.1 kB 00:00
Reading repository metadata in from local files
primary.xml.gz 100% |=========================| 106 kB 00:00
update : ################################################## 261/261
Added 5 new packages, deleted 0 old in 2.02 seconds
Reducing Dag RPM Repository for Red Hat Enterprise Linux to included packages only
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysql-server to pack into transaction set.
mysql-server-4.1.20-1.RHE 100% |=========================| 29 kB 00:00
---> Package mysql-server.i386 0:4.1.20-1.RHEL4.1 set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient.so.14(libmysqlclient_14) for package: mysql-server
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server
--> Processing Dependency: perl-DBI for package: mysql-server
--> Processing Dependency: perl(DBI) for package: mysql-server
--> Processing Dependency: mysql = 4.1.20-1.RHEL4.1 for package: mysql-server
--> Processing Dependency: libmysqlclient.so.14 for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.14(libmysqlclient_14) for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.14 for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for perl-DBD-MySQL to pack into transaction set.
perl-DBD-MySQL-2.9004-3.1 100% |=========================| 5.4 kB 00:00
---> Package perl-DBD-MySQL.i386 0:2.9004-3.1 set to be updated
---> Downloading header for mysql to pack into transaction set.
mysql-4.1.20-1.RHEL4.1.i3 100% |=========================| 35 kB 00:00
---> Package mysql.i386 0:4.1.20-1.RHEL4.1 set to be updated
---> Downloading header for perl-DBI to pack into transaction set.
perl-DBI-1.40-8.i386.rpm 100% |=========================| 11 kB 00:00
---> Package perl-DBI.i386 0:1.40-8 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
mysql-server i386 4.1.20-1.RHEL4.1 update 9.8 M
Installing for dependencies:
mysql i386 4.1.20-1.RHEL4.1 update 2.9 M
perl-DBD-MySQL i386 2.9004-3.1 base 111 k
perl-DBI i386 1.40-8 base 466 k

Transaction Summary
=============================================================================
Install 4 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 13 M
Downloading Packages:
(1/4): perl-DBD-MySQL-2.9 100% |=========================| 111 kB 00:00
(2/4): mysql-server-4.1.2 100% |=========================| 9.8 MB 00:11
(3/4): mysql-4.1.20-1.RHE 100% |=========================| 2.9 MB 00:03
(4/4): perl-DBI-1.40-8.i3 100% |=========================| 466 kB 00:01
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: perl-DBI ######################### [1/4]
Installing: mysql ######################### [2/4]
Installing: perl-DBD-MySQL ######################### [3/4]
Installing: mysql-server ######################### [4/4]

Installed: mysql-server.i386 0:4.1.20-1.RHEL4.1
Dependency Installed: mysql.i386 0:4.1.20-1.RHEL4.1 perl-DBD-MySQL.i386 0:2.9004-3.1 perl-DBI.i386 0:1.40-8
Complete!

Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Reducing Dag RPM Repository for Red Hat Enterprise Linux to included packages only
Finished
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for php-mysql to pack into transaction set.
php-mysql-4.3.9-3.15.i386 100% |=========================| 18 kB 00:00
---> Package php-mysql.i386 0:4.3.9-3.15 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
php-mysql i386 4.3.9-3.15 update 35 k

Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 35 k
Downloading Packages:
(1/1): php-mysql-4.3.9-3. 100% |=========================| 35 kB 00:00
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: php-mysql ######################### [1/1]

Installed: php-mysql.i386 0:4.3.9-3.15
Complete!

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1　 ← 找到这一行，在这一行的下面添加新的规则，让MySQL的默认编码为UTF-8
default-character-set = utf8　 ← 添加这一行

然后在配置文件的文尾填加如下语句：

[mysql]
default-character-set = utf8

[root@sample ~]# chkconfig --list mysqld　 ← 确认MySQL自启动
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off　 ← 如果2--5为on的状态就OK

[root@sample ~]# /etc/rc.d/init.d/mysqld start　 ← 启动MySQL服务

Initializing MySQL database:　　　　　　　　 [ OK ]
Starting MySQL: 　　　　　　　　　　　　　[ OK ]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.20

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

mysql> select user,host,password from mysql.user;　 ← 查看用户信息
+------+------------------------------+---------------+
| user | host 　　　　　　　　　| password　|
+------+------------------------------+---------------+
| root | localhost　　　　 　　　| 　　　　　　|　 ← root密码为空 
| root | sample.centospub.com　 |　　　　　　 |　 ← root密码为空
|　　　| sample.centospub.com | 　　　　　　|
|　　　| localhost　　　　　　 | 　　　　　　|
+------+------------------------------+---------------+
4 rows in set (0.00 sec)

mysql> set password for root@localhost=password('在这里填入root密码');　 ← 设置root密码
Query OK, 0 rows affected (0.01 sec)

mysql> set password for root@'sample.centospub.com'=password('在这里填入root密码');　 ← 设置root密码
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,password from mysql.user;　 ← 查看用户信息
+------+--------------------------------+--------------------------+
| user | host　　　　　　　　　　| password　　 　　|
+------+--------------------------------+--------------------------+
| root | localhost　　　　　　　　| 19b68057189b027f |　 ← root密码被设置
| root | sample.centospub.com　　 | 19b68057189b027f |　 ← root密码被设置
| 　　 | sample.centospub.com　　 | 　　　　　　　　　|
| 　　 | localhost　　　　　　　　|　　　　　　　　　 |
+------+--------------------------------+--------------------------+
4 rows in set (0.01 sec)

mysql> exit　 ← 退出MySQL服务器
Bye

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)　 ← 出现此错误信息说明密码设置成功

[root@localhost ~]# mysql -u root -h sample.centospub.com　 ← 通过空密码用root登录

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)　 ← 出现此错误信息说明密码设置成功

[root@sample ~]# mysql -u root -p　 ← 通过密码用root登录
Enter password:　 ← 在这里输入密码

Welcome to the MySQL monitor. Commands end with ; or \g.　 ← 确认用密码能够成功登录
Your MySQL connection id is 5 to server version: 4.1.20

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

mysql> exit
Bye

[root@sample ~]# mysql -u root -h sample.centospub.com -p　 ← 通过密码用root登录
Enter password:　 ← 在这里输入密码

Welcome to the MySQL monitor. Commands end with ; or \g.　 ← 确认用密码能够成功登录
Your MySQL connection id is 6 to server version: 4.1.20

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

mysql> exit　 ← 退出MySQL服务器
Bye

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.20

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

mysql> select user,host from mysql.user;　 ← 查看用户信息
+------+----------------------------+
| user | host　　　　　　　　 |
+------+----------------------------+
|　　　| localhost 　　　　　　|

|　　　| 127.0.0.1 　　　　　　|
| root | localhost 　　　　　　|
|　　　| sample.centospub.com |
| root | sample.centospub.com　 |
+------+----------------------------+
4 rows in set (0.02 sec)

mysql> delete from mysql.user where user='';　 ← 删除匿名用户
Query OK, 2 rows affected (0.17 sec)

mysql> select user,host from mysql.user;　 ← 查看用户信息
+------+----------------------------+
| user | host　　　　　　　　 |
+------+----------------------------+
| root | localhost　　　　　　|
| root | sample.centospub.com |
+------+----------------------------+
2 rows in set (0.00 sec)

mysql> exit　 ← 退出MySQL服务器
Bye

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

mysql> show databases;　 ← 查看系统已存在的数据库
+-------------+
| Database |
+-------------+
| mysql　　 |
| test　　　|
+------------+
2 rows in set (0.02 sec)

mysql> drop database test;　 ← 删除名为test的空数据库
Query OK, 0 rows affected (0.07 sec)

mysql> show databases;　 ← 查看系统已存在的数据库
+-------------+
| Database |
+-------------+
| mysql 　　|　 ← 确认名为test的数据库被删除，已不存在
+-------------+
1 row in set (0.00 sec)

mysql> exit　 ← 退出MySQL服务器
Bye

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.1.20

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

mysql> grant all privileges on test.* to centospub@localhost identified by '在这里定义密码';　 ← 建立对test数据库有完全操作权限的名为centospub的用户
Query OK, 0 rows affected (0.03 sec)

mysql> select user from mysql.user where user='centospub';　 ← 确认centospub用户的存在与否
+---------+
| user 　|
+---------+
| centospub |　 ← 确认centospub已经被建立
+---------+
1 row in set (0.01 sec)

mysql> exit　 ← 退出MySQL服务器
Bye

[root@sample ~]# mysql -u centospub -p　 ← 用新建立的centospub用户登录MySQL服务器
Enter password:　 ← 在这里输入密码

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.20

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

mysql> create database test;　 ← 建立名为test的数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;　 ← 查看系统已存在的数据库
+-------------+
| Database |
+-------------+
| test 　　　|
+-------------+
1 row in set (0.00 sec)

mysql> use test　 ← 连接到数据库
Database changed

mysql> create table test(num int, name varchar(50));　 ← 在数据库中建立表
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;　 ← 查看数据库中已存在的表
+-------------------+
| Tables_in_test |
+-------------------+
| test　　　　　|
+-------------------+
1 row in set (0.01 sec)

mysql> insert into test values(1,'Hello World!');　 ← 插入一个值到表中
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;　 ← 查看数据库中的表的信息
+------+-------------------+
| num | name 　　　　　|
+------+-------------------+
| 1 　　| Hello World! 　|
+------+-------------------+
1 row in set (0.00 sec)

mysql> update test set name='Hello Everyone!';　 ← 更新表的信息，赋予新的值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;　 ← 查看数据库中的表的信息
+------+----------------------+
| num | name　　　　　 |
+------+----------------------+
| 1 　　| Hello Everyone! |　 ← 确认被更新到新的值
+------+----------------------+
1 row in set (0.01 sec)

mysql> delete from test where num=1;　 ← 删除表内的值
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;　 ← 确认删除结果
Empty set (0.01 sec)

mysql> drop table test;　 ← 删除表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;　 ← 查看表信息
Empty set (0.00 sec)　 ← 确认表已被删除

mysql> drop database test;　 ← 删除名为test的数据库
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;　 ← 查看已存在的数据库
Empty set (0.01 sec)　 ← 确认test数据库已被删除（这里非root用户的关系，看不到名为mysql的数据库）

mysql> exit　 ← 退出MySQL服务器
Bye

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

mysql> revoke all privileges on *.* from centospub@localhost;　 ← 取消centospub用户对数据库的操作权限
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='centospub' and host='localhost';　 ← 删除centospub用户
Query OK, 1 row affected (0.01 sec)

mysql> select user from mysql.user where user='centospub';　 ← 查找用户centospub，确认已删除与否
Empty set (0.01 sec)　 ← 确认centospub用户已不存在

mysql> flush privileges;　 ← 刷新，使以上操作生效
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye