服务器安装mysql

1. 如果没wget
yum -y install wget
2. 下载 mysql-community-release-el7-5.noarch.rpm
wget http://repo.mysql.com/mysql-community-release-el7.rpm
3. 通过rpm安装mysql
rpm -ivh mysql-community-release-el7.rpm
4. 安装好之后，在继续通过yum软件包，出现的提示都按Y安装
yum install mysql-server
5. 安装好之后，确认一下是否安装好了。
rpm -qa|grep mysql
6. 启动mysql服务, 并查看状态 active（running）
systemctl start mysqld.service
systemctl status mysqld.service
7. 查看默认密码
grep ‘temporary password‘ /var/log/ysqld.log
8. 运行mysql
mysql -uroot -p
9. 修改密码，密码必须为强密码，否则会保错
show databases;
alter user ‘root‘@‘localhost‘ identified by ‘root123‘;   #报错
alter user ‘root‘@‘localhost‘ identified by ‘admin@123‘;
10. 刷新权限
FLUSH PRIVILEGES;

连接到本机上的MYSQL
mysql -uroot -proot

连接到远程主机上的MySQL
mysql -h127.0.0.1 -uroot -p123

1. 2003报错解决方案
#查看网络端口信息 - local address: 0.0.0.0：3306, programe name
netstat -ntpl
#查看防火墙的状态 - 如果3306是drop状态，或者无3306端口，说明3306端口设置问题
iptables -vnL
解决方法：启动服务，添加监听端口
service mysqld start;
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

拓展知识：
service iptables stop   #临时关闭防火墙
service iptables start  #开启防火墙
chkconfig iptables off  #开机不启动防火墙

2. 1130报错解决方案 - 授权MySQL远程登陆
方法一：（推荐）
mysql -u root -proot
GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘yourpassword‘ WITH GRANT OPTION;
FLUSH PRIVILEGES;
方法二：
mysql -uroot -proot
use mysql;
update user set host = ‘%‘ where user = ‘root‘;
select host, user from user;

#新建数据库
CREATE DATABASE test;
show databases;

#新建表
use test;
show tables;

CREATE TABLE employee (
	id int(10),
	name char(20),
	phone varchar(12)
);

#插入数据
INSERT INTO employee(id,name,phone) VALUES(01,‘Tom‘,110110110);

#新建数据库
CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

#查询、创建、删除用户
select user,host from mysql.user;
create user ‘test‘@‘localhost‘ identified by ‘1234‘;   --test改为‘%’表示在任何数据库都能登录
--drop user test@‘localhost‘ ;
--drop user test@‘%‘ ;

#修改用户密码
-- mysql_5.7版本以前
--方法1
set password for test =password(‘1122‘);  
--方法2，需刷新权限
update  mysql.user set password=password(‘1234‘) where user=‘test‘
flush privileges;
 
--mysql_5.7以后
alter user ‘test‘@‘localhost‘ identified by ‘1122‘;


#为用户分配权限
--1.查看用户权限
show grants for test;

--2.mysql_5.7以前
--授予用户test通过外网IP对数据库“testdb”的全部权限
grant all privileges on ‘testdb‘.* to ‘test‘@‘localhost‘ identified by ‘1234‘; 
flush privileges;

grant create,alter,drop,select,insert,update,delete on testdb.* to test@‘localhost‘;
flush privileges;

--3.mysql_5.7以后
--赋予用户test通过外网IP对数据库“testdb”的全部权限
grant all privileges on testdb.* to ‘test‘@‘localhost‘; 
flush privileges;

grant create,alter,drop,select,insert,update,delete on testdb.* to ‘test‘@‘localhost‘;
flush privileges;

--4.删除用户权限
revoke all privileges on testdb.* from ‘test‘@‘localhost‘;  --消除全部权限
revoke select on testdb.* from ‘test‘@‘localhost‘;  --消除单个权限


#创建表空间
create tablespace tablespacename datafile ‘d:\data.dbf‘ size 64m;
--为用户分配默认表空间
alert user test default tablespace tablespacename;