redhat 5 配置MySQL

wu07p90b86

1.启动数据库: /etc/rc.d/init.d/mysqld start
2.修改根密码: mysqladmin -uroot password 'yourpassword'
3.修改该文件: /etc/rc.d/init.d/mysqld 的这句话：
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file="$mypidfile" --log-error="$errlogfile" >/dev/null --default-character-set=utf8 2>&1 &
(CentOS5.2里自带的Mysql5.0.45：
/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
>/dev/null --default-character-set=utf8 2>&1 &
)
4.在 /etc/my.cnf 里添加如下内容：
[ mysqld]
max_allowed_packet = 2M
max_connections = 1000
default-character-set=utf8
init_connect='SET NAMES utf8'
[client]
default-character-set=utf8
5.最后重启服务: /etc/init.d/mysqld restart
6.最终得到:
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45 Source distribution

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

smysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0

Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.45 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 15 sec

Threads: 1 Questions: 4 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.267
--------------
mysql> show variables like '%character%' ;
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

7.以上在
CentoOS5.4, mysql 5.0.77; CentOS5.2,mysql-5.0.45; CentOS5.0,mysql-5.0.22; CentOS4.6,mysql-5.0.27
上均测试通过.
8、为Mysql 创建账号linux上用root登陆，设置权限，grant 权限名（sqlserver和mysql不一样的，可以看手册知道，分所有的权限用all） on 库名（*表全部）.表名
to 要授权的用户名@"%"(%表示所有的IP，可以只些一个IP） identified by "密码"；
通常都是写
grant all on *.* to root@"%" identified by "密码";
或者GRANT ALL PRIVILEGES ON *.* TO root@"%%" IDENTIFIED BY '密码' WITH GRANT OPTION

命令方式的.注意每行后边都跟个 ； 表示一个命令语句结束.
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：
grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。

grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
如果你不想test2有密码，可以再打一个命令将密码消掉。
grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
为mysql用户更改密码
update mysql.user set password=password('新密码') where User="用户名" and Host="localhost";



方法一：
# /etc/rc.d/init.d/mysqld stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(’newpassword’) where USER=’root’;
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/rc.d/init.d/mysqld restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>
mysql>


在ubunte中看到这个 tsunli 写道:/etc/mysql/debian.cnf有缺省的用户/密码 尝试，里面居然是明文的密码 竟然成功登录了。
查询user表 use mysql select host, user, password from user 里面有4条记录～～～:-!
mysql> select host, user, password from user;
root的密码未知，不猜了，改掉:
mysql> set password for 'root'@'localhost' = password('yourpass');
重新登录OK！
mysql之删除重复数据
　　//删除id重复的数据，适合id是手工主键
delete person as a from person as a,
(
    select *,min(id) from person group by id having count(1) > 1
) as b
where a.id = b.id
　　
//查找重复的，并且除掉最小的那个
delete tb_person as a from tb_person as a,
(
select *,min(id) from tb_person  group by name having count(1) > 1
) as b
 where a.name = b.name and a.id > b.id;

<script type="text/javascript"></script>