Category: MySQL Administration

Dear community, maybe you have already tested, there are new added features in MySQL 5.7. See previous post -> About 5.7.7-rc BUGs

“General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options. Today’s topic is altering table to use this “General Tablespace”.

If you notice, now script trying to connect to MySQL using a temporary password which is generated for root@localhost and logged into hidden .mysql_secret file.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: Y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Using existing root password.
Estimated strength of the password: 100

From now we have an option to activate our password policy on the fly after fresh installation.
If you try to give a password such 12345 it will fail at password policy check:

Change the root password? (Press y|Y for Yes, any other key for No) : Y
New password:
Re-enter new password:
Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
... Failed! Error: Your password does not satisfy the current policy requirements

The fail reason is strength of password is equal to 25 , where it should be 100.
After giving a proper password:

New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

The other parts of output should be familiar:

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!

Recently want to install Percona XtraDB Cluster + HAProxy + KeepAlived on CentOS 7, but could not find any all-in-one guide. So decided to write down all steps necessary for getting started and running HA solution using Open Source projects. Nowadays high availability is one of the main concerns faced by big and small companies.Minimum wasting of time (downtime per year), sustainably working of infrastructure and etc. is the main mission for all companies that they are trying to achieve.There are different approaches to this mission. One of them as large companies did, to buy expensive software and support from vendors. But small companies could not go through these steps. The true power of Open Source comes up at these moments.You can build your own High Availability solution using free but yet powerful Open Source projects. In this article we will show you how to achieve Database Clustering and Load Balancing. We will use:

Percona XtraDB Cluster – Percona XtraDB Cluster is High Availability and Scalability solution for MySQL Users. What is PXC?

HAproxy – HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications See for further information HAproxy official site

KeepAlived – Keepalived is a routing software written in C. The main goal of this project is to provide simple and robust facilities for loadbalancing and high-availability to Linux system and Linux based infrastructures See for further information KeepAlived official site

General Architecture

Before diving in the step-by-step guide let’s to see what we want to accomplish at the end of tutorial:

As diagram shows there must be at least 3 CentOS 6.5/7 instance for PXC Galera Cluster setup. Total 3 CentOS 7 instance with all static local IPs and only 1 public IP for KeepAlived. So another advantage of this architecture that it will reduce public ip usage to 1.

{: .note } >

For this tutorial we will not use public IP as it is an test environment, all CentOSs will use local ips.

Prerequisites

We need 3 instances of CentOS 7 with minimal installation. Also you should give a static ips to all servers. Here is sample IPs that you may give to your CentOS instances for testing this tutorial:

Node1 (1 GB RAM, 1 CPU VM) – 192.168.1.99 – (HAproxy1)

Node2 (1 GB RAM, 1 CPU VM) – 192.168.1.88 – (HAproxy2)

Node3 (1 GB RAM, 1 CPU VM) – 192.168.1.77 – (HAproxy3)

Also keep in mind that we need another IP for KeepAlived that will act as Virtual IP for HAproxy intances.

Installing and Configuring PXC

Installing and Running PXC:

The easy way to install is through official yum repo. You should activate Percona repo on all 3 Nodes:

As you see the cluster size is 3 and it means all 3 Nodes connected and working. Also you can see from output that Cluster is ready for accepting connection. Let’s create database from Node3 and test its creation from other Nodes:

Disabling SElinux permanently or running in permissive mode for all time is dangerous related to security. As we have set SElinux into permissive mode, it will not prevent any Galera actions, instead it has already logged all related information into audit.log file. Using this file we should create rules and reenable SElinux. On all 3 nodes:

Lets explore config file a bit more: The default port number 9000 is for WEB UI for HAproxy monitoring. Another thing to remember that when using PXC Galera Cluster with SST options mysqldump and rsync (default) it will lock each other nodes from getting updates while DDL, DML statements executing. To avoid such situation and not to stuck with Deadlocks, we decide to separate Write operations. In other words, Write operations (e.g insert, update, delete etc.) will go only to Node1. So on the application side, you should send write operations to port 3310 as we put in haproxy.cfg file, and for read operations to port number 3311. There is an available non-locking SST option XtraBackup (the famous hot online backup tool for MySQL), but it is the subject of another topic.

As we have mentioned port number 9000 for WEB Ui related to HAproxy statistics. So lets connect to IP of on of the HAproxy servers. The connection URL for me is: http://192.168.1.88:9000/haproxy_stats. In Very first connection there will be an pop-screen for login and password. These information is stored in /etc/haproxy/haproxy.cfg file stats auth haproxy:haproxy. So the Login is: haproxy and Password is: haproxy too.

You will see the statistics page with correctly working Cluster Nodes:

Now lets check MySQL connectivity over HAproxy. For this purpose create sample database user on PXC Cluster nodes. Our cluster is ready and that’s why it is sufficient to run this command one of the nodes and other will pick-up automatically

create user 'all'@'%' identified by '12345';
grant all on *.* to 'all'@'%';

Installing and Configuring KeepAlived

The Final step in our environment is installing and configuring KeepAlived , acting as Virtual IP and switch-over between HAproxy instances. The idea is, when one of HAproxy servers fails, another one must do same work without interrupting all architecture. The new IP address for us is 192.168.1.199 – which is Virtual IP for our HAproxy servers. We will connect to MySQL(PXC) through this IP and KeepAlived will decide to which HAproxy instance it must send this connection. Lets test:

Conclusion

In this tutorial you learn how to install and configure PXC on CentOS 7 and also how to load balance this cluster using wellknown HAproxy. As bonus you learn to install/configure KeepAlived and access database over Virtual IP which is a next level of redundancy in High Availability approach. If you have any suggesstions or errors, please do not hesitate to contact. Thank you for reading.

What is Optimizer Trace? After reading topic about Optimizer Tracer by [Morgan Tocker][1] decided to test it. From [Optimizer Trace and EXPLAIN FORMAT=JSON in 5.7][2]: Optimizer trace is a new diagnostic tool introduced in MySQL 5.6 to show how the optimizer is working internally. It is similar to EXPLAIN, with a few notable differences: It doesn’t just show the intended execution plan, it shows the alternative choices. You enable the optimizer trace, then you run the actual query. It is far more verbose in its output. For understanding goal of article please read previous one about related verified optimizer BUG: [Playing with count() optimizer work][3] ** We have 2 queries: **select count() from sales; select count(*) from sales where sales_id > 0; Firstly let’s get explain plan for query with JSON format and as regular:

From Explain plan it is obvious that, first query will use “Index Scan”, second will use “Range Scan + Index”. First query will use, sales_cust_idx in customer_id column, second query will use primary key in sales_id column. From first view, there now difference between queries, but optimizer estimates half of rows when attaching sales_id > 0 condition. See related BUG: [#68814][4] Now let’s examine problem with Optimizer Tracer. So before running query you should enable optimizer trace:

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

It is much more complicated with second query and due to lacking documentation for all output, i am looking for explanations from experts.
First thing is, it says in “potential_range_indices” that “index”: “sales_cust_index” is not usable:

But in script it is defined as: /var/lib/mysql/mysqld.log Even if you comment out default settings in my.cnf file, MySQL by default will create error log file as follows: datadir + host_name.err = /var/lib/mysql/host_name.err Again there will be no such error log file defined in log rotate script. Second problem is usage of mysqladmin. Script is calling mysqladmin without any password. We assume that there is no such MySQL instance without user, at least in production environment:

In previous topic, we have covered “Transportable Tablespace” concept by copying and importing table’s tablespace to remote server. See -> Copying Tablespaces to Remote Server The idea is copying tablespace file to remote server, in remote server you must create identical database names and table names manually, then you should discard new table’s tablespace file and import new copied one. To achieve this you must have running MySQL version >= 5.6, innodb_file_per_table=1 and you must know “CREATE statement” of table. Let’s to change our test condition. Assume that, you have MySQL server and you have taken physical backup of your server (you can use Percona XtraBackup, cold backup for eg.). But one of the wonderful day somebody deleted all table data (say -> delete from table_name). In fact your table at this moment exists(.frm and .ibd), you can easily discard table’s tablespace and import tablespace from backup folder. But if table is dropped and you don’t know the create of table. Or even database is dropped. Our path will differ from previous one: *1. Create dropped database manually. 2. Create dropped table by extracting table’s create statement from .frm file which is in backed up directory. To extract table create statement from .frm file you can use **mysqlfrm tool from MySQL Utilities. 3. Discard table’s tablespace (ALTER TABLE t DISCARD TABLESPACE;) 4. Copy .ibd file from backup directory to MySQL’s datadir database directory 5. Import copied back tablespace file.(ALTER TABLE t IMPORT TABLESPACE;)*** You can also read about this concept from documentation -> tablespace-copying I have automatized this process adding table create statement extracting functionality to MySQL-AutoXtraBackup project as –partial recovery option. Here is a demo usage video:

If you tested and found issues, please report it to improve this opensource project.