MySql Backup

Welcome Guest Search | Active Topics | Log In | Register
Backup way and speed Options · View
MB
#1 Posted : Friday, August 17, 2012 5:38:12 AM
Rank: Guest

Groups: Registered

Joined: 8/10/2009
Posts: 1,024
Hello again.

Before anything else I'd like to say that this post is in no way a meaning to say bad things about this wonderful piece of software, but to try and help the developer and all of us who use it be happier with it.

After my problem with the collation I started looking at my backup sql file to see why it would take about 2 hours to restore a 100MB sql file when the same database backup by HeidiSQL would take about 30 seconds to restore.

I realized its because of the way its saves the data and then restores it. This software does a query (INSERT) for every value instead of an array of values for each table, which in turn restores faster due to the time it takes to open a new query.

I'll show an example here:

1) MySQLBackupFTP:

Code:


CREATE TABLE IF NOT EXISTS `dr_listavalores` (
  `ListaValoresID` int(11) NOT NULL,
  `Sigla` varchar(20) NOT NULL,
  `Descricao` varchar(200) NOT NULL,
  `Contexto` varchar(20) NOT NULL,
  `Ordem` smallint(6) default NULL,
  PRIMARY KEY  (`ListaValoresID`),
  UNIQUE KEY `Sigla` (`Sigla`,`Contexto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dr_listavalores`
--

INSERT INTO `dr_listavalores` VALUES (1,'MANUAL','MANUAL','TIPO_FORMULARIO',1);
INSERT INTO `dr_listavalores` VALUES (2,'IDIGITAL','IDIGITAL','TIPO_FORMULARIO',2);
INSERT INTO `dr_listavalores` VALUES (3,'OUTRO','OUTRO','TIPO_FORMULARIO',3);
INSERT INTO `dr_listavalores` VALUES (51,'PU','PEDIDO ÚNICO','MODELO_FORMULARIO',1);
INSERT INTO `dr_listavalores` VALUES (52,'DMP','DMP','MODELO_FORMULARIO',2);
...


2) HeidiSQL:

Code:


CREATE TABLE IF NOT EXISTS `dr_listavalores` (
  `ListaValoresID` int(11) NOT NULL,
  `Sigla` varchar(20) NOT NULL,
  `Descricao` varchar(200) NOT NULL,
  `Contexto` varchar(20) NOT NULL,
  `Ordem` smallint(6) default NULL,
  PRIMARY KEY  (`ListaValoresID`),
  UNIQUE KEY `Sigla` (`Sigla`,`Contexto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table guias.dr_listavalores: ~91 rows (approximately)
/*!40000 ALTER TABLE `dr_listavalores` DISABLE KEYS */;

INSERT INTO `dr_listavalores` (`ListaValoresID`, `Sigla`, `Descricao`, `Contexto`, `Ordem`) VALUES
    (1, 'MANUAL', 'MANUAL', 'TIPO_FORMULARIO', 1),
    (2, 'IDIGITAL', 'IDIGITAL', 'TIPO_FORMULARIO', 2),
    (3, 'OUTRO', 'OUTRO', 'TIPO_FORMULARIO', 3),
    (51, 'PU', 'PEDIDO ÚNICO', 'MODELO_FORMULARIO', 1),
    (52, 'DMP', 'DMP', 'MODELO_FORMULARIO', 2),
        ...


If this could be implemented it would make the restore a lot faster.

Thanks again,

MB
AlexP
#2 Posted : Friday, August 17, 2012 10:10:28 AM
Rank: Administration

Groups: Administrators, Registered

Joined: 12/1/2011
Posts: 822
Hi,

Thank you for your feedback! The release with this feature is in progress now and we are going to publish it soon. Sorry for the long delay to adding it.
MB
#3 Posted : Monday, August 20, 2012 11:11:00 AM
Rank: Guest

Groups: Registered

Joined: 8/10/2009
Posts: 1,024
It is I who thanks you for such a great program and great support. :-)
AlexP
#4 Posted : Thursday, August 23, 2012 6:56:07 AM
Rank: Administration

Groups: Administrators, Registered

Joined: 12/1/2011
Posts: 822
Hi,

Please update the software to the latest release (1.4.9) and turn on "Extended inserts" option at "Output SQL file options" window. To open it click at "Advanced" toolbutton, then at "More SQL Options..." button at "Advanced Settings" window.
MBorges
#5 Posted : Thursday, August 23, 2012 12:35:28 PM
Rank: Newbie

Groups: Registered

Joined: 8/21/2012
Posts: 2
Location: Lisbon
Thanks a lot my friend.
This sure worked for me.
From about 2 hours to less than 1 minute.

WONDERFULL.

Thanks again,
MB

Alexander Omelchenko
#6 Posted : Thursday, August 18, 2016 5:57:40 AM
Rank: Advanced Member

Groups: Administrators, Registered

Joined: 12/23/2015
Posts: 457
Good news! Please feel free to contact us on any issues or concerns.
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You can create polls in this forum.
You can vote in polls in this forum.

©Pranas.NET Sql Server Backup MySql Backup Free SQl Backup SQL Backup Master Employee monitoring software Web Scraping Azure SQL Database Backups ergonotes.com