Planet MySQL

Laravel Cashier Braintree Payment Gateway Tutorial With Example

In this tutorial, we will see Laravel Cashier Braintree Payment Gateway Tutorial With Example We will build a Subscription based Platform in which the user can choose a plan, and according to that, he will be charged. We use Braintree for this demo, and we will discuss the stripe in the next tutorial. So, we will build a simple payment gateway in which a user can charge according to their selected plan. It is a straightforward system to work with, and for that, we need to create a developer account at Braintree. So, if you have not created yet, then please go to this link.

Laravel Cashier Braintree Payment Gateway

Integrate payment gateway in any web application used to be a very tough task. But nowadays, there are lots of SDKs available to work with that is why it is effortless to integrate any payment gateway to any web application. Now, you can find the Official Laravel Documentation to integrate Braintree here.

Braintree Caveats

For many operations, the Stripe and Braintree implementations of the Cashier function is the same. Both services provide the subscription billing with the credit cards, but Braintree also supports the payments via PayPal. We will not use Paypal for this example. However, Braintree also lacks some features that are supported by the Stripe. You should keep the following things in mind when deciding to use Stripe or Braintree:

  • Braintree supports PayPal while Stripe does not.
  • Braintree does not support the increment and decrement methods on subscriptions. This is a Braintree limitation, not a Cashier limitation.
  • Braintree does not support percentage based discounts. This is a Braintree limitation, not a Cashier limitation.

Let us start this small project by installing the Laravel Framework. I am using Laravel 5.7 for this example.

Step 1: Install and configure Laravel

Install Laravel using the following command.

laravel new subscription

Now, go inside the project folder and configure the database inside the .env file.

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

Also, install the npm dependencies using the following command.

npm install

Compile the CSS and JS files.

npm run dev

Also, create the standard authentication using the following command.

php artisan make:auth Step 2: Install and configure Braintree package

Next step is to install the Laravel Cashier package. So let us install that.

composer require "laravel/cashier-braintree":"~2.0"

Next step is to register the Laravel\Cashier\CashierServiceProvider  service provider in your config/app.php configuration file.

... Laravel\Cashier\CashierServiceProvider::class, ...

Now, we need to create and modify the database migrations.

First, edit the create_users_table.php file and add the following fields inside the schema.

Schema::table('users', function ($table) { $table->string('braintree_id')->nullable(); $table->string('paypal_email')->nullable(); $table->string('card_brand')->nullable(); $table->string('card_last_four')->nullable(); $table->timestamp('trial_ends_at')->nullable(); });

So, our final create_users_table.php file looks like below.

<?php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateUsersTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('email')->unique(); $table->timestamp('email_verified_at')->nullable(); $table->string('password'); $table->string('braintree_id')->nullable(); $table->string('paypal_email')->nullable(); $table->string('card_brand')->nullable(); $table->string('card_last_four')->nullable(); $table->timestamp('trial_ends_at')->nullable(); $table->rememberToken(); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('users'); } }

Also, we need to create two more migrations file. To create the files, hit the following commands.

php artisan make:migration create_plans_table php artisan make:migration create_subscriptions_table

Now, write the following schemas inside it.

<?php // create_subscriptions_table.php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateSubsriptionsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('subsriptions', function (Blueprint $table) { $table->increments('id'); $table->integer('user_id'); $table->string('name'); $table->string('braintree_id'); $table->string('braintree_plan'); $table->integer('quantity'); $table->timestamp('trial_ends_at')->nullable(); $table->timestamp('ends_at')->nullable(); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('subsriptions'); } }

And the following is plans table schema.

<?php // create_plans_table.php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreatePlansTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('plans', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('slug')->unique(); $table->string('braintree_plan'); $table->float('cost'); $table->text('description')->nullable(); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('plans'); } }

Now go to the terminal and create the tables using the following command.

php artisan migrate

It will create the tables.

 

Now, add the Billable Trait inside the User.php model.

// User.php use Laravel\Cashier\Billable; class User extends Authenticatable { use Billable; } Step 3: Grab and add API keys for Braintree

Now, if you have not created a developer account at Braintree, then please create one.

After that, you will be redirected to the Dashboard and then go to the Account >> My User and click the View Authorizations link under the API Keys, Tokenization Keys, Encryption Keys section.

 

After clicking the View Authorizations link, you will be redirected to your Sandbox API Keys page.

 

Now, you need to click the View links inside the table, and now you can see your following keys and data.

  1. Your Public Key
  2. Your Private Key
  3. Your Environment
  4. Your Merchant Id

You need to add these keys inside your .env file.

BRAINTREE_ENV=sandbox BRAINTREE_MERCHANT_ID=your merchant id BRAINTREE_PUBLIC_KEY=your public key BRAINTREE_PRIVATE_KEY=your private key

Next step is that you should configure the following options inside your services.php file.

// services.php 'braintree' => [ 'model' => App\User::class, 'environment' => env('BRAINTREE_ENV'), 'merchant_id' => env('BRAINTREE_MERCHANT_ID'), 'public_key' => env('BRAINTREE_PUBLIC_KEY'), 'private_key' => env('BRAINTREE_PRIVATE_KEY'), ],

Then you should add the following Braintree SDK calls to your AppServiceProvider’s boot method.

// AppServiceProvider.php use Braintree_Configuration; public function boot() { Braintree_Configuration::environment(env('BRAINTREE_ENV')); Braintree_Configuration::merchantId(env('BRAINTREE_MERCHANT_ID')); Braintree_Configuration::publicKey(env('BRAINTREE_PUBLIC_KEY')); Braintree_Configuration::privateKey(env('BRAINTREE_PRIVATE_KEY')); }

You can also set the currency. Dollar($) is by default.

You can change the default currency by calling the Cashier::useCurrency method from within the boot method of one of your service providers. In our case we have used the AppServiceProvider. The useCurrency method accepts two string parameters: the currency and the currency’s symbol.

// AppServiceProvider.php use Laravel\Cashier\Cashier; Cashier::useCurrency('eur', '€'); Step 4: Create plans on Braintree dashboard

Now, for this example, we will create only two plans. You can create as per your requirement. You can find the plans inside your dashboard on the left sidebar.

I have created two plans.

  1. Basic
  2. Professional

 

Also, you need to add the plans manually inside the plans table inside MySQL.

 

Make sure both plans have the same names as on MySQL and Braintree dashboard.

Step 5: Display plans on Frontend

First, define the routes for our application inside the routes >> web.php file.

// web.php Route::group(['middleware' => 'auth'], function() { Route::get('/home', 'HomeController@index')->name('home'); Route::get('/plans', 'PlanController@index')->name('plans.index'); });

We have taken the auth middleware to protect the routes related to payment and home.

Now, create the Plan.php model and PlanController.php file.

php artisan make:model Plan php artisan make:controller PlanController

Define the index method inside the PlanController.

// PlanController.php use App\Plan; public function index() { $plans = Plan::all(); return view('plans.index', compact('plans')); }

Now, inside the resources >> views folder, create one folder called plans and inside that folder, create one file called index.blade.php file. Write the following code.

@extends('layouts.app') @section('content') <div class="container"> <div class="row justify-content-center"> <div class="col-md-12"> <div class="card"> <div class="card-header">Plans</div> <div class="card-body"> <ul class="list-group"> @foreach($plans as $plan) <li class="list-group-item clearfix"> <div class="pull-left"> <h5>{{ $plan->name }}</h5> <h5>${{ number_format($plan->cost, 2) }} monthly</h5> <h5>{{ $plan->description }}</h5> <a href="" class="btn btn-outline-dark pull-right">Choose</a> </div> </li> @endforeach </ul> </div> </div> </div> </div> </div> @endsection

Save the file and navigate to the URL: http://subscription.test/plans. If you have not registered yet, then please register one user in our application.

You will see something like this.

 

So, we have successfully displayed the plans from the database. Now, the next step is when a user chooses the plan, you will redirect to a page from where a user can be charged for that plan.

Step 6: Show the plan

So, when the user chooses the plan, we need to redirect the user to a particular plan page.

Define one more route inside the routes >> web.php file.

// web.php Route::group(['middleware' => 'auth'], function() { Route::get('/home', 'HomeController@index')->name('home'); Route::get('/plans', 'PlanController@index')->name('plans.index'); Route::get('/plan/{plan}', 'PlanController@show')->name('plans.show'); });

Now, by default, RouteModelBinding works with the ID of the model. But we will not pass the ID to show the particular plan instead we will pass the slug. So we need to define it inside the Plan.php model. Also, define the fillable fields as well.

<?php // Plan.php namespace App; use Illuminate\Database\Eloquent\Model; class Plan extends Model { protected $fillable = [ 'name', 'slug', 'braintree_plan', 'cost', 'description' ]; public function getRouteKeyName() { return 'slug'; } }

Here, we have defined the function called getRouteKeyName. So based on this function, now we can fetch the record based on the slug and not based on the ID. That is why we have taken the slug field as a unique field in the database.

Now, define the show() function inside the PlanController.php file.

// PlanController.php public function show(Plan $plan, Request $request) { return view('plans.show', compact('plan')); }

Next step is to create a view file called show.blade.php inside the resources >> views >> plans folder. Add the following code inside the show.blade.php file.

@extends('layouts.app') @section('content') <div class="container"> <div class="row justify-content-center"> <div class="col-md-12"> <div class="card"> <div class="card-header">{{ $plan->name }}</div> <div class="card-body"> </div> </div> </div> </div> </div> @endsection

So, here, we will display the payment form.

Step 7: Display the Payment Form

For displaying the Payment Form, we will use the Drop-in UI. You can find the complete documentation here. We are using Version 2 of the Drop-in UI. There is version 3 but let us stick with version 2 for this example. Now, we are implementing the Client Side configuration. Configure the container and from where the Drop-in UI will add the payment method nonce. Make sure to replace CLIENT_AUTHORIZATION with your generated client token.

Now, we need to include the External JS files in our project. For that, we need to modify the resources >> views >> layouts >> app.blade.php file.

<!DOCTYPE html> <html lang="{{ str_replace('_', '-', app()->getLocale()) }}"> <head> <meta charset="utf-8"> <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> <!-- Fonts --> <link rel="dns-prefetch" href="//fonts.gstatic.com"> <link href="https://fonts.googleapis.com/css?family=Nunito" rel="stylesheet" type="text/css"> <!-- Styles --> <link href="{{ asset('css/app.css') }}" rel="stylesheet"> </head> <body> <div id="app"> <nav class="navbar navbar-expand-md navbar-light navbar-laravel"> <div class="container"> <a class="navbar-brand" href="{{ url('/') }}"> {{ config('app.name', 'Laravel') }} </a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="{{ __('Toggle navigation') }}"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarSupportedContent"> <!-- Left Side Of Navbar --> <ul class="navbar-nav mr-auto"> </ul> <!-- Right Side Of Navbar --> <ul class="navbar-nav ml-auto"> <!-- Authentication Links --> @guest <li class="nav-item"> <a class="nav-link" href="{{ route('login') }}">{{ __('Login') }}</a> </li> <li class="nav-item"> @if (Route::has('register')) <a class="nav-link" href="{{ route('register') }}">{{ __('Register') }}</a> @endif </li> @else <li class="nav-item"> <a class="nav-link" href="{{ route('plans.index') }}">{{ __('Plans') }}</a> </li> <li class="nav-item dropdown"> <a id="navbarDropdown" class="nav-link dropdown-toggle" href="#" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" v-pre> {{ Auth::user()->name }} <span class="caret"></span> </a> <div class="dropdown-menu dropdown-menu-right" aria-labelledby="navbarDropdown"> <a class="dropdown-item" 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 </form> </div> </li> @endguest </ul> </div> </div> </nav> <main class="py-4"> @yield('content') </main> </div> <!-- Scripts --> <script src="{{ asset('js/app.js') }}"></script> @yield('scripts'); </body> </html>

Here, we have defined one navigation link called plans and also add one section for scripts. So, now we can add the Javascript per pagewise using @yield directive.

Also, we need to fetch the Client Token for authorization. So we will fetch that token from Backend using AJAX request.

So, define one route inside the web.php file.

// web.php Route::group(['middleware' => 'auth'], function() { Route::get('/home', 'HomeController@index')->name('home'); Route::get('/plans', 'PlanController@index')->name('plans.index'); Route::get('/plan/{plan}', 'PlanController@show')->name('plans.show'); Route::get('/braintree/token', 'BraintreeTokenController@index')->name('token'); });

Create a new Controller called BraintreeTokenController using the following command.

php artisan make:controller BraintreeTokenController

Write the following code inside the BraintreeTokenController.php file.

<?php // BraintreeTokenController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use Braintree_ClientToken; class BraintreeTokenController extends Controller { public function index() { return response()->json([ 'data' => [ 'token' => Braintree_ClientToken::generate() ] ]); } }

The index() function returns the ClientToken to the Clientside JS file, and now we can authorize our Laravel application with the Braintree developer account.

So, write the final code inside the show.blade.php file.

@extends('layouts.app') @section('content') <div class="container"> <div class="row justify-content-center"> <div class="col-md-12"> <div class="card"> <div class="card-header">{{ $plan->name }}</div> <div class="card-body"> <form method="post" action="{{ route('subscription.create') }}"> @csrf <div id="dropin-container"></div> <hr /> <input type="hidden" name="plan" value="{{ $plan->id }}" /> <button type="submit" class="btn btn-outline-dark d-none" id="payment-button">Pay</button> </form> </div> </div> </div> </div> </div> @endsection @section('scripts') <script src="https://js.braintreegateway.com/js/braintree-2.32.1.min.js"></script> <script> jQuery.ajax({ url: "{{ route('token') }}", }) .done(function(res) { braintree.setup(res.data.token, 'dropin', { container: 'dropin-container', onReady: function() { jQuery('#payment-button').removeClass('d-none') } }); }); </script> @endsection

So, when the document is loaded, we will send an Ajax request to the Laravel server and get the Client Auth token. From that token, included js will generate the Payment Form. We defined the form like this.

<form method="post" action=""> @csrf <div id="dropin-container"></div> <hr /> <input type="hidden" name="plan" value="{{ $plan->id }}" /> <button type="submit" class="btn btn-outline-dark d-none" id="payment-button">Pay</button> </form>

Also, write the Javascript like this.

@section('scripts') <script src="https://js.braintreegateway.com/js/braintree-2.32.1.min.js"></script> <script> jQuery.ajax({ url: "{{ route('token') }}", }) .done(function(res) { braintree.setup(res.data.token, 'dropin', { container: 'dropin-container', onReady: function() { jQuery('#payment-button').removeClass('d-none') } }); }); </script> @endsection

So, when the request succeeds, it will return the token, and we use that token to create a Payment Form. The form looks like this.

 

So, here we need to enter the two details. You can enter the details like below inputs.

  1. Card Number: 4242 4242 4242 4242
  2. Expiration Date: 10/21 or whatever you like

These are dummy details, but these details generally used in sandbox account to check the application.

Right now nothing will happen because we need to define the form action to store the data inside the database tables. So let us define the post route.

Step 8: Save the plan details

Define the final route inside the web.php file.

<?php // web.php Route::get('/', function () { return view('welcome'); }); Auth::routes(); Route::group(['middleware' => 'auth'], function() { Route::get('/home', 'HomeController@index')->name('home'); Route::get('/plans', 'PlanController@index')->name('plans.index'); Route::get('/plan/{plan}', 'PlanController@show')->name('plans.show'); Route::get('/braintree/token', 'BraintreeTokenController@index')->name('token'); Route::post('/subscription', 'SubscriptionController@create')->name('subscription.create'); });

We have defined the post route for the subscription details. Now, create SubscriptionController using the following command.

php artisan make:controller SubscriptionController

Inside that controller, we need to define one function called create().

<?php // SubscriptionController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Plan; class SubscriptionController extends Controller { public function create(Request $request, Plan $plan) { $plan = Plan::findOrFail($request->get('plan')); $request->user() ->newSubscription('main', $plan->braintree_plan) ->create($request->payment_method_nonce); return redirect()->route('home')->with('success', 'Your plan subscribed successfully'); } }

First, we have to fetch the plan according to the id. Then we need to pass that plan to the subscribedToPlan() function.

So, here we have used the Billable trait’s subscribedToPlan() method and pass the first parameter plan and the second parameter main.

We are creating a new Subscription, and if the payment made successfully then, it would redirect to the HomePage with a  success message.

Write the following code inside the home.blade.php file.

@extends('layouts.app') @section('content') <div class="container"> <div class="row justify-content-center"> <div class="col-md-8"> @if(session()->get('success')) <div class="alert alert-success"> {{ session()->get('success') }} </div> @endif <div class="card"> <div class="card-header">Dashboard</div> <div class="card-body"> @if (session('status')) <div class="alert alert-success" role="alert"> {{ session('status') }} </div> @endif You are logged in! </div> </div> </div> </div> </div> @endsection

So, if your all of the configurations are right, then you should go to any of the plans and try to subscribe to the plan. If you are redirecting to the homepage, then you are almost done.

 

You can see that one database entry inside the subscriptions table is there.

 

That means, we have successfully subscribed the Professional plan. Also, there is updation on the user’s table. We have already added some more fields.

You can see inside your Dashboard that Sales and Transaction volume is there. So, our Laravel Cashier Braintree Payment Gateway Tutorial With Example is almost complete.

Step 9: Security Tweaks

Now, we need to keep in mind one thing that if the user is already subscribed to one plan, then we need the user to prevent to choose that plan. So, we need to add one condition on the choose button.

So, add the condition inside the index.blade.php file.

@if(!auth()->user()->subscribedToPlan($plan->braintree_plan, 'main')) <a href="{{ route('plans.show', $plan->slug) }}" class="btn btn-outline-dark pull-right">Choose</a> @endif

Also, we need to add the condition inside the PlanController.php file’s show() function.

// PlanController.php public function show(Plan $plan, Request $request) { if($request->user()->subscribedToPlan($plan->braintree_plan, 'main')) { return redirect()->route('home')->with('success', 'You have already subscribed the plan'); } return view('plans.show', compact('plan')); }

Also, we need to do the same thing inside the SubscriptionController’s create() method.

// SubscriptionController.php public function create(Request $request, Plan $plan) { if($request->user()->subscribedToPlan($plan->braintree_plan, 'main')) { return redirect()->route('home'); } $plan = Plan::findOrFail($request->get('plan')); $request->user() ->newSubscription('main', $plan->braintree_plan) ->create($request->payment_method_nonce); return redirect()->route('home')->with('success', 'Your plan subscribed successfully'); }

Save the file, and now you are good to go. I am putting this Laravel Cashier Braintree Payment Gateway Tutorial’s whole code on Github.

Finally, Laravel Cashier Braintree Payment Gateway Tutorial With Example article is over. There are still so many things that we can do with the project. But for basic understanding, this is enough. Thanks.

Github Code

 

The post Laravel Cashier Braintree Payment Gateway Tutorial With Example appeared first on AppDividend.

Percona Live 2019 Call for Papers is Now Open!

Announcing the opening of the Percona Live 2019 Open Source Database Conference call for papers. It will be open from now until January 20, 2019. The Percona Live Open Source Database Conference 2019 takes place May 28-30 in Austin, Texas.

Our theme this year is CONNECT. ACCELERATE. INNOVATE.

As a speaker at Percona Live, you’ll have the opportunity to CONNECT with your peers—open source database experts and enthusiasts who share your commitment to improving knowledge and exchanging ideas. ACCELERATE your projects and career by presenting at the premier open source database event, a great way to build your personal and company brands. And influence the evolution of the open source software movement by demonstrating how you INNOVATE!

Community initiatives remain core to the open source ethos, and we are proud of the contribution we make with Percona Live in showcasing thought leading practices in the open source database world.

With a nod to innovation, this year we are introducing a business track to benefit those business leaders who are exploring the use of open source and are interested in learning more about its costs and benefits.

Speaking Opportunities

The Percona Live Open Source Database Conference 2019 Call for Papers is open until January 20, 2019. We invite you to submit your speaking proposal for breakout, tutorial or lightning talk sessions. Classes and talks are invited for Foundation (either entry-level or of general interest to all), Core (intermediate), and Masterclass (advanced) levels.

  • Breakout Session. Broadly cover a technology area using specific examples. Sessions should be either 25 minutes or 50 minutes in length (including Q&A).
  • Tutorial Session. Present a technical session that aims for a level between a training class and a conference breakout session. We encourage attendees to bring and use laptops for working on detailed and hands-on presentations. Tutorials will be three or six hours in length (including Q&A).
  • Lightning Talk. Give a five-minute presentation focusing on one key point that interests the open source community: technical, lighthearted or entertaining talks on new ideas, a successful project, a cautionary story, a quick tip or demonstration.

If your proposal is selected for breakout or tutorial sessions, you will receive a complimentary full conference pass.

Topics and Themes

We want proposals that cover the many aspects of application development using all open source databases, as well as new and interesting ways to monitor and manage database environments. Did you just embrace open source databases this year? What are the technical and business values of moving to or using open source databases? How did you convince your company to make the move? Was there tangible ROI?

Best practices and current trends, including design, application development, performance optimization, HA and clustering, cloud, containers and new technologies –  what’s holding your focus? Share your case studies, experiences and technical knowledge with an engaged audience of open source peers.

In the submission entry, indicate which of these themes your proposal best fits: tutorial, business needs; case studies/use cases; operations; or development. Also include which track(s) from the list below would be best suited to your talk.

Tracks

The conference committee is looking for proposals that cover the many aspects of using, deploying and managing open source databases, including:

  • MySQL. Do you have an opinion on what is new and exciting in MySQL? With the release of MySQL 8.0, are you using the latest features? How and why? Are they helping you solve any business issues, or making deployment of applications and websites easier, faster or more efficient? Did the new release influence you to change to MySQL? What do you see as the biggest impact of the MySQL 8.0 release? Do you use MySQL in conjunction with other databases in your environment?
  • MariaDB. Talks highlighting MariaDB and MariaDB compatible databases and related tools. Discuss the latest features, how to optimize performance, and demonstrate the best practices you’ve adopted from real production use cases and applications.
  • PostgreSQL. Why do you use PostgreSQL as opposed to other SQL options? Have you done a comparison or benchmark of PostgreSQL vs. other types of databases related to your applications? Why, and what were the results? How does PostgreSQL help you with application performance or deployment? How do you use PostgreSQL in conjunction with other databases in your environment?
  • MongoDB. Has the 4.0 release improved your experience in application development or time-to-market? How are the new features making your database environment better? What is it about MongoDB 4.0 that excites you? What are your experiences with Atlas? Have you moved to it, and has it lived up to its promises? Do you use MongoDB in conjunction with other databases in your environment?
  • Polyglot Persistence. How are you using multiple open source databases together? What tools and technologies are helping you to get them interacting efficiently? In what ways are multiple databases working together helping to solve critical business issues? What are the best practices you’ve discovered in your production environments?
  • Observability and Monitoring. How are you designing your database-powered applications for observability? What monitoring tools and methods are providing you with the best application and database insights for running your business? How are you using tools to troubleshoot issues and bottlenecks? How are you observing your production environment in order to understand the critical aspects of your deployments? 
  • Kubernetes. How are you running open source databases on the Kubernetes, OpenShift and other container platforms? What software are you using to facilitate their use? What best practices and processes are making containers a vital part of your business strategy? 
  • Automation and AI. How are you using automation to run databases at scale? Are you using automation to create self-running, self-healing, and self-tuning databases? Is machine learning and artificial intelligence (AI) helping you create a new generation of database automation?
  • Migration to Open Source Databases. How are you migrating to open source databases? Are you migrating on-premises or to the cloud? What are the tools and strategies you’ve used that have been successful, and what have you learned during and after the migration? Do you have real-world migration stories that illustrate how best to migrate?
  • Database Security and Compliance. All of us have experienced security and compliance challenges. From new legislation like GDPR, PCI and HIPAA, exploited software bugs, or new threats such as ransomware attacks, when is enough “enough”? What are your best practices for preventing incursions? How do you maintain compliance as you move to the cloud? Are you finding that security and compliance requirements are preventing your ability to be agile?
  • Other Open Source Databases. There are many, many great open source database software and solutions we can learn about. Submit other open source database talk ideas – we welcome talks for both established database technologies as well as the emerging new ones that no one has yet heard about (but should).
  • Business and Enterprise. Has your company seen big improvements in ROI from using Open Source Databases? Are there efficiency levels or interesting case studies you want to share? How did you convince your company to move to Open Source?
How to Respond to the Call for Papers

For information on how to submit your proposal, visit our call for papers page.

Sponsorship

If you would like to obtain a sponsor pack for Percona Live Open Source Database Conference 2019, you will find more information including a prospectus on our sponsorship page. You are welcome to contact me, Bronwyn Campbell, directly.

FOSDEM MySQL, MariaDB and Friends 2019 Schedule

The MySQL, MariaDB and Friends devroom was once again flooded with submissions. The committee, consisting of community members Daniël van Eeden, Art van Scheppingen, Giuseppe Maxia and Aurélien Lequoy, as well as Tom de Cooman for Percona, Vicentiu Ciorbaru for MariaDB and Nuno Carvalho for MySQL/Oracle, had the difficult job of whittling down 67 submissions into the final 17.

The following sessions were selected:

Session Speaker Start End Welcome Frédéric Descamps and Ian Gilfillan 10h00 10h30 Patterns and anti-patterns in OSS participation – Lessons from MySQL AB, the MariaDB Foundation, and others Zak Greant 10h40 11h00 Making Your MySQL Replication Setup Run Faster! Vitor Oliveira 11h10 11h30 The consequences of sync_binlog != 1 Jean-François Gagné 11h40 12h00 MySQL Replication – Advanced Features Peter Zaitsev 12h10 12h30 Tapping Into the Binary Log Change Stream Luis Soares 12h40 13h00 The art of multiplexing MySQL connections with ProxySQL René Cannaò 13h10 13h30 Improve your SQL – Common Table Expressions (CTE) and Window Functions Susanne Holzgraefe 13h40 14h00 LATERAL derived table in MySQL Norvald H. Ryeng 14h10 14h30 Test complex database systems in a laptop with dbdeployer – The friendly tool that makes DBA and developers life easy Giuseppe Maxia 14h40 15h00 MariaDB and MySQL – what statistics optimizer needs – or when and how not to use indexes Sergei Golubchik 15h10 15h30 MySQL and the CAP theorem: relevance & misconceptions – Dissecting, affirming and refuting CAP assumptions in real production systems Shlomi Noach 15h40 16h00 How to create a useful MySQL bug report – …and make sure it’s properly processed Valerii Kravchuk 16h10 16h30 How Online Backup works in MyRocks and RocksDB Yoshinori Matsunobu 16h40 17h00 TiDB: Distributed, horizontally scalable, MySQL compatible Morgan Tocker 17h10 17h30 MySQL Performance Schema in 20 Minutes Sveta Smirnova 17h40 18h00 MySQL 8.0 Component Infrastructure – Why, what’s there, what’s next and how to use it Joro Kodinov 18h10 18h30 MySQL 8.0 Document Store: How to Mix NoSQL & SQL in MySQL 8.0 – a database where developers and DBAs live in peace Frédéric Descamps 18h40 19h00

All session will be taking place in H.1308, Rolin (to be confirmed) on Saturday 2 February. Please check closer to the time for any schedule changes. Look forward to seeing you there!

Fun with Bugs #74 - On MySQL Bug Reports I am Subscribed to, Part XI

For some reason the Committee of FOSDEM 2019 MySQL, MariaDB & Friends Devroom of all my talks submitted picked up the one on how to create a useful MySQL bug report, so I have no options but continue to write about MySQL bugs, as long and MySQL Community wants and even prefers to listen and read about them... That's what I do, with pleasure.

Today I'll continue my series of posts about community bug reports I am subscribed to with the review of bugs reported since October 1, 2018, starting from the oldest and skipping those MySQL 8 regression ones I've already commented on:
  • Bug #92609 - "Upgrade to 8.0.12 fails". This bug reported by Frederic Steinfels is about upgrade from MySQL 5.7 that leads to crash. Nice workaround was found:
    "The work around is delete all .TRG file (or move them to out side of mysql data folder) then update. After success we can re-create the trigger."The bug is really fixed in MySQL 8.0.14 according to the last comment, but for some reason it is still "Verified". Probably will be closed when MySQL 8.0.14 is released.
  • Bug #92631 - "importing dump from mysqldump --all-databases breaks SYS schema due to routines". This bug affecting MySQL 5.7 (and not 8.0) was reported by Shane Bester. Workaround is actually documented in the manual - add sys schema explicitly while dumping and dump it separately, then re-create.
  • Bug #92661 - "SELECT on key partitioned enum reading all partitions instead of 1". Interesting corner case found by Frederic Steinfels. MariaDB 10.3.7 also seems affected:
    MariaDB [test]> explain partitions select id from product where outdated='0';
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    | id   | select_type | table   | partitions | type | possible_keys | key      |
    key_len | ref   | rows | Extra                    |
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    |    1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated |
    1       | const |    2 | Using where; Using index |
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    1 row in set (0.002 sec)

    MariaDB [test]> explain partitions select id from product where outdated='1';
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    | id   | select_type | table   | partitions | type | possible_keys | key      |
    key_len | ref   | rows | Extra                    |
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    |    1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated |
    1       | const | 2048 | Using where; Using index |
    +------+-------------+---------+------------+------+---------------+----------+-
    --------+-------+------+--------------------------+
    1 row in set (0.002 sec)
  • Bug #92809 - "Inconsistent ResultSet for different Execution Plans". The full test case is not public and it took a lot of arguing until this bug (reported by Juan Arruti) was finally "Verified". Based on the workaround, setting optimizer_switch='materialization=off', this feature of MySQL optimizer is still problematic.
  • Bug #92850 - "Bad select+order by+limit performance in 5.7". As Sveta Smirnova demonstrated, there are still cases when FORCE INDEX hints really needed to help optimizer to use proper plan, even in somewhat obvious case of single table and single proper index... What a surprise!
  • Bug #92882 - "MTS not replication crash-safe with GTID and all the right parameters." As  Jean-François Gagné proved, at least statement-based multi-threaded replication with GTIDs is not safe in case of OS crash. Good that there is an easy enough workaround: stop slave; reset slave; start slave; See also his Bug #93081 - "Please implement a better relay log recovery." that refers to several more known problems with relay log recovery.
  • Bug #92949 - "add auto_increment column as PK cause RBR replication inconsistent". This probably should never happen in production (adding primary key while data are already active changed concurrently), but still this is a nice corner case reported by Fungo Wang.
  • Bug #92996 - "ANALYZE TABLE still locks tables 10 years later". Domas Mituzas is trying hard to escalate this well known problem of blocking queries if ANALYZE TABLE was executed at some wrong time (when long running query against the table was in progress). The problem was resolved this year in Percona Server, see this blog post. See also my MDEV-15101 (fix is planned for version 10.4 in MariaDB).
  • Bug #93033 - "Missing info on partitioned tables in I_S.INNODB_COLUMNS after upgrade to 8.0". Yet another regression in MySQL 8 vs 5.7 was reported by Alexey Kopytov.
  • Bug #93049 - "ORDER BY pk, otherdata should just use PK". There is no reason to use filesort, as Domas Mituzas kindly noted. MariaDB is also, unfortunately, affected.
  • Bug #93083 - "InnoDB: Failing assertion: srv_read_only_mode || trx->in_depth > 0". This Severity 6 bug (only debug binaries are directly affected) was reported by Ramesh Sivaraman from Percona QA. At least it was verified instantly (I've subscribed to double check what happens to bugs with low severity levels).
I'll stop for now. More detailed review of remaining bugs reported in November is coming soon.

Sheep are everywhere in East Sussex and bugs are everywhere in MySQL. Not that many, but still they are visible.
To summarize my conclusions from this list:
  1. Sometimes it takes too much efforts to force proper bug report processing. I had written more about this here.
  2. Having materialization=on in optimizer_switch in MySQL 5.7+ may cause wrong results. Take care to double check.
  3. There still cases of single table SELECTs where optimizer can do a much better job. FORCE INDEX helps, sometimes.
  4. Multi-threaded statement-based replication in MySQL 5.6 and 5.7 is not crash safe, even with GTIDs, relay_log_info_repository-TABLE and relay_log_recovery=ON. A lot of improvements in relay log recovery are needed.
  5. Oracle engineers still care to document workarounds in active public bug reports. This is great!
  6. Percona still fixes some really important and annoying bugs way before other vendors.
  7. MySQL 8 is different in many small details, so regressions are to be expected.

MySQL 8: Drop Several Stored Events, Procedures, or Functions

Maybe the biggest new feature in MySQL 8 is the new transaction data dictionary that improves the consistency of schema objects among other things. To further protect the data in the data dictionary, the data dictionary tables are hidden and their content only exposed through the Information Schema. (One exception is when you use the debug binary, then it is possible to get direct access to the data dictionary tables. This is not recommended at all on production systems!)

A side effect of the data dictionary tables being hidden is that those that have had a habit of manipulating the tables directly in MySQL 5.7 and earlier (I will not recommend doing that) will no longer be able to do so. Examples of manipulating the tables include dropping several stored events, procedures, and/or functions by deleting the corresponding rows in the mysql.event and mysql.proc tables.

Tip: I will use the word object to cover either an event, procedure, or function.

There are some discussions on Stack Overflow on how to do this task. See for example How to delete multiple events in mysql 8.0 database ? it was working in 5.7 but not in 8.0? and MySQL 8 – Remove All Stored Procedures in a Specific Database.

The obvious thought may be to write a stored procedure that can delete the objects, however that requires using a prepared statement which is not supported for DROP EVENT and similar:

mysql> SET @sql = 'DROP EVENT db1.event1'; Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt_drop FROM @sql; ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

There is a feature request to implement this in Bug 42337.

So what to do instead? My suggestion is to use MySQL Shell. There you can generate the queries and use session.sql() to execute them without using a prepared statement. However, first some objects are needed.

Deleting events, functions, and procedures in MySQL ShellSetup

Before we start looking at the options to drop several events, procedures, or functions, lets create some objects to work with. For the examples there are three events, three procedures, and three functions that can be created as follows:

mysql> CREATE SCHEMA db1; Query OK, 1 row affected (0.01 sec) mysql> use db1; Database changed mysql> CREATE EVENT event1 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1); Query OK, 0 rows affected (0.04 sec) mysql> CREATE EVENT event2 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1); Query OK, 0 rows affected (0.00 sec) mysql> CREATE EVENT `event``3` ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1); Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE proc1() DO SLEEP(1); Query OK, 0 rows affected (0.01 sec) mysql> CREATE PROCEDURE proc2() DO SLEEP(1); Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE `proc``3`() DO SLEEP(1); Query OK, 0 rows affected (0.00 sec) mysql> CREATE FUNCTION func1() RETURNS int DETERMINISTIC RETURN 1; Query OK, 0 rows affected (0.40 sec) mysql> CREATE FUNCTION func2() RETURNS int DETERMINISTIC RETURN 1; Query OK, 0 rows affected (0.08 sec) mysql> CREATE FUNCTION `func``3`() RETURNS int DETERMINISTIC RETURN 1; Query OK, 0 rows affected (0.30 sec) mysql> SELECT EVENT_SCHEMA, EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'db1'; +--------------+------------+ | EVENT_SCHEMA | EVENT_NAME | +--------------+------------+ | db1 | event1 | | db1 | event2 | | db1 | event`3 | +--------------+------------+ 3 rows in set (0.01 sec) mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'db1'; +----------------+--------------+--------------+ | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | +----------------+--------------+--------------+ | db1 | func1 | FUNCTION | | db1 | func2 | FUNCTION | | db1 | func`3 | FUNCTION | | db1 | proc1 | PROCEDURE | | db1 | proc2 | PROCEDURE | | db1 | proc`3 | PROCEDURE | +----------------+--------------+--------------+ 6 rows in set (0.01 sec)

This also shows how the events and routines can be found from the Information Schema.

The third event, procedure, and function are named with a backtick before the digit. This is to ensure our delete method quotes the identifier names correctly. If you are deleting events and routines from a production system, you are encouraged to test carefully that you quote the identifiers correctly.

Now on to delete the objects.

MySQL Shell

MySQL Shell is one of the great recent additions to MySQL. It supports performing DBA and devops tasks using SQL, Python, and JavaScript. Having a full featured programing language available allows you to automate tasks that otherwise would have been cumbersome or impossible.

The Python and JavaScript modes support executing queries either using NoSQL CRUD methods or SQL statements. So, now the task of deleting events, procedures, or functions becomes as simple as executing a query against the Information Schema to find the names of the objects and then generate the queries in a loop. The following will use Python, but it can also be written in JavaScript if you prefer.

I will first go through dropping events, then functions and procedures.

Events – Interactive

It is possible to get the names using the select() CRUD method on the information_schema.EVENTS table:

mysql-py> i_s = session.get_schema("information_schema") mysql-py> events = i_s.EVENTS \ -> .select("sys.quote_identifier(EVENT_SCHEMA) AS EventSchema", "sys.quote_identifier(EVENT_NAME) AS EventName") \ -> .where("EVENT_SCHEMA = 'db1'").execute().fetch_all(); mysql-py> events [ [ "`db1`", "`event1`" ], [ "`db1`", "`event2`" ], [ "`db1`", "`event``3`" ] ]

First an object of the information_schema is stored in the i_s variable. This allows you to access the Information Schema EVENTS table using i_s.EVENTS. From here the select() CRUD method is used to find all events in the db1 schema.

Tip: If you have a different criteria for which events to delete, edit the argument to the where() method.

The sys.quote_identifier() function is used to ensure that the schema and event names are quoted correctly, so for example the backtick in event`3 is escaped correctly (by adding a second backtick).

Important: The sys.quote_identifier() only works guaranteed to work correctly when the ANSI_QUOTES SQL mode is not enabled. When ANSI quotes are used, double quotes (“) are using to quote identifiers though backticks are still supported.

Now events is a list of the three events that were created earlier. You can easily check which events where found by just “executing” events. With that, you can delete the events one by one in a loop. For this a query template is first defined, then used to add the name of the schema and event. Usually it is not safe to do that using the Python format() function, however here we have ensured the names are quotes appropriately by using sys.quote_identifier().

mysql-py> sql_fmt = "DROP EVENT {0}.{1}" mysql-py> for event in events: -> print(sql_fmt.format(*event)) -> session.sql(sql_fmt.format(*event)).execute() -> DROP EVENT `db1`.`event1` DROP EVENT `db1`.`event2` DROP EVENT `db1`.`event``3` Query OK, 0 rows affected (0.0038 sec)

That’s it. Not exactly a one liner, but not hard either.

This is fine if it is a one off that you need to delete some events, but what if this is a more regular task? MySQL Shell can accommodate for that as well.

Events – Function

I have previously discussed how you can use external libraries in MySQL Shell and even shown an example of using it to have auto-repeating reports. It can also be used to implement a function where you give the schema you want to delete events from as an argument.

Let’s look at how the code looks for that:

from mysqlsh import globals def events_by_schema(schema, verbose=True): """Drops all events in the schema passed as an argument. Requires a connection to exist to the MySQL instance where the events are to be dropped. Optionally specify whether to be verbose (enabled by default) by printing each DROP EVENT statement executed. Returns the number of events dropped and False if an issue is encountered so no attempts are made to drop the events. """ # Get the session and ensure it is connected if not globals.session.is_open(): print("Please create a connection first.") return False # Define the query to get the events i_s = globals.session.get_schema("information_schema") stmt = i_s.EVENTS.select( "sys.quote_identifier(EVENT_SCHEMA) AS EventSchema", "sys.quote_identifier(EVENT_NAME) AS EventName") stmt = stmt.where("EVENT_SCHEMA = :schema") # Execute the query and check for warnings result = stmt.bind("schema", schema).execute() events = result.fetch_all() if (result.get_warnings_count() > 0): # Bail out and print the warnings print("Warnings occurred - bailing out:") print(result.get_warnings()) return False # Drop the events and check for warnings after each event sql_fmt = "DROP EVENT {0}.{1}" for event in events: sql = sql_fmt.format(*event) if verbose: print(sql) drop_result = globals.session.sql(sql).execute() if (drop_result.get_warnings_count() > 0): print("Warnings occurred:") print(result.get_warnings()) return len(events)

Yes, this code is a lot longer than the previous example, but in reality it is the same with some extra checks added and obtaining the session (the connection to the MySQL instance) from the mysqlsh.globals module. Since the schema is now provided as an argument to the function, a placeholder and bind() are used to ensure it is safe to use the value in a query.

If you save the code in a file in the Python search path, you can import it into MySQL Shell. Recall from my earlier blog that you can include extra directories in your search path by updating mysqlshrc.py in one of the places where MySQL Shell looks for it; for example in ${HOME}/.mysqlsh on Linux or %APPDATA%\MySQL\mysqlsh on Windows. An example of the mysqlshrc.py file is:

import sys sys.path.append("D:\MySQL\Shell\Python")

In this example, the code has been saved in the file delete_objects.py, so you can delete all events in the db1 schema like the following example:

mysql-py> import delete_objects mysql-py> num_delete_events = delete_objects.events_by_schema('db1') DROP EVENT `db1`.`event1` DROP EVENT `db1`.`event2` DROP EVENT `db1`.`event``3`

That completes the example for events. Now let’s turn to functions and procedures.

Procedures and Functions – Interactive

The example of deleting functions and/or procedures is very similar to what have just been seen for events, other than using the information_schema.ROUTINES table instead of information_schema.EVENTS to get the routine names and that your need to set a filter on ROUTINE_TYPE to specify whether you want procedures or functions (or both if the filter is absent). So, I will jump straight into the example by first dropping all functions, then all procedures in the db1 schema:

mysql-py> i_s = session.get_schema("information_schema") mysql-py> functions = i_s.ROUTINES \ -> .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \ -> .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'FUNCTION'").execute().fetch_all() mysql-py> functions [ [ "`db1`", "`func1`" ], [ "`db1`", "`func2`" ], [ "`db1`", "`func``3`" ] ] mysql-py> for function in functions: -> print(sql_fmt.format(*function)) -> session.sql(sql_fmt.format(*function)).execute() -> DROP FUNCTION `db1`.`func1` DROP FUNCTION `db1`.`func2` DROP FUNCTION `db1`.`func``3` Query OK, 0 rows affected (0.0684 sec)

Similar for the procedures:

mysql-py> i_s = session.get_schema("information_schema") mysql-py> procedures = i_s.ROUTINES \ -> .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \ -> .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'PROCEDURE'").execute().fetch_all() mysql-py> procedures [ [ "`db1`", "`proc1`" ], [ "`db1`", "`proc2`" ], [ "`db1`", "`proc``3`" ] ] mysql-py> sql_fmt = "DROP PROCEDURE {0}.{1}" mysql-py> for procedure in procedures: -> print(sql_fmt.format(*procedure)) -> session.sql(sql_fmt.format(*procedure)).execute() DROP PROCEDURE `db1`.`proc1` DROP PROCEDURE `db1`.`proc2` DROP PROCEDURE `db1`.`proc``3` Query OK, 0 rows affected (0.0976 sec)

Again, it is possible to create a function, so the code can be re-used.

Functions and Procedures – Function

Since the code for deleting functions and procedures is so similar – it is just the filter and DROP FUNCTION versus DROP PROCEDURE that is the difference – it is simple to use the same function to delete either. In fact, it would not take much to combine it with events_by_schema() from above, but that will be left as an exercise for the reader (there is a hint in the next section using MySQL Connector/Python).

The code for the routines_by_schema() function is:

from mysqlsh import globals def routines_by_schema(schema, routine_type=None, verbose=True): """Drops all routines of a given type in the schema passed as an argument. If no type is given, both functions and procedures are dropped. The default is to drop both. Requires a connection to exist to the MySQL instance where the routines are to be dropped. Optionally specify whether to be verbose (enabled by default) by printing each DROP FUNCTION|PROCEDURE statement executed. Returns the number of routines dropped and False if an issue is encountered so no attempts are made to drop the routines. """ # Get the session and ensure it is connected if not globals.session.is_open(): print("Please create a connection first.") return False # Define the query to get the routines i_s = globals.session.get_schema("information_schema") filters = ["ROUTINE_SCHEMA = :schema"] if routine_type is not None: filters.append("ROUTINE_TYPE = :type") stmt = i_s.ROUTINES.select( "sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName", "ROUTINE_TYPE") stmt = stmt.where(" AND ".join(filters)) # Execute the query and check for warnings stmt = stmt.bind("schema", schema) if routine_type is not None: stmt = stmt.bind("type", routine_type) result = stmt.execute() routines = result.fetch_all() routines = result.fetch_all() if (result.get_warnings_count() > 0): # Bail out and print the warnings print("Warnings occurred - bailing out:") print(result.get_warnings()) return False # Drop the routines and check for warnings after each routine sql_fmt = "DROP {2} {0}.{1}" for routine in routines: sql = sql_fmt.format(*routine) if verbose: print(sql) drop_result = globals.session.sql(sql).execute() if (drop_result.get_warnings_count() > 0): print("Warnings occurred:") print(result.get_warnings()) return len(routines)

The function takes an extra argument compared to events_by_schema(), so it is possible to either delete both functions and procedures (the default) or just one type. The use of the function is also similar to what have been seen before:

mysql-py> num_delete_functions = delete_objects.routines_by_schema('db1', 'FUNCTION') DROP FUNCTION `db1`.`func1` DROP FUNCTION `db1`.`func2` DROP FUNCTION `db1`.`func``3` mysql-py> num_delete_procedure = delete_objects.routines_by_schema('db1', 'PROCEDURE') DROP PROCEDURE `db1`.`proc1` DROP PROCEDURE `db1`.`proc2` DROP PROCEDURE `db1`.`proc``3` # Restore the functions and procedures mysql-py> num_delete_routines = delete_objects.routines_by_schema('db1') DROP FUNCTION `db1`.`func1` DROP FUNCTION `db1`.`func2` DROP FUNCTION `db1`.`func``3` DROP PROCEDURE `db1`.`proc1` DROP PROCEDURE `db1`.`proc2` DROP PROCEDURE `db1`.`proc``3`

The last thing is to look at how the same actions can be done with MySQL Connector/Python.

MySQL Connector/Python

Given how similar using Python in MySQL Shell is with MySQL Connector/Python, it is natural to make the functions available as a command-line tool or function for Connector/Python programs. The following shows an example of how that can be done – combining events_by_schema() and routines_by_schema() into one function. The source code is:

import mysqlx def objects_by_schema(session, schema, object_type=None, verbose=True): """Drops all events, functions, and/or procedures in the schema passed as an argument. Requires a connection to exist to the MySQL instance where the events are to be dropped. The object_type can be None (drop all events, functions, and procedures - the default), "EVENT", "FUNCTION", or "PROCEDURE". Optionally specify whether to be verbose (enabled by default) by printing each DROP statement executed. Returns the number of events, functions, and procedures dropped as a dictionary or False if an issue is encountered so no attempts are made to drop the events. """ # Get the session and ensure it is connected if not session.is_open(): print("Please create a connection first.") return False # Get an object for the Information Schema # and whether ANSI quotes are used i_s = session.get_schema("information_schema") sql_mode = session.sql("SELECT @@session.sql_mode AS sql_mode").execute() \ .fetch_one()["sql_mode"] sql_modes = sql_mode.split(",") ansi_quotes = "ANSI_QUOTES" if "ANSI_QUOTES" in sql_modes else "" events = [] # If dropping events, get a list of all events if object_type is None or object_type == "EVENT": events_table = i_s.get_table("EVENTS") stmt = events_table.select( "EVENT_SCHEMA AS ObjectSchema", "EVENT_NAME AS ObjectName", "'EVENT' AS ObjectType") stmt = stmt.where("EVENT_SCHEMA = :schema") # Execute the query and check for warnings result = stmt.bind("schema", schema).execute() events = result.fetch_all() if (result.get_warnings_count() > 0): # Bail out and print the warnings print("Warnings occurred - bailing out:") print(result.get_warnings()) return False routines = [] if object_type is None or object_type in ("FUNCTION", "PROCEDURE"): routines_table = i_s.get_table("ROUTINES") filters = ["ROUTINE_SCHEMA = :schema"] if object_type is not None: filters.append("ROUTINE_TYPE = :type") stmt = routines_table.select( "ROUTINE_SCHEMA AS ObjectSchema", "ROUTINE_NAME AS ObjectName", "ROUTINE_TYPE AS ObjectType") stmt = stmt.where(" AND ".join(filters)) # Execute the query and check for warnings stmt = stmt.bind("schema", schema) if object_type is not None: stmt = stmt.bind("type", object_type) result = stmt.execute() routines = result.fetch_all() if (result.get_warnings_count() > 0): # Bail out and print the warnings print("Warnings occurred - bailing out:") print(result.get_warnings()) return False # Drop the routines and check for warnings after each routine objects = events + routines sql_fmt = "DROP {0} {1}.{2}" objects_deleted = { "EVENT": 0, "FUNCTION": 0, "PROCEDURE": 0, } for obj in objects: objects_deleted[obj[2]] += 1 sql = sql_fmt.format(obj[2], mysqlx.helpers.quote_identifier(obj[0], ansi_quotes), mysqlx.helpers.quote_identifier(obj[1], ansi_quotes)) if verbose: print(sql) drop_result = session.sql(sql).execute() if (drop_result.get_warnings_count() > 0): print("Warnings occurred:") print(result.get_warnings()) return objects_deleted if __name__ == "__main__": import sys import argparse import getpass # Handle the command line arguments parser = argparse.ArgumentParser( prog="delete_objects.py", description="Delete all events, functions, and/or procedures from a " + "single schema.") parser.add_argument("--type", default="ALL", choices=["ALL", "EVENT", "FUNCTION", "PROCEDURE"], help="The object type to drop.") parser.add_argument("--schema", default=None, help="The schema to drop objects from.") parser.add_argument("--verbose", type=int, default=1, help="Whether to produce verbose output.") parser.add_argument("--user", default=None, help="The user to connect to MySQL as.") parser.add_argument("--host", default="127.0.0.1", help="The host to connect to MySQL on.") parser.add_argument("--port", type=int, default=33060, help="The mysqlx port to connect to MySQL on.") # Parse the arguments and get the password used when connecting to MySQL args = parser.parse_args(sys.argv[1:]) object_type = args.type if args.type == "ALL": object_type = None verbose = True if args.verbose > 0 else False password = getpass.getpass("MySQL Password: ") # Connect to MySQL session = mysqlx.get_session(user=args.user, password=password, host=args.host, port=args.port) # Drop the objects objects_deleted = objects_by_schema(session, args.schema, object_type, verbose) if verbose: print(objects_deleted)

While the example is pretty extensive, it is not quite complete. There is only support for TCP connections using the user, password, host, and port options, and the password is always provided interactively (the most secure). Support for a configuration file and other options can be added if needed.

Instead of using the sys.quote_identifier() function, the schema and object names are quoted using the mysqlx.helpers.quote_identifier() function. Otherwise the program should be self explanatory given the previous examples. You can use python delete_objects.py --help to get help with the supported arguments.

 

Using AI and satellite imagery for disaster insights

WHAT THE RESEARCH IS:

A framework for using convolutional neural networks (CNNs) on satellite imagery to identify the areas most severely affected by a disaster. This new method has the potential to produce more accurate information in far less time than current manual methods. Ultimately, the goal of this research is to allow rescue workers to quickly identify where aid is needed most, without relying on manually annotated, disaster-specific data sets.

HOW IT WORKS:

Researchers from Facebook and CrowdAI train models on CNNs to detect human-made features, such as roads. Existing approaches for disaster impact analysis require data sets that can be expensive to produce because they require time-consuming manual annotation (for instance, annotating buildings damaged by fire as a new class) to train on. This new method only uses general road and building data sets. These are readily available and can be scalable to other, similar natural disasters. Since differences due to changing seasons, time of day, or other factors can cause erroneous results, the models are first trained to detect these high-level features (i.e., roads). The models then generate prediction masks in regions experiencing a disaster. By computing the relative change between features extracted from snapshots of data captured both before and after a disaster, it’s possible to identify the areas of maximum change.

The researchers also propose a new metric to help quantify the detected changes — the Disaster Impact Index (DII), normalized for different types of features and disasters. When using data sets from the Hurricane Harvey flooding and the Tubbs Fire in Santa Rosa, CA, there was a strong correlation between the model’s calculated DII and the actual areas affected.

WHY IT MATTERS:

During a crisis, it is important to map the entire affected area comprehensively, which is very difficult and time-consuming to do manually. This method could get important disaster mapping information into the hands of rescue and relief workers in significantly less time. Quickly and accurately identifying which areas are most affected allows aid organizations to deliver supplies and aid where they are needed most. In the future, this could be extended to quantify disaster impact on natural features like farmlands and forests, and to assess damage from other disasters, such as earthquakes.

READ THE FULL PAPER:
From satellite imagery to disaster insights

The post Using AI and satellite imagery for disaster insights appeared first on Facebook Code.

How To Install October CMS on Mac

How To Install October CMS on Mac is today’s leading topic. October CMS is built on Laravel  Framework, the best existing PHP framework. Laravel framework includes all of the tools and classes that are necessary for making the quality websites and applications. OctoberCMS adds even more power to Laravel with the collection of simple and flexible features. Development with October can easily be adapted to any existing team workflow. Use your favorite version control system for storing and deploying website themes and plugins. You can build any websites or web apps with OctoberCMS from simple promotional sites to high load social networks.

October CMS Features

There are many features of October CMS. I will be showing you one by one.

The simple modern templating engine

Twig is the simple templating engine, created specifically for web designers. It won’t slow down your app, and it automatically takes care of escaping and other tedious tasks.

Page components

Components are the building blocks for Pages. Just add the component to a Page to add a new functionality and configure it with a Inspector. An Inspector is the visual tool for managing component properties.

Extensible platform

Extend the platform with Plugins that seamlessly integrate with October’s back-end and front-end. Plugin classes are elementary, and you can describe the Plugin and register the features that you want in October.

Easy administrative interfaces

Effortlessly create back-end interfaces for your plugins. Back-end pages require minimal PHP coding and use simple configuration files.

Simple AJAX framework

The AJAX framework lets you efficiently bind the AJAX request to the form or a button. These requests can be handled by the components or by your code in a Page or Layout.

How To Install October CMS on Mac

First, you need to download the zip file from its documentation. You can find it on here.

There are some system requirements. I am listing here.

  1. PHP version 7.0 or higher
  2. PDO PHP Extension
  3. cURL PHP Extension
  4. OpenSSL PHP Extension
  5. Mbstring PHP Library
  6. ZipArchive PHP Library
  7. GD PHP Library

Now, after downloading that file, you need to place that zip folder inside your server root. If you are using localhost via MAMP or XAMPP, then your web root might be an htdocs folder. So you need to place that zip file inside that folder and now unzip them.

Next step, is to permit the folder and its subfolders.

Next step is to open the browser and navigate to this URL: http://localhost/install-master/install.php

My OctoberCMS folder’s name is install-master, and yours might be as well.

If you navigate to this URL, then you might see the below screen.

 

Click agree and continue to the next step.

Now, you need to create a database inside the MySQL. So create one database inside phpmyadmin.

After that, navigate to the octobercms’ database configuration page and fill the details.

 

After filling all the information, click the Administrator button.

Now, fill the administrator details.

 

After clicking the continue button, you have three options.

 

For this demo, let us start with Start from scratch option.

Now, you can see that our installation process has begun. Finally, our installation has successful, and you can see that we got the two URLs — one for frontend and one for the backend.

 

Finally, How To Install October CMS on Mac tutorial is over. Thanks for taking.

The post How To Install October CMS on Mac appeared first on AppDividend.

Database Backup Encryption - Best Practices

Offsite backup storage should be a critical part of any organisation’s disaster recovery plan. The ability to store data in a separate physical location, where it could survive a catastrophic event which destroys all the data in your primary data center, ensures your data survival and continuity of your organisation. A Cloud storage service is quite a good method to store offsite backups. No matter if you are using a cloud provider or if you are just copying data to an external data center, the backup encryption is a must in such cases. In one of our previous blogs, we discussed several methods of encrypting your backups. Today we will focus on some best practices around backup encryption.

Ensure that your secrets are safe Related resources  How to Encrypt Your MySQL & MariaDB Backups

To encrypt and decrypt your data you have to use some sort of a password or a key. Depending on the encryption method (symmetrical or asymmetrical), it can be one secret for both encryption and decryption or it can be a public key for encryption and a private key for decryption. What is important, you should keep those safe. If you happen to use asymmetric encryption, you should focus on the private key, the one you will use for decrypting backups.

You can store keys in a key management system or a vault - there are numerous options on the market to pick from like Amazon’s KMS or Hashicorp’s Vault. Even if you decide not to use those solutions, you still should apply generic security practices like to ensure that only the correct users can access your keys and passwords. You should also consider preparing your backup scripts in a way that you will not expose keys or passwords in the list of running processes. Ideally, put them in the file instead of passing them as an argument to some commands.

Consider asymmetric encryption

The main difference between symmetric and asymmetric encryption is that while using symmetric encryption for both encryption and decryption, you use a single key or password. This requires higher security standards on both ends of the process. You have to make sure that the host on which you encrypt the data is very secure as a leak of the symmetric encryption key will allow the access to all of your encrypted backups.

On the other hand, if you use asymmetric encryption, you have two keys: the public key for encrypting the data and the private key for decryption. This makes things so much easier - you don’t really have to care about the public key. Even if it would be compromised, it will still not allow for any kind of access to the data from backups. You have to focus on the security of the private key only. It is easier - you are most likely encrypting backups on a daily basis (if not more frequent) while restore happens from time to time, making it feasible to store the private key in more secure location (even on a dedicated physical device). Below is a very quick example on how you can use gpg to generate a key pair and use it to encrypt data.

First, you have to generate the keys:

root@vagrant:~# gpg --gen-key gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. gpg: directory `/root/.gnupg' created gpg: new configuration file `/root/.gnupg/gpg.conf' created gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run gpg: keyring `/root/.gnupg/secring.gpg' created gpg: keyring `/root/.gnupg/pubring.gpg' created Please select what kind of key you want: (1) RSA and RSA (default) (2) DSA and Elgamal (3) DSA (sign only) (4) RSA (sign only) Your selection? RSA keys may be between 1024 and 4096 bits long. What keysize do you want? (2048) 4096 Requested keysize is 4096 bits Please specify how long the key should be valid. 0 = key does not expire <n> = key expires in n days <n>w = key expires in n weeks <n>m = key expires in n months <n>y = key expires in n years Key is valid for? (0) Key does not expire at all Is this correct? (y/N) y You need a user ID to identify your key; the software constructs the user ID from the Real Name, Comment and Email Address in this form: "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>" Real name: Krzysztof Ksiazek Email address: my@backups.cc Comment: Backup key You selected this USER-ID: "Krzysztof Ksiazek (Backup key) <my@backups.cc>" Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? o You need a Passphrase to protect your secret key.

This created both public and private keys. Next, you want to export your public key to use for encrypting the data:

gpg --armor --export my@backups.cc > mybackupkey.asc

Next, you can use it to encrypt your backup.

root@vagrant:~# xtrabackup --backup --stream=xbstream | gzip | gpg -e --armor -r my@backups.cc -o /backup/pgp_encrypted.backup

Finally, an example how you can use your primary key (in this case it’s stored in the local key ring) to decrypt your backups:

root@vagrant:/backup# gpg -d /backup/pgp_encrypted.backup | gunzip | xbstream -x encryption: using gcrypt 1.6.5 You need a passphrase to unlock the secret key for user: "Krzysztof Ksiazek (Backup key) <my@backups.cc>" 4096-bit RSA key, ID E047CD69, created 2018-11-19 (main key ID BC341551) gpg: gpg-agent is not available in this session gpg: encrypted with 4096-bit RSA key, ID E047CD69, created 2018-11-19 "Krzysztof Ksiazek (Backup key) <my@backups.cc>" Rotate your encryption keys

No matter what kind of encryption you implemented, symmetric or asymmetric, you have to think about the key rotation. First of all, it is very important to have a mechanism in place to rotate the keys. This might be useful in case of a security breach, and you would have to quickly change keys that you use for backup encryption and decryption. Of course, in case of a security breach, you need to consider what is going to happen with the old backups which were encrypted using compromised keys. They have been compromised although they still may be useful and required as per Recovery Point Objective. There are couple of options including re-encrypting them or moving them to a non-compromised localization.

Speed up the encryption process by parallelizing it

If you have an option to implement parallelization of the encryption process, consider it. Encryption performance mostly depends on the CPU power, thus allowing more CPU cores to work in parallel to encrypt the file should result in much smaller encryption times. Some of the encryption tools give such option. One of them is xtrabackup which has an option to use embedded encryption and parallelize the process.

What you are looking for is either “--encrypt-key” or “--encrypt-key-file” options which enable embedded encryption. While doing that you can also define “--encrypt-threads” and “--encrypt-chunk-size”. Second increases a working buffer for encryption, first defines how many threads should be used for encryption.

Of course, this is just one of the solutions you can implement. You can achieve this using shell tools. An example below:

root@vagrant:~# files=2 ; mariabackup --user=root --backup --pass=pass --stream=xbstream |split -b 60M - backup ; ls backup* | parallel -j ${files} --workdir "$(pwd)" 'echo "encrypting {}" ; openssl enc -aes-256-cbc -salt -in "{}" -k mypass > "111{}"'

This is by no means a perfect solution as you have to know in advance how big, more or less, the backup will be to split it to predefined number of files matching the parallelization level you want to achieve (if you want to use 2 CPU cores, you should have two files, if you want to use 4 cores, 4 files etc). It also requires disk space that is twice the size of the backup, as at first it generates multiple files using split and then encryption creates another set of encrypted files. On the other hand, if your data set size is acceptable and you would like to improve encryption performance, that’s an option you can consider. To decrypt the backup you will have to decrypt each of the individual files and then use ‘cat’ to join them together.

Test your backups

No matter how you are going to implement the backup encryption, you have to test it. First of all, all backups have to be tested, encrypted or not. Backups may not be complete, or may suffer from some type of corruption. You cannot be sure that your backup can be restored until you actually perform the restore. That’s why regular backup verification is a must. Encryption adds more complexity to the backup process. Issues may show up at the encryption time, again - bugs or glitches may corrupt the encrypted files. Once encrypted, the question is then if it is possible to decrypt it and restore?

You should have a restore test process in place. Ideally, the restore test would be executed after each backup. As a minimum, you should test your backups a couple of times per year. Definitely you have to test it as soon as a change in the backup process had been introduced. Have you added compression to the backup? Did you change the encryption method? Did you rotate the encryption key? All of those actions may have some impact on your ability to actually restore your backup. Therefore you should make sure you test the whole process after every change.

ClusterControl can automate the verification process, both on demand or scheduled after every backup.

To verify an existing backup, you just need to pick the one from the list, click on “Restore” option and then go through the restore wizard. First, you need to verify which backup you want to restore.

Then, on the next step, you should pick the restore and verify option.

You need to pass some information about the host on which you want to test the restore. It has to be accessible via SSH from the ClusterControl instance. You may decide to keep the restore test server up and running (and then dump some partial data from it if you wanted to go for a partial restore) or shut it down.

The final step is all about verifying if you made the correct choices. If yes, you can start the backup verification job.

If the verification completed successfully, you will see that the backup is marked as verified on the list of the backups.

If you want to automate this process, it is also possible with ClusterControl. When scheduling the backup you can enable backup verification:

This adds another step in the backup scheduling wizard.

Here you again have to define the host which you want to use for backup restore tests, decide if you want to install the software on it (or maybe you already have it done), if you want to keep the restore server up and whether you want to test the backup immediately after it is completed or maybe you want to wait a bit.

Tags:  MySQL MariaDB backup encryption security

Shinguz: MariaDB indexing of NULL values

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras:  Optimizer null index mariadb

Shinguz: MariaDB indexing of NULL values

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras:  Optimizer null index mariadb

Shinguz: MariaDB indexing of NULL values

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras:  Optimizer null index mariadb

Shinguz: MariaDB indexing of NULL values

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras:  Optimizer null index mariadb

Percona Server for MySQL 5.6.42-84.2 Is Now Available

Percona announces the release of Percona Server 5.6.42-84.2 on November 29, 2018 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.6.42, including all the bug fixes in it, Percona Server 5.6.42-84.2 is the current GA release in the Percona Server 5.6 series. All of Percona‘s software is open-source and free.

Improvements
  • PS-4790: Improve user statistics accuracy
Bugs Fixed
  • Slave replication could break if upstream bug #74145 (FLUSH LOGS improperly disables the logging if the log file cannot be accessed) occurred in master. Bug fixed PS-1017 (Upstream #83232).
  • The binary log could be corrupted when the disk partition used for temporary. files (tmpdir system variable) had little free space. Bug fixed PS-1107 (Upstream #72457).
  • PURGE CHANGED_PAGE_BITMAPS did not work when the innodb_data_home_dir system variable was used. Bug fixed PS-4723.
  • Setting the tokudb_last_lock_timeout variable via the command line could cause the server to stop working when the actual timeout took place. Bug fixed PS-4943.
  • Dropping TokuDB table with non-alphanumeric characters could lead to a crash. Bug fixed PS-4979.

Other bugs fixed

  • PS-4781: sql_yacc.yy uses SQLCOM_SELECT instead of SQLCOM_SHOW_XXXX_STATS
  • PS-4529: MTR: index_merge_rocksdb2 inadvertently tests InnoDB instead of MyRocks
  • PS-4746: Revert our fix for PS-3851 (Percona Ver 5.6.39-83.1 Failing assertion: sym_node->table != NULL)
  • PS-4773: Percona Server sources can’t be compiled without server
  • PS-4785: Setting version_suffix to NULL leads to handle_fatal_signal (sig=11) in Sys_var_version::global_value_ptr
  • PS-4813: Using flush_caches leads to SELinux denial errors
  • PS-4881: Add LLVM/clang 7 to Travis-CI

Find the release notes for Percona Server for MySQL 5.6.42-84.2 in our online documentation. Report bugs in the Jira bug tracker.

 

MySQL High Availability Framework Explained – Part I

In this two-part blog series, we will explain the details and functionality of a High Availability (HA) framework for MySQL hosting using MySQL semisynchronous replication and the Corosync plus Pacemaker stack. In Part I, we’ll walk you through the basics of High Availability, the components of an HA framework, and then introduce you to the HA framework for MySQL.

What is High Availability?

The availability of a computer system is the percentage of time its services are up during a period of time. It’s generally expressed as a series of 9′s. For example, the table below shows availability and the corresponding downtime measured over one year.

Availability % Downtime Per Year 90% (“one 9“) 36.53 days 99% (“two 9s“) 3.65 days 99.9% (“three 9s“) 8.77 hours 99.99% (“four 9s“) 52.60 minutes 99.999% (“five 9s“) 5.26 minutes 99.9999% (“six 9s“) 31.56 seconds

The meaning of High Availability varies depending on the requirements of your application and business.  For example, if you cannot afford a downtime of more than a few minutes per year in your service, we say that the service needs to have 99.999% High Availability.

Components of an HA Framework

The essence of being highly available is the ability to instantaneously recover from failures that can happen in any part of a system. There are four highly essential components in any HA framework that need to work together in an automated fashion to enable this recoverability. Let’s review  these components in detail:

1. Redundancy in Infrastructure & Data

For a service to be highly available, we need to ensure that there is a redundancy in the infrastructure hosting as well as an up-to-date redundant copy of data the service uses or provides. This acts as a standby service ready to take over in case the primary is impacted by failures.

2. Failure Detection & Correction Mechanism

It’s extremely important to immediately detect any failures in any part of the primary system that may impact its availability. This will enable the framework to either take corrective actions on the same primary system, or failover the services to a standby system.

3. Failover Mechanism

This component handles the responsibility to failover the services to your standby infrastructure. Please note that in case there are multiple redundant systems available, this failover mechanism component has to identify the most suitable system among those and promote it as the primary service.

4. Application/User Redirection Mechanism

Once the standby systems have taken over as the primary, this component ensures that all of the application and user connections start happening to the new primary.

MySQL High Availability Framework Explained - Part IClick To Tweet The HA Framework for MySQL

Based on the above model, we use the following HA framework for our MySQL hosting at ScaleGrid:

  • A 3-Node Master-Slave setup using MySQL semisynchronous replication to provide infrastructure and data redundancy.
  • The Corosync plus Pacemaker stack to provide failure detection, correction, and failover mechanism.
  • A DNS mapping or Virtual IP component to provide the application and user redirection mechanism.

Check out the diagram below to visualize the software stack of this architecture:

Let’s review the functionality of some of the key components in this framework.

  1. Corosync

    Corosync provides a communication framework for the nodes with reliable message-passing between them. It forms a cluster ring of nodes, and keeps track of the nodes joining and leaving the cluster through cluster membership. Corosync closely works with Pacemaker to communicate about the node availability so that Pacemaker can take appropriate decisions.

  2. Pacemaker

    Also known as Cluster Resource Manager (CRM), Pacemaker ensures the high availability for MySQL running on the cluster and detects and handles node-level failures by interfacing with Corosync. It also detects and handles failures of MySQL by interfacing with the Resource Agent (RA). Pacemaker configures and manages the MySQL resource through start, stop, monitor, promote, and demote operations.

  3. Resource Agent

    The Resource Agent acts as an interface between MySQL and Pacemaker. It Implements start, stop, promote, demote, and monitor operations that are invoked by the Pacemaker.  There is a fully-functional Resource Agent called Percona Replication Manager (PRM) for MySQL implemented by Percona. This has been enhanced by ScaleGrid and is available on our GitHub page.

  4. DNS Mapping Component

    The Resource Agent, on completing a successful failover, invokes this component which updates the DNS records of the master MySQL server with the IP address of the new master. Note that clients always use a master DNS name to connect with the MySQL server, and by managing the mapping of this DNS name to the IP address of the current master, we can ensure that clients do not have to change their connection strings or properties when there is a failover.

In Part II of this blog series, you’ll learn about the critical data redundancy component which is achieved using MySQL semisynchronous replication. We’ll also dive deep into the semisynchronous replication details and configurations that we use to achieve our high availability support, and lastly, review various failure scenarios and the way the framework responds and recovers from these conditions.

Cloud Backup Options for MySQL & MariaDB Databases

The principal objective of backing up your data is, of course, the ability to roll back and access your archives in case of hardware failure. To do business today, you need the certainty of knowing that in the case of disaster, your data will be protected and accessible. You would need to store your backups offsite, in case your datacenter goes down in flames.

Data protection remains a challenge for small and medium-sized businesses. Small-to-medium sized businesses prefer to archive their company’s data using direct-attached storage, with the majority of firms having plans to do offsite backup copies. Local storage approach can lead to one of the most severe dilemmas the modern company can face - loss of data in case of disaster.

Related resources  Database Backups - Comparing MariaDB Mariabackup and Percona Xtrabackup  How to do Point-in-Time Recovery of MySQL & MariaDB Data using ClusterControl  MySQL & MariaDB Database Backup Resources  How to Encrypt Your MySQL & MariaDB Backups  Simplified Backup & Recovery Management for Polyglot Database Infrastructures

Many factors come into deliberation when judging on whether to allow a business critical database to be transferred offsite, and when choosing a suitable vendor to do so. Traditional methods like writing to tape and shipping to a remote location can be a complicated process that requires special hardware, adequately trained staff and procedures to ensure that backups are regularly produced, protected and that the information contained in them is verified for integrity. Small businesses usually have small IT budgets. Often they can not afford to have a secondary datacenter, even if they have a dedicated data center. But nevertheless, it is still important to keep a copy of your backup files offsite. Disasters like hurricane, flood, fire or theft can destroy your servers and storage. Keeping backed up data in the separate data center ensures data is safe, no matter what is going on in your primary datacenter. Cloud storage is a great way of addressing this problem.
With the cloud backup approach, there are a number of factors to consider. Some of the questions you have are:

  • Is backed-up data secured at rest in the external data center?
  • Is transfer to or from the external data center through the public internet network safe?
  • Is there an effect on RTO (Recovery Time Objective)?
  • Is the backup and recovery process easy enough for our IT staff?
  • Are there any changes required to existing processes?
  • Are the 3rd party backup tools needed?
  • What are the additional costs in terms of required software or data transfer?
  • What are the storage costs?
Backup features when doing a backup to the cloud

If your MySQL server or backup destination is located in an exposed infrastructure like a public cloud, hosting provider or connected through an untrusted WAN network, you need to think about additional actions in your backup policy. There are few different ways to perform database backups for MySQL, and depending on the type of backup, recovery time, size, and infrastructure options will vary. Since many of the cloud storage solutions are simply storage with different API front ends, any backup solution can be performed with a bit of scripting. So what are the options we have to make process smooth and secure?

Encryption

It is always a good idea to enforce encryption to enhance the security of backup data. A simple use case to implement encryption is where you want to push the backup to an offsite backup storage located in the public cloud.

When creating an encrypted backup, one thing to have in mind is that it usually takes more time to recover. The backup has to be decrypted before any recovery activities. With a big dataset, this could introduce some delays to the RTO.

On the other hand, if you are using private key for encryption, make sure to store the key in a safe place. If the private key is missing, the backup will be useless and unrecoverable. If the key is stolen, all created backups that use the same key would be compromised as they are no longer secured. You can use the popular GnuPG or OpenSSL to generate the private or public keys.
To perform mysqldump encryption using GnuPG, generate a private key and follow the wizard accordingly:

$ gpg --gen-key

Create a plain mysqldump backup as usual:

$ mysqldump --routines --events --triggers --single-transaction db1 | gzip > db1.tar.gz

Encrypt the dump file and remove the older plain backup:

$ gpg --encrypt -r ‘admin@email.com’ db1.tar.gz $ rm -f db1.tar.gz

GnuPG will automatically append .gpg extension on the encrypted file. To decrypt,
simply run the gpg command with --decrypt flag:

$ gpg --output db1.tar.gz --decrypt db1.tar.gz.gpg

To create an encrypted mysqldump using OpenSSL, one has to generate a private key and a public key:
OpenSSL req -x509 -nodes -newkey rsa:2048 -keyout dump.priv.pem -out dump.pub.pem

This private key (dump.priv.pem) must be kept in a safe place for future decryption. For mysqldump, an encrypted backup can be created by piping the content to openssl, for example

mysqldump --routines --events --triggers --single-transaction database | openssl smime -encrypt -binary -text -aes256 -out database.sql.enc -outform DER dump.pub.pem

To decrypt, simply use the private key (dump.priv.pem) alongside the -decrypt flag:
openssl smime -decrypt -in database.sql.enc -binary -inform

DEM -inkey dump.priv.pem -out database.sql

Percona XtraBackup can be used to encrypt or decrypt local or streaming backups with xbstream option to add another layer of protection to the backups. Encryption is done with the libgcrypt library. Both --encrypt-key option and --encryptkey-file option can be used to specify the encryption key. Encryption keys can be generated with commands like

$ openssl rand -base64 24 $ bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1

This value then can be used as the encryption key. Example of the innobackupex command using the --encrypt-key:

$ innobackupex --encrypt=AES256 --encrypt-key=”bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1” /storage/backups/encrypted

The output of the above OpenSSL command can also be redirected to a file and can be treated as a key file:

openssl rand -base64 24 > /etc/keys/pxb.key

Use it with the --encrypt-key-file option instead:

innobackupex --encrypt=AES256 --encrypt-key-file=/etc/keys/pxb.key /storage/backups/encrypted

To decrypt, simply use the --decrypt option with appropriate --encrypt-key or --encrypt-key-file:

$ innobackupex --decrypt=AES256 --encrypt-key=”bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1” /storage/backups/encrypted/2018-11-18_11-10-09/

For more information about MySQL and MariaDB encryption, please check our another blog post.

Compression

Within the database cloud backup world, compression is one of your best friends. It can not only save storage space, but it can also significantly reduce the time required to download/upload data.
There are lots of compression tools available out there, namely gzip, bzip2, zip, rar, and 7z.
Normally, mysqldump can have best compression rates as it is a flat text file. Depending on the compression tool and ratio, a compressed mysqldump can be up to 6 times smaller than the original backup size. To compress the backup, you can pipe the mysqldump output to a compression tool and redirect it to a destination file. You can also skip several things like comments, lock tables statement (if InnoDB), skip GTID purged and triggers:

mysqldump --single-transaction --skip-comments --skip-triggers --skip-lock-tables --set-gtid-purged OFF --all-databases | gzip > /storage/backups/all-databases.sql.gz

With Percona Xtrabackup, you can use the streaming mode (innobackupex), which sends the backup to STDOUT in special tar or xbstream format instead of copying files to the backup directory. Having a compressed backup could save you up to 50% of the original backup size, depending on the dataset. Append the --compress option in the backup command. By using the xbstream in streaming backups, you can speed up the compression process by using the --compress-threads option. This option specifies the number of threads created by xtrabackup for parallel data compression. The default value for this option is 1. To use this feature, add the option to a local backup. An example backup with compression:

innobackupex --stream=xbstream --compress --compress-threads=4 > /storage/backups/backup.xbstream

Before applying logs during the preparation stage, compressed files will need to be
decompressed using xbstream:
Then, use qpress to extract each file ending with .qp in their respective directory before
running --apply-log command to prepare the MySQL data.

$ xbstream -x < /storage/backups/backup.xbstream Limit network throughput

An great option for cloud backups is to limit network streaming bandwidth (Mb/s) when doing a backup. You can achieve that with pv tool. The pv utility comes with data modifiers option -L RATE, --rate-limit RATE which limit the transfer to a maximum of RATE bytes per second. Below example will restrict it to 2MB/s.

$ pv -q -L 2m

In below example, you can see xtrabackup with parallel gzip, encryption

/usr/bin/innobackupex --defaults-file=/etc/mysql/my.cnf --galera-info --parallel 4 --stream=xbstream --no-timestamp . | pv -q -L 2m | pigz -9 - | openssl enc -aes-256-cbc -pass file:/var/tmp/cmon-008688-19992-72450efc3b6e9e4f.tmp > /home/ubuntu/backups/BACKUP-3445/backup-full-2018-11-28_213540.xbstream.gz.aes256 ) 2>&1. Transfer backup to Cloud

Now when your backup is compressed and encrypted, it is ready for transfer.

Google cloud

The gsutil command line tool is used to manage, monitor and use your storage buckets on Google Cloud Storage. If you already installed the gcloud util, you already have the gsutil installed. Otherwise, follow the instructions for your Linux distribution from here.

To install the gcloud CLI you can follow below procedure:

curl https://sdk.cloud.google.com | bash

Restart your shell:

exec -l $SHELL

Run gcloud init to initialize the gcloud environment:

gcloud init

With the gsutil command line tool installed and authenticated, create a regional storage bucket named mysql-backups-storage in your current project.

gsutil mb -c regional -l europe-west1 gs://severalnines-storage/ Creating gs://mysql-backups-storage/ Amazon S3

If you are not using RDS to host your databases, it is very probable that you are doing your own backups. Amazon’s AWS platform, S3 (Amazon Simple Storage Service) is a data storage service that can be used to store database backups or other business critical files. Either it’s Amazon EC2 instance or your on-prem environment you can use the service to secure your data.

While backups can be uploaded through the web interface, the dedicated s3 command line interface can be used to do it from the command line and through backup automation scripts. If backups are to be kept for a very long time, and recovery time isn’t a concern, backups can be transferred to Amazon Glacier service, providing much cheaper long-term storage. Files (amazon objects) are logically stored in a huge flat container named bucket. S3 presents a REST interface to its internals. You can use this API to perform CRUD operations on buckets and objects, as well as to change permissions and configurations on both.

The primary distribution method for the AWS CLI on Linux, Windows, and macOS is pip, a package manager for Python. Instruction can be found here.

aws s3 cp severalnines.sql s3://severalnine-sbucket/mysql_backups

By default S3 provides eleven 9s object durability. It means that if you store 1.000.000.000 (1 billion) objects into it, you can expect to lose 1 object every 10 years on average. The way S3 achieves that impressive number of 9s is by replicating the object automatically in multiple Availability Zones, which we’ll talk about in another post. Amazon has regional datacenters all around the world.

Microsoft Azure Storage

Microsoft’s public cloud platform, Azure, has storage options with their control line interface. Information can be found here. The open-source, cross-platform Azure CLI provides a set of commands for working with the Azure platform. It gives much of the functionality seen in the Azure portal, including rich data access.

The installation of Azure CLI is fairly simple, you can find instructions here. Below you can find how to transfer your backup to Microsoft storage.

az storage blob upload --container-name severalnines --file severalnines.sql --name severalnines_backup ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE Hybrid Storage for MySQL and MariaDB backups

With the growing public and private cloud storage industry, we have a new category called hybrid storage. This technology allows the files to be stored locally, with changes automatically synced to remote in the cloud. Such an approach is coming from the need of having recent backups stored locally for fast restore (lower RTO), as well as business continuity objectives.
The important aspect of efficient resource usage is to have separate backup retentions. Data that is stored locally, on redundant disk drives would be kept for a shorter period while cloud backup storage would be held for a longer time. Many times the requirement for longer backup retention comes from legal obligations for different industries (like telecoms having to store connection metadata). Cloud providers like Google Cloud Services, Microsoft Azure and Amazon S3 each offer virtually unlimited storage, decreasing local space needs. It allows you to retain your backup files longer, for as long as you would like and not have concerns around local disk space.

ClusterControl backup management - hybrid storage

When scheduling backup with ClusterControl, each of the backup methods are configurable with a set of options on how you want the backup to be executed. The most important for the hybrid cloud storage would be:

  • Network throttling
  • Encryption with the build in key management
  • Compression
  • Retention period for the local backups
  • Retention period for the cloud backups
ClusterControl dual backup retention ClusterControl advanced backup features for cloud, parallel compression, network bandwitch limit, encryption etc ... Conclusion

The cloud has changed the data backup industry. Because of its affordable price point, smaller businesses have an offsite solution that backs up all of their data.

Your company can take advantage of cloud scalability and pay-as-you-go pricing for growing storage needs. You can design a backup strategy to provide both local copies in the datacenter for immediate restoration, and a seamless gateway to cloud storage services from AWS, Google and Azure.

Advanced TLS and AES 256-bit encryption and compression features support secure backups that take up significantly less space in the cloud.

Tags:  MySQL MariaDB cloud backup encryption compression

MySQL High Availability: Stale Reads and How to Fix Them

Continuing on the series of blog posts about MySQL High Availability, today we will talk about stale reads and how to overcome this issue.

The Problem

Stale reads is a read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value (source Wiktionary).

A practical scenario is when your application applies INSERT or UPDATE data to your master/writer node, and has to read it immediately after. If this particular read is served from another server in the replication/cluster topology, the data is either not there yet (in case of an INSERT) or it still provides the old value (in case of an UPDATE).

If your application or part of your application is sensitive to stale reads, then this is something to consider when implementing HA/load balancing.

How NOT to fix stale reads

While working with customers, we have seen a few incorrect attempts to fix the issue:

SELECT SLEEP(X)

The most common incorrect approach that we see in Percona support is when customers add a sleep between the write and the read. This may work in some cases, but it’s not 100% reliable for all scenarios, and it can add latency when there is no need.

Let’s review an example where by the time you query your slave, the data is already applied and you have configured your transaction to start with a SELECT SLEEP(1). In this case, you just added 1000ms latency when there was no need for it.

Another example could be when the slave is lagging behind for more than whatever you configured as the parameter on the sleep command. In this case, you will have to create a login to keep trying the sleep until the slave has received the data: potentially it could take several seconds.

Reference: SELECT SLEEP.

Semisync replication

By default, MySQL replication is asynchronous, and this is exactly what causes the stale read. However, MySQL distributes a plugin that can make the replication semi-synchronous. We have seen customers enabling it hoping the stale reads problem will go away. In fact, that is not the case. The semi-synchronous plugin only ensures that at least one slave has received it (IO Thread has streamed the binlog event to relay log), but the action of applying the event is done asynchronously. In other words, stale reads are still a problem with semi-sync replication.

Reference: Semisync replication.

How to PROPERLY fix stale reads

There are several ways to fix/overcome this situation, and each one has its pros and cons:

1) MASTER_POS_WAIT

Consists of executing a SHOW MASTER STATUS right after your write, getting the binlog file and position, connecting on a slave, and executing the SELECT MASTER_POS_WAIT function, passing the binlog file and position as parameters. The execution will block until the slave has applied the position via the function. You can optionally pass a timeout to exit the function in case of exceeding this timeout.

Pros:

  • Works on all MySQL versions
  • No prerequisites

Cons:

  • Requires an application code rewrite.
  • It’s a blocking operation, and can add significant latency to queries in cases where a slave/node is too far behind.

Reference: MASTER_POS_WAIT.

2) WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS

Requires GTID: this is similar to the previous approach, but in this case, we need to track the executed GTID from the master (also available on SHOW MASTER STATUS).

Pros:

  • Works on all MySQL versions.

Cons:

  • Requires an application code rewrite.
  • It’s a blocking operation, can add significant latency to queries in cases where a slave/node is too far behind.
  • As it requires GTID, it only works on versions from 5.6 onwards.

Reference: WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS

3) Querying slave_relay_log_info

Consists of enabling relay_log_info_repository=TABLE and sync_relay_log_info=1 on the slave, and using a similar approach to option 1. After the write, execute  SHOW MASTER STATUS, connect to the slave, and query mysql.slave_relay_log_info , passing the binlog name and position to verify if the slave is already applying a position after the one you got from SHOW MASTER STATUS.

Pros:

  • This is not a blocking operation.
  • In cases where the slave is missing the position you require, you can try to connect to another slave and repeat the process. There is even an option to fail over back to the master if none of the slaves have the said position.

Cons:

  • Requires an application code rewrite.
  • In cases of checking multiple slaves, this can add significant latency.

Reference: slave_relay_log_info.

4) wsrep-sync-wait

Requires Galera/Percona XtraDB Cluster: Consists of setting a global/session variable to enforce consistency. This will block execution of subsequent queries until the node has applied all write-sets from it’s applier queue. It can be configured to trigger on multiple commands, such as SELECT, INSERT, and so on.

Pros:

  • Easy to implement. Built-in as a SESSION variable.

Cons:

  • Requires an application code rewrite in the event that you want to implement the solution on per session basis.
  • It’s a blocking operation, and can add significant latency to queries if a slave/node is too far behind.

Reference: wsrep-sync-wait

5) ProxySQL 2.0 GTID consistent reads

Requires MySQL 5.7 and GTID: MySQL 5.7 returns the GTID generated by a commit as part of the OK package. ProxySQL with the help of binlog readers installed on MySQL servers can keep track of which GTID the slave has already applied. With this information + the GTID received from the OK package at the moment of the write, ProxySQL will decide if it will route a subsequent read to one of the slaves/read nodes or if the master/write node will serve the read.

Pros:

  • Transparent to the application – no code changes are required.
  • Adds minimal latency.

Cons:

  • This still a new feature of ProxySQL 2.0, which is not yet GA.

Referece: GTID consistent reads.

Conclusions

Undesirable issues can arise from adding HA and distributing the load across multiple servers. Stale reads can cause an impact on applications sensitive to them. We have demonstrated various approaches you can use to overcome them.


Photo by Tim Evans on Unsplash

MySQL InnoDB Cluster with 2 Data Centers for Disaster Recovery: howto – part 2

In the first part of this howto, I illustrated how to setup two MySQL InnoDB Cluster linked by an asynchronous replication.

In that solution, I didn’t use any replication filters to ignore the replication of the InnoDB Cluster’s metadata (mysql_innodb_cluster_metadata), but I used the same metadata tables with two different clusters in it.

The benefit is that this allows to backup everything from any node in any of the data center, it works also in MySQL 5.7, and there is not risk to mess up with the replication filters.

In this blog I will show how to use replication filters to link two different clusters. This doesn’t work on MySQL 5.7 because you cannot have filters for a specific channel and if you globally filter the metadata, changes like topology changes, won’t be replicated inside the group and you will have issue with MySQL Shell and MySQL Router. So this solution works only for MySQL 8.0 (since 8.0.1).

Let’s consider we have again the exact same setup: 2 DCs and 6 nodes (see previous post).

On DC1 we have a cluster like this one:

JS> cluster.status(); { "clusterName": "clusterDC1", "defaultReplicaSet": { "name": "default", "primary": "mysql1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "mysql1:3306": { "address": "mysql1:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql2:3306": { "address": "mysql2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql3:3306": { "address": "mysql3:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://clusteradmin@mysql1:3306" }

Now we will take a backup of this running cluster (in the first post, we were using the backup of a single instance, that I called the “production server”).

We choose one of the 3 members and we run the following command (this is using MySQL Enterprise Backup):

/opt/meb/bin/mysqlbackup \ --host 127.0.0.1 \ --backup-dir /vagrant/backup/ \ backup-and-apply-log

And we restore it on the 3 machines that will be part of the new InnoDB Cluster in DC2:

# systemctl stop mysqld # rm -rf /var/lib/mysql/* # /opt/meb/bin/mysqlbackup --backup-dir=/vagrant/backup/ copy-back # chown -R mysql. /var/lib/mysql # systemctl start mysqld

We can also already configure the instances using MySQL Shell:

JS> dba.configureInstance('clusteradmin@mysql4') JS> dba.configureInstance('clusteradmin@mysql5') JS> dba.configureInstance('clusteradmin@mysql6')

When done, as the backup was performed on a machine that was already part of a Group and as I use different name for relay logs on each nodes (default behavior), I need to perform on all 3 new servers the following SQL statement:

SQL> RESET SLAVE ALL FOR CHANNEL "group_replication_applier";

It’s time to create the second cluster:

JS> \c clusteradmin@mysql4 JS> cluster2=dba.createCluster('clusterDC2') JS> cluster2.addInstance('clusteradmin@mysql5') JS> cluster2.addInstance('clusteradmin@mysql6')

And we have now our second cluster:

JS> cluster2.status() { "clusterName": "clusterDC2", "defaultReplicaSet": { "name": "default", "primary": "mysql4:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "mysql4:3306": { "address": "mysql4:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql5:3306": { "address": "mysql5:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql6:3306": { "address": "mysql6:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://clusteradmin@mysql4:3306" }

As recommended, I will create a user to use with replication. On the clusterDC1’s Primary-Master, I enter the following statements in SQL:

SQL> create user 'repl'@'%' identified by 'replication' REQUIRE SSL SQL> grant replication slave on *.* to 'repl'@'%';

And finally setup asynchronous replication between the Primary-Master of DC2 and another member of DC1:

SQL> CHANGE MASTER TO MASTER_HOST='mysql1', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='replication', MASTER_AUTO_POSITION=1, MASTER_SSL=1 FOR CHANNEL 'asyncDC1'; SQL> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(mysql_innodb_cluster_metadata) FOR CHANNEL 'asyncDC1'; SQL> START SLAVE FOR CHANNEL 'asyncDC1';

Of course we can see that it works as expected:

SQL> show slave status FOR CHANNEL 'asyncDC1'\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 2092043 Relay_Log_File: mysql5-relay-bin-asyncdc1.000002 Relay_Log_Pos: 57195 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql_innodb_cluster_metadata Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2092043 Relay_Log_Space: 57405 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 14177781 Master_UUID: 21b458bd-f298-11e8-9bf0-08002718d305 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 463c9331-f29e-11e8-9f64-08002718d305:44-77 Executed_Gtid_Set: 21b458bd-f298-11e8-9bf0-08002718d305:1-12, 4358f6f3-f2a0-11e8-8243-08002718d305:1-14, 463c9331-f29e-11e8-9f64-08002718d305:1-77, 4c63779c-f29f-11e8-918e-08002718d305:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: asyncdc1 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0

We can also verify all this in performance_schema:

SQL> select * from replication_applier_filters\G *************************** 1. row *************************** CHANNEL_NAME: asyncdc1 FILTER_NAME: REPLICATE_IGNORE_DB FILTER_RULE: mysql_innodb_cluster_metadata CONFIGURED_BY: CHANGE_REPLICATION_FILTER_FOR_CHANNEL ACTIVE_SINCE: 2018-11-28 00:13:09.089575 COUNTER: 0 sql> select * from replication_applier_status_by_worker where CHANNEL_NAME='asyncdc1'\G *************************** 1. row *************************** CHANNEL_NAME: asyncdc1 WORKER_ID: 0 THREAD_ID: 78 SERVICE_STATE: ON LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION: 463c9331-f29e-11e8-9f64-08002718d305:77 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2018-11-28 00:14:36.764934 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2018-11-28 00:14:36.794974 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2018-11-28 00:14:36.775163 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2018-11-28 00:14:36.875206 APPLYING_TRANSACTION: APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000

And now we can do the same on the Primary-Master of DC1 to point on the Primary-Master of DC2 (or use MySQL Router as explained in part 1). Don’t forget that this is a Disaster Recovery Solution and that you should not write on both DCs at the same time !

The Symfony Demo Application and MySQL 8

The Symfony Frame work is very popular with PHP developers and it has a very nice Demo Application to help novices learn the intricacies involved. But the demo does not use MySQL. So this blog entry is about re configuring the demo so it works with MySQL 8. And I am using Ubuntu 18.04 LTS to you may have to adjust the following commands to work with your operating system.

This is not difficult but there are some steps that are not exactly self evident that this blog will show you how to get the demo working.  


Preliminaries
The first thing to do is to make sure you have PHP 7.2 or better installed including the php7.2-intl (sudo apt-get install php7.2-intl) package as well as the PDO connector. I will admit I have been using PHP since it appeared and this is the first time I have had to install this package.

And you will want Composer to do the behind the scenes lifting for you and Doctrine to map the relations in the PHP code to the database.  Please see my previous blog on getting Doctrine to work with MySQL 8 (Big hint for the TL;DR crowd, set your .env to DATABASE_URL=mysql://account:password@localhost:3306/databasename ).

You will want to create an account on the MySQL server for use with this demo and then make sure it will have the proper rights to use the new schema.

CREATE USER 'demouser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'S3cr3t#'; 

and  

GRANT ALL on 'databasename'.* to 'demouser'@'localhost'; 


The Demo Now we can start work on the demo itself. 

The first thing to do in a terminal window is type composer create-project symfony/symfony-demo.  Composer will get the demo code ready for you.  Now cd cymfony-demo.   

Change the .env file (you may have to copy the .env-dist to .env and edit it) as noted above DATABASE_URL=mysql://demouser:S3c3t#@localhost:3306/databasename 

Now it is time to use Doctrine to build create the database, the schemas, and load the data.

$ php bin/console doctrine:database:create
$ php bin/console doctrine:schema:create
$ php bin/console doctrine:fixtures:load


Finally enter php bin/console server:run to start the demo. You will get a notice about the URL to use to get to the demo via a web browser. Use that URL in your web browser to get to the actual demo and below you can see that URL is http://127.0.0.1:8000. 


Running the Symfony Demo Application. Note that the 
application is listening on http:127.0.0.1:8000 The Symfony Demo to help you explore this popular PHP Freamwork

10 Node Frameworks to Use in 2019

Introduction

More developers have switched to using JavaScript to build more applications, especially for the web. This has brought about an exponential growth in the usage of frameworks built specifically for the JavaScript community to facilitate quick prototyping and building of awesome projects.

When Node.js was introduced to the tech community in 2009 as a tool for building scalable server-side web applications, it came with a lot of benefits which includes but not limited to the usage of event-driven non-blocking input/output model, single-threaded asynchronous programming amongst others.

The fact that, as a developer, you can easily use the same language both for the client-side and server-side scripting easily increased the quick adoption and rapid the usage of Node.

Over the years, a lot of experienced JavaScript developers have built quite a number of impressive frameworks to easily get started with Node.js when developing web applications.

As we look into 2019, I will list some of the most popular Node.js frameworks that you should consider using for building web applications irrespective of the size.

What is a Node framework?

A Node.js framework is just some abstract design, built out of Node.js, that embodies the control flow of the given framework’s design. So it is almost like the skeleton of a program whereby the customised codes you write kind of makes up as the meat that completes the program.

So for every Node.js function, there would be some generic implementation unique to the framework which would then require the user to follow the lead of the framework by adding more codes to define its use case.

Benefits of Node frameworks

Node.js frameworks are mostly used because of their productivity, scalability and speed, making them one of the first choice for building enterprise applications for companies.

Node.js allows you to write the same language for both your front-end and backend, saving you the stress of learning a new language for some simple implementation, and also helping you maintain the same coding pattern all through.

By using a framework, you can work with a set of tools, guidelines, and recommended practices that help you save time. It also can help solidify the code standards across a team of developers.

Selecting a Node Framework

Selecting a framework can be a bit tricky and subjective to its use case. This is because we choose based on a particular feature we like. Ranging from the weight of the framework on the application, speed, simplicity, learning curve, flexibility and configuration, use case or maybe even popularity in some cases, GitHub stars.

Next, lets take a deep dive into the objective of this post and go through the list of Node.js frameworks that will help boost your productivity when building JavaScript applications, especially on the server-side.

Stars aren't everything so we'll be organizing by what we've seen to be popular on Scotch.io.

1. AdonisJs [GitHub Stars: 5,053]

AdonisJsis a Node.js framework. From the official documentation, "AdonisJs is a Node.js MVC framework that runs on all major operating systems. It offers a stable ecosystem to write a server-side web application so that you can focus on business needs over finalising which package to choose or not."

Adonis is billed as the Laravel of Node. Simplicity and a focus on getting things done.

"We're big fans of Laravel's approach (Scotch is built on Laravel) so when we saw those same principles come to the Node side, we were very excited." - Chris Sevilleja

Why AdonisJS?

AdonisJs has a support for an ORM is made with SQL-databases in mind (PostgreSQL). It creates efficient SQL-queries and is based on active record idea. Its query builder is easy to learn and allows us to build simple queries quickly.

AdonisJs has good support for No-SQL database like MongoDB too. It's MVC structure is quite similar to Laravel, so if you've been using Laravel for web development, AdonisJs will be a walk in the park.

To get started easily check out this comprehensive article by Chimezie here on scotch.io.

2. Express.js [GitHub Stars: 41,036]

Express.js is a fast, non-opinionated, minimalist web framework for Node.js. It is simply a technology built on Node.js which behaves like a middleware to help manage our servers and routes. Looking at the asynchronous nature of Node.js and the fact that Express.js was built on node, the ability to build a light-weight application that can process more than a single request seamlessly actually depends on the serving capability of technologies like express.

It’s robust API allows users to configure routes to send/receive requests between the front-end and the database (acting as a HTTP server framework). A good advantage with express is how it supports a lot of other packages and other template engines such as Pug, Mustache, EJS and a lot more.

Some of the numerous advantages of Express.js includes:

  • Almost the standard for Node.js web middleware

  • Fully customisable

  • Low learning curve

  • Majorly focused on browsers, making templating and rendering an almost out of the box feature.

Express.js has shown, over time, that it’s popularity is worth the hype with its easy to use methods and functions. It is probably the most popular Node.js framework available for the JavaScript community on GitHub with over 41,000 stars [Github stars: 41,036].

Looking at this framework and all it’s exciting abilities, I do not see it going away anytime soon.

3. Meteor.js [GitHub Stars: 40,490]

The Meteor docs defines meteor as a full-stack JavaScript platform for developing modern web and mobile applications. It’s major advantage is it’s realtime update. As changes are made to the web app, it automatically updates the template with the latest changes.

The Node.js framework makes development quite very simplified by providing a platform for the entire tier of the application to be in the same language; JavaScript. Making it function just as efficient in both the server and client side.

Meteor stands the capability of serving large projects like reaction commerce( known to be one of the largest and most popular e-commerce open source projects).

The most fascinating aspect of the Meteor framework is the very rich and organised documentation/large community it has, helping users learn fast by reaching out and getting their hands dirty with projects, very fast.

With the fact that meteor is leveraging on the Facebook GraphQL datastack to come up with meteor Apollo, as far back as 2016, only indicates that they have good plans and a visionary perception of what the future holds for data, how it is managed and how it flows. If there is any list of Node.js frameworks to watch out for, I would probably be arrested if I did not add Meteor to that list.

4. Nest.js [GitHub Stars: 10,128]

NestJs is a framework built with Node.js, It is used for building efficient, scalable Node.js server-side applications. Nest uses progressive JavaScript and is written with TypeScript. Being built with TypeScript means that Nest comes with strong typing and combines elements of OOP(Object Oriented Programming), FP(Functional Programming) and FRP(Functional Reactive Programming).

Nest also makes use of Express, It provides an out of the box application architecture which allows for the effortless creation of highly testable, scalable, loosely coupled, and easily maintainable applications.

Nest CLI can be used to generate nest.js applications with a lot of features out of the box. According to the information on the website, one can contact the nest community of developers behind the nest framework to find out more about expertise consulting, on-site enterprise support, trainings, and private sessions. Isn’t that cool? Well I guess it is, and I also think this also should make it into the list of Node.js frameworks to look out for in 2019.

5. Sails.js [GitHub Stars: 19,887]

According to the official site, Sails is another Node.js framework used to build custom enterprise-grade Node.js apps. It boasts of being the most popular MVC Node.js framework with the support for modern apps requirements. The APIs are data-driven, with a scalable service oriented architecture.

Let us take a closer look at what they mean here. Sails bundles an ORM, waterlines, that makes compatibility possible with almost all databases, going as far as providing a huge number of community projects. Some of its officially supported adapters include MYSQL, Mongo, PostgreSQL, Redis, and even Local Disk.

Looking at the backend, Just by running an installation command, `sails generate api bookstore` for instance, sails blows your mind by providing you some basic blueprints, without you writing any codes at all.

This command provides you endpoints to CRUD bookstore. You think that is all right, check this out: Sails is also compatible with almost all frontend technologies ranging from React, Angular, Backbone, iOS/objective C, Android/java, windows phone and probably even some technologies yet to be created. For this one, 2019 it is! summarised features include:

  • Many automated generators.

  • Requires no additional routing

  • Great frontend compatibility with other frontend technologies.

  • Transparent support for Websockets.

  • Enables faster build of REST API.

  • Compatible with almost all database, thanks to its waterline ORM.

6. Koa.js [GitHub Stars: 23,902]

Referred to as the next generation web framework for Node.js(according to the website), Koa was created by the same team that created Express.js, making it seem like it would pick up from where express left off. Koa is unique in the fact that it uses some really cool ECMAScript(ES6) methods that have not even landed in some browsers yet, it allows you to work without callbacks, while also providing you with an immense increase in error handling. it requires a Node.js version of at least 0.11 or higher.

According to the website, Koa does not bundle any middleware within core, meaning the middlewares are more cascaded/streamlined, and every line of code is quite elegant and granular, thereby allowing you to structure the parts however you want(component-based middlewares). This makes the framework to have more control over configurations and handling.

Koa became futureproof owing to the fact that it could actually ditch the holy grail of asynchronous functionality: callbacks.

Some key features include:

  • Ditched callbacks hell

  • Component-based building blocks

  • Cascading middlewares

  • Quite modular

  • Efficient error handling

This is definitely a framework for the future and I am almost beginning to see that if an article for frameworks to lookout for in the year 2020 comes out, it would still probably make the list.

7. LoopBack.js [GitHub Stars: 11,985]

LoopBack is another Node.js framework with an easy-to-use CLI and a dynamic API explorer. It allows you to create your models based on your schema or dynamic models in the absence of a schema. It is compatible with a good number of REST services and a wide variety of databases including MySQL, Oracle, MongoDB, Postgres and so on.

It has the ability to allow a user build a server API that maps to another server, almost like creating an API that is a proxy for another API. It’s support for native mobile and browser SDKs for clients like Android/Java, iOS, Browser javaScript(Angular).

Key features:

Most of these details were collected from their Website/documentation which I found very exciting to go through and even try to get a basic setup up, Indicating that they have a well structured documentation and a community distributed across different media( StrongLoop blog, LoopBack Google Group, LoopBack Gitter channel ). For instance, the Loopback blog provides lots of tutorials and use cases on how to leverage the use of the technology in different ways.

Amongst some of its powerful users are Go Daddy, Flight Office, Bank of America(Meryll Linch), Symantec, Intellum, ShoppinPal and so on.

8. Hapi.js [GitHub Stars: 10,371]

Just like ExpressJs, the common hapi.js(supported by Walmart Labs) is a Node.js framework that helps serve data by intermediating between the server side and client. It is quite a good substitute for Express(they both have their unique features).

Hapi is a configuration-driven pattern, traditionally modeled to control web server operations. A unique feature it has is the ability to create a server on a specific IP, with features like the ‘onPreHandler’, we can do something with a request before it is completed by intercepting it and doing some pre-processing on the request.

Considering it’s ‘handler’ function where we can call a route and still pass some configurations while making the requests, just to get the function to do something specified in the configuration. This handler, from what we see, acts like a pseudo-middleware.

Let us look at some key features that make hapiJs promising:

  • There is a deeper control over request handling.

  • Detailed API reference and a good support for document generation

  • Has more functions for building web servers

  • Configuration-based approach to some sub-middlewares(pseudo-middlewares)

  • Provides the availability of caching, Authentication, and input validation.

  • Has a plugin-based architecture for scaling.

  • Provides you with really good enterprise plugins like the joi, yar, catbox, boom, tv, travelogue, and so on.

HapiJs might not be as popular [github stars: 10,371] as Express but it has some good backing up and it seems to be gaining some grounds too. It does not seem like it is slowing down its mark and relevance anytime soon.

9. Derby.js [4,350]

According to the Derby.js site, it is a full stack Node.js framework for writing modern web applications. Derby has been around a little while, quite long enough to have proven itself to hop into 2019 and rock some chords. Let’s see what we have here.

DerbyJs provides you with seamless data synchronisation between your server and client with an automatic conflict resolution powered by ShareDB's operational transformation of JSON and text. It permits you the opportunity to add customised codes to build highly efficient web applications.

10. Total.js [Github stars: 3,853]

Total.js boast of being a very fast development Node.js framework, that requires little maintenance, with a good performance and a seamless scaling transition. It shows some promise by giving some insight on their website, where they ask for visitors willing to contribute to the growth of the framework. So far the Total.js team has spent some time trying to get more premium sponsors to join them. This is another indication that they have plans to expand and should be checked out for more growth in the nearest future to come.

Total.js has some really beautiful versions like the Total.js Eshop, which contains a user interface optimized for mobile devices, and downloadable by all premium members. The Eshop is one of the best Node.js e-commerce system. This is because of its many versions of unique content management system(CMS).

Conclusion

If there is anything we can pick from this article, I can bet you must have noticed before now that picking a framework is based on what you actually want to achieve with it.

The Node.js frameworks above have purely shown us that whatever framework we are going for, there is an open and welcoming community out there solving issues and ready to aid you with learning the basics of that particular framework, which a is vital factor to look out for amongst lots more other factors like GitHub contributions, stars, issues and so on. Just for the fun of it, you can find a lot more of Node.js frameworks here.

Please note that all the Node.js framework highlighted in this post were selected based on popularity in the JavaScript community, usage and personal opinion.

Do you know of any other awesome Node.js framework that you feel its worthy of being added to the list? please feel free to mention it in the comment section below.

I do hope you find this post very helpful. Happy coding.

MySQL Partition Manager (Yahoo!) in a nutshell

Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.

In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.

Needs for partitioning:

  • Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
  • Easy Archival or Purge by dropping or truncate  of partition
  • Lesser fragmentation, hence avoiding frequent table optimization.

Partitions management activity like adding/Dropping partition is made easy and automated by using yahoo partition manager.

Implementation:

Implementation of this partition manager tool is quite simple. We can start using by importing this sql file , We will walk through the Internal working of partition manager in this blog.

The partition_manager_settings table

CREATE TABLE `partition_manager_settings` ( `table` varchar(64) NOT NULL COMMENT 'table name', `column` varchar(64) NOT NULL COMMENT 'numeric column with time info', `granularity` int(10) unsigned NOT NULL COMMENT 'granularity of column, i.e. 1=seconds, 60=minutes...', `increment` int(10) unsigned NOT NULL COMMENT 'seconds per individual partition', `retain` int(10) unsigned NULL COMMENT 'seconds of data to retain, null for infinite', `buffer` int(10) unsigned NULL COMMENT 'seconds of empty future partitions to create', PRIMARY KEY (`table`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=Dynamic;

Table breakdown:

Column Name Definition Table Table name which we need to make partitions column name to make partitions granularity factoring granularity in seconds (1 denotes seconds, 60 denotes minutes, 3600 denotes hours) increment Number of seconds per individual partition (86400 denotes 1 day) retain Seconds worth of data to retain or null for infinite buffer Seconds worth of empty feature partitions to maintain

This table (partition_manager_settings) will be created by executing the partition manager script.

Along with partition_manager_settings table by default, it will create an event for automatic execution of this procedure in the specified interval to have a check for dropping and adding partitions based on the conditions.

mysql> show events\G *************************** 1. row *************************** Db: mydbops Name: run_partition_manager Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 86400 Interval field: SECOND Starts: 2000-01-01 00:00:00 Ends: NULL Status: ENABLED Originator: 2345 character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci

Event structure:

mysql> show create event run_partition_manager\G *************************** 1. row *************************** Event: run_partition_manager sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `run_partition_manager` ON SCHEDULE EVERY 86400 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN IF @@global.read_only=0 THEN CALL partition_manager(); END IF; END character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci

By default, this event will run for every day (86400 seconds).

Now let’s see our demo implementation.

A sample test table structure below,

mysql> show create table data\G *************************** 1. row *************************** Table: data Create Table: CREATE TABLE `data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created`), KEY `index_created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

A mandatory thing for creating partitions for a table is to have the column(partition key) based on which are adding has to be a part of the primary key/Unique key

Here we are making `created` as our partition key, which is part of composite primary key “PRIMARY KEY (`id`,`created`)

You need to insert the table and column which you need to do partitions.

mysql> select * from partition_manager_settings\G *************************** 1. row *************************** table: data column: unix_timestamp(created) granularity: 1 increment: 3600 retain: 7200 buffer: 36000

Here I have added the table to create an hourly based partition and drop the partitions which are older than 2 hours. Along with that, it will create 10 empty partitions as a buffer, each time when the partition manager event is being called.

Below is table structure after execution of the partition manager procedure.

mysql> show create table data\G *************************** 1. row *************************** Table: data Create Table: CREATE TABLE `data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created`), KEY `index_created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (unix_timestamp(created)) (PARTITION p_START VALUES LESS THAN (0) ENGINE = InnoDB, PARTITION p_1534402800 VALUES LESS THAN (1534402800) ENGINE = InnoDB, PARTITION p_1534406400 VALUES LESS THAN (1534406400) ENGINE = InnoDB, PARTITION p_1534410000 VALUES LESS THAN (1534410000) ENGINE = InnoDB, PARTITION p_1534413600 VALUES LESS THAN (1534413600) ENGINE = InnoDB, PARTITION p_1534417200 VALUES LESS THAN (1534417200) ENGINE = InnoDB, PARTITION p_1534420800 VALUES LESS THAN (1534420800) ENGINE = InnoDB, PARTITION p_1534424400 VALUES LESS THAN (1534424400) ENGINE = InnoDB, PARTITION p_1534428000 VALUES LESS THAN (1534428000) ENGINE = InnoDB, PARTITION p_1534431600 VALUES LESS THAN (1534431600) ENGINE = InnoDB, PARTITION p_1534435200 VALUES LESS THAN (1534435200) ENGINE = InnoDB, PARTITION p_1534438800 VALUES LESS THAN (1534438800) ENGINE = InnoDB, PARTITION p_1534442400 VALUES LESS THAN (1534442400) ENGINE = InnoDB, PARTITION p_1534446000 VALUES LESS THAN (1534446000) ENGINE = InnoDB, PARTITION p_END VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

In the file system level, each partition has its own tablespace.

mysql> \! ls -lrth /usr/local/var/mysql/mydbops/ total 3608 -rw-r----- 1 dhanasekar admin 61B Jul 18 19:24 db.opt -rw-r----- 1 dhanasekar admin 8.7K Aug 16 13:52 partition_manager_settings.frm -rw-r----- 1 mydbops admin 96K Aug 16 13:54 partition_manager_settings.ibd -rw-r----- 1 mydbops admin 8.5K Aug 16 13:54 data.frm -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_START.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534402800.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534406400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534417200.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534420800.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534424400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534410000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534413600.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534428000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534431600.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534435200.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534442400.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534446000.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_END.ibd -rw-r----- 1 mydbops admin 112K Aug 16 13:54 data#P#p_1534438800.ibd

So Now its easy to maintain the partitions for n number of tables using the partition manager.

The column retains will have the value of how much data which needs to be active in the table. In this case, the retain has the value as 7200 seconds which is 2 hours So it will drop the partitions after the certain period of time.

The next iteration of the procedure call is based on the value of a minimum of increment from the partition_manager_settings table. There is one another partition to correct this setting at the end of the partition_manager procedure.

Last few lines of partition manager procedure:

.... .... close cur_table_list; # confirm schedule for next run call schedule_partition_manager(); /* 5.6.29+/5.7.11+ only - mysql bug 77288 */ END;; DELIMITER ;

The procedure Schedule_partition_manager controls the execution of the “run_partition_manager“, Based on the min increment value of each table which is being managed.

mysql> show create procedure schedule_partition_manager\G *************************** 1. row *************************** Procedure: schedule_partition_manager sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `schedule_partition_manager`() begin declare min_increment int unsigned; set min_increment=null; select min(s.increment) from partition_manager_settings s into min_increment; if min_increment is not null then ALTER DEFINER='root'@'localhost' EVENT run_partition_manager ON SCHEDULE EVERY min_increment SECOND STARTS '2000-01-01 00:00:00' ENABLE; end if; end character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci

Manageability:

The partition_manager_settings table is where we need to add/remove tables to make partitions. This will be executed in an order of insertion in this table.

Key Takeaways/Limitations:

  • This partition manager will only work for range partitions.
  • This directly won’t work for the partitions for timezone dependent.
  • We have to make use of functions to get the required partitions.
  • Ensure that events are always on

This procedure will help you in managing the partition in an efficient way.  I hope this blog post gives you a better idea about yahoo partition manager.

Pages