Mysql 5.7
DML
-
replace
在插入一条记录之前，需要确认一下表里这条记录的某个字段值是否重复，如果重复，需要更新一下其他的列，如果没有重复，就直接插入
replace into t(id,update_time) values(1,now());再执行replace into的时候，mysql会首先寻找主键或者唯一键来判断记录是否冲突，如果冲突，就执行delete+insert操作，如果不冲突就执行insert操作
在mysql的某些版本中，在存在冲突的时候，主库会执行delete+insert操作，而binlog中记录的是update操作，从而导致从库的自增id与实际不符。在主从切换时候需要手动调整
DDL
- 表的设计原则
- 见名知意
- 满足范式
- 选型合理
- 字符集选择utf8,utf8mb64
- 引擎选择innodb
- 行锁
- 高性能
- 支持事务
- 尽量避免长宽表，字段不超过50个
- 表和字段添加注释
- 禁止在数据库中存储图片，文件等大数据，建议放到oss服务器
- 命名规范
- 小写字母，并采用下划线分割
- 临时库，表以tmp为前缀，并以日期为后缀，例如tmp_table_20220104
- 不建议使用存储过程、触发器、视图等
- 事务
- 原子性A
- 一致性C
- 隔离性I
- 持久性D
- 事务隔离级别
- RU读未提交【允许脏读】
- RC读已提交【不可重复读】
- RR可重复度【幻读】
- S串行化
- 字段设计规范
- 遵循少而精
- 尽量选择最小的数据类型
- 适当冗余
- 所有字段均需添加not null属性
- 使用unsigned存储非负数值
- 添加unsigned属性，可以在同样的占用空间下，增加一倍的数据存储范围，例如int类型(-2147483648,2147483647)，而添加unsigned之后，取值范围为(0,4294967295)
- 使用int储存ip值
- inet_ntoa()和inet_aton可以实现ip和十进制数字之间的相互转换
- 尽可能小的使用varchar字段
- 不要随意字段之间的类型转换
- 字段需要有默认值
- 控制索引、包含字段的数量
- 禁止冗余、重复索引
- 禁止索引null值
- 禁止使用外键
- 合理利用索引覆盖避免io
- 大批量更新拆小粒度
- sql设计
- 用in代替or
- 禁止隐式类型转换 — 会使索引失效
- 避免使用join和子查询
- 子查询和超过3表以上的join非常容易造成慢sql而影响到mysql的整体性能
- 避免在mysql的索引列进行数学运算和函数运算
- 避免大事务
- 获取大量数据时，建议分批获取数据，每次获取数据少于500条，结果集应小于1M
- 用union all代替union
- union会去掉重复数据，并且进行排序
- union all会不管排序和去重，直接把结果合并，相比于排序省去了很多计算资源
- 避免全表扫描
- 禁止使用前导%去查询
- 可以另起一个从库单独用来进行类似的查询操作
- 禁止对null进行相关判断
- 拼接sql时注意防止注入
- update和delete带where条件
Redis
- 命名规范
- 使用规范
- 容量，qps预估
- 单节点qps在2w以内
- 结构选择
- String
- k|v
- Hash
- Set
- Sorted Set
- List
- String
- 使用规范
- 冷热数据分离
- 不同业务数据分开存储
- 存储key一定要设置超时时间
- 必须对存储的大文本数据进行先压缩后存储，严禁存储图片，视频等
- 线上redis禁止使用危险命令
- hmget
- hgetall
- hvals
- hkeys
- smembers
- keys
- …
- 使用列表作为队列的时候，对队列长度进行监控
故障排查
- 无索引导致全表扫描
- 索引列上使用函数导致索引失效
- 数据类型隐式类型转换导致索引失效
- 使用union all代替or【拆sql语句，可以使用索引】
数据库主从搭建【docker】
-
拉取镜像
docker pull mysql:5.7.37 -
docker run 创建主容器
docker run \ -p 3307:3306 \ --name=mysql-master \ -v /mydata/mysql-master/log:/var/log/mysql \ -v /mydata/mysql-master/data:/var/lib/mysql \ -v /mydata/mysql-master/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql:5.7.37 -
docker ps 查看运行情况
-
在/mydata/mysql-master/conf/文件夹下创建my.cnf配置文件文件
[mysqld] ## 设置server-id，同一局域网全局唯一 server-id=101 ## 指定不需要同步的数据库名称 binlog-ignore-db=mysql ## 开启二进制日志功能 log-bin=mall-mysql-bin ## 设置二进制日志使用内存大小(事务) binlog_cache_size=1M ## 设置使用二进制的日志格式（mixed，statement，row） binlog-format=mixed ## 二进制日志过期清理时间，默认值是0，表示不自动清理 expire_logs_days=7 ## 如果主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断 ## 如：1062错误是指一些主键重复，1032错误是因为主从数据库不一致 slave_skip_errors=1062 -
创建上述配置文件之后，重新重启容器实例
-
进入容器，并在maser容器实例内部创建数据同步用户
CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%'; -
docker run 创建从容器
docker run \ -p 3308:3306 \ --name=mysql-slave \ -v /mydata/mysql-slave/log:/var/log/mysql \ -v /mydata/mysql-slave/data:/var/lib/mysql \ -v /mydata/mysql-slave/conf:/etc/mysql \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql:5.7.37 -
docker ps 查看运行情况
-
在/mydata/mysql-slave/conf/文件夹下创建my.cnf配置文件文件
[mysqld] ## 设置server-id，同一局域网全局唯一 server-id=102 ## 指定不需要同步的数据库名称 binlog-ignore-db=mysql ## 开启二进制日志功能,以备slave1作为其他数据库实例的master时使用 log-bin=mall-mysql-slave1-bin ## 设置二进制日志使用内存大小(事务) binlog_cache_size=1M ## 设置使用二进制的日志格式（mixed，statement，row） binlog-format=mixed ## 二进制日志过期清理时间，默认值是0，表示不自动清理 expire_logs_days=7 ## 如果主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断 ## 如：1062错误是指一些主键重复，1032错误是因为主从数据库不一致 slave_skip_errors=1062 ## relay_log配置中继日志 relay_log=mall-mysql-relay-bin ## log_slave_updates表示slave将复制事件写进自己的二进制日志 log_slave_updates=1 ## slave设置为只读（具有super权限的用户） read_only=1 -
重启从容器，并进入容器查看
mysql -uroot -p; root -
执行如下语句：
change master to master_host='主机ip',master_user='slave',master_password='123456',master_port='3307',master_log_file='mall-mysql-bin.000001',mysql_log_pos=617,mysql_connect_retry=30;参数说明 master_host 主数据库ip地址 master_user 在主数据库创建用于同步数据的用户账号 master_password 在主数据库创建用于同步数据的用户密码 master_port 主数据库运行端口 master_log_file 指定从数据库复制数据的日志文件，通过查看主数据的庄毅啊，获取File参数 mysql_log_pos 指定从数据库从哪个位置开始复制数据，通过查看主数据状态，获取position参数 mysql_connect_retry 连接失败重试的时间间隔，单位sbinlog打开设置
win10
- 查看数据库binlog是否打开
show variables like 'log_bin';
show variables like 'log_bin%';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2aqv85da-1644390879899)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616101339651.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c4QNpv2M-1644390879901)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616112031907.png)]
-
打开binlog日志
2.1 找到mysql的配置文件mysql.ini【在mysql的安装目录下】
2.2 在mysqld下配置如下
#开启binlog日志 server_id=1 log-bin=mysql-bin binlog-format=ROW[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m7xZ8hsl-1644390879903)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616112246377.png)]
binlog-format:主从同步的形式有三种
statement 会将对数据库操作的sql语句写入到binlog中
row 会将每一条数据的变化写入到binlog中。
mixed statement与row的混合。Mysql决定什么时候写statement格式的，什么时候写row格式的binlog
2.3 重启服务，再次查询即可
net stop mysql net start mysqlshow variables like 'log_bin%';[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P9FWI9EW-1644390879903)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616112407603.png)]
linux
- 查看binlog状态
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1CVTzz6S-1644390879904)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616113128885.png)]
-
找到配置文件并修改
-
配置文件的位置
/etc/my.cnf -
添加配置
# 开启二进制日志 server-id=1 log-bin=/usr/local/mysql/data/mysql-bin
-
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4fD6aWD3-1644390879905)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616113847798.png)]
docker
-
配置映射到本地文件，本地文件配置如下
[mysqld] #binlog setting log-bin=/var/lib/mysql/mysql-bin server-id=123454 binlog-format=ROW -
重启mysql，查询发现binlog没有开启
docker restart mysql -
这个坑在网上找了好久才找到，记录下过程
首先，查看docker的关于my.cnf的日志发现有这么一句：
mysqld: [Warning] World-writable config file '/etc/mysql/``my.cnf``' is ignored.在网上查看是由于配置文件的权限导致的https://blog.csdn.net/qilovehua/article/details/45508925，详情可参考这篇博文
然后进入docker的mysql容器去查看，果然my.cnf的权限是777
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LPOhkoXk-1644390879905)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20210616142116847.png)]
将其改为644
chmod 644 my.cnf再次重启mysql容器，查询看binlog成功打开
但是，如果后期你对映射的配置文件有任何的修改，他会被再次映射到/etc/mysql/my.cnf这个文件并且权限重置为777，需要再次修改
canal使用调研
canal-server
https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
canal-adapter
https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.adapter-1.1.5.tar.gz
数据库同步的命令
/opt/zbox/run/mysql/mysqldump -uroot -pZenTao-Cestc123 --databases zentaoep | /opt/zbox/run/mysql/mysql -h 10.255.237.120 -uroot -pwhut.VINCENT920812
/opt/zbox/run/mysql/mysqldump -uroot -pZenTao-Cestc123 --databases zentaoep | /opt/zbox/run/mysql/mysql -h 10.101.42.163 -uroot -proot
/opt/zbox/run/mysql/mysqldump -uroot -pZenTao-Cestc123 --lock-tables=0 zentaoep > /opt/zbox/mysql_bak_`date +%Y%m%d`.sql
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
