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.