I haven’t posted in ages due to being generally slammed with work but this little piece I threw together was too good to forget about so I wanted to put it down.

If you work with a larger owncloud deployment and have a lot of users and allow file sharing, you may be curious to occasionally take a look at how many shares there are, who owns them, who they are shared with. This isn’t easy to get from the Web GUI but via the command line and mysql it isn’t bad at all.

So, login to mysql on the command line and then use your owncloud database; ie. (if your db name is “owncloud”)

use owncloud;

Then run the following:

select id, share_with, uid_owner, item_type, file_target from oc_share
INTO OUTFILE '/var/lib/mysql-files/shares.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Exit mysql command line and go to /var/lib/mysql-files and you will find a nicely formatted CSV file with a list of all shares and who they are shared with. The columns from left to right…

ID, Who it is shared with (one line per person/group), the person that owns the share, whether or not it is a file or folder, the name/location of the file/folder that is shared

Cheers…

This is the final article (I think…) on MySQL database backup. In my previous article I discussed how to craft a very efficient script for automated LOCAL backup of your MySQL databases. But that isn’t the whole story. What happens if your server burns to the ground… or more likely the hard drive gives up the ghost, corrupting everything and effectively leaving you up the proverbial creek without a paddle? Today I want to finish our script by adding a couple of extra components and giving you a paddle in the form of “offsite” backups. (more…)

Earlier this year I wrote an article about creating scripts for backing up your MySQL databases.

A week ago I wrote another article about automated and secure root user login to your root MySQL database account.

I have also been learning Python on and off and learning quite a bit about variables and loops. While the syntax doesn’t carry over to BASH, the the logic does.

Combining the ability of password-less root login and for-do loops, I was able to drastically clean up the size of my original database backup script (my first reference above). To be exact, I had nine databases and 35 lines of code. My new script has 10 lines of code. Furthermore, my original script wasn’t “intelligent” in the sense that you would have to update it (and make it longer) anytime you wanted to backup an additional database. The script I am going to discuss in this article should stay at ~10 lines regardless of how many databases are being backed up on the server.

So today’s script is vastly shorter and, better yet, will automatically backup any new database you add to your MySQL instance! Awesome right? (more…)

If you are looking for a quick way to install a LAMP (Linux, Apache, MySQL, PHP) stack on an Ubuntu server, this should take care of you:

sudo apt-get install tasksel
sudo tasksel install lamp-server
mysql_secure_installation

First command installs tasksel… which is a really handy program. (more…)

Do you work with MySQL? I do… quite a bit.

Do you often script stuff on your server to make your life easier? I do that as well… quite a bit…

Are you including your database user account and password (or worse… your mysql instance root user account and password!) in plain-text in your script… I was doing this… and it is bad practice from a security standpoint for sure…

Okay, so if you have a bunch of scripts (and I have several for database maintenance and database backups) floating around and many of them contain your MySQL root user account credentials… that can be a real issue. There is a better way!
(more…)