20210129 1. MySQL 基础 - 拉勾教育

MySQL 基础

安装 MySQL

1. 准备 rpm 包，上传到 Linux 并解压

   #	Linux 之间远程复制文件
   scp root@192.168.181.131:/root/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar /root/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
   mkdir /root/mysqlinstall
   tar -xvf /root/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C /root/mysqlinstall
   

2. 删除 CentOS 7 自带的 MariaDB

   #	查找
   rpm -qa | grep mariadb
   #	删除
   rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
   

3. 分模块安装 MySQL

   1. common
   2. libs
   3. lib-compact
   4. client
   5. server
   6. devel

   #	安装
   rpm -ivh /root/mysqlinstall/mysql-community-common-5.7.28-1.el7.x86_64.rpm
   rpm -ivh /root/mysqlinstall/mysql-community-libs-5.7.28-1.el7.x86_64.rpm
   rpm -ivh /root/mysqlinstall/mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
   rpm -ivh /root/mysqlinstall/mysql-community-client-5.7.28-1.el7.x86_64.rpm
   rpm -ivh /root/mysqlinstall/mysql-community-server-5.7.28-1.el7.x86_64.rpm
   rpm -ivh /root/mysqlinstall/mysql-community-devel-5.7.28-1.el7.x86_64.rpm
   
   
   #	卸载
   rpm -ev --nodeps mysql-community-client-5.7.28-1.el7.x86_64
   rpm -ev --nodeps mysql-community-common-5.7.28-1.el7.x86_64
   rpm -ev --nodeps mysql-community-libs-5.7.28-1.el7.x86_64
   rpm -ev --nodeps mysql-community-devel-5.7.28-1.el7.x86_64
   rpm -ev --nodeps mysql-community-libs-compat-5.7.28-1.el7.x86_64
   rpm -ev --nodeps mysql-community-server-5.7.28-1.el7.x86_64
   
   ##	删除相关文件、目录
   rm -f /etc/my.cnf
   rm -f /var/log/mysqld.log
   
   
   find / -name mysql
   rm -rf /etc/selinux/targeted/active/modules/100/mysql
   rm -rf /etc/selinux/targeted/tmp/modules/100/mysql
   rm -rf /var/lib/mysql
   rm -rf /var/lib/mysql/mysql
   rm -rf /usr/lib64/mysql
   rm -rf /usr/share/mysql
   

4. 设置开机启动

   systemctl enable mysqld 
   systemctl daemon-reload
   

5. 初始化 MySQL

   mysqld --initialize --user=mysql
   

   #	解决报错，原因是已存在 data 文件夹，如果是新库，直接删除之后再重新初始化
   [root@localhost ~]# mysqld --initialize --user=mysql
   2021-01-25T01:36:03.126200Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
   2021-01-25T01:36:03.130345Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
   2021-01-25T01:36:03.130376Z 0 [ERROR] Aborting
   

6. 查看初始密码

   cat /var/log/mysqld.log |grep 'temporary password'
   

7. 启动 MySQL

   #	启动
   systemctl start mysqld.service
   #	查看状态
   systemctl status mysqld.service
   

8. 登录并修改 root 密码，密码修改后重新登录

   mysql -uroot -p 	# 输入初始密码
   set password=password('123456');
   exit
   mysql -uroot -p 	# 输入修改后的密码
   

学前必备基础

• MySQL软件下载和安装（建议版本5.7.28）
• 熟悉MySQL工具和基本SQL操作
  • Window : MySQL WorkBench, Navicat, SQLyog,HeidiSQL,MySQL Front
  • Linux：MySQL WorkBeanch, Navicat
  • IOS：Navicat、Sequel Pro
  • DDL、DML、DQL、TCL
• 熟悉主键、外键、非空、唯一等约束
  • 创建主键、外键....
• 熟悉索引、事务概念和基本使用
  • 概念、创建

课程主要内容

• MySQL架构原理和存储机制
  • MySQL体系结构（内存结构、磁盘结构）、SQL运行机制、存储引擎、Undo/Redo Log等等
• MySQL索引存储机制和工作原理
  • 索引存储结构、索引查询原理、索引分析和优化、查询优化等
• MySQL事务和锁工作原理
  • 事务隔离级别、事务并发处理、锁机制和实战等
• MySQL集群架构及相关原理
  • 集群架构设计理念、主从架构、双主架构、分库分表等
• 互联网海量数据处理实战
  • ShardingSphere、MyCat中间实战操作，分库分表实战
• MySQL第三方工具实战
  • 同步工具、运维工具、监控工具等

MySQL 起源和分支

• 数据库流行度排行参考：DB-Engines 流行度排行
• MySQL 是最流行的关系型数据库软件之一，由于其体积小、速度快、开源免费、简单易用、维护成本低等，在集群架构中易于扩展、高可用，因此深受开发者和企业的欢迎。

MySQL发展历程如下：

时间	事件
1979	当时瑞典的 Monty Widenius 在 Tcx DataKonsult 公司工作，他开发了一款名为 Unireg 的工具，它是一个面向报表的存储引擎，利用索引顺序来读取数据，这也是 ISAM 存储引擎算法的前身。
1985	Monty 和 David Axmart 等几个小伙子成立了一家公司（ MySQL AB 前身），研发出了 ISAM （ Indexed Sequential Access Method ）存储引擎工具。
1990	客户要求 ISAM 工具能提供 SQL 接口，于是 Monty 找到了 David Hughes （ mSQL 的发明人）商讨合作事宜，后来发现 mSQL 的速度也无法满足需求。于是 Monty 决心自己重写一个 SQL 支持，由此着手 MySQL 设计和研发。
1996	Monty 与 David Axmart 一起协作，开发出 MySQL 第一个版本 1.0 。
1996/10	MySQL 3.1 发布了，没有 2.x 版本。最开始只提供了 Solaris 下的二进制版本。同年 11 月发布了 Linux 版本。
1999-2000	Monty 、 Allan 和 David 三人在瑞典创建了 MySQL AB 公司，并且与 Sleepycat 合作引入了 BDB 引擎， MySQL 从此开始支持事务处理了
2000	MySQL 公布了自已的源代码，并采用 GPL （ GNU General Public License ）许可协议正式开源。
2000/4	MySQL 对旧的存储引擎 ISAM 进行了整理，命名为 MyISAM
2001	Heikki Tuuri 向 MySQL 建议集成他的 InnoDB 存储引擎，这个引擎同样支持事务处理，还支持行级锁。 MySQL 与 InnoDB 正式结合版本是 4.0 。至此 MySQL 已集成了 MyISAM 和 InnoDB 两大主力引擎。
2005/10	MySQL 5.0 版本发布，这是 MySQL 历史上最有里程碑意义的一个版本，在 5.0 版本加入了游标、存储过程和触发器的支持。
2008/1	2008 年 1 月 MySQL AB 公司被 Sun 公司以 10 亿美金收购， MySQL 数据库进入 Sun 时代。
2009/4	Oracle 公司以 74 亿美元收购 Sun 公司，自此 MySQL 数据库进入 Oracle 时代，而其第三方的存储引擎 InnoDB 早在 2005 年就被 Oracle 公司收购
2010/4	发布了 MySQL 5.5 版本。 Oracle 对 MySQL 版本重新进行了划分，分成了社区版和企业版。默认引擎更换为 InnoDB 、增加表分区等。
2013/2	MySQL 5.6 首个正式版 5.6.10 发布。 MySQL 5.6 对 InnoDB 引擎进行了改造，提供全文索引能力，使 InnoDB 适合各种应用场景。
2015/10	MySQL 5.7 首个 GA 正式版 5.7.9 发布。
2016/9	MySQL 8.0 首个开发版发布，增加了数据字典、账号权限角色表、 InnoDB 增强、 JSON 增强等等。
2018/4	MySQL 8.0 首个 GA 正式版 8.0.11 发布

• MySQL 从最初的 1.0 、 3.1 到后来的 8.0 ，发生了各种各样的变化。被 Oracle 收购后， MySQL 的版本演化出了多个分支，除了需要付费的 MySQL 企业版本，还有很多 MySQL 社区版本。
• 还有一条分支非常流行的开源分支版本叫 Percona Server ，它是 MySQL 的技术支持公司 Percona 推出的，也是在实际工作中经常碰到的。 Percona Server 在 MySQL 官方版本的基础上做了一些补丁和优化，同时推出了一些工具。
• 另外一个非常不错的版本叫 MariaDB ，它是 MySQL 的公司被 Oracle 收购后， MySQL 的创始人 Monty 先生，按原来的思路重新写的一套新数据库，同时也把 InnoDB 引擎作为主要存储引擎，也算 MySQL 的分支。

MySQL 应用架构演变

• 架构 V1.0 - 单机单库

  瓶颈：

  • 数据量太大，超出一台服务器承受
  • 读写操作量太大，超出一台服务器承受
  • 一台服务器挂了，应用也会挂掉（可用性差）

• 架构 V2.0 - 主从架构

  

  V2.0 架构主要解决架构 V1.0 下的高可用和读扩展问题，通过给 Instance 挂载从库解决读取的压力，主库宕机也可以通过主从切换保障高可用。在 MySQL 的场景下就是通过主从结构（双主结构也属于特殊的主从），主库抗写压力，通过从库来分担读压力，对于写少读多的应用， V2.0 主从架构完全能够胜任。

  V2.0 瓶颈：

  • 数据量太大，超出一台服务器承受
  • 写操作太大，超出一台主库服务器承受

• 架构 V3.0 - 分库分表

  

  对于 V1.0 和 V2.0 遇到写入瓶颈和存储瓶颈时，可以通过水平拆分来解决，水平拆分和垂直拆分有较大区别，垂直拆分拆完的结果，每一个实例都是拥有全部数据的，而水平拆分之后，任何实例都只有全量的 1/n 的数据。如上图所示，将 Userinfo 拆分为 3 个 Sharding ，每个 Sharding 持有总量的 1/3 数据， 3 个 Sharding 数据的总和等于一份完整数据。

  数据如何路由成为一个关键问题， 一般可以采用范围拆分，List拆分、Hash拆分等。

  如何保持数据的一致性也是个难题。

• 架构 V4.0 - 云数据库

  云数据库（云计算）现在是各大 IT 公司内部作为节约成本的一个突破口，对于数据存储的 MySQL 来说，如何让其成为一个 saas （ Software as a Service ）是关键点。 MySQL 作为一个 saas 服务，服务提供商负责解决可配置性，可扩展性，多用户存储结构设计等这些疑难问题。

杂项

• MySQL 中 set global 与 set 的区别：set global 是全局的，除了当前链接会话，其他会话会立即生效，而 set 只是会话级别的，当前会话立即生效，别的会话不会有有影响

• 配置文件目录：/etc/my.cnf

• data 目录：/var/lib/mysql

• mysqldump 命令使用

  #	查看帮助手册
  mysqldump --help
  #	备份所有库
  mysqldump --all-databases > mysql_all.sql -uroot -p
  

主从复制操作步骤

开启主从复制

1. 主库配置

   1. 修改 MySQL 配置文件 /etc/my.cnf

      [mysqld]
      #	启用 log_bin
      log_bin=mysql-bin
      #	设置 server-id
      server-id=1
      sync-binlog=1
      #	指定主从复制时排除的库
      binlog-ignore-db=performance_schema
      binlog-ignore-db=information_schema
      binlog-ignore-db=sys
      
      #	指定主从复制的库
      # binlog-do-db=lagou
      

   2. 重启 MySQL，应用配置

      #	重启 MySQL
      systemctl restart mysqld
      

   3. 主库授权

      mysql -uroot -p
      grant replication slave on *.* to 'root'@'%' identified by '123456';
      grant all privileges on *.* to 'root'@'%' identified by '123456';
      flush privileges;
      #	查看主库状态
      show master status;
      

2. 从库配置

   1. 确保从库可以访问主库

      mysql -h 192.168.181.131 -P 3306 -u root -p
      

   2. 修改 MySQL 配置文件 /etc/my.cnf

      [mysqld]
      server-id=2
      relay_log=mysql-relay-bin
      read_only=1
      
      
      relay_log_purge=0		#	备用主库需设置
      log_bin=mysql-bin		#	备用主库需设置
      

   3. 重启 MySQL，应用配置

      #	重启 MySQL
      systemctl restart mysqld
      

   4. 从库授权

      grant replication slave on *.* to 'root'@'%' identified by '123456';
      grant all privileges on *.* to 'root'@'%' identified by '123456';
      flush privileges;
      

   5. 从库设置跟随主库

      mysql -uroot -p
      #	查看从库状态，确认结果为空，之前没有进行过配置
      show slave status;
      #	master_log_file 和 master_log_pos 从主库状态信息中获取
      change master to master_host='192.168.181.131',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=869;
      #	开始跟随主库
      start slave;
      #	再次查看从库状态，可以看到状态信息
      ##	行形式查看
      show slave status;
      ##	列形式查看
      show slave status\G;
      

3. 主库从库同步使用

   1. 主库创建库和表

      create database lagou; 
      use lagou;
      create table lagou_position(id int primary key, name varchar(20), salary varchar(20), city varchar(20)) engine=innodb charset=utf8;
      create table lagou_position_detail(id int primary key, pid int, description text) engine=innodb charset=utf8;
      
      insert into lagou_position values (1, 'p1', 1000, 'sh');
      insert into lagou_position values (2, 'p2', 2000, 'bj');
      insert into lagou_position_detail values (1, 1, 'd1');
      

   2. 从库确认已同步

      show databases;
      use lagou;
      show tables;
      select * from lagou_position;
      select * from lagou_position_detail;
      

取消主从复制

1、slave 流程

# 停止 slave
stop slave;

# 清除 slave 信息
reset slave all;

# 可以通过以下命令查看当前状态
show slave status\G;

之后 slave 可以直接关闭下线。

2、master 流程

清除 master 上主从信息：

reset master;

修改配置重启 MySQL：

如果想彻底清除主从的机制，可以修改配置文件，删除主从相关的配置项，然后重启 MySQL 即可。

参考资料

• Mysql取消主从复制