R Datalicious

R is calling me.  Anyhow, I’m trying to learn R.  I think R is very powerful, because it has features that allow you to collect data in various ways.  With R, you can also easily analyze data and make charts, graphs, and whatnot.  I’m a newbie in R, and so I may not be able to fully appreciate R.  I’m a newbie in R, and so I don’t know how to express how powerful R is to you.  Nonetheless, I know a few things so far.

Here is one example among many of how I understand R could be used by you.  With R, you can install Rvest package to easily collect data from the Internet (i.e., websites).  Note:  Data don’t have to come from the Internet.  It’s possible to use R to collect data and then store the data into MySQL database.  It is also possible that the data in MySQL can also be converted back into appropriate data formats in which R would be able to utilize.  Marrying MySQL and R together, it’s possible to enlarge the data collection scale by using R, and using R to format data in various manners.  (My newbie speak, because you may know of a more elegant way of going about using R in this specific manner.)

Anyhow, to scratch the surface of R and see how powerful R can be, just check out the two videos right after the break.  (These YouTube videos are not mine, because I embed them from other YouTube’s user-generated content authors.)  Enjoy!


What About Infobright?

I’m not that well versed with any database solution, but I’m depending on MySQL for several projects of mine.  Honestly, MySQL is way more adequate for what I’m trying to achieve with my projects.  Nonetheless, I’m a curious being, always looking for something new to experiment with.  So, recently I’d stumbled on an analytical database solution known as Infobright.

To tell the truth, I’m so new to something as analytical database solution and so I’m not sure of its benefits, but I think analytical database solution is gearing toward users who need to work with numerous terabytes of data.  These heavy users need ways to query their data as fast as they can, but the relational database such as MySQL sometimes isn’t fast enough to satisfy these users — here is where something as Infobright enters the scene.  Also, it seems Infobright can compress huge size of collections of data into much smaller size of collections of data, and this saves database owners lot of storage spaces.

I don’t think I can explain how Infobright would work better than the videos  (i.e., I’ve found them on YouTube) within this post.  Nonetheless, I sense that Infobright is an awesome solution for users who need to have something more robust than MySQL’s MyISAM and InnoDB engines.  Oh, I forgot to tell you that Infobright is probably just another engine which designs to work with MySQL.  Check the videos right after the break to know more about Infobright, because you never know if one day you may want to consider Infobright for your projects.

I’m sure if you dig around, you may find more useful information on Infobright and analytical database solution.  Infobright has an open source version for its community (i.e., users like you), and so you can take a whiff at their official website’s info on how to get Infobright’s community version so you can play around with it.  It’s free to download the Infobright community version!

Backing Up MySQL Databases using a shell script

Have you ever wondered how to backup huge amount MySQL databases, but you also want to be able to cherry pick specific MySQL databases to be included in your backup schedule?  How about on top of what you’ve already wondered, to be able to schedule the backup of these MySQL databases?  Obviously, typing in long command lines aren’t going to cut it.  Some control panels such as Cpanel can help alleviate your pain, but sometimes Cpanel chokes badly on long list of MySQL databases or huge MySQL databases, so you cannot really feel safe that your MySQL databases will get back up.  Rsync is great, but sometimes MySQL databases are hidden in locations that rather mystical to you since your system may have configure rather differently.  Why not just use mysqldump since it’s knowing rather well where all MySQL databases are hidden?  Unfortunately, if using mysqldump command in its normal way can be rather crude such as entering long command, but entering command is not yet the worse of all since you still have to re-entering the same very long command over and over again for every backup.  Plus, mysqldump itself cannot be used with cron efficiently to schedule your backup.

The solution is to use a shell script that someone or perhaps it’s you who cooks up the script itself.  To write a shell script, it’s demanding that you must know how to program somewhat.  Some of us are rather clueless on programming front, and so such option also may be out of our grasp.  Fortunately, the web is a huge archive where we can dig for some clues.  I’ve found this short shell script on DreamHost’s wiki page which proves to be very useful to me, and so it may also be the very mysqldump script you need for doing backups of your MySQL databases.

domains=( yourdomain.com yourdomain2.com )
sqldbs=( yourdb1 yourdb2 )
suffix=$(date +%m-%d-%Y)
for (( i = 0 ; i < ${#domains[@]} ; i++ )) do cpath=$opath${domains[$i]} if [ -d $cpath ] then filler="just some action to prevent syntax error" else echo Creating $cpath mkdir -p $cpath fi mysqldump -c -h $mysqlhost --user $username --password=$password ${sqldbs[$i]} > ${cpath}/${sqldbs[$i]}_$suffix.sql

So, let us break apart the essential parts from the code above which will allow you to configure and eventually execute the script to backup your MySQL databases.  At where it says domains= and inside parentheses it seems you need to enter your domain name, but that is misleading!  Instead, what you need to do is to enter a directory that you’re intending to keep your backup databases inside.  Unfortunately, this script behaves as if one database per domain name/directory as I have mentioned above.  To work around this, enter the same domain name or directory name inside the parentheses as many time as you need so the next line of code will understand how to store which database in which directory.  To make it clearer, the next line of code is sqldbs= and inside the parentheses would be your database names such as username_db01 username_db02 and so on; as you can see if you name the former line of code such as domains=( example.com example01.com ), then you will get two different directories which the former hold username_db01’s backup db and the latter directory which is example01.com will hold username_db02’s backup db.  In my case, I have abundance of databases under a domain name, and my configuration of the code above would turn out to be domains=( essayboard.com essayboard.com essayboard.com essayboard.com ) and continue on with the next line of code would be sqldbs=( db_01 db_02 db_03 db_04 ) — this makes db_01 and db_02 and db_03 and db_04 to be backed up inside only one directory names essayboard.com.

The line of code says opath=$HOME/backup/, you can change the portion says backup/ to anything you like such as you have a directory named backingupsales/.  This line of code will tell the script to create the directory which defined from the code domains=( yourdomain.com yourdomain2.com ).  To put it another way, whatever directories the line of code domains= creates, these will be placed inside the directory of which you had defined above from the line of code opath=.

The lines of code mysqlhost= and username= and password= are self-explanatory, but you need to put a single semicolon before and after the string of text you enter right after the equal sign.

Don’t touch anything else, and save the script in a location inside your server that you would remember.  Anytime you want to do a backup, just become a user which has permission to execute the script and do this command line:  sh thescriptname.sh.  thescriptname.sh can be in any name since it’s you who ultimately name the script and save it.  Just make sure the extension of the script would end up with .sh.

You can also use your text editor or Open Office’s spreadsheet to manipulate hundreds or even thousands of database names, then copy those database names and directory names into the appropriate portions of the script’s lines of codes.  I was able to back up thousands of databases using this script, and it takes me only few minutes to configure the script myself.

Since we have the script, we can always configure a cron job to run the script daily or weekly or monthly.  How to configure a cron job is beyond the scope of this post.  I’m sure you can google it easily.  Have fun backing up your MySQL databases!

Ghosts of The Past, A Story Of Dead MySQL Databases Repopulated Inside Cpanel’s Account

Ghosts from the past visit the now, and I’m not talking about a horror here.  Recently, I had learned something from Cpanel that I had no idea before.  If you’re unfamiliar with Cpanel, I suggest you google it!  Back to the main course, I logged into Cpanel and noticed that the MySQL databases I had deleted eon ago repopulated inside Cpanel.  I pulled my hair, googled everywhere, but I couldn’t find the source of the souls of these supposed to be dead databases.  Luckily, I called up a person who knew much more about ghosts of the past of MySQL/Cpanel, and he showed me the way.

It turned out, even though inside PHPMyAdmin and /var/lib/mysql, the ghosts of the past did not repopulated, and it supposed to be that way — this gave me an idea that everything had been normal besides the fact that the ghosts of the past of dead databases had repopulated within Cpanel.  The person I called up figured it out and told me that inside directory /var/cpanel/databases/, there would be two files that I could take a look at.  One would start with username.cache (replace username with a real account’s username) and the other would start with username.yaml (replace username with a real account’s username).

It turned out he was corrected.  The ghosts of the past of MySQL turned out to be just a list of lost souls who’s silhouettes refused to leave the living world.  Anyway, I deleted the list of dead databases in those two files, and my Cpanel is perfect again.  If you still don’t know why I had wanted to get rid the ghosts of the past, I’ll tell you so… it’s because I do not want every time I get into Cpanel’s MySQL section and I have to wait for Cpanel to recall all the ghosts of the past; it takes a long time you know?  Also, it can be very confusing by staring at the ghosts of the past, fearing of getting lost among them.

So, take a look at my report here and get rid of yours before Halloween once again returns!