Nup ol buddy ol pal. =)

[TheHolo.Net]

hehe...fun time

I have a favor to ask of you in regards to our host.

I’m not really sure if its possible(well I know its possible, I’m just not sure Tera-Byte will do it) but we need for the swforum username to have access to the /var/lib/mysql/swforums directory so that I can at least copy our database files to a public directory or maybe even be able to use the MySQL utilities to do a database back up. They really have to have some kind of procedure so that site operators can do their own database backups, whether its MySQL utilities or a simple request to them.

SSH is working great and I have been able to actually navigate thru the “real” root directory and find the folder our database resides in, but once I am there, poof, that is all I can do, look at the directory name. Trying to access said directory via a “cd” command generates a “permission denied” error.

I guess that’s what we need to ask:

Do you have a procedure for allowing your users to back up their own MySQL Database?

If not, do you have any procedures for backing up a user's MySQL database? Like a request maybe?

Is it possible for me to get full permissions to the “/var/lib/mysql/swforums” directory with the swforum username?

[TheHolo.Net]

Bump. :)

[Q]

Sorry, haven't been around much in the past few days. Work has been keeping me on my toes. |I I'll e-mail them about this as soon as I get home tonight.

[TheHolo.Net]

Thanks. :)

[TheHolo.Net]

Given Tera-Byte's so informative reply to our query, I did a little poking around in the telnet/SSH side of the server and found the command to backup the database. I'll have to give it a try later, when I can close the board for a little while and not inconvienience the posters too much.

For my own reference:

Command: mysqldump

usage:

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.
-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.
-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
--extended-insert --quick --lock-tables
-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 `
-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!
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

[TheHolo.Net]

Reference to a backup script I will try using.

<ul> Script to Dump MySQL Databases (http://vbulletin.com/forum/showthread.php?s=&threadid=4256)[/list]

[TheHolo.Net]

I need to do more research on executing scripts before I can use that one, but with its contents I was able to create a backup of our database as of 10:38 PM MST tonight that clocked in at 91 MB, which I am downloading a local copy of now. :D

Is pleased he is able to do database backups at all.

[Q]

Hmm... glad that Tera-Byte apparently doesn't consider that 91mb part of our alloted 200. :D