Python全栈（三）数据库优化之13.MySQL高级-主从复制和数据库总结
一、主从复制
1.目的和解决的问题：
目的：读写分离，一个数据库只负责读，一个只负责写。
解决的问题：
数据分布:随意停止或开始复制，并在不同地理位置分布数据备份；
负载均衡:降低单个服务器的压力；
故障切换:帮助应用程序避免单点失败；
升级测试:可以使用更高版本的MySQL作为从库。
2.基本原理：
如图
3.复制的三步骤：
（1）master将改变记录到二进制日志，这些记录过程叫做二进制日志事件binary log events；
（2）slave将master的binary log events拷贝到它的中继日志；
（3）slave重做中继日志中的事件,将改变应用到自己的数据库中。
MySQL复制是异步的且串行的。
4.复制的基本原则：
每个slave只有一个master；
每个slave只能有一个唯一的服务器ID；
每个master可以有多个salve。
5.一主一从常见配置：
MySQL版本一致且后台服务可以运行；
主从主机可以相互通信；
主从配置都在[mysqld]结点下,都是小写。
主机（Windows系统）配置my.ini：
server_id=2 #[必须]主服务器唯一ID，不能与从机重复
log-bin=自己本地的路径/mysqlbin（例如mysql-bin） #[必须]启用二进制日志
log-err = 自己本地的路径/mysqlerr #[可选]启用错误日志
1
2
3
从机（Linux Ubuntu系统）配置mysqld.cnf(/etc/mysql/mysql.conf.d/mysqld.cnf)：
bind-address=0.0.0.0 #让任何IP地址连接
server_id=1 #[必须]主服务器唯一ID
log-bin=自己本地的路径/mysqlbin（例如/var/log/mysql/mysql-bin.log） #[可选]启用二进制日志
1
2
3
修改过配置文件之后，要重启MySQL服务：
service mysql restart
1
关闭Linux防火墙：
service iptables stop
1
关闭Windows防火墙：
关闭专用网络防火墙。
在Windows主机上建立账户并授权slave：
create user 'slave'@'从机数据库IP' identified with mysql_native_password by 'password';
#例如create user 'zhangsan'@'192.168.1.1' identified with mysql_native_password by '123456';
1
2
授权并查看状态：
grant replication slave on *.* to 'slave'@'从机数据库IP' identified by 'password';
#例如grant replication slave on *.* to 'zhangsan'@'192.168.1.1' identified by '123456';
show master status;
1
2
3
4
记录下File和position的值。
6.配置Linux从机
change master to master_host = '192.168.0.161',
master_user = 'jerry',
master_password = '123456',
master_log_file = 'binlog.000004',
master_log_pos= 908;
7.测试是否配置成功
start slave; #启动从服务器复制功能
show slave status;
1
2
3
下面两个参数都是yes,则说明主从配置成功：
slave_io_running:yes；
slave_sql_running:yes。
主从复制总结：
当读数据和写数据都是对同一个数据库进行操作时，可能会遇到性能瓶颈，所以新增一个与原数据库相同的数据库作为从数据库，即备份，实现读写分离（主数据库写，从数据库读），既能提高读写效率，又能提高安全性，但是会产生一定延迟。
二、MySQL操作规范
1.命名规范
表名建议使用有业务意义的英文词汇，必要时可加数字和下划线，并以英文字母开头。
库、表、字段全部采用小写：
MySQL在Linux下默认是区分大小写的，而在Windows下不区分大小写。因此，防止出现问题，建议都设置为小写。
避免用 MySQL 的保留字。
命名（包括表名、列名）禁止超过 30 个字符。
临时库、表名必须以tmp为前缀，并以日期为后缀，如：tmp_shop_info_20200120。
备份库、表必须以bak为前缀，并以日期为后缀，如：bak_shop_info_20200120。
索引命名：
非唯一索引必须按照"idx_字段名称"或"idx_表名_字段名称"进行命名；
唯一索引必须按照"uniq_字段名称"或"uniq_表名_字段名称"进行命名。
2.设计规范
主键：
表必须有主键；
不使用更新频繁的列做主键；
尽量不选择字符串列做主键；
不使用 UUID（不重复的字符串）、MD5 HASH 做主键；
默认使用非空的唯一键。
如无特殊要求，建议都使用 InnoDB 引擎。
默认使用 utf8mb4 字符集，数据排序规则使用 utf8mb4_general_ci：
utf8mb4 为万国码，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情。
所有表、字段都需要增加 comment 来描述此表、字段所表示的含义：
如data_status TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效'。
尽可能不使用 TEXT、BLOB 类型：
原因：会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用TEXT、BLOB类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。字段用多大就取多大，避免资源浪费和性能下降。
单表列数目建议小于30。
3.SQL语句规范
避免隐式转换：
varchar要加引号。
尽量不使用select *,只select需要的字段：
读取不需要的列会增加CPU、IO、NET消耗，并且不能有效的利用覆盖索引。使用SELECT *容易在增加或者删除字段后导致程序报错。
建议将子查询转换为关联查询。
建议应用程序捕获SQL异常，并有相应处理：
可以避免数据库攻击。
4.行为规范
批量导入、导出数据必须提前通知DBA协助观察；
不在业务高峰期批量更新、查询数据库；
删除表或者库要求尽量先重命名rename、备份，观察几天，确定对业务没影响，再drop
三、数据库基础总结
1.数据类型
整数：tinyint、smallint、mediumint、int、bigint；
实数：float、double；
字符串：varchar、char、text、blob；
日期时间：timestamp、datetime。
2.列属性
自增auto_increment；
默认值default；
非空not null；
零填充zerofill；
无符号unsigned。
3.数据库操作
连接数据库：
mysql -uroot -p #更安全
或者
mysql -u root -proot
1
2
3
退出数据库：
exit
--或者quit
1
2
查看所有数据库：
show databases;
1
创建数据库：
#创建数据库
create database 数据库名字 charset = utf8;
#查看创建数据库的命令
show create database 数据库名字;
#使用数据库
use mydatabase;
4.数据库表操作
查看当前数据库中所有表：
show tables;
1
创建表：
create table 数据表名字(字段 类型 约束[,字段 类型 约束]);
1
查看表：
desc demo1;
1
查看表的创建语句：
show create table salary;
1
5.DML数据库管理语言
新增：
#全列插入：
insert [into] 表名 values(...);
#部分插入：
insert into 表名(列1 ,...) values(值1 ,...);
修改：
update 表名 SET 字段1=新值,字段2=新值[where 条件];
update 表名 SET 字段1=新值,字段2=新值;
1
2
删除：
delete from 表名 [where条件];
delete from 表名; #清空表中的数据
1
2
查询：
where子句：=、>、<
逻辑运算符：and、or、not
模糊查询：%、_
范围查询：in、between…and…
空判断：is null、is not null
聚合函数：count、max、min、sum、avg
分组：group by
排序：asc、desc
分页：limit
