MySql数据库(1)

一 数据库的基础
数据库的组成
库 表 记录
1、数据库的类型
    1) 关系型数据库
        按照统一的格式标准存储数据
        存储结构化数据
        减小数据冗余
        存储海量数据保证数据的真实性和唯一性以及有效性
    2) 非关系型数据库 
        数据存储格式要求不严格
        侧重高速度读取和写入
        存储非结构化数据
        存储数据类型广泛
2、常见的关系型数据库类型
    1) Oracle
        甲骨文公司数据库
        大型数据库
        稳定性强
        收费成本高
        所有平台都支持
    2) SQL Server
        微软的数据库产品
        图形化界面
        使用简单
        仅支持Windows
        收费
    3) MySQL
        oracle公司产品
        小型关系化数据库
        开源
        稳定性强
        不支持高并发
    4) DB2
        IBM公司数据库
        所有平台都支持
        大型数据库
3、MySQL的优势
        支持多线程和多用户访问
        使用C/S设计结构
        简单易用，查询速度快
        稳定性强
        安全可靠性强

二 安装和配置数据库
1、安装依赖程序和创建管理数据库用户
    1) 安装光盘自带的ncurses-devel-5.9-14.20130511.el7_4.x86_64
mount /dev/cdrom /mnt/
cd /mnt/Packages/
rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm 

    2) 安装MySQL依赖软件cmake
解压cmake
yum -y install gcc gcc-c++ libstdc++-devel
tar zxvf cmake-2.8.6.tar.gz -C /usr/src/
cd /usr/src/cmake-2.8.6/
./configure 
gmake && gmake install

安装依赖程序ncurses-devel cmake
yum -y install cmake ncurses-devel
    3) 安装MySQL并创建管理数据库用户
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql 
解压mysql
tar -zxvf mysql-5.5.22.tar.gz  -C /usr/src/
cd /usr/src/mysql-5.5.22/
使用cmake配置MySQL程序(每个选项都有空格)
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci 
-DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
DCMAKE_INSTALL_PREFIX=/usr/local/mysql          #mysql安装目录
DDEFAULT_CHARSET=utf8                            #默认字符集为utf8
DDEFAULT_COLLATION=utf8_general_ci                #默认使用的字符集校对规则
DEXTRA_CHARSETS=all                                #安装所有扩展字符集
DSYSCONFDIR=/etc                                #初始化参数目录
                                                                       
make && make install

2、配置MySQL服务
    1)修改MySQL程序的所有者
chown -R mysql:mysql /usr/local/mysql/
    2) 生成MySQL主配置文件
cp /usr/src/mysql-5.5.22/support-files/my-medium.cnf /etc/my.cnf
    3) 生成服务控制文件添加执行权限
cp /usr/src/mysql-5.5.22/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
     4) 优化MySQL服务控制命令(设置环境变量)
vim /etc/profile
PATH=$PATH:/usr/local/mysql/bin
source /etc/profile
     5) 初始化MySQL程序
/usr/local/mysql/scripts/mysql_install_db  
--user=mysql --basedir=/usr/local/mysql  
--datadir=/usr/local/mysql/data/
     6) 设置服务开机自启
chkconfig --add mysqld
chkconfig --level 35 mysqld on
       7) 重启MySQL服务
systemctl restart mysqld
     8)监听端口
      netstat -anptu | grep mysql              mysql默认端口3306
 
三 、数据库的基本管理
    初始设置密码
    mysqladmin -u root password
    登陆
    mysql -uroot -ppwd@123
    exit   ctrl+c  退出 
1、数据库的基本管理
    1) 创建数据库
        create database benet;
    2) 查看数据库
        show databases;
    3) 删除数据库
        drop database benet;
    4) 切换数据库
        use mysql;
2、 表的管理
    1) 查看库中的表
        show tables;
    2) 创建表设计表的结构
        create table benet.students (姓名 char(4),性别 char(2),班级 char(6),电话 char(11),primary key (电话));
        create table benet.users (user_name char(4) NOT NULL,user_passwd char(10) DEFAULT '',primary key (user_name));
                                                     不能为空                        默认加密    主键（真实性，有效性，唯一性)
    3) 表中写入数据（连续列可以不指定列名)
        insert into benet.students value ('张三','男','hb3011','17801133987');
    4) 表中写入数据（不连续列指定列名)
        insert into benet.students （姓名,电话) value ('李四','17801133987');
    5) 查看表中所有数据
        select * from benet.students
    6) 查看表中指定列的数据
        select 姓名,电话 from benet.students
    7) 查询表中指定条件的信息
        select * from benet.students where 姓名='李四';
    8) 修改数据记录
        update benet.students set 电话='18800000000' where 姓名='李四';
    9) 删除数据记录
        delete from benet.students where 姓名='李四'；
    10) 查看表的结构 
        describe mysql.host;
    11) 删除表
        drop table benet.123;
    12) 使用update修改密码
        update mysql.user set password=password('pwd123456') where User="zabbix" and Host="localhost";
        update mysql.user set password=PASSWORD('123456') where user='root';
        刷新用户授权信息
        flush privileges;
    在mysql数据库修改root账户密码
    update mysql.user set password=PASSWORD('1234567') where user='root';
    删除数据库的来宾用户user,password均为空
    查看
    select user,host,password from mysql.user where user='';
    删除
    delete from mysql.user where user='';
3、数据库用户授权
    1) 授权类型
    select  查询权限
    insert  插入权限
    update  更新权限
    delete  删除权限
    all     全部权限
    2) 授予用户插入权限
    grant insert on benet.* to 'ceo'@'localhost' identified by 'qwe123.0';
           权限                                   新建用户使用       密码
    3) 查看用户权限
    show grants for ceo@localhost;
    4) 撤销用户权限
    revoke insert on benet.* from 'boos'@'localhost';
            权限                    用户
    5) 授予用户查看，读锁表权限 创建用户test 允许在主机192.168.100.20登录
    mysql> grant select,lock tables ON benet.* TO 'test'@'192.168.100.20' IDENTIFIED BY 'pwd@123';
    6)使用garnt授权用户tom通过远程主机192.168.100.20访问 
        mysql> grant all on *.* to 'tom'@'192.168.100.20' identified by 'pwd@123';
    7)查看授权 
        mysql> show grants for 'tom'@'192.168.100.20';
    8)客户端安装mysql客户端访问 
        [root@centos02 ~]# yum -y install mariadb
        [root@centos02 ~]# mysql -h 192.168.100.10 -utom -ppwd@123 -P 3306
    9)授权用户本地登录 
        mysql> grant all on *.* to 'bob'@'localhost' identified by 'pwd@123';
    10)撤销授权 
        mysql> revoke all on *.* from 'tom'@'192.168.100.20';

MySql数据库(2)

一，MySQL数据库完全备份和恢复
1、mysql备份的目的和mysql常见的故障类型
    1)mysql备份的目的
        防止数据丢失
        将损失降到最低
        恢复数据保证系统的数据稳定运行
    2)mysql常见的故障类型
        程序错误：mysql程序损坏
        人为因素：人为操作导致数据丢失
        物理故障：计算机磁盘坏道
        运算错误：程序逻辑错误
        灾难：不可抗力因素
        病毒木马：感染病毒木马导致数据损坏
2、数据库备份类型
    1)冷备份
        停止数据库服务 备份数据存储目录
    2)热备份
        数据库服务正常运行 备份数据库数据
        Mysql的MMM高可用，Mysql的主从复制，MYSQL的双机热备份
    3)温和备份
        锁定表禁止用户写数据 允许读取数据 备份数据保证数据的完整性
3、数据库备份策略
    1)完整备份
        对整个数据库的数据文件和日志文件进行备份
        第一次备份使用完整备份
        备份时间长占用磁盘空间大
    2)差异备份
        完整备份后使用差异备份
        只备份完整备份后修改的数据
    3)增量备份
        完整备份后使用增量备份
        备份新添加或修改的数据
        备份速度快
        占用磁盘空间小
4、常见的备份工具
    1)冷备份工具
        cp复制数据  tar归档
    2)专用备份工具
        mysqldump：mysql程序自带的备份工具，使用广泛
        mysqlhostcopy：通过perl语言编写的脚本备份工具
    3)二进制日志
        配置增量备份恢复使用
    4)第三方工具
        innobackupex    //编译二进制文件 可以备份innodb/Xtradb
        xtrabackup      //mysql热备份工具
        xbstream        //备份数据需要转换数据类型为xbstream数据格式
二、配置完整数据库备份还原
1、配置冷备份
    1)停止数据库服务
        systemctl stop mysqld
    2)创建存储备份位置
        mkdir /backup
    3)使用tar命令归档进行备份
        tar zcvf /backup/mysql_all-$(date +%F).tar.gz  /usr/local/mysql/date/
    4)模拟数据库丢失
        rm -rf /usr/local/mysql/data/
    5)创建数据恢复目录
        mkdir /restore
    6)将备份数据恢复到/restore
        tar zxvf /backup/mysql_all-$(date +%F).tar.gz  -C /restore
    7)移动恢复数据到数据库指定位置
        mv /restore/usr/local/mysql/data/ /usr/lcoal/mysql/data/
    8)启动服务
        systemctl start mysqld
    9)查询数据
    select * from benet.students;
2、使用mysqldump备份数据
 1)备份数据库表
    mysqldump -uroot -ppwd@123 benet students > /bak/benet_students.sql
   将students表恢复到accp数据库
     mysql -uroot -ppwd@123 -e 'create database accp';   创建accp数据库
mysql -uroot -ppwd@123 accp < ./benet_students.sql 将students表恢复到accp数据库
      mysql -uroot -ppwd@123 -e 'select * from accp.students'; 查询恢复数据
     2)备份数据库
        mysqldump -uroot -ppwd@123 --database benet accp  > /bak/benet.accp_database.sql
        删除数据库
        mysql -uroot -ppwd@123 -e 'drop database accp';
        mysql -uroot -ppwd@123 -e 'drop database accp';
        恢复数据库
        mysql -uroot -ppwd@123 < /bakup/benet.accp_database
        查看数据库
        mysql -uroot -ppwd@123 -e ' database';
    3)备份MySQL中所有数据库
        mysqldump -uroot -ppwd@123 --opt --all-databases > /bak/all_database.sql
3、使用source恢复数据
    1)恢复数据库表
        source /bak/benet_students.sql
    2)恢复数据库
        source /bak/all_database.sql
    3)恢复数据库注意事项
        create database benet;
        mysql -uroot -ppwd@123 --database benet < benet.sql

4、增量备份和恢复配置
    开启二进制日志功能
    vim /etc/my.cnf
    [mysql]
    log-bin=/usr/local/mysql/data/mysql-bin
    systemctl restart mysqld
    1)增量备份的特点
        需要对数据库的二进制日志进行备份
        二进制日志丢失数据无法进行恢复
    2)查看二进制日志
        mysqlbinlog --no-defaults /usr/local/mysql/data/ 
    3)日志切割
        mysqladmin  -uroot -ppwd@123 flush-logs
    4)通过二进制日志恢复误删除数据
        mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -ppwd@123
    5)使用日志位置恢复
        起始位置恢复数据
        mysqlbinlog --start-position='391' /usr/local/mysql/data/mysql-bin.000006 | mysql -uroot -ppwd@123
        结束位置恢复数据
        mysqlbinlog --stop-position='366' /usr/local/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123
        起始-结束位置恢复数据
        mysqlbinlog --start-position='171' stop-position='371' /usr/lcoal/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123
        起始时间恢复数据
        mysqlbinlog --start-datetime='19-12-19 2:02:07' /usr/local/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123
        结束时间恢复数据
        mysqlbinlog --stop-datetime='19-12-19 2:02:13' /usr/local/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123
        起始-结束时间恢复数据
        mysqlbinlog --start-datetime='2021-04-01 19:16:25' stop-datetime='2021-04-01 19:16:33' /usr/lcoal/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123

mysql -uroot -ppwd@123

create database benet;

use benet;

create table benet.users (name char(4),password char(6)NOT NULL,primary key(name));

show tables;

insert into benet.users value ('zhangsan','123456');

insert into benet.users value ('lisi','123456');

select * from benet.users;

insert into kaoshi.class value ('张三','一班','20170822','110','105','92','235');
insert into kaoshi.class value ('李四','一班','20170820','95','115','110','260');
insert into kaoshi.class value ('王五','一班','20170818','95','103','108','270'); 
insert into kaoshi.class value ('赵六','一班','20170816','100','109','112','265');
insert into kaoshi.class value ('李宁','二班','20170824','92','98','105','235');
insert into kaoshi.class value ('陈铭','二班','20170826','111','107','96','204');
insert into kaoshi.class value ('付杰','二班','20170828','115','118','116','268');
insert into kaoshi.class value ('郭尚','二班','20170830','111','99','80','259');