CentOS MySQL 5.1 配置

CentOS MySQL配置

安装MySQL

sudo yum -y install mysql-server

mysql目录为:

/usr/share/mysql

mysql配置文件路径为:

/etc/my.cnf

验证MySQL

rpm -qa | grep mysql-server

配置字符集

编辑配置文件

sudo vim /etc/my.cnf

在[mysqld]和[client]节点下添加(没有client则新建)

#mysql 5.1
default-character-set = utf8
#mysql 5.5
character-set-server = utf8

自启动配置

打开自启动

sudo chkconfig mysqld on

查看自启动,2-5为on表示成功

sudo chkconfig --list mysqld

配置防火墙

编辑iptables

sudo vim /etc/sysconfig/iptables

添加如下规则

#mysql port
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

保存退出后重启防火墙

sudo service iptables restart

初始化mysql

启动mysql

sudo service mysqld start

初始化mysql环境

mysql -u root

查看mysql的用户

-- 5.1
select user,host,password from mysql.user;
-- 5.7
select user,host,authentication_string from mysql.user;

修改root密码

set password for root@localhost=password('123456');
set password for root@127.0.0.1=password('123456');

退出后登录root用户输入密码验证

mysql -u root -p

删除匿名用户

查看是否有匿名用户

select user,host from mysql.user;

删除匿名用户

delete from mysql.user where user='';

刷新,使操作生效

flush privileges;

插入新用户

-- mysql5.1
insert into mysql.user(Host,User,Password) values ("localhost","gavinandre",password("123456"));
-- mysql5.7
CREATE USER 'gavinandre'@'localhost' IDENTIFIED BY '123456';

flush privileges;

创建数据库

create database `test_database` default character set utf8 collate utf8_general_ci;

导入sql文件

use test_database;
source /directory/test_database.sql;
show tables;

flush privileges;

添加权限

指定用户赋予所有权限

grant all privileges on test_database.* to 'gavinandre'@'localhost' identified by '123456' with grant option;

指定用户开通外网所有权限

grant all privileges on *.* to 'gavinandre'@'%' identified by '123456' with grant option;

删除赋予的权限

revoke all privileges on test_database.* from 'gavinandre'@'localhost';
revoke grant option on test_database.* from 'gavinandre'@'localhost';

flush privileges;

查询权限

show grants for 'gavinandre'@'localhost';

select * from mysql.user \G;

mysql常用命令

启动

sudo service mysqld start

关闭

sudo service mysqld stop

重启

sudo service mysqld restart