Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. MySQL 8.0 allows unprivileged user access to its data if table mysql.user does not exists

    Yesterday my Percona colleague Ceri Williams found a nasty bug in MySQL 8.0. Its twin for Percona Server reported at PS-5431.He was installing MySQL Server 8.0 having not supported option in his configuration file. Thus initialization failed, but, surprisingly, the subsequent restart was successful and he was able to create, modify and delete tables in his MySQL installation. In other words: he got full access to the database. But he did not create any user account yet!This new instance of MySQL Server did not have privilege tables, particularly mysql.user, and silently started with --skip-grant-option.At the first glance starting just initialized MySQL Server with a non-safe option should not cause any harm, because datadir has no data yet. But this is what the experienced user expects! Newbies may miss the fact that their server is now accessible worldwide and start inserting data.This is bad by itself, but we experienced more and found that the same behavior may happen if someone removes table mysql.user, then let MySQL Server read its privilege tables. E.g., performs a restart.To demonstrate this behavior I created a table which holds credit card information and inserted data into it:mysql> create table test.very_important_table(    -> id int not null primary key,    -> credit_card_num char(16),    -> credit_card_owner varchar(256),    -> credit_card_expire_month char(2),    -> credit_card_expire_year char(2),    -> credit_card_cvv char(3))    -> engine=innodb;Query OK, 0 rows affected (3.80 sec)mysql> insert into test.very_important_table values(1, '1234123412341234', 'Sveta Smirnova', '02', '20', '123');Query OK, 1 row affected (0.04 sec)mysql> select current_user();+----------------+| current_user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)After that, I removed the table mysql.user (which I did not expect I will be allowed to do):mysql> drop table mysql.user;Query OK, 0 rows affected (0.08 sec) Then I restarted the server and logged in as user foo and was able to access data in the table with credit card information:$ mysql -ufoo test...mysql> select current_user();+-----------------------------------+| current_user()                    |+-----------------------------------+| skip-grants user@skip-grants host |+-----------------------------------+1 row in set (0.00 sec)mysql> select * from very_important_table;+----+------------------+-------------------+--------------------------+-------------------------+-----------------+| id | credit_card_num  | credit_card_owner | credit_card_expire_month | credit_card_expire_year | credit_card_cvv |+----+------------------+-------------------+--------------------------+-------------------------+-----------------+|  1 | 1234123412341234 | Sveta Smirnova    | 02                       | 20                      | 123             |+----+------------------+-------------------+--------------------------+-------------------------+-----------------+1 row in set (0.00 sec) A bug report was considered a feature request in the first turn and now still in the discussion on how serious it is. There are a few reasons why you may not take this flaw seriously.First, there is no harm when someone has access to the empty MySQL instance. Allowing to connect to such an instance may help DBA to solve the issue, for example, by running the mysql_upgrade command. Thought it does not restore mysql.user table anyway:mysql> show tables from mysql like 'user';Empty set (0.00 sec)mysql> \qBye$ mysql_upgradeChecking if update is needed.Checking server version.Error occurred: Query against mysql.user table failed when checking the mysql.session.$ mysqlmysql> show tables from mysql like 'user';Empty set (0.00 sec) Second, it is not expected that someone would drop table mysql.user manually. It is also not harmful for SQL injection attack if you limit access to your application user: one has to have a privilege which allows updating mysql database to perform the drop. It is good practice to do not grant such privileges for application users.Third, while it is possible that mysql tablespace may be corrupted on the file system level, it is very unlikely that it will damage only data, belonging to the privilege tables. There are much higher chances that MySQL Server won't be able to start at all after such a corruption happens.Fourth, to perform such an attack on the file system level, one needs to have access to the file system and can do more significant harm than the behavior reported.These are the reasons why this behavior may be not considered bad security flow.However, for new users of MySQL, it is possible to make such a mistake and silently open access to their database for everyone.
  2. How to Install MySQL on Ubuntu 18.04

    In this tutorial, we will show you how to install and improve the security of MySQL on Ubuntu 18.04. MySQL is one of the most popular and widely-used open-source relational database management systems. It is used for building web applications such as WordPress, Drupal, Joomla, and Magento, and is an important part of the LAMP/LEMP […]
  3. Percona Monitoring and Management (PMM) 1.17.1 Is Now Available

    Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible. In this release, we are introducing support for detection of our upcoming PMM 2.0 release in order to avoid potential version conflicts in the future, as PMM 1.x will not be compatible with PMM 2.x. Another improvement in this release is we have updated the Tooltips for Dashboard MySQL Query Response Time by providing a description of what the graphs display, along with links to related documentation resources. An example of Tooltips in action: PMM 1.17.1 release provides fixes for CVE-2018-16492 and CVE-2018-16487 vulnerabilities, related to Node.js modules. The authentication system used in PMM is not susceptible to the attacks described in these CVE reports. PMM does not use client-side data objects to control user-access. In release 1.17.1 we have included two improvements and fixed nine bugs. Improvements PMM-1339: Improve tooltips for MySQL Query Response Time dashboard PMM-3477: Add Ubuntu 18.10 support Fixed Bugs PMM-3471: Fix global status metric names in mysqld_exporter for MySQL 8.0 compatibility PMM-3400: Duplicate column in the Query Analytics dashboard Explain section PMM-3353: postgres_exporter does not work with PostgreSQL 11 PMM-3188: Duplicate data on Amazon RDS / Aurora MySQL Metrics dashboard PMM-2615: Fix wrong formatting in log which appears if pmm-qan-agent process fails to start PMM-2592: MySQL Replication Dashboard shows error with multi-source replication PMM-2327: Member State Uptime and Max Member Ping time charts on the MongoDB ReplSet dashboard return an error PMM-955: Fix format of User Time and CPU Time Graphs on MySQL User Statistics dashboard PMM-3522: CVE-2018-16492 and CVE-2018-16487 Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.
  4. Authentication and Authorization With Flask-Login

    Allowing users to login to your app is one of the most common features you'll add to a web app you build. This article will cover how to add simple authentication to your Flask app. The main package we will use to accomplish this is Flask Login. What We'll Be Building We're going to build some sign up and login pages that allows our app to allow users to login and access protected pages that non-logged in users can see. We'll grab information from the user model and display it on our protected pages when the user logs in to simulate what a profile would look like. We will cover the following in this article: Use the Flask-Login library for session management Use the built-in Flask utility for hashing passwords Add protected pages to our app for logged in users only Use Flask-SQLAlchemy to create a user model Create sign up and login forms for our users to create accounts and login Flash error messages back to users when something goes wrong Use information from the user's account to display on the profile page Setting Up The Application Our app will use the Flask app factory pattern with blueprints. We'll have one blueprint that handles everything auth related, and we'll have another blueprint for our regular routes, which include the index and the protected profile page. In a real app, of course, you can break down the functionality in any way you like, but what I've proposed will work well for this tutorial. To start, we need to create the directories and files for our project. - project ---- templates -------- base.html <!-- contains common layout and links --> -------- index.html <!-- show the home page --> -------- login.html <!-- show the login form --> -------- profile.html <!-- show the profile page --> -------- signup.html <!-- show the signup form --> ---- __init__.py <!-- setup our app --> ---- auth.py <!-- the auth routes for our app --> ---- main.py <!-- the non-auth routes for our app --> ---- models.py <!-- our user model --> You can create those files and we'll add them as we progress along. Install Packages There are three main packages we need for our project: Flask Flask-Login - to handle the user sessions after authentication Flask-SQLAlchemy - to represent the user model and interface with our database We'll only be using SQLite for the database to avoid having to install any extra dependencies for the database. Here's what you need to run after creating your virtual environment to install the packages. pip install flask flask-sqlalchemy flask-login Main App File Let's start by creating the __init__.py file for our project. This will have the function to create our app which will initialize the database and register our blueprints. At the moment this won't do much, but it will be needed for the rest of our app. All we need to do is initialize SQLAlchemy, set some configuration values, and register our blueprints here. __init__.py # __init__.py from flask import Flask from flask__sqlalchemy import SQLAlchemy # init SQLAlchemy so we can use it later in our models db = SQLAlchemy() def create_app(): app = Flask(__name) app.config['SECRET_KEY'] = '9OLWxND4o83j4K4iuopO' app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite' db.init_app(app) # blueprint for auth routes in our app from .auth import auth as auth_blueprint app.register_blueprint(auth_blueprint) # blueprint for non-auth parts of app from .main import main as main_blueprint app.register_blueprint(main_blueprint) return app Route Scaffolding Now that we have the main app file, we can start adding in our routes. For our routes, we'll use two blueprints. For our main blueprint, we'll have a home page (/) and profile page (/profile) for after we log in. If the user tries to access the profile page without being logged in, they'll be sent to our login route. For our auth blueprint, we'll have routes to retrieve both the login page (/login) and signup page (/signup). We'll also have routes for handling the POST request from both of those two routes. Finally, we'll have a logout route (/logout) to logout an active user. Let's go ahead and add them even though they won't do much. Later we will update them so we can use them. main.py # main.py from flask import Blueprint from . import db main = Blueprint('main', __name__) @main.route('/') def index(): return 'Index' @main.route('/profile') def profile(): return 'Profile' auth.py # auth.py from flask import Blueprint from . import db auth = Blueprint('auth', __name__) @auth.route('/login') def login(): return 'Login' @auth.route('/signup') def signup(): return 'Signup' @auth.route('/logout') def logout(): return 'Logout' You can now set the FLASK_APP and FLASK_DEBUG values and run the project. You should be able to view navigate to the five possible URLs and see the text returned. export FLASK_APP=project export FLASK_DEBUG=1 flask run Templates Let's go ahead and create the templates that are used in our app. This is the first step before we can implement the actual login functionality. Our app will use four templates: index.html profile.html login.html signup.html We'll also have a base template that will have code common to each of the pages. In this case, the base template will have navigation links and the general layout of the page. Let's create them now. templates/base.html <!-- templates/base.html --> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Flask Auth Example</title> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.7.2/css/bulma.min.css" /> </head> <body> <section class="hero is-primary is-fullheight"> <div class="hero-head"> <nav class="navbar"> <div class="container"> <div id="navbarMenuHeroA" class="navbar-menu"> <div class="navbar-end"> <a href="/{{ url_for('main.index') }}" class="navbar-item"> Home </a> <a href="/{{ url_for('main.profile') }}" class="navbar-item"> Profile </a> <a href="/{{ url_for('auth.login') }}" class="navbar-item"> Login </a> <a href="/{{ url_for('auth.signup') }}" class="navbar-item"> Sign Up </a> <a href="/{{ url_for('auth.logout') }}" class="navbar-item"> Logout </a> </div> </div> </div> </nav> </div> <div class="hero-body"> <div class="container has-text-centered"> {% block content %} {% endblock %} </div> </div> </section> </body> </html> templates/index.html <!-- templates/index.html --> {% extends "base.html" %} {% block content %} <h1 class="title"> Flask Login Example </h1> <h2 class="subtitle"> Easy authentication and authorization in Flask. </h2> {% endblock %} templates/login.html <!-- templates/login.html --> {% extends "base.html" %} {% block content %} <div class="column is-4 is-offset-4"> <h3 class="title">Login</h3> <div class="box"> <form method="POST" action="/login"> <div class="field"> <div class="control"> <input class="input is-large" type="email" name="email" placeholder="Your Email" autofocus=""> </div> </div> <div class="field"> <div class="control"> <input class="input is-large" type="password" name="password" placeholder="Your Password"> </div> </div> <div class="field"> <label class="checkbox"> <input type="checkbox"> Remember me </label> </div> <button class="button is-block is-info is-large is-fullwidth">Login</button> </form> </div> </div> {% endblock %} templates/signup.html <!-- templates/signup.html --> {% extends "base.html" %} {% block content %} <div class="column is-4 is-offset-4"> <h3 class="title">Sign Up</h3> <div class="box"> <form method="POST" action="/signup"> <div class="field"> <div class="control"> <input class="input is-large" type="email" name="email" placeholder="Email" autofocus=""> </div> </div> <div class="field"> <div class="control"> <input class="input is-large" type="text" name="name" placeholder="Name" autofocus=""> </div> </div> <div class="field"> <div class="control"> <input class="input is-large" type="password" name="password" placeholder="Password"> </div> </div> <button class="button is-block is-info is-large is-fullwidth">Sign Up</button> </form> </div> </div> {% endblock %} templates/profile.html <!-- templates/profile.html --> {% extends "base.html" %} {% block content %} <h1 class="title"> Welcome, Anthony! </h1> {% endblock %} Once you've added the templates, we can update the return statements in each of the routes we have to return the templates instead of the text. main.py # main.py from flask import Blueprint, render_template ... @main.route('/') def index(): return render_template('index.html') @main.route('/profile') def profile(): return render_template('profile.html') main.py # auth.py from flask import Blueprint, render_template ... @auth.route('/login') def login(): return render_template('login.html') @auth.route('/signup') def signup(): return render_template('signup.html') For example, here is what the signup page looks like if you navigate to /signup. You should be able see the pages for /, /login, and /profile as well. We'll leave /logout alone for now because it won't display a template when it's done. User Model Our user model represents what it means for our app to have a user. To keep it simple, we'll have fields for an email address, password, and name. Of course in your application, you may decide you want much more information to be stored per user. You can add things like birthday, profile picture, location, or any user preferences. Models created in Flask-SQLAlchemy are represented by classes which then translate to tables in a database. The attributes of those classes then turn into columns for those tables. Let's go ahead and create that user model. models.py # models.py from . import db class User(db.Model): id = db.Column(db.Integer, primary_key=True) # primary keys are required by SQLAlchemy email = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) name = db.Column(db.String(1000)) Database Config Like I said before, we'll be using a SQLite database. We could create a SQLite database on our own, but let's have Flask-SQLAlchemy do it for us. We already have the path of the database specified in the __init__.py file, so we just need to tell Flask-SQLAlchemy to create the database for us in the Python REPL. If you stop your app and open up a Python REPL, we can create the database using the create_all method on the db object. from project import db, create_app db.create_all(app=create_app()) # pass the create_app result so Flask-SQLAlchemy gets the configuration. You should now see a db.sqlite file in your project directory. This database will have our user table in it. Sign up Method Now that we have everything set up, we can finally get to writing the code for the authorization. For our sign up function, we're going to take the data the user types into the form and add it to our database. But before we add it, we need to make sure the user doesn't already exist in the database. If it doesn't, then we need to make sure we hash the password before placing it into the database, because we don't want our passwords stored in plaintext. Let's start by adding a second function to handle the POSTed form data. In this function, we will gather the data passed from the user first. Let's start by creating the function and adding a redirect to the bottom because we know when we add the user to the database, we will redirect to the login route. auth.py # auth.py from flask import Blueprint, render_template, redirect, url_for ... @auth.route('/signup', methods=['POST']) def signup_post(): # code to validate and add user to database goes here return redirect(url_for('auth.login')) Now, let's add the rest of the code necessary for signing up a user. To start, we'll have to use the request object to get the form data. If you're not familar with the request object, I wrote on article on it here: https://scotch.io/bar-talk/processing-incoming-request-data-in-flask auth.py # auth.py from flask import Blueprint, render_template, redirect, url_for, request from werkzeug.security import generate_password_hash, check_password_hash from .models import User from . import db ... @auth.route('/signup', methods=['POST']) def signup_post(): email = request.form.get('email') name = request.form.get('name') password = request.form.get('password') user = User.query.filter_by(email=email).first() # if this returns a user, then the email already exists in database if user: # if a user is found, we want to redirect back to signup page so user can try again return redirect(url_for('auth.signup')) # create new user with the form data. Hash the password so plaintext version isn't saved. new_user = User(email=email, name=name, password=generate_password_hash(password, method='sha256')) # add the new user to the database db.session.add(new_user) db.session.commit() return redirect(url_for('auth.login')) Test Sign Up Method Now that we have the sign up method done, we should be able to create a new user. Use the form to create a user. There are two ways you can verify if the sign up worked: you can use a database viewer to look at the row that was added to your table, or you can simply try signing up with the same email address again, and if you get an error, you know the first email was saved properly. So let's take that approach. We can add code to let the user know the email already exists and tell them to go to the login page. By calling the flash function, we will send a message to the next request, which in this case, is the redirect. The page we land on will then have access to that message in the template. First, we add the flash before we redirect back to our signup page. auth.py # auth.py from flask import Blueprint, render_template, redirect, url_for, request, flash ... @auth.route('/signup', methods=['POST']) def signup_post(): ... if user: # if a user is found, we want to redirect back to signup page so user can try again flash('Email address already exists') return redirect(url_for('auth.signup')) To get the flashed message in the template, we can add this code above the form. This will display the message directly above the form. templates/signup.html <!-- templates/signup.html --> ... {% with messages = get_flashed_messages() %} {% if messages %} <div class="notification is-danger"> {{ messages[0] }}. Go to <a href="/{{ url_for('auth.login') }}">login page</a>. </div> {% endif %} {% endwith %} <form method="POST" action="/signup"> ... Login Method The login method is similiar to the signup function in that we will take the user information and do something with it. In this case, we will compare the email address entered to see if it's in the database. If so, we will test the password the user provided by hashing the password the user passes in and comparing it to the hashed password in the database. We know the user has entered the correct password when both hashed passwords match. Once the user has passed the password check, we know that they have the correct credentials and we can go ahead and log them in using Flask-Login. By calling login_user, Flask-Login will create a session for that user that will persist as the user stays logged in, which will allow the user to view protected pages. We can start with a new route for handling the POSTed data. We'll redirect to the profile page when the user successfully logs in. auth.py # auth.py ... @auth.route('/login', methods=['POST']) def login_post(): # login code goes here return redirect(url_for('main.profile')) Now, we need to verify if the user has the correct credentials. auth.py # auth.py ... @auth.route('/login', methods=['POST']) def login_post(): email = request.form.get('email') password = request.form.get('password') remember = True if request.form.get('remember') else False user = User.query.filter_by(email=email).first() # check if user actually exists # take the user supplied password, hash it, and compare it to the hashed password in database if not user or not check_password_hash(user.password, password): flash('Please check your login details and try again.') return redirect(url_for('auth.login')) # if user doesn't exist or password is wrong, reload the page # if the above check passes, then we know the user has the right credentials return redirect(url_for('main.profile')) Let's add in the block in the template so the user can see the flashed message. Like the signup form, let's add the potential error message directly above the form. templates/login.html <!-- templates/login.html --> ... {% with messages = get_flashed_messages() %} {% if messages %} <div class="notification is-danger"> {{ messages[0] }} </div> {% endif %} {% endwith %} <form method="POST" action="/login"> So we have have the ability to say a user has been logged in succesfully, but there is nothing to actually log the user in anywhere. This is where we bring in Flask-Login. But first, we need a few things for Flask-Login to work. We start by adding something called the UserMixin to our User model. The UserMixin will add Flask-Login attributes to our model so Flask-Login will be able to work with it. models.py # models.py from flask_login import UserMixin from . import db class User(UserMixin, db.Model): id = db.Column(db.Integer, primary_key=True) # primary keys are required by SQLAlchemy email = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) name = db.Column(db.String(1000)) Then, we need to specify our user loader. A user loader tells Flask-Login how to find a specific user from the ID that is stored in their session cookie. We can add this in our create_app function along with basic init code for Flask-Login. __init__.py # __init__.py ... from flask_login import LoginManager def create_app(): ... db.init_app(app) login_manager = LoginManager() login_manager.login_view = 'auth.login' login_manager.init_app(app) from .models import User @login_manager.user_loader def load_user(user_id): # since the user_id is just the primary key of our user table, use it in the query for the user return User.query.get(int(user_id)) Finally, we can add the login_user function just before we redirect to the profile page to create the session. auth.py # auth.py from flask_login import login_user from .models import User ... @auth.route('/login', methods=['POST']) def login_post(): # if the above check passes, then we know the user has the right credentials login_user(user, remember=remember) return redirect(url_for('main.profile')) With Flask-Login setup, we can finally use the /login route. When everything is successful, we will see the profile page. Protected Pages If your name isn't also Anthony, then you'll see that your name is wrong. What we want is the profile to display the name in the database. So first, we need to protect the page and then access the user's data to get the name. To protect a page when using Flask-Login is very simple: we add the @login_requried decorator between the route and the function. This will prevent a user who isn't logged in from seeing the route. If the user isn't logged in, the user will get redirected to the login page, per the Flask-Login configuration. With routes that are decorated with the login_required decorator, we then have the ability to use the current_user object inside of the function. This current_user represents the user from the database, and we can access all of the attributes of that user with dot notation. For example, current_user.email, current_user.password, and current_user.name and current_user.id will return the actual values stored in the database for the logged in user. Let's use the name of the current user and send it to the template. We then will use that name and display its value. main.py # main.py from flask import Blueprint, render_template from flask_login import login_required, current_user ... @main.route('/profile') @login_required def profile(): return render_template('profile.html', name=current_user.name) templates/profile.html <!-- templates/profile.html --> ... <h1 class="title"> Welcome, {{ name }}! </h1> Once we go to our profile page, we then see that the user's name appears. The final thing we can do is update our logout view. We can call the logout_user function in a route for logging out. We have the login_required decorator because it doesn't make sense to logout a user who isn't logged in to begin with. auth.py # auth.py from flask_login import login_user, logout_user, login_required ... @auth.route('/logout') @login_required def logout(): logout_user() return redirect(url_for('main.index')) After we logout and try viewing the profile page again, we see a error message appear. This is because Flask-Login flashes a message for us when the user isn't allowed to access a page. One last thing we can do is put if statements in the templates to display only the links relevant to the user. So before the user logins in, they will have the option to login or signup. After they have logged in, they can go to their profile or logout. templates/base.html <!-- templates/base.html --> ... <div class="navbar-end"> <a href="/{{ url_for('main.index') }}" class="navbar-item"> Home </a> {% if current_user.is_authenticated %} <a href="/{{ url_for('main.profile') }}" class="navbar-item"> Profile </a> {% endif %} {% if not current_user.is_authenticated %} <a href="/{{ url_for('auth.login') }}" class="navbar-item"> Login </a> <a href="/{{ url_for('auth.signup') }}" class="navbar-item"> Sign Up </a> {% endif %} {% if current_user.is_authenticated %} <a href="/{{ url_for('auth.logout') }}" class="navbar-item"> Logout </a> {% endif %} </div> Conclusion We've done it! We have used Flask-Login and Flask-SQLAlchemy to build a very basic login system for our app. We covered how to authenticate a user by first creating a user model and storing the user information to later. Then we had to verify the user's password was correct by hashing the password from the form and comparing it to the one stored in the database. Finally, we added authorization to our app by using the @login_required decotor on a profile page so only logged in users can see that page. What we created in this tutorial will be sufficient for smaller apps, but if you wish to have more functionality from the beginning, you may want to consider using either the Flask-User or Flask-Security libraries, which are both build on top of the Flask-Login library.
  5. ProxySQL Native Support for Percona XtraDB Cluster (PXC)

    ProxySQL in its versions up to 1.x did not natively support Percona XtraDB Cluster (PXC). Instead, it relied on the flexibility offered by the scheduler. This approach allowed users to implement their own preferred way to manage the ProxySQL behaviour in relation to the Galera events. From version 2.0 we can use native ProxySQL support for PXC.. The mechanism to activate native support is very similar to the one already in place for group replication. In brief it is based on the table [runtime_]mysql_galera_hostgroups and the information needed is mostly the same: writer_hostgroup: the hostgroup ID that refers to the WRITER backup_writer_hostgroup: the hostgoup ID referring to the Hostgorup that will contain the candidate servers reader_hostgroup: The reader Hostgroup ID, containing the list of servers that need to be taken in consideration offline_hostgroup: The Hostgroup ID that will eventually contain the writer that will be put OFFLINE active: True[1]/False[0] if this configuration needs to be used or not max_writers: This will contain the MAX number of writers you want to have at the same time. In a sane setup this should be always 1, but if you want to have multiple writers, you can define it up to the number of nodes. writer_is_also_reader: If true [1] the Writer will NOT be removed from the reader HG max_transactions_behind: The number of wsrep_local_recv_queue after which the node will be set OFFLINE. This must be carefully set, observing the node behaviour. comment: I suggest to put some meaningful notes to identify what is what. Given the above let us see what we need to do in order to have a working galera native solution. I will have three Servers: 192.168.1.205 (Node1) 192.168.1.21 (Node2) 192.168.1.231 (node3) As set of Hostgroup, I will have: Writer HG-> 100 Reader HG-> 101 BackupW HG-> 102 offHG HG-> 9101 To set it up Servers first: INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.205',101,3306,1000); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.21',101,3306,1000); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.231',101,3306,1000); Then the galera settings: insert into mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (100,102,101,9101,0,1,1,16); As usual if we want to have R/W split we need to define the rules for it: insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,destination_hostgroup,active,retries,match_digest,apply) values(1040,6033,'windmills','app_test',100,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,destination_hostgroup,active,retries,match_digest,apply) values(1041,6033,'windmills','app_test',101,1,3,'^SELECT.*@@',1); save mysql query rules to disk; load mysql query rules to run; Then another important variable… the server version, please do yourself a good service ad NEVER use the default. update global_variables set variable_value='5.7.0' where variable_name='mysql-server_version'; LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK; Finally activate the whole thing: save mysql servers to disk; load mysql servers to runtime; One thing to note before we go ahead. In the list of servers I had: Filled only the READER HG Used the same weight This because of the election mechanism ProxySQL will use to identify the writer, and the (many) problems that may be attached to it. For now let us go ahead and see what happens when I load this information to runtime. Before running the above commands: +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ After: +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1000 | 100 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 501 | | 1000 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 501 | | 1000 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 546 | | 1000 | 101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 467 | | 1000 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 546 | | 1000 | 102 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 467 | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ mysql> select * from runtime_mysql_galera_hostgroups \G *************************** 1. row *************************** writer_hostgroup: 100 backup_writer_hostgroup: 102 reader_hostgroup: 101 offline_hostgroup: 9101 active: 0 <----------- note this max_writers: 1 writer_is_also_reader: 1 max_transactions_behind: 16 comment: NULL 1 row in set (0.01 sec) As we can see, ProxySQL had taken the nodes from my READER group and distribute them adding node 1 in the writer and node 2 as backup_writer. But – there is a but – wasn’t my rule set with Active=0? Indeed it was, and I assume this is a bug (#Issue  1902). The other thing we should note is that ProxySQL had elected as writer node 3 (192.168.1.231). As I said before what should we do IF we want to have a specific node as preferred writer? We need to modify its weight. So say we want to have node 1 (192.168.1.205) as writer we will need something like this: INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.205',101,3306,10000); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.21',101,3306,100); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.231',101,3306,100); Doing that will give us : +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 10000 | 100 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2209 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 546 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 508 | | 10000 | 101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2209 | | 100 | 102 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 546 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 508 | +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ If you noticed, given we had set the WEIGHT in node 1 higher, this node will become also the most utilized for reads. We probably do not want that, so let us modify the reader weight. update mysql_servers set weight=10 where hostgroup_id=101 and hostname='192.168.1.205'; At this point if we trigger the failover, with set global wsrep_reject_queries=all; on node 1. ProxySQL will take action and will elect another node as writer: +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 100 | 100 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 562 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 562 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 588 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 588 | | 10000 | 9101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 468 | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ Node 3 (192.168.1.231) is the new writer and node 1 is in the special group for OFFLINE. Let see now what will happen IF we put back node 1. +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 10000 | 100 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 449 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 532 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 569 | | 10000 | 101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 449 | | 100 | 102 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 532 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 569 | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ Ooops the READER has come back with the HIGHEST value and as such it will be the most used node, once more. To fix it, we need to re-run the update as before. But there is a way to avoid this? In short the answer is NO! This, in my opinion, is BAD and is worth a feature request, because this can really put a node on the knees. Now this is not the only problem. There is another point that is probably worth discussion, which is the fact ProxySQL is currently doing FAILOVER/FAILBACK. Failover, is obviously something we want to have, but failback is another discussion. The point is, once the failover is complete and the cluster has redistributed the incoming requests, doing a failback is an impacting operation that can be a disruptive one too. If all nodes are treated as equal, there is no real way to prevent it, while if YOU set a node to be the main writer, something can be done, let us see what and how. Say we have: INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.205',101,3306,1000); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.21',101,3306,100); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.231',101,3306,100); +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1000 | 100 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 470 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 558 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 613 | | 10 | 101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 470 | | 100 | 102 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 558 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 613 | +--------+-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ Let us put the node downset global wsrep_reject_queries=all; And check: +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 100 | 100 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 519 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 519 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 506 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 506 | | 1000 | 9101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 527 | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ We can now manipulate the weight in the special OFFLINE group and see what happen: update mysql_servers set weight=10 where hostgroup_id=9101 and hostname='192.168.1.205' Then I put the node up again:set global wsrep_reject_queries=none; +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 100 | 100 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 537 | | 100 | 101 | 192.168.1.231 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 537 | | 100 | 101 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 573 | | 10 | 101 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 458 | | 100 | 102 | 192.168.1.21 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 573 | | 10 | 102 | 192.168.1.205 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 458 | +--------+-----------+---------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ That’s it, the node is back but with no service interruption. At this point we can decide if make this node reader like the others, or wait and plan a proper time of the day when we can put it back as writer, while, in the meanwhile it has a bit of load to warm its bufferpool. The other point – and important information – is what is ProxySQL is currently checking on Galera? From reading the code Proxy will trap the following: read_only wsrep_local_recv_queue wsrep_desync wsrep_reject_queries wsrep_sst_donor_rejects_queries primary_partition Plus the standard sanity checks on the node. Finally to monitor the whole situation we can use this: mysql> select * from mysql_server_galera_log order by time_start_us desc limit 10; +---------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ | hostname | port | time_start_us | success_time_us | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | error | +---------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ | 192.168.1.231 | 3306 | 1549982591661779 | 2884 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.21 | 3306 | 1549982591659644 | 2778 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.205 | 3306 | 1549982591658728 | 2794 | YES | NO | 0 | 4 | NO | YES | NO | NULL | | 192.168.1.231 | 3306 | 1549982586669233 | 2827 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.21 | 3306 | 1549982586663458 | 5100 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.205 | 3306 | 1549982586658973 | 4132 | YES | NO | 0 | 4 | NO | YES | NO | NULL | | 192.168.1.231 | 3306 | 1549982581665317 | 3084 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.21 | 3306 | 1549982581661261 | 3129 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.205 | 3306 | 1549982581658242 | 2786 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 192.168.1.231 | 3306 | 1549982576661349 | 2982 | YES | NO | 0 | 4 | NO | NO | NO | NULL | +---------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ As you can see above the log table keeps track of what is changed. In this case, it reports that node 1 has wsrep_reject_queries activated, and it will continue to log this until we set wsrep_reject_queries=none. Conclusions ProxySQL galera native integration is a useful feature to manage any Galera implementation, no matter whether it’s Percona PXC, MariaDB cluster or MySQL/Galera. The generic approach is obviously a good thing, still it may miss some specific extension like we have in PXC with the performance_schema pxc_cluster_view table. I’ve already objected about the failover/failback, and I am here again to remind you: whenever you do a controlled failover REMEMBER to change the weight to prevent an immediate failback. This is obviously not possible in the case of a real failover, and, for instance, a simple temporary eviction will cause two downtimes instead only one. Some environments are fine with that others not so. Personally I think there should be a FLAG in the configuration, such that we can decide if failback should be executed or not.  

Upcoming Events

Latest News

Visitors

We have 96 guests and no members online