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 -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!
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
#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
#mysqladmin password -uroot '123456'
(1)、把mysql服务进程停掉：
#service mysqld stop 【1：service mysql stop】
(2)、把/var/lib/mysql整个目录移到/dbdata/mysql5.1 【2：】
#mv /var/lib/mysql /dbdata/mysql5.1
这样就把mysql的数据文件移动到了/dbdata/mysql5.1 下
为 保证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 【3：/etc/rc.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 （加上此行）
Starting MySQL..Manager of pid-file quit without updating file. failed!
查过网上的资料都不能正常解决。
修改/etc/my.cnf文件：【4：/etc/my.cnf】
[client]
#password = your_password
port =
3306
socket =
/tmp/mysql.sock
datadir =
/data1/mysql/var/ #这个不能加在上面
[mysqld]
port =
3306
socket =
/tmp/mysql.sock
datadir =
/data1/mysql/var/ #只要这里加就可以了，
修改完后如果报上面的错误，那么请修改 /etc/init.d/下mysql文件，记得用ultraedit或者直接在SSH下用VI修改：
将
datadir=/usr/local/mysql/var
改为：
datadir=/data1/mysql/var/ #你所指定的路径
--------------------------------------------------------------------------------------------
·编辑配置文件，支持中文。
#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
#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
登陆时,指定使用该数据库
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;
用户及权限控制
1、查看当前的用户
use mysql;
select * from user;
这样就可以了
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 http://www.360doc.com/mailto:root@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for http://www.360doc.com/mailto:root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO http://www.360doc.com/mailto:'root'@'localhost' 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 登陆：
1、grant:
mysql> GRANT ALL PRIVILEGES ON *.* TO user;@localhost
mysql> flush privileges; 告诉服务器,重读授权表user
------------------------------
删除数据库
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
mysql5.1创建用户:
上面建立的用户可以在任何地方登陆。
如果要限制在固定地址登陆，比如localhost 登陆：
CREATE USER http://www.360doc.com/mailto:yy@localhost IDENTIFIED BY '123';
Query OK, 0 rows affected (0.01 sec)