centOS Linux下用yum安裝mysql 第一篇：安裝和配置MySQL 第一步：安裝MySQL [root@192 local]# yum -y install mysql-server ← 安裝MySQL [root@192 local]# yum -y install php-mysql ← 安裝php-mysql 第二步：配置MySQL [root@192 local] #vim /etc/my.cnf ← 編輯MySQL的配置文件 [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 第二篇：啟動MySQL和初始環境設定 第一步:啟動MySQL服務 [root@192 local]#chkconfig mysqld on ← 設置MySQL服務隨系統啟動自啟動 [root@192 local]#chkconfig --list mysqld ← 確認MySQL自啟動 mysqld 0:關閉 1:關閉 2:啟用 3:啟用 4:啟用 5:啟用 6:關閉 ← 如果2--5為啟用(或on)的狀態就OK [root@192 local]#/etc/rc.d/init.d/mysqld start ← 啟動MySQL服務 初始化 MySQL 數據庫： Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h 192.168.0.1 password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses athttp://shop.mysql.com [確定] 啟動 mysqld： [確定] 第二步：MySQL初始環境設定 [1] 為MySQL的root用戶設置密碼 MySQL在剛剛被安裝的時候，它的root用戶是沒有被設置密碼的。首先來設置MySQL的root密碼。 [root@192 local]#mysql -u root ← 在沒設置密碼之時，用root用戶登錄MySQL服務器 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.95 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user,host,password from mysql.user; ← 查看用戶信息 mysql> select user,host,password from mysql.user; +------+-------------+----------+ | user | host | password | +------+-------------+----------+ | root | localhost | | | root | 192.168.0.1 | | | root | 127.0.0.1 | | | | localhost | | | | 192.168.0.1 | | +------+-------------+----------+ 5 rows in set (0.03 sec) mysql> set password forroot@localhost=password ('在這裡填入root密碼'); ← 設置root密碼 譬如，在我的系統中，我是如下設置： mysql> set password for root@localhost=password ('wangyun'); Query OK, 0 rows affected (0.01 sec) mysql> set password for [email protected]=password ('wangyun'); Query OK, 0 rows affected (0.01 sec) mysql> set password for [email protected]=password ('wangyun'); Query OK, 0 rows affected (0.01 sec) mysql> select user,host,password from mysql.user; ← 查看用戶信息 +------+-------------+------------------+ | user | host | password | +------+-------------+------------------+ | root | localhost | 5f2dfe4b07af795b | | root | 192.168.0.1 | 5f2dfe4b07af795b | | root | 127.0.0.1 | 5f2dfe4b07af795b | | | localhost | | | | 192.168.0.1 | | +------+-------------+------------------+ 5 rows in set (0.01 sec) mysql> exit ← 退出MySQL服務器 Bye [2] 測試設置的root密碼是否生效 [root@192 local]# mysql -u root ← 通過空密碼用root登錄 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ← 出現此錯誤信息說明密碼設置成功 [root@192 local]# mysql -u root-p ← 通過密碼用root登錄 Enter password: ← 在這裡輸入密碼 Welcome to the MySQL monitor. Commands end with ; or \g. ← 確認用密碼能夠成功登錄 Your MySQL connection id is 5 Server version: 5.0.95 Source distribution ... ... Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. [root@192 local]#mysql -u root -h 127.0.0.1 -p ← 通過密碼用root登錄 Enter password: ← 在這裡輸入密碼 Welcome to the MySQL monitor. Commands end with ; or \g.← 確認用密碼能夠成功登錄 Your MySQL connection id is 13 Server version: 5.0.95 Source distribution ...... Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit ← 退出MySQL服務器 Bye [3] 刪除匿名用戶 在MySQL剛剛被安裝後，存在用戶名、密碼為空的用戶。這使得數據庫服務器有無需密碼被登錄的可能性。為消除隱患，將匿名用戶刪除。 [root@192 local]# mysql -u root -p; ← 通過密碼用root登錄 Enter password: ← 在這裡輸入密碼 mysql> select user,host from mysql.user; +------+-------------+ | user | host | +------+-------------+ | root | 127.0.0.1 | | | 192.168.0.1 | | root | 192.168.0.1 | | | localhost | | root | localhost | +------+-------------+ 5 rows in set (0.03 sec) mysql> delete from mysql.user where user=''; ← 刪除匿名用戶 Query OK, 2 rows affected (0.04 sec) mysql> select user,host from mysql.user; ← 查看用戶信息 +------+-------------+ | user | host | +------+-------------+ | root | 127.0.0.1 | | root | 192.168.0.1 | | root | localhost | +------+-------------+ 3 rows in set (0.00 sec) mysql> flush privileges; ← 刷新，使以上操作生效 mysql> exit; ←退出MySQL服務器 Bye 第三篇：測試 經過第一篇和第二篇這二部分的設置，MySQL就可以正常試用。 好了，第三篇的內容都不是必須的了！ 我們來測試MySQL，讓我們學習或復習常用的SQL語句。 [root@192 local]#mysql -u centospub-p; ← 通過密碼用root登錄 Enter password: ← 在這裡輸入密碼 mysql> grant all privileges on test.* to centospub@localhost identified by '在這裡定義密碼'; ← 建立對test數據庫有完全操作,權限的名為centospub的用戶 譬如，在我的系統中，設置如下： mysql>grant all privileges on test.* to centospub@localhost identified by'wangyun'; Query OK, 0 rows affected (0.03 sec) mysql> select user from mysql.user where user='centospub';← 確認centospub用戶的存在與否 |+----------+ | user | +-----------+ | centospub | +-----------+ 1 row in set (0.02 sec) mysql> exit; Bye [root@192 local]# mysql -u centospub -p; ← 用新建立的centospub用戶登錄MySQL服務器 Enter password: ← 在這裡輸入密碼 mysql>create database test; ← 建立名為test的數據庫 mysql> show databases; ← 查看系統已存在的數據庫 +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.01 sec) mysql> use test ← 連接到數據庫 Database changed mysql> create table test -> ( -> num int , ← 在數據庫中建立表 -> name varchar(50) -> ); Query OK, 0 rows affected (0.14 sec) mysql> show tables; ← 查看數據庫中已存在的表 +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.02 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.14 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.01 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 然後，刪除測試用過的遺留用戶 [root@192 local]# mysql -u root -p; Enter password: mysql> revoke all privileges on *.* from centospub@localhost; ← 取消centospub用戶對數據庫的操作權限 Query OK, 0 rows affected (0.01 sec) mysql> delete from mysql.user where user='centospub'and host='localhost'; ← 刪除centospub用戶 Query OK, 1 row affected (0.14 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.00 sec) mysql> exit; Bye [root@192 local]# /etc/rc.d/init.d/httpd restart ← 重新啟動HTTP服務 ← 注意：語句後面無需;分號 停止 httpd： [確定] 啟動 httpd： [確定] 第四篇：日志位置和網絡服務器 一、日志位置 mysql 默認的日志位置，mysql日志文件默認存放位置 /var/log/mysqld.log [root@192 ~]# cd /var/log [root@192 log]# pwd /var/log [root@192 log]# ls acpid boot.log.2 conman.old dmesg maillog messages.2 ppp samba setroubleshoot tallylog anaconda.log boot.log.3 cron faillog maillog.1 messages.3 prelink scrollkeeper.log spooler vbox anaconda.syslog boot.log.4 cron.1 gdm maillog.2 messages.4 rpmpkgs secure spooler.1 wtmp anaconda.xlog brcm-iscsi.log cron.2 httpd maillog.3 mysqld.log rpmpkgs.1 secure.1 spooler.2 xen audit btmp cron.3 lastlog maillog.4 news rpmpkgs.2 secure.2 spooler.3 Xorg.0.log boot.log clumond.log cron.4 libvirt messages piranha rpmpkgs.3 secure.3 spooler.4 Xorg.0.log.old boot.log.1 conman cups mail messages.1 pm rpmpkgs.4 secure.4 squid yum.log 二、數據庫服務器 mysql是支持網絡的數據庫服務器，經常需要通過互聯網去訪問，mysql默認使用tcp協議，端口是3306 [root@192 log]# cat mysqld.log 130503 11:25:03 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 130503 11:25:07 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 130503 11:25:09 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 130503 11:25:09 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 130503 11:25:12 InnoDB: Started; log sequence number 0 0 130503 11:25:12 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.95' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution [root@192 log]# netstat -tupln Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:2208 0.0.0.0:* LISTEN 3569/hpiod tcp 0 0 0.0.0.0:5989 0.0.0.0:* LISTEN 3905/cimserver tcp 0 0 0.0.0.0:11111 0.0.0.0:* LISTEN 4783/ricci tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3549/mysqld tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 2854/portmap tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 13747/httpd tcp 0 0 0.0.0.0:16851 0.0.0.0:* LISTEN 4669/modclusterd tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 3888/dnsmasq tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3590/sshd tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 3602/cupsd tcp 0 0 0.0.0.0:952 0.0.0.0:* LISTEN 2890/rpc.statd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 3643/sendmail: acce tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 13747/httpd tcp 0 0 127.0.0.1:2207 0.0.0.0:* LISTEN 3574/python udp 0 0 0.0.0.0:946 0.0.0.0:* 2890/rpc.statd udp 0 0 192.168.122.1:53 0.0.0.0:* 3888/dnsmasq udp 0 0 0.0.0.0:949 0.0.0.0:* 2890/rpc.statd udp 0 0 0.0.0.0:67 0.0.0.0:* 3888/dnsmasq udp 0 0 0.0.0.0:68 0.0.0.0:* 4516/dhclient udp 0 0 0.0.0.0:5353 0.0.0.0:* 3846/avahi-daemon: udp 0 0 0.0.0.0:111 0.0.0.0:* 2854/portmap udp 0 0 0.0.0.0:631 0.0.0.0:* 3602/cupsd udp 0 0 0.0.0.0:34943 0.0.0.0:* 3846/avahi-daemon:
