主数据库一般是准实时的业务数据库,这个缘起

2019-08-17 作者:数据库   |   浏览(84)

1.  背景及原因

关系型数据库的一个基本原则是将不同细分数据放在单独的表中存储。这样做的好处是:

1).避免重复数据的出现

2).方便数据更新

3).避免创建重复数据时出错

例子:

有供应商信息和产品信息两部分。如果将他们放在一个表中。弊端有:

1). 同一供应商的所有数据所在行的供应商信息相同,即产生重复数据。

2). 在供应商信息变更时,如果更换联系方式或地址,需要在所有包含此供应商信息的行更新。

3). 在录入同一供应商的多个产品时,有可能会供应商信息不同,导致调取数据时无法使用。

通过将供应商和产品信息分别放在不同的表中,可以避免以上弊端。

1). 一个供应商只需一条记录,省时省空间。

2). 变更供应商信息时,只需变更一次。方便快捷不易出错。

3). 录入信息时,不会出错。

DB中存在如下几张表:

orders:

order_num, order_date, cust_id

venders:

vend_id, vend_name, vend_address, vend_contact

products:

prod_id, prod_name, vend_id, prod_price

customers:

cust_小鱼儿玄机30码姐妹,id, cust_name, cust_address, cust_city, cust_state

orderitems:

order_num, order_item, prod_id, quantity, item_price

1)安装mysql

ubuntu中安装一台mysql了,docker安装另外一台mysql

获取mysql的镜像,主从同步尽量保证多台mysql的版本相同,我的ubuntu中存在的mysql是5.7.22版本,所以获取5.7.22版本的镜像为例:

docker image pull mysql:5.7.22
或
docker load -i mysql_docker_5722.tar

 

运行mysql docker镜像,需要在宿主机中建立文件目录用于mysql容器保存数据和读取配置文件。

在家目录中(/home/python)中创建目录,将mysql的配置文件放到此目录中

cd ~
mkdir mysql_slave
cd mysql_slave
mkdir data
cp /etc/mysql/mysql.conf.d ./

 

将docker运行的mysql作为slave来运行,开启前需要修改配置文件。

编辑 ~/mysql_slave/mysql.conf.d/mysqld.cnf 文件,修改

port  =  8306
general_log  = 0
server-id  = 2

 

让此台mysql运行在8306端口上,且mysql编号为2

创建docker容器

docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=mysql -d --network=host -v /home/python/mysql_slave/data:/var/lib/mysql -v /home/python/mysql_slave/mysql.conf.d:/etc/mysql/mysql.conf.d  mysql:5.7.22

 

  • MYSQL_ROOT_PASSWORD 是创建mysql root用户的密码

测试,在ubuntu中使用mysql命令尝试连接docker容器中的mysql

mysql -uroot -pmysql -h 127.0.0.1 --port=8306

 

什么是主从复制?

其实这是一篇没有技术含量的文章,精通SQL优化的请绕道。这个缘起于在优化一个SQL过程中,同事问了我一个问题,为什么SQL中存在隐式转换,但是执行计划没有变? 我思索了一下,觉得这个问题也有点意思,说不定有些对隐式转换了解得不深入的同学都有此疑问,那么下面结合上下文场景做一个细节方面的解答。

2. 表的联结

 由于以上原因,在调出多个表中信息时,就需要将多个表通过主键和外键联结。

 联结的基本方法:1. 指出需要联结的表;2. 指出通过哪个字段联结。

 例子:

需要供应商和所提供产品信息:

select vend_name, prod_name, prod_price #select fields
from vendors, products  #from tables
where vendors.vend_id=products.vend_id #how to join tables;

2)备份主服务器原有数据到从服务器

如果在设置主从同步前,主服务器上已有大量数据,可以使用mysqldump进行数据备份并还原到从服务器以实现数据的复制。

在主服务器Ubuntu上进行备份,执行命令:

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

小鱼儿玄机30码姐妹 1

  • -u :用户名

  • -p :示密码

  • --all-databases :导出所有数据库

  • --lock-all-tables :执行操作时锁住所有表,防止操作时有数据修改

  • ~/master_db.sql :导出的备份数据(sql文件)位置,可自己指定

在docker容器中导入数据

mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql

 

主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。

我们一个系统中使用了ORMLite框架,粗心的开发人员弄出了不少下面这样的SQL语句,都存在隐式转换问题,如下所示,表machine_stop_alarm_msg 的结构如下,字段machine_no、status都为VARCHAR(10),但是下面SQL,传入的变量@P0,@P1都是NVARCHAR(4000)类型。

3. 两种联结方法

除了上面的在where字句中创建联结,还可以使用关键字join ... on...

例如上面的语句还可写成:

select vend_name, prod_name, prod_price #select fields
from vendors inner join products  #from tables
on vendors.vend_id=products.vend_id #how to join tables;

此处注意,在指定联结字段时,需要使用完全限定列名,既table.column的格式。

笛卡儿积:当没有制定联结字段时会出现笛卡儿积。既,被联结的两个表中任意一行都和另一个表中所有行联结。

3)配置主服务器master(Ubuntu中的MySQL)

编辑设置mysqld的配置文件,设置log_bin和server-id

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

小鱼儿玄机30码姐妹 2

 

重启mysql服务

sudo service mysql restart

 

登入主服务器Ubuntu中的mysql,创建用于从服务器同步数据使用的帐号

mysql –uroot –pmysql

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';

FLUSH PRIVILEGES;

 

小鱼儿玄机30码姐妹 3

 

获取主服务器的二进制日志信息 

SHOW MASTER STATUS;

 

File为使用的日志文件名字,Position为使用的文件位置,这两个参数须记下,配置从服务器时会用到。

 

主从复制的作用(好处)**!**

 

4. 联结多个表

在一条SQL语句中可以联结任意多张表。但是要注意:联结表非常消耗数据库系统资源,所以一定要注意控制联结的使用。

一个例子:

select prod_name, vend_name, prod_price, quantity
from products, vendors, orderitems
where products.prod_id = orderitems.prod_id
and vendors.vend_id=products.vend_id
and orderitems = 20005;

4)配置从服务器slave (docker中的mysql)

进入docker中的mysql

mysql -uroot -pmysql -h 127.0.0.1 --port=8306

执行

change master to master_host='127.0.0.1', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;
  • master_host:主服务器Ubuntu的ip地址
  • master_log_file: 前面查询到的主服务器日志文件名
  • master_log_pos: 前面查询到的主服务器日志文件位置

启动slave服务器,并查看同步状态

start slave;
show slave status G

 

小鱼儿玄机30码姐妹 4

 

 

 

1、做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。

小鱼儿玄机30码姐妹 5

5. 联结的类型

1). 内联结和外联结

内联结:查找出的行是通过两个表的相等测试的数据。inner join on

外联结:在联结是指定一个表,并反回其中所有行,无论是否通过相等测试。外联结包括左联结右联结。left/right outer join on

一个例子:检索系统中所有下了订单的客户id和订单数量

内联结:

select customers.cust_id, orders.order_num
from customers inner join orders
  on customers.cust_id= orders.cust_id;

只有下了订单的客户信息会被检索到。

外联结:

select customers.cust_id, orders.order_num
from customers left outer join orders
  on customers.cust_id= orders.cust_id;

left outer join左边的customers表中所有的行都会被检索到。不论客户是否下单。

左联结和右联结的区别:

左联结关键字的左边的表会被检索出所有行,右联结关键字右边的表会被检索出所有行。所以左联结和右联结可以轻易转换,在实现功能上没有区别。

2).自联结

在一些特定情况下,需要让一张表自己和自己做联结,就需要用到自联结。例如需要查出生产了产品ID为DTNTR的供应商的所有产品。

使用子查询:

select prod_id, prod_name
from products
where vend_id in (select vend_id
                          from products
                          where prod_id="DTNTR");

使用自联结:

select p1.prod_id, p2.prod_name
from products as p1 inner join products as p2
on p1.vend_id=p2.vend_id
and p2.prod_id="DTNTR";

3).自然联结

所有查找出的列都是唯一的,不会有一个行被输出两次。自然联结需要通过人工手动实现,没有公式或关键字能制定自然联结。

2、架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。

DECLARE  @P0 nvarchar(4000),@P1 nvarchar(4000);

 

SET @P0='1';

SET @P1='K172';

 

SELECT [recid],[machine_no] 

   ,[stop_stime] 

   ,[stop_etime] 

   ,[status] 

   ,[memo] 

   ,[createddate]  

FROM machine_stop_alarm_msg t  

WHERE 1=1  

AND t.status=@P0  

AND t.machine_no in(@P1 )  

ORDER BY machine_no, 

   stop_stime ;  

6. 有聚合函数的联结

联结可以和聚合函数一起使用。例如,需要检索出所有客户的订单数:

内连接:

select customers.cust_id, customers.cust_name,
count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id inner join orders.cust_id
group by customers.cust_name;

检索出所有已下单客户的订单数。

外联结:

select customers.cust_id, customers.cust_name,
count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id inner join orders.cust_id
group by customers.cust_name;

检索出所有客户的订单数,包括没有订单数为0的客户。

3、读写分离,使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度。

 

Mysql简介:

 

数据库的种类很多,Oracle、Sql Server、mySQL、Access等他们个自有自己的特点和应用范围,因为一直做sqlserver之前用的比较多的是SqlServer.由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。MySQL是一个多用户、多线程的关系型数据库管理系统。 工作模式是基于客户机/服务器结构。目前它可以支持几乎所有的操作系统,同时也可以和php完美结合。

machine_stop_alarm_msg 表只有一个聚集索引PK_machine_stop_alarm_msg,字段为recid。

 

小鱼儿玄机30码姐妹 6

简单的来说 ,MySql是一个开放的、快速的、多线程的、多用户的SQL数据库服务器。

 

下面介绍怎么使用Mysql实现主从复制.

当时我优化的时候,就觉得这个SQL语句存在两个问题:1 缺少索引; 2 存在隐式转换问题。当时创建了下面索引,并要求开发人员修改SQL,避免隐式转换。

1.首先用Vmware创建两个centos7的服务器系统(多个也可以,这里只创建两个,并关闭防火墙).两个服务器的IP分别是 主:192.168.2.128,从:192.168.2.130.

CREATE NONCLUSTERED INDEX ix_machine_stop_alarm_msg_n1

ON [dbo].[machine_stop_alarm_msg] ([machine_no],[status])

INCLUDE ([recid],[stop_stime],[stop_etime],[memo],[createddate])

GO

小鱼儿玄机30码姐妹 7

 

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:主数据库一般是准实时的业务数据库,这个缘起

关键词: 小鱼儿玄机30码