Mariadb 主从复制&读写分离 数据库 主从&读写分离 节点规划 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 mariadb [root@mysql2 ~]# yum install -y mariadb mariadb-server [root@mysql2 ~]# systemctl start mariadb && systemctl enable mariadb # 初始化mariadb [root@mysql1 ~]# mysql_secure_installation ## 注意：Disallow root login remotely? [Y/n] n ## 其他为 yes ;密码设置为 000000 [root@mysql2 ~]# mysql_secure_installation ## 注意：Disallow root login remotely? [Y/n] n ## 其他为 yes ;密码设置为 000000 # 安装JDK & 查看JDK版本 [root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel [root@mycat ~]# java -version # 二进制安装 mycat ## SecureXF 上传 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz [root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/ # 配置环境变量 [root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile [root@mycat ~]# source /etc/profile 3、配置服务 ### 主从复制 # mysql1 [root@mysql1 ~]# vi/etc/my.cnf.d/server.cnf # 在 [mysqld]标签下添加 log_bin = mysql-bin #记录操作日志 binlog_ignore_db = mysql #不同步mysql系统数据库 server_id = 77 #数据库集群中的每个节点id都要不同 # 重启数据库 [root@mysql1 ~]# systemctl restart mariadb # 进入数据库并配置 [root@mysql1 ~]# mysql -uroot -p000000 MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "000000"; MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000'; # mysql2 [root@mysql2 ~]# vi/etc/my.cnf.d/server.cnf # 在 [mysqld]标签下添加 log_bin = mysql-bin binlog_ignore_db = mysql server_id = 78 [root@mysql2 ~]# systemctl restart mariadb [root@mysql2 ~]# mysql -uroot -p000000 MariaDB [(none)]> change master to master_host='mysql1',master_user='user',master_password='000000'; ### 读写分离 # mycat # schema.xml配置文件 ;将原内容替换为下面文字 [root@mycat ~]# vi /usr/local/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="test" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="172.30.11.12:3306" user="root" password="000000"> <readHost host="hostS1" url="172.30.11.13:3306" user="root" password="000000" /> </writeHost> </dataHost> </mycat:schema> # 修改配置文件权限 [root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml # 编辑mycat的访问用户 [root@mycat ~]# vi /usr/local/mycat/conf/server.xml # 在配置文件的最后部分，修改 password schemas <user name="root"> <property name="password">000000</property> <property name="schemas">USERDB</property # 然后删除如下几行： <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> # 启动Mycat服务 [root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start 4、验证 ### 主从复制 # mysql2 # 登录查询主从状态 [root@mysql2 ~]# mysql -uroot -p000000 MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G # mysql1 创建库test，并在库test中创建表company，插入表数据 [root@mysql1 ~]# mysql -uroot -p000000 MariaDB [(none)]> create database test; MariaDB [(none)]> use test; MariaDB [test]> create table company(id int not null primary key,name varchar(50),addr varchar(255)); MariaDB [test]> insert into company values(1,"alibaba","china"); MariaDB [test]> select * from company; # mysql2 查看数据库列表。找到test数据库，查询表 验证从数据库的复制 [root@mysql2 ~]# mysql -uroot -p000000 MariaDB [(none)]> use test; MariaDB [test]> show tables; MariaDB [test]> select * from company; ### 读写分离 # 安装客户端工具 [root@mycat ~]# yum install -y MariaDB-client # 使用mysql命令查看Mycat服务的逻辑库USERDB [root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000 MySQL [(none)]> show databases; MySQL [(none)]> use USERDB MySQL [USERDB]> show tables; MySQL [USERDB]> select * from company; # 使用mysql命令对表company添加一条数据 MySQL [USERDB]> insert into company values(2,"bastetball","usa"); MySQL [USERDB]> select * from company; # 验证Mycat服务对数据库读写操作分离 [root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show @@datasource;'

Redis 基础操作 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> ttl key5 (integer) -1 列表 LPUSH RPUSH LRANGE db01:6379> RPUSH LST 1 2 3 4 5 (integer) 5 db01:6379> get lst (error) WRONGTYPE Operation against a key holding the wrong kind of value db01:6379> lrange lst 0 -1 1) "1" 2) "2" 3) "3" 4) "4" 5) "5" RPOP LPOP db01:6379> LPOP LST "1" db01:6379> lrange LST 0 -1 1) "2" 2) "3" 3) "4" 4) "5" db01:6379> RPOP LST "5" db01:6379> lrange LST 0 -1 1) "2" 2) "3" 3) "4" 哈希 HMSET HMGAT HGAT db01:6379> HMSET acha name acha age 20 six m OK db01:6379> HMGET acha name age six 1) "acha" 2) "20" 3) "m" db01:6379> HGET acha age "20" 集合 SADD SMEMBERS db01:6379> sadd set1 1 2 3 (integer) 3 db01:6379> smembers set1 1) "1" 2) "2" 3) "3" SREM db01:6379> srem set1 2 3 (integer) 2 db01:6379> smembers set1 1) "1" SDIFF (差集) db01:6379> sadd set1 1 2 3 4 (integer) 2 db01:6379> sadd set2 1 4 5 (integer) 3 db01:6379> sdiff set1 set2 1) "2" 2) "3" SINTER (交集) db01:6379> sinter set1 set2 1) "1" 2) "4" SUNION (并集) db01:6379> sunion set1 set2 1) "1" 2) "2" 3) "3" 4) "4" 5) "5" 持久化 RDB 可以在指定的时间间隔内生成数据集的 时间点快照 优点：速度快，适合于用做备份，主从复制也是基于RDB持久化功能实现的 缺点：会有数据丢失 配置参数 vim /data/6379/redis.conf dir /data/6379 dbfilename dump.rdb #900秒（15分钟）内有1个更改 save 900 1 #300秒（5分钟）内有10个更改 save 300 10 #60秒内有10000个更改 save 60 10000 AOF 记录服务器执行的所有写操作命令，并在服务器启动时，通过重新执行这些命令来还原数据集。AOF 文件中的命令全部以 Redis 协议的格式来保存，新命令会被追加到文件的末尾 优点：可以最大程度保证数据不丢 缺点：日志记录量级比较大 配置参数 #日志文件名 appendfilename "appendonly.aof" #是否打开aof日志功能 appendonly yes #每1个命令,都立即同步到aof appendfsync always #每秒写1次 appendfsync everysec #写入工作交给操作系统,由操作系统判断缓冲区大小,统一写入到aof appendfsync no Tip: RDB & AOF 可以同时开启 安全认证 redis默认开启了保护模式，只允许本地回环地址登录并访问数据库 protected-mode yes/no （保护模式，是否只允许本地访问） bind：指定IP进行监听 [root@db01 ~]# vim /opt/redis_cluster/redis_6379/conf/redis_6379.conf bind 10.35.172.71 127.0.0.1 requirepass：密码 [root@db01 ~]# vim /opt/redis_cluster/redis_6379/conf/redis_6379.conf requirepass sa 密码验证 [root@db01 ~]# redis-cli -a sa 127.0.0.1:6379> set k1 v1 OK or [root@db01 ~]# redis-cli -a sa 127.0.0.1:6379> set k1 v1 OK 主从复制 一个主节点可以有多个从节点 每个从节点只能由一个主节点 配置方法： 在配置文件中加入 slaveof {masterHost} {masterPort} 启动命令后加 slaveof {masterHost} {masterPort} 直接使用命令 slaveof {masterHost} {masterPort} 查看状态信息： Info replication 断开复制: slaveof no one Tip:断开主从关系，从节点升为主节点，不会清空原有数据 哨兵 Sentinel (哨兵)，基于主从复制，为redis提供高可用性，故障时，可以自动切换主从 作用： 监控(Monitoring) 提醒(Notification) 自动故障迁移(Automatic failover) 部署： 创建数据目录 mkdir -p /data/redis_cluster/redis_26379 创建配置目录 mkdir -p /opt/redis_cluster/redis_26379/{conf,pid,logs} 生成配置文件 vim /opt/redis_cluster/redis_26379/conf/redis_26379.conf bind 10.35.172.71 port 26379 daemonize yes logfile /opt/redis_cluster/redis_26379/logs/redis_26379.log dir /data/redis_cluster/redis_26379 sentinel monitor mymaster 10.35.172.71 6379 2 sentinel down-after-milliseconds mymaster 3000 sentinel parallel-syncs mymaster 1 sentinel failover-timeout mymaster 18000 配置文件说明 #主节点别名 主节点ip和端口 判断主节点失败,两个sentinel节点同意 sentinel monitor mymaster 10.0.0.51 6379 2 #选项指定 Sentinel 认为服务器已经断线所需的毫秒数。 sentinel down-after-milliseconds mymaster 30000 #向新的主节点发起复制操作的从节点个数，1轮询发起复制 sentinel parallel-syncs mymaster 1 #故障转移超时时间 sentinel failover-timeout mymaster 180000 启动 redis-sentinel /opt/redis_cluster/redis_26379/conf/redis_26379.conf 登录 redis-cli -h db01 -p 26379 查询 Info Sentinel 手动切换主节点 将从节点 slavepriority 配置为0 设置主节点 将slave-priority调回原值 #查询命令 CONFIG GET slave-priority #设置命令 CONFIG SET slave-priority 0 #主动切换 sentinel failover mymaster

索引 索引 索引的作用 类似于一本书中的目录,起到优化查询的作用 索引的分类(算法) 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 语句 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 utf8mb4; 建表规范 表名小写字母,不能数字开头 不能是保留字符,使用和业务有关的表名 选择合适的数据类型及长度 每个列设置 NOT NULL + DEFAULT ;对于数据0填充,对于字符使用有效字符串填充 每个列设置注释 表必须设置存储引擎和字符集 主键列尽量是无关列数字列,最好是自增长 enum类型不要保存数字,只能是字符串类型 查询表信息 SHOW TABLES; SHOW CREATE TABLE stu; DESC stu; 创建一个表结构一样的表 CREATE TABLE test LIKE stu; 删表(不代表生产操作) DROP TABLE test; 修改 在stu表中添加qq列 DESC stu; ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号'; 在sname后加微信列 ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname; 在id列前加一个新列num ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST ; DESC stu; 把刚才添加的列都删掉(危险,不代表生产操作 ALTER TABLE stu DROP num; DESC stu; ALTER TABLE stu DROP qq; ALTER TABLE stu DROP wechat; 修改sname数据类型的属性 DESC stu; ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名'; 将gender 改为 sex 数据类型改为 CHAR 类型 ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别'; DCL grant revoke DML 插入数据(insert) 简单 INSERT stu VALUES(1,'zs',18,'m',NOW()); SELECT * FROM stu; 规范 NSERT INTO stu(id,sname,age,sex,intime) VALUES (2,'ls',19,'f',NOW()); 录入多行 INSERT INTO stu(sname,age,sex) VALUES ('aa',11,'m'), ('bb',12,'f'), ('cc',13,'m'); update (一定加where条件) UPDATE stu SET sname='aaa'; SELECT * FROM stu; UPDATE stu SET sname='bb' WHERE id=6; delete (一定要加where条件) DELETE FROM stu; DELETE FROM stu WHERE id=9; 生产中屏蔽delete功能，使用update替代delete ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ; UPDATE stu SET is_del=1 WHERE id=7; SELECT * FROM stu WHERE is_del=0; DQL DQL介绍 SELECT SHOW SELECT 语句的应用 SELECT单独使用的情况 mysql> select @@basedir; mysql> select @@port; mysql> select @@innodb_flush_log_at_trx_commit; mysql> show variables like 'innodb%'; mysql> select database(); mysql> select now(); SELECT通用语法表(单表) select 列 from 表 where 条件 group by 条件 having 条件 order by 条件 limit 学习环境说明 world 数据库 city 城市表 country 国家表 countrylanguage 语言表 city表结构 ID 城市序号 name 城市名代号 countrycode 国家 district 区域 population 人口 SELECT 配合 FROM 子句使用 SELECT 列 from 表 示例： 查询表中所有的信息(生产中几乎是没有这种需求的) USE world ; SELECT id,NAME ,countrycode ,district,population FROM city; 或者 SELECT * FROM city; 查询表中 name 和population的值 SELECT NAME ,population FROM city; SELECT 配合 WHERE 子句使用 select 列 from 表 where 过滤条件 示例: 等值条件查询 查询中国所有的城市名和人口数 SELECT NAME,population FROM city WHERE countrycode='CHN'; 比较判断查询 世界上小于100人的城市名和人口数 SELECT NAME,population FROM city WHERE population<100; 逻辑连接符 查询中国人口数量大于1000w的城市名和人口 SELECT NAME,population FROM city WHERE countrycode='CHN' AND population>8000000; 查询中国或美国的城市名和人口数 SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA'; 查询人口数量在500w到600w之间的城市名和人口数 SELECT NAME,population FROM city WHERE population>5000000 AND population<6000000; 或者 SELECT NAME,population FROM city WHERE population BETWEEN 5000000 AND 6000000; 模糊查询 查询一下contrycode中带有CH开头,城市信息 SELECT * FROM city WHERE countrycode LIKE 'CH%'; TIP:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差。如果业务中有大量需求,我们用"ES"来替代。 in 语句 查询中国或美国的城市信息 SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA'; 或者 SELECT NAME,population FROM city WHERE countrycode IN ('CHN' ,'USA'); GROUP BY 将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作 统计每个国家,城市的个数 SELECT countrycode ,COUNT(id) FROM city GROUP BY countrycode; 统计每个国家的总人口数. SELECT countrycode,SUM(population) FROM city GROUP BY countrycode; 统计每个 国家 省 的个数 SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode; 统计中国 每个省的总人口数 SELECT district, SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ; 统计中国 每个省城市的个数 SELECT district, COUNT(NAME) FROM city WHERE countrycode='CHN' GROUP BY distric 统计中国 每个省城市的名字列表GROUP_CONCAT() SELECT district, GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district ; anhui : hefei,huaian .... SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city WHERE countrycode='CHN' GROUP BY district ; ORDER BY 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列 SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC ; LIMIT 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名 SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 0; LIMIT M,N :跳过M行,显示一共N行 LIMIT Y OFFSET X: 跳过X行,显示一共Y行 小结 select disctrict , count(name) from city where countrycode='CHN' group by district having count(name) >10 order by count(name) desc limit 3; union 和 union all 多个结果集合并查询的功能 查询中或者美国的城市信息 SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA'; 改写 SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA'; union 和 union all 的区别 ? union all 不做去重复 union 会做去重操作 练习题 统计中国每个省的总人口数，只打印总人口数小于100w的 SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<1000000; 查看中国所有的城市，并按人口数进行排序(从大到小) SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC; 统计中国各个省的总人口数量，按照总人口从大到小排序 SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC ; 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名 SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3; 多表连接查询(内连接) 作用 单表数据不能满足查询需求时 查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数 SELECT countrycode,NAME,population FROM city WHERE population<100; PCN Adamstown 42 SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN'; 多表连接基本语法 student ：学生表 =============== sno： 学号 sname：学生姓名 sage： 学生年龄 ssex： 学生性别 teacher ：教师表 ================ tno： 教师编号 tname：教师名字 course ：课程表 =============== cno： 课程编号 cname：课程名字 tno： 教师编号 score ：成绩表 ============== sno： 学号 cno： 课程编号 score：成绩 多表连接例子 统计zhang3,学习了几门课 SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3'; 查询zhang3,学习的课程名称有哪些? SELECT student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3' GROUP BY student.sname; 查询oldguo老师教的学生名和个数. SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' GROUP BY teacher.tname; 查询oldguo所教课程的平均分数 SELECT teacher.tname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo' GROUP BY sc.cno; 每位老师所教课程的平均分,并按平均分排序 SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno GROUP BY teacher.tname,course.cname ORDER BY AVG(sc.score); 查询oldguo所教的不及格的学生姓名 SELECT teacher.tname,student.sname,sc.score FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' AND sc.score<60; 查询所有老师所教学生不及格的信息(扩展) SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score)) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teacher.tno; 别名应用 表别名 (全局调用) SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno; 列别名（having 和 order by 调用） SELECT t.tname as 讲师名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) as 不及格的 FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60; 扩展类内容-元数据获取 元数据介绍及获取 元数据是存储在"基表"中。 通过专用的DDL语句，DCL语句进行修改 通过专用视图和命令进行元数据的查询 information_schema中保存了大量元数据查询的试图 show 命令是封装好功能，提供元数据查询基础功能 information_schema的基本应用 tables 视图的应用 use information_schema; mysql> desc tables; TABLE_SCHEMA 表所在的库名 TABLE_NAME 表名 ENGINE 存储引擎 TABLE_ROWS 数据行 AVG_ROW_LENGTH 平均行长度 INDEX_LENGTH 索引长度 示例 USE information_schema; DESC TABLES; 显示所有的库和表的信息 SELECT table_schema,table_name FROM information_schema.tables; 以以下模式 显示所有的库和表的信息 world city,country,countrylanguage SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema; 查询所有innodb引擎的表 SELECT table_schema,table_name ,ENGINE FROM information_schema.tables WHERE ENGINE='innodb'; 统计world下的city表占用空间大小 表的数据量=平均行长度*行数+索引长度 AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world' AND table_name='city'; 统计world库数据量总大小 SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 FROM information_schema.TABLES WHERE table_schema='world'; 统计每个库的数据量大小，并按数据量从大到小排序 SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_KB DESC ; 配合concat()函数拼接语句或命令 示例： 模仿以下语句,进行数据库的分库分表备份。 mysqldump -uroot -p123 world city >/bak/world_city.sql SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name ," >/bak/",table_schema,"_",table_name,".sql") FROM information_schema.tables; 模仿以下语句,进行批量生成对world库下所有表进行操作 ALTER TABLE world.city DISCARD TABLESPACE; SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;") FROM information_schema.tables WHERE table_schema='world'; show介绍 show databases; 查看数据库名 show tables; 查看表名 show create database xx; 查看建库语句 show create table xx; 查看建表语句 show processlist; 查看所有用户连接情况 show charset; 查看支持的字符集 show collation; 查看所有支持的校对规则 show grants for xx; 查看用户的权限信息 show variables like '%xx%' 查看参数信息 show engines; 查看所有支持的存储引擎类型 show index from xxx 查看表的索引信息 show engine innodb status\G 查看innoDB引擎详细状态信息 show binary logs 查看二进制日志的列表信息 show binlog events in '' 查看二进制日志的事件信息 show master status ; 查看mysql当前使用二进制日志信息 show slave status\G 查看从库状态信息 show relaylog events in '' 查看中继日志的事件信息 show status like '' 查看数据库整体状态信息

MySQL 用户和权限管理 用户和权限管理 用户作用 登录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文件位置 socket=/tmp/mysql.sock # 服务器id号 server_id=6 # 短口号 port=3306 # 客户端配置 [mysql] # socket文件位置 socket=/tmp/mysql.sock 配置文件读取顺序 查询： mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 强制使用自定义配置文件 --defautls-file [root@db01 tmp]# mysqld_safe --defaults-file=/tmp/aa.txt & MySQL的连接管理 mysql连接命令 注意:提前应该将用户授权做好 # 授权 mysql> grant all on *.* to root@'10.0.0.%' identified by '123'; # TCPIP mysql -uroot -p -h 10.0.0.51 -P3306 # Socket mysql -uroot -p -S /tmp/mysql.sock 客户端工具 dbforger sqlyog navicat