Planet MySQL

MySQL Enterprise backup : How to setup a slave for a standalone server with zero downtime?

 

 

It’s a well know fact that we could take backups with almost zero downtime with the “mysqldump” tool provided with the community edition of MySQL with a magical option --single-transaction on one  condition that all the tables are InnoDB. Myisam tables locked even with single transaction option.

 

Now let us talk about the MySQL Enterprise backup tool that is only available with the Enterprise edition. We are also offered a 30 day trial. It has been said that MEB(let us just call the MySQL Enterprise backup, MEB)  provides hot online backups with zero downtime.

 

So a hot backup is done online and doesn’t disrupt the normal operations of the database and it even captures the database operations happening during the backup process and applies it during the restoration process. This is applicable only to InnoDB tables.

 

When it comes to other engine tables like Myisam, CSV it has to read lock the tables..

So this is pretty much similar to mysqldump with --single-transaction in terms to locking and disruption to database operations, ain’t it?  But mysqldump produces a sql file whereas MEB produces and backup of the data directory.

 

Wait.., This is not the only difference. There are several other features available in MEB like parallelism, incremental backup, differential backup,backup compression backup encryption etc.., Let’s not get into those now.

Let’s first concentrate on creating a slave from a standalone server with very minimal disruption to database in the most simple way possible using MEB.

 

I have a MySQL community edition installed in port 3306 of my test server. I have downloaded the trial version of MEB from the below link

https://www.mysql.com/trials/

There are several packages available. Let's choose the generic linux version.

How to install MEB ?

 

The downloaded package in zip format

Unzip the file and you would receive below files

 

meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz.asc meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz.md5

 The .tar.gz file is the one. 

Arav $] tar  -xzvf meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz Arav $] cd meb-3.12.3-linux-glibc2.5-x86-64bit Arav $] cd /bin/ Arav $] ls -ltrh -rwxr-xr-x. 1 7161 31415 8.0M May  5  2016 mysqlbackup  

 That’s it we have just installed MEB. So easy isn’t it.

Now steps to take backup. We are interested in taking the backup into a single image file

  

  Arav $] ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/aravinth/ent_backup/my_ba.mbi --backup-dir=/home/aravinth/ent_backup/ backup-to-image  

 

--backup-image= : location to place the backup image

--backup-dir= : stores temporary files created during backup. It also has the log file that stores the course of the backup process.

 The location would be /home/aravinth/ent_backup/meta/MEB_2017-05-17.12-22-52_image_backup.log.

This file also holds the binary log position to configure the slave.

 

To get the binary log position run the below command

  

 

Arav $] grep 'INFO: MySQL binlog position:'  /home/aravinth/ent_backup/meta/MEB_2017-05-17.12-22-52_image_backup.log

 

170517 12:40:27 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000436, position 55853225

 

Now that backup is complete and we have the exact binlog position, we can configure the slave.

 

Let's start by installing MySQL in the slave server. Login to the slave server and use the below commands to install MySQL

 

 

Arav $] wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Arav $] tar -xzvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Arav $] mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql

Arav $] cd /usr/local/mysql

Arav $] mkdir /mysqldata/

Arav $] chown -R mysql.mysql /mysqldata/

Arav $] cp support-files/mysql.server /etc/

Arav $] ./scripts/mysql_installdb --datadir=/mysqldata/ --user=mysql

Arav $]./bin/mysqld_safe --datadir=/mysqldata/ --user=mysql &

 

  Now shutdown the slave server and empty the data directory to start the restoration process. MEB needs a empty data directory for restoration process. You could also use --force option which replaces the files in data directory    

 

Arav $ ] ./bin/mysqladmin -uroot -p shutdown

Arav $] rm -rf /mysqldata/*

 

    Now run the below command in the master server to restore the backup in the empty data directory of the slave server.  

 

Arav $] ./mysqlbackup --user=root --password --port=3310 --host=192.168.1.2 --datadir=/home/aravinth/ent_test/mysql/data/ --backup-image=/home/aravinth/ent_backup/my_ba.mbi --backup-dir=/tmp/ent_backup/ copy-back-and-apply-log

 

 

--host=192.168.1.2 : the ip of the slave server

 

--datadir=/home/aravinth/ent_test/mysql/data/ : the data directory path of the slave server which is empty. If not empty it would through and error, we could also use force option to override it.

 

--backup-dir=/tmp/ent_backup/ : temporary directory to store temp location to store the temporary files created during the restore process

 

copy-back-and-apply-log : This is the option that tells MEB to restore the files to data directory and apply the changes that happened during the backup process

 

Wait for the restoration to complete. It took around 5 minutes to complete for 30G of data directory. Once the restoration process is complete start the mysql using mysqld_safe or "service start mysql" and configure the slave as shown below.

 

Create the replication user in master. Login to the master server and execute the below commands to create the replication user

Arav $]./bin/mysql -u root -p

mysql $] grant replication slave on *.* to repicator@192.168.1.2 identified by 'replica'

 

 

Use the below commands in slave server to configure the slave using the position that we got from the MEB log file. 

 

Arav $]./bin/mysql -u root -p

mysql $] change master to master_host='192.168.1.1',master_user='replicator',master_password='replica',master_log_file='mysql-bin.000436',master_log_pos=55853225;

mysql $] start slave;

 

 

Now run "Show slave status" check if the slave is running perfect. Slave should be behind by a few seconds depending on the hits in the master server. It should start catching up.

 

Once the slave has caught up with master it can be made availave as for read hits from application.

 

Now let's look at the backup and restoration speed

 

Backup Type

mysqldump

MEB

Data Directory Size

30G

30G

Backup Time

20 mins 40 secs

18 mins 20 secs

Restore Time

35 mins 11 secs

5 mins 41 secs

 

Restore time is very less in MEB since it involves only disk level operations. Yet it is a great improvement that too with no downtime.

Also I believe with compression enabled in MEB backup, the time taken would yet go down. Let’s explore that another day.

Percona Live Open Source Database Conference 2017 Slides and Videos Available

The slides and videos from the Percona Live Open Source Database Conference 2017 are available for viewing and download. The videos and slides cover the keynotes, breakout sessions and MySQL and MongoDB 101 sessions.

To view slides, go to the Percona Live agenda, and select the talk you want slides for from the schedule, and click through to the talk web page. The slides are available below the talk description. There is also a page with all the slides that is searchable by topic, talk title, speaker, company or keywords.

To view videos, go to the Percona Live 2017 video page. The available videos are searchable by topic, talk title, speaker, company or keywords.

There are a few slides and videos outstanding due to unforeseen circumstances. However, we will upload those as they become available.

Some examples of videos and slide decks from the Percona Live conference:

MongoDB 101: Efficient CRUD Queries in MongoDB
Adamo Tonete, Senior Technical Engineer, Percona
Video: https://www.percona.com/live/17/content/efficient-crud-queries-mongodb
Slides: https://www.percona.com/live/17/sessions/efficient-crud-queries-mongodb

MySQL 101: Choosing a MySQL High Availability Solution
Marcos Albe, Principal Technical Services Engineer, Percona
Video: https://www.percona.com/live/17/content/choosing-mysql-high-availability-solution
Slides: https://www.percona.com/live/17/sessions/choosing-mysql-high-availability-solution

Breakout Session: Using the MySQL Document Store
Mike Zinner, Sr. Software Development Director and Alfredo Kojima, Sr. Software Development Manager, Oracle
Video: https://www.percona.com/live/17/content/using-mysql-document-store
Slides: https://www.percona.com/live/17/sessions/using-mysql-document-store

Keynote: Continuent is Back! But What Does Continuent Do Anyway?
Eero Teerikorpi, Founder and CEO and MC Brown, VP Products, Continuent
Video: https://www.percona.com/live/17/content/continuent-back-what-does-continuent-do-anyway
Slides: https://www.percona.com/live/17/sessions/continuent-back-what-does-continuent-do-anyway

Please let us know if you have any issues. Enjoy the videos!

Percona Live Europe 2017: Dublin, Ireland!

This year’s Percona Live Europe will take place September 25th-27th, 2017, in Dublin, Ireland. Put it on your calendar now! Information on speakers, talks, sponsorship and registration will be available in the coming months.

We have developed multiple sponsorship options to allow participation at a level that best meets your partnering needs. Our goal is to create a significant opportunity for our partners to interact with Percona customers, other partners and community members. Sponsorship opportunities are available for Percona Live Europe 2017.

Download a prospectus here.

We look forward to seeing you there!

PHP-SQL-Parser updated again

I have bumped the minor version to 4.1.2 with this release which incorporates various pull requests from contributors. Of note is support for ALTER statements in PHPSQLCreator, which is the components of PHP-SQL-Parser responsible for turning a parse tree back into an executable SQL statement, basically an "unparser".

Command Line Aficionados: Introducing s9s for ClusterControl

Easily Integrate ClusterControl With Your Existing DevOps Tools via s9s - Our New Command Line Interface

We’ve heard your call (and, selfishly, our own): please meet s9s - the new command line interface (CLI) for ClusterControl, our all-inclusive open source database management system.

At every conference we’ve attended so far, visitors have been asking us whether there is a command line interface for ClusterControl. And, we’re not afraid to admit, some of us at Severalnines have always wanted to have one as well. So those same colleagues have gone and created the s9s CLI for ClusterControl, which we’re happy to present today.

In fact, Johan Andersson, our CTO, is one of our command line aficionados and he describes the new CLI as follows:

What’s the ClusterControl CLI all about?

The ClusterControl CLI, is an open source project and optional package introduced with ClusterControl version 1.4.1. It is a command line tool to interact, control and manage your entire database infrastructure using ClusterControl. The s9s command line project is open source and is located on GitHub.

The ClusterControl CLI opens a new door for cluster automation where you can easily integrate it with existing deployment automation tools like Ansible, Puppet, Chef or Salt. This allows you to easily integrate scripts from your orchestration tools inside the CLI.

Users who have downloaded ClusterControl can use the CLI for all the ClusterControl features while they’re on the Enterprise trial of ClusterControl. Community users can then use the deployment and monitoring functionalities of ClusterControl. Existing customers can use the CLI to the full extent of ClusterControl.

Usage and Installation

The CLI can be installed by adding the s9s tools repository and using a package manager, as well as be compiled from source. The current installation script to install ClusterControl, install-cc, will automatically install the command line client. The command line client can also be installed on another computer or workstation for remote management. Finally, the CLI requires ClusterControl 1.4.1 or later.

Moreover, all communication between the client and the controller is encrypted and secured using TLS.

The ClusterControl CLI allows you to deploy and manage open source databases and load balancers in a way that is fully integrated and aligned with the ClusterControl core and GUI.

The s9s command line project is open source and located on GitHub: https://github.com/severalnines/s9s-tools

For examples and additional information, e.g, how to setup users and authentication, please visit
https://severalnines.com/docs/components.html#clustercontrol-cli

Before you get started, you need to have ClusterControl version 1.4.1 or later installed, see https://severalnines.com/download-clustercontrol-database-management-system

Some of the things you can do from the CLI in ClusterControl
  • Deploy and manage database clusters
    • MySQL
    • PostgreSQL
    • MongoDB to be added soon
  • Monitor your databases
    • Status of nodes and clusters
    • Cluster properties can be extracted
    • Gives detailed enough information about your clusters
  • Manage your systems and integrate with DevOps tools
    • Create, stop or start clusters
    • Add, remove, or restart nodes in the cluster
    • Create database users (CREATE USER, GRANT privileges to user)
      • Users created in the CLI are traceable through the system
    • Create load balancers (HAProxy, ProxySQL)
    • Create and Restore backups
    • Use maintenance mode
    • Conduct configuration changes of db nodes
    • Integrate with existing deployment automation
      • Ansible, Puppet, Chef or Salt, ...

Actions you take from the CLI will be visible in the ClusterControl Web UI and vice versa.

How to contribute

The CLI project (aka s9s-tools) can be accessed via GitHub. We encourage users to contribute to the project by:

  • Trying out the CLI and give us feedback
  • Letting us know about missing features, wishes, or problems by opening issues on GitHub
  • Contributing patches to the project
Related resources  About the ClusterControl CLI  Introduction video  CLI on GitHub  Documentation To sum things up

The ClusterControl CLI and GUI are fully integrated and synced to allow you to utilize the CLI for deployment and management of your databases and load balancers, whilst using the advanced graphs in the GUI for monitoring and troubleshooting. The CLI offers detailed information about node stats and cluster stats, enabling scripts and other tools to benefit from those.

In our experience, System Administrators and DevOps professionals are the mostly likely to benefit from a CLI for ClusterControl as they are accustomed to using scripts to perform their daily tasks.

Happy command-line-clustering!

Tags:  CLI clustercontrol database management MySQL PostgreSQL

MySQL Connector/C++ 1.1.9 has been released

Dear MySQL Users,

A new GA (general availability) version of MySQL Connector/C++ has
been made available: MySQL Connector/C++ 1.1.9 GA. The MySQL
Connector/C++ provides a C++ API for connecting client applications to
the MySQL Server 5.5 or newer.

You can download the production release at:

http://dev.mysql.com/downloads/connector/cpp/1.1.html

MySQL Connector C++ (Commercial) will be available for download on the
My Oracle Support (MOS) website. This release will be available on eDelivery
(OSDC) in next month’s upload cycle.

The MySQL driver for C++ offers an easy to use API derived from JDBC
4.0. MySQL Workbench has used it successfully for years.

We have improved the driver since the last GA release. Please see the
documentation and the CHANGES file in the source distribution for a
detailed description of bugs that have been fixed. Bug descriptions are
also listed below.

Enjoy!

Changes in MySQL Connector/C++ 1.1.9 (2017-05-16, General Availability)

Compilation Notes

* The Windows version of Connector/C++ Community is now
built using the dynamic C++ runtime library (that is, with the
/MD compiler option), with the following implications for users:

+ Target hosts running Windows applications that use
Connector/C++ Community now need the Visual C++
Redistributable for Visual Studio 2013
(https://www.microsoft.com/en-us/download/details.aspx?id=40784)
installed on them.

+ Client applications on Windows that use Connector/C++
Community should be compiled with the /MD compiler option.

Security Notes

* The linked OpenSSL library for Connector/C++ 1.1.9
Commercial has been updated to version 1.0.2k. For a description
of issues fixed in this version, see
http://www.openssl.org/news/vulnerabilities.html
This change does not affect the Oracle-produced MySQL Community
build of Connector/C++, which uses the yaSSL library instead.

Bugs Fixed

* Values returned by getDouble() from DOUBLE table columns
were truncated (decimal part missing) if the locale was set to
fr_CA, which uses comma as the decimal separator. (Bug
#17227390, Bug #69719)

* Connections to localhost failed if the local server was
bound only to its IPv6 interface. (Bug #17050354, Bug #69663)

On Behalf of the MySQL/ORACLE RE Team
Balasubramanian Kandasamy

User Authorization in Laravel 5.4 with Spatie Laravel-Permission

What We'll Build

When building an application, we often need to set up an access control list (ACL). An ACL specifies the level of permission granted to a user of an application. For example a user John may have the permission to read and write to a resource while another user Smith may have the permission only to read the resource.

In this tutorial, I will teach you how to add access control to a Laravel app using Laravel-permission package. For this tutorial we will build a simple blog application where users can be assigned different levels of permission. Our user admin page will look like this:

Why Use Laravel-Permission

The Laravel-Permission package is built on top of Laravel's authorization features introduced in the 5.1.1 release. Although there are other packages that claim to offer similar functionalities, none of them have the same level of activity and maintenance as the laravel-permission package.

Development Environment and Installation

You can get Laravel up and running by first downloading the installer

composer global require "laravel/installer"

Then add $HOME/.composer/vendor/bin to your $PATH so the laravel executable can be located by your system. Now you can install the latest stable version of Laravel by running

laravel new

To install the laravel-permission package run

composer require spatie/laravel-permission

Next include the package to our list of service providers, in config/app.php add Spatie\Permission\PermissionServiceProvider::class so our file looks like this

'providers' => [ ... Spatie\Permission\PermissionServiceProvider::class, ];

Next publish the migration file for this package with the command

php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="migrations" Database Setup and Migrations

Next create the database and update the .env file to include the database information. For example, for this tutorial the database information section of the .env looks like this:

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=acl4 DB_USERNAME=root DB_PASSWORD=

To build the tables, run

php artisan migrate

Please note that in Laravel 5.4 the default character set is changed to utf8mb4, therefore if you are running MariaDB or MYSQL version lower than 5.7.7 you may get this error when trying to run migration files

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

To fix this error edit the app\Providers\AppServiceProvider.php file, setting the default string length in the boot method

use Illuminate\Support\Facades\Schema; public function boot() { Schema::defaultStringLength(191); }

After that run the migration again. If it works as normal you would find the following tables in your database:

  • migrations: This keeps track of migration process that have ran
  • users: This holds the users data of the application
  • password_resets: Holds token information when users request a new password
  • permissions: This holds the various permissions needed in the application
  • roles: This holds the roles in our application
  • role_has_permission: This is a pivot table that holds relationship information between the permissions table and the role table
  • user_has_roles: Also a pivot table, holds relationship information between the roles and the users table.
  • user_has_permissions: Also a pivot table, holds relationship information between the users table and the permissions table.

Publish the configuration file for this package by running

php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="config"

The config file allows us to set the location of the Eloquent model of the permission and role class. You can also manually set the table names that should be used to retrieve your roles and permissions. Next we need to add the HasRoles trait to the User model:

use Illuminate\Foundation\Auth\User as Authenticatable; use Spatie\Permission\Traits\HasRoles; class User extends Authenticatable { use HasRoles; // ... } Laravel Collective HTML Form builder

Next install Laravel Collective HTML Form builder as this will be useful further on when we are creating our forms:

composer require laravelcollective/html

Then add your new provider to the providers array of config/app.php:

'providers' => [ ... Collective\Html\HtmlServiceProvider::class, ];

Finally, add two class aliases to the aliases array of config/app.php:

'aliases' => [ // ... 'Form' => Collective\Html\FormFacade::class, 'Html' => Collective\Html\HtmlFacade::class, // ... ],

That's all the installation and configuration needed. A role can be created like a regular Eloquent model, like this:

use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; $role = Role::create(['name' => 'writer']); $permission = Permission::create(['name' => 'edit articles']);

You can also get the permissions associated to a user like this:

$permissions = $user->permissions;

And using the pluck method, pluck() you can get the role names associated with a user like this:

$roles = $user->roles()->pluck('name');

Other methods available to us include:

  • givePermissionTo(): Allows us to give persmission to a user or role
  • revokePermissionTo(): Revoke permission from a user or role
  • hasPermissionTo(): Check if a user or role has a given permission
  • assignRole(): Assigns role to a user
  • removeRole(): Removes role from a user
  • hasRole(): Checks if a user has a role
  • hasAnyRole(Role::all()): Checks if a user has any of a given list of roles
  • hasAllRoles(Role::all()): Checks if a user has all of a given list of role

The methods assignRole, hasRole, hasAnyRole, hasAllRoles and removeRole can accept a string, a Spatie\Permission\Models\Role-object or an \Illuminate\Support\Collection object. The givePermissionTo and revokePermissionTo methods can accept a string or a Spatie\Permission\Models\Permission object.

Laravel-Permission also allows to use Blade directives to verify if the logged in user has all or any of a given list of roles:

@role('writer') I'm a writer! @else I'm not a writer... @endrole @hasrole('writer') I'm a writer! @else I'm not a writer... @endhasrole @hasanyrole(Role::all()) I have one or more of these roles! @else I have none of these roles... @endhasanyrole @hasallroles(Role::all()) I have all of these roles! @else I don't have all of these roles... @endhasallroles

The Blade directives above depends on the users role. Sometimes we need to check directly in our view if a user has a certain permission. You can do that using Laravel's native @can directive:

@can('Edit Post') I have permission to edit @endcan Controllers, Authentication and Views

You will need a total of four controllers for this application. Let's use resource controllers, as this automatically adds stub methods for us. Our controllers will be called

  1. PostController
  2. UserController
  3. RoleController
  4. PermissionController

Before working on these controllers let's create our authentication system. With one command Laravel provides a quick way to scaffold all of the routes and views needed for authentication.

php artisan make:auth

After running this command you would notice two new links for user login and registration in the home page.

This command also creates a HomeController (you can delete this as it won't be needed), a resources/views/layouts/app.blade.php file which contains markup that would be shared by all our views and an app/Http/Controllers/Auth directory which contains the controllers for registration and login. Switch into this directory and open the RegisterController.phpfile. Remove the bcrypt function in the create method, so the the method looks like this

protected function create(array $data) { return User::create([ 'name' => $data['name'], 'email' => $data['email'], 'password' => $data['password'], ]); }

Instead let's define a mutator in app\User.php which would encrypt all our password fields. In app\User.php add this method:

public function setPasswordAttribute($password) { $this->attributes['password'] = bcrypt($password); }

This would provide the same functionality as before but now you don't need to write the bcrypt function when dealing with the password field in subsequent controllers.

Also in the RegisterController.phpfile. Change the $redirectTo property to:

protected $redirectTo = '/';

Do the same thing in the LoginController.phpfile.

Since the HomeController has been deleted our users are now redirected to the home page which would contain a list of our blog posts.

Next let's edit the resources/views/layouts/app.blade.php file to include: an extra drop-down 'Admin' link to view all users and an errors file which checks if our form produced any error. The 'Admin' link would only be viewed by users with the 'Admin' Role. We would also create a custom styles.css which would have extra styling for our resources/views/posts/index.blade.php view. The styling is just a paragraph in the teaser of our index view, the file should be located in public/css/styles.css

{{-- resources/views/layouts/app.blade.php --}} <!DOCTYPE html> <html lang="{{ config('app.locale') }}"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- CSRF Token --> <meta name="csrf-token" content="{{ csrf_token() }}"> <title>{{ config('app.name', 'Laravel') }}</title> <!-- Styles --> <link href="{{ asset('css/app.css') }}" rel="stylesheet"> <link href="{{ asset('css/styles.css') }}" rel="stylesheet"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <!-- Scripts --> <script> window.Laravel = {!! json_encode([ 'csrfToken' => csrf_token(), ]) !!}; </script> <script src="https://use.fontawesome.com/9712be8772.js"></script> </head> <body> <div id="app"> <nav class="navbar navbar-default navbar-static-top"> <div class="container"> <div class="navbar-header"> <!-- Collapsed Hamburger --> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#app-navbar-collapse"> <span class="sr-only">Toggle Navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <!-- Branding Image --> <a class="navbar-brand" href="{{ url('/') }}"> {{ config('app.name', 'Laravel') }} </a> </div> <div class="collapse navbar-collapse" id="app-navbar-collapse"> <!-- Left Side Of Navbar --> <ul class="nav navbar-nav"> <li><a href="{{ url('/') }}">Home</a></li> @if (!Auth::guest()) <li><a href="{{ route('posts.create') }}">New Article</a></li> @endif </ul> <!-- Right Side Of Navbar --> <ul class="nav navbar-nav navbar-right"> <!-- Authentication Links --> @if (Auth::guest()) <li><a href="{{ route('login') }}">Login</a></li> <li><a href="{{ route('register') }}">Register</a></li> @else <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"> {{ Auth::user()->name }} <span class="caret"></span> </a> <ul class="dropdown-menu" role="menu"> <li> @role('Admin') {{-- Laravel-permission blade helper --}} <a href="#"><i class="fa fa-btn fa-unlock"></i>Admin</a> @endrole <a href="{{ route('logout') }}" onclick="event.preventDefault(); document.getElementById('logout-form').submit();"> Logout </a> <form id="logout-form" action="{{ route('logout') }}" method="POST" style="display: none;"> {{ csrf_field() }} </form> </li> </ul> </li> @endif </ul> </div> </div> </nav> @if(Session::has('flash_message')) <div class="container"> <div class="alert alert-success"><em> {!! session('flash_message') !!}</em> </div> </div> @endif <div class="row"> <div class="col-md-8 col-md-offset-2"> @include ('errors.list') {{-- Including error file --}} </div> </div> @yield('content') </div> <!-- Scripts --> <script src="{{ asset('js/app.js') }}"></script> </body> </html>

The error file is:

{{-- resources\views\errors\list.blade.php --}} @if (count($errors) > 0) <div class="alert alert-danger"> <ul> @foreach ($errors->all() as $error) <li>{{ $error }}</li> @endforeach </ul> </div> @endif

and the styles.css file is simply:

p.teaser { text-indent: 30px; } Post Controller

First, let's create the migration and model files for the PostController

php artisan make:model Post -m

This command generates a migration file in app/database/migrations for generating a new MySQL table named posts in our database and a model file Post.phpin the app directory. Let's edit the migration file to include title and body fields of our post. Add a title and body field so the migration file looks like this:

<?php //database\migrations\xxxx_xx_xx_xxxxxx_create_posts_table.php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreatePostsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('posts', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('posts'); } }

After saving the file, run migration again

php artisan migrate

You can now check the database for the post table and columns.

Next make the title and body field of the Post model mass assignable

namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { protected $fillable = [ 'title', 'body' ]; }

Now let's generate our resource controller.

php artisan make:controller PostController --resource

This will create our controller with all the stub methods needed. Edit this file to look like this

<?php // app/Http/Controllers/PostController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Post; use Auth; use Session; class PostController extends Controller { public function __construct() { $this->middleware(['auth', 'clearance'])->except('index', 'show'); } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $posts = Post::orderby('id', 'desc')->paginate(5); //show only 5 items at a time in descending order return view('posts.index', compact('posts')); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { return view('posts.create'); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validating title and body field $this->validate($request, [ 'title'=>'required|max:100', 'body' =>'required', ]); $title = $request['title']; $body = $request['body']; $post = Post::create($request->only('title', 'body')); //Display a successful message upon save return redirect()->route('posts.index') ->with('flash_message', 'Article, '. $post->title.' created'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { $post = Post::findOrFail($id); //Find post of id = $id return view ('posts.show', compact('post')); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $post = Post::findOrFail($id); return view('posts.edit', compact('post')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $this->validate($request, [ 'title'=>'required|max:100', 'body'=>'required', ]); $post = Post::findOrFail($id); $post->title = $request->input('title'); $post->body = $request->input('body'); $post->save(); return redirect()->route('posts.show', $post->id)->with('flash_message', 'Article, '. $post->title.' updated'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $post = Post::findOrFail($id); $post->delete(); return redirect()->route('posts.index') ->with('flash_message', 'Article successfully deleted'); } }

Here the Post class was imported from our model and the Auth class which was generated with the make:auth command earlier. These were imported so that you would be able to make Eloquent queries on the Post table and so as to be able to have access to authentication information of our users. In the constructor two middlewares were called, one is auth which restricts access to the PostController methods to authenticated users the other is a custom middleware is yet to be created. This would be responsible for our Permissions and Roles system. Next, index and show are passed into the except method to allow all users to be able to view posts.

The index() method lists all the available posts. It queries the post table for all posts and passes this information to the view. Paginate() allows us to limit the number of posts in a page, in this case five.

The create() method simply returns the posts/create view which would contain a form for creating new posts. The store() method saves the information input from the posts/create view. The information is first validated and after it is saved, a flash message is passed to the view posts/index.

Our show() method of the PostController allows us to display a single post. This method takes the post id as an argument and passes it to the method Post::find(). The result of the query is then sent to our posts/show view.

The edit() method, similar to the create() method simply returns the posts/edit view which would contain a form for creating editing posts. The update() method takes the information from the posts/edit view and updates the record. The destroy() method let's us delete a post.

Now that you have the PostController you need to set up the routes. Edit your app/routes/web.php file to look like this:

<?php Route::get('/', function () { return view('welcome'); }); Auth::routes(); Route::get('/', 'PostController@index')->name('home'); Route::resource('users', 'UserController'); Route::resource('roles', 'RoleController'); Route::resource('permissions', 'PermissionController'); Route::resource('posts', 'PostController');

The / route is the route to our home page, here it was renamed to home The Auth route was generated when you ran the make:auth command. It handles authentication related routes. The other four routes are for resources that would be created later.

Post Views

Only four views are needed for our PostController. Create the files \resources\views\posts\index.blade.php, \resources\views\posts\create.blade.php, \resources\views\posts\show.blade.php, \resources\views\posts\edit.blade.php

Edit the index.blade.phpfile to look like this

@extends('layouts.app') @section('content') <div class="container"> <div class="row"> <div class="col-md-10 col-md-offset-1"> <div class="panel panel-default"> <div class="panel-heading"><h3>Posts</h3></div> <div class="panel-heading">Page {{ $posts->currentPage() }} of {{ $posts->lastPage() }}</div> @foreach ($posts as $post) <div class="panel-body"> <li style="list-style-type:disc"> <a href="{{ route('posts.show', $post->id ) }}"><b>{{ $post->title }}</b><br> <p class="teaser"> {{ str_limit($post->body, 100) }} {{-- Limit teaser to 100 characters --}} </p> </a> </li> </div> @endforeach </div> <div class="text-center"> {!! $posts->links() !!} </div> </div> </div> </div> @endsection

Notice that this file extends views\layouts\app.php file, which was generated earlier by the make:auth command.

The create.blade.php file looks like this

@extends('layouts.app') @section('title', '| Create New Post') @section('content') <div class="row"> <div class="col-md-8 col-md-offset-2"> <h1>Create New Post</h1> <hr> {{-- Using the Laravel HTML Form Collective to create our form --}} {{ Form::open(array('route' => 'posts.store')) }} <div class="form-group"> {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} <br> {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} <br> {{ Form::submit('Create Post', array('class' => 'btn btn-success btn-lg btn-block')) }} {{ Form::close() }} </div> </div> </div> @endsection

The show view looks like this:

@extends('layouts.app') @section('title', '| View Post') @section('content') <div class="container"> <h1>{{ $post->title }}</h1> <hr> <p class="lead">{{ $post->body }} </p> <hr> {!! Form::open(['method' => 'DELETE', 'route' => ['posts.destroy', $post->id] ]) !!} <a href="{{ url()->previous() }}" class="btn btn-primary">Back</a> @can('Edit Post') <a href="{{ route('posts.edit', $post->id) }}" class="btn btn-info" role="button">Edit</a> @endcan @can('Delete Post') {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} @endcan {!! Form::close() !!} </div> @endsection

Here the can directive checks if a user has the permission to Edit or Delete Posts, if so the Edit and Delete button will be displayed. If the user does not have these permissions, only the Back button would be displayed.

The edit view just displays a edit form that will be used to update records:

@extends('layouts.app') @section('title', '| Edit Post') @section('content') <div class="row"> <div class="col-md-8 col-md-offset-2"> <h1>Edit Post</h1> <hr> {{ Form::model($post, array('route' => array('posts.update', $post->id), 'method' => 'PUT')) }} <div class="form-group"> {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }}<br> {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }}<br> {{ Form::submit('Save', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> </div> </div> @endsection

If you visit the home page you would see this

User Controller

The UserController will handle displaying all users, creating of new users, editing users, assigning roles to users and deleting users. As before generate the controller by running

php artisan make:controller UserController --resource

Then replace the content of this file with:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\User; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; //Enables us to output flash messaging use Session; class UserController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { //Get all users and pass it to the view $users = User::all(); return view('users.index')->with('users', $users); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { //Get all roles and pass it to the view $roles = Role::get(); return view('users.create', ['roles'=>$roles]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users', 'password'=>'required|min:6|confirmed' ]); $user = User::create($request->only('email', 'name', 'password')); //Retrieving only the email and password data $roles = $request['roles']; //Retrieving the roles field //Checking if a role was selected if (isset($roles)) { foreach ($roles as $role) { $role_r = Role::where('id', '=', $role)->firstOrFail(); $user->assignRole($role_r); //Assigning role to user } } //Redirect to the users.index view and display message return redirect()->route('users.index') ->with('flash_message', 'User successfully added.'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('users'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $user = User::findOrFail($id); //Get user with specified id $roles = Role::get(); //Get all roles return view('users.edit', compact('user', 'roles')); //pass user and roles data to view } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $user = User::findOrFail($id); //Get role specified by id //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users,email,'.$id, 'password'=>'required|min:6|confirmed' ]); $input = $request->only(['name', 'email', 'password']); //Retreive the name, email and password fields $roles = $request['roles']; //Retreive all roles $user->fill($input)->save(); if (isset($roles)) { $user->roles()->sync($roles); //If one or more role is selected associate user to roles } else { $user->roles()->detach(); //If no role is selected remove exisiting role associated to a user } return redirect()->route('users.index') ->with('flash_message', 'User successfully edited.'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { //Find a user with a given id and delete $user = User::findOrFail($id); $user->delete(); return redirect()->route('users.index') ->with('flash_message', 'User successfully deleted.'); } }

Here the User class, the Role class, the Permission class and the Auth class are imported. In the constructor the auth middleware is called to make sure only authenticated users have access to the User resource. A custom middleware isAdmin is also called. This checks if the authenticated user has administrator privileges. This middleware will be created later.

The index() method gets all users from the Users table and passes it to the index view which will display all users in a table. The create() method first gets all the Roles from the Roles table and passes it to the create view. This is so that Roles can be added when creating a User.

The store() method saves the input from the create view, after validating the input, looping through the Roles that was passed in the form and assigning these Roles to the User. The show()method just redirects back to the users page as for this demonstration, we wont need to show each user individually.

The edit() method gets the user corresponding to the id passed, then gets all roles and passes it to the edit view. The update() method validates data from the edit view and saves the updated name and password fields. It gets all roles from the roles table and while looping through them, removes any role assign to the user. It then takes the role data inputted from the form, matches them with the values in the databases and assigns these roles to the user.

The destroy() method allows us to delete a user along with it's corresponding role.

User Views

Three views are needed here: index, create and edit views. The index view would contain a table that lists all our users and their roles.

{{-- \resources\views\users\index.blade.php --}} @extends('layouts.app') @section('title', '| Users') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-users"></i> User Administration <a href="{{ route('roles.index') }}" class="btn btn-default pull-right">Roles</a> <a href="{{ route('permissions.index') }}" class="btn btn-default pull-right">Permissions</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Date/Time Added</th> <th>User Roles</th> <th>Operations</th> </tr> </thead> <tbody> @foreach ($users as $user) <tr> <td>{{ $user->name }}</td> <td>{{ $user->email }}</td> <td>{{ $user->created_at->format('F d, Y h:ia') }}</td> <td>{{ $user->roles()->pluck('name')->implode(' ') }}</td>{{-- Retrieve array of roles associated to a user and convert to string --}} <td> <a href="{{ route('users.edit', $user->id) }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['users.destroy', $user->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ route('users.create') }}" class="btn btn-success">Add User</a> </div> @endsection

The create view is just a form that allows us to create new users and assign roles to them.

{{-- \resources\views\users\create.blade.php --}} @extends('layouts.app') @section('title', '| Add User') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-user-plus'></i> Add User</h1> <hr> {{ Form::open(array('url' => 'users')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('email', 'Email') }} {{ Form::email('email', '', array('class' => 'form-control')) }} </div> <div class='form-group'> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach </div> <div class="form-group"> {{ Form::label('password', 'Password') }}<br> {{ Form::password('password', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('password', 'Confirm Password') }}<br> {{ Form::password('password_confirmation', array('class' => 'form-control')) }} </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

The edit view is a form that allows us to edit users and their roles. Using Laravel's form model binding the form is automatically populated with the previous values.

{{-- \resources\views\users\edit.blade.php --}} @extends('layouts.app') @section('title', '| Edit User') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-user-plus'></i> Edit {{$user->name}}</h1> <hr> {{ Form::model($user, array('route' => array('users.update', $user->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with user data --}} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('email', 'Email') }} {{ Form::email('email', null, array('class' => 'form-control')) }} </div> <h5><b>Give Role</b></h5> <div class='form-group'> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id, $user->roles ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach </div> <div class="form-group"> {{ Form::label('password', 'Password') }}<br> {{ Form::password('password', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('password', 'Confirm Password') }}<br> {{ Form::password('password_confirmation', array('class' => 'form-control')) }} </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Permission Controller

Now let's tackle the PermissionControllerCreate the file and paste the following code:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; use Session; class PermissionController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $permissions = Permission::all(); //Get all permissions return view('permissions.index')->with('permissions', $permissions); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $roles = Role::get(); //Get all roles return view('permissions.create')->with('roles', $roles); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { $this->validate($request, [ 'name'=>'required|max:40', ]); $name = $request['name']; $permission = new Permission(); $permission->name = $name; $roles = $request['roles']; $permission->save(); if (!empty($request['roles'])) { //If one or more role is selected foreach ($roles as $role) { $r = Role::where('id', '=', $role)->firstOrFail(); //Match input role to db record $permission = Permission::where('name', '=', $name)->first(); //Match input //permission to db record $r->givePermissionTo($permission); } } return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('permissions'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $permission = Permission::findOrFail($id); return view('permissions.edit', compact('permission')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $permission = Permission::findOrFail($id); $this->validate($request, [ 'name'=>'required|max:40', ]); $input = $request->all(); $permission->fill($input)->save(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $permission = Permission::findOrFail($id); //Make it impossible to delete this specific permission if ($permission->name == "Administer roles & permissions") { return redirect()->route('permissions.index') ->with('flash_message', 'Cannot delete this Permission!'); } $permission->delete(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission deleted!'); } }

In the store() method, we are making it possible for a role to be selected as a permission is created. After validating and saving the permission name field, a check is done if a role was selected if it was, a permission is assigned to the selected role.

Permission View

Three views are needed here as well. The index view would list in a table all the available permissions, the create view is a form which would be used to create a new permission and the edit view is a form that let's us edit existing permission.

{{-- \resources\views\permissions\index.blade.php --}} @extends('layouts.app') @section('title', '| Permissions') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-key"></i>Available Permissions <a href="{{ route('users.index') }}" class="btn btn-default pull-right">Users</a> <a href="{{ route('roles.index') }}" class="btn btn-default pull-right">Roles</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Permissions</th> <th>Operation</th> </tr> </thead> <tbody> @foreach ($permissions as $permission) <tr> <td>{{ $permission->name }}</td> <td> <a href="{{ URL::to('permissions/'.$permission->id.'/edit') }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['permissions.destroy', $permission->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ URL::to('permissions/create') }}" class="btn btn-success">Add Permission</a> </div> @endsection

The following is the create view

{{-- \resources\views\permissions\create.blade.php --}} @extends('layouts.app') @section('title', '| Create Permission') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Add Permission</h1> <br> {{ Form::open(array('url' => 'permissions')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} </div><br> @if(!$roles->isEmpty()) //If no roles exist yet <h4>Assign Permission to Roles</h4> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach @endif <br> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

And finally the edit view:

@extends('layouts.app') @section('title', '| Edit Permission') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Edit {{$permission->name}}</h1> <br> {{ Form::model($permission, array('route' => array('permissions.update', $permission->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with permission data --}} <div class="form-group"> {{ Form::label('name', 'Permission Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <br> {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Role Controller

The RoleController is quite similar to the UserController. This controller will allow us to create roles and assign one or more permissions to a role. Create the file and paste the following code:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; use Session; class RoleController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']);//isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $roles = Role::all();//Get all roles return view('roles.index')->with('roles', $roles); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $permissions = Permission::all();//Get all permissions return view('roles.create', ['permissions'=>$permissions]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name and permissions field $this->validate($request, [ 'name'=>'required|unique:roles|max:10', 'permissions' =>'required', ] ); $name = $request['name']; $role = new Role(); $role->name = $name; $permissions = $request['permissions']; $role->save(); //Looping thru selected permissions foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Fetch the newly created role and assign permission $role = Role::where('name', '=', $name)->first(); $role->givePermissionTo($p); } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('roles'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $role = Role::findOrFail($id); $permissions = Permission::all(); return view('roles.edit', compact('role', 'permissions')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $role = Role::findOrFail($id);//Get role with the given id //Validate name and permission fields $this->validate($request, [ 'name'=>'required|max:10|unique:roles,name,'.$id, 'permissions' =>'required', ]); $input = $request->except(['permissions']); $permissions = $request['permissions']; $role->fill($input)->save(); $p_all = Permission::all();//Get all permissions foreach ($p_all as $p) { $role->revokePermissionTo($p); //Remove all permissions associated with role } foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Get corresponding form //permission in db $role->givePermissionTo($p); //Assign permission to role } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $role = Role::findOrFail($id); $role->delete(); return redirect()->route('roles.index') ->with('flash_message', 'Role deleted!'); } } Roles View

Three views are needed here as well. The index view to display available roles and associated permissions, the create view to add a new role and a view to edit an existing role. Create the index.blade.php file and paste the following:

{{-- \resources\views\roles\index.blade.php --}} @extends('layouts.app') @section('title', '| Roles') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-key"></i> Roles <a href="{{ route('users.index') }}" class="btn btn-default pull-right">Users</a> <a href="{{ route('permissions.index') }}" class="btn btn-default pull-right">Permissions</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Role</th> <th>Permissions</th> <th>Operation</th> </tr> </thead> <tbody> @foreach ($roles as $role) <tr> <td>{{ $role->name }}</td> <td>{{ str_replace(array('[',']','"'),'', $role->permissions()->pluck('name')) }}</td>{{-- Retrieve array of permissions associated to a role and convert to string --}} <td> <a href="{{ URL::to('roles/'.$role->id.'/edit') }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['roles.destroy', $role->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ URL::to('roles/create') }}" class="btn btn-success">Add Role</a> </div> @endsection

For the create view:

@extends('layouts.app') @section('title', '| Add Role') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Add Role</h1> <hr> {{ Form::open(array('url' => 'roles')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <h5><b>Assign Permissions</b></h5> <div class='form-group'> @foreach ($permissions as $permission) {{ Form::checkbox('permissions[]', $permission->id ) }} {{ Form::label($permission->name, ucfirst($permission->name)) }}<br> @endforeach </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

And for the edit view:

@extends('layouts.app') @section('title', '| Edit Role') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Edit Role: {{$role->name}}</h1> <hr> {{ Form::model($role, array('route' => array('roles.update', $role->id), 'method' => 'PUT')) }} <div class="form-group"> {{ Form::label('name', 'Role Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <h5><b>Assign Permissions</b></h5> @foreach ($permissions as $permission) {{Form::checkbox('permissions[]', $permission->id, $role->permissions ) }} {{Form::label($permission->name, ucfirst($permission->name)) }}<br> @endforeach <br> {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Middleware

To restrict access to the roles and permissions page, a middleware was included called isAdmin in our PermissionController and RoleController. This middleware counts how many users are in the Users table, and if there are more than one users, it checks if the current authenticated User has the permission to 'Administer roles & permissions'. To create a permission visit http://localhost:8000/permissions/create. Then go to http://localhost:8000/roles/create to create a role, to which you can now assign the permission you created. For example you can create a permission called 'Administer roles & permissions' and a 'Admin' role to which you would assign this permission. Create the AdminMiddleware in the directory app/Http/Middleware/ and enter the following code:

<?php namespace App\Http\Middleware; use Closure; use Illuminate\Support\Facades\Auth; use App\User; class AdminMiddleware { /** * Handle an incoming request. * * @param \Illuminate\Http\Request $request * @param \Closure $next * @return mixed */ public function handle($request, Closure $next) { $user = User::all()->count(); if (!($user == 1)) { if (!Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user does //not have this permission { abort('401'); } } return $next($request); } }

A middleware called clearance was also included in our PostController. This middleware would check if a user has the permissions Administer roles & permissions, Create Post, Edit Post and Delete Post.

<?php namespace App\Http\Middleware; use Closure; use Illuminate\Support\Facades\Auth; class ClearanceMiddleware { /** * Handle an incoming request. * * @param \Illuminate\Http\Request $request * @param \Closure $next * @return mixed */ public function handle($request, Closure $next) { if (Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user has this //permission { return $next($request); } if ($request->is('posts/create'))//If user is creating a post { if (!Auth::user()->hasPermissionTo('Create Post')) { abort('401'); } else { return $next($request); } } if ($request->is('posts/*/edit')) //If user is editing a post { if (!Auth::user()->hasPermissionTo('Edit Post')) { abort('401'); } else { return $next($request); } } if ($request->isMethod('Delete')) //If user is deleting a post { if (!Auth::user()->hasPermissionTo('Delete Post')) { abort('401'); } else { return $next($request); } } return $next($request); } }

Add AdminMiddleware::class and ClearanceMiddleware::class to the $routeMiddleware property of /app/Http/kernel.php like this:

protected $routeMiddleware = [ 'auth' => \Illuminate\Auth\Middleware\Authenticate::class, 'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class, 'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class, 'can' => \Illuminate\Auth\Middleware\Authorize::class, 'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class, 'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class, 'isAdmin' => \App\Http\Middleware\AdminMiddleware::class, 'clearance' => \App\Http\Middleware\ClearanceMiddleware::class, ];

In both middelwares a 401 exception would be thrown if the conditions are not meet. Let's create a custom 401 error page:

{{-- \resources\views\errors\401.blade.php --}} @extends('layouts.app') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><center>401<br> ACCESS DENIED</center></h1> </div> @endsection Wrapping Up

First lets create an 'Admin' user and then create the necessary permissions and roles. Click on Register and create a user, then go to http://localhost:8000/permissions and create permissions to Create Post, Edit Post, Delete Post and Administer roles & permissions. After creating these permissions, your permissions page should look like this:

Next, you need to create roles to which you would add the Create, Edit and Delete Permissions. Click on Roles and create these roles:

  • Admin- A user assigned to this role would have all permissions
  • Owner- A user assigned to this role would have selected permissions assigned to it by Admin

Finally assign the Role of 'Admin' to the currently logged in User. Click on Users and then Edit. Check the Admin box under Give Role:

After assigning the 'Admin' role to our user, notice that you now have a new Admin link in the drop of the navigation, this links to our users page. Now create a new user and give it the more restrictive role of Owner. If you login as this user and try to visit the User, Role or Permission pages you get this as expected:

The Owner role does not have permission to Administer Roles & Users hence the exception is thrown.

To demonstrate how this works for posts, create a post by clicking on New Article. After creating the post, view the post and you would notice you have along with the Back button, an Edit and Delete button as shown below:

Now if you logout and view the post only the Back button will be available to us. This also works if you have a logged in user who does not have permissions to Edit or Delete Post.

Conclusion

The laravel-permission package makes it relatively easy to build a role and permission system. To recap we have considered installation of the laravel-permission package, laravel-permission blade directives, creating a custom middleware and implementing an access control list in a Laravel application. You can look at the final product on Github and if you have any questions or comments, don’t hesitate to post them below.

User Authorization in Laravel 5.4 with Spatie Laravel-Permission

What We'll Build

When building an application, we often need to set up an access control list (ACL). An ACL specifies the level of permission granted to a user of an application. For example a user John may have the permission to read and write to a resource while another user Smith may have the permission only to read the resource.

In this tutorial, I will teach you how to add access control to a Laravel app using Laravel-permission package. For this tutorial we will build a simple blog application where users can be assigned different levels of permission. Our user admin page will look like this:

Why Use Laravel-Permission

The Laravel-Permission package is built on top of Laravel's authorization features introduced in the 5.1.1 release. Although there are other packages that claim to offer similar functionalities, none of them have the same level of activity and maintenance as the laravel-permission package.

Development Environment and Installation

You can get Laravel up and running by first downloading the installer

composer global require "laravel/installer"

Then add $HOME/.composer/vendor/bin to your $PATH so the laravel executable can be located by your system. Now you can install the latest stable version of Laravel by running

laravel new

To install the laravel-permission package run

composer require spatie/laravel-permission

Next include the package to our list of service providers, in config/app.php add Spatie\Permission\PermissionServiceProvider::class so our file looks like this

'providers' => [ ... Spatie\Permission\PermissionServiceProvider::class, ];

Next publish the migration file for this package with the command

php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="migrations" Database Setup and Migrations

Next create the database and update the .env file to include the database information. For example, for this tutorial the database information section of the .env looks like this:

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=acl4 DB_USERNAME=root DB_PASSWORD=

To build the tables, run

php artisan migrate

Please note that in Laravel 5.4 the default character set is changed to utf8mb4, therefore if you are running MariaDB or MYSQL version lower than 5.7.7 you may get this error when trying to run migration files

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

To fix this error edit the app\Providers\AppServiceProvider.php file, setting the default string length in the boot method

use Illuminate\Support\Facades\Schema; public function boot() { Schema::defaultStringLength(191); }

After that run the migration again. If it works as normal you would find the following tables in your database:

  • migrations: This keeps track of migration process that have ran
  • users: This holds the users data of the application
  • password_resets: Holds token information when users request a new password
  • permissions: This holds the various permissions needed in the application
  • roles: This holds the roles in our application
  • role_has_permission: This is a pivot table that holds relationship information between the permissions table and the role table
  • user_has_roles: Also a pivot table, holds relationship information between the roles and the users table.
  • user_has_permissions: Also a pivot table, holds relationship information between the users table and the permissions table.

Publish the configuration file for this package by running

php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="config"

The config file allows us to set the location of the Eloquent model of the permission and role class. You can also manually set the table names that should be used to retrieve your roles and permissions. Next we need to add the HasRoles trait to the User model:

use Illuminate\Foundation\Auth\User as Authenticatable; use Spatie\Permission\Traits\HasRoles; class User extends Authenticatable { use HasRoles; // ... } Laravel Collective HTML Form builder

Next install Laravel Collective HTML Form builder as this will be useful further on when we are creating our forms:

composer require laravelcollective/html

Then add your new provider to the providers array of config/app.php:

'providers' => [ ... Collective\Html\HtmlServiceProvider::class, ];

Finally, add two class aliases to the aliases array of config/app.php:

'aliases' => [ // ... 'Form' => Collective\Html\FormFacade::class, 'Html' => Collective\Html\HtmlFacade::class, // ... ],

That's all the installation and configuration needed. A role can be created like a regular Eloquent model, like this:

use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; $role = Role::create(['name' => 'writer']); $permission = Permission::create(['name' => 'edit articles']);

You can also get the permissions associated to a user like this:

$permissions = $user->permissions;

And using the pluck method, pluck() you can get the role names associated with a user like this:

$roles = $user->roles()->pluck('name');

Other methods available to us include:

  • givePermissionTo(): Allows us to give persmission to a user or role
  • revokePermissionTo(): Revoke permission from a user or role
  • hasPermissionTo(): Check if a user or role has a given permission
  • assignRole(): Assigns role to a user
  • removeRole(): Removes role from a user
  • hasRole(): Checks if a user has a role
  • hasAnyRole(Role::all()): Checks if a user has any of a given list of roles
  • hasAllRoles(Role::all()): Checks if a user has all of a given list of role

The methods assignRole, hasRole, hasAnyRole, hasAllRoles and removeRole can accept a string, a Spatie\Permission\Models\Role-object or an \Illuminate\Support\Collection object. The givePermissionTo and revokePermissionTo methods can accept a string or a Spatie\Permission\Models\Permission object.

Laravel-Permission also allows to use Blade directives to verify if the logged in user has all or any of a given list of roles:

@role('writer') I'm a writer! @else I'm not a writer... @endrole @hasrole('writer') I'm a writer! @else I'm not a writer... @endhasrole @hasanyrole(Role::all()) I have one or more of these roles! @else I have none of these roles... @endhasanyrole @hasallroles(Role::all()) I have all of these roles! @else I don't have all of these roles... @endhasallroles

The Blade directives above depends on the users role. Sometimes we need to check directly in our view if a user has a certain permission. You can do that using Laravel's native @can directive:

@can('Edit Post') I have permission to edit @endcan Controllers, Authentication and Views

You will need a total of four controllers for this application. Let's use resource controllers, as this automatically adds stub methods for us. Our controllers will be called

  1. PostController
  2. UserController
  3. RoleController
  4. PermissionController

Before working on these controllers let's create our authentication system. With one command Laravel provides a quick way to scaffold all of the routes and views needed for authentication.

php artisan make:auth

After running this command you would notice two new links for user login and registration in the home page.

This command also creates a HomeController (you can delete this as it won't be needed), a resources/views/layouts/app.blade.php file which contains markup that would be shared by all our views and an app/Http/Controllers/Auth directory which contains the controllers for registration and login. Switch into this directory and open the RegisterController.phpfile. Remove the bcrypt function in the create method, so the the method looks like this

protected function create(array $data) { return User::create([ 'name' => $data['name'], 'email' => $data['email'], 'password' => $data['password'], ]); }

Instead let's define a mutator in app\User.php which would encrypt all our password fields. In app\User.php add this method:

public function setPasswordAttribute($password) { $this->attributes['password'] = bcrypt($password); }

This would provide the same functionality as before but now you don't need to write the bcrypt function when dealing with the password field in subsequent controllers.

Also in the RegisterController.phpfile. Change the $redirectTo property to:

protected $redirectTo = '/';

Do the same thing in the LoginController.phpfile.

Since the HomeController has been deleted our users are now redirected to the home page which would contain a list of our blog posts.

Next let's edit the resources/views/layouts/app.blade.php file to include: an extra drop-down 'Admin' link to view all users and an errors file which checks if our form produced any error. The 'Admin' link would only be viewed by users with the 'Admin' Role. We would also create a custom styles.css which would have extra styling for our resources/views/posts/index.blade.php view. The styling is just a paragraph in the teaser of our index view, the file should be located in public/css/styles.css

{{-- resources/views/layouts/app.blade.php --}} <!DOCTYPE html> <html lang="{{ config('app.locale') }}"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- CSRF Token --> <meta name="csrf-token" content="{{ csrf_token() }}"> <title>{{ config('app.name', 'Laravel') }}</title> <!-- Styles --> <link href="{{ asset('css/app.css') }}" rel="stylesheet"> <link href="{{ asset('css/styles.css') }}" rel="stylesheet"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <!-- Scripts --> <script> window.Laravel = {!! json_encode([ 'csrfToken' => csrf_token(), ]) !!}; </script> <script src="https://use.fontawesome.com/9712be8772.js"></script> </head> <body> <div id="app"> <nav class="navbar navbar-default navbar-static-top"> <div class="container"> <div class="navbar-header"> <!-- Collapsed Hamburger --> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#app-navbar-collapse"> <span class="sr-only">Toggle Navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <!-- Branding Image --> <a class="navbar-brand" href="{{ url('/') }}"> {{ config('app.name', 'Laravel') }} </a> </div> <div class="collapse navbar-collapse" id="app-navbar-collapse"> <!-- Left Side Of Navbar --> <ul class="nav navbar-nav"> <li><a href="{{ url('/') }}">Home</a></li> @if (!Auth::guest()) <li><a href="{{ route('posts.create') }}">New Article</a></li> @endif </ul> <!-- Right Side Of Navbar --> <ul class="nav navbar-nav navbar-right"> <!-- Authentication Links --> @if (Auth::guest()) <li><a href="{{ route('login') }}">Login</a></li> <li><a href="{{ route('register') }}">Register</a></li> @else <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false"> {{ Auth::user()->name }} <span class="caret"></span> </a> <ul class="dropdown-menu" role="menu"> <li> @role('Admin') {{-- Laravel-permission blade helper --}} <a href="#"><i class="fa fa-btn fa-unlock"></i>Admin</a> @endrole <a href="{{ route('logout') }}" onclick="event.preventDefault(); document.getElementById('logout-form').submit();"> Logout </a> <form id="logout-form" action="{{ route('logout') }}" method="POST" style="display: none;"> {{ csrf_field() }} </form> </li> </ul> </li> @endif </ul> </div> </div> </nav> @if(Session::has('flash_message')) <div class="container"> <div class="alert alert-success"><em> {!! session('flash_message') !!}</em> </div> </div> @endif <div class="row"> <div class="col-md-8 col-md-offset-2"> @include ('errors.list') {{-- Including error file --}} </div> </div> @yield('content') </div> <!-- Scripts --> <script src="{{ asset('js/app.js') }}"></script> </body> </html>

The error file is:

{{-- resources\views\errors\list.blade.php --}} @if (count($errors) > 0) <div class="alert alert-danger"> <ul> @foreach ($errors->all() as $error) <li>{{ $error }}</li> @endforeach </ul> </div> @endif

and the styles.css file is simply:

p.teaser { text-indent: 30px; } Post Controller

First, let's create the migration and model files for the PostController

php artisan make:model Post -m

This command generates a migration file in app/database/migrations for generating a new MySQL table named posts in our database and a model file Post.phpin the app directory. Let's edit the migration file to include title and body fields of our post. Add a title and body field so the migration file looks like this:

<?php //database\migrations\xxxx_xx_xx_xxxxxx_create_posts_table.php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreatePostsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('posts', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('posts'); } }

After saving the file, run migration again

php artisan migrate

You can now check the database for the post table and columns.

Next make the title and body field of the Post model mass assignable

namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { protected $fillable = [ 'title', 'body' ]; }

Now let's generate our resource controller.

php artisan make:controller PostController --resource

This will create our controller with all the stub methods needed. Edit this file to look like this

<?php // app/Http/Controllers/PostController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Post; use Auth; use Session; class PostController extends Controller { public function __construct() { $this->middleware(['auth', 'clearance'])->except('index', 'show'); } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $posts = Post::orderby('id', 'desc')->paginate(5); //show only 5 items at a time in descending order return view('posts.index', compact('posts')); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { return view('posts.create'); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validating title and body field $this->validate($request, [ 'title'=>'required|max:100', 'body' =>'required', ]); $title = $request['title']; $body = $request['body']; $post = Post::create($request->only('title', 'body')); //Display a successful message upon save return redirect()->route('posts.index') ->with('flash_message', 'Article, '. $post->title.' created'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { $post = Post::findOrFail($id); //Find post of id = $id return view ('posts.show', compact('post')); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $post = Post::findOrFail($id); return view('posts.edit', compact('post')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $this->validate($request, [ 'title'=>'required|max:100', 'body'=>'required', ]); $post = Post::findOrFail($id); $post->title = $request->input('title'); $post->body = $request->input('body'); $post->save(); return redirect()->route('posts.show', $post->id)->with('flash_message', 'Article, '. $post->title.' updated'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $post = Post::findOrFail($id); $post->delete(); return redirect()->route('posts.index') ->with('flash_message', 'Article successfully deleted'); } }

Here the Post class was imported from our model and the Auth class which was generated with the make:auth command earlier. These were imported so that you would be able to make Eloquent queries on the Post table and so as to be able to have access to authentication information of our users. In the constructor two middlewares were called, one is auth which restricts access to the PostController methods to authenticated users the other is a custom middleware is yet to be created. This would be responsible for our Permissions and Roles system. Next, index and show are passed into the except method to allow all users to be able to view posts.

The index() method lists all the available posts. It queries the post table for all posts and passes this information to the view. Paginate() allows us to limit the number of posts in a page, in this case five.

The create() method simply returns the posts/create view which would contain a form for creating new posts. The store() method saves the information input from the posts/create view. The information is first validated and after it is saved, a flash message is passed to the view posts/index.

Our show() method of the PostController allows us to display a single post. This method takes the post id as an argument and passes it to the method Post::find(). The result of the query is then sent to our posts/show view.

The edit() method, similar to the create() method simply returns the posts/edit view which would contain a form for creating editing posts. The update() method takes the information from the posts/edit view and updates the record. The destroy() method let's us delete a post.

Now that you have the PostController you need to set up the routes. Edit your app/routes/web.php file to look like this:

<?php Route::get('/', function () { return view('welcome'); }); Auth::routes(); Route::get('/', 'PostController@index')->name('home'); Route::resource('users', 'UserController'); Route::resource('roles', 'RoleController'); Route::resource('permissions', 'PermissionController'); Route::resource('posts', 'PostController');

The / route is the route to our home page, here it was renamed to home The Auth route was generated when you ran the make:auth command. It handles authentication related routes. The other four routes are for resources that would be created later.

Post Views

Only four views are needed for our PostController. Create the files \resources\views\posts\index.blade.php, \resources\views\posts\create.blade.php, \resources\views\posts\show.blade.php, \resources\views\posts\edit.blade.php

Edit the index.blade.phpfile to look like this

@extends('layouts.app') @section('content') <div class="container"> <div class="row"> <div class="col-md-10 col-md-offset-1"> <div class="panel panel-default"> <div class="panel-heading"><h3>Posts</h3></div> <div class="panel-heading">Page {{ $posts->currentPage() }} of {{ $posts->lastPage() }}</div> @foreach ($posts as $post) <div class="panel-body"> <li style="list-style-type:disc"> <a href="{{ route('posts.show', $post->id ) }}"><b>{{ $post->title }}</b><br> <p class="teaser"> {{ str_limit($post->body, 100) }} {{-- Limit teaser to 100 characters --}} </p> </a> </li> </div> @endforeach </div> <div class="text-center"> {!! $posts->links() !!} </div> </div> </div> </div> @endsection

Notice that this file extends views\layouts\app.php file, which was generated earlier by the make:auth command.

The create.blade.php file looks like this

@extends('layouts.app') @section('title', '| Create New Post') @section('content') <div class="row"> <div class="col-md-8 col-md-offset-2"> <h1>Create New Post</h1> <hr> {{-- Using the Laravel HTML Form Collective to create our form --}} {{ Form::open(array('route' => 'posts.store')) }} <div class="form-group"> {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} <br> {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} <br> {{ Form::submit('Create Post', array('class' => 'btn btn-success btn-lg btn-block')) }} {{ Form::close() }} </div> </div> </div> @endsection

The show view looks like this:

@extends('layouts.app') @section('title', '| View Post') @section('content') <div class="container"> <h1>{{ $post->title }}</h1> <hr> <p class="lead">{{ $post->body }} </p> <hr> {!! Form::open(['method' => 'DELETE', 'route' => ['posts.destroy', $post->id] ]) !!} <a href="{{ url()->previous() }}" class="btn btn-primary">Back</a> @can('Edit Post') <a href="{{ route('posts.edit', $post->id) }}" class="btn btn-info" role="button">Edit</a> @endcan @can('Delete Post') {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} @endcan {!! Form::close() !!} </div> @endsection

Here the can directive checks if a user has the permission to Edit or Delete Posts, if so the Edit and Delete button will be displayed. If the user does not have these permissions, only the Back button would be displayed.

The edit view just displays a edit form that will be used to update records:

@extends('layouts.app') @section('title', '| Edit Post') @section('content') <div class="row"> <div class="col-md-8 col-md-offset-2"> <h1>Edit Post</h1> <hr> {{ Form::model($post, array('route' => array('posts.update', $post->id), 'method' => 'PUT')) }} <div class="form-group"> {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }}<br> {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }}<br> {{ Form::submit('Save', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> </div> </div> @endsection

If you visit the home page you would see this

User Controller

The UserController will handle displaying all users, creating of new users, editing users, assigning roles to users and deleting users. As before generate the controller by running

php artisan make:controller UserController --resource

Then replace the content of this file with:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\User; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; //Enables us to output flash messaging use Session; class UserController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { //Get all users and pass it to the view $users = User::all(); return view('users.index')->with('users', $users); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { //Get all roles and pass it to the view $roles = Role::get(); return view('users.create', ['roles'=>$roles]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users', 'password'=>'required|min:6|confirmed' ]); $user = User::create($request->only('email', 'name', 'password')); //Retrieving only the email and password data $roles = $request['roles']; //Retrieving the roles field //Checking if a role was selected if (isset($roles)) { foreach ($roles as $role) { $role_r = Role::where('id', '=', $role)->firstOrFail(); $user->assignRole($role_r); //Assigning role to user } } //Redirect to the users.index view and display message return redirect()->route('users.index') ->with('flash_message', 'User successfully added.'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('users'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $user = User::findOrFail($id); //Get user with specified id $roles = Role::get(); //Get all roles return view('users.edit', compact('user', 'roles')); //pass user and roles data to view } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $user = User::findOrFail($id); //Get role specified by id //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users,email,'.$id, 'password'=>'required|min:6|confirmed' ]); $input = $request->only(['name', 'email', 'password']); //Retreive the name, email and password fields $roles = $request['roles']; //Retreive all roles $user->fill($input)->save(); if (isset($roles)) { $user->roles()->sync($roles); //If one or more role is selected associate user to roles } else { $user->roles()->detach(); //If no role is selected remove exisiting role associated to a user } return redirect()->route('users.index') ->with('flash_message', 'User successfully edited.'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { //Find a user with a given id and delete $user = User::findOrFail($id); $user->delete(); return redirect()->route('users.index') ->with('flash_message', 'User successfully deleted.'); } }

Here the User class, the Role class, the Permission class and the Auth class are imported. In the constructor the auth middleware is called to make sure only authenticated users have access to the User resource. A custom middleware isAdmin is also called. This checks if the authenticated user has administrator privileges. This middleware will be created later.

The index() method gets all users from the Users table and passes it to the index view which will display all users in a table. The create() method first gets all the Roles from the Roles table and passes it to the create view. This is so that Roles can be added when creating a User.

The store() method saves the input from the create view, after validating the input, looping through the Roles that was passed in the form and assigning these Roles to the User. The show()method just redirects back to the users page as for this demonstration, we wont need to show each user individually.

The edit() method gets the user corresponding to the id passed, then gets all roles and passes it to the edit view. The update() method validates data from the edit view and saves the updated name and password fields. It gets all roles from the roles table and while looping through them, removes any role assign to the user. It then takes the role data inputted from the form, matches them with the values in the databases and assigns these roles to the user.

The destroy() method allows us to delete a user along with it's corresponding role.

User Views

Three views are needed here: index, create and edit views. The index view would contain a table that lists all our users and their roles.

{{-- \resources\views\users\index.blade.php --}} @extends('layouts.app') @section('title', '| Users') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-users"></i> User Administration <a href="{{ route('roles.index') }}" class="btn btn-default pull-right">Roles</a> <a href="{{ route('permissions.index') }}" class="btn btn-default pull-right">Permissions</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Date/Time Added</th> <th>User Roles</th> <th>Operations</th> </tr> </thead> <tbody> @foreach ($users as $user) <tr> <td>{{ $user->name }}</td> <td>{{ $user->email }}</td> <td>{{ $user->created_at->format('F d, Y h:ia') }}</td> <td>{{ $user->roles()->pluck('name')->implode(' ') }}</td>{{-- Retrieve array of roles associated to a user and convert to string --}} <td> <a href="{{ route('users.edit', $user->id) }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['users.destroy', $user->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ route('users.create') }}" class="btn btn-success">Add User</a> </div> @endsection

The create view is just a form that allows us to create new users and assign roles to them.

{{-- \resources\views\users\create.blade.php --}} @extends('layouts.app') @section('title', '| Add User') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-user-plus'></i> Add User</h1> <hr> {{ Form::open(array('url' => 'users')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('email', 'Email') }} {{ Form::email('email', '', array('class' => 'form-control')) }} </div> <div class='form-group'> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach </div> <div class="form-group"> {{ Form::label('password', 'Password') }}<br> {{ Form::password('password', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('password', 'Confirm Password') }}<br> {{ Form::password('password_confirmation', array('class' => 'form-control')) }} </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

The edit view is a form that allows us to edit users and their roles. Using Laravel's form model binding the form is automatically populated with the previous values.

{{-- \resources\views\users\edit.blade.php --}} @extends('layouts.app') @section('title', '| Edit User') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-user-plus'></i> Edit {{$user->name}}</h1> <hr> {{ Form::model($user, array('route' => array('users.update', $user->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with user data --}} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('email', 'Email') }} {{ Form::email('email', null, array('class' => 'form-control')) }} </div> <h5><b>Give Role</b></h5> <div class='form-group'> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id, $user->roles ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach </div> <div class="form-group"> {{ Form::label('password', 'Password') }}<br> {{ Form::password('password', array('class' => 'form-control')) }} </div> <div class="form-group"> {{ Form::label('password', 'Confirm Password') }}<br> {{ Form::password('password_confirmation', array('class' => 'form-control')) }} </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Permission Controller

Now let's tackle the PermissionControllerCreate the file and paste the following code:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; use Session; class PermissionController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $permissions = Permission::all(); //Get all permissions return view('permissions.index')->with('permissions', $permissions); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $roles = Role::get(); //Get all roles return view('permissions.create')->with('roles', $roles); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { $this->validate($request, [ 'name'=>'required|max:40', ]); $name = $request['name']; $permission = new Permission(); $permission->name = $name; $roles = $request['roles']; $permission->save(); if (!empty($request['roles'])) { //If one or more role is selected foreach ($roles as $role) { $r = Role::where('id', '=', $role)->firstOrFail(); //Match input role to db record $permission = Permission::where('name', '=', $name)->first(); //Match input //permission to db record $r->givePermissionTo($permission); } } return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('permissions'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $permission = Permission::findOrFail($id); return view('permissions.edit', compact('permission')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $permission = Permission::findOrFail($id); $this->validate($request, [ 'name'=>'required|max:40', ]); $input = $request->all(); $permission->fill($input)->save(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $permission = Permission::findOrFail($id); //Make it impossible to delete this specific permission if ($permission->name == "Administer roles & permissions") { return redirect()->route('permissions.index') ->with('flash_message', 'Cannot delete this Permission!'); } $permission->delete(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission deleted!'); } }

In the store() method, we are making it possible for a role to be selected as a permission is created. After validating and saving the permission name field, a check is done if a role was selected if it was, a permission is assigned to the selected role.

Permission View

Three views are needed here as well. The index view would list in a table all the available permissions, the create view is a form which would be used to create a new permission and the edit view is a form that let's us edit existing permission.

{{-- \resources\views\permissions\index.blade.php --}} @extends('layouts.app') @section('title', '| Permissions') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-key"></i>Available Permissions <a href="{{ route('users.index') }}" class="btn btn-default pull-right">Users</a> <a href="{{ route('roles.index') }}" class="btn btn-default pull-right">Roles</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Permissions</th> <th>Operation</th> </tr> </thead> <tbody> @foreach ($permissions as $permission) <tr> <td>{{ $permission->name }}</td> <td> <a href="{{ URL::to('permissions/'.$permission->id.'/edit') }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['permissions.destroy', $permission->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ URL::to('permissions/create') }}" class="btn btn-success">Add Permission</a> </div> @endsection

The following is the create view

{{-- \resources\views\permissions\create.blade.php --}} @extends('layouts.app') @section('title', '| Create Permission') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Add Permission</h1> <br> {{ Form::open(array('url' => 'permissions')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} </div><br> @if(!$roles->isEmpty()) //If no roles exist yet <h4>Assign Permission to Roles</h4> @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }}<br> @endforeach @endif <br> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

And finally the edit view:

@extends('layouts.app') @section('title', '| Edit Permission') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Edit {{$permission->name}}</h1> <br> {{ Form::model($permission, array('route' => array('permissions.update', $permission->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with permission data --}} <div class="form-group"> {{ Form::label('name', 'Permission Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <br> {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Role Controller

The RoleController is quite similar to the UserController. This controller will allow us to create roles and assign one or more permissions to a role. Create the file and paste the following code:

<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use Auth; //Importing laravel-permission models use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; use Session; class RoleController extends Controller { public function __construct() { $this->middleware(['auth', 'isAdmin']);//isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $roles = Role::all();//Get all roles return view('roles.index')->with('roles', $roles); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $permissions = Permission::all();//Get all permissions return view('roles.create', ['permissions'=>$permissions]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name and permissions field $this->validate($request, [ 'name'=>'required|unique:roles|max:10', 'permissions' =>'required', ] ); $name = $request['name']; $role = new Role(); $role->name = $name; $permissions = $request['permissions']; $role->save(); //Looping thru selected permissions foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Fetch the newly created role and assign permission $role = Role::where('name', '=', $name)->first(); $role->givePermissionTo($p); } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('roles'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $role = Role::findOrFail($id); $permissions = Permission::all(); return view('roles.edit', compact('role', 'permissions')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $role = Role::findOrFail($id);//Get role with the given id //Validate name and permission fields $this->validate($request, [ 'name'=>'required|max:10|unique:roles,name,'.$id, 'permissions' =>'required', ]); $input = $request->except(['permissions']); $permissions = $request['permissions']; $role->fill($input)->save(); $p_all = Permission::all();//Get all permissions foreach ($p_all as $p) { $role->revokePermissionTo($p); //Remove all permissions associated with role } foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Get corresponding form //permission in db $role->givePermissionTo($p); //Assign permission to role } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $role = Role::findOrFail($id); $role->delete(); return redirect()->route('roles.index') ->with('flash_message', 'Role deleted!'); } } Roles View

Three views are needed here as well. The index view to display available roles and associated permissions, the create view to add a new role and a view to edit an existing role. Create the index.blade.php file and paste the following:

{{-- \resources\views\roles\index.blade.php --}} @extends('layouts.app') @section('title', '| Roles') @section('content') <div class="col-lg-10 col-lg-offset-1"> <h1><i class="fa fa-key"></i> Roles <a href="{{ route('users.index') }}" class="btn btn-default pull-right">Users</a> <a href="{{ route('permissions.index') }}" class="btn btn-default pull-right">Permissions</a></h1> <hr> <div class="table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th>Role</th> <th>Permissions</th> <th>Operation</th> </tr> </thead> <tbody> @foreach ($roles as $role) <tr> <td>{{ $role->name }}</td> <td>{{ str_replace(array('[',']','"'),'', $role->permissions()->pluck('name')) }}</td>{{-- Retrieve array of permissions associated to a role and convert to string --}} <td> <a href="{{ URL::to('roles/'.$role->id.'/edit') }}" class="btn btn-info pull-left" style="margin-right: 3px;">Edit</a> {!! Form::open(['method' => 'DELETE', 'route' => ['roles.destroy', $role->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} </td> </tr> @endforeach </tbody> </table> </div> <a href="{{ URL::to('roles/create') }}" class="btn btn-success">Add Role</a> </div> @endsection

For the create view:

@extends('layouts.app') @section('title', '| Add Role') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Add Role</h1> <hr> {{ Form::open(array('url' => 'roles')) }} <div class="form-group"> {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <h5><b>Assign Permissions</b></h5> <div class='form-group'> @foreach ($permissions as $permission) {{ Form::checkbox('permissions[]', $permission->id ) }} {{ Form::label($permission->name, ucfirst($permission->name)) }}<br> @endforeach </div> {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection

And for the edit view:

@extends('layouts.app') @section('title', '| Edit Role') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><i class='fa fa-key'></i> Edit Role: {{$role->name}}</h1> <hr> {{ Form::model($role, array('route' => array('roles.update', $role->id), 'method' => 'PUT')) }} <div class="form-group"> {{ Form::label('name', 'Role Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} </div> <h5><b>Assign Permissions</b></h5> @foreach ($permissions as $permission) {{Form::checkbox('permissions[]', $permission->id, $role->permissions ) }} {{Form::label($permission->name, ucfirst($permission->name)) }}<br> @endforeach <br> {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} </div> @endsection Middleware

To restrict access to the roles and permissions page, a middleware was included called isAdmin in our PermissionController and RoleController. This middleware counts how many users are in the Users table, and if there are more than one users, it checks if the current authenticated User has the permission to 'Administer roles & permissions'. To create a permission visit http://localhost:8000/permissions/create. Then go to http://localhost:8000/roles/create to create a role, to which you can now assign the permission you created. For example you can create a permission called 'Administer roles & permissions' and a 'Admin' role to which you would assign this permission. Create the AdminMiddleware in the directory app/Http/Middleware/ and enter the following code:

<?php namespace App\Http\Middleware; use Closure; use Illuminate\Support\Facades\Auth; use App\User; class AdminMiddleware { /** * Handle an incoming request. * * @param \Illuminate\Http\Request $request * @param \Closure $next * @return mixed */ public function handle($request, Closure $next) { $user = User::all()->count(); if (!($user == 1)) { if (!Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user does //not have this permission { abort('401'); } } return $next($request); } }

A middleware called clearance was also included in our PostController. This middleware would check if a user has the permissions Administer roles & permissions, Create Post, Edit Post and Delete Post.

<?php namespace App\Http\Middleware; use Closure; use Illuminate\Support\Facades\Auth; class ClearanceMiddleware { /** * Handle an incoming request. * * @param \Illuminate\Http\Request $request * @param \Closure $next * @return mixed */ public function handle($request, Closure $next) { if (Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user has this //permission { return $next($request); } if ($request->is('posts/create'))//If user is creating a post { if (!Auth::user()->hasPermissionTo('Create Post')) { abort('401'); } else { return $next($request); } } if ($request->is('posts/*/edit')) //If user is editing a post { if (!Auth::user()->hasPermissionTo('Edit Post')) { abort('401'); } else { return $next($request); } } if ($request->isMethod('Delete')) //If user is deleting a post { if (!Auth::user()->hasPermissionTo('Delete Post')) { abort('401'); } else { return $next($request); } } return $next($request); } }

Add AdminMiddleware::class and ClearanceMiddleware::class to the $routeMiddleware property of /app/Http/kernel.php like this:

protected $routeMiddleware = [ 'auth' => \Illuminate\Auth\Middleware\Authenticate::class, 'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class, 'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class, 'can' => \Illuminate\Auth\Middleware\Authorize::class, 'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class, 'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class, 'isAdmin' => \App\Http\Middleware\AdminMiddleware::class, 'clearance' => \App\Http\Middleware\ClearanceMiddleware::class, ];

In both middelwares a 401 exception would be thrown if the conditions are not meet. Let's create a custom 401 error page:

{{-- \resources\views\errors\401.blade.php --}} @extends('layouts.app') @section('content') <div class='col-lg-4 col-lg-offset-4'> <h1><center>401<br> ACCESS DENIED</center></h1> </div> @endsection Wrapping Up

First lets create an 'Admin' user and then create the necessary permissions and roles. Click on Register and create a user, then go to http://localhost:8000/permissions and create permissions to Create Post, Edit Post, Delete Post and Administer roles & permissions. After creating these permissions, your permissions page should look like this:

Next, you need to create roles to which you would add the Create, Edit and Delete Permissions. Click on Roles and create these roles:

  • Admin- A user assigned to this role would have all permissions
  • Owner- A user assigned to this role would have selected permissions assigned to it by Admin

Finally assign the Role of 'Admin' to the currently logged in User. Click on Users and then Edit. Check the Admin box under Give Role:

After assigning the 'Admin' role to our user, notice that you now have a new Admin link in the drop of the navigation, this links to our users page. Now create a new user and give it the more restrictive role of Owner. If you login as this user and try to visit the User, Role or Permission pages you get this as expected:

The Owner role does not have permission to Administer Roles & Users hence the exception is thrown.

To demonstrate how this works for posts, create a post by clicking on New Article. After creating the post, view the post and you would notice you have along with the Back button, an Edit and Delete button as shown below:

Now if you logout and view the post only the Back button will be available to us. This also works if you have a logged in user who does not have permissions to Edit or Delete Post.

Conclusion

The laravel-permission package makes it relatively easy to build a role and permission system. To recap we have considered installation of the laravel-permission package, laravel-permission blade directives, creating a custom middleware and implementing an access control list in a Laravel application. You can look at the final product on Github and if you have any questions or comments, don’t hesitate to post them below.

Setup high availability for ProxySQL via KeepAlived in AWS

Usually application do not connect directly to Percona XtraDB Cluster, but go through a proxy – ProxySQL, for instance. However if only one proxy node is used it becomes a single point of failure. Not long ago Marco Tusa wrote about how to configure two ProxySQL nodes in front of XtraDB cluster. If deployed on EC2 instances it doesn’t work that way because Amazon doesn’t allow to assign secondary IP address on an interface.

This post describes how to configure highly available ProxySQL with keepalived, proxysql-tools and AWS Elastic Network Interface(ENI).
The application connects to a single Virtual IP. The VIP is assigned to ENI which is managed by keepalived. proxysql-tools moves ENI between ProxySQL instances and monitor health of XtraDB Cluster nodes.

Setup Percona XtraDB Cluster

Node #1: 172.31.8.51 Node #2: 172.31.12.128 Node #3: 172.31.3.159

Setting up XtraDB Cluster is pretty straightforward and not different from any other case.

# wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb # sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb # sudo apt-get update # sudo apt-get install percona-xtradb-cluster-57 # sudo service mysql stop

MySQL configuration file on other nodes differs only in wsrep_node_address.

# cat /etc/mysql/my.cnf !includedir /etc/mysql/conf.d/ [mysqld] datadir=/var/lib/mysql user=mysql wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_address=gcomm://172.31.8.51,172.31.12.128,172.31.3.159 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_node_address=172.31.8.51 wsrep_sst_method=xtrabackup-v2 wsrep_cluster_name=cluster_1 wsrep_sst_auth="sstuser:s3cretPass"

 

After this step, We can bootstrap our cluster. I have made in from first node by next command:

# /etc/init.d/mysql bootstrap-pxc

When the first node has been started, cluster status can be checked by:

mysql&gt; show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | cc00ee27-3433-11e7-84e5-4a9beaabe9c9 | ... | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | ... | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON | +----------------------------+--------------------------------------+

When all nodes are running, wsrep_cluster_size will be equal to number of clusters.
Cluster setup is complete at this step.

Setup ProxySQL and KeepAlived

ProxySQL active: 172.31.24.212 ProxySQL passive: 172.31.19.155 ENI address: 172.31.26.237

It’s worth noting ProxySQL instances and ENI must be on the same subnet.

First, We must to configure our ProxySQL instances for proxy request to cluster nodes.

# apt install proxysql # apt-get install percona-xtradb-cluster-client-5.7

Now, we’ll setup ProxySQL for work with our Galera nodes. I will use default hostgroup.

# service proxysql start # mysql -u admin -p -h 127.0.0.1 -P 6032 mysql@proxysql1&gt; INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.8.51',3306); mysql@proxysql1&gt; INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.12.128',3306); mysql@proxysql1&gt; INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.3.159',3306);

Now, we must create user for monitoring Percona XtraDB Cluster nodes in ProxySQL. You can do it on any node of cluster.

mysql@node1&gt; CREATE USER 'monitor'@'%' IDENTIFIED BY '*****'; mysql@node1&gt; GRANT USAGE ON *.* TO 'monitor'@'%';

And update settings in proxysql:

mysql@proxysql1&gt; UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; mysql@proxysql1&gt; UPDATE global_variables SET variable_value='*****' WHERE variable_name='mysql-monitor_password'; mysql@proxysql1&gt; LOAD MYSQL VARIABLES TO RUNTIME; mysql@proxysql1&gt; SAVE MYSQL VARIABLES TO DISK;

After this changes, let’s try to see monitoring and ping logs:

mysql@proxysql1 SELECT * FROM monitor.mysql_server_ping_log DESC LIMIT 6; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 172.31.12.128 | 3306 | 1494492166885382 | 627 | NULL | | 172.31.3.159 | 3306 | 1494492166887154 | 586 | NULL | | 172.31.8.51 | 3306 | 1494492166888947 | 501 | NULL | | 172.31.12.128 | 3306 | 1494492176885541 | 596 | NULL | | 172.31.3.159 | 3306 | 1494492176887442 | 599 | NULL | | 172.31.8.51 | 3306 | 1494492176889317 | 527 | NULL | +---------------+------+------------------+----------------------+------------+ 6 rows in set (0.00 sec)

The previous examples show that ProxySQL is able to connect and ping the nodes you added.
To enable monitoring of these nodes, load them at runtime:

mysql@proxysql1&gt; LOAD MYSQL SERVERS TO RUNTIME;

Now, we must to create user that will work with our XtraDB Cluster nodes.
To add a user, insert credentials into mysql_users table:

mysql@proxysql1&gt; INSERT INTO mysql_users (username,password) VALUES ('proxy_user','*****'); Query OK, 1 row affected (0.00 sec) mysql@proxysql1&gt; LOAD MYSQL USERS TO RUNTIME;

To provide read/write access to the cluster for ProxySQL, add this user on one of the Percona XtraDB Cluster nodes:

mysql@node2&gt; CREATE USER 'proxy_user'@'%' IDENTIFIED BY '*****'; Query OK, 0 rows affected (0.01 sec) mysql@node2&gt; GRANT ALL ON *.* TO 'proxy_user'@'%'; Query OK, 0 rows affected (0.00 sec)

proxysql-tools can monitor health of XtraDB Cluster nodes and take out of rotation failed nodes.

Install it to your proxysql instances using pip install proxysql-tools  and add it into ProxySQL scheduler:

mysql@proxysql1&gt; INSERT INTO scheduler (id,interval_ms,filename,arg1,arg2) VALUES (1, '15000', '/usr/local/bin/proxysql_tools', 'galera', 'register'); mysql@proxysql1&gt; LOAD SCHEDULER TO RUNTIME; To make sure that the script has been loaded, check the runtime_scheduler table: mysql@proxysql1&gt; SELECT * FROM runtime_schedulerG *************************** 1. row *************************** id: 1 active: 1 interval_ms: 15000 filename: /usr/local/bin/proxysql_tools arg1: galera arg2: register arg3: NULL arg4: NULL arg5: NULL comment: 1 row in set (0.00 sec)

Repeat steps above for another ProxySQL instance.
Now, ProxySQL instances are configured. But we haven’t added high availability yet.

The idea is following.

keepalived on each of ProxySQL nodes will monitor each other. When one of the nodes becomes active we will attach ENI to the active node and configure VIP on it. The application will use the VIP to connect to MySQL. No configuration changes are needed when the active ProxySQL node changes.

So, install KeepAlived on ProxySQL instances and create the network interface in AWS that will be in same subnet with ProxySQL instances.

proxysql-tools attaches network interface to an active node. Let’s configure it:
Create config file /etc/twindb/proxysql-tools.cfg and add content below:

# cat /etc/twindb/proxysql-tools.cfg [proxysql] # ProxySQL admin interface connectivity information admin_host=127.0.0.1 admin_port=6032 admin_username=admin admin_password=*** # MySQL user used by ProxySQL monitoring module to monitor MySQL servers monitor_username=monitor monitor_password=*** # Virtual IP for HA configuration virtual_ip=172.31.26.237 virtual_netmask=255.255.240.0 [aws] aws_access_key_id=*** aws_secret_access_key=*** aws_default_region=***

Virtual IP is address of your ENI. After this, we must configure KeepAlived.

# cat /etc/keepalived/keepalived.conf global_defs { notification_email { root@localhost } notification_email_from keepalived@localhost smtp_server localhost smtp_connect_timeout 30 } vrrp_script chk_proxysql { script "/usr/bin/mysqladmin ping -h 127.0.0.1 -P 3306 &gt; /dev/null 2&gt;&amp;1" interval 1 timeout 1 fall 3 rise 3 user root } vrrp_instance proxysql_instance { notify_master "/usr/local/bin/proxysql-tool aws notify_master" virtual_router_id 41 state BACKUP interface eth0 dont_track_primary unicast_peer { 172.31.19.155 } priority 200 authentication { auth_type PASS auth_pass 1066 } track_script { chk_proxysql } nopreempt debug

unicast_peer is the IP on eth0 of other ProxySQL node proxysql-tool with arguments aws notify_master attach ENI Virtual IP to the instance. On passive ProxySQL, config is same except unicast_peer:

# cat /etc/keepalived/keepalived.conf global_defs { notification_email { root@localhost } notification_email_from keepalived@localhost smtp_server localhost smtp_connect_timeout 30 } vrrp_script chk_proxysql { script "/usr/bin/mysqladmin ping -h 127.0.0.1 -P 3306 &gt; /dev/null 2&gt;&amp;1" interval 1 timeout 1 fall 3 rise 3 user root } vrrp_instance proxysql_instance { notify_master "/usr/local/bin/proxysql-tool aws notify_master" virtual_router_id 41 state BACKUP interface eth0 dont_track_primary unicast_peer { 172.31.19.212 } priority 200 authentication { auth_type PASS auth_pass 1066 } track_script { chk_proxysql } nopreempt debug

And now you can start keepalived as service. One of the ProxySQL nodes will become active, /usr/local/bin/proxysql-tool aws notify_master will move the ENI to the active node and assign the VIP to it.

The post Setup high availability for ProxySQL via KeepAlived in AWS appeared first on Backup and Data Recovery for MySQL.

Performance regression between Percona Server 5.6 & 5.7

I) An upgrade which hasn’t gone as planned

One of our customer needed help after trying to upgrade their slave from Percona Server 5.6 to Percona Server 5.7. (here 5.7.17 is used)
Indeed, from time to time, the replication lag was linearly growing, which could be quite annoying…

(good guest, 5.7 is the red dashed line!)

Their use case is quite interesting :

  • a lot of Queries Per Second (read & write, nearly 10k queries/s on this slave)
  • a lot of tables (about 600k spread across different databases)
  • a lot of writes
  • 24 CPUs, and 386G of RAM
  • A big buffer pool (270G)
  • not that fast SSD disks.

Because we have a high QPS but also a lot of writes, we need to fine tune the innodb settings.

The idea is to make sure to not flush too fast on disk the dirty pages in the innodb buffer pool, and to have enough room in the redo log. The most important settings to maintain a good balance are:

  • innodb_io_capacity=2000
  • innodb_io_capacity_max=12000
  • innodb_log_file_size=32G

Other parameters like the innodb_buffer_pool_instances, innodb_flush_neighbors, innodb_lru_scan_depth, innodb_purge_threads or innodb_page_cleaners… have been fine tuned as well, but it doesn’t impact much the flushing speed of the dirty pages.

In practical, with those settings, the server has about 24% of dirty pages, and 20% of redo log used in average under a “normal” usage.

It seems to be not bad, so why the server is sometimes so slow?

II) Let’s investigate! a) The LRU manager

I have first investigated if the innodb buffer pool flush was working properly. Although it was not the main cause of the performance regression, I noticed a new threaded LRU (Least Recently Used) manager has been introduced in XtraDB 5.7. Basically, for each buffer pool instance, a list of dirty pages is stored in an LRU queue. The job of this LRU manager is to flush a few dirty pages, when there are not enough free pages available in the buffer pool.

However, the mecanism to wake up those threads is not optimal: it’s based on a timer which will wake up the thread every 1s + an extra time which is auto-ajusted depending on the size of the buffer pool free list and what happened during the last thread execution.

With this implementation, XtraDB is spending a lot of CPU cycle sleeping (thru the use of the os_thread_sleep function). A better implementation would be perhaps to use an event wait which would trigger a thread wake up once the free list reaches some defined threshold.

As a result, I opened a bug report on percona server to report this issue:

https://bugs.launchpad.net/percona-server/+bug/1690399

For the record, Mark Callaghan already opened a few years ago a bug report on MySQL asking to try to remove the os_thread_sleep calls : https://bugs.mysql.com/bug.php?id=68588

I’ve just noticed XtraDB 5.7.18 included a few improvement in the LRU Manager, we need to test if it improves things: https://bugs.launchpad.net/percona-server/+bug/1631309

 

b) ALTER TABLE are slow!

Well actually, it’s not always the case, that’s why it was tricky to track it down.
It’s worth noting this ALTER TABLE issue in an upstream bug, from MySQL 5.7 (thanks Marko Mäkelä for noticing it). So this one is affecting Percona Server 5.7, MySQL 5.7 and MariaDB 10.2.

The workload of this customer can create from time to time a few new tables, fill them, and use an ALTER TABLE to add new indexes.

In the introduction, I mentioned the server has been fine tuned to avoid flushing too much the buffer pool. It means the amount of dirty pages is potentially high, depending on the size of the buffer pool. In our case, with 24% of dirty pages, it represents about 4.2m of dirty pages.

The issue in 5.7 is that when we rebuild an index, the function FlushObserver:flush is called to flush the dirty pages associated with the modified table. Before doing the flush, it tries to count the number of dirty pages to estimate the amount of time the operation could take. Unfortunately, the implementation of this count is really inefficient : it iterates on all the buffer pool instances, and for each buffer pool, on all the dirty pages, to try to find those one corresponding to the modified table.

With 4.2m of dirty pages, it means it iterates on 4.2m dirty pages (even if the table is empty!).

Here are the performance results:

I created another bug report for this specific issue : https://bugs.launchpad.net/percona-server/+bug/1690588

Let’s hope they will fix those issues quickly!

The post Performance regression between Percona Server 5.6 & 5.7 appeared first on Softizy Blog.

ProxySQL and Mirroring what about it?

{autotoc enabled=yes}

 

Overview

I love ProxySQL, I think it is a great component for expanding architecture flexibility and HA, but not all what shine is gold.

Let me make clear that I only want to set the expectations right, and avoid to sell carbon for gold. Carbon has it's own use, gold has another. 

 

First of all let me clarify what is mirroring for me (and hope most of you).

Then we need to cover the basic of how ProxySQL manage the (I cannot say mirroring) traffic dispatch.

ProxySQL receive a connection from the application, and through it we can have a simple SELECT or a more complex transaction. ProxySQL will get each query, pass it to the QueryProcessor, process the query, identify if the query is mirrored, duplicate the whole mysql session ProxySQL internal object and associate it to a mirror queue, which refer to a mirror threads pool.


If the pool is free (has an available active slot in the concurrent active threads set) then the query is processed right away, if not it will stay in the queue. If the queue is full, the query is lost.

Whatever is returned from the query goes to /dev/null, as such no result set is passed back to client.

The whole process is not free for a server, actually if you will check the CPU utilization you will see that the “mirroring” in ProxySQL will actually double the CPU utilization. Meaning that also the traffic on server A will be impacted because resource contention.

Summarizing ProxySQL will:

  • Send the query for execution in different order
  • Completely ignore any transaction isolation
  • Have different number of query executed on B respect to A
  • Add significant load on the server resources

Comparing this with the point and expectations I mention in the reasoning at the end of this article, it is quite clear to me that at the moment we cannot consider ProxySQL as a valid mechanism to duplicate consistent load from server A to server B.

 

Personally, I don’t think that ProxySQL development Team (Rene’ :D), should waste his time on fixing this part, there are so many other things to cover and improve on ProxySQL.

After having work extensively with ProxySQL and have done deep QA on mirroring, I think that either we keep it as basic blind traffic dispatcher or a full re-conceptualization is required.

Setup

But once we have clarified that, we can still see ProxySQL “traffic dispatch” (cannot say mirroring really) as a very interesting feature, that may result useful in many ways, especially because it is so easy to setup.

The following is the result of tests I had performed, which should help in setting correct expectations.

Tests were simple, load data in a PXC cluster and use ProxySQL to replicate the load on a MySQL master-slave environment.

Machines for MySQL/PXC where VM with CentOS 7, 4 CPU 3 GB RAM, attached storage.

Machine for ProxySQL VM CentOS 7, 8 CPU 8GB RAM.

Why I choose to give ProxySQL such higher  volume of resources?

I knew in advance I may need to play a bit with a couple of settings requiring more memory and CPU cycles and I want to be sure I don’t get any problem from ProxySQL in relation to CPU and Memory.

The application that I was using to add load is a Java application I develop to perform my tests. App is https://github.com/Tusamarco/blogs/blob/master/stresstool_base_app.tar.gz, the whole set I had used to do the test is here

I had used 4 different tables,

1 2 3 4 5 6 7 8 +------------------+ | Tables_in_mirror | +------------------+ | mirtabAUTOINC | | mirtabMID | | mirtabMIDPart | | mirtabMIDUUID |  

 

For full table definition see here

 

ProxySQL setup

Ok so let start.

First setup ProxySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 DELETE FROM mysql_servers WHERE hostgroup_id IN (500,501,700,701); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',500,3306,60000,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',501,3306,100,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.21',501,3306,20000,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.231',501,3306,20000,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',700,3306,1,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',701,3306,1,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.25',701,3306,1,400); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.43',701,3306,1,400); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;   DELETE FROM mysql_users WHERE username='load_RW'; INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) VALUES ('load_RW','test',1,500,'test',1); LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;   DELETE FROM mysql_query_rules WHERE rule_id=202; INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,apply) VALUES(202,'load_RW',500,700,1,3,1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK  

 

Tests results

Now the action. 

Test1

The first test is mainly a simple functional test during which I insert records using 1 single thread in PXC and in MySQL.

No surprise here I have 3000 loops and at the end I have 3000 records on both platforms.

To have a baseline we can see that ProxySQL CPU utilization is quite low

 

AT the same time the number of "questions" against PXC and MySQL very similar:
PXC

MySQL

 

The other two metrics we want to keep an eye on are Mirror_concurrency and Mirror_queue_length this two refer respectively to mysql-mirror_max_concurrency and mysql-mirror_max_queue_length.

The two new variables and metrics were introduced in ProxySQL 1.4.0 with the intent to control and manage the load ProxySQL generate internally related to the mirroring feature.

 

In this case as you can see we have a max of 3 concurrent connections and 0 queue entry, all good.

Now that we have a baseline, and that we know at functional level "it works" let see what happen increasing the load.

 

Test 2.

Scope of the test was to identify how ProxySQL will behave with standard configuration and increasing load.

It comes up that as soon as ProxySQL has a little bit more load, it will start to loose some query along the way.

Executing 3000 loop for 40 threads, insert only will result in 120,000 rows inserted in all the 4 tables in PXC but the table in the Secondary (mirrored) platform will only have a variable number between 101,359 and 104,072. Showing consistent lost of data.

Reviewing the insight and comparing the connections running in PXC and the secondary we can see that, as expected, the PXC number of connections is scaling serving the number of incoming requests, while the connections on the Secondary are limited by the default value of mysql-mirror_max_concurrency=16.

 

Is also interesting to notice that to process the queue of transaction existing in ProxySQL the connection on the Secondary persist longer than the connection in PXC.

 

As we can see above the queue as an evident bell curve reaching the 6K entries which is quite below the mysql-mirror_max_queue_length limit (32K). Yet the queries were drop by ProxySQL, which indicate the queue is not really enough to accommodate the pending work.

 

CPU wise ProxySQL as expected take a bit more cycles, but nothing crazy and the overhead for the simple mirroring queue processing can be see when the main load stops around 12:47.

 

Another interesting graph to keep an eye on is the one describing the executed command inside PXC and the Secondary:

PXC

Secondary

 

As you can see the traffic on the Secondary was significantly less 669 average than PXC 1.17K. Then it spikes when the main load on the PXC node terminates.

In short it is quite clear that ProxySQL is not able here to scale following the traffic existing in PXC and actually loosing significant amount of data on the Secondary.

Doubling the load in the Test3 show the same behavior, having ProxySQL reach his limit for the traffic duplication.

 

But can this be optimized?

Of course yes, this is what the mysql-mirror_max_concurrency is for, lets see what is going to happen if I increase the value from 16 to 100 just to make it crazy high.

 

Test 4

2 app node writing.

I am jumping the description of test 3 because is mainly the same of Test 2 with more load.

 

The first thing that is coming to the attention is that both PXC and secondary report same number of rows in the tables (240,000). That is a first good win.

 

Second the number of running connections:

Lines now are much closer and the queue just drop to few entries.

 

Commands executed in PXC:

And commands executed in the Secondary:

Average execution report the same value, and very similar trend.

 

Finally, what was the CPU cost and effect?

PXC:

 

Secondary:

As expected some difference in the CPU usage distribution exists, but the trend is consistent between the two nodes and with the operations.

 

The ProxySQL CPU utilization is definitely higher than before:

But absolutely manageable, and still reflecting the initial distribution.

 

Finally what about CRUD now?
So far I had only tested the insert operation, but what happen if we run a full CRUD set of tests?

 

Test 7 Crud

First of all, let us review the executed commands, in PXC

And Secondary

While in appearance we have very similar workload, selects aside the behavior will significantly diverge.
This because in the Secondary the different operations are not encapsulated by the transaction and executed as they are received.  We can see significant difference in update and delete operations between the two.

 

Also the threads in execution will show a different picture between the two platform.

PXC

Secondary

It appears quite clear that PXC is constantly having more running threads and more connections.

Never the less both platform process similar total number of questions.

PXC

Secondary

Both having an average around 1.17K/second questions.

This is also another indication of how much the behavior is impacted when we have concurrent operation but no respect of the isolation or execution order.

Different behavior that is also clear reviewing the CPU utilization.

PXC

Secondary

 

Conclusions

To close this long article, I want to go back to the start.

We cannot consider the mirror function in ProxySQL as a real mirroring, but more a traffic redirection.

 

Use ProxySQL with this approach, is still partially effective in testing the load and the effect it may have on a secondary platform.
As we know data consistency is not guarantee in this scenario, and Selects, Updates and Delete are affected by this given the different data-set and result-set they will manage.

Given that, the server behaviors will change between original and mirror, if not in the quantity in the quality.

I am convinced that when we need a tool able to test our production load on a different or new platform, we will do better to look to something else, like Query Playback recently reviewed and significantly patch by DropBox (https://github.com/Percona-Lab/query-playback).

At the end ProxySQL is already a cool tool and if it doesn't cover this, well I can live with that, I am interested to have it working as it should and does in many other functionalities.

 

Acknowledgments

As usual to Rene' who had work on fixing and introducing new functionalities associated to the mirror, like the queue and concurrency control.

To the Percona team who develop PMM, all the graphs here (except 3) comes from PMM, and some of them are my customization.

Percona Server for MySQL 5.7.18-14 is Now Available

Percona announces the GA release of Percona Server for MySQL 5.7.18-14 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.7.18, including all the bug fixes in it, Percona Server for MySQL 5.7.18-14 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.18-14 milestone at Launchpad.

New Features: Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • In-place upgrade from Percona Server 5.6 to 5.7 by using standalone packages would fail if /var/lib/mysql wasn’t defined as the datadir. Bug fixed #1687276.
  • Combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Processing GTIDs in the relay log that were already been executed were causing write/fsync amplification. Bug fixed #1669928 (upstream #85141).
  • Text/BLOB fields were not handling sorting of the empty string consistently between InnoDB and filesort. Bug fixed #1674867 (upstream #81810) by porting a Facebook patch for MySQL.
  • InnoDB adaptive hash index was using a partitioning algorithm which would produce uneven distribution when the server contained many tables with an identical schema. Bug fixed #1679155 (upstream #81814).
  • For plugin variables that are signed numbers, doing a SHOW VARIABLES would always show an unsigned number. Fixed by porting a Facebook patch for MySQL.

Other bugs fixed: #1629250 (upstream #83245), #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1670588 (upstream #84173), #1672389, #1674507, #1675623, #1650294, #1659224, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1677943, #1678692, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, #1687386, #1687432, #1687600, and #1674281.

The release notes for Percona Server for MySQL 5.7.18-14 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

Percona Server for MySQL 5.6.36-82.0 is Now Available

Percona announces the release of Percona Server for MySQL 5.6.36-82.0 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.36, and including all the bug fixes in it, Percona Server for MySQL 5.6.36-82.0 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.36-82.0 milestone on Launchpad.

New Features: Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992.
  • tokubackup_slave_info file was created for a master server after taking the backup with Percona TokuBackup. Bug fixed #135.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • The combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug in Percona Server 5.6.28-76.1 caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Creating a compression dictionary with innodb_fake_changes enabled could lead to a server crash. Bug fixed #1629257.

Other bugs fixed: #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1683456, #1670588 (upstream #84173), #1672389, #1674507, #1674867, #1675623, #1650294, #1659224, #1660565, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1678692, #1678792, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, and #1674281.

Release notes for Percona Server for MySQL 5.6.36-82.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

The Perfect Server - Ubuntu 17.04 (Zesty Zapus) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1

This tutorial shows how to install an Ubuntu 17.04 (Zesty Zapus) server (with Apache2, BIND, Dovecot) for the installation of ISPConfig 3.1, and how to install ISPConfig. ISPConfig 3 is a web hosting control panel that allows you to configure the following services through a web browser: Apache or nginx web server, Postfix mail server, Courier or Dovecot IMAP/POP3 server, MySQL, BIND or MyDNS nameserver, PureFTPd, SpamAssassin, ClamAV, and many more. This setup covers the installation of Apache (instead of nginx), BIND (instead of MyDNS), and Dovecot (instead of Courier).

Multi Tb migration Using mydumper

In this post I will explain how to transfer a multi terabyte size database between two MySQL instances using mydumper, which is a logical backup and restore tool that works in parallel. I will also cover the case where you need to transfer only a subset of data.

Big tables are often problematic because of the amount of time needed to do mysqldump/restore which is single threaded. It is not uncommon for this type of process to take several days.

From MySQL 5.6 and on, we also have transportable tablespaces, but there are some limitations with that approach.

Physical backups (e.g. xtrabackup) have the advantage to be faster, but there are some scenarios where you still need/want a logical backup (e.g migrating to RDS/Aurora/Google Cloud SQL).

Steps Install mydumper/myloader

The first thing you’ll need to do is get mydumper installed. Latest version at the time of this writing is 0.9.1 and you can get rpm here .

You can also manually compile as follows (the instructions are for RHEL-based system):

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz tar zxvf mydumper-0.6.2.tar.gz

You can also get the source from GitHub:

git clone https://github.com/maxbube/mydumper.git

Here’s how to compile mydumper. This will put the files on /usr/local/bin/

sudo yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake gcc-c++ cmake . make make install Export data using mydumper

By default, mydumper will work by creating several parallel processes (rule of thumb is 1 export process per core) that will read one table each and write table contents to one file for each table. In this case, I’m exporting City and State tables:

./mydumper -t 8 -B world_innodb -T City,State

This is an improvement over traditional mysqldump, but we can still do better. mydumper can split each table into chunks (e.g. 100k rows) and write each chunk to a separate file, allowing to parallelize the import later on. I have omitted the -T argument here, so all tables from world_innodb will be exported.

./mydumper -t 8 --rows=100000 -B world_innodb

If exporting Innodb tables only, it makes sense to use –trx-consistency-only so the tool uses less locking. You will still get the binlog file/pos needed to seed a slave.

./mydumper -t 8 --rows=100000 --trx-consistency-only -B world_innodb

You can also specify a regular expression to export only some databases, let’s say db1 and db2.

./mydumper -t 8 --rows=100000 --trx-consistency-only --regex '^(db1|db2)' -B world_innodb

Other options include the ability to compress the exported data on the fly, and also export triggers, code and events. Finally, I also recommend the use of –verbose option for added visibility into what each thread is doing.

Here is an example of the complete command:

./mydumper -t 8 \ --rows=100000 \ --regex '^(db1|db2)' \ --compress \ --triggers \ --routines \ --events \ -v 3 \ --compress \ --trx-consistency-only \ -B world_innodb \ --outputdir /data/export \ --logfile /data/mydumper.log

While running multiple threads, I noticed some contention related to adaptive hash index (this is on 5.5, I haven’t tested if this happens on other versions as well). Disabling AHI can have an impact on read queries, so if you can afford having the host out of production while the export is running (which is probably a good idea as you will be hitting it hard with reads), I recommend to disable AHI temporarily.

Import data using myloader

The load phase is the most painful part, usually taking way longer than the time it took to export data.

If you run mydumper using the –rows option as described above, several myloader threads can insert concurrently on the same table, speeding up the process. Otherwise, you only get multiple tables imported in parallel, which is helpful but reduces the benefits if you have a handful of huge tables and mostly small tables.

Other potential way to reduce the import time is temporarily relax consistency by setting innodb_flush_log_at_trx_commit=0 sync_binlog=0. Also set query_cache_type=0 AND query_cache_size=0 to prevent the query cache mutex from being used.

You can control myloader transaction size with queries-per-transaction parameter. Using the default value (1000) produced really big transactions, I had better results by reducing this to 100.

The -o option will drop the tables on the destination database if they already exist.

Here is an example of the command (rule of thumb in this case is have import_threads = cores / 2):

myloader --threads=4 -d /data/export/ -B db1 -q 100 -o -v 3

NOTE: myloader works by setting sql-log-bin=0 for the import session by default, so make sure to override that (option is -e) if you have any slaves down the chain.

Alternative myloader

There is a fork of myloader that defers creation of secondary indexes. The author suggests it is faster for tables over 150 MM rows. I haven’t had the time to test it and, unfortunately, it is based on the older 0.6 myloader. Do let me know in the comments section if you have tried it.

Importing into RDS

If you are using the Multi-AZ feature of RDS, that means writes are synchronously applied to another standby RDS instance on a different availability zone.

This greatly increases write latency, which is a performance killer for myloader. I advise to disable this feature until the import is complete.

Database IO performance tests

I work with InnoDB and RocksDB storage engines for MySQL and do performance tests to compare both storage engines and storage devices. I have expertise in MySQL and storage engines but not so much in storage devices, so I don't mind running MySQL. Other people have expertise in the layers under MySQL (Linux, storage) and might mind running MySQL. Fortunately, we have benchmark clients for them.

Obviously there is fio and it is my first choiceThe fio team even added support for coordinated omission when I asked for it. Alas it can't generate all of the IO patterns that I need.

It would be great to share a link to io.go here were Domas to publish that code.
I wrote innosim many years ago to simulate InnoDB IO patterns. Docs are here and a helper script to run a sequence of tests is here.

Finally there is db_bench for RocksDB. One challenge with RocksDB is tuning, so I have a script to help with that and use good options to run a sequence of tests in a special pattern. Well, it works as long as I keep the script current and I just updated it today. It runs these benchmarks in order:
  1. fillseq - Put N key-value pairs in key order
  2. overwrite - Put N key-value pairs in random order. Queries done after a key-order load avoid a few sources of overhead that usually should not be avoided, so this shuffles the database.
  3. overwrite - Put key-values pairs in random order. Runs for K seconds.
  4. updaterandom - do read-modify-write in random order. Runs for K of seconds.
  5. readwhilewriting - 1 rate-limited writer and T threads doing Get.
  6. seekrandomwhilewriting - 1 rate-limited writer and T threads doing range scans.

Improving replication with multiple storage engines

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the other engine at once, this is an unavoidable problem with current MariaDB / MySQL code.

To solve this I have implemented MDEV-12179, per-engine mysql.gtid_slave_pos tables, which should hopefully be in MariaDB 10.3 soon. This patch makes the server able to use multiple replication state tables, one for each engine used. This way, InnoDB transactions can update the InnoDB replication table, and eg. MyRocks transactions can update the MyRocks table. No cross-engine transactions are needed (unless the application itself uses both InnoDB and MyRocks in a single transaction).

The feature is enabled with the new option --gtid-pos-auto-engines=innodb,rocksdb. The server will automatically create the new replication tables when/if needed, and will read any such tables present at server start to restore the replication state.

Performance test

To test the impact of the new feature, I ran a sysbench write-only load on a master, and measured the time for a slave to apply the full load. The workload is using MyRocks tables, while the default mysql.gtid_slave_pos table is stored in InnoDB. The performance was compared with and without --gtid-pos-auto-engines=innodb,rocksdb. Full details of test options are available following the link at the end of the article.

Replication injects an update into a small table as part of each commit. The performance impact of this will be most noticeable for fast transactions, where the commit overhead is relatively larger. It will be particularly noticeable when durability is enabled (--innodb-flush-log-at-trx-commit=1 and similar). If another storage engine is added into a transaction, extra fsync() calls are needed in the commit step, which can be very expensive.

I tested the performance in two scenarios:

  1. A "worst case" scenario with durability/fsync enabled for binlog, InnoDB, and MyRocks, on hardware with slow fsync.
  2. A "best case" scenario with all durability/fsync disabled.
In the "worst case" we would hope to see substantial improvement due to reducing the number of fsync operations. In the "best case" improvements will be expected to be small, if any, though there may still be some improvement due to avoiding CPU and some I/O overhead from running the commits through two engines.

The figure at the start of the article shows the results from the "worst case". The left bar is the time for the slave to catch up when the replication state table is using the default InnoDB storage engine. The middle bar is the time when using --gtid-pos-auto-engines=innodb,myrocks and the right bar is when the state table is changed to MyRocks (MyRocks-only load).

We see a huge speed penalty from the cross-engine transactions introduced by the InnoDB state table, the slave is twice as slow. However, with the patch, all the performance is recovered compared to MyRocks-only setup.

The test was run on consumer-grade hardware with limited I/O capabilities. I ran a small script to test the speed of fdatasync() (see link at end of article). This SATA-attached SSD can do around 120 fdatasync() calls per second, writing 16 KB blocks at random round-robin among five 1MB data files. In the "worst case" test, the load is completely disk-bound. Thus, the absolute transactions-per-second numbers are low, and the impact of the new feature is very big.

The results of the "best case" is in the following figure. The "best case" workload is CPU-bound, disk utilisation is low. Sysbench write-only does several queries in each transaction, so commit overhead is relatively lower. Still, we see a substantial cost of replication introduced cross-engine, it runs 18% slower than the MyRocks-only case. And again, the patch is able to fully recover the performance.

So I think these are really good results for the new feature. The impact for the user is low - just set a server option, and the server will handle the rest. We could eventually make InnoDB, TokuDB, and MyRocks default for --gtid-pos-auto-engines to make it fully automatic. The actual performance gain will depend completely on the workload, and the absolute numbers from these performance tests mean little, perhaps. But they do show that there should be significant potential gain in many cases, and enourmous gains in some cases.

Conclusions

I hope this feature will help experiments with, and eventual migration to, the new storage engines such as TokuDB and MyRocks. The ability to have good replication performance when different storage engines are used in different transactions (but not within a single transaction) should make it easier to experiment, without committing everything on a server to a new and unknown engine. There might even be use cases for deploying permanently on a mixed-engine setup, with different parts of the data utilising different performance characteristics of each engine.

The present work here is implemented for MariaDB only. However, there is some discussions on porting it to other MySQL variants. While the details of the implementation will differ somewhat, due to code differences in MariaDB replication, I believe a similar approach should work well in the other MySQL variants also. So it is definitely a possibility, if there is interest.

Links:

MariaDB Analytics Tutorial: 5 steps to get started in 10 minutes

MariaDB Analytics Tutorial: 5 steps to get started in 10 minutes Amy Krishnamohan Thu, 05/11/2017 - 16:40

Looking for an easy way to get started with analytics? MariaDB ColumnStore provides a simple, open and scalable analytics solution. It leverages a pluggable storage engine to handle analytic workloads while keeping the same ANSI SQL interface that is used across the MariaDB portfolio. This blog provides a quick 5-step tutorial to help you get started with MariaDB ColumnStore.

Before you begin, please download the sample dataset, including:

 

Step 1: MariaDB ColumnStore Installation and Configuration

In this step, you will learn how to download and install MariaDB ColumnStore.

Step 2: Create Table and Load Data

MariaDB ColumnStore does not require you to set up index and partitioning. It provides an easy way to create a table and load data without help from DBAs. In addition, when ColumnStore loads data, it uses cpimport which leverages parallel query loading capability. To learn more about cpimport, watch this presentation by our solutions engineer, Anders Karlsson.

Step 3: Create Dimension Table / Cross Engine Join

Leveraging the MariaDB Server interface, we can use "Dimension Tables" from the InnoDB storage engine and join those with the "Fact Table" data in ColumnStore. In this demo, we join a loan stats fact table and dimension table to create a sample quarterly report on loan amount.

Step 4: Window Function

Another benefit of ColumnStore is built-in analytics queries like window functions. Without writing complex code, users can run window functions in SQL to run time series analysis or run averages on a certain dataset. In this example, with one SQL query, you can report on the top ranked delinquent loan amounts in five specific states.

Step 5: Data Visualization: Tableau integration

ColumnStore provides an easy way to connect to third-party BI tools like Tableau using a generic ODBC driver, enabling you to better visualize your data.

Hope you enjoyed the tutorial! Here are some additional resources to help you along the way:

Looking for an easy way to get started with analytics? MariaDB ColumnStore provides a simple, open and scalable analytics solution. It leverages a pluggable storage engine to handle analytic workloads while keeping the same ANSI SQL interface that is used across the MariaDB portfolio. This blog provides a quick 5-step tutorial to help you get started with MariaDB ColumnStore.

Login or Register to post comments

Pages