Showing entries 1 to 10 of 159
10 Older Entries »
Displaying posts with tag: Technical Track (reset)
Restore a table / database from full backup – Yet another way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on …

[Read more]
Graceful master switchover with ProxySQL and Orchestrator

Introduction

One of the things I like about Continuent Tungsten is how Tungsten Connector can hold traffic while a (graceful) master switch is taking place. This means the application may experience a brief spike in latency, but has not returned any errors from the database layer.

René also described a similar process using ProxySQL and mysqlrpladmin here. In this post we will try to achieve the same feat, a graceful master switchover with ProxySQL and Orchestrator.

ProxySQL considerations

ProxySQL needs to isolate our application from the changes going on at the database layer.
By design, if a query needs to be sent to a hostgroup that has no servers in ONLINE state, ProxySQL waits until either a server becomes available or a timeout expires (mysql-connect_timeout_server_max if I am not …

[Read more]
ProxySQL behind a load balancer in Google Cloud

Introduction

In this article we will explore one approach for deploying ProxySQL behind a load balancer in Google Cloud.

While considering the deployment of ProxySQL, one has basically the following options:

  1. Install ProxySQL on existing application server(s)
  2. Provision dedicated ProxySQL server(s) between your application servers and the database layer.

Each approach has its pros and cons, but if there’s a significant number of application servers (more than a dozen or so) having a dedicated ProxySQL “layer” can be a more attractive option, specially if there is no service discovery mechanism in place (e.g. Consul).

Let’s consider a simple scenario, with a master and a small number of slaves in a single geographic region. Assuming that you are following the best practice, your database servers should be split into different availability zones. So for ProxySQL, it also …

[Read more]
Datascape Podcast Episode 24 – MariaDB and the MySQL Ecosystem

Today on the show, I am joined by my colleagues, John Schulz and Valerie Parham-Thompson. Both John and Valerie just returned from the MariaDB Conference in New York City. As MariaDB is a native, relative and viable option to MySQL, I thought that it would be a good time to delve into the platform in detail and share what we have discovered with you here today. In this episode, we find out more about the MariaDB Conference – why it was founded, what the attendance was like and who the Conference is beneficial for. We then take a closer look at MariaDB and investigate the key differences between MariaDB and MySQL – highlighting MariaDB’s newest and most user-friendly features. We also learn more about other software platforms, such as Percona and Oracle, and how they compare to MariaDB. Our insightful guests continue to offer some helpful tips and resources for anyone getting started in MariaDB. So, if you are currently using MySQL or MariaDB, …

[Read more]
The case against auto increment in MySQL

Introduction

In my travels to visit many customers over the last few years, I often see my customers creating many or all of their MySQL InnoDB tables using auto-increment primary keys. Many Object Relational Mappers do this by default on behalf of the user. Once the tables are all created with auto increment primary keys, then the database designer/developer goes about assigning alternate keys that they will actually use to access the data. Most of the time the auto-increment key is simply there to ensure that there is a unique key on the table and it’s often not used as an access path. This is a common design pattern, but is it the best way to create tables using MySQL? I am witting this blog to present the case that it’s a pretty bad idea most of the time.

Why it’s a bad Idea

There are four reasons why, which I will explain in some depth later in the post but for now, they are:

  • Auto increment keys …
[Read more]
Join Pythian at Percona Live 2018

Percona Live 2018 is coming up soon, and Pythian is excited to be participating again this year. The Percona Live Open Source Database Conference is the premier open source database event for individuals and businesses that develop and use open source database software. This year the conference will take place April 23 – April 25 in Santa Clara, California. Once again Pythian will be hosting the highly anticipated Pythian Community Dinner on Tuesday, April 24th at Pedro’s Restaurant and Cantina.

We’ve also got a fantastic lineup up of speakers. You’ll want to make sure you register for the following sessions:

Monday, April 23, 2018

9:30 am – 12: 30 pm PST

  • Derek Downey will present “Hands on ProxySQL”
  • Matthias Crauwels & Pep Pla will …
[Read more]
MariaDB version upgrade to 10.1.31 breaks Galera cluster

Recently we faced an issue where the config management software had automatically upgraded the mariadb-server-10.1 package to the latest 10.1.31 version. This upgrade broke the galera cluster setup for this installation.

I’ve started to recreate this issue in my local lab setup and I managed to reproduce this problem.

I have created a 3 node galera setup: galera1 (192.168.55.100), galera2 (192.168.55.101) and galera3 (192.168.55.102). All 3 servers run MariaDB-10.1.30. Galera replication is working fine.

This is the basic galera config:

# cat /etc/mysql/conf.d/cluster.cnf
#########################################################
# Galera config
#########################################################

[mysqld]
wsrep_on                                  = ON
wsrep_provider                            = /usr/lib/libgalera_smm.so
wsrep_provider_options                    =
wsrep_cluster_name                        = …
[Read more]
MySQL 5.7 Multi-threads replication operation tips

With support of multi-threads replication starting from MySQL 5.7, the operations on slave are slightly different from single-thread replication. Here is a list of some operation tips for the convenience of use as below:

1. Skip a statement for a specific channel.

Sometimes, we might find out that one of the channels stop replication due to some error, and we may want to skip the statement for that channel so that we can restart a slave for it. We need to be very careful not to skip the statement from the other channel, since the command SET GLOBAL sql_slave_skip_counter = N is for global. How can we make sure the global sql_slave_skip_counter is applied to a specific channel and not to the other channel? Here are the steps:

1.1: Stop all slaves by: stop slave;

stop slave;

1.2: Set up the count of statement to skip by: SET GLOBAL sql_slave_skip_counter = N;

SET GLOBAL …
[Read more]
Increasing functional testing velocity with pt-query-digest

Whenever we do upgrades for our clients from one major version of MySQL to another we strongly recommend to test in two forms.

First, it would be a performance test between the old version and the new version to make sure there aren’t going to be any unexpected issues with the query processing rates. Secondly, do a functional test to ensure all queries that are running on the old version will not have syntactic errors or problems with reserved words in the new version that we’re upgrading to.

If a client doesn’t have an appropriate testing platform to perform these types of tests, we will leverage available tools to test to the best of our ability. More often than not this includes using pt-upgrade after capturing slow logs with …

[Read more]
How to Enable Binary Logging on an Amazon RDS Read Replica

One of the more common struggles I’ve had to assist with in regard to Amazon RDS is enabling binary logging on read replicas, or forming multi-tier replication in instances using version 5.6 or later after seeing that multi-tier replication is not supported in version 5.5 (for a reason that will become clear by the end of this post.)

First off, let’s have a look at the topology that I have in place in my AWS account. As you’ll see below I have a master, blog1, and a read replica that I created via the AWS console called blog2. You’ll also notice that, despite being supported, if I select instance actions while having blog2 highlighted the option to create a read replica is grayed out.

Further, if we use the MySQL CLI to connect to blog2 and check the global variables for log_bin and binlog_format, you’ll see that binary logging is off and binlog_format is set to statement. This is strange considering that the parameter …

[Read more]
Showing entries 1 to 10 of 159
10 Older Entries »