[plugin] [ORPHAN] rss_admin_db_manager - database backups and more

swf · 2005-08-24 21:26:54

hmm it worked for me this time. Great plugin. I know I can use mysql front or phpmyadmin for backups but this is convenient to be able to do it form within txp.

bauhouse · 2005-08-24 21:42:49

Some Problems and a Suggestion

1. I have a local installation of Textpattern 4.0 (the deanload) working in Mac OS X 10.4 with MySQL v4.1.12-standard. I also have the identical Textpattern install on TextDrive where they have MySQL v4.1.13-log running. I made a backup of my database in phpMyAdmin, just in case. Then, I’ve been trying out the db manager plugin on both installs. The db backup does not work on the local install (see errors above), but for the TextDrive install, db backup works for both the .sql file and gzipped file methods.

2. The .sql file contains HTML from the db backup page:

3. The .sql.gz file comes up with an error when trying to dearchive the file in Mac OS X: (I will email this to you, wilshire.)

<pre>
Unable to unarchive “database_filename.sql” into “Desktop”. (Error 2 -No such file or directory.)
</pre>

The SQL queries are garbled, but the HTML at the end of the file reads just fine, even though it shouldn’t be there: Edit: oops! The HTML doesn’t render too well here as code. I’ll email the sql files to you, wilshire.

The suggestion is to add more information to your help page about how the plugin will save the files to the “files” directory defined as the File Uploads Path in the Textpattern Advanced Preferences and that the plugin reads the files available and automatically lists them. That way I can backup the database on TextDrive, download the file to my Desktop, drop the file into the “files” directory of my local Textpattern install where I can go to the db backup tab and see all the backup files dropped into the “files” directory listed according to database name and date and time of backup. Very cool!

If the above problems were fixed, I would say this is one of the best plugins ever written. Well worth the ransom. You truly are a genius, wilshire.

Last edited by bauhouse (2005-08-24 21:51:41)

bauhouse · 2005-08-24 22:26:22

One other thought: because the backup files are saved to the “files” directory, there is a potential security risk when that directory is not protected. If I protect it, all my downloads become inaccessible to the public. Can the backup directory be set to another directory where I can set some .htaccess security, or even better, a directory outside of public_html?

aba · 2005-08-24 22:52:03

> bauhouse wrote:

> One other thought: because the backup files are saved to the “files” directory, there is a potential security risk when that directory is not protected. If I protect it, all my downloads become inaccessible to the public. Can the backup directory be set to another directory where I can set some .htaccess security, or even better, a directory outside of public_html?

“Options -Indexes” in a .htaccess might help.
I consider the names of the sql-Files wired enough so nobody stumbles over them because I only have them in that folder until I download them (and without Indexes someone needs to be quite good to guess the correct timestamp for the filename). Experience told me that saving a sql dump on the server that lost it’s data might be not the best idea…

But I have to admit, it would be nice to be able to give another path (changing the “BACKUP FAILED: file not there”-error message to something more understandable like “BACKUP FAILED: backup-path “.$backup_path.” does not exist” would be a nice touch as well. I think some people were a bit confused what the error message really meant…)

NeilA · 2005-08-24 22:58:43

Just for the record – installed the plugin on 4 TXP installs on Dreamhost, and thus far all create an error-free .sql and .gz file.

Haven’t tried restore yet, as I need to install the plugin on my testbed TXP…

However, I would echo the desirability of being able to choose the directory the backup goes into.

steventer · 2005-08-25 09:32:01

Doesn’t work for me; this is all I get in the downloaded file:

mysqldump Ver 8.23 Distrib 3.23.58, for pc-linux (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] —databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] —all-databases [OPTIONS]

-A, —all-databases Dump all the databases. This will be same as —databases with all databases selected. -a, —all Include all MySQL specific create options. -#, —debug=… Output debug log. Often this is ‘d:t:o,filename`. —character-sets-dir=… Directory where character sets are -?, —help Display this help message and exit. -B, —databases To dump several databases. Note the difference in usage; In this case no tables are given. All name arguments are regarded as databasenames. ‘USE db_name;’ will be included in the output -c, —complete-insert Use complete insert statements. -C, —compress Use compression in server/client protocol. —default-character-set=… Set the default character set -e, —extended-insert Allows utilization of the new, much faster INSERT syntax. —add-drop-table Add a ‘drop table’ before each create. —add-locks Add locks around insert statements. —allow-keywords Allow creation of column names that are keywords. —delayed-insert Insert rows with INSERT DELAYED. —delete-master-logs Issue RESET MASTER on the master just after taking the dump, and before unlocking tables. This will automatically enable —first-slave. —master-data This will cause the master position and filename to be appended to your output, before unlocking tables. This will automatically enable —first-slave. -F, —flush-logs Flush logs file in server before starting dump. -f, —force Continue even if we get an sql-error. -h, —host=… Connect to host. -l, —lock-tables Lock all tables for read. —no-autocommit Wrap tables with autocommit/commit statements. -K, —disable-keys ‘/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and ‘/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put in the output. -n, —no-create-db ‘CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;’ will not be put in the output. The above line will be added otherwise, if —databases or —all-databases option was given. -t, —no-create-info Don’t write table creation info. -d, —no-data No row information. -O, —set-variable var=option give a variable a value. —help lists variables —opt Same as —add-drop-table —add-locks —all —quick —extended-insert —lock-tables —disable-keys -p, —password[=…] Password to use when connecting to server. If password is not given it’s solicited on the tty. -P, —port=… Port number to use for connection. -q, —quick Don’t buffer query, dump directly to stdout. -Q, —quote-names Quote table and column names with ` -r, —result-file=… Direct output to a given file. This option should be used in MSDOS, because it prevents new line ‘\n’ from being converted to ‘\n\r’ (newline + carriage return). -S, —socket=… Socket file to use for connection. —tables Overrides option —databases (-B). -T, —tab=… Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon. -u, —user=# User for login if not current user. -v, —verbose Print info about the various stages. -V, —version Output version information and exit. -w, —where= dump only selected records; QUOTES mandatory! -X, —xml dump a database as well formed XML -x, —first-slave Locks all tables across all databases. EXAMPLES: “—where=user=‘jimf’” “-wuserid>1” “-wuserid<1” Use ~~T (—tab=…) with —fields~~… —fields-terminated-by=… Fields in the textfile are terminated by … —fields-enclosed-by=… Fields in the importfile are enclosed by … —fields-optionally-enclosed-by=… Fields in the i.file are opt. enclosed by … —fields-escaped-by=… Fields in the i.file are escaped by … —lines-terminated-by=… Lines in the i.file are terminated by …

Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
—print-defaults Print the program argument list and exit
—no-defaults Don’t read default options from any options file
—defaults-file=# Only read default options from the given file #
—defaults-extra-file=# Read this file after the global files are read

Possible variables for option —set-variable (-O) are:
max_allowed_packet current value: 25165824
net_buffer_length current value: 1047551

wilshire · 2005-08-25 12:28:02

doggiez – The server OS is whats important.

Stephen – good suggestions. I’ll have to look through your emails at the messages. I struggled with using the files directory versus another one. Maybe I should add the ability to define that path. The nice thing about using the files directory as you said is that you can upload a backup and then restore it. The downside of course being that its in a public directory.

steventer – Looks like a problem in how mysqldump is being called.

I’m going to work on making a few updates to the plugin so that you can turn on a debug mode to get more detailed logging of whats going on. That will help me to take a look at the problems. Stay tuned for an update.

els · 2005-08-25 13:04:06

> wilshire wrote:

> doggiez – The server OS is whats important.

Linux.

aba · 2005-08-25 14:10:59

> wilshire wrote:

> steventer – Looks like a problem in how mysqldump is being called.

mysqldump Ver 8.23 Distrib 3.23.58, for pc-linux (i686)

afair this version does not know anything about —skip-lock-tables

steventer: find the following code segment in the plugin and delete the —skip-lock-tables parameter, then try again.

<code> if($gzip) { $backup_path.= ‘.gz’; $backup_cmd = $path_to_mysqldump.$mysql_hup.’ —add-drop-table —skip-lock-tables ‘.$DB->db.’ | gzip > ‘.$backup_path; } else { $backup_cmd = $path_to_mysqldump.$mysql_hup.’ —add-drop-table —skip-lock-tables ‘.$DB->db.’ > ‘.$backup_path; }
</code>

steventer · 2005-08-25 15:12:38

Aba! Thanks very much!

it didn’t work quite the way you said (I think your <code><del></del></code>s were in slightly the wrong place, but I changed that section to the following:

<code> if($gzip) { $backup_path.= ‘.gz’; $backup_cmd = $path_to_mysqldump.$mysql_hup.’ —add-drop-table ‘.$DB->db.’ | gzip > ‘.$backup_path; } else { $backup_cmd = $path_to_mysqldump.$mysql_hup.’ —add-drop-table ‘.$DB->db.’ > ‘.$backup_path; }
</code> and it worked just great!

Thanks again.

Steve

wilshire · 2005-08-25 15:21:26

Thanks aba. I was suspicious of that option causing problems. I know at least a few hosts (Textdrive being one) that don’t allow you to lock tables by default. I’ll have to look at making this an option.

Can anyone else having problems confirm that removing the —skip-lock-tables option as aba suggested?

aba · 2005-08-25 15:23:44

it didn’t work quite the way you said (I think your <code><del></del></code>s were in slightly the wrong place,

As your post indicates, the del sections aren’t from me, they result from the code block parsing… ;)

Glad it worked for you.

bauhouse · 2005-08-25 16:14:20

I’ve explored the problem for Mac OS X a little more. What opened my eyes to the problem was trying to download the files created by db backup from different browsers. Trying to save the .sql file from Internet Explorer returned the following message:

<pre>
Internet Exploder doesn’t know how to handle the type of file you have selected.
You can choose to save this file to your disk or you can configure a Helper Application for this file.
MIME Type: application/download
File Name: index.php
</pre>

So the file corruption is happening when clicking on the download link because the download link appears to be combining the encoded SQL file and the index.php file. This was confirmed by downloading the same file by FTP (using Transmit 3) from TextDrive and comparing the file downloaded from the db backup tab. The file downloaded by FTP was perfect. The file downloaded from the db backup tab was corrupted. I’ll email you examples.

bauhouse · 2005-08-25 20:07:57

My local db backup problem in Mac OS X 10.4 — where the db backup tab would save an empty gzipped file or return an “empty file” error — ended up being a command line problem to do with the MySQL install: my default shell could not find the mysqldump command without the full path /usr/local/mysql/bin/mysqldump. This can be solved by editing the plugin. Change the following variable from this:

<pre>$path_to_mysqldump = ‘mysqldump’;</pre>

to this:

<pre>$path_to_mysqldump = ‘/usr/local/mysql/bin/mysqldump’;</pre>

Or to avoid having to modify the plugin everytime you restore your database, change the PATH environment variable in your shell’s login script:

<pre>echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> ~/.bash_profile</pre>

That is, find the invisible file .bash_profile (for example, with a [free] text editor such as TextWrangler using “File > Open Hidden…”) in the top level directory of your user account and add export PATH=/usr/local/mysql/bin:$PATH to the end of the file.

Last edited by bauhouse (2005-08-25 22:58:53)

aba · 2005-08-25 22:44:51

wilshire: If you reword the error messages and fix the mysqldump problem you might want to look into this as well:

<code>
delete * from txp_image

0/1 Query(s) Executed Successfully
</code>

Textpattern CMS

Textpattern CMS support forum

#31 2005-08-24 21:26:54

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#32 2005-08-24 21:42:49

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#33 2005-08-24 22:26:22

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#34 2005-08-24 22:52:03

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#35 2005-08-24 22:58:43

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#36 2005-08-25 09:32:01

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#37 2005-08-25 12:28:02

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#38 2005-08-25 13:04:06

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#39 2005-08-25 14:10:59

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#40 2005-08-25 15:12:38

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#41 2005-08-25 15:21:26

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#42 2005-08-25 15:23:44

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#43 2005-08-25 16:14:20

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#44 2005-08-25 20:07:57

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

#45 2005-08-25 22:44:51

Re: [plugin] [ORPHAN] rss_admin_db_manager - database backups and more

Board footer