Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > linux.debian.user > #182772 > unrolled thread

OT: SQL database - some questions

Back to article view | Back to linux.debian.user

Contents

  OT: SQL database - some questions Hans <hans.ullrich@loop.de> - 2017-06-28 10:10 +0200
    Re: OT: SQL database - some questions Mirco Piccin <pictux@gmail.com> - 2017-06-28 10:50 +0200
      Re: OT: SQL database - some questions Hans <hans.ullrich@loop.de> - 2017-06-28 11:00 +0200
        Re: OT: SQL database - some questions Darac Marjal <mailinglist@darac.org.uk> - 2017-06-28 11:20 +0200
          Re: OT: SQL database - some questions Hans <hans.ullrich@loop.de> - 2017-06-28 14:30 +0200
            Re: OT: SQL database - some questions Greg Wooledge <wooledg@eeg.ccf.org> - 2017-06-28 15:00 +0200
              Re: OT: SQL database - some questions Hans <hans.ullrich@loop.de> - 2017-06-28 15:20 +0200
                Re: OT: SQL database - some questions Joe <joe@jretrading.com> - 2017-06-28 17:40 +0200
        Re: OT: SQL database - some questions Mirco Piccin <pictux@gmail.com> - 2017-06-28 11:20 +0200
        Re: OT: SQL database - some questions Brad Rogers <brad@fineby.me.uk> - 2017-06-28 11:30 +0200
    Re: OT: SQL database - some questions Celejar <celejar@gmail.com> - 2017-06-29 01:10 +0200
      Re: OT: SQL database - some questions Richard Owlett <rowlett@cloud85.net> - 2017-06-29 12:20 +0200
    Re: OT: SQL database - some questions Joel Rees <joel.rees@gmail.com> - 2017-06-30 00:50 +0200

#182772 — OT: SQL database - some questions

Dear list,

this is a little offtopic, but maybe you can make things a little bit clearer 
for me.

I had had a webserver hosted by a provider, which is switched off since a 
year. From this server I got a backup of a sql database. The system that ran 
that time, was wordpress, and the database is called something like 
"bla_bla_wp2016018_911.sql.gz"

On this website I wrote some blogs, which I want to have back.

So my question: Are these blogs content in this database? And if yes, can I 
restore them without to setup a complete wordpress server with sql database?

Is there an easy way or only a hard way?

Would be nice, if someone could give me some points, I am not so experienced 
with databases.

Thanks and regards

Hans

[toc] | [next] | [standalone]

#182773

Hi Hans,

> I had had a webserver hosted by a provider, which is switched off since a
> year. From this server I got a backup of a sql database. The system that ran
> that time, was wordpress, and the database is called something like
> "bla_bla_wp2016018_911.sql.gz"

i suppose the backup has all the blog data, so yes, you should be able
to resume from it your blogs (if you haven't deleted them).
The backup of a database usually comes with all the data (of course)
and with all the sql commands useful to recreate the tables. You only
need to create a database for first.
Here more info:
https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/reloading-sql-format-dumps.html

After restore, you can browse the tables  to find your blogs using
Tora, phpMyAdmin, or any other tools.
All the data will be of course with html tags.

Good luck
M

[toc] | [prev] | [next] | [standalone]

#182774

Am Mittwoch, 28. Juni 2017, 10:41:05 CEST schrieb Mirco Piccin:
> Hi Hans,
> 
Hi Mirco

> Here more info:
> https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/reloading-sql-format-d
> umps.html
> 

sorry, but that was not quite the thing, I was looking for.
> After restore, you can browse the tables  to find your blogs using
> Tora, phpMyAdmin, or any other tools.
> All the data will be of course with html tags.
> 

Maybe I should precise my question: How can I restore my data from this sql 
database on my desktop? Is there a tool to extract my blogs? 

I do not want to build a webserver with php and wordpress and so on, just 
extract my blogs.  Phpmyadmin needs a webserver to run. This is not, what I 
wanted, of course.

> Good luck
> M

Best

Hans

[toc] | [prev] | [next] | [standalone]

#182775

[Multipart message — attachments visible in raw view] — view raw

On Wed, Jun 28, 2017 at 10:53:02AM +0200, Hans wrote:
>Am Mittwoch, 28. Juni 2017, 10:41:05 CEST schrieb Mirco Piccin:
>> Hi Hans,
>>
>Hi Mirco
>
>> Here more info:
>> https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/reloading-sql-format-d
>> umps.html
>>
>
>sorry, but that was not quite the thing, I was looking for.
>> After restore, you can browse the tables  to find your blogs using
>> Tora, phpMyAdmin, or any other tools.
>> All the data will be of course with html tags.
>>
>
>Maybe I should precise my question: How can I restore my data from this sql
>database on my desktop? Is there a tool to extract my blogs?

SQL is a text-based language. If you open the file in vim (or gunzip it,
then open it, if you prefer a graphical editor such as gedit), you
should expect to see a series of statements. The first set will probably
start something like "CREATE TABLE". For your purposes, you can ignore
these. Scroll down to find statements beginning with either "UPDATE
<sometable>" or "INSERT INTO <sometable". With a bit of luck, each blog
entry will be a separate record. You should be able to identify the
content of the blog entries, plus there will probably also be metadata
such as when the blog was posted and so on.

Converting this into whatever format you now want is left as an exercise
for the reader :)

>
>I do not want to build a webserver with php and wordpress and so on, just
>extract my blogs.  Phpmyadmin needs a webserver to run. This is not, what I
>wanted, of course.
>
>> Good luck
>> M
>
>Best
>
>Hans
>

-- 
For more information, please reread.

[toc] | [prev] | [next] | [standalone]

#182785

sqlbrowser looked best promising, but my sql databases can't be opened with 
it. I want to export the *.sql file into a new database, but I get stuck with 
an error. Telling ḿe "unknown statement: 1#, unrecogized token: 1"#"", so it 
looks like a format error. 

Well, I do not know, what is causing that, as I said, I am not expierience in 
databases.

Hans

 

[toc] | [prev] | [next] | [standalone]

#182786

On Wed, Jun 28, 2017 at 02:25:55PM +0200, Hans wrote:
> sqlbrowser looked best promising, but my sql databases can't be opened with 
> it. I want to export the *.sql file into a new database, but I get stuck with 
> an error. Telling ???e "unknown statement: 1#, unrecogized token: 1"#"", so it 
> looks like a format error. 
> 
> Well, I do not know, what is causing that, as I said, I am not expierience in 
> databases.

Start by actually reading the compressed backup, using zless.  See whether
it looks like an SQL dump.  If it does, then you can proceed to the next
steps.

Install mariadb/mysql (whatever the server package is called, for your
release of Debian).  Do whatever it is you need to do in the database
admin account so that you can restore your database dump as your regular
user.

As your regular user, feed the compressed database dump to the "mysql"
(or its mariadb equivalent) command:

zcat yourfile.gz | mysql

You may have to supply a password, or some command line options to mysql,
or something like that.  Whatever you would normally do to restore a
mysql database dump.

If you get stuck, try googling "restore mysql database dump" or similar.
Yours is presumably compressed, due to the *.gz suffix on the file, so
you'll need to zcat it, instead of just feeding it directly to mysql.
That's pretty much it.

[toc] | [prev] | [next] | [standalone]

#182787

Hi Greg, 
huuuuuu, that looks quite difficult for me. 
> Start by actually reading the compressed backup, using zless.  See whether
> it looks like an SQL dump.  If it does, then you can proceed to the next
> steps.
> 
Ho dso I do this? I unzipped my *.sql.gz and have now *.sql file. How can I 
see, if it is a sql-dump? What is this?


> Install mariadb/mysql (whatever the server package is called, for your
> release of Debian).  Do whatever it is you need to do in the database
> admin account so that you can restore your database dump as your regular
> user.
> 

I have installed mysql, but I do not want to run it as root. I am not sure, to 
kill some databases on my system, I need for other things. 

> As your regular user, feed the compressed database dump to the "mysql"
> (or its mariadb equivalent) command:
> 
> zcat yourfile.gz | mysql
> 
How can I create a database as a npormal user? mysqladmin inhibits this. I did 
mysqladmin db1 , then mysql db1 < mydatabase.sql , which only worked as root.

> You may have to supply a password, or some command line options to mysql,
> or something like that.  Whatever you would normally do to restore a
> mysql database dump.

Too heavy for me, sorry.
> 
> If you get stuck, try googling "restore mysql database dump" or similar.
> Yours is presumably compressed, due to the *.gz suffix on the file, so
> you'll need to zcat it, instead of just feeding it directly to mysql.
> That's pretty much it.

Same, too heavy.

Folks, I think, this is not an easy stuff! I am not experienced enough and I 
give up for now. 

Maybe I will take the other solution by using vim, and extract all my blogs 
from the sql-file manually, then put it into an html editor like bluegriffon, 
and then save all the blogs in a html file. Doing so, they can be trancoded 
into pdf or implemented into ODT. However, this is a lot of manual work, but 
not "brain killing" like this. 

I hoped, there would be an easy way, opening a GUI, choose my text with drag-
and-drop and off we go. 

Sorry, there is none, I see now. Let this issue close, thanks, great thanks, 
for all the help, really, but it is going much too far now.

Best wishes and thanks again

Hans

[toc] | [prev] | [next] | [standalone]

#182790

On Wed, 28 Jun 2017 15:12:26 +0200
Hans <hans.ullrich@loop.de> wrote:

> Hi Greg, 
> huuuuuu, that looks quite difficult for me. 
> > Start by actually reading the compressed backup, using zless.  See
> > whether it looks like an SQL dump.  If it does, then you can
> > proceed to the next steps.
> >   
> Ho dso I do this? I unzipped my *.sql.gz and have now *.sql file. How
> can I see, if it is a sql-dump? What is this?
> 

Any program which can handle text should be able to see the content, if
it was not encrypted when the backup was made. Here is a fragment of
one of my old .sql dump files:

-- Dumping structure for table service1.tbl_units
DROP TABLE IF EXISTS `tbl_units`;
CREATE TABLE IF NOT EXISTS `tbl_units` (
  `UnitID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Model` varchar(20) NOT NULL,
  `Serial` varchar(10) NOT NULL,
  `Firmware` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`UnitID`),
  UNIQUE KEY `Serial` (`Serial`)
) ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=utf8
COMMENT='Construction history';

-- Dumping data for table service1.tbl_units: ~311 rows (approximately)
DELETE FROM `tbl_units`;
/*!40000 ALTER TABLE `tbl_units` DISABLE KEYS */;
INSERT INTO `tbl_units` (`UnitID`, `Model`, `Serial`, `Firmware`) VALUES
	(1, 'ILC-1', '0001', 'V1.23'),
	(2, 'ILC-1', '0002', 'V1.23'),
	(3, 'ILC-1', '0003', 'V1.23'),
	(4, 'ILC-1', '0004', 'V1.23'),
	(5, 'ILC-1', '0005', 'V1.23'),
	(6, 'ILC-1', '0006', 'V1.23'),
	(7, 'ILC-1', '0007', 'V1.23'),
	(8, 'ILC-1', '0008', 'V1.23'),

As you can see, the actual table data is in a highly structured form,
and could almost certainly be imported somewhere as CSV. I realise that
some of the data in fields of your database will be quite large.

The only issue I can see is one of size, if there is a very large
number of fields in each record of the table you need, or if a field is
too large for a cell in a CSV application such as LibreOffice Calc, or
if there are too many records.

-- 
Joe

[toc] | [prev] | [next] | [standalone]

#182776

Hi Hans,

> Maybe I should precise my question: How can I restore my data from this sql
> database on my desktop? Is there a tool to extract my blogs?
>
> I do not want to build a webserver with php and wordpress and so on, just
> extract my blogs.  Phpmyadmin needs a webserver to run. This is not, what I
> wanted, of course.

you need apache + php only if you are using phpMyAdmin.

If you use Tora (or mySqlBrowser, or squirrel-sql, ..) you can browse
your database from your desktop.
But you must have a database server in which restore the database (and
the data)!

After restoring, i think you can copy every blog entry into an html files.
Then you can open them directly in your web browser.
Other way: you can try to migrate them to other desktop tools (like
Tagspaces) in which data are stored in html format.
Of course you can write a little bash/java/python script to do the job.

M

[toc] | [prev] | [next] | [standalone]

#182777

[Multipart message — attachments visible in raw view] — view raw

On Wed, 28 Jun 2017 10:53:02 +0200
Hans <hans.ullrich@loop.de> wrote:

Hello Hans,

>Maybe I should precise my question: How can I restore my data from this
>sql database on my desktop? Is there a tool to extract my blogs? 

'sqlitebrowser' _may_ be of some assistance.

Disclaimer;  I've only used it briefly so don't know its full
capabilities.

-- 
 Regards  _
         / )           "The blindingly obvious is
        / _)rad        never immediately apparent"
I'm not here for your entertainment
U & Ur Hand - P!nk

[toc] | [prev] | [next] | [standalone]

#182804

On Wed, 28 Jun 2017 10:00:00 +0200
Hans <hans.ullrich@loop.de> wrote:

> Dear list,
> 
> this is a little offtopic, but maybe you can make things a little bit clearer 
> for me.
> 
> I had had a webserver hosted by a provider, which is switched off since a 
> year. From this server I got a backup of a sql database. The system that ran 
> that time, was wordpress, and the database is called something like 
> "bla_bla_wp2016018_911.sql.gz"
> 
> On this website I wrote some blogs, which I want to have back.
> 
> So my question: Are these blogs content in this database? And if yes, can I 

WordPress database backups contain all the blog content (but not, IIUC,
files (such as media) that you may have uploaded in association with
the blog).

> restore them without to setup a complete wordpress server with sql database?

Yes, as other posters have suggested, but you should think carefully
about your insistence on not setting up a WordPress installation. It's
actually not that difficult - mysql and wordpress can both be easily
installed the usual way via the package managment system, and while
there may be a little fussing necessary to get WP working properly and
import your database backup, it shouldn't be too difficult even for a
database novice. WordPress has great documentation for this sort of
thing, and the last time I did this (in a very similar situation to
yours - I was leaving one hosting provider for another, the former
account was shut down, and I had up to date database backups), I was
actually quite shocked by how simple it was - just open the WordPress
interface (dashboard), do "import database", and that's pretty much it!

> Is there an easy way or only a hard way?

> Would be nice, if someone could give me some points, I am not so experienced 
> with databases.
> 
> Thanks and regards
> 
> Hans

Celejar

[toc] | [prev] | [next] | [standalone]

#182815

On 06/28/2017 01:29 PM, Celejar wrote:
> On Wed, 28 Jun 2017 10:00:00 +0200
> Hans <hans.ullrich@loop.de> wrote:
>
>> Dear list,
>>
>> this is a little offtopic, but maybe you can make things a little bit clearer
>> for me.
>>
>> I had had a webserver hosted by a provider, which is switched off since a
>> year. From this server I got a backup of a sql database. The system that ran
>> that time, was wordpress, and the database is called something like
>> "bla_bla_wp2016018_911.sql.gz"
>>
>> On this website I wrote some blogs, which I want to have back.
>>
>> So my question: Are these blogs content in this database? And if yes, can I
>
> WordPress database backups contain all the blog content (but not, IIUC,
> files (such as media) that you may have uploaded in association with
> the blog).
>
>> restore them without to setup a complete wordpress server with sql database?
>
> Yes, as other posters have suggested, but you should think carefully
> about your insistence on not setting up a WordPress installation. It's
> actually not that difficult - mysql and wordpress can both be easily
> installed the usual way via the package managment system, and while
> there may be a little fussing necessary to get WP working properly and
> import your database backup, it shouldn't be too difficult even for a
> database novice. WordPress has great documentation for this sort of
> thing, and the last time I did this (in a very similar situation to
> yours - I was leaving one hosting provider for another, the former
> account was shut down, and I had up to date database backups), I was
> actually quite shocked by how simple it was - just open the WordPress
> interface (dashboard), do "import database", and that's pretty much
> it!
>

I agree. I'm a newbie my self. I did it with no problem under Jessie and 
setting up the server on localhost.

Under Stretch with Mariadb in place of Mysql there is a change that I've 
not comprehended yet.


>> Is there an easy way or only a hard way?
>
>> Would be nice, if someone could give me some points, I am not so experienced
>> with databases.
>>
>> Thanks and regards
>>
>> Hans
>
> Celejar
>
>

[toc] | [prev] | [next] | [standalone]

#182836

On Wed, Jun 28, 2017 at 5:00 PM, Hans <hans.ullrich@loop.de> wrote:
> Dear list,
>
> this is a little offtopic, but maybe you can make things a little bit clearer
> for me.
>
> I had had a webserver hosted by a provider, which is switched off since a
> year. From this server I got a backup of a sql database. The system that ran
> that time, was wordpress, and the database is called something like
> "bla_bla_wp2016018_911.sql.gz"
>
> On this website I wrote some blogs, which I want to have back.
>
> So my question: Are these blogs content in this database? And if yes, can I
> restore them without to setup a complete wordpress server with sql database?

This question should be first asked on the wordpress list, not here. Some
of the wordpress devs do haunt the debian lists, but you'll just get better
answers there.

> Is there an easy way or only a hard way?

Yes. There is an easy way and many ways to easily make it way harder than it
should be.

> Would be nice, if someone could give me some points, I am not so experienced
> with databases.

It will be much easier to figure the database part out with wordpress
installed and running. You can set it up for access only from the local
machine, and then you can play around with the files as you like.

Much easier to make progress when you have the thing in front of you,
running.

If you have, for example, other mysql/maria database stuff on the
machine, you can set up wordpress to not conflict, with help from the
people on the wordpress list.

And if you have questions they can't answer, we may be able to help
you here. Or we may send you to the mysql list.

(I personally have found that shying away from signing onto new
mailing lists has caused me more trouble than keeping track of the
new passwords, etc.)

-- 
Joel Rees

One of these days I'll get someone to pay me
to design a language that combines the best of Forth and C.
Then I'll be able to leap wide instruction sets with a single #ifdef,
run faster than a speeding infinite loop with a #define,
and stop all integer size bugs with my bare cast.
http://defining-computers.blogspot.com/2017/06/reinventing-computers.html

More of my delusions:
http://reiisi.blogspot.com/2017/05/do-not-pay-modern-danegeld-ransomware.html
http://reiisi.blogspot.jp/p/novels-i-am-writing.html

[toc] | [prev] | [standalone]

Back to top | Article view | linux.debian.user

csiph-web