MYSQL 安装配置
下载软件包：
MySQL-client-community-5.1.42-0.rhel5.i386
MySQL-devel-community-5.1.42-0.rhel5.i386
MySQL-shared-community-5.1.42-0.rhel5.i386
MySQL-server-community-5.1.42-0.rhel5.i386
安装软件包：
[root@rhel5a tmp]# rpm -ivh MySQL-*.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-community ########################################### [ 25%]
2:MySQL-client-community ########################################### [ 50%]
3:MySQL-devel-community ########################################### [ 75%]
4:MySQL-server-community ########################################### [100%]
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 rhel5a 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.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at
Support MySQL by buying support/licenses from
Starting MySQL...[ OK ]
Giving mysqld 2 seconds to start
------------------------------------------------------
确认安装成功并启动：mysql 监听的端口为3306
#netstat -na |grep 3306
[root@rhel5a tmp]# ps -ef|grep mysqld
root 10869 1 0 02:06 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/rhel5a.pid
mysql 10924 10869 0 02:06 pts/1 00:00:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/rhel5a.err --pid-file=/var/lib/mysql/rhel5a.pid
root 10974 9479 0 02:11 pts/1 00:00:00 grep mysqld
相关配置事项：
1、了解下面的文件路径：
配置文件：/usr/share/mysql/my-huge.cnf
MySQL启动脚本/etc/init.d/mysql /etc/rc.d/init.d/mysql
默认数据文件存放目录 /var/lib/mysql
2、注册为 service 服务
#chkconfig --add mysql
以后就可以使用
#service start mysql 启动或停止mysql 数据库了
是否要设置为开机自启动：
#chkconfig mysql on/off
# chkconfig mysql --list
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
3、修改初始用户密码：
#mysqladmin password -uroot '123456'
4、重新设置数据文件位置：
(1)、把mysql服务进程停掉：
#service mysqld stop
(2)、把/var/lib/mysql整个目录移到/dbdata/mysql5.1
#mv /var/lib/mysql /dbdata/mysql5.1
这样就把mysql的数据文件移动到了/dbdata/mysql5.1 下
(3)、编辑mysql的配置文件/usr/share/mysql/my-huge.cnf
为 保证mysql能够正常工作，需要指明mysql.sock文件的产生位置。修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：
/dbdata/mysql5.1/mysql/mysql.sock 。操作如下：
命令: vi /usr/share/mysql/my-huge.cnf (用vi工具编辑my.cnf文件，找到下列数据修改之)
# the mysql server
[mysqld]
port = 3306
#socket = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用"#"注释此行）
socket = /dbdata/mysql5.1/mysql/mysql.sock （加上此行）
(4)、修改mysql启动脚本/etc/rc.d/init.d/mysql
最后，需要修改mysql启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：
/dbdata/mysql5.1
[root@test1 etc]# vi /etc/rc.d/init.d/mysqld
#datadir=/var/lib/mysql （注释此行）
datadir=/dbdata/mysql5.1 （加上此行）
5、字符集的支持：
·编辑配置文件，支持中文。
#vi /usr/share/mysql/my-huge.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#character-set-client=gb2312 ［以前用这一行，现在提示重复定义！？顾不用了。］
character-set-server=gb2312
[mysql]
no-auto-rehash
default-character-set=gb2312
# Remove the next comment character if you are not familiar with SQL
#safe-updates
6、登陆测试：
#mysql -u root -p
Enter password: （输入密码）
mysql> show database
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| shop |
+----------------+
1 row in set (0.01 sec)
mysql> desc shop;
+---------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| article | int(4) unsigned zerofill | NO | PRI | 0000 | |
| dealer | char(20) | NO | PRI | | |
| price | double(16,2) | NO | | 0.00 | |
+---------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
---------------------------------
创建一个数据库
mysql> CREATE DATABASE menagerie;
在Unix下，数据库名字是区分大小写,windows 下不需要.
mysql> USE menagerie 使用该数据库
Database changed
登陆时,指定使用该数据库
shell> mysql -h host -u user -p menagerie
Enter password: ********
-------------------------
查看数据库中有哪些表
mysql> SHOW TABLES;
Empty set (0.00 sec)
---------------------------------
装载数据:
你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以在CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件表示这些，使用\N。例如，对Whistler鸟的记录看起来像这样的(这里在值之间的空白是一个单个的定位字符)：
Whistler Gwen bird \N 1997-12-09 \N
为了装载文本文件“pet.txt”到pet表中，使用这个命令：
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
------------------------------------
用户及权限控制
1、查看当前的用户
use mysql;
select * from user;
这样就可以了
2、查看当前登陆用户:
mysql> select user();
+----------------+
| user() |
+----------------+
| |
+----------------+
1 row in set (0.00 sec)
3、查看用户权限:
mysql> show grant for root;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant for root' at line 1
mysql> show grants for ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
------------------
创建用户:
mysql> create user test identified by "test";
Query OK, 0 rows affected (0.01 sec)
yy表示你要建立的用户名，后面的123表示密码
上面建立的用户可以在任何地方登陆。
如果要限制在固定地址登陆，比如localhost 登陆：
CREATE USER IDENTIFIED BY '123';
用户权限控制:
1、grant:
mysql> GRANT ALL PRIVILEGES ON *.* TO user;@localhost
grant select,insert,update,delete on *.* to " Identified by "abc";
grant select,insert,update,delete on *.* to " Identified by "abc";
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"
mysql> flush privileges; 告诉服务器,重读授权表user
2、权限回收：
mysql>REVOKE privileges (columns) ON what FROM account;
mysql>REVOKE SELECT ON db.* FROM ;
删除test帐号从本机查询db数据库的权限。
------------------------------
删除数据库
mysql> drop database test;
删除用户:
mysql> DROP USER test;
删除表
mysql> drop table shop;
Query OK, 0 rows affected (0.00 sec)
备份及恢复
登录时指定编码方式
mysql -uroot -p3376211849 -h127.0.0.1 -P3308 cmxt --default-character-set=gbk
mysql> show variables like '%char%'; 查看数据当前的编码
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /home/coremail/mysql/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
数据的导出：
mysqldump -n --default-character-set=utf8 -ucoremail -p密码 cm30 > cm30.sql
mysqldump -n --default-character-set=utf8 -ucoremail -p密码 cm_fng_db > cm_fng_db.sql
数据的导入：
◦drop 掉无用的数据库
◦create database cmxt2 以及 create database cm_fng_db
◦使用一下命令导入 mysql 数据
mysql --default-character-set=utf8 cmxt2 < cm30.sql
mysql --default-character-set=utf8 cm_fng_db < cm_fng_db.sql
mysql4.1创建用户：
grant all privileges on *.* to identified by ;
mysql5.1创建用户:
yy表示你要建立的用户名，后面的123表示密码
上面建立的用户可以在任何地方登陆。
如果要限制在固定地址登陆，比如localhost 登陆：
CREATE USER IDENTIFIED BY '123';
mysql> create user test identified by "test";
Query OK, 0 rows affected (0.01 sec)
