0

Tips & Tricks : MariaDB on CentOS : Installation and first configuration

-

In order to install MySQL/MariaDB database engine on CentOS follow these:

[root@localhost ~]# yum install mariadb-server
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# systemctl start mariadb

[root@localhost ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.


Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost ~]#

Recommended is also to deploy phpMyAdmin, if you don’t have any issues with installing web server on same box as database.

That will simplify MySQL/MariaDB management.

0

Code snippets : Windows : Importing Event Logs to Database

-

Code snippet from old archives. It shows how to import Windows event log entries to database. Important is to setup database and table accordingly, so it contains apropriate columns.

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=EventLogs;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM tblEventLog" , objConn, 3, 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
("Select Category, CategoryString, ComputerName, Data, EventCode, _
EventIdentifier, EventType, LogFile, Message, RecordNumber, SourceName, _
TimeGenerated, TimeWritten, Type, User from Win32_NTLogEvent")
For Each objEvent in colRetrievedEvents
objRS.AddNew
objRS("el_Category") = objEvent.Category
objRS("el_CategoryString") = objEvent.CategoryString
objRS("el_ComputerName") = objEvent.ComputerName
objRS("el_Data") = objEvent.Data
objRS("el_EventCode") = objEvent.EventCode
objRS("el_EventIdentifier") = objEvent.EventIdentifier
objRS("el_EventType") = objEvent.EventType
objRS("el_LogFile") = objEvent.LogFile
objRS("el_Message") = objEvent.Message
objRS("el_RecordNumber") = objEvent.RecordNumber
objRS("el_SourceName") = objEvent.SourceName
objRS("el_TimeGenerated") = objEvent.TimeGenerated
objRS("el_TimeWritten") = objEvent.TimeWritten
objRS("el_Type") = objEvent.Type
objRS("el_User") = objEvent.User
objRS.Update
Next
objRS.Close
objConn.Close
5

How-to : Setup AlwaysOn Availability Group on SQL Server 2012

-

SQL Server 2012 has very nice HADR technology built-in, AlwaysOn Availability Groups. It allows to have multiple replicas of selected databases across multiple servers, which can be even located across multiple sites. AlwaysOn itself provides mechanism to keep all replicas of database synchronized and up-to-date, as some of them might serve data for read-only purposes (for example reporting).

MSDN article: AlwaysOn Availability Groups (SQL Server) describes AlwaysOn technology in details. In this article I will focus on practical side of this HADR technology and how to get this up and running fast without any issues.

To test and demonstrate SQL Server 2012 AlwaysOn capabilities I built simple Lab environment on VMware Workstation as shown below. Lab contains 2 servers with Windows Server 2012 R2 Datacenter and SQL Server 2012 Enterprise.

Lab environment also contains Active Directory domain and Domain Controller, which is not shown on the diagram.

In order to get SQL Server AlwaysOn up and running we will complete following activities:

  • Install Failover Clustering Role
  • Configure Windows Server Failover Clustering
  • SQL Server installation
  • Demo database preparation
  • Enable Availability Groups on SQL Server
  • Creating AlwaysOn Availability Group

So, let’s get started…

(more…)

0

Code snippets : Execute SQL script using PowerShell

-

Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

  • SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
  • SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
  • SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

(more…)

5

How-to : Install Bareos with WebUI on openSUSE 13.2

-

Recently I went through installation process of Bareos on openSUSE. Bareos seems to be interesting solution for backups in heterogeneous  environments.

Ok,let’s start and see how to deploy in 5 steps:

  • Step 1: Add repository with Beros and Beros WebUI; Install Beros
  • Step 2: Create Bareos database and Tables on MySQL
  • Step 3: Configuring Bareos (few config files)
  • Step 4: Starting services and accessing WebUI portal
  • Step 5: Enable/Autostart Bareos services (optional)

(more…)

0

Tips & Tricks : Missing SQL Server assmeblies in PowerShell

-

When you try to connect to SQL Server from PowerShell script and you get following message:


Unable to find type [Microsoft.SqlServer.Management.Smo.Server]

then you have to download and install following components:

  • Microsoft System CLR Types for SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Shared Management Objects
  • Microsoft Windows PowerShell Extensions for SQL Server 2008 R2

From here.

Page 1 of 3123