mysql-创建用户及权限分配

-- root用户执行
GRANT ALL ON *.* TO 'system' @'%' WITH GRANT OPTION;
REVOKE ALL ON *.* FROM 'system' @'%';


-- 创建用户
create user 'testdbuser2' @'%' IDENTIFIED by '123456' ;
drop user 'testdbuser2' @'%';

-- 创建数据库
create database testdb2;
drop database testdb2;

-- 授权
GRANT ALL ON testdb2.* TO 'testdbuser2' @'%';
REVOKE ALL ON testdb2.* FROM 'testdbuser2' @'%';

解压安装mysql

1.下载二进制软件包

https://dev.mysql.com/downloads/mysql/

1
2
3

windows安装zip包即可 

linux 下载Generic版本(注意glibc版本,可以通过 rpm -q glibc 查看)

2. 编写my.cfg并初始化数据库

my.cfg

[mysqld]
user = p2m
basedir = ./
datadir = ./data
port=3306
socket=/tmp/mysql.sock
max_connections=20
# 数据库编码
character-set-server=utf8
# 默认存储引擎
default-storage-engine=INNODB

[client]
port=3306
scoket=/tmp/mysql.sock

初始化数据库

export PATH=$PATH:./bin
mysqld --defaults-file=my.conf --initialize --console

执行完成时会显示初始化的root密码，记录下来

启动数据库

mysqld --defaults-file=my.conf --console

-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

-- 远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'；
flush privileges;

3.客户端访问

1 2	export PATH=$PATH:./bin mysql -uroot -p[tempPassWord]

测试表及数据

CREATE TABLE dept
(
 deptno INT PRIMARY KEY,
 dname VARCHAR(14),
 loc VARCHAR(13)
);

CREATE TABLE emp
(
 EMPNO INT(4) PRIMARY KEY,
 ENAME VARCHAR(10),
 JOB VARCHAR(9),
 MGR INT(4),
 HIREDATE DATE,
 SAL DOUBLE,
 COMM DOUBLE,
 deptno INT);

CREATE TABLE salgrade
(
 grade INT PRIMARY KEY,
 losal INT,
 hisal INT);

INSERT
	INTO
	emp
VALUES

(7369,
'SMITH',
'CLERK',
7902,
DATE('1980-12-17'),
800,
NULL,
20);

INSERT
	INTO
	emp
VALUES

(7499,
'ALLEN',
'SALESMAN',
7698,
DATE('1981-2-20'),
1600,
300,
30);

INSERT
	INTO
	emp
VALUES

(7521,
'WARD',
'SALESMAN',
7698,
DATE('1981-2-22'),
1250,
500,
30);

INSERT
	INTO
	emp
VALUES

(7566,
'JONES',
'MANAGER',
7839,
DATE('1981-4-2'),
2975,
NULL,
20);

INSERT
	INTO
	emp
VALUES

(7654,
'MARTIN',
'SALESMAN',
7698,
DATE('1981-9-28'),
1250,
1400,
30);

INSERT
	INTO
	emp
VALUES

(7698,
'BLAKE',
'MANAGER',
7839,
DATE('1981-5-1'),
2850,
NULL,
30);

INSERT
	INTO
	emp
VALUES

(7782,
'CLARK',
'MANAGER',
7839,
DATE('1981-6-9'),
2450,
NULL,
10);

INSERT
	INTO
	emp
VALUES

(7788,
'SCOTT',
'ANALYST',
7566,
DATE('1987-4-19'),
3000,
NULL,
20);

INSERT
	INTO
	emp
VALUES

(7839,
'KING',
'PRESIDENT',
NULL,
DATE('1981-11-17'),
5000,
NULL,
10);

INSERT
	INTO
	emp
VALUES

(7844,
'TURNER',
'SALESMAN',
7698,
DATE('1981-9-8'),
1500,
0,
30);

INSERT
	INTO
	emp
VALUES

(7876,
'ADAMS',
'CLERK',
7788,
DATE('1987-5-23'),
1100,
NULL,
20);

INSERT
	INTO
	emp
VALUES

(7900,
'JAMES',
'CLERK',
7698,
DATE('1981-12-3'),
950,
NULL,
30);

INSERT
	INTO
	emp
VALUES

(7902,
'FORD',
'ANALYST',
7566,
DATE('1981-12-3'),
3000,
NULL,
20);

INSERT
	INTO
	emp
VALUES

(7934,
'MILLER',
'CLERK',
7782,
DATE('1982-1-23'),
1300,
NULL,
10);

INSERT
	INTO
	dept
VALUES

(10,
'ACCOUNTING',
'NEW YORK');

INSERT
	INTO
	dept
VALUES

(20,
'RESEARCH',
'DALLAS');

INSERT
	INTO
	dept
VALUES

(30,
'SALES',
'CHICAGO');

INSERT
	INTO
	dept
VALUES

(40,
'OPERATIONS',
'BOSTON');

INSERT
	INTO
	salgrade
VALUES(1,
700,
1200);

INSERT
	INTO
	salgrade
VALUES(2,
1201,
1400);

INSERT
	INTO
	salgrade
VALUES(3,
1401,
2000);

INSERT
	INTO
	salgrade
VALUES(4,
2001,
3000);

INSERT
	INTO
	salgrade
VALUES(5,
3001,
9999);