DBA 数据库管理员

• 小公司 - 不设置专职DBA
• 大公司 - 专职DBA：维护数据库稳定，解决请求变慢，数据丢失，安全等问题。
  • DBA -> 业务架构师
  • DBA -> 运维专家
  • DBA -> 数据库研发

DBA 工作职责

• 基础运维
  • 安装部署
  • 监控
  • 故障处理
• 安全运维
  • 数据备份与恢复
  • 安全访问、安全漏洞
  • 审计
• 性能调优
  • 数据库优化
  • 容量评估、软硬件升级
• 开发支持
  • 存储方案制定
  • 数据库设计
  • 数据库变更、SQL Review
• 流程与培训
  • 数据库开发规范
  • 运维流程标准化
  • 业务培训

满足互联网业务的严格要求：

• 高性能（数据库 很重要）
• 高可用（业务运行不中断）
• 可扩展（数据库拓展 支撑海量数据和业务）
• 安全性（敏感 核心业务数据）
• 数据备份(amazon为防止黑客攻击或硬盘损坏，每天增量备份，隔几天全备份)

DBA 专业技能

• 数据库原理
• Linux与Shell
• 计算机体系结构
• 网络原理
• 数据库系统与操作
• 服务器硬件
• 业务架构设计

数据库 基本知识

数据库分类：
关系型数据库SQL：oracle MySQL
非关系型数据库NoSQL（Not only sql）：mongodb redis memcached

MySQL是C/S架构的软件：
服务器 mysqld
客户端

库-表-行

数据库服务器 可以管理n个库,每个库有n个表,表里是一行行的数据

CRUD：增加(Create)、读取查询(Retrieve)、更新(Update) 删除(Delete)

增 增加一行记录
删 删除一行。删除最小单位为“行”，不能单独把某一具体行的某一列删除(只能 修改为null)
改
查

SQL（ Structured Query Language）结构化查询语言

数据库管理系统通过 SQL 语言来管理数据库中的数据。

• DDL
  • CREATE TABLE
  • DROP TABLE
  • ALTER TABLE
• DML
  • SELECT FROM TABLE【最频繁】
  • INSERT INTO TABLE
  • UPDATE TABLE SET
  • DELETE FROM TABLE
• DCL
  • GRANT
  • REVOKE
• TCL
  • COMMIT
  • ROLLBACK

linux安装数据库

# 下载安装数据库服务器
sudo apt-get install mysql-server-5.6

# 启动MySQL服务 2种方法
sudo /etc/init.d/mysql start
sudo service mysql start

# 停止MySQL服务 2种方法
sudo /etc/init.d/mysql stop
sudo service mysql stop

# 重启
sudo service mysql restart

# 查看状态
sudo /etc/init.d/mysql status

windows安装数据库服务器：自定义安装， 把 数据 程序 分开放在两个文件夹

连接过程

任何满足mysql通信规范的软件都可以作为客户端来连接服务器。
常用的客户端：
命令行客户端（mysql自带）
navicat（图形化）
phpMyAdmin（web）

1.运行服务器端

windows启动服务进程mysqld.exe

2.运行客户端

在mysql\bin目录运行mysql.exe
或配置环境变量用cmd输入mysql.exe运行

3.连接 - 方式1 - 使用Socket连接（本地连接）

# 需要指定参数：socket文件(文件权限必须是777)   用户名    密码( -p回车后 再输入密码更安全)
mysql -S/tmp/mysql.sock -uroot -p
#socket一般存储路径为：/tmp/mysql.sock
# 如果找不到文件可先进行tcp连接 然后通过如下命令查找
show global variables like 'socket';

3.连接 - 方式2 - 使用命令行（TCP/IP远程连接）MySQL服务器

客户端发送SQL指令

mysql -hlocalhost -P3306 -uroot -p
#回车 输入密码 登录验证
#DBA运维管理工具大多使用高效的命令行方式：多台机器可以同时操作
#MySQL命令行里有丰富的扩展参数      -h省略即localhost     -u root可写成-uroot
#远程连接时要指定IP和端口

4.连接成功 - 执行语句

服务器端接收到SQL指令-处理指令-返回结果
客户端收到结果-显示结果

status;
# 打印数据库状态 ：服务器编码 数据库编码 客户端编码
#每条语句后 跟分号;或者\g  回车执行  否则报1064:SQL syntax错误


#语句中没有分号 则可用 \c主动取消本语句的执行(即什么都不执行)
mysql> select 222\c

# 如果\c之前有分号，则语句依然执行！

mysql> select 1;\c
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)


show processlist;
# 展示当前连接


\q
quit;
exit;
#断开连接 释放服务器资源

有密码且已登录 这样改密码:

方法1: UPDATE直接编辑user表

use mysql;
update user set password=password("newpass") where user='root' and host='localhost';
flush previliges;

方法2: SET PASSWORD命令

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
flush previliges;

有密码但未登录 改密码:

windowes下cmd进入这个目录执行MySQL\bin>

mysqladmin -u root -p老密码 password "pass"

本来为空密码 设置密码

mysqladmin -u root password "newpass"

丢失root密码 重置密码:

以安全模式启动mysql，这样不需要密码可以直接以root身份登录，然后重设密码。

停掉MySQL服务 关闭正在运行的MySQL
Ubuntu和Debian下:

sudo service mysql stop

CentOS、RHEL、Fedora下:

sudo service mysqld stop

以安全模式启动mysql

sudo mysqld_safe --skip-grant-tables --skip-networking &

--skip-networking是避免其他人远程无密码登录MySQL。

直接用root登录，无需密码：

mysql -u root

设密码：

mysql> use mysql;
mysql> update user set password=PASSWORD("mynewpassword") where User='root';
mysql> flush privileges;
mysql > quit

quit不需要分号。

重启服务：

sudo service mysql restart  #Ubuntu和Debian下
sudo service mysqld restart #CentOS、RHEL、Fedora下

用新密码登录：

mysql -u root -pnewpass

-p和密码间不能有空格。

windwos下重置root密码

cmd命令
cd 到mysqlbin目录
输入命令

mysqld --skip-grant-tables

如果没有出现提示信息，就对了

再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysqlbin目录。
输入mysql 将出现MySQL提示符 >
连接权限数据库>use mysql;
改密码：> update user set password=password("123456") where user="root";
刷新权限（必须的步骤）>flush privileges;
退出 >q

关闭所有mysql程序和服务
重新打开用新的root密码登录

仅支持Ubuntu和Debian重置root密码的方案:

在Ubuntu和Debian系统中有一个debian-sys-maint用户，Debian类系统下一些系统脚本对mysql的操作是通过这个用户完成的。所以我们可以通过这个用户来修改root密码。用户的密码可以在/etc/mysql/debian.cnf下找到：

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

用该用户登录mysql后，可以修改密码：

sudo mysql -u debian-sys-maint -p

查看帮助

#按照层次查看帮助
? contents

? data types #继续查看某一具体category (如查看mysql支持的数据类型data types)

? INT #继续查看 更具体的介绍

#按关键字 快速查阅帮助
? show  #查看show命令都能看什么东西
? create table #查看建表的语法


mysql> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)] [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length) [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.

Rules for permissible table names are given in
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

基本操作-库

mysql> show databases; #当前数据库服务器有哪些数据库

mysql> create database php; #创建1个名为php的数据库

mysql> use php #选中1个数据库（这条语句末尾可以不加分号）

mysql> show tables; #查看php库内有什么表:空表就回显Empty set

mysql> drop databese test; #删除test数据库

mysql本身并没有提供[修改数据库名]的方法！！不能修改数据库名！

基本操作-表

在php库下创建表:

mysql> use php
mysql> create table stu (
    id int,
    name varchar(20),
    age int,
    area varchar(20)
);

CREATE TABLE 表名
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

修改表:

所有的数据表的修改SQL语句 开头都是ALTER TABLE+表名

重命名表:

ALTER TABLE tb_name RENAME {TO|AS} tb_new_name;

mysql> rename table tb_name to tb_new_name; #批量修改多个表的名称

尽量不要随意修改表的名称。对视图有影响

表中新增(ADD)列:

ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];

列 即 字段
不指定 FIRST|AFTER col_name 时,新增字段默认会插入到表的最下面
字段定义后可指定FIRST，则新增字段置于最上面;
也可在字段定义后指定AFTER col_name 则新增字段置于col_name之下

表中丢弃(DROP)列:

ALTER TABLE tb_name DROP col_name1,DROP col_name2; #可一次性删除多个列

修改表中列:
修改列定义 即修改列的属性。

ALTER TABLE tb_name MODIFY col_name column_definition [FIRST|AFTER col_name];

比如：

ALTER TABLE test ADD id SMALLINT unsigned KEY auto_increment first;

key 表示主键(无需primary) 如果要修改的列已经是主键了，则修改列定义的时候再加上主键字段会报错
first 让本字段成为本表的第一个字段。

主键:表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。

修改列名:

ALTER TABLE tb_name CHANGE col_name col_new_name column_definition [FIRST|AFTER col_name];

修改列名时，新列名后面要有列定义(数据类型)

修改列顺序:
其实同上，比如：

#把join_time列放到role列后面
ALTER TABLE `user` CHANGE `join_time` `join_time` DATE NULL DEFAULT NULL AFTER `role`;

修改约束

添加主键约束
PRIMARY KEY

ALTER TABLE tb_name ADD [CONSTRAIN[symbol]] PRIMARY KEY [index.type] (index_col_name,.....);

删除主键约束

ALTER TABLE tb_name DROP [index.type] PRIMARY KEY;

不需要指定列名，因为每个表只有一个主键(注意！不意味着主键只有一列)

添加唯一约束
UNIQUE

ALTER TABLE tb_name ADD [index.type] unique(col_name);

删除唯一约束

ALTER TABLE tb_name DROP [index.type] unique(col_name);

DEFAULT

ALTER TABLE tb_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT};
for example:
ALTER TABLE users ALTER age SET DEFAULT 20;

添加外键约束
FOREIGN KEY

ALTER TABLE tb_name ADD [CONSTRAIN[symbol]] FOREIGN KEY [index_name](index_col_name,...)reference_definition;
for example:
ALTER TABLE users add FOREIGN KEY (pid) REFERENCE provinces (id);

删除外键约束

ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;

删除表:

mysql> drop table newstu;

表的描述
desc 表名; #可查看到该表的字段信息,低版本好像可以用description代替desc
如

desc newstu;

回显一个表的

字段名   字段数据类型  是否可空
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

基本操作-行

注意编码 字符集
我们配置mysql建表用UTF-8编码，而win下的窗口是gbk编码(如cmd)
操作数据时要 声明[客户端]正在使用的字符集!

mysql> set names gbk; #声明[客户端]使用的是GBK字符集

新增一条记录
新增一行数据

#不指定插入字段名称时 values后的值要和[字段的顺序]一致
insert into newstu (id,name) values(3,'张三');

#指定插入的字段名称为 id,name时
这些字段可以不写在[字段列表]:可空字段、非空且有默认值的字段、自增字段
(插入新记录即自动设置为:     NULL       默认值               自增到相应数字)

insert into newstu (id,name) values(3,'张三');

插入 多条记录
插入 多行数据

insert into newstu (id,name) values (3,"张三"),(4,"李斯"),(5,"王五");

查看表中[所有行] 的 [所有列]

select * from newstu;

查看表中[所有行] 的 部分列

select id from newstu;
select id,name from newstu;

查看表中 部分行 的 [所有列]

select * from newstu where id>1; --id大于1的那些行

查看表中 部分行 的 部分列

select id,name from newstu where id>1;

修改/更新 表中某一条记录

update newstu set id=1,name = "张飞" where name="张三";

更新 表中[所有记录]

update newstu set id=1,name = "张飞";

新增一个字段
一次只能增加一个字段

表中已有的记录，这一新增字段会自动赋值为null

alter table stu add age2 tinyint unsigned; #被声明为tinyint unsigned的字段age2的存储值范围是0-255
#改变表stu [增加1个列age2] 无符号型

删除 表中[所有记录]

delete from stu; #新手模式下delete语句必须加where子句。否则delete语句不会执行。
delete from stu where id=2; #删除id为2的那些行

mysql 注释

注释符"--"后面至少跟一个空白符(例如空格、tab、换行符 等)
这样才可以"-- "

mysql 服务器支持的注释符有3种：

# 到该行结束      单行注释
-- 到该行结束     单行注释

/* 行中间或多个行 多行注释 */

mysql> SELECT 1+1;      # 这个注释直到该行结束
mysql> SELECT 1+1;     -- 这个注释直到该行结束
mysql> SELECT 1 /* 这是一个在行中间的注释 */ + 1;
mysql> SELECT 1+
/*
这是一个多行注释的形式
*/
1;

[注意]
-- (双长划) 注释风格要求在两个长划后至少有一个空白符!(例如空格、tab、换行符 等)

尽管服务器理解刚才描述的注释句法，但 MySQL 客户端的语法分析在 /* ... */ 注释方式上还有所限制：

单引号和双引号被用来标志一个被引用字符串的开始，即使是在一个注释中也是这样的。
所以 如果注释中的引号没有另一个引号与之配对，那语法分析程序就[不会认为注释结束] !!!
如果你以交互式运行 mysql，你会产生困惑，因为提示符从 mysql> 变为 ’> 或 ">

一个分号;是指当前 SQL 语句的结束 并且跟随它的任何东西表示下一行的开始。

不论 以交互式运行 mysql 还是用 mysql < some-file 让 mysql 读取文件(里面写着命令)，
这个限制均存在。

笔者认为:单行注释最好用 # 开始
用 -- 做 注释后面必须至少有一个空格,[忘写了就会出现错误]!

发送到服务器之前，mysql客户程序也执行部分语句解析(例如，它通过解析来确定在多语句行中的语句边界）

之所以要求使用空格，是为了防止与自动生成SQL查询有关的问题，它采用了类似下面的代码，其中，自动为“!payment!”插入“payment”的值：
UPDATE account SET credit=credit-!payment!
考虑一下，如果“payment”的值为负数如“-1”时会出现什么情况：
UPDATE account SET credit=credit--1
在SQL中“credit--1”是合法的表达式，但是，如果“--1”被解释为注释开始，部分表达式将被舍弃。结果是 表达式的意义与预期的意义完全不同。
UPDATE account SET credit=credit
该语句不会对值作任何更改！这表明，允许注释以“--”开始会产生严重后果。所以--后面必须使用空格

在MySQL 5.1中，mysql解析/* ...*/注释的唯一局限性是结合该风格的注释定界符使用的叹号!标记了有条件执行的SQL语句部分。
适用于交互式运行mysql 和 将命令放入一个文件中并以批处理模式使用mysql来处理mysql < file_name的文件。
详细信息和例子参见1.8.4节，“MySQL对标准SQL的扩展”。

MySQL服务器包含一些其他SQL DBMS中不具备的扩展。注意，如果使用了它们，将无法把代码移植到其他SQL服务器。在某些情况下，你可以编写包含MySQL扩展的代码，但仍保持其可移植性，方法是用/*... /注释掉这些扩展。
在本例中，MySQL服务器能够解析并执行注释中的代码，就像对待其他MySQL语句一样，但其他SQL服务器将忽略这些扩展。例如：
SELECT /! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

如果在字符“!”后添加了版本号，仅当MySQL的版本等于或高于指定的版本号时才会执行注释中的语法：
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

这句是说，如果你的版本号为3.23.02或更高，MySQL服务器将使用TEMPORARY关键字。

信息函数(安全常用)

原文http://www.sqlinjectionwiki.com/Categories/2/mysql-sql-injection-cheat-sheet/

Version

SELECT @@VERSION 
 SELECT version()

Current User

SELECT user()
 SELECT system_user()

Current Database

SELECT database()

@@version_compile_os