Mysql数据库性能优化三(分表、增量备份、还原)

接上篇Mysql数据库性能优化二

对表进行水平划分    

如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了。如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了。所以一个好的拆分依据是 最重要的。关键字:UNION

例:

  • 订单表根据订单产生时间来分表(一年一张)
  • 学生情况表
  • 查询电话费,近三个月的数据放入一张表,一年内的放入到另一张表

对表进行垂直划分 

有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。 (JOIN)

Mysql数据库性能优化三(分表、增量备份、还原)

【试题内容】、【答案信息】两个表,最初是作为几个字段添加到【试题信息】里的,可以看到试题内容和答案这两个字段很长,在表里有3万记录时,表已经占 了1G的空间,在列试题列表时非常慢。经过分析,发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件,分页显示试题详细内容。而每 次检索都是这几个表做join,每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表,只有显示详细内容的时候才读这个大表,由此 就产生了【试题内容】、【答案信息】两个表。

选择适当的字段类型,特别是主键     

选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。比如主键, 建议使用自增类型,这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁慢太明显了。涉及到几个表做join时,效果就更明显了。

建议使用一个不含业务逻辑的id做主角如s1001。例:

int 4 bigint 8 mediumint smallint 2 tinyint 1
md5 char(32)
id :整数 tinyint samllint int bigint
student表
id stuno  stuname  adress
 s1001 小民   深圳

文件、图片等大文件用文件系统存储

数据库只存储路径。图片和文件存放在文件系统,甚至单独放在一台服务器(图床 / 视频服务器 ).

数据库参数配置

最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

对于myisam,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数

在my.ini修改端口3306,默认存储引擎和最大连接数

在my.ini中.
port=3306 [有两个地方修改]
default-storage-engine=INNODB
max_connections=100

合理的硬件资源和操作系统

如果你的机器内存超过4G,那么毋庸置疑应当采用64位操作系统和64位mysql 5.5.19 or mysql5.6

读写分离

如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。

Mysql数据库性能优化三(分表、增量备份、还原)

Master
  Slave1
  Slave2
  Slave3

主库master用来写入,slave1—slave3都用来做select,每个数据库分担的压力小了很多。
要实现这种方式,需要程序特别设计,写都操作master,读都操作slave,给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理,对程 序来读写哪些数据库是透明的。官方有个mysql-proxy,但是还是alpha版本的。新浪有个amobe for mysql,也可达到这个目的,结构如下

Mysql数据库性能优化三(分表、增量备份、还原)

定时完成数据库的备份

项目实际需求,请完成定时备份某个数据库,或者定时备份数据库的某些表的操作

windows 下每隔1小时,备份一次数据newsdb

windows 每天晚上2:00   备份 newsdb 下 某一张表

cmd> mysqldump –u root –p密码  数据库名 > 把数据库放入到某个目录

案例,备份 mydb 库的所有表

进入mysqldump所在的目录

cmd> mysqldump –u root –phsp shop> d:/shop.log   [把shop数据库的所有表全部导出]

cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop数据库的 temusers和emp导出]

如何恢复数据的表

进入的mysql操作界面

mysql>source  备份文件的全路径

定时备份:(把命令写入到my.bat 问中)

windows 如何定时备份 (每天凌晨2:00)

使用windows自带的计划任务,定时执行批处理命令。

增量备份和还原

定义:mysql数据库会以二进制的形式,自动把用户对mysql数据库的操作,记录到文件,当用户希望恢复的时候,可以使用备份文件进行恢复。

增量备份会记录dml语句、创建表的语句,不会记录select。记录的东西包括:sql语句本身、操作时间,位置

进行增量备份的步骤和恢复

注意:mysql5.0及之前的版本是不支持增量备份的

1、配置my.ini文件或者my.conf,启用二进制备份。

打开my.ini文件,查找log-bin,进行配置:log-bin=G:\Database\mysqlbinlog\mylog

在G:\Database目录下面新建目录mysqlbinlog

2、重启mysql服务

这个时候会在mysqlbinlog目录下面看到以下两个文件:

mylog.000001:日志备份文件。如果要查看这个日志文件里面的信息,我们可以使用mysqlbinlog程序查看,mysqlbinlog程序存放在mysql的bin目录下面(“C:\Program Files\MySQL\MySQL Server 5.6\bin”)。

执行sql语句

UPDATE emp set ename='zouqj' where empno=100003;

开始——运行——cmd,mysqlbinlog 备份文件路径

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog G:\Database\mysqlbinlog\mylog.000001

Mysql数据库性能优化三(分表、增量备份、还原)

mylog.index:日志索引文件,里面记录了所以的日志文件。(G:\Database\mysqlbinlog\mylog.000001)

3、假设现在问题来了,我这条update是误操作,如何进行恢复

在mysql日志中会记录每一次操作的时间和位置,所以我们既可以根据时间来恢复,也可以根据位置来恢复。

那么,我们现在马上可以从上图看出,这条语句产生的时间是"2016-04-17 12:01:36",位置是614

按时间来恢复

我们可以选择在语句产生时间的前一秒

执行cmd命令:mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

这个时候我再执行SQL语句查看

SELECT * from emp where empno=100003;

结果变成了

Mysql数据库性能优化三(分表、增量备份、还原)

按位置来恢复

执行cmd命令:mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

这个时候再执行SQL来查看结果,又变回来了。

Mysql数据库性能优化三(分表、增量备份、还原)

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持我们!

时间: 2016-12-08

MySQL数据库备份以及常用备份工具集合

一.数据库备份种类 按照数据库大小备份,有四种类型,分别应用于不同场合,下面简要介绍一下: 1.1完全备份 这是大多数人常用的方式,它可以备份整个数据库,包含用户表.系统表.索引.视图和存储过程等所有数据库对象.但它需要花费更多的时间和空间,所以,一般推荐一周做一次完全备份. 1.2事务日志备份 事务日志是一个单独的文件,它记录数据库的改变,备份的时候只需要复制自上次备份以来对数据库所做的改变,所以只需要很少的时间.为了使数据库具有鲁棒性,推荐每小时甚至更频繁的备份事务日志. 1.3差异备份 也

MySQL数据库如何导入导出(备份还原)

本文适用范围:全面阐述MySQL数据库的各种操作,分虚拟主机和服务器两种情况. 虚拟主机 1.通过PHPMyAdmin的导入导出功能,这个软件一般只支持几兆数据的导出,太大的数据可能会超时. 2.通过程序自带的数据库备份还原功能来操作,一些常见的PHP程序如DZ论坛等,后台都有数据库还原和备份的功能,方便我们转移空间数据. 3.如果您的数据库在朝暮数据购买,我们的管理面板支持一键备份和还原.点击备份按钮后,您可以到数据库对应的空间上通过FTP方式下载. 服务器或VPS 首先我们远程到服务器上(W

mysql 5.6 从陌生到熟练之_数据库备份恢复的实现方法

MySQL数据库使用命令行备份|MySQL数据库备份命令 例如: 数据库地址:127.0.0.1 数据库用户名:root 数据库密码:root 数据库名称: szldb 备份数据库到D盘跟目录 mysqldump -h127.0.0.1 -uroot -proot szldb > d:/backupfile.sql 备份到当前目录 备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库 mysqldump --add-drop-table -h127.0.0.

Mysql数据库增量备份的思路和方法

MySQL数据库增量备份,在这之前修改我们的数据库配置文件/etc/my.cnf开启bin-log日志功能即可.接下来是我参考了下网上的一些方法,自己写的,主要还是要能学到他的一些思路和方法. #function:MYSQL增量备份 #version:1.0.0 #author:wangyanlin #date:2017/08/02 #---------------------------------------------------------------------------------

教你如何恢复使用MEB备份的MySQL数据库

恢复使用MEB备份的MySQL数据库,执行一个普通备份 [root@test bin]# ./mysqlbackup --defaults-file=/service/mysql5.5/my.cnf --socket=/data/mysql5.5/mysql.sock --user=root --backup-dir=/backup/5.5/normal --password backup MySQL Enterprise Backup version 3.8.2 [2013/06/18] Co

MySQL备份时排除指定数据库的方法

使用mysqldump命令备份时候,--all-databases 可以备份所有的数据库. 使用ignore-table 还可以排除制定的表.但是,mysqldump没有参数可以排除数据库的. 要备份的数据库少的时候,可以通过mysqldump -uroot -p123456 --databases db1 db2 db3 > mysqldump.sql 这样来备份. 但是假如数据库有数十个的话,这样写起来很累人,也很low.解决办法还是有的,看下面: [下面演示用的mysql用户名的root,

MYSQL主从数据库同步备份配置的方法

下文分步骤给大家介绍的非常详细,具体详情请看下文吧. 一.准备 用两台服务器做测试: Master Server: 192.0.0.1/Linux/MYSQL 4.1.12 Slave Server: 192.0.0.2/Linux/MYSQL 4.1.18 做主从服务器的原则是,MYSQL版本要相同,如果不能满足,最起码从服务器的MYSQL的版本必须高于主服务器的MYSQL版本 二.配置master服务器 1. 登录Master服务器,编辑my.cnf #vim /etc/my.cnf 在[m

mysql自动定时备份数据库的最佳方法(windows服务器)

网上有很多关于window下Mysql自动备份的方法,可是真的能用的也没有几个,有些说的还非常的复杂,难以操作. 我们都知道mssql本身就自带了计划任务可以用来自动备份,可是mysql咱们要怎么样自动有备份呢? 教大家一个最简单的方法. 这个方法是用bat批处理来完成的. 假想环境:MySQL 安装位置:D:\Program Files\MySQL\MySQL Server 5.0\data 如图: 数据库名称为:952game数据库备份目的地:d:\db_bak\ (你想把数据库备份到哪个盘

Linux VPS备份教程 数据库/网站文件自动定时备份

几天写过两篇使用VPS的安全性设置的博文,其实不管我们如何设置安全,及时的备份VPS数据才是最为重要的.因为VPS与主机不同,主机可能很多时候服务商代为我们备份,VPS的操作和安全性大部分都需要我们自己来负责,即便很多VPS上提供每日备份服务,但是不可确定因素实在太多.比如商家跑路.母机硬盘损坏.不可控因素等. 关于VPS备份教程方法可用的方法也很多,比如我们常规的备份直接通过FTP下载,MYSQL数据库导出也是可以操作的,但是这些都比较麻烦,而且还需要人为去执行.这篇文章老左分享目前大家比较常

Navicat for MySQL定时备份数据库及数据恢复详解

在做数据库修改或删除操作中,可能会导致数据错误,甚至数据库奔溃,而有效的定时备份能很好地保护数据库.本篇文章主要讲述Navicat for MySQL定时备份数据库和数据恢复等功能,同时可以定时播放电影等设置,希望对您有所帮助,如果文章中存在错误或不足之处,还请海涵~ 一. 设置计划任务定时备份数据库 计划任务就是让电脑在指定的时间内执行指定的动作,这些动作可以是一个程序,也可以是一个批处理,但是至少是可以运行的!其实再通俗一点也就是相当于你在那个时间里面进行了对某个东西对鼠标双击的操作. 1.

自动定时备份sqlserver数据库的方法

下面是我已经证实可用的自动备份的方法. 1.打开企业管理器->管理->sql server代理 2.新建一个作业,作业名称随便取,例如:data备份,所有者选择sa,当然你也可以选择其他用户,前提是该用户有执行作业的权限: 3.点击步骤标签,进入步骤面板.新建步骤,步骤名可以随便填写,如步骤1,类型和数据库默认,不需要修改.命令中写入以下语句: BACKUP DATABASE [数据库名] TO DISK = N'F:\\databack\\firenews备份' WITH NOINIT ,

MSSQL 2008 自动备份数据库的设置方法

首先,打开MSSQL2008,然后在"管理"大类中,双击"维护计划"子类,这时候,如果以前设置过任务计划的,就会显示出任务列表:如果没有,那么什么都不会显示. 在"维护计划"上右击,选择"新建维护计划",然后输入一个名称,这里我们输入默认名称"MaintenancePlan",然后确定.然后再工具栏中,在"常规"标签中,将"备份数据库任务"拖到设计窗体中,如图示: 然

mysql自动增量备份的实例方法(本地备份与远程备份)

1.本地备份编写自动备份脚本:vim /var/lib/mysql/autobak内容如下: 复制代码 代码如下: cd /data/home/mysqlbakrq=` date +%Y%m%d `/usr/local/mysql/bin/mysqldump sqldb --flush-logs -uroot -p123456 --opt > 777city_$rq.sql 保存退出. chmod -777  /var/lib/mysql/autobak crontab -e00 00 * *

SQL Server远程定时备份数据库脚本分享

经常会有定时备份SQL Server数据库的需要.定时备份到本机的话,还是挺容易的,计划任务就可以完成,但如果是整机挂了,那备份到本机是没意义的,那么就需要来考虑备份到局域网中,其它电脑里. 下面就分享一份在网上找了之后,自己再简单整理过的代码,配合 SQL Server 代理中的作业功能,已经稳定运行一个星期了,每小时就备份一次. 复制代码 代码如下: -- 创建网络映射(Y是盘符:IP地址后面要带共享文件夹的名称:password是密码,双引号引起:account是远程电脑的登录名) exe

SQL server 定时自动备份数据库的图文方法

SQL2000版本 用JOB 企业管理器 --管理 --SQL Server代理 --右键作业 --新建作业 --"常规"项中输入作业名称 --"步骤"项 --新建 --"步骤名"中输入步骤名 --"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库 --"命令"中输入要执行的语句: --确定 --"调度"

SQL SERVER备份数据库存储过程的方法

sqlserver批量导出存储过程 在查询分析器中,选中数据库-->右键"任务"-->在弹出菜单中选择"生成脚本" -->"下一步"-->选择你要存储过程所在数据库--> "下一步"-->勾选"存储过程",并下一步--> 勾选你要生成的存储过程名称,并下一步--> 选择将脚本保存到文件,并选择保存路径-->完成 还有一种直接通过sql语句实现: set

使用Python发送邮件附件以定时备份MySQL的教程

最近迁移了wordpress,系统升级为CentOS 6,很奇怪的一个问题,在原来CentOS 5.8下用的很正常的定时备份数据库并通过邮件发送的脚本不能发送附件,其他都正常,邮件内容也是uuencode生成的文件编码,但是就是不产生附件.而且找不出原因,望有知道的不吝赐教. 为了解决这一问题,我用Python写了一个mail客户端,可以发送附件,是一个命令行程序.废话不多说.贴代码: #!/usr/bin/env python #-*- coding: utf8 -*- ''' #======