MySQL基础知识与主从配置

最新推荐文章于 2024-04-21 22:56:16 发布
最新推荐文章于 2024-04-21 22:56:16 发布
阅读量120 收藏
点赞数
文章标签： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41720386/article/details/103728532
版权

存储引擎

InnoDB：支持事务，行锁，不支持全文搜索
MyISAM: 读效率高，支持全文搜索，不支持事务。如果数据不会修改，可以使用压缩表，减少磁盘IO

 

索引的分类

普通索引，唯一索引，单列索引，组合索引

空间数据索引: MyISAM支持，可以用作地理数据存储

全文索引：查找文本中的关键字，适用于Match和Against操作。如全文搜索创建表时指定FULLTEXT(column)，查询select * from dual where Match(column) Against('tttt');

从数据结构上分为：

B-Tree索引：Innodb和myIsam只支持B-Tree索引

Hash索引：只支持等值查询，不支持范围查询如大于小于，hash值冲突时维护成本高。长字符串作为索引时效率低，可以用CRC32函数冗余一列哈希值，根据hash值列索引再查询

数据存储方式上：

聚簇索引(一个表只能有一个聚簇索引)：默认是主键索引，将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引需要二次查找。

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。

索引查找数据的过程：

1.InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，

若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2.若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。

第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O。与主存不同，磁盘I/O存在机械消耗，因此磁盘I/O时间消耗巨大。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

每个页默认16KB。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。

 

Explain Query SQL

通过explain分析查询sql时可观察type字段的值是否全表扫描再进行相关优化，type的取值为：

index：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序，所以数据是有序的，

            不过在不需排序的情况下效率比all还要低。extra列中的值为‘Using index’，称这种情况为索引覆盖，

             查询数据刚好为索引列，无需回表取下数据

range：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。

ref：  查找条件列使用了索引而且不为主键和unique

const: 通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量  

 

备份与调试

mysqldump导出sql：
mysqldump -u root -p --databases db1 db2 > /backup.sql

开启错误日志：
log-error= /var/log/mysql/error.log

开启慢查询日志：
log-slow-queries=/path/slow_query.sql
log_query_time=10

 

常见问题

1.sql执行流程

客户端发起查询，服务器检查缓存，SQL解析，优化器生成执行计划，调用存储引擎API 

2.varchar最大长度

varchar需要用1个字节或2个字节记录字符串的长度，因此最多能存储2^16-1(65535)个字节的数据 

3.group by怎样优化

group by默认按分组字段排序，如果不需要排序可以使用order by null

 

MySQL主从配置

1、拉取镜像

docker pull mysql:5.7.26

2、创建配置文件

主从同步需要修改mysql的配置文件，这里采用宿主机文件挂载到容器内的做法，分别创建/usr/local/cfg/master.cnf和/usr/local/cfg/slave.cnf，内容如下

master.cnf

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
server-id=1
innodb_flush_log_at_trx_commit=2 
sync_binlog=1 
log-bin=mysql-bin-1
default-time_zone = '+8:00'
log-bin-trust-function-creators=1

slave.cnf与master.cnf基本一致，只是server-id修改为2

server-id=2
log-bin=mysql-bin-2

3、启动镜像

将data文件映射到宿主机，否则容器删除时数据就丢失了

docker run --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -p 3307:3306 -v /usr/local/cfg/master.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /docker/mysql_master/data:/var/lib/mysql --privileged=true -d 7faa3c53e6d6

docker run --name mysql_slave -e MYSQL_ROOT_PASSWORD=123456 -p 3308:3306 -v /usr/local/cfg/slave.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /docker/mysql_slave/data:/var/lib/mysql --privileged=true -d 7faa3c53e6d6

 

4、开启防火墙端口

firewall-cmd --zone=public --add-port=3307/tcp --permanent
firewall-cmd --zone=public --add-port=3308/tcp --permanent
firewall-cmd --reload

 

5、给数据库账户分配数据同步权限

容器内登录mysql -u root -p或连接工具连接

grant replication slave on *.* to 'abc'@'127.0.0.1' identified by '123456'; 

 

6、连接主库，查询语句

show master status; 记录File和Position字段值，配置从库时用到

 

7、连接从库

change master to master_host='127.0.0.1',
master_port=3307,
master_user='root' ,
master_password='123456',
master_log_file='mysql-bin-1.000003', //File字段
master_log_pos=154; //Position字段

start slave;

 

单向的主从关联配置完成，如果是互为主从则在主库执行相同操作

1、连接从库

show master status; 记录File和Position字段值

2、连接主库

change master to master_host='127.0.0.1',
master_port=3308,
master_user='root' ,
master_password='123456',
master_log_file='mysql-bin-2.000003', //File字段
master_log_pos=154; //Position字段

start slave;

 

Mycat配置

1、下载mycat

注意下载路径，后面配置Dokcerfile用到，比如我的是/usr/local/mycat

wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

重命名：mv Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz mycat.tar.gz

 

2、创建Dockerfile文件

在mycat安装包的路径下创建Dockerfile，内容如下

FROM openjdk:8
ADD mycat.tar.gz /usr/local/
VOLUME /usr/local/mycat/conf
ENV MYCAT_HOME=/usr/local/mycat
EXPOSE 8066 9066
CMD ["/usr/local/mycat/bin/mycat", "console","&"]

 

3、构建镜像

注意最后有一个.号

docker build -t mycat:1.6 .

 

4、添加Mycat配置文件，配置文件可参考官方文档

如/usr/local/mycat/conf/schema.xml和/usr/local/mycat/conf/server.xml

schema.xml主要是配置数据库和连接信息

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dataNode1"></schema>
	
    <dataNode name="dataNode1" dataHost="dataHost1" database="test" />
	
	
	<dataHost name="dataHost1" maxCon="1000" minCon="10" balance="1"
			writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
			<heartbeat>select user()</heartbeat>
			<!-- can have multi write hosts -->
			<writeHost host="hostM1" url="127.0.0.1:3307" user="root"
					password="123456">
			</writeHost>
			<writeHost host="hostS1" url="127.0.0.1:3308" user="root"
					password="123456" />
	</dataHost>

</mycat:schema>

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">1</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

    <property name="sequnceHandlerType">2</property>
    <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<property name="processorBufferPoolType">0</property>
	<property name="handleDistributedTransactions">0</property>
	<property name="useOffHeapForMerge">1</property>
	<property name="memoryPageSize">64k</property>
	<property name="spillsFileBufferSize">1k</property>
	<property name="useStreamOutput">0</property>
	<property name="systemReserveMemorySize">384m</property>
	<property name="useZKSwitch">false</property>

    </system>

   <!-- 读写都可用的用户 -->
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">test</property>
    </user>
    

</mycat:server>

 

5、启动Mycat

docker run --name mycat -p 8066:8066 -p 9066:9066 -v /usr/local/mycat/conf/schema.xml:/usr/local/mycat/conf/schema.xml
-v /usr/local/mycat/conf/server.xml:/usr/local/mycat/conf/server.xml -d mycat:1.6

 

 

hkqyy123
关注
数据库基础：mysql主从集群搭建
2401_84049040的博客
04-21 861
互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！” />[外链图片转存中…(img-JrXxa0lh-1713710850494)][外链图片转存中…(img-ryFl10rA-1713710850494)]《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
mybatis mysql 主从_mybatis主从数据库配置文件
weixin_31830691的博客
01-27 1302
第三章 : JavaORM框架之Mybatis篇(Ibatis)欢迎查看Java开发之上帝之眼系列教程，如果您正在为Java后端庞大的体系所困扰，如果您正在为各种繁出不穷的技术和各种框架所迷茫，那么本系列文章将带您窥探Java庞大的体系。本系列教程希望您能站在上帝的角度去观察(了解)Java体系。使Java的各种后端技术在你心中模块化；让你在工作中能将Java...文章jimisun2018-10...
用docker配置mysql主从集群同步
小小的幸福
09-17 4663
1.安装docker 详见https://blog.csdn.net/qq_33320785/article/details/81189061   安装步骤 sudo yum install -y yum-utils device-mapper-persistent-data lvm2 sudo yum-config-manager --add-repo https://d...
一个mysql的master的my.cnf
weixin_34414650的博客
10-25 104
[mysqld]port = 3306socket = /tmp/mysql.sockdatadir=/data/mysql/dataserver-id = 1skip-name-resolveexpire_logs_days=15log-bin=mysql-binbinlog_format=mixedreplicate-do-db=allin_platformrepli...
标配stad_5.5_mycnf
薄刀刀的技术博客
08-24 788
[client] port            = 3306 default-character-set = utf8 # The MySQL server [mysqld] port            = 3306 socket          = /tmp/smart.sock user = mysql server-id=171 ##########
Mysql主从配置详解（第一篇，共五篇）
qq_40001362的博客
11-14 392
使用Docker容器搭建MySql主从复制 使用Docker容器搭建MySql主从复制 查看容器ip命令为docker inspect 96fd[id前四位] Master_mysql_ip_172.17.0.4 slave_mysql_ip_172.17.0.5 1.docker上面寻找mysq，命令如下所示：docker search mysql 2.pull mysql到本地...
数据库系列——MySQL主从复制配置
F1520080的博客
04-21 814
同时修改mysql配置文件开启二进制日志，新增部分如下：[mysqld]log-bin=master-bin #这边写的是对应bin的日志名称然后重启数据库，在主库上使用同样先新增几行配置：[mysqld]然后重启数据库，使用如下语句连接主库：接着在从库上运行开启备份,正常情况如下图所示：Slave_IO_Running和Slave_SQL_Running都为yes。可以用这个步骤开启多个从库。
Mysql基础知识,主从复制
土豆科技的博客
03-05 188
目录1. 介绍1.1 原理2. 配置2.1 配置主服务器2.2 配置从服务器 1. 介绍 MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，重新更新到从数据库，使得主从服务器的数据达到一致。 ...
数据库读写分离之Mysql的主从配置_mysql_docker_5722，2024年最新java 面试官
2401_84412011的博客
04-21 775
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
MySQL8.0.27/26主从配置与读写分离简明手册
01-24
MySQL 8.0.27/26 主从配置与读写分离是数据库高可用性和负载均衡的重要实践，尤其在大型应用系统中，确保数据的一致性和服务的不间断至关重要。以下将详细介绍整个配置过程和相关知识点。 首先，环境准备是基础，...
Centos7系统下Mysql主从同步配置方案
09-09
MySQL主从同步是一种提高数据库高可用性和数据冗余的重要手段，尤其在CentOS7系统下，配置Mysql主从同步可以确保数据的安全性，并在主节点出现问题时无缝切换到从节点，保持服务不间断。以下是对标题和描述中涉及的...
mysql基础知识(不包含备份和主从)-详细笔记文档总结
07-07
"mysql基础知识总结" MySQL 是一种结构化查询语言，是一种对关系数据库中的数据进行定义和操作的语言方法。MySQL 数据库系统可以通过 yum 安装和源码安装两种方式进行安装。 MySQL 基础知识 ### 1. 什么是 SQL ...
mysql的主从复制(同步)基础知识-详细笔记文档总结
07-07
MySQL 主从复制（同步）基础知识总结 MySQL 主从复制是一种数据复制技术，用于将数据从一个 MySQL 服务器（主服务器）复制到一个或多个其他 MySQL 服务器（从服务器）。在主从复制过程中，主服务器负责接收和处理...
numexpr-2.8.3-cp38-cp38-win_amd64.whl
10-14
numexpr-2.8.3-cp38-cp38-win_amd64.whl
ujson-5.3.0-cp311-cp311-win_amd64.whl
10-14
ujson-5.3.0-cp311-cp311-win_amd64.whl
基于MATLAB车牌识别程序技术实现面板GUI.zip
最新发布
10-14
vos3000
RJFireWall-maste赛资源
10-14
RJFireWall-maste赛资源
msgpack-1.0.4-cp39-cp39-win_amd64.whl
10-14
msgpack-1.0.4-cp39-cp39-win_amd64.whl
MySQL主从配置详解与实战
"MySQL主从配置教程" MySQL主从配置是一种常见的数据库高可用性和负载均衡方案，它允许数据从一个主数据库（Master）实时同步到一个或多个从数据库（Slave）。这种配置可以确保数据的安全性，提高系统的容错能力，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值