How To Create a New User for MariaDB

I had to setup a few test databases recently and I had forgotten how to create a new local user. For future reference.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

You can also use the percent sign to allow both local and remote access.

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; 

In order to allow permissions to tables you need to grant privileges. This command will grant your user privileges to all databases on the server. Not normally what you would do as it is not very secure but in this case I am just setting up a test on a VM inside the local network.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

When you are all done you will need to reload the permissions in order for them to take effect.

FLUSH PRIVILEGES;

References: 

http://dev.mysql.com/doc/refman/5.7/en/create-user.html 

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Syslog to MySQL Update

I am in the process of updating some machines around here.
I am installing Mageia 4 Linux on one machine and it will be my syslog repository.

Mageia 4 is running journalctl and does not use syslog natively by default.
I had to install rsyslog, rsyslog-mysql, mariadb, and other required packages.

The first step is to modify the 00_ common.conf. Most of the lines matched what I needed but we needed to enable the UDP port to listen for other devices logging to this one.
 Uncomment the following two lines.

$ModLoad imudp.so
$UDPServerRun 514




Restart rsyslog to use the new settings.

systemctl rsyslog restart

Now run netstat -a and verify the the UDP port *.syslog or UDP port 514 is open.

Force some device that is configured to log to this server to do something to create and entry. I logged into a switch to get it to log. Then check the log file.

tail /var/log/syslog

If you have entries from the correct device you know that remote logging is working. Now to get the database part done.

rsyslog come with a default SQL schema in  /usr/share/doc/rsyslog-mysql/.
The file is called createDB.sql.

To setup the default database I ran:

mysql < createDB.sql

 This creates a database called Syslog with two tables.

Then I created a new user and gave it privileges to the Syslog database:

GRANT ALL PRIVILEGES ON Syslog.* To 'Syslogusername'@'localhost' IDENTIFIED BY 'password';

The rsyslog config file /etc/rsyslog.conf calls config files in two other locations.

One location is /etc/rsyslodg.d/ and the file 01_mysql.conf needs to have two lines:

$ModLoad ommysql.so
 

*.*     :ommysql:127.0.0.1,Syslog,Syslogusername,password

The first line loads the mysql library and the second line has the database name and the user credentials.

Another restart of the rsyslog service and your entries should now start being logged into the database.

SELECT * FROM Syslog.SystemEvents; and check for the entries. 

Removing Duplicates in MySQL

I inherited a database that had some performance problems.

To improve performance on one of the tables we added a few indexes based on common searches for that table. The problem was that there were a number of duplicates that prevented creation of the indexes.

To fix this problem I ran the following query all at once in phpmyadmin as temporary tables only last the duration of the connection.

CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM bad_table;
 
DELETE FROM bad_table;

INSERT INTO bad_table(email,nid,timestamp) SELECT email,nid,timestamp FROM bad_temp;

DROP TABLE bad_temp;

This took care of the problem and I was able to add the index.

ALTER TABLE `good_table` 
ADD PRIMARY KEY (`email`, `nid`, `timestamp`)

A nod to Database Journal that gave me the idea.

Syslog to MySql on openSUSE

I have been experimenting with openSUSE lately as I am not impressed with the direction Mandriva Linux is going. Mandriva has been my main distribution since early versions of Mandrake back around 1998.

I followed the steps in my earlier post but I had to fiddle with a few more things to get it running on openSUSE.

In /etc/rsyslog.conf I added:

$ModLoad imudp         # load udp server
$UDPServerRun 514      # start udp server

the UDP server was not running by default so I added it and poked a hole in the firewall so that I could log the router messages to the database.

Syslog to MySQL

I had a need to put Syslog data into a database for further analysis.
Using one of my linux boxes this turned out to be much easier than I thought, at least for basic functionality.

Basically I followed the instructions at http://www.rsyslog.com/doc/rsyslog_mysql.html
 and everything just worked.

I already had MySQL installed and Rsyslog was the default syslog on my Linux distribution so I used the included script to create the database, then I created the account.

mysql -u root -p < /usr/share/doc/rsyslog-mysql/createDB.sql

grant all privileges on Syslog.* to 'Syslog'@'%' identified by 'password' with grant option;

Then I modified the rsyslog config file.

#put this line as the first line in /etc/rsyslog.conf
$ModLoad ommysql

#this line logs everything
*.*       :ommysql:127.0.0.1,Syslog,Syslog,password
#*.*       :ommysql:127.0.0.1,db-name,db-user,db-password

Then I restarted the Rsyslog process.
/etc/rc.d/init.d/rsyslogd restart

Then I issued a 'SELECT * FROM Syslog.SystemEvents;'


I saw that the local machine was logging all of its syslog data and the other devices that were logging to the syslog server were also showing up in the database. As a point of interest, all of the data was still being put in the regular syslog file as well.

Future work on this will be to tailor what gets logged and some possible customizations.

Dynamic R Plotting

I needed to create a graph that contained multiple lines of data.
This is relatively easy to do in R when you know about the data like the maximum values and the number of lines.
What is not so easy is to create an R script that will dynamically create the graph without knowing anything about the data ahead of time.

It appears that the call to plot() needs to have sort of parameters to create graph.
Just calling plot.new() isn't going to give me the results that I need.
I am certain that there is a better way to do this but it works.
I just used a counter in the loop and if it is the first time through I call plot with the first line.
If it is not the first time through then the lines() method is called to add the data.

The colors are dynamically created based on the number of lines.

# required libraries
library(RMySQL)

cntr <- 1# set a counter that will represent the # of times through the loop

# connect to db using credentials in my.cnf
dbcon <- dbConnect( MySQL(), group="localnfl")

# get the years with data from the database
# returns a data frame
years <- dbGetQuery( dbcon, "SELECT DISTINCT( year) FROM nfl.bet_lines")

#define the image that will hold the graph
png("../../public_html/betline.png", width=1024, height=1024)

#create a vector to hold text for legend
legend_text <- character()

# choose enough colors (one for each year)
# returns a character vector
colors = rainbow( length(years$year) )
# iterate over each year
for( y in years$year )
{
  #create a string that will be the SQL
  # used this as a poor man's prepared statement to pass in year
  sql <- sprintf("SELECT IF( (hscore-vscore)>0 AND lineopen>0,
                         ABS(lineopen- (hscore-vscore))*1,
                         ABS(lineopen- (hscore-vscore))*-1) AS mov
                  FROM nfl.bet_lines
                  WHERE week <= 17 AND year = %d
                  ORDER BY mov asc",y )

  # run the query returns a one column data frame
  values <- dbGetQuery( dbcon, sql )

  # if this is the first time through create the plot with
  # type="n" for no actual plot ( could probably just plot the first line)
  if( cntr == 1 )
  {
    # create the plot with no axes labels( ann =FALSE )
    plot( row.names(values), values$mov, ylim=c(-50,50), type="n", ann=FALSE )
  }

  lines( values$mov, type="l", lwd="2", col=colors[cntr] )

  #add the current year numeric value to the legend text as a string
  legend_text[length(legend_text)+1] = as.character(y)

  cntr <- cntr + 1  #increment the counter
}

#create the main title, double font size(cex=2), italic is 4
title(main="Mad Title", cex.main=2, font.main=4)

# place text on top(3)
mtext( "actual > 0 and opening line > 0", 3 )

# Label the x and y axes
title(ylab="| Line - Actual |")
title(xlab="Game Number")

#create the legend
legend( "topleft", legend_text, fill=colors )

dev.off()

dbDisconnect(dbcon) 

Let me know if you have any suggestions on improving this R script.

MySql Shortcut

If you access a MySql database from a linux machine here is a tip that can save you some time. In your home directory create a file named .my.cnf. That is dot-my-dot-cnf.

Then in that file you can create multiple entries like the following:

[client]
user=username
password=%!;*;^123
host=localhost

Now when you ssh in and type mysql, the client will automatically parse and use those credentials. You can specify multiple hosts if you want like this:

[client]
user=username
password=%$**^123
host=hosta.example.com

[client]
user=usernameB
password=%B^123
host=hostb.example.com

Then the MySql client will choose the proper set depending on the host you are connecting to. This feature served me well when using R scripts. In a R script that access a database you can specify the particular client group that you want to use.

In the .my.cnf file.

[Rdata]
user=Ruser
password=password
host=localhost

Then in the R file itself you can create the connection like this:

dbcon <- dbConnect( MySQL(), group="Rdata")

Now you can move the script around or share it without worrying about revealing your password information.