LO Base/MySQL Backup??

users
#1

Hi All

I'm now using the MySQL back-end (5.1.55) with LO Base (3.6.5.2) as my front-end - all on PCLOS 2013.

I've been "messing" around since late last year getting this all right, (with lots of GREAT help from the Forum!!)

My question now is - Where does MySQL store the Database and how do I track it down to make a backup?? Because of all my "messing" around I have several different files in the system that may be the right one but I'm not sure which is which.

I lost my whole Database at the beginning of the year when I re-installed my OpSys so DON'T want that to happen again and need a reliable backup method.

Any suggestions would be most welcome.

Thanks a lot

IanW
Pretoria RSA

#2

If I remember correctly, you have installed MyAdmin on your computer to perform administrative tasks for MySQL. Use it to create a backup file. (Backing up data is one of the administrative tasks.)

--Dan

#3

mysqldump database name > filename (with authentication) is the best backup
method. You can look up the details in the docs.

#4

Hi Ian,

My question now is - Where does MySQL store the Database and how do I
track it down to make a backup?? Because of all my "messing" around I
have several different files in the system that may be the right one but
I'm not sure which is which.

The data directory is usually to be found somewhere as a subdirectory in
/var

/var/lib/mysql
/var/data/mysql
/var/www/mysql (not really supposed to be there, but some distros do
some funky things)

or else somewhere in /usr/local/mysql (as is the case on OSX)

/usr/local/mysql/data/

Alex

#5

Hi Ian,

Note that if you are using InnoDB as your engine for your database,
simply copying the whole data directory to somewhere else in the hope of
being able to copy it back one day if everything goes pear shaped will
not work. InnoDB uses a binary log to watch for changes to the database,
and these need to be replayed when re-installing your database. There is
a whole set of dedicated information for the tools that mysql provides
specifically for this purpose and which are run from the command line.

If you can't install mysql workbench, which I understand is problematic
from a brief reading of the PCLinuxOS forum, you could always try
installing MySQLAdministrator if you can find a suitable RPM for your
system. Although this has been officially abandoned by mysql as
end-of-life (and replaced by Workbench), it can still be used fairly
reasonably.

According to this post, you can find your mysql data directory here :
/var/lib/mysql

http://www.pclinuxos.com/forum/index.php?topic=103953.0

Alex

#6

My question now is - Where does MySQL store the Database and how do I
track it down to make a backup??

You'll have to use the MySQL administration tool for that. LO only sees
a server running at an IP address listening to a specific port. It has
no means to know where the data is.

And I still recommend PostgreSQL over MySQL. :wink:

Among others, it allows backing up a database while "live".

Sincerely,

Wolfgang

#7

Someone mentioned mysqldump, this is definitely the way to go. Set it up
on a cron and have it dump files to a backup directory.

#8

Sajan,
The only problem I see with a cron job is a security issue. I assume you are putting your MySQL password(s) in your cron job script, since mysqldump requires it. I would not recommend doing that. I have a system backup script which I manually run monthly. It does not include the passwords. When that script runs, mysqldump prompts me for the password, I enter it, and off it goes.

I might add that I have been using mysqldump for several years and I have not had any problems with it or with retrieving the database data from its backup files. I need to retrieve the backup when I upgrade MySQL, in order to restore my databases in the new version. It works fine, even when I upgraded from MySQL 5.0.67 to 5.5.29.
Girvin Herr

Sajan Parikh wrote:

#9

Hi Girvin.
For security I created a mysql user 'backup' with read only access for backup.
I make the cron script readable by root only

-rwxr-x--- 1 root root 586 2012-12-05 16:19 /etc/cron.backup/dailytmp.bu

MYSQL_PWD="*******" mysqldump -ubackup --all-databases --add-drop-database | gzip -9 > /home/bu/mysql.sql.gz

Steve

#10

I don't see the security issue in mysqldump using a cron. You can throw
your password in a mysqldump.cnf file and make it only readable by your
user. You can combine that with what Steve said and use a special MySQL
user as well.

#11

Sajan & Steve,
Thanks. I will have to look at this as an alternate possibility.
I didn't mean to criticize, but I take computer security seriously and when I see a vulnerability, I tend to bring it to the person's attention. There are far too many people out there who take computer security nonchalantly and can be bitten.
Girvin

Sajan Parikh wrote:

#12

Hi :slight_smile:
I think for a lot of us on the list that have been lurking around this topic the security issue has been interesting.  I think this discussion and the different approaches to dealing with the issue may well help some of us in the future.  many thanks to all that have posted in this thread.  Some valuable insights there! :slight_smile:
Thanks and regards from
Tom :slight_smile:

#13

No issue Girvin. Discussion is good.

It's worth noting that mysqldump is there to be run on a cron. Unless you
use something like Percona or pay the $5k for Enterprise, mysqldump is what
is being used by sysadmins everywhere considering there's really not much
else unless you go the route of snapshots and doing master-slave. Which is
what I've done in the past for larger databases, but I just assumed this
was a smaller more personal database server. There's no security issue with
mysqldump itself. Of course, anything can be used stupidly and insecurely,
that doesn't mean the product was insecure though.

I think my question to you would be why you specifically think putting
mysqldump in a cron is insecure?

#14

Hi Ian,
Ive been reading the posts concerning the security of your database using
mysqldump. Are you using a remote server for your MySQL, or is it on your
own computer?
I've completed a database using LO Base with embedded HSQL. I guess I can
use a MySQL server on my own machine to which I can connect LO Base to
avoid the data loss problems with the embedded
system. Is that he reason you went to MySQL?
Hank