youto`blog - mysql
type: channel
link: http://www.youto.club/index.php/category/sql/
items: Mariadb 主从复制&读写分离
items: Redis 基础操作
items: 索引
items: SQL 语句
items: MySQL 用户和权限管理
items: MySQL 体系结构
items: mysql 5.7 二进制安装

Mariadb 主从复制&读写分离
type: item
date: 2021-07-19T10:50:00+00:00
description: 数据库 主从&读写分离 节点规划 IP 主机名 节点 10.35.172.77 mysql1 主数据库节点 10.35.172.78 mysql2 从数据库节点 10.35.172.79 mycat 数据库中间件节 1、基础环境搭建 ### mysql1 # 修改主机名 [root@mysql1 ~]# hostnamectl set-hostname mysql1 # 配置解析文件 [root@mysql1 ~]# echo "10.35.172.77 mysql1 10.35.172.78 mysql2" >> /etc/hosts # 关闭 selinux&防火墙 [root@mysql1 ~]# setenforce 0 [root@mysql1 ~]# systemctl stop firewalld # SecureXF上传 mariad-repo.tar.gz && 解压到 /opt [root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt # 配置yum源 [root@mysql1 ~]# mkdir /etc/yum.repo.d/bak [root@mysql1 ~]# mv /etc/yum.repo.d/* /bak [root@mysql1 ~]# echo "[mariadb] name=mariadb baseurl=file:///opt/mariadb-repo gpgcheck=0 enabled=1 [centos] name=centos baseurl=ftp://10.35.172.81/centos gpgcheck=0 enabled=1" > /etc/yum.repos.d/local.repo ### mysql2 [root@mysql2 ~]# hostnamectl set-hostname mysql2 [root@mysql2 ~]# echo "10.35.172.77 mysql1 10.35.172.78 mysql2" >> /etc/hosts [root@mysql2 ~]# setenforce 0 [root@mysql2 ~]# systemctl stop firewalld # SecureXF 上传 mariad-repo.tar.gz [root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt [root@mysql2 ~]# mkdir /etc/yum.repo.d/bak [root@mysql2 ~]# mv /etc/yum.repo.d/* /bak [root@mysql2 ~]# echo "[mariadb] name=mariadb baseurl=file:///opt/mariadb-repo gpgcheck=0 enabled=1 [centos] name=centos baseurl=ftp://10.35.172.81/centos gpgcheck=0 enabled=1" > /etc/yum.repos.d/local.repo ### mycat [root@mycat ~]# hostnamectl set-hostname mycat [root@mycat ~]# setenforce 0 [root@mycat ~]# systemctl stop firewalld [root@mycat ~]# mkdir /etc/yum.repo.d/bak [root@mycat ~]# mv /etc/yum.repo.d/* /bak # SecureXF 上传 mariad-repo.tar.gz [root@mycat ~]# tar -zxvf mariadb-repo.tar.gz -C /opt [root@mycat ~]# echo "[mariadb] name=mariadb baseurl=file:///opt/mariadb-repo gpgcheck=0 enabled=1 [centos] name=centos baseurl=ftp://10.35.172.81/centos gpgcheck=0 enabled=1" > /etc/yum.repos.d/local.repo 2、安装服务 # 安装 mariadb 并启动 [root@mysql1 ~]# yum install -y mariadb mariadb-server [root@mysql1 ~]# systemctl start mariadb && systemctl enable mar…

Redis 基础操作
type: item
date: 2021-04-27T09:20:14+00:00
description: Redis 安装 目录规划 [root@r_db01 ~]# tree /data /data ├── redis_cluster │ └── redis_6379 └── soft [root@r_db01 ~]# tree /opt/ /opt/ └── redis_cluster └── redis_6379 ├── conf ├── logs └── pid 编译安装 创建所需目录 mkdir -p /data/soft mkdir -p /data/redis_cluster/redis_6379 mkdir -p /opt/redis_cluster/redis_6379/{conf,pid,logs} 下载tar包 cd /data/soft/ wget http://download.redis.io/releases/redis-3.2.9.tar.gz 解压安装 yum install -y gcc-c++ tar zxf redis-3.2.9.tar.gz -C /opt/redis_cluster/ ln -s /opt/redis_cluster/redis-3.2.9/ /opt/redis_cluster/redis cd /opt/redis_cluster/redis make && make install 生成配置文件 vi /opt/redis_cluster/redis_6379/conf/redis_6379.conf ### 以守护进程模式启动 daemonize yes ### 绑定的主机地址 bind 10.35.172.71 ### 监听端口 port 6379 ### pid文件和log文件的保存地址 pidfile /opt/redis_cluster/redis_6379/pid/redis_6379.pid logfile /opt/redis_cluster/redis_6379/logs/redis_6379.log ### 设置数据库的数量，默认数据库为0 databases 16 ### 指定本地持久化文件的文件名,默认是dump.rdb dbfilename redis_6379.rdb ### 本地数据库的目录 dir /data/redis_cluster/redis_6379 启动服务 启动 redis-server /opt/redis_cluster/redis_6379/conf/redis_6379.conf 关闭 redis-cli -h db01 shutdown 使用 全局命令 查看所以键(禁止使用) Keys * 查看键总数 Dbsize 检查键是否存在(存在:1 不存在: 0) Exists key 删除键 Del key [key …] 键过期 Expire key seconds # 大于等于0的证书: 键剩余过期时间 # -1: 键没设置过期时间 # -2: 键不存在 查看键的类型 Type key 字符类型 设置 - 获取 SET GET db01:6379> set key value OK db01:6379> get key "value" 计数器 INCR INCRBY db01:6379> SET A 10 OK db01:6379> GET A "10" db01:6379> INCR A (integer) 11 db01:6379> GET A "11" db01:6379> INCRBY A 4 (integer) 15 db01:6379> get A "15" 多值设置 - 多支获取 MEST MGET db01:6379> mset key3 v3 key4 v4 key5 v5 OK db01:6379> mget key3 key4 key5 1) "v3" 2) "v4" 3) "v5" 判断,删除 EXISTS DEL db01:6379> exists key5 (integer) 1 db01:6379> del key5 (integer) 1 db01:6379> exists key5 (integer) 0 db01:6379> del key5 (integer) 0 查看类型 TYPE db01:6379> set key5 v5 OK db01:6379> type key5 string 过期(单位: 秒) EXPIRE db01:6379> expire key5 10 (integer) 1 db01:6379> ttl key5 (integer) 6 去除超时 PERSIST db01:6379> persist key5 (integer) 1 db01:6379…

索引
type: item
date: 2021-04-11T07:35:00+00:00
description: 索引 索引的作用 类似于一本书中的目录,起到优化查询的作用 索引的分类(算法) B树 默认使用的索引类型 R树 Hash FullText GIS 索引 Btree索引功能上的分类 辅助索引 提取索引列的所有值,进行排序 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点 在叶子节点中的值,都会对应存储主键ID 聚集索引 MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的 MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行 聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根 聚集索引和辅助索引的区别 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可 在一张表中,聚集索引只能有一个,一般是主键. 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值. 聚集索引,叶子节点存储的时有序的整行数据. MySQL 的表数据存储是聚集索引组织表 辅助索引细分 单列辅助索引 联合索引(覆盖索引) 唯一索引 索引树高度 索引树高度应当越低越好,一般维持在3-4最佳 数据行数较多 分表 : parttion 用的比较少了；分片,分布式架构 字段长度 业务允许,尽量选择字符长度短的列作为索引列 业务不允许,采用前缀索引. 数据类型 char 和 varchar enum 索引的命令操作 查询索引 desc city; PRI ==> 主键索引 MUL ==> 辅助索引 UNI ==> 唯一索引 mysql> show index from city\G 创建索引 单列的辅助索引 mysql> alter table city add index idx_name(name); 多列的联合索引 mysql> alter table city add index idx_c_p(countrycode,population); 唯一索引: mysql> alter table city add unique index uidx_dis(district); mysql> select count(district) from city; mysql> select count(distinct district) from city; 前缀索引 mysql> alter table city add index idx_dis(district(5)); 删除索引 mysql> alter table city drop index idx_name; mysql> alter table city drop index idx_c_p; mysql> alter table city drop index idx_dis; 压力测试 准备 mysql> use test mysql> source /tmp/t100w.sql 未做优化之前测试 mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='test' \ --query="select * from test.t100w where k2='MN89'" engine=innodb \ --number-of-queries=2000 -uroot -p123 -verbose 索引优化后 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

SQL 语句
type: item
date: 2021-04-11T07:02:00+00:00
description: SQL MySQL内置功能 连接数据库 -u -p -S -h -P -e < 示例： mysql -u root -p -S /tmp/mysql.sock mysql -u root -p -h 10.0.0.51 -P3306 -e 免交互执行 sql 语句 [root@db01 ~]# mysql -uroot -p -e "show databases;" < 导入数据 [root@db01 ~]# mysql -uroot -p123 /root/world.sql 内置命令 help 帮助 \c ctrl+c 结束上个命令运行 \q quit exit ctrl+d 退出 \G 竖行显示 source 恢复备份文件 SQL 基础应用 介绍 结构化的查询语言 关系型数据库通用的命令 遵循SQL92标准（SQL_MODE） 常用种类 DDL 数据定义语言 DCL 数据控制语言 DML 数据操作语言 DQL 数据查询语言 数据库的逻辑结构 库 库名 库属性(字符集，排序规则) 表 表名 表属性(存储引擎类型，字符集，排序规则) 列名 列属性(数据类型，约束，其他属性) 数据行 字符集(charset) 查看支持字符集 show charset utf8 三个字符 utfmb4 四个字符(支持emoji) 排序规则(collation) 查看排序规则 show collation 英文字符串的大小写不敏感 utf8mb4_general_ci 大小写不敏感 utf8mb4_bin 大小写敏感(存拼音,日文) 数据类型 数字：tinyint int 字符串: char(100) 定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充 varchar(100) 变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间. 会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间) 如何选择这两个数据类型? 少于255个字符串长度,定长的列值,选择char 多于255字符长度,变长的字符串,可以选择varchar 枚举 address enum('sz','sh','bj'.....) 可能会影响到索引的性能 时间 datetime 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999 timestamp 范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999 二进制 DDL 库的定义 创建数据库 CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin; 查看库情况 SHOW DATABASES; SHOW CREATE DATABASE zabbix; 删除数据库(不代表生产操作) DROP DATABASE oldguo; 修改数据库字符集 注意: 一定是从小往大了改,比如utf8--->utf8mb4. 目标字符集一定是源字符集的严格超级. CREATE DATABASE oldguo; SHOW CREATE DATABASE oldguo; ALTER DATABASE oldguo CHARSET utf8mb4; 库定义规范 库名使用小写字符 库名不能以数字开头 不能为数据库内部关键字 必须设置字符集 表的定义 建表 表名, 列名, 列属性, 表属性 列属性 PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一. NOT NULL : 非空约束,不允许空值 UNIQUE KEY : 唯一键约束,不允许重复值 DEFAULT : 一般配合 NOT NULL 一起使用. UNSIGNED : 无符号,一般是配合数字列,非负数 COMMENT : 注释 AUTO_INCREMENT : 自增长的列 示例： CREATE TABLE stu ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间' )ENGINE INNODB CHARSET utf8…

MySQL 用户和权限管理
type: item
date: 2021-04-02T13:32:00+00:00
description: 用户和权限管理 用户作用 登录mysql 管理mysql 用户定义 用户名@'白名单' 写法： wordpress@'%' wordpress@'localhost' wordpress@'127.0.0.1' wordpress@'10.0.0.%' wordpress@'10.0.0.5%' wordpress@'10.0.0.0/255.255.254.0' wordpress@'10.0.%' 用户管理 创建用户 mysql> create user ac@'10.0.0.%' identified by 'sa'; 说明：8.0以前，可以自动创建用户并授权 mysql> grant all on *.* to ac@'10.0.0.%' identified by 'sa'; 查询用户 mysql> select user,host from mysql.user; 修改用户密码 mysql> alter user ac@'10.0.0.%' identified by 'sa'; 删除用户 mysql> drop user ac@'10.0.0.%' ; 权限管理 权限列表 ALL SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE 授权命令 例：grant all on *.* to ac@'10.0.0.%' identified by 'sa' with grant option; 说明：grant [权限] on [作用目标] to [用户] identified by [密码] with grant option 授权需求 创建一个管理员用户root，可以通过10网段，管理数据库. grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123'; 创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE. grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123'; 权限回收 show grants for wordpress@'10.0.0.%'; mysql> revoke delete on wordpress.* from 'wordpress'@'10.0.0.%'; mysql> show grants for wordpress@'10.0.0.%'; MySQL 的启动和关闭 正常启停 sys -v mysql.server start ---> mysqld_safe ---> mysqld system mysql.service ---> mysqld 需要依赖于 /etc/my.cnf 维护 mysqld_safe 例：修改密码mysqld_safe --skip-grant-tables --skip-networking & 将参数临时加到命令行，命令行的优先级高 初始化配置 初始化作用 影响数据库的启动 影响到客户端的功能 初始化方法 初始化配置文件(例如/etc/my.cnf) 启动命令行上进行设置(例如:mysqld_safe mysqld) 预编译时设置(仅限于编译安装时设置) 配置文件格式 [标签] xxx=xxx [标签] xxx=xxx 标签归类 服务器端： [mysqld] [mysqld_safe] [server] 客户端： [mysql] [mysqladmin] [mysqldump] [client] 模板文件 (5.7) # 服务器端配置 [mysqld] # 用户 user=mysql # 软件安装目录 basedir=/application/mysql # 数据路径 datadir=/data/mysql/data # socket文件位…

MySQL 体系结构
type: item
date: 2021-03-31T16:47:00+00:00
description: 内容介绍： C/S 结构 实例构成 SQL语句分类 三层结构 逻辑存储结构 物理存储结构 innodb(段页区) C/S 结构 连接方式： TCP/IP mysql -u [用户名] -p [密码] -h [主机] -P [端口] Socket mysql -u [用户名]-p [密码] -S /tmp/mysql.sock 实例构成 mysqld + master thread + 干活的Thread + 预分配的内存 守护进程 主进程 进程 内存 SQL语句 结构化的查询语言 DQL 数据查询语言 DDL 数据定义语言 DML 数据操作语言 DCL 数据控制语言 例如：mysql> select user,host from mysql.user; 三层结构 连接层 连接协议 - Socket - TCP/IP 验证用户名(root@localhost)密码合法性，进行授权表匹配 派生一个专用连接线程（接收SQL，返回结果） SQL层 验证SQL语法和SQL_MODE 验证语义 验证权限 解析器进行语句解析，生成执行计划（解析树） 优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划 CUP IO MEM 执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法 提供query cache(默认关闭)，一般不开，会用redis 记录操作日志（binlog），默认关闭 存储引擎层 与物理硬件交互 根据SQL层提供的取数据的方法;获取数据返回给SQL;结构化成表,连接层线程返回给用户 逻辑存储结构 库 表 列 数据行 表属性 物理存储结构 库：使用 FS 上的目录来表示 表： MyISAM(ext2) user.frm ： 存储的表结构（列，列属性） user.MYD : 存储的数据记录 user.MYI ： 存储索引 InnoDB(XFS) time_zone.frm ： 存储的表结构（列，列属性） time_zone.ibd ： 存储的数据记录和索引 ibdata1 : 数据字典信息 innodb(段页区) 区：16K 页：64个区组成，大小为1M 段：多个连续的页组成（分区表除外）

mysql 5.7 二进制安装
type: item
date: 2021-03-28T08:02:00+00:00
description: 二进制 安装 下载并上传软件至 /server/tools [root@db01 ~]# mkdir -p /server/tools [root@db01 ~]# cd /server/tools/ [root@db01 /server/tools]# yum install -y lrzsz [root@db01 /server/tools]# ls mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 解压软件 [root@db01 /server/tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@db01 ~]# mkdir /application [root@db01 /server/tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql 创建用户 处理原始环境 [root@db01 ~]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y [root@db01 ~]# rpm -qa |grep mariadb [root@db01 ~]# useradd -s /sbin/nologin mysql 设置环境变量 vim /etc/profile export PATH=/application/mysql/bin:$PATH [root@db01 ~]# source /etc/profile [root@db01 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper 创建数据路径并授权 添加一块新磁盘模拟数据盘 格式化并挂载磁盘 [root@db01 ~]# mkfs.xfs /dev/sdc [root@db01 ~]# mkdir /data [root@db01 ~]# blkid [root@db01 ~]# vim /etc/fstab [root@db01 ~]# UUID="b7fde522-aa37-412a-9584-8313a673c5cc" /data xfs defaults 0 0 [root@db01 ~]# mount -a [root@db01 ~]# df -h 授权 chown -R mysql.mysql /application/* chown -R mysql.mysql /data 初始化数据（创建系统数据） 5.6 版本 /application/mysql/scripts/mysql_install_db 5.7 版本 正常版 [root@db01 ~]# mkdir /data/mysql/data -p [root@db01 ~]# chown -R mysql.mysql /data [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data 说明： --initialize 参数： 对于密码复杂度进行定制：12位，4种 密码过期时间：180 给root@localhost用户设置临时密码 报错: mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解决： ```linux [root@db01 ~]# yum install -y libaio-devel [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data2019-06-13T04:21:27.706564Z 1 [Note] A temporary password is generated for root@localhost: =mrV)_16is4U - 无限制版 --initialize-insecure 参数： 无限制，无临时密码 ```linux [root@db01 /data/mysql/data]# \rm -rf /data/mysql/data/* [root@db01 ~]# mysqld --initialize-insecure…