Linux中的MySQL使用(CentOS默认安装）

Linux中的MySQL使用(CentOS默认安装）

一.修改/etc/my.cnf文件
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=utf8 增加
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql] 增加
default-character-set=utf8 增加
二.启动Mysql并设置隋系统启动
chkconfig mysqld on
chkconfig --list mysqld（查看系统服务）
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
2-5为on，OK。
/etc/rc.d/init.d/mysqld start
三.设置密码
 /usr/bin/mysqladmin -u root password '123456'

四.测试

1.应用密码为空测试：

mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

2.输入密码测试：

mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.77 Source distmysql> ribution

mysql>

五.删除匿名用户

1.查看用户，host，密码列表方法

select user,host,password from mysql.user;

2.删除匿名用户

 select user,host from mysql.user 查看用户信息

delete from mysql.user where user=""; 删除匿名用户

六.删除测试用数据库

1.show databases; 查看系统已经存在的数据库

2.drop database test;

七.对Mysql进行测试

1. grant all privileges on test.* to CentOSpub@localhost identified by '123456'; 建立对test数据库有完全操作权的名为centosput的用户

2.select user from mysql.user 查看用户存在与否

    -> ;
+-----------+
| user      |
+-----------+
| root      |
| centospub |
| root      |
| root      |
+-----------+

3.退出，并以centospub登录mysql服务器

exit

mysql -u centospub -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

4.建立test数据库 create database test;

5.show databases；查看

6.use test; 链接到数据库（Database changed)

7.create table test(num int,name varchar(50));建立表

8.show tables; 查看数据库中存在的表

9.insert into test valuse(1,'Hello World');插入新值

10.查看插入新值

select * from test
    -> ;
+------+--------------+
| num  | name         |
+------+--------------+
|    1 | Hello World! |
+------+--------------+

11.update test set name='Hello Everyone!'; 更新表信息

12.再次查看

mysql> select * from test
    -> ;
+------+-----------------+
| num  | name            |
+------+-----------------+
|    1 | Hello Everyone! |
+------+-----------------+
1 row in set (0.00 sec)

13.delete from test;删除值

14.drop table test;删除test表

15.show tables；查看表，确认

16.drop database test；删除test数据库

17.show databases;查看库，确认

八.删除测试用过的用户

1.应用root登录mysql数据库

2.revoke all privileges on *.* from centospub@localhost; 取消centospub用户对数据库的操作权限

3.delete from mysql.user where user='centospub' and host='localhost';删除

4.select user from mysql.user;查看，确认

5.flush privileges；刷新，使生效

九.重新启动http服务

/etc/rc.d/init.d/httpd restart