mysql

已于 2022-09-17 16:47:03 修改
阅读量182 收藏
点赞数
文章标签： mysql 数据库
于 2022-09-06 22:12:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41973331/article/details/126735155
版权

mysql

简介

数据

  • 数据(data)，是指未经过处理的原始记录
  • 数据描述事物的符号记录，是可定义为意义的实体，涉及事物的存在形式
  • 是关于事件之一组离散且客观的事实描述，是构成讯息和知识的原始材料

数据库

  • 数据库（Database）指长期存储在计算机内的、有组织的、可共享的数据集合
  • 我们常说 XX 数据库，实质上是 XX 数据库管理系统(DBMS)

数据库

管理系统

  • 定义

    • DBMS-database management system
    • 为管理数据库而设计的大型电脑软件管理系统，有存储、截取、维护及备份等功能

  • 分类

    • RDBMS

      • 定义

        • 关系型数据库
        • 建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据
        • 在关系型数据模型中，用二维表格表示数据库中的数据。这些表格称为关系

      • 常用

        • MySQL、Oracle、MariaDB、SQL Server、DB2、PostgreSQL

      • 优点

        • 易于维护：都是使用表结构，格式一致
        • 使用方便：SQL语言通用，可用于复杂查询
        • 安全：数据存储在磁盘中

      • 缺点

        • 读写性能比较差，不能满足海量数据的高效率读写
        • 固定的表结构，灵活度较低
        • 不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间

    • NOSQL

      • 定义

        • 非关系型数据库，Not Only SQL意为不仅仅是 SQL
        • 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

      • 常用

        • Redis、Neo4j、MongoDB、Memcached、HBase

      • 优点

        • 使用灵活，应用场景广泛：存储数据的格式可以是key-value形式、文档形式、图片形式等
        • 速度快，效率高：可以使用硬盘或者内存作为载体，而关系型数据库只能使用硬盘
        • 海量数据的维护和处理非常轻松
        • 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势
        • 可以实现数据的分布式处理

      • 缺点

        • 不提供sql支持，学习和使用成本较高
        • 没有事务处理，没有保证数据的完整性和安全性
        • 功能没有关系型数据库完善

      • 种类

        • 键值（Key-Value）存储数据库
        • 列存储（Column-Oriented）数据库
        • 面向文档（Document-Oriented）的数据库
        • 图形（Graph）数据库

MySQL

  • 定义

    • 数据库管理系统中的一个实例，简单的说就是一个软件
    • 最流行的关系型数据库管理系统之一。社区版本下载地址： http://dev.mysql.com/downloads/mysql/

  • 端口

    • 3306

  • 数据库组成

    • 库（database）

      • 由一到多个表组成，库映射到系统中是目录

    • 表（table）

      • 包含有记录，表映射到系统中是文件

    • 记录（record）

      • 是由一定数量不同属性的字段组成的

  • 体系架构

    • C/S架构： 客户端和服务器端都得装软件

  • 安装

    • 源码安装

      • 优点

        • 可根据实际环境需求制定编译参数，灵活性好；一台服务器可以安装多个mysql

      • 缺点

        • 安装步骤复杂，编译时间长

    • rpm安装

      • 优点

        • 安装简单

      • 缺点

        • 需要单独下载客户端和服务器端，安装路径不灵活，默认路径不能更改，一台服务器只能装一个

    • 二进制包

      • 优点

        • 安装简单，可以安装到任何路径下，灵活性好；一台服务器可以安装多个mysql

      • 缺点

        • 已经编译好，性能不如源码编译

MySQL安装

yum安装

  • 配置MySQL源

    • vim /etc/yum.repos.d/mysql57.repo

[mysql57]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

  • 安装启动

    • yum -y install mysql-community-server

      • MySQL client 已经包括在server套件内

    • systemctl start mysqld && systemctl enable mysqld

      • 首次启动系统将自动初始化

  • 登录使用

    • 找初始密码

      • grep “password” /var/log/mysqld.log

    • 登录数据库

      • mysql -uroot -p’%i+g10uS.dre’

    • 改密码

      • mysql> alter user root@localhost identified by “xxx@123”;
      • 密码必须符合复杂性要求，包含字母大小写，数字，特赦符号，长度不少于8位

    • 数据目录

      • /var/lib/mysql

    • vim /etc/my.cnf

      • validate-password=OFF

        • 关闭mysql密码强度策略，生产环境禁用，首次启动不可关闭

      • skip-grant-tables

        • 跳过密码进入数据库,用于忘记密码时使用

源码安装

  • 环境准备

    • 清除mariadb

      • yum remove -y mariadb-libs

    • 安装工具

      • yum install -y cmake gcc gcc-c++ ncurses-devel openssl-devel perl-Data-Dumper

      • 作用

        • cmake

          • CMake编译器，用于设置mysql的编译参数

        • gcc gcc-c++

          • 编译器依赖包

        • ncurses-devel

          • 字符终端处理库

        • openssl-devel

          • openssl的库文件

        • perl-Data-Dumper

          • Perl的Data:Dumper模块

    • 创建用户

      • useradd -r -M -s /sbin/nologin mysql
      • -r 创建系统用户

    • 创建数据目录

      • mkdir -p /data/mysql{5.6,5.7};chown mysql.mysql /data/mysql{5.6,5.7}/ -R

    • 准备源码包

      • wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
      • tar xvf mysql-boost-5.7.24.tar.gz

  • 编译安装

    • 预编译

      • cd mysql-5.7.24

      • mysql-5.6

        • cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6
          -DMYSQL_DATADIR=/data/mysql5.6
          -DDEFAULT_CHARSET=utf8
          -DDEFAULT_COLLATION=utf8_general_ci
          -DENABLED_LOCAL_INFILE=1

      • mysql-5.7

        • cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.7
          -DMYSQL_DATADIR=/data/mysql5.7
          -DDEFAULT_CHARSET=utf8
          -DDEFAULT_COLLATION=utf8_general_ci
          -DDOWNLOAD_BOOST=1 # 自动下载boost文件
          -DWITH_BOOST=/usr/local/boost # 先检测此目录中有没有boost文件。如果有，自动解压；如果没有，先下载再解压。

      • 参考文档

        • https://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html

    • 编译

      • make -j 2 #-j指定使用cpu数量

    • 安装

      • make install

  • 初始化

    • 作用

      • 在MySQL数据目录下生成MySQL服务器运行所必需的一些文件、库表，否则无法启动

    • 5.6

      • /usr/local/mysql5.6/scripts/mysql_install_db --user=mysql --datadir=/data/mysql5.6 --basedir=/usr/local/mysql5.6

    • 5.7

      • /usr/local/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/data/mysql5.7

    • 参数

      • –initialize-insecure

        • 不设置初始密码

      • –initialize

        • 生成随机初始密码

      • –user

        • 指定系统用户

      • –basedir

        • 指定安装目录（不要提前创建）

      • –datadir

        • 指定数据目录（要提前创建）

  • 个性化设置

    • 制作软链接

      • ln -s /usr/local/mysql5.6 /usr/local/mysql
      • 可以修改软连接来自由切换版本

    • 将mysql命令
      加为系统命令

      • 软链接方式

        • ln -s /usr/local/mysql/bin/* /usr/local/bin

      • 配置PATH

        • echo “export PATH=$PATH:/usr/local/mysql/bin” > /etc/profile.d/mysql.sh
        • . /etc/profile.d/mysql.sh

    • 设置开机启动

      • cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

        • mysql.server为启动脚本

      • chkconfig --add mysqld

        • 将mysqld添加到chkconfig管理的开机启动中

      • chkconfig

        • 查看mysqld在各运行级别中是否开机启动

      • chkconfig mysqld off

        • 关闭开机启动

      • 脚本实现

        • #!/bin/bash
          #chkconfig:2345 99 99 #交给chkconfig管理
          运行级别 启动优先级 停止优先级
          …

  • mysql服务

    • 5.6创建配置文件

      • cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

    • 启动

      • mysqld_safe &

        • 注意放在后台运行否则终端一直占用

      • /etc/init.d/mysqld start

    • 关闭

      • mysqladmin shutdown
      • /etc/init.d/mysqld stop

  • 客户端使用

    • 登录

      • mysql

    • 退出

      • exit
      • \q

登录使用

  • root密码

    • 设置

      • mysqladmin -uroot password 123

    • 修改

      • mysqladmin -uroot -p123456 password 123

  • 登录

    • 本地登陆

      • mysql -u root -p

    • 远程登陆

      • mysql -u root -h 192.168.1.2 -p

        • 需要授权才支持远程登录

    • 常用选项

      • -u 用户

        • 指定登录用户，默认为root

      • -p

        • 接此选项表示需要密码验证 ，后面可接密码
        • 默认情况下支持匿名登陆

      • -h 远程服务器地址

        • 指定远程服务器的地址或主机名

      • -P 端口

        • 指定连接端口，默认为3306

      • -S 套接字文件

        • 指定套接字文件(配置文件改变默认路径时)
        • 套接字默认文件：/tmp/mysql.sock
        • 必须要有套接字文件才可登录

      • -e “SQL语句”

        • 可以在shell命令行中以非交互的方式使用数据库

      • -D 数据库名

        • 指定数据库

  • 内部命令

    • 注意

      • 文本命令要以”;“结尾

    • 帮助信息

      • help

        • \h

      • ?

        • ?

    • 退出

      • quit

        • \q

      • exit

    • 清除

      • clear

        • \c

      • 在没敲分号的时候使用，否则无效

    • 发送命令到服务器

      • go

        • \g

          • ;

      • ego

        • \G

          • 垂直显示结果

    • 进入数据库

      • use

        • \u

    • 调用系统命令

      • system

        • !

    • 执行sql脚本

      • source

        • .

  • 重置密码

    • 关闭MySQL服务

    • 以忽略授权表的方式启动服务

      • mysqld_safe --skip-grant-table &

    • 以root用户登录
      并修改密码

      • mysql

      • update mysql.user set password=password(‘123’) where host=‘localhost’ && user=‘root’;

    • 重启MySQL服务

SQL语句

介绍

  • 定义

    • SQL结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言

  • 语法

    • SQL语句可以单行或多行书写，以分号结尾

    • 关键字、函数名、列名和索引名不区分大小写

    • 数据库的名字、表名、别名区分大小写

    • 注释

      • 单行注释

        • – 注释内容

        • 注释内容

      • 多行注释

        • /* 注释内容 */

SQL语句分类

  • DDL

    • 介绍

      • Data Definition Language 数据库定义语言
      • 定义数据库、表、索引、存储过程、函数

    • 语句

      • CREATE

        • 创建
        • create database 库名
        • create table 表名 (字段,约束…)

      • DROP

        • 删除
        • drop 库/表 库名/表名

      • ALTER

        • 修改

        • alter table 表名

          • add/drop/rename/change/modify/alter

      • truncate

  • DML

    • 介绍

      • Data Manipulation Language 数据库操纵语言
      • 用来对数据库中表的记录进行增删改

    • 语句

      • INSERT

        • 插入数据
        • insert into 表名 [(字段…)] values(值…)
        • insert into 表名 set 字段=值…

      • DELETE

        • 删除数据
        • delete from 表名

      • UPDATE

        • 更新数据
        • update 表名 set 字段=‘’

  • DCL

    • 介绍

      • Data Control Language 数据库控制语言
      • 用来创建数据库用户、控制数据库的访问权限

    • 语句

      • GRANT

        • 授权

      • REVOKE

        • 撤销

  • DQL

    • 介绍

      • Data Query Language 数据库查询语言
      • 用来查询数据库中表的记录

    • 语句

      • SELECT

        • 查询
        • select 字段… from 表名 [where]

基本sql语句

  • 库操作

    • 查看库

      • 所有库

        • show databases;

      • 库属性

        • show create database db2;

    • 创建库

      • create database db1;

    • 切换库

      • use db1;

    • 删除库

      • drop database db2;

  • 表操作

    • 创建表

      • create table t1 (id int(10),name char(10));

        • 至少要有一个字段,多个字段用","隔开

    • 查看表

      • 当前库中所有

        • show tables;

      • 结构

        • desc t1;

      • 创建属性

        • show create table t1;

    • 修改表名

      • alter table t1 rename to t10;

      • to可省略

    • 添加字段

      • 末尾

        • alter table t1 add sex char(10);

      • 指定字段后

        • alter table t1 add QQ int after name;

      • 首位

        • alter table t1 add uid int first;

    • 删除字段

      • alter table t1 drop uid;

    • 修改字段

      • 名称及属性

        • alter table t1 change id uid int;

      • 属性

        • alter table t1 modify uid int(10);

    • 清空表

      • truncate stu_info;

        • DDL语句

    • 删除表

      • drop table t2;

  • 记录操作

    • 添加记录

      • insert into t1 set uid=1,name=‘tom’,sex=‘male’;

      • insert into t1 (uid,name,sex) values (2,‘jim’,‘male’);

      • 多条

        • insert into t1 (uid,name,sex) values (3,‘lilei’,‘male’),(‘4’,‘lily’,‘female’);

        • insert into t1 values (5,‘lucy’,123,‘female’);

          • 注意列数和值数一定要一样

    • 修改记录

      • update t1 set name=‘hanmeimei’ where uid=6;

        • 注意定位

      • update t1 set uid=6 where name=‘lucy’ and QQ is null;

      • 多条件定位

        • and && or || not !

    • 删除记录

      • delete from t1 where uid=6;

        • 注意定位，无定位则删除全部

  • 单表查询

    • 所有字段

      • select * from t1;

    • 指定字段

      • select name,qq from t1;

    • 指定字段筛选记录

      • select name,chinese from t2 where chinese >= 80;

    • 多条件筛选

      • select name,chinese,english from t2 where chinese >= 80 and english = 100;

      • select 姓名,语文 from score where 语文 between 61 and 79;

      • select 姓名,籍贯 from info where 籍贯 in(‘广东’,‘广西’);

    • 记录排序

      • 升序

        • select * from t2 order by chinese [asc];

      • 降序

        • select * from t2 order by chinese desc;

      • 前n条

        • select * from t2 order by chinese desc limit 2;

    • 子查询

      • select * from t2 where english=(select max(english) from t2);

    • 通配符筛选

      • select * from t2 where name like ‘t%’;

      • like通配符

        • %

          • 所有

        • _

          • 任意单个字符

    • 正则表达式

      • select * from t2 where name regexp ‘^t’;

    • 别名

      • select name,chinese+math+english as total from t2;

      • as可省略

    • 统计

      • 值为真则统计

      • select count(name) from t2;

        • 统计name字段的记录数量，null不会被统计

      • select count(1) from t2;

        • 统计记录数量，包括null。1表示恒为真

      • select sex,count(sex) from t1 group by sex;

        • 按字段值分组统计

  • 多表联合查询

    • 内连接

      • 两个表某字段的值完全一样

      • select t1.name,t1.sex,t2.math from t1 join t2 on t1.name=t2.name;

      • select t1.name,t1.sex,t2.math from t1,t2 where t1.name=t2.name;

    • 外连接

      • 左连接

        • 显示出左表中所有的记录

        • select t1.name,t1.sex,t2.math from t1 left join t2 on t1.name=t2.name;

      • 右连接

        • 显示出右表中所有的记录

        • select t2.name,t1.sex,t2.math from t1 right join t2 on t1.name=t2.name;

    • 别名

      • select a.name,a.sex,b.math from t1 a join t2 b on a.name=b.name;

    • 前面字段的表名可省略

其他常用命令

  • status

    • 查看服务器运行状态，实时连接数, 慢查询数量, 平均查询时间等

  • show processlist;

    • 查看连接数

  • show variables;

    • 查看服务器设置
    • 通常可以在配置文件中进行设置
    • 某些设置可以使用set命令临时设置，只对当前连接生效

数据类型

数值类型

  • 整型

    • 字段

      • 存储占的字节数 有符号数范围 无符号数范围unsigned

    • 类型

      • tinyint

        • 1B(8bits) -128～127 0～255(2^8-1)

      • smallint

        • 2B -32768~32767 0~65535

      • mediumint

        • 3B -8388608~8388607 0~16777215

      • int

        • 4B -2147483648~2147483647 0~4294967295

      • bigint

        • 8B -9223372036854775808~9223372036854775807 0~18446744073709551615

    • 定义无符号数

      • create table t1(ip tinyint unsigned);

    • 零填充，补0

      • create table zf (id tinyint zerofill);

    • 注意

      • 在存储的时候，默认是有符号数的
      • 整型可以指定显示宽度，但是并不影响取值范围，所以无需指定

  • 浮点型

    • float(M,D)

      • M是总长度，D小数的位数
      • 存储空间4B

    • double(M,D)

      • 存储空间8B

字符串类型

  • char

    • 定长字符串

      • 0-255 bytes

    • 指定长度多长，就占用多少个字符，和字段值的长度无关

    • 存储时候长度不够，默认会在字符串后面补空格，但是查询时，空格会被脱掉

  • varchar

    • 变长字符串

      • 0-65535 bytes

    • 指定的长度为最大占用长度

    • 变长字符串需要1个(255以内)或2个字节存储字符串的长度

  • enum

    • 枚举类型

      • 多个给定值中选择一个

    • create table t5 (sex enum(‘male’,‘female’));

  • set

    • 集合类型

      • 多个给定值中选者一个或多个

    • create table t6 (addr set(‘GZ’,‘SH’,‘BJ’));

    • insert t6 set addr=‘GZ,BJ’;

日期时间类型

  • DATE

    • YYYY-MM-DD
    • 1000-01-01 至 9999-12-31

  • TIME

    • HH:MM:SS
    • -838:59:59 至 838:59:59

  • YEAR

    • YYYY
    • 1901 至 2155

  • DATETIME

    • YYYY-MM-DD HH:MM:SS
    • 1000-01-01 00:00:00 至 9999-12-31 23:59:59

  • TIMESTAMP

    • YYYY-MM-DD HH:MM:SS
    • 1970-01-01 00:00:01 至 2038-01-19 03:14:07

函数

函数

  • database()

    • 当前所在的库
    • select database();

  • user()

    • 当前登陆的用户
    • select user();

  • password()

    • 给字符串加密，一般用于修改密码

    • set password for root@localhost = password(‘123’);

    • update mysql.user set password=password(‘123’) where host=‘localhost’ && user=‘root’;

flush privileges;

聚合函数

  • sum()

    • 求和
    • select sum(age) from t1;

  • avg()

    • 平均值
    • select avg(age) from t1;

  • max()

    • 最大值
    • select max(age) from t1;

  • min()

    • 最小值
    • select min(age) from t1;

  • count()

    • 统计，不统计null
    • select sex,count(sex) from t1 group by sex;
    • select count(*) from pass;

时间函数

  • 当前时间

    • curtime()

  • 当前日期

    • curdate()

  • 系统时间

    • now()
    • sysdate()

字符串函数

  • concat()

    • 连接函数，指定连接符拼接字段
    • select concat(username,‘:’,uid) from pass;

  • lower()

    • 转换成小写
    • update pass set username=‘ROOT’ where username=‘root’;

  • upper()

    • 转换成大写
    • select upper(username) from pass;

  • length()

    • 求长度
    • select username,length(username) from pass;

运算符

算数运算符

        • / % () power()
    • 返回运算结果

  • select 4*3,3+6,5-2;

  • select power(2,10);

比较运算符

  • < >= <= = !=

    • 成立返回1，不成立返回0
    • 匹配null不能用=，用is

  • select 3>2;

逻辑运算符

  • and && or || not !

    • 成立返回1，不成立返回0

  • select not 4<5;

  • select 4<5 && 3=3;

用户权限

用户管理

  • 用户数据

    • 库

      • mysql

    • 表

      • user

  • 查看

    • select user,host,password from user;

  • 创建

    • 语法

      • create user ‘name’@‘host’ identified by ‘password’;

    • 组成

      • @‘host’

        • 限定登录主机，默认任意主机

        • 写法

          • 具体ip

            • 192.168.10.11

          • 网段

            • 192.168.10.%

          • 主机名

            • tomcat1.xf.com

          • 域内所有

            • %.xf.com

          • 任意主机

            • %

      • identified by

        • 设置密码

  • 删除

    • 单个

      • drop user 用户名@主机;

        • 不接主机相当于@‘%’

    • 批量

      • delete from user where user=‘’;

        • 删除匿名用户

  • 修改密码

    • mysqladmin -u root -p123 password ‘456’

    • set password for ‘root’@‘localhost’=password(‘1’);

    • update mysql.user set password=password(‘2’) where user=‘root’ and host=‘localhost’;

    • 刷新权限

      • flush privileges;

权限管理

  • 分类

    • 全局

      • 用于一个给定服务器中的所有数据库
      • 存储在mysql.user表中

    • 库

      • 用于一个给定数据库中的所有表
      • 存储在mysql.db和mysql.host表中

    • 表

      • 用于一个给定的表中的所有列
      • 存储在mysql.tables_priv表中

    • 列

      • 用于一个给定的表中的单列
      • 存储在mysql.columns_priv表中

  • 查看

    • show grants for ‘用户名’@‘主机’;

  • 授权

    • 语法

      • grant 权限列表 on 库名.表名 to 用户@主机 identified by ‘密码’;

    • 组成

      • 权限列表

        • 对应表中查询

          • desc

        • all

          • 所有权限，grant除外

      • 库表

          • 所有

        • 具体名字

    • 示例

      • 全局

        • grant all on . to ‘user1’@‘1.1.1.21’ identified by ‘123’;

      • 库

        • grant select,insert,update on db1.* to ‘user2’@‘1.1.1.%’ identified by ‘123’;

      • 表

        • grant select on db1.t1 to ‘user3’@‘%’ identified by ‘456’;

      • 列

        • grant select(id,name) on db1.t1 to ‘user4’@‘%’ identified by ‘456’;

    • 注意

      • 用户未授权默认无任何权限
      • 若用户不存在则会自动创建，故无需提前创建用户

  • 回收

    • 语法

      • revoke 权限 on 库名.表名 from ‘用户’@‘主机’;

    • 全部

      • revoke all on . from xiaomi@‘%’;

    • 指定

      • revoke insert on db1.tb1 from mi@‘%’;

  • 刷新权限

    • flush privileges;

管理原则

  • 只授予能满足需要的最小权限
  • 创建用户的时候限制用户的登陆主机
  • 初始化数据库的时候删除没有密码的用户
  • 为每个用户设置满足密码复杂度的密码
  • 定期清理不需要的用户。回收权限或者删除用户

约束

概述

  • 约束是作用于表中字段上的规则，用于限制存储在表中的数据
  • 可以在创建表/修改表的时候添加约束

类型

  • Type

  • Null

    • 默认yes

      • alter table score modify id int null ;

    • not null

      • 非空

        • 限制该字段的数据不能为null
        • create table t2 (id int not null );
        • alter table score modify id int not null;

      • 特性

        • int类型会将不允许为空的id字段转换成0
        • 字符串型，非空约束会将空值转换成空字符串

  • Key

    • unique

      • 唯一

        • 保证该字段的所有数据都是唯一、不重复的
        • 唯一性约束对空值无效

      • 设定

        • alter tabe score modify id int unique;

      • 删除

        • alter table score drop index key_name;

    • primary key

      • 主键

        • 唯一标识表中的每一条记录，要求非空且唯一
        • 一张表只能有一个主键，可以将多列设为联合主键
        • primary key == not null + unique

      • 设定

        • 单个字段

          • 列级约束

            • 在字段后直接指定主键约束(默认值为null)
            • create table t1 (id int primary key,name char(10));

          • 表级约束

            • 整张表的所有字段都定义完成之后再来指定主键(默认值是0)
            • create table t1 (id int,name char(10),primary key (id));

        • 多个字段联合主键

          • 联合主键只能在所有字段都定义完成之后，才能定义主键
          • create table t1 (id int,name char(10),primary key (id,name));

      • 删除

        • alter table score drop primary key;

    • foreign key

      • 外键

        • 一个表的数据依赖另一张表的主键列的数据，主键列没有出现的值不能出现在外键字段

    • 查看key

      • show keys from score;

  • Default

    • default

      • 默认值

        • 插入数据时，如果未指定该字段的值，则采用默认值
        • create table t1(sex char(10) default ‘male’ );

      • 修改默认值

        • alter table t1 modity sex varchar(10) default ‘jim’;
        • alter table t1 alter sex set default ‘tom’;

  • Extra

    • auto_increment

      • 自增

        • create table t1 (id int unique auto_increment);

      • 要求

        • 该字段必须是数值型
        • 字段上要有唯一性索引或者主键(联合使用)

      • 特性

        • 当自增字段发生断档时，值会从最大值继续自增
        • 当delete删除最大值时，下一个值仍然从删除之前的最大值继续自增
        • 当truncate表时，值从1开始重新计算

外键约束

  • 创建条件

    • 存储引擎必须是innodb

    • 相关联字段数据类型要一致

    • 最好在外键列上建索引

      • 目的是为了减少扫描范围，不创建影响性能

  • 添加外键

    • 创建父表

      • create table class (id int primary key,class char(20));

    • 创建子表

      • create table student (class_id int,name char(20),foreign key(class_id) references class(id));

    • 约束

      • 子表中的关联数据依赖于父表，不能向子表中插入父表中不存在的值
      • 不能删除父表中被子表所依赖的记录，或修改父表中主键字段

    • 级联操作

      • 父表中的数据发生删除或者更新时，子表中相关数据也会发生相应的变化

      • on delete cascade

        • 级联删除

      • on update cascade

        • 级联更新

      • create table student (class_id int ,name char(20),foreign key(class_id) references class (id) on delete cascade on update cascade);

  • 删除外键

    • 先查看外键名字

      • show create table student;

    • alter table 表名 drop foreign key 外键名字

索引

介绍

  • 功能

    • 加快数据检索的速度，提高效率

  • 缺点

    • 创建和维护索引都需要消耗时间，消耗的时间长短取决于表中数据量的多少
    • 会占用磁盘空间
    • 更新数据库中的数据时，索引也会更新

  • 分类

    • 单列索引、多列索引、唯一性索引、主键索引、普通索引 …

创建索引

  • 创建表时直接创建

    • create table 表名 (字段名 类型,…,index索引名);
    • 创建索引时不指定名字，那么默认会创建和字段名同名的索引

  • 已经存在的表添加

    • create index 索引名 on 表名 (字段名);
    • alter table 表名 add index 索引名;

查看索引

  • show create table t1\G;
  • show index from t1\G;

删除索引

  • drop index 索引名 on 表名;
  • alter table 表名 drop index 索引名;

存储引擎

查看

  • 当前软件支持

    • show engines;

  • 当前默认使用

    • show variables like ‘storage_engine%’;

常用

  • innodb

    • 默认引擎
    • 支持事务、行级锁、外键，占用磁盘空间更多

  • myisam

    • 不支持事务，也不支持外键，所以访问速度快

    • 目录结构

      • .frm

        • 存储的是表结构

      • .MYD

        • 存储的是表数据

      • .MYI

        • 存储的是表的索引

      • db.opt

        • 存储当前数据库的默认字符集和字符集规则

  • memory

    • 存储数据的位置是内存，因此访问速度最快
    • 重启服务之后，数据丢失，一般用于临时表

设置

  • 建表时指定

    • create table t2(id int,) engine=myisam;

  • 永久修改默认存储引擎

    • 修改配置文件

      • vim /etc/my.cnf
        [mysqld]
        default-storage-engine=MYISAM

    • 重启服务生效

  • 已经创建的表修改

    • alter table t3 engine=innodb;

事务

操作类型

  • dml操作

    • insert update delete

满足ACID特性

  • A 原子性

    • 一个事务是一个整体，它里面的所有操作要么都做，要么都不做，不可再分割

  • C 一致性

    • 事务开始前和结束后，数据库的完整性约束是一致的

  • I 隔离性、独立性

    • 两个事务之间是互不相干

  • D 持久性

    • 事务完成之后，对数据库的所有更改都会持久的保存在数据中，不会被回滚

事务处理方法

  • begin

    • 开始一个事务

  • rollback

    • 事务回滚，即取消数据的修改

  • commit

    • 事务确认（保存修改）

    • 不提交事务，数据不会保存

    • mysql默认自动提交

      • set autocommit=0 禁止
      • set autocommit=1 开启

事务结束的标志

  • 明确执行commit提交，表示确认更改
  • 明确执行rollback回滚，表示取消更改
  • 遇到ddl语句，自动提交(create、alter、drop、truncate)

日志

查看配置

  • show variables like ‘%log%’;

  • vim /etc/my.cnf

错误日志

  • log_error

    • /data/mysql5.6/mysql.err

  • 记录mysql服务器的启动、运行和关闭过程

通用查询日志

  • general_log

    • /data/mysql5.6/mysql.log

  • 记录所有对数据库的请求信息(包括登陆、查询)，默认不开启

  • 配置

    • general_log = on

      • 打开

    • general_log_file = select.log

      • 自定义文件名

慢查询日志

  • 名称

    • slow_query_log
    • /data/mysql5.6/mysql-slow.log

  • 作用

    • 记录的是查询时间超过指定时间的查询语句
    • 一般用于优化查询，默认不开启

  • 配置

    • slow_query_log = 1

      • 打开

    • long_query_time = 5

      • 设置超时时间-s

    • slow_query_log_file = slow-log

      • 自定义文件名

  • 验证

    • select sleep(6);

      • 执行一个超过5秒的查询操作

二进制日志

  • 名称

    • log_bin

      • 默认关闭，建议打开

  • 作用

    • 记录用户对数据库所做的所有的ddl和dml操作句
    • 用于AB复制、数据恢复

  • 配置

    • 5.6

      • log-bin = my-bin

        • 打开并定义文件名

    • 5.7

      • log-bin = my-bin

      • server_id = 1

        • 默认为0，与log-bin冲突

      • binlog_format = MIXED

        • 设置binlog同步模式(日志记录格式)

        • STATEMENT

          • 基于sql语句，5.6默认

        • ROW

          • 基于行，5.7默认。需要转码查看
          • –base64-output=DECODE-ROWS -v

        • MIXED

          • 混合模式

  • 使用

    • shell查看

      • mysqlbinlog 文件名

        • at 4 #事件发生的位置信息
        • 170606 14:51:00 #事件发生的时间信息

    • 数据恢复

      • 使用位置点

        • mysqlbinlog --start-position=245 --stop-position=534 log_bin.000001 |mysql -uroot -p123

        • –start-position

          • 起始位置点

        • –stop-position

          • 结束位置点

      • 使用时间

        • mysqlbinlog --start-datetime=‘17-06-06 15:25:04’ --stop-datetime=‘17-06-06 15:26:15’ log_bin.000002 | mysql -u root -p123

        • –start-datetime

          • 起始时间

        • –stop-datetime

          • 结束时间

      • 过滤操作

        • 在mysql中查看，过滤需要恢复的库名、表名，整理后交给mysql执行

      • 注意

        • 正确选择起始位和结束位，注意事务的处理节点(begin、rollback、commit)
        • 位置点和时间点可以混合使用

    • MySQL内

      • 列出日志文件

        • show master logs;

      • 手动轮转

        • flush logs;

      • 查看操作记录

        • show binlog events in ‘bin-log.000006’;

      • 查看当前文件

        • show master status\G

  • 删除

    • 方法

      • 删除某个日志文件之前的

        • purge master logs to ‘log_bin.000002’;

      • 删除某个时间所在文件之前的

        • purge master logs before ‘2018-03-07 15:16:52’;

      • 删除所有（禁用）

        • reset master;

          • 删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件

        • reset slave all;

          • 删除slave数据库的relaylog日志文件，并重新启用新的relaylog文件

    • 注意

      • 删除二进制日志前先将其和数据库备份
      • 严禁采用物理方法删除二进制日志，会导致无法启动

复制

复制到已有表

  • 指定字段

    • insert into user1(id,name) select id,name from user;

  • 结构一致

    • insert into user1 select * from user where id<10;

复制到新表

  • 复制表字段、类型，含记录

    • create table user2 select * from user;

  • 复制表结构，不含记录

    • 仅字段、类型

      • create table user2 select * from user where 0;

    • 约束及索引

      • create table user6 like user;

备份恢复

数据迁移

  • 导入

    • 系统文件导入到数据库

    • 创建存放表

      • 字段数与源文件一致

    • 导入记录

      • load data infile ‘文件路径’ into table 表名 [fields terminated by ‘分隔符’];

      • load data infile “/opt/test.txt” into table test;

      • load data infile ‘/etc/passwd’ into table pass fields terminated by ‘:’;

    • 注意

      • 默认字段分隔符\t，记录分隔符\n

        • fields terminated by 指定字段分隔符

      • mysql用户对要导入的文件至少要有读的权限

      • 文件名用双引号或单引号引起来

  • 导出

    • 将数据库中的数据导出到系统文件中

    • select * from pass into outfile “/opt/passout”;

    • 注意

      • 导出的文件一定不要提前创建

      • mysql用户对目录要有读写权限

      • 默认字段分隔符\t，记录分隔符\n

        • fields terminated by 指定字段分隔符

备份分类

  • 根据服务是否在线

    • 热备份

      • 读写均不受影响

    • 冷备份

      • 也称为离线备份，读写操作均终止

    • 温备份

      • 仅可以执行读操作

  • 根据备份数据方式

    • 物理备份

      • 直接复制数据文件，速度快
      • 占用空间大，迁移性受限

    • 逻辑备份

      • 备份sql语句，速度慢、丢失浮点精度
      • 方便使用文本根据直接进行处理、可移植能力强

  • 根据备份数据内容

    • 完全备份

      • 备份全部数据

    • 增量备份

      • 仅备份上次完全备份或增量备份以后变化的数据

    • 差异备份

      • 仅备份上次完全备份以来变化的数据

时间选择

  • 数据库访问量较小的时候

常用工具

  • mysqldump

    • mysql官方提供的逻辑备份工具，热备及温备

  • lv

    • 逻辑卷快照

      • 注意快照后新产生数据的备份（bin_log日志）

  • xtrabackup

    • 开源的免费数据库热备份软件，可实现逻辑备份和物理备份

  • Innodb

    • 商业工具

  • mysqlbackup

    • Mysql企业版的备份工具

备份建议

  • 定期做恢复测试非常重要
  • 大数据库建议物理备份（基于快照），小的数据库建议逻辑备份
  • 备份binlog日志
  • 逻辑备份与物理备份混合
  • 尽量保留多份备份（跨设备、跨地域）

mysqldump

  • 备份表

    • mysqldump -u用户 -p密码 库名 表1 [表2 …] > /path/tables.sql

  • 备份库

    • mysqldump -u用户 -p密码 -B 库1 [库2 …] > /path/databases.sql

  • 全备份

    • mysqldump -u用户 -p密码 -A > /path/all.sql

  • 恢复

    • mysql -u用户 -p密码 [库名] < /path/tables.sql

  • 全备

    • mysqldump -A --master-data --single-transaction > backup_sunday_1_AM.sql

  • 增备

    • mysqldump -A --single-transaction --flush-logs --master-data=2 > backup_sunday_1_AM.sql

  • 选项

    • -B, --databases

      • 做库备份时指定所要备份的库
      • 不接-B无创建库的备份指令，若库被删除需先手动创建后恢复

    • -A, --all-databases

      • 全库备份

    • -F, --flush-logs

      • 备份后刷新日志(启动新的日志记录)

    • -x, --lock-all-tables

      • 给所有的表加锁

    • –master-data[=value]

      • 将二进制日志的位置和文件名写入到输出中

      • 1

        • 位置和文件名被写入CHANGE MASTER语句形式的转储输出

      • 2

        • CHANGE MASTER语句被写成SQL注释

Xtrabackup

  • 介绍

    Xtrabackup介绍
    1、Xtrabackup是什么
    Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。
    mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合

    Xtrabackup备份原理
    备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件,系统表空间文件ibdatax，复制结束后，执行flush tables with readlock,然后复制.frm MYI MYD等文件，完成后执行unlock tables,最后停止xtrabackup_log,备份完成

    1、在InnoDB内部会维护一个redo/undo日志文件，也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

    2、Xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

    3、上面就是xtrabackup的备份过程。接下来是准备（prepare）过程，在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像mysql刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了，这个过程在xtrabackup的编译二进制程序中实现。

    4、在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以myisam表数据与InnoDB表数据是同步的。类似oracle的，InnoDB的prepare过程可以称为recover（恢复），myisam的数据复制过程可以称为restore（还原）。

    Xtrabackup有两个主要的工具：xtrabackup、innobackupex
    1)、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表
    2)、 innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.
    innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。
    还加入了一些使用的选项。如slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

    2、Xtrabackup可以做什么 :
    在线(热)备份整个库的InnoDB、 XtraDB表
    在xtrabackup的上一次整库备份基础上做增量备份(innodb only)
    以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用)
    MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。

    Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下：
    1).首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。
    2).在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，如果是则备份该页，同时记录当前检查点的LSN。

    首先，在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；
    接着，开始拷贝全部的数据文件.ibd；拷贝结束之后，才停止拷贝logfile。
    因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。

    • Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份

  • 备份原理

    • 备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件,系统表空间文件ibdatax，复制结束后，执行flush tables with readlock,然后复制.frm MYI MYD等文件，完成后执行unlock tables,最后停止xtrabackup_log,备份完成

  • 安装

    • 下载

      • http://www.percona.com/downloads/XtraBackup/

    • MySQL5.6及之前

      • Percona XtraBackup 2.3及以上

        • yum install -y xtrabackup

    • MySQL5.7

      • Percona XtraBackup 2.4及以上

    • MySQL8.0

      • Percona XtraBackup 8.0及以上

  • 全备

    • 备份

      • innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/

    • 还原准备

      • innobackupex --apply-log /backup/2018-03-08_10-54-36/
      • 处理未完成的事务。热备，备份过程中可能发生数据改变

    • 还原

      • 需要停止mysql并删除数据目录中所有文件。生产环境注意用其他服务器做测试
      • innobackupex --copy-back --datadir=/data/mysql5.6 /backup/2018-03-08_10-54-36/

    • 还原文件属性

      • chown mysql.mysql -R /data/mysql

        • 还原的数据文件属组默认为root

  • 修改配置

    • vim /etc/my.cnf

      • [mysqld]
        datadir = /data/mysql5.6

[client]
socket = /tmp/mysql.sock
- 配置文件中设置好后就不需在命令中加对应的参数

  • 增备

    • 先做全备

      • /backup/full #全备目录

    • 创建增备1

      • innobackupex -uroot -p123 --incremental /backup/1/ --incremental-basedir=/backup/full/

    • 创建增备2

      • innobackupex -uroot -p123 --incremental /backup/2/ --incremental-basedir=/backup/1/

    • 还原

      • 还原准备

        • innobackupex --apply-log --redo-only /backup/full/

      • 合并增备1

        • innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/1/

      • 合并增备2

        • innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/2/

      • 全量还原

        • innobackupex --copy-back /backup/full/

  • 参数

    • -u p P S H

      • 同mysql。H=>h

    • –defaults-file

      • 指定配置文件，默认为/etc/my.cnf

    • –no-timestamp

      • 不创建时间戳目录，直接备份到指定的目录中

    • –apply-log

      • 对xtrabackup的–prepare参数的封装

    • –redo-only --apply-log

      • 强制备份日志时只redo，跳过rollback。这在做增量备份时非常必要

    • –copy-back

      • 将备份数据文件拷贝到mysql数据目录

    • –datadir=/data/mysql5.6

      • 指定数据恢复的目录

    • –incremental

      • 指定增备目录

    • –incremental-basedir=/backup/full/

      • 指定基础目录，在该备份的基础上做增备
      • 增备以上一次备份为基础，差备以上一次全备为基础

    • –databases=LIST

      • 指定备份的库

    • –include=REGEXP

      • 指定备份的表

    • –slave-info

      • 备份从库，xtrabackup_slave_info 文件记录备份结束pos

    • –use-memory=size

      • 还原准备时指定使用内存，默认为100M，增加内存可加快速度

AB复制

工作原理(过程)

  • master上开启二进制日志，将数据更新记录到二进制日志文件中
  • 从slave start开始，slave通过I/O线程向master请求二进制日志文件，slave要知道向谁请求从哪个位置点请求
  • master接收到slave的I/O请求之后，就会从相应的位置点开始，给slave传日志
  • slave接收到日志后，会写入本地的中继日志中
  • slave通过sql线程读取中继日志中的内容，在数据库中执行相应的操作
  • 到此为止，master和slave上的数据一致，之后slave服务器进入等待状态，等待master的后续更新

相关线程

  • 从服务器

    • I/O线程

      • 负责从主服务器上索要二进制日志，并将其存入从服务器的中继日志中

    • SQL线程

      • 负责读取从服务器上的中继日志，并对数据库执行相应的操作

  • 主服务器

    • binlog dump线程

      • 负责发送二进制日志

配置

  • 传统方式

    • master

      • 配置文件

        • [mysqld]
          log-bin = my-bin
          binlog_format = MIXED
          datadir = /data/mysql5.6
          server-id = 1

[client]
socket = /tmp/mysql.sock

	- 创建复制账号

		- > grant replication slave,replication client on *.* to 'slave'@'1.1.1.%' identified by '123';

	- 全备

		- # mysqldump -uroot -p123 -A --master-data --single-transaction > /tmp/all.sql
		- 或# innobackupex -uroot -p123 --slave-info --no-timestamp /backup/

	- 查看复制开始点

		- 主库无变化

			- > show master status\G

		- 主库有变化

			- mysqldump查看备份文件
			- xtrabackup查看xtrabackup_slave_info
			- CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=xx;

- slave

	- 同步master数据

		- 恢复全备

	- 配置文件

		- [mysqld]

log-bin = my-bin
binlog_format = MIXED
server-id = 2 #和master不同
#read_only = 1 #仅复制用户及超级用户可写
#log_slave_updates #从库重放操作默认不记录binlog

	- server-uuid

		- vim /data/mysql5.6/auto.cnf
		- 主从服务器的server-id保证不一致，如果是克隆的服务器要手动修改

	- 重启MySQL服务
	- 测试复制账号是否能远程登录
	- 登录本地MySQL配置

		- > change master to master_host='1.1.1.21',master_user='slave',master_password='123',master_log_file='my-bin.000003',master_log_pos=553; 
		- > start  slave;

			- 启动从服务器

	- 查看从服务器的状态

		- > show slave status\G

			- Slave_IO_Running: Yes
			- Slave_SQL_Running: Yes

  • GTID方式

    • GTID

      • 全局事务ID（global transaction identifier），由UUID+TID组成
      • UUID是一个MySQL实例(内存和后台进程的集合)的唯一标识
      • TID代表了该实例上已经提交的事务数量
      • GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务,并且会补全没有执行的事务）
      • 当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中(先记录gtid再记录事务操作)

    • master_auto_position

      • 从服务器连接到主服务器之后，把自己执行过的GTID<SQL线程> 、获取到的GTID<IO线程>发给主服务器
      • 主服务器把从服务器缺少的GTID及对应的transactions发过去补全

    • 配置文件

      • 增加部分（主从都加）

        • gtid_mode = on
          enforce_gtid_consistency = 1
          log_slave_updates = 1 #将重放操作记录到binlog

      • 其他与传统方式一致

    • 登录本地MySQL配置

      • change master to master_host=‘1.1.1.21’,master_user=‘slave’,master_password=‘123’,master_auto_position=1;

      • start slave;

        • 启动从服务器

    • GTID的优点

      • 一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
      • GTID用于代替传统复制，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
      • 减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

主从复制延迟

  • 主从复制介绍

    • Master负责写操作的负载，所有写操作都在Master上进行，而读的操作则分摊到Slave上进行
    • 读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是集中在读的操作，所以配置多个Slave

  • 主从复制形式

    • 一主一从、主主复制、一主多从、多主一从、联级复制

  • 问题

    • 情况一

      • 问题

        • 从服务器配置过低

      • 解决

        • 升级从服务器的配置,让只读节点的配置大于或者等于主节点的配置

    • 情况二

      • 问题

        • 主库的QPS过高

          • 只读节点与主库采用单线程同步，而主库是并发多线程写入

      • 解决

        • 开启只读节点的并行复制 (mysql5.6.3以后支持多线程复制)

    • 情况三

      • 问题

        • 主库的DDL语句

          • DDL操作在主库执行时间很长，那么在备库也会消耗同样的时间
          • 只读节点上正在执行一个耗时长的的查询，堵塞来自主库的DDL

      • 解决

        • 执行操作之前对可能带来的影响要有考量
        • kill掉只读节点上的大查询操作

    • 情况四

      • 问题

        • 主库执行大事务

          • 主库执行了一条insert … select非常大的插入操作，产生了大量binlog

      • 解决

        • 将大事务拆分成为小事务进行排量提交

    • 情况五

      • 问题

        • 无主键的表进行DML操作

          • 由于表中没有主键，导致每一个事务条目的更新都是全表扫描

      • 解决

        • 每张表在设计的时候都加上一个主键

  • 规避

    • 数据库设置: 主从同步加速

      • sync_binlog在slave端设置为0(事务提交后不立即做磁盘同步)
      • 禁用log-slave-updates
      • 直接禁用slave端的binlog
      • slave端(innodb引擎)：innodb_flush_log_at_trx_commit =2

    • 优化架构

      • 业务的持久化层的实现采用分库架构
      • 单个库读写分离，一主多从，主写从读
      • 在业务和mysql之间加入memcache或者redis的cache层
      • 不同业务的mysql物理上放在不同机器

    • 硬件方面

      • 使用比主库更好的硬件设备作为slave
      • 存储用ssd或者盘阵或者san,提升随机写的性能
      • 主从间保证处在同一个交换机下面，并且是万兆环境

主从复制监控

  • 状态参数

    • Master_Log_File

      • SLAVE中的I/O线程当前正在请求的主服务器binlog的名称

    • Read_Master_Log_Pos

      • SLAVE中的I/O线程已经读取的binlog位置

    • Relay_Log_Pos

      • 在当前的中继日志中，SQL线程已读取和执行的位置

    • Relay_Log_File

      • SQL线程当前正在读取和执行的中继日志文件的名称

    • Relay_Master_Log_File

      • 由SQL线程执行的包含多数近期事件的主服务器binlog的名称

    • Slave_IO_Running

      • I/O线程是否启动并成功地连接到主服务器上

    • Slave_SQL_Running

      • SQL线程是否启动

    • Seconds_Behind_Master

      • 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，s

  • 监控参数

    • Slave_IO_Running

      • Yes表示和主库连接正常并能实施复制工作
      • No则说明与主库通信异常，排查Last_IO_Error

    • Slave_SQL_Running

      • YES表示正常，NO表示执行失败

    • Seconds_Behind_Master

      • NULL

        • IO/SQL发生故障

      • 0

        • 主从复制良好(网络阻塞时不准确)

      • 正值

        • 主从出现延时，值越大延时越大

      • 负值

        • bug值，不应该出现

  • 严谨判断

    • 同时查看主从状态show status
    • 对比binlog文件及位置点
    • 或mk-heartbeat工具

主从切换步骤

mycat

数据库中间件

  • mycat , atlas, mysql-proxy, mysql-router, cobar

介绍

  • 官网

    • http://www.mycat.org.cn/

  • 简介

    • 一个开源的分布式数据库系统，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，后端支持MySQL等主流数据库

  • 应用

    • 读写分离，主从切换
    • 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
    • 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
    • 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
    • 报表系统，借助于Mycat的分表能力，处理大规模报表的统计
    • 替代Hbase，分析大数据
    • 作为海量数据实时查询的一种简单有效方案

  • 原理

    • 拦截了用户发送过来的SQL语句，首先对SQL语句做一些特定的分析
    • 如分片分析、路由分析、读写分离分析、缓存分析等
    • 然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户

配置读

写分离

  • 环境准备

    • 服务器

      • master1：1.1.1.21
      • master2：1.1.1.22
      • slave1：1.1.1.23
      • slave2：1.1.1.24
      • mycat：1.1.1.25
      • client：1.1.1.26

    • 主从关系

      • master1与master2互为主从
      • master1与slave1建立主从
      • master2与slave2建立主从
      • 为了展示效果, 在此不配置主从关系, 在实际工作中一定要配置好

  • 软件安装

    • java环境

      • tar xf jdk-8u181-linux-x64.tar.gz -C /usr/local/

      • ln -s /usr/local/jdk1.8.0_181/ /usr/local/java

      • #vim /etc/profile.d/java.sh
        export JAVA_HOME=/usr/local/java
        export PATH= J A V A H O M E / b i n : JAVA_HOME/bin: JAVAH​OME/bin:PATH

      • . /etc/profile.d/java.sh

      • java -version

        • 查看版本

    • 安装mycat

      • tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

    • 所有服务器安装mysql

  • 配置

    • master及slave

      • 授权mycat远程操作，创建相同的库及表，表中分别插入不同的数据

    • mycat

      • server.xml

        • vim /usr/local/mycat/conf/server.xml
        • …
          #连接mycat的用户名与密码
          tom123
          TESTDB #数据逻辑库,多个逻辑库用","分开
        • …
          #只读用户,此用户不可进行写操作
          user
          TESTDB
          true

      • schema.xml

        • vim /usr/local/mycat/conf/schema.xml
select user() #健康检测 
	- 参数注释

		- schema

标签

			- 总标签, 包括虚拟库, 以及连接属性, 还有虚拟节点
			- name

				- 定义虚拟库的名字，要和server.xml定义一致

			- dataNode

				- 定义虚拟节点名字，要和下一级的dataNode标签里的name属性一致

		- dataNode

标签

			- 包括dataNode名字，虚拟host属性，以及要使用后端物理机的数据库
			- name

				- 定义dataNode名字，该属性提供给schema里dataNode使用

			- dataHost

				- 可自定义，下一级标签需要使用该信息

			- database

				- 指定使用后端物理机的哪个数据库，就是把上级标签定义的虚拟数据库映射到后端物理机上的那个真实的数据库

		- dataHost

标签

			- 包括后端物理机的属性，地址，端口，以及用户名密码，该物理机是负责写还是读
			- name

				- 使用上级标签dataNode里的dataHost

			- balance

				- 读负载均衡类型
				- 0

					- 不开启读写分离，所有读操作都发送到当前可用的writeHost上

				- 1

					- 全部的readHost与stand by writeHost参与 select语句的负载均衡

				- 2

					- 所有读操作都随机的在writeHost、readhost上分发

				- 3

					- 所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力(只在1.4 及其以后版本支持)

			- writeType

				- 写负载均衡类型
				- 0

					- 所有写操作发送到配置的第一个writeHost，第一个挂了切到备writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 

				- 1

					- 所有写操作都随机的发送到配置的writeHost。

				- 2

					- 没实现

			- switchType

				- 切换类型
				- -1

					- 表示不自动切换

				- 1

					- 默认值，自动切换

				- 2

					- 基于MySQL主从同步的状态决定是否切换

		- writeHost标签

			- 定义后端物理机属性，定义了该物理机是可读还是可写，可以有多个

		- heartbeat标签

			- 定义如何探测后端物理机是否存活，使用select user语句去探测

  • 启动验证

    • 启动mycat服务

      • /usr/local/mycat/bin/mycat start &

    • 查看是否启动成功

      • netstat -tanp |grep .066

        • 9006管理端口
        • 8006工作端口

    • client登录

      • mysql -u tom -ptom123 -h 1.1.1.25 -P 8066

    • 验证

      • 客户端连接后进入到虚拟库查看表的内容,验证读负载均衡
      • 插入一些数据验证写操作
      • 关闭主写库验证写库切换

  • 管理端口常用命令

    • 连接管理端口

      • mysql -uroot -proot -h 1.1.1.25 -P9066

    • show @@help;

      • 列出所有管理命令

    • reload @@config;

      • 更新 schema.xml 配置文件

    • reload @@config_all;

      • 更新所有配置文件

    • show @@datanode;

      • 查询显示 Mycat 数据节点列表

    • show @@version;

      • 查看版本

    • show @@connection;

      • 查看连接信息

    • show @@backend;

      • 查看后端连接状态

启动问题

  • JVM启动失败

    • 报错

      • Java HotSpot™ 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0

    • 解决

      • echo “wrapper.startup.timeout=300” >> /usr/local/mycat/conf/wrapper.conf

  • 内存不足

    • 报错

      • error=‘Cannot allocate memory’（errno=12

    • 解决

      • 方案1

        • vim /usr/local/mycat/conf/wrapper.conf
        • wrapper.java.additional.10=-Xmx4G
          wrapper.java.additional.11=-Xms1G
          改为
          wrapper.java.additional.10=-Xmx2G
          wrapper.java.additional.11=-Xms512M

      • 方案2

        • 增大虚拟机内存配置，1G或2G

  • 启动日志

    • /usr/local/mycat/logs/wrapper.log

      • error
      • cause by

  • 访问日志

    • /usr/local/mycat/logs/mycat.log

缺陷

  • 系统性能损失较严重，30-40%

lamp架构(编译)

环境准备

  • 安装mysql

  • 安装apr

    • tar -xf apr-1.5.2.tar.gz -C /usr/src
    • cd /usr/src/apr-1.5.2
    • ./configure --prefix=/usr/local/apr
    • make -j2 && make install

  • 安装apr-util

    • tar xf apr-util-1.5.4.tar.gz -C /usr/src
    • cd /urs/src/apr-util-1.5.4
    • ./configure --prefix=/usr/local/apr-util --with-apr=/usr/local/apr
    • make -j2 && make install

  • 安装apache

    • tar xf httpd-2.4.23.tar.gz -C /usr/src/
    • cd /usr/src/httpd-2.4.23/
    • ./configure --prefix=/usr/local/apache --sysconfdir=/etc/httpd --enable-modules=all --enable-mods-shared=all --enable-so --enable-ssl --enable-cgi --enable-rewrite --with-apr=/usr/local/apr --with-apr-util=/usr/local/apr-util/ --with-pcre --with-libxml2 --with-mpm=event --enable-mpms-shared=all
    • make -j2 && make install
    • /usr/local/apache/bin/httpd

  • 安装php

    • yum install libxml2-devel bzip2-devel -y

      • php依赖包

    • tar xf php-5.5.30.tar.gz -C /usr/src/

    • cd /usr/src/php-5.5.30/

    • ./configure --prefix=/usr/local/php --with-apxs2=/usr/local/apache/bin/apxs --with-config-file-path=/etc/ --with-config-file-scan-dir=/etc/php.d/ --with-libxml-dir --with-openssl --with-pcre-regex --with-zlib --with-bz2 --with-libxml-dir --with-pcre-dir --with-jpeg-dir --with-png-dir --with-zlib-dir --with-freetype-dir --enable-mbstring --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-libxml-dir --enable-zip --enable-maintainer-zts

    • make -j2 && make install

    • grep php5 /etc/httpd/httpd.conf

      • LoadModule php5_module modules/libphp.so

  • apache测试

    • 配置文件

      • vim /etc/httpd/httpd.conf

DirectoryIndex index.php index.html
AddHandler php5-script .php
AddType text/html .php

- 重启

	- /usr/local/apache/bin/apachectl -k restart

- 静态测试

	- curl 127.0.0.1

- 动态测试

	- rm -rf /usr/local/apache/htdocs/index.html
	- #vim /usr/local/apache/htdocs/index.php
<?php phpinfo(); ?> 
	- 浏览器访问，显示php帮助信息

  • php测试

    • mysql授权

      • create database farm;

      • grant all on farm.* to farm@localhost identified by ‘farm’;

      • mysql -u farm -p farm -h localhost

    • php连接mysql

      • #vim /usr/local/apache/htdocs/index.php
<?php mysql_connect("localhost","root","123456") or die("NOT"); echo "Success"; ?> 
	- 浏览器访问，显示Success

安装农场项目

  • 解压软件

    • yum -y install unzip
    • unzip farm-ucenter1.5.zip

  • 发布目录

    • rm -rf /usr/local/apache/htdocs/*
    • cp -r upload/* /usr/local/apache/htdocs/
    • chown daemon.daemon /usr/local/apache/htdocs/* -R

  • 导入数据库

    • mysql -ufarm -pfarm -D farm </usr/local/apache/htdocs/qqfarm.sql

  • 浏览器访问安装

screen(拓展)

  • 操作时间很长的命令如编译，中断后可接着执行

  • yum -y install screen

  • 使用

    • 执行命令前先执行screen

    • 中断后重新连接终端

    • screen -ls

      • 显示目前所有的screen作业

    • screen -r <作业名称>

      • 恢复离线的screen作业

lamp架构(yum)

yum -y install httpd mariadb-server php php-mysql

systemctl start httpd mariadb

httpd默认已配置好php，路径为/etc/httpd/conf.d/php.conf

配置好数据库(建库授权)后将软件放置发布目录即可

weixin_41973331
关注
深入探讨MySQL分表策略与实践
weixin_44976692的博客
02-27 2万+
MySQL分表是将原始表中的数据按照一定规则分散到多个表中，以减轻单表数据量过大的压力，提高数据库的性能和可用性。常见的分表方式包括水平分表和垂直分表。将表中的行数据按照某种规则拆分到不同的物理表中，常见的拆分规则包括按时间、按数据量等。水平分表：当单表数据量巨大，且数据增长迅速时，水平分表是一个常用的解决方案。适用于数据的增长方向是单向的，即数据的增加只在某一个维度上进行，如时间维度、订单ID维度等。
【MySQL系列】MySQL中text,longtext,mediumtext区别
檀越的博客
04-02 8万+
mysql 中 text,longtext,mediumtext 字段类型的意思, 以及区别 mysql 中 text，longtext，mediumtext 字段类型区别为：字节限制不同、I/O 不同、行迁移不同。 一、字节限制不同 1、text 字段类型：text 字段类型的字节限制为 65535 字节。 2、longtext 字段类型：longtext 字段类型的字节限制为 2147483647 字节。 3、mediumtext 字段类型：mediumtext 字段类型的字节限制为 16777215
解决：安装MySQL 5.7 的时候报错：unknown variable ‘mysqlx_port=0.0‘
scoful的专栏
06-14 4万+
解决：安装MySQL 5.7 的时候报错：unknown variable 'mysqlx_port=0.0'
Ubuntu deb文件 安装 MySQL
桃李不言下自成蹊
11-15 7万+
sudo apt -y install vim net-tools wget gcc make cmake lrzsz sudo apt -y install libmecab2 libjson-perl
Mac 上如何安装Mysql？ 如何配置 Mysql？以及如何开启并使用MySQL
hirezy
09-20 3万+
有许多开发的小伙伴，使用的是mac，那么在mac上如何安装，配置Mysql，以及使用Mysql了，今天来一个系统的教程。
MySQL安装及配置详细教程
Mxdon_on的博客
04-22 16万+
MySQL安装及配置超详细教程 首先下载安装包 [网址]（https://dev.mysql.com/downloads/file/?id=476233）进入页面后可以不登录。后点击底部”No thanks, just start my download.”即可开始下载。 安装： 直接把压缩包解压到你想安装的路径即可，相当于不用安装，可以看到我的解压路径在D盘的ROUTE文件夹下的mysq...
mysql5.7.35安装配置教程【超级详细安装教程】
m0_46590717的博客
09-13 9万+
MySQL5.7.35安装教程 下载工具 官网下载下载 在下图中选择你自己需要的版本即可 第二种下载方式如下图所示下载 下载完成后对工具包进行解压，我解压的在D盘 解压好过后在里面新建my.ini文件 （如果你不知道怎么创建my.ini文件请看）右击新建文本文档 创建文本文档过后进行重命名讲文本文档的后缀名改为ini如图操作 再将新建的文本文档改名为my.ini 编辑my.ini文件将下面的代码复制进去记得更改里面【basedir】【datadir】的路径为你自己的安装路径 [mysqld] #
MySQL命令行连接
m0_53188792的博客
12-23 7342
命令行连接mysql
Oracle与MySQL：一场深度技术较量
聚焦于深度解析最新的编程语言特性、框架升级、架构设计、开发工具和最佳实践，涵盖Web前端（如Vue3, React, Angular等）、后端开发（如Node.js, Java, Python等）、移动端开发（iOS, Android原生及跨平台开发）
04-29 5万+
Oracle与MySQL作为数据库领域中两款极具影响力的系统，各自拥有庞大的用户群体和丰富的应用场景。尽管它们均致力于解决数据存储、管理和检索问题，但在设计理念、核心特性、适用场景及运维管理等方面存在显著差异。本篇文章将深入剖析Oracle与MySQL之间的区别性特征，旨在为开发者、架构师以及决策者提供全面的技术参考，以便根据项目需求选择最适合的数据库解决方案。
Mysql详细安装教程
最爱吃鱼罐头的博客
03-22 1万+
详细介绍了MySQL的安装过程以及安装失败该怎么办，还有如何完整卸载MySQL
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
mysql驱动（适用于mysql5.7版本）
12-07
MySQL驱动是Java应用程序连接到MySQL数据库的关键组件，它允许程序通过Java Database Connectivity (JDBC) API与MySQL服务器进行通信。本压缩包包含了两个适用于MySQL 5.7版本的驱动文件：`mysql-connector-java-...
MySQL安装配置教程（超级详细、保姆级）
热门推荐
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
mysql的查询操作
weixin_45710581的博客
09-24 881
MySQL的查询操作是数据库管理和数据检索的核心。通过SQL（Structured Query Language，结构化查询语言）语句，用户可以执行包括数据检索、数据插入、更新和删除在内的多种操作。在本文中，我们将重点讨论数据检索（即SELECT语句）的高级用法和最佳实践。
【MySQL】数据库基础认识
最新发布
m0_74755811的博客
09-25 1246
存储数据用文件就可以了，为什么还要弄个数据库?文件保存数据有以下几个缺点：文件的安全性问题文件不利于数据查询和管理文件不利于存储海量数据文件在程序中控制不方便数据库存储介质：磁盘、内存。为了解决上述问题，专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。数据库的水平是衡量一个程序员水平的重要指标。
Mysql(3:Mysql事务)
2301_81085167的博客
09-24 901
Mysql事务
Python酷玩之旅_如何连接MySQL（mysql-connector-python）
splendid_java的专栏
09-24 714
Python作为数据科学、机器学习等领域的必选武器，备受各界人士的喜爱。当你面对不同类型、存储于各类介质的数据时，第一时间是不是要让它亮个相？做个统计，画个图表，搞个报表… 等等。正如Java中的JdbcDriver一样，Python中也有同样角色的库。比如今天博主正要介绍的，它是Python中操作数据库的常用工具，我们先从它说起吧，Let’s go~Python中连接mysql有各种工具支持，博主推荐使用，它是MySQL官方提供的标准工具，依赖少，查询方便。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值