Back up dbs in txd

colak · 2013-10-27 07:47:54

Our db is getting larger (35mbs, 25 of which is the textpattern table). The new txd phpMyAdmin does not allow for compressed backups (although virtualmin does). I am trying to use both but there seem to be some data missing when I back up the textpattern table as the resulting file is about 23Mbs (not 25).

Can someone post a step by step tutorial on how to back up largish dbs please.

I found this post by Jacques but I’m not sure

if it will work on larger dbs
how to install and make it work

Last edited by colak (2013-10-27 08:05:37)

Gocom · 2013-10-27 09:44:31

colak wrote:

resulting file is about 23Mbs (not 25).

The SQL dump? The SQL dump size has no relation to the database size. They are two different things. Other one is a data table, other is merely list of SQL statements. To check whether the file contains everything, you will have to import it, the size tells nothing since you are comparing apples with oranges.

I can create a dummy database that weights 1 GB, but when it backup it, the resulting SQL dump is just hundred kilobytes. I just have to create rows which space requirement is more than there is data, resulting a lot of nothing, other than SQL statements. For instance, the textpattern table (when as actual table) will require more space than there is actual data.

Can someone post a step by step tutorial on how to back up largish dbs please. […] I found this post by Jacques but I’m not sure

That’s a shell script meant for backing up the databases and uploading them to other server. You would SSH to the machine, and run it.

To simply backup a large database, SSH to the machine and then run mysqldump command:

$ ssh username@servername.textdrive.us
$ mysqldump -u username -ppassword database > some/where/were/you/want/dump.sql

Last edited by Gocom (2013-10-27 09:44:43)

colak · 2013-10-27 10:57:11

Hi Jukka, thanks for your reply but I still don’t get it. I’m a total idiot with this things.

Do I have to upload those files in the server?
- If so where in the server? (I have a number of sites there).
Do I ssh using the OSX terminal?
How to I enable ssh in txd?
is there a typo in the script above? should it be: -p password?

Gocom · 2013-10-27 12:26:18

colak wrote:

Do I have to upload those files in the server?

You mean those shell scripts? No, and you don’t need them, and place would matter neither. Those scripts are created for use as an automated task that takes a backup and rsyncs it elsewhere.

Do I ssh using the OSX terminal?

Yes. To get more information about each command, ask man:

$ man ssh

You can navigate the man page using arrows and close the page by pressing q.

How to I enable ssh in txd?

Its always enabled, I believe. Otherwise you wouldn’t be able to even use SFTP.

is there a typo in the script above? should it be: -p password?

There is never space before password argument’s value; unless the space is part of the password. Before you run unknown commands, you should first always read the man page. You know, someone could tell you commands that would nuke your drives and you would have never known. Man page tells what the application doe, its options, how to use them and what they mean.

Last edited by Gocom (2013-10-27 12:28:24)

colak · 2013-10-27 13:32:30

Ok, I’m getting somewhere now and I’m kind of starting to understand. I have 2 questions re your command $ mysqldump -u username -ppassword database > some/where/were/you/want/dump.sql

some/where/were/you/want/dump.sql: could this be ~/Desktop/bu/dump.sql ? That is, could I have the dump in my disk?
related to the question above, should I create an empty file called dump.sql in the directory I specify?

Gocom · 2013-10-27 14:03:56

colak wrote:

That is, could I have the dump in my disk?

No, each command is run on the server you are SSH’d into; think at as if you were using remote desktop (its not your system, its the remote system). You can either;

Use SSH’s command argument and write the output to your local system.
Or download the finished file using SFTP, scp or rsync.

Using SSH’s command argument:

$ ssh username@servername.textdrive.us "mysqldump -u username -ppassword database" > ~/Desktop/bu/dump.sql

Which logins to servername.textdrive.us as username, runs the mysqldump command, captures output, closes the connection and forwards the captured output to ~/Desktop/bu/dump.sql. I believe SSH uses memory to keep the output, so you can’t use it backup databases that are several gigabytes in size, tho.

Otherwise if you want to keep connection open, or not to capture files in memory, you can use, for instance, scp after you are done with the session:

$ ssh username@servername.textdrive.us
> mysqldump -u username -ppassword database > /tmp/dump.sql

Then end the session (CTRL+C, or open new tab with CMD+T), and on local end download the file from the server:

$ scp -C username@servername.textdrive.us:/tmp/dump.sql ~/Desktop/bu/dump.sql

related to the question above, should I create an empty file called dump.sql? in the directory I specify?

No. Terminal will try to create any directories and files for you if they don’t exist.

Last edited by Gocom (2013-10-27 14:11:54)

colak · 2013-10-28 08:08:19

I Jukka, I am still trying to do this backup.

This is what I do

I created a folder just before the root of the sites
I typed $ ssh myusernamemyservername.textdrive.us@
I was prompted to type the password which I did
I tried using $ mysqldump -u mysername -pmypassword database > some/where/were/you/want/dump.sql but I get -bash: $: command not found

> And a second later I did it by taking $ out:) Thanks sooo much for all your help and advice on this one

Last edited by colak (2013-10-28 08:10:43)

gaekwad · 2013-10-28 15:36:43

Yiannis, if you’re on OS X (and your references to OS X Terminal make me assume you are), try SequelPro as a GUI option. It’s excellent, and as long as your MySQL instance isn’t limiting connections to just localhost, you should have no problem connecting to your server.

I’m late to the party, I know, but you might get along OK with SequelPro.

mrdale · 2013-10-28 16:06:39

gaekwad wrote: try SequelPro

Listen to the man from crack-town. SequelPro is incredible and it’ll work well with your textdrive hosting (if it’s up).

colak · 2013-10-28 16:23:17

Hi Pete and Dale

That sounds good and I downloaded and installed it. One question regarding this one.

How do I back up to my disk? :)

gaekwad · 2013-10-28 17:25:58

colak wrote:

How do I back up to my disk? :)

Connect to your server, choose the database from the drop down (top left), ensure you have the tables listed on the right, then File -> Export. Choose your save location and filename format. I did a quick and dirty screencast here – the part where the Export dialog appears is File -> Export, or Apple-Shift-E. The filename format I use is to ensure I know what’s what in a given file; I would much prefer an slightly verbose/unwieldy filename than something useless like export.sql.

Last edited by gaekwad (2013-10-28 17:30:28)

colak · 2013-10-28 17:55:37

Thanks Pete,

I might have downloaded a newer version to what you have but your screencast was tremendously helpful. Main difference is that I had to select all the tables before clicking on the button.

Textpattern CMS

Textpattern CMS support forum

#1 2013-10-27 07:47:54

Back up dbs in txd

#2 2013-10-27 09:44:31

Re: Back up dbs in txd

#3 2013-10-27 10:57:11

Re: Back up dbs in txd

#4 2013-10-27 12:26:18

Re: Back up dbs in txd

#5 2013-10-27 13:32:30

Re: Back up dbs in txd

#6 2013-10-27 14:03:56

Re: Back up dbs in txd

#7 2013-10-28 08:08:19

Re: Back up dbs in txd

#8 2013-10-28 15:36:43

Re: Back up dbs in txd

#9 2013-10-28 16:06:39

Re: Back up dbs in txd

#10 2013-10-28 16:23:17

Re: Back up dbs in txd

#11 2013-10-28 17:25:58

Re: Back up dbs in txd

#12 2013-10-28 17:55:37

Re: Back up dbs in txd

Board footer