transaction-isolation-levels

Understanding Isolation Levels in Galera Cluster

In an earlier article we have gone through the process of Certification Based Replication in Galera Cluster for MySQL and MariaDB. To recall the overview, Galera Cluster is a multi-master cluster setup that uses the Galera Replication Plugin for synchronous and certification based database replication. This article examines the concept of Isolation Levels in Galera Cluster, that is an inherent feature of the MySQL InnoDB database engine.

Read more
database-monitoring

Monitoring Your MySQL Database Using mysqladmin

As with any other dynamic running process, the MySQL database also needs sustained management and administration of which monitoring is an important part. Database monitoring allows you to find out mis-configurations, under or over resource allocation, inefficient schema design, security issues, poor performance that include slowness, connection aborts, process hanging, partial resultsets etc. It is giving you an opportunity to fine tune your platform server, database server, its configuration and other performance factors. This article provides you an insight into monitoring your MySQL database through tracking and analyzing important database metrics, variables and logs using mysqladmin.

Read more

galera-cluster

Working of Certification based Replication in Galera Cluster

In the earlier articles, we have covered the basics of Galera Cluster for MySQL and MariaDB and another article about MariaDB Galera Cluster in particular. To recall the overview of Galera Cluster – It is a synchronous multi-master cluster that uses the InnoDB storage engine (XtraDB also for MariaDB). It is actually the Galera replication plugin that extends the wsrep API of the underlying DBMS, MySQL or MariaDB. Galera Cluster uses Certification based synchronous replication in the multi master server setup. In this article we will look into the technical aspects of Galera Cluster Certification based replication functionality.
Read more

Is Cloud Database Good for Your Business?

You have heard lot about Cloud now-a-days – cloud computing, cloud storage, cloud database, etc. but have you thought about how cloud technology can influence your business, the positive ones and negative sides? Also it is worth discussing how you can utilize cloud for the development of your business, overcoming any disadvantages associated with it. This article is trying to define what cloud database implies for your business and its advantages and disadvantages. Also it is trying to provoke discussions about the suitability of cloud database for you.

Read more

aws-rds-ec2

Choosing between AWS RDS MySQL and Self Managed MySQL EC2

The Amazon Web Service (AWS) products AWS EC2 and AWS RDS comes with sophisticated technology features and capabilities that serve the needs for Web based computing and storage efficiently. When coming to the decision making process for selecting between AWS RDS and the self managed AWS ECS or any independent MySQL DBaaS for your MySQL database needs, there are some prominent factors to consider. These factors are crucial and affect the operational and economic aspects of your database deployment. This article provides a comprehensive overview of both products to help you understand the distinction between them for making a suitable and cost-effective decision.
AWS EC2
Amazon Elastic Compute Cloud (Amazon EC2) is a web service that provides scalable cloud computing facilities. You can select either a pre-configured instance optimized for your application needs or first deploy an instance and configure it through provisioning and scale up/down facilities. The EC2 packages are organized according to the level of optimization and resource allocation done in terms of CPUs, Memory, Storage, etc.
Its advantages are:
1. Pay per provisioning.
2. Quick scale up/down capabilities.
3. Self managed with root access and allows configuration of CPU, memory, storage etc.
4. Rapid commissioning of multiple server instances.
5. Choice to select instance types and OS (Linux and Windows).
6. Availability of pre-configured instances.
7. Flexibility to choose instance location.
8. Elastic IP addresses.
AWS RDS
Amazon Relational Database Service (RDS) is a managed web service that assists in setting up a relational database in cloud. Its advantages are:
1. Resource scaling.
2. Automated database administration for hardware provisioning, database setup, patching, upgrades, backups, etc.
3. Automatic failure detection and recovery.
4. Flexibility to schedule and perform backups and restore operations.
6. Read scaling through Read Replicas.
7. Availability of AWS IWM (Identity and Access Management) and network isolation through Amazon Virtual Private Cloud (VPC) to enhance security.
8. On-demand pricing.
9. High availability with provisioning of Multi-AZ DB instance deployments with synchronized standby replicas on different Availability Zones for automatic failover and recovery.
Amazon RDS is implemented by using DB instances – an isolated database environment in the cloud and can contain multiple databases. A DB instance can be created and modified by using the AWS CLI, the AWS API or the AWS management console. Currently the Amazon RDS supports the DB engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle and SQL Server.
Amazon MySQL RDS
It is the MySQL based DB instance deployments in the cloud. As like other types of DB engines, MySQL RDS also offers advantages like high availability, automated DB administration, backup and recovery, provisioned storage and IOPS with scaling, read replicas on different AWS regions for load balancing and automatic failover, isolation and security, Multi-AZ instances with synchronous standby replicas on separate Availability Zones for failover and backups, etc.
Limitations with MySQL RDS
1. Storage is charged per provisioning, not according to consumption. Once storage is allocated, it cannot be returned without rebuilding the instance from scratch or a logical backup restore.
2. Access to the Operating System and Shell Access is denied. So tasks that need to be run from an OS access cannot not be done.
3. MySQL user accounts with SUPER privilege are not allowed. Operations that need this privilege like setting of some system variables, replication configuration and operations, binary log management, InnoDB key rotation etc. cannot be performed. These can only be achieved through RDS stored routines and RDS parameter groups.
4. There is no slow query log, instead need to use RDS provided mechanism for logging slow queries.
5. There is no multi-master replication in RDS. For Single Zone instances there is one master and multiple read replicas with asynchronous replication and no failover. This means that the replication is not fault proof and may cause data loss. Multi-AZ instances come with additional cost and have a primary instance and standby secondary instance at another zone. Though they use synchronous replication, the secondary database is in-active and couldn’t be used until the failover. Then the secondary becomes the primary.
6. Scale up or down is automatic but have downtime due to switchover time.
7. Replicating from AWS RDS to another cloud provider is not supported.
8. Does not support all available MySQL replication options like multi-master, multi-source, tree replication etc.
Cost Comparison 
Base Cost calculation for the m4.large setup (2 X vCPU, 8 GB Memory) as on September 8, 2017 for 1 month of usage for EC2, Single Zone RDS and Multi-AZ RDS
EC2 m4.large
$0.1 per Hour X 24 hours X 30 days = $72
Single Zone RDS db.m4.large
$0.175 per Hour X 24 hours X 30 days = $126
Multi-AZ RDS (Multi Zone) db.m4.large
$0.350 per Hour X 24 hours X 30 days = $252
We can see that base monthly cost (without adding backup storage and bandwidth) for RDS is almost double with EC2 for the same configuration. When it comes to Multi-AZ RDS, it becomes double than Single Zone RDS.
 
The Conclusion
AWS RDS is a fully managed solution and you have little to do in terms of frequent and daily DBA tasks. So you are free to focus on the application side and its connectivity and interface to the already running database. It is a good choice for critical business implementations where little customizations are needed and scalability is the main constraint.
AWS EC2 on the other side demands good effort in configuring and managing your DB deployment. But the flexibility is that, if you have reasonable expertise with DBA tasks, you can implement a tailored DB setup with your choice of configuration, optimization and other enhancements like specialized scripts for carrying out custom tasks etc. Along with the cost effectiveness, EC2 is suitable if you need multiple RDS instances or your MySQL server has a variety of plugins needed. So you can go for EC2, if you need to have the flexibility of setting up an adaptive DB implementation that scale and evolve with your applications.
mariadb-galera-cluster

MariaDB Galera Cluster

We have covered the basics about Galera Cluster in a previous article – Galera Cluster for MySQL and MariaDB. This article further goes into the Galera technology and discusses topics like:
1. Who provides Galera Cluster?
2. What is MariaDB Galera Cluster?
3. An overview of MariaDB Galera Cluster Setup.
Galera Cluster is a synchronous multi-master cluster that uses the InnoDB storage engine. In MariaDB, it supports the XtraDB and InnoDB storage engines. It is actually the Galera replication plugin that extends the wsrep API of the underlying DBMS. Galera Cluster uses Certification based synchronous replication in the multi master server setup. This replication eliminates many issues faced by asynchronous replication based clusters, like write conflicts, replication lag between cluster nodes, slaves going out of sync with masters, single point of failure, etc. Certification based replication utilizes group communication and transaction ordering techniques. Changes at one node are grouped as a write-set upon COMMIT and this write-set is broadcasted to other nodes. Each node including the source node performs a certification test to decide whether the write-set can be applied or not. Only if the certification test passes, the write-set is applied as a transaction and COMMITTED on the nodes; otherwise a roll-back is performed, thus discarding the changes. The certification test is based on Global Ordering of transactions in which each transaction is assigned a global transaction id. During COMMIT time the last transaction is checked with previous transactions to detect any primary key conflicts and if conflict detected, certification test is failed. If test passes, all nodes receive transactions in the same global order.
Galera Cluster Plugin is an open source patch for MySQL developed by Codership. It is available at Codership as 2 software packages – the Galera replication library and original MySQL version extended with the Write Set Replication (wsrep) API implementation (mysql-wsrep). Including this Codership product, there are 3 Galera variants:
1. MySQL Galera Cluster by Codership
2. MariaDB Galera Cluster by MariaDB
3. Percona XtraDB Cluster for MySQL by Percona that integrates the Percona Server and Percona XtraBackup with Codership Galera library.
The MariaDB Galera Cluster
Starting with MariaDB 10.1, the wsrep API for Galera Cluster is included in the original MariaDB package. MariaDB Galera Cluster uses the Codership Galera Library and mysql-wsrep to implement its Cluster. MariaDB is available for the major Linux distributions like openSUSE, Arch Linux, Fedora, CentOS, RedHat, Mint, Ubuntu, Debian, etc. As in any Galera Cluster implementation, MariaDB Galera Cluster is also implemented using the below components:
1. DBMS – here it is MariaDB Server.
2. wsrep API – defines and implements the interface and responsibilities for the DBMS Server and replication provider.
3. wsrep hooks – the wsrep integration of the wsrep API, inside the DBMS engine.
4. Galera plugin – implements the wsrep API for Galera Library to provide the write-set replication functionality.
5. Certification layer – prepare write-sets and performs certification.
6. Replication layer – manages the entire replication and provides total ordering capabilities.
7. GCS framework – provides plugin architecture for various group communication plugins for the Galera Cluster.
Features of the MariaDB Galera Cluster
As like any other Galera Clusters, MariaDB Galera Cluster also has the following features:
1. Certification based synchronous replication.
2. Multi-master topology.
3. Clients can read/write to any node.
4. Cluster membership control and dropping of failed nodes from the cluster with re-join upon recovery.
5. Automatic joining of nodes to running cluster.
6. Row level parallel replication.
7. Direct client connections through MariaDB interface.
Downloading and Installing MariaDB Galera Cluster
It can be downloaded and installed using Yum or Apt. The Galera package is included by default in the MariaDB packages. On Ubuntu, following will install the MariaDB Server with Galera Plugin:
sudo apt-get update
sudo apt-get install mariadb-server
Apart from the memory needed for MariaDB Server, additional memory is needed for the certification index and uncommitted writesets. There is a process called Writeset caching that takes place when a node could not process an incoming writeset. This typically occurs when the node is undergoing a state change operation like WRITE or dumping through utilities like mysqldump. Correspondingly the source node also could not apply the writeset at the target node in this scenario. Then the pending writeset will be cached in memory for a catch-up phase. The reading of writeset from in-memory cache and committing it should happen normally but if the system runs out of memory, the state transfer of the nodes will fail or the cluster will block waiting for the state transfer to end. The following Galera parameters are to be configured for writeset caching:
gcs.recv_q_hard_limit : it is the maximum allowed size of the recv queue (committed writesets to be replicated).It should normally has a value of RAM + SWAP. If this limit is exceeded, the Galera Cluster will abort the server.
gcs.recv_q_soft_limit : It is the fraction (in decimal form) of gcs.recv_q_hard_limit after which replication rate will be throttled to avoid a memory shortage and server abort. Its default value is 0.25.
gcs.max_throttle : It is a fraction (in decimal form) that specifies how much to throttle replication rate during state transfer so that running out of memory can be avoided. Its default value is 0.25.
The following server requirements are applicable to a MariaDB Galera Cluster:
1. General log and slow query log need to be file type.
2. For versions before 5.5.40-galera and 10.0.14-galera, query cache has to be disabled.
Running MariaDB Galera Cluster
A new cluster needs to be bootstrapped in the server indicating to the server that there is no existing cluster running on the server. For this invoke mysqld with the option –wsrep-new-cluster.
$ mysqld –wsrep-new-cluster
For systems using SysV init scripts:
$ service mysql bootstrap
To add a new node to a running cluster:
$ mysqld –wsrep_cluster_address=gcomm://192.168.0.1
The IP address can be replaced by DNS name also. Once added, the new node need to connect to any existing node to automatically retrieve the cluster map and reconnect to the rest of the nodes.
MariaDB Galera Cluster Settings
Following mandatory settings are applicable to the cluster. They are to be set either in the MariaDB Server configuration file or as command line option (given in brackets).
wsrep_provider : file location of the wsrep library. (–wsrep-provider=value)
wsrep_cluster_address : IP/DNS address of cluster nodes to connect to when starting up. Eg: gcomm://192.168.0.1:1234?gmcast.listen_addr=0.0.0.0:2345. It is also possible to specify addresses of all nodes – gcomm://<node1 or ip:port>,<node2 or ip2:port>,<node3 or ip3:port> (–wsrep-cluster-address=value)
binlog_format : specifies the binary log format that decides whether the replication is row-based, statement-based or mixed. Valid values are ROW, STATEMENT or MIXED. (–binlog-format=format)
default_storage_engine : to be enabled at server startup for server to start. Default value is InnoDB. (–default-storage-engine=name)
innodb_autoinc_lock_mode : A numeric value from 0 to 2, that specifies the locking mode to be used for generating auto increment values in the tables. Default values – 2 for >=MariaDB 10.2.4 and 1 for MariaDB 10.2.3. (–innodb-autoinc-lock-mode=#)
0 – Traditional lock mode that holds a table-level lock for all INSERTS until the auto increment value is generated.
1 – Consecutive lock mode that holds a table-level lock for all bulk INSERTS. For simple INSERTS no lock is used, instead a lightweight mutex is used (mutual exclusion object that facilitates multiple threads to access a resource).
2 – Interleaved lock mode that does not locks tables. Though most fastest and scalable mode, this is not suitable for statement-based replication.
innodb_doublewrite : A Boolean default value of 1 (ON) specifies InnoDB to first store data in a doublewrite buffer before writing to the data file. Disabling this has a marginal improvement in performance. (–innodb-doublewrite)
query_cache_size : the query cache size in bytes. By default it is 1M (–query-cache-size=#)
wsrep_on : To enable wsrep replication. It has a default Boolean value OFF and needs to be turned ON for transactions to be applied to nodes in the cluster. However, it will not affect setting up the cluster and membership of the nodes with default value OFF. For Galera replication ON is needed. (–wsrep-on[={0|1}])
The Galera STATUS VARIABLES can be queried with the statement:
SHOW STATUS LIKE ‘wsrep_%’;
mysql-cluster-security

Securing MySQL NDB Cluster

As discussed in a previous article about MySQL NDB Cluster, it is a database cluster setup based on the shared-nothing clustering and database sharding architecture. It is built on the NDB or NDBCLUSTER storage engine for MySQL DBMS. Also the 2nd article about MySQL NDB Cluster Installation outlined installation of the cluster through setting up its nodes – SQL node, data nodes and management node. This article discusses how to secure your MySQL NDB Cluster through the below perspectives:
1. Network security
2. Privilege system
3. Standard security procedures for the NDB cluster.
Detailed information is available at MySQL Development Documentation about NDB Cluster Security.
NDB Cluster Network Security
Cluster communication can be divided into client to cluster and cluster node to node categories. Node to node (between SQL, Data and Management nodes) communication are not recommended to be through SSL or using any other encryption protocols. This is because of the extra overhead due to network lag and latency that can affect the cluster performance. A network related security concern that needs to be addressed for the NDB Cluster is specified below.
For the config.ini file of an NDB Cluster, if the HostName parameter under [mysqld el_id=’59ad103a65a56′] is blank or absent, or if there are empty slots under the [mysqld el_id=’59ad103a65a93′] section, then the management node will not check about from which host, an SQL/API node or MySQL Server is attempting a connection to it. In such a case an SQl/API node can connect to the management node and access its data. To prevent this, specify a HostName parameter for all [mysqld el_id=’59ad103a65acc’] and [api] sections in the config.ini file. Additionally, in such a scenario and management node client (ndb_mgm client) that knows the management node’s host address can connect to it and execute administrative commands to control the cluster.
The above issue can be resolved by many ways. One is by isolating cluster node network from external public network from which connections to the cluster is initiated. This can be done by placing a hardware or network firewall between the public network and SQL nodes.
Though costly, this setup is dependable and fault proof. It does not allow any access to data and management nodes from outside or any communication from the data and management nodes to the public network. Obviously the SQL nodes need to be secured against hacking.
Another option is to use software firewalls on all host nodes and configure them to block data packets that need not come or go outside the cluster.
The firewall on the data and management nodes hosts allow connection from SQL nodes only. Outside applications or clients can only communicate with cluster through SQL nodes. Each host machine will allow and deny traffic as specified below:
SQL node: Allows traffic from management and data nodes from within the cluster network that’s IP address is allowed, allows traffic from within the network through the assigned port and denies all other traffic.
Management and Data nodes: Allows traffic from management and data nodes, allows traffic from SQL nodes through the allowed IP address and denies all other traffic.
A combination of hardware and software firewall (network and host based) also can be used to secure the cluster.
This setup keeps the cluster behind the network firewall, but allows traffic between the management/data nodes and SQL nodes.
MySQL Privileges and NDB Cluster Security
Like any other MySQL storage engines, user privileges information for NDB storage engine tables are stored in mysql system database. Since MySQL grant tables use the MyISAM storage engine, those tables are not duplicated or shared across the SQL nodes in the NDB cluster. So changes in users and privileges made in one SQL node are not propagated to other SQL nodes by default. To enable this propagation, or distributed privileges across all SQL nodes, following steps need to be implemented:
On each of the SQL node MySQL server, import the script share/ ndb_dist_priv.sql available inside the MySQL installation directory.
shell> mysql options -uroot < share/ndb_dist_priv.sql
This script will create a number of stored routines on the SQL node. To verify this, use the below query to get the output as shown:
mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE
    -> FROM INFORMATION_SCHEMA.ROUTINES
    -> WHERE ROUTINE_NAME LIKE ‘mysql_cluster%’
    -> ORDER BY ROUTINE_TYPE;
+———————————————+—————-+————–+
| ROUTINE_NAME | ROUTINE_SCHEMA | ROUTINE_TYPE |
+———————————————+—————-+————–+
| mysql_cluster_privileges_are_distributed | mysql | FUNCTION |
| mysql_cluster_backup_privileges | mysql | PROCEDURE |
| mysql_cluster_move_grant_tables | mysql | PROCEDURE |
| mysql_cluster_move_privileges | mysql | PROCEDURE |
| mysql_cluster_restore_local_privileges | mysql | PROCEDURE |
| mysql_cluster_restore_privileges | mysql | PROCEDURE |
| mysql_cluster_restore_privileges_from_local | mysql | PROCEDURE |
+———————————————+—————-+————–+
7 rows in set (0.01 sec)
The stored procedure mysql_cluster_move_privileges creates 2 sets of copies of the mysql database, one as MyISAM and other as NDBCLUSTER storage engine. Then the script invokes mysql_cluster_move_grant_tables that contain ALTER TABLE statements to convert the mysql system tables to NDB storage engine.
Once this conversion to distributed privileges is made at each SQL nodes, any change in users or privileges at any one SQL node will be distributed to all SQL nodes in the cluster.
As stated above in Cluster Network Security, it is important to secure the users in any SQL Node, so that a compromised node is not used to access and corrupt data in other SQL nodes. So ensure that the config.ini does not contain an empty [mysqld] block or use the HostName parameter. Also a firewall based security system is necessary to segregate the cluster from public network to ensure proper security.
Standard Security Procedures for NDB Cluster
The security procedures applicable to a standard MySQL Server is also valid for a MySQL NDB Cluster.
1. Always run the MySQL server (mysqld) as the mysql system user, that is part of the mysql user group.
2. Ensure that the system’s MySQL data directory is owned by the mysql user.
3. Ensure that the my.cnf of each SQL node contains user=mysql in the [mysqld] section.
4. Never run mysqld as the system root user.
5. Delete the anonymous user account created during installation.
6. Always keep in mind that the different nodes inside the cluster have high mutual transparency and hence it is highly recommended to secure the cluster through tight user privilege management and network security.
database-versioning

Why Your Business Need Database Versioning?

How database versioning boosts your business growth?
Applications are neither ideal nor perfect. Along with the dynamic implementation environments and rapid technology changes, certain features can become obsolete and some new ones might be needed. Workflows and processes in the application need to be changed. Bugs and vulnerabilities can be reported any time. Competitors may release a new feature that demands you to reciprocate with matching or better feature. The chances are huge for you to initiate, perform and deliver a stable minor or major version. A delay or absence in this can negatively affect the reputation and/or monetary attributes like market performance, profit etc. In these circumstances, it is essential for you to make available for your teams, tools that automates and simplifies processes as much as possible. Applications or services have code and databases are integral parts of any product. A properly version controlled database boosts and simplifies development and production deployment processes. It also helps in critical analysis, comparison and cross review of your application sets. The results give you new insights and directions for a new promising version release and you can focus on your business growth, since everything is automated.
Advantages of general version control
Version Control (VC) is important to organizations due to the following factors:
1. It allows tracking changes and modifications.
2. It allows comparison of any 2 versions and to roll back to a previous version if something went wrong in the current version.
3. Multiple teams can share a single repository with their own branches (that represent a different functionality or feature) and can pull from the main branch or root and can push into the main branch to merge their work with that of other teams’ branches.
4. Interim releases or minor versions and major version releases can be effectively performed.
5. Provides an automated and robust workflow for fixing errors or bugs in the form of patches (a new branch that fixes the bug) and hotfixes that addresses customer’s revised needs.
6. Enables organizations to adopt agile development and delivery in which new features can be added as per market needs efficiently.
Why businesses need database versioning – technology and process perspective.
While the above advantages are clear and a necessity for application codebase, when it comes to databases where the applications work with real data and data structures or schemas, version control achieves more significance. This can be attributed to many reasons, some of which are:
1. Technology, business environment and customer requirement changes pushes businesses to alter and adapt their database to best suite the necessity of the current time. If this is ignored, businesses suffer setbacks like losses in market share, decrease in product popularity and reduced relevance of the product due to lack of required features etc.
2. Even if application codebase utilizes version control to implement agile delivery models that enable the product to be in sync with market needs through minor and major version releases and patches, code developers at some point of time get stuck due to the complexity of database modifications that occurred in tandem with feature or code changes. At certain levels of branch merges, a mismatch or disparity in database related code or the database itself can break the entire merging process.
3. Database changes (structure, metadata and data) made by one team or branch, need to fully managed and tracked to aid error-free merging for minor and major releases. If database versioning is not implemented along with code versioning, some branches will not merge properly resulting in bugs and errors.
4. It might need to go back for a previous version of code at some point of time. This rollback requires the database at that time so that rolled back version is bug-free. Also there can be occasions when you need to audit your database versions to identify the best performed one. Database versions along with corresponding code versions enable auditing and continuous delivery patterns.
What to be brought under database versioning?
The database schema, metadata and data itself are the main elements that need to be version controlled. While it is not logical or recommended to version the database source code or binary, the above mentioned elements fit perfectly as candidates for version control in an agile, continuous integration and delivery scenario. All 3 elements can be represented as SQL files and it becomes as easy as file versioning with SQL files and other needed log files only to be put under version control. Another prominent element that needs versioning is backups. These will be compressed SQL files and will be represented as physical or logical backups. They also can be incremental backups where the binary log implements some kind of version control without much automation.
Implementing database versioning
A best practice to make your database fit for version control is using scripts, preferably SQL or otherwise any programming language of your choice to create and modify your database schema. There will be an initial script that creates the first version and further changes in schema, metadata and data are to be done through scripts only. Multiple teams in a project working on different branches of the application can maintain their own database instances and the versions of scripts that represent various points of branches (pulls and pushes) are stored along with the corresponding code. Some kind of automation script needs to work on the team’s database instance to run the current version of the database scripts to bring the database to the state of code at that level. This ensures that during a merge process, the database is updated automatically to reflect the current state of the application.
Incremental versioning of the database scripts is a simple and straightforward method to track changes in incremental versions of SQL scripts and logs. During the merge all increments are applied to the database to bring it to the current state. The extra overhead here is the need of manual actions from developers or DBA to run the incremental scripts during merges.
 
Who is responsible – DBAs or Developers?
Once an application is deployed, the DBA takes control of the database administration. While developers continue to work on the application codebase repository, modifying code and database, and finally do a merge for a release, DBA gets the new code with installation and upgrade script for database. In this model of continuous delivery, DBAs scope does not cover database version control but developers do. The exception is that DBA can monitor and audit database versions if he can be a part of database versioning. For this purpose, it is recommended to provide the DBA with their own test and QA database version repository. They can comfortably perform all the needed activities like benchmarking, analysis, optimization, auditing etc. and can find the best possible schema, structure and configuration. All these attributes and information can be conveyed to the developers as SQL scripts or as metadata or as plain text and developers can take this information as guidelines for next release. Like this, DBAs can be active contributors to the growth and development of entire application including the database.
Docker Enterprise Edition

Docker Releases Enterprise Edition with Multi-Architecture and Multi-Tenant Support

On August 16, 2017 Docker announced the new release of Docker Enterprise Edition (EE). Docker EE is a Container-as-a-Service (CAAS) platform that provides a cross-platform, cross-architecture supply chain and deployment environment for software applications and microservices built to work in Windows, Linux and Cloud environments. The Docker container platform is integrated to the platform infrastructure to provide a native and optimized experience for the applications. Docker EE has the Docker tested and certified infrastructure, containers and plugins needed to run applications on Enterprise Platforms. The new Docker EE provides a container management platform that accommodates Windows, Linux and Mainframe apps and supports a broad range of infrastructure types.
According to Banjot Chanana, Head of Product Management for Docker:
“With Docker EE, organizations can shift more investment into innovation by reducing the costs of maintaining their existing applications, while increasing flexibility and security. Docker EE enables organizations to unite traditional applications and microservices built on Windows, Linux or mainframe onto a single, secure software supply chain, accelerating application delivery by 13x while reducing IT spend more than 50 percent.”
The salient features of the new release of Docker EE are:
Multi-Architecture Orchestration for Applications
Docker claims to enable organizations to modernize Windows, Linux and Mainframe applications by managing all of them in the same cluser. The definite advantage of this is centralization of access controls, security and operational policies etc across teams, departments and business units.
Secured Multi-tenancy for Organizations to Define Their Own IT Services Model 
With these, organizations are offered more flexibility and options to customize their IT services model using the new role-based access models. This allows sharing, allocation and collaboration of nodes and resources according to the business requirements. So teams and projects can now logically and physically compartmentalized without disrupting the workflow, roles and processes.
 
Enhanced Security and Integrity through Policy based Automation
The flow of an application from development to production can be securely automated and accelerated using features like automated image promotion, immutable repositories to prevent version overrides during promotion to production etc. This allows teams to focus on development quality and speed while organizational compliance to standards and quality are automatically ensured and maintained.
 
Docker is changing conventions?
With the new features like multi-tenancy, Role Based access control, integrated management and end to end security model, Docker EE is changing the tedious and complex workflows and policies for organizations. The level of abstraction and functionality Docker is offering certainly will enable organizations to focus on modernizing their software engineering and operational paradigms. This will certainly be a shift from the rigid and over-surveillance process implementation to a more relaxed and growth oriented service delivery and deployment model.
mariadb-security

How to Secure your MariaDB Database Server

At the time of writing this article, the current MariaDB 10.2 offers MariaDB TX Subscription to access the feature MariaDB MaxScale. MariaDB TX subscription offers high-end functionality like implementing business-critical production, test and QA deployments, management, monitoring and recovery tools, mission-critical patching and above all, access to the MariaDB Database Proxy, MaxScale.
Following are the security related features of MariaDB MaxScale:
1. Database firewall filter.
2. Denial of Service protection.
3. Data Masking to protect sensitive data.
This article discusses steps to secure your MariaDB Server installation from the installation phase onwards.
Install MariaDB with mysql_secure_installation
mysql_secure_installation is an executable shell script available with MariaDB to install it with added security measures like setting password for root accounts, remove root accounts that are accessible from outside localhost, remove anonymous user accounts and remove test database (along with its privileges) that can be accessed by anonymous users.
shell> mysql_secure_installation
Below are the prompts being asked by the script:
Enter current password for root (enter for none):
OK, successfully used password, moving on…
Change the root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
Avoid running mysqld as Linux root user
If the mysqld user has Linux System user “root” access and MariaDB FILE privilege, then he can modify any files in the system. To prevent this, always create or use the included user mysql to run the mysqld process. For this start mysqld with mysql as user.
mysql> mysqld_safe –user=mysql
Below is a list of user management guidelines for a secure MariaDB implementation:
1. Grant root access only to local clients. Example: apache server installed on the same machine or restrict host name to specific host only. ‘root’ @ ‘192.250.200.25’ – specifies access to user root from the host 192.250.200.25 only.
2. Use strong passwords for users.
3. Have separate users for each database.
4. Restrict the hosts or IPs that can access the database server.
5. Grant only required privileges to client users. Administrative privileges like GRANT etc. are not needed for client users.
Password Check and Validation
The Password Validation Plugin API in MariaDB allows for ensuring that user passwords meet at least minimum security requirements. There are 2 plugins – simple_password_check plugin and cracklib_password_check plugin that use this API. Since they are not enabled by default install them manually. Atleast one plugin needs to be enabled to implement password validation checks. If multiple plugins are loaded, all validation checks must be passed for setting or changing passwords.
To install simple_password_check plugin:
INSTALL SONAME ‘simple_password_check’;
OR
INSTALL PLUGIN ‘simple_password_check’;
The default values for the options for this plugin enforce the below password validation rules. Password should have:
1. At least 8 character in length.
2. Requires at least one digit.
3. Requires at least one uppercase and one lowercase letter.
4. Requires at least one symbol or special character.
The configuration options that can change the validation requirements are set either through SQL SET GLOBAL statements or through my.cnf/my.ini. Various options are:
–simple-password-check-digits=# minimum number of digits needed in password (range: 0 to 1000, default = 1)
–simple-password-check-letters-same-case=# needs this many upper and lower case letters (range: 0 to 1000, default = 1)
–simple-password-check-minimal-length=# minimum password length (range: 0 to 1000, default = 8)
–simple-password-check-other-characters=# needs this many special characters or symbols (range: 0 to 1000, default = 1)
Examples:
SET GLOBAL simple_password_check_minimal_length = 10;
OR
[mysqld]
simple-password-check-minimal-length = 10
If password is directly set by hash, then these validations are bypassed. In order to avoid this, enable the strict_password_validation variable (along with validation plugin) that reject passwords that cannot be validated (hash passwords).
SET GLOBAL strict_password_validation = 1;
OR
[mysqld]
strict-password-validation = 1
The SET DEFAULT ROLE statement uses MariaDB’s User Roles feature to assign a default set of packaged privileges as roles to new users.
SET DEFAULT ROLE new_user;
To remove the Default Role from the current user:
SET DEFAULT ROLE NONE;
Use Secure Connections to transfer data
The Transport Layer Security (TLS) can be used to encrypt data during transfer. In MariaDB TLS support is pre-compiled, but disabled by default. To enable it, start the server with –ssl option. Configure the server for encrypted connections by adding the Certificate Authority (CA) certificate, Server Public Key Certificate and the Server Private Key in my.cnf.
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
Use the file names as applicable.
For client side configuration, use CA certificate, Client Public Key certificate and Client Private Key. Invoke the client like:
mysql –ssl-ca=ca.pem
Encrypt Tables and Data
Table encryption ensures that even if someone intrudes into database or access data, the data is protected due to encryption and is immune to tampering. In MariaDB the storage engines XtraDB, InnoDB and Aria supports encryption with Aria needing tables to be created with the default option ROW_FORMAT=PAGE. All tablespaces, individual tables and log files can be encrypted. Only data that are at rest in the database are encrypted and transferring data sent to the client are not encrypted. For that, secure connections using TLS protocol needs to be used.
The limitations are:
1. Disk based Galera gcache is not encrypted.
2. The Audit Plugin cannot create encrypted output. Instead the output needs to be sent to syslog for configuring protection.
3. File based general query and slow query logs cannot be encrypted.
4. Aria log is not encrypted.
5. Error log is not encrypted since it contains query text, data, crashes, assertion failures etc. It can also be protected through syslog.
For table encryption, use an encryption plugin like file_key_management plugin and configure storage engines to use it. This plugin has the following components to be configured:
1. file_key_management_filename : file that stores the encryption keys and plugin read the keys from this file.
2.file_key_management_filekey : an optional key to decrypt the key file. The key can also be a filename that contains the key preceded by the string FILE (eg: FILE:/path/to/filekey).
3. file_key_management_encryption_algorithm : the encryption algorithm
An example configuration in my.cnf or my.ini:
[mysqld]
file_key_management_encryption_algorithm=aes_cbc
file_key_management_filename = /home/mdb/keys.enc
file_key_management_filekey = secret
To encrypt XtraDB and InnoDB tables, use the below configuration (values are examples) in my.cnf or my.ini:
[mysqld]
plugin-load-add=file_key_management
file-key-management
file-key-management-filename = /mount/usb1/keys.txt
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4
Then use CREATE TABLE or ALTER TABLE queries with the below options:
ENCRYPTED : YES/NO (YES)
ENCRYPTION_KEY_ID : Positive Integer
Example:
CREATE TABLE T (id int, value varchar(255)) ENCRYPTED=YES ENCRYPTION_KEY_ID=17;
ALTER TABLE T ENCRYPTED=YES ENCRYPTION_KEY_ID=18;
To disable encryption and decrypt the table:
ALTER TABLE T encrypted=NO;
Aria tables can be encrypted by the below configuration in my.cnf/my.ini:
[mysqld]
aria-encrypt-tables=1
To encrypt temporary tables in disk created during query execution, set the below parameter in my.cnf/my.ini:
[mysqld]
encrypt-tmp-disk-tables=1
To encrypt temporary files created on disk during binary log transaction caching, filesorts etc:
[mysqld]
encrypt-tmp-files=1
To encrypt binary logs and relay logs:
[mysqld]
encrypt-binlog=1
Setup Auditing and Logging
The MariaDB Audit Plugin logs lot of security and performance related relevant information like incoming connections, executed queries, accesses to individual tables and information about users who read/write at what time to the database. The logs are entered on a file or syslog. This information helps us to detect suspicious activities and to investigate security breaches.
Enable logging by setting server_audit_logging to ON.
SET GLOBAL server_audit_logging=on
The events that can be logged are grouped as CONNECT, QUERY and TABLE events. To log these events, set the server_audit_events variable:
SET GLOBAL server_audit_events = ‘CONNECT,QUERY,TABLE’;
OR
[mysqld]
server_audit_events=connect,query
User activities are also logged since the activities belong to any one of the types connect, query or table. You can include or exclude users whose events are to be logged using the server_audit_incl_users variable.
To add a user without removing previous usernames:
SET GLOBAL server_audit_incl_users = CONCAT(@@global.server_audit_incl_users, ‘,maria’);
To specifically include or exclude users:
server_audit_incl_users=robert
server_audit_excl_users=sam,rocky