DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

How to Setup MySQL Master Master Replication

Posted on April 4, 2018 Written by Andy Hayes 1 Comment

MySQL master master replication, also known as “mysql chained replication”, “multi master replication or “mysql daisy chaining replication” is an extension of mysql replication allowing the creation of multiple master servers that can then be masters of multiple slaves. In this post, I demonstrate how to setup mysql master master replication.

In a multi master mysql configuration, bar the first master server, each additional master acts as both a master and slave. Therefore, it is possible to create new masters from each additional slave added.

Take a look at this diagram which helps to illustrate one possible configuration.

mysql master master replication

In this tutorial we’ll cover a basic example of three servers; two masters and one slave but first let’s understand why we might want to set up chained replication with mysql.

This tutorial assumes that it is a new configuration where everything is being set up in advance of the application hitting the database servers.

Use cases for MySQL Master Master Replication

  • Migrations
  • Improving replication performance

Using MySQL Master Master Replication to Facilitate Database Migrations

I was recently involved in a large database migration project. We had to move several hundred gigabytes of data onto new production servers. The databases resided on two sets of database servers. All of them were to be consolidated onto one set of database servers. Each migration was to happen on different days. After each migration, the application would be brought back online. Application downtime needed to be as minimal as possible.

Common approaches to backup and restore databases involve using mysqldump or Percona Xtrabackup to name two. Backing up and restoring large databases takes time, especially with mysqldump so I decided to get the databases syncing between the current and new production environments in each case.

Improving replication performance with Multi Master MySQL Replication

One of the reasons why MySQL replication is employed by system, application and data architects is to scale databases horizontally. Having an additional slave or slaves helps to scale read operations.

As each slave connects, it creates additional load on the master. Each slave must receive a full copy of the binary log. In environments where there are many slaves, this can increase the network load on the master where it starts to become a bottleneck. In addition, the master may also be serving requests as part of the solution as well as processing the writes.

Adding an additional master as a slave helps to take the load of the primary master. This model can be scaled so that multiple secondary masters can be created, all as slaves to the primary master depending on requirements.

So let’s look at how to set up a basic mysql master, master, slave configuration

The changes should be applied to the my.cnf (my.ini in Windows) and for them to be persisted, the mysql service should be restarted.

Overview:

Master Server (primary)

  • Enable the binary log
  • Create user to allow secondary master server to connect

Master Server (secondary)

  • Enable the binary log
  • Enable the relay log
  • Enable log slave updates
  • Create user to allow slave to connect
  • Point at primary master log position

Slave Server (slave to secondary master server)

  • Enable the relay log
  • Point at secondary master log position

Detailed setup

1/ Configuration changes

The following screenshots show the changes I have made to the my.cnf on my test virtual machines.

Master Server config (primary)

mysql master master replication primary master config

Master Server config (secondary)

mysql master master replication secondary master config

Slave Server config (slave to secondary master server)

mysql master master replication slave config

So as with standard replication, the binary log and relay logs are enabled using the log-bin and relay-log settings. The additional setting used in this configuration is the log-slave-updates. This tells the secondary master to log the changes from the primary master to its own binary log which will allow those changes to be applied to the slave.

2/ Create user on the primary master to allow the slaves to connect.


GRANT REPLICATION SLAVE ON *.* TO 'user'@'host' IDENTIFIED BY 'SecurePassword';


3/ Find the master binary log file and current position and apply them to the secondary master using CHANGE MASTER TO

On the primary master


SHOW MASTER STATUS\G;


*************************** 1. row ***************************
File: mysql-bin.000001
Position: 897
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:

On the secondary master execute this changing to your host, user, log file etc accordingly:

CHANGE MASTER TO
MASTER_HOST='primarymaster.home',
MASTER_USER='replication',
MASTER_PASSWORD='SecurePassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=897;

To finish, execute this on the secondary master:


START SLAVE;


Check the status of replication on the secondary master using:


SHOW SLAVE STATUS\G;


Check that it’s working ok, look for these lines in the output as guides that it is working ok:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error:
Seconds_Behind_Master: 0

4/ Repeat steps 2 and 3 between the secondary master and the slave

Perform the same steps changing user, host, log file and log positions accordingly

5/ Test

Apply an update to the primary master and check that it replicates across to the secondary master and the slave, for example create a new database.

Summary

Master master replication in MySQL is a great way to improve replication performance or to facilitate synchronisation of database servers as part of database migration activities.

For more information on this topic, you can view this link

If you want more information on how to set up master/slave replication you can view my post here

I hope you found this post useful 🙂

Filed Under: MySQL Administration Tagged With: mysql, replication

How To Use SQL to Convert a STRING to an INT

Posted on November 25, 2017 Written by Andy Hayes Leave a Comment

Using SQL to convert a string to an int is used in a variety of situations. You may have text data that you cannot alter at the source and you need to get some accurate answers from it. You may also have text data that you want to insert to an integer column. There could be other reasons too.

There is an easy solution to this and I will show you examples in both SQL Server and MySQL.

How to Convert a String to Number Using T-SQL in SQL Server

Let us create some data first to demonstrate this:

If I create some data like the following and then try to order it from lowest number to highest, the results are not output as I need them to.

Of course the database doesn’t know this and outputs them correctly as it is text data.


CREATE TABLE t1
(txt VARCHAR(10))

INSERT t1
SELECT '2';
INSERT t1
SELECT '3';
INSERT t1
SELECT '11';
INSERT t1
SELECT '1';

SELECT *
FROM t1
ORDER BY txt;


 

sql convert string to int

I need to use some additional sql to convert the string values to integers. So I will use the CONVERT() function to do this for me.


SELECT *
FROM t1
ORDER BY CONVERT(INT, txt);


Now the results are output correctly:

sql convert string to int with convert function

You can see there is a slight change in the execution plans here with the conversion of the string data to a number happening in the second plan:

sql convert string to int execution plan

How to Convert a String to Number Using in MySQL

This is also possible with MySQL, the code above containing CONVERT() no longer works, instead it can be written as follows:

SELECT *
FROM t1
ORDER BY CONVERT(txt, SIGNED INTEGER);

Ordering the above query without the CONVERT does not alter the execution plan.

I hope you found this post useful 🙂

Filed Under: SQL Tips and Tricks Tagged With: mysql, sql server

How to set up MySQL Replication Tutorial

Posted on November 11, 2017 Written by Andy Hayes 1 Comment

One may setup MySQL replication typically either to scale out, facilitate reporting or to provide backups of MySQL databases. I wrote a post about this before.

The whole process relies on binary logs which are output to a location on the master server and read in by the slave.

This post is a MySQL replication tutorial. I am going to setup MySQL replication involving two MySQL servers, a master and a slave. This post assumes that the servers are new and that no databases have been created on them yet.

I am not going to list every possible setting or configuration. Just necessary settings to get you up and running.

The mysql config file will require changes on both the master and slave. Ensure that there are no settings in the mysql configuration file that would cause any networking issues. Turn off –skip-networking and ensure that –bind-address is correctly set.

1/ Basic settings for configuring the master server for MySQL replication

  • server-id – each server in your configuration needs its own unique id. Start at 1 up to 32^
  • log-bin – this is the binary log file prefix, so make this something like “mysql-bin”, “bin-log” etc. Files will be output according to the path which you specify. The user that mysql is running as needs permission to write to the path specified.
  • expire-logs-days – the number of days to retain your binary logs for. Set this to ensure that logs are not removed prematurely resulting in slave data loss if it were ever to fall behind.
  • binlog-format – read the documentation carefully on this one. It’s a really important config option which if set incorrectly can mean that your slave contains different data to what was written to the master. Possible values; STATEMENT, ROW and MIXED

setup mysql replication master config file

Now restart your mysql service on the master to apply the settings and check your variables have been persisted and that the binary logs exist in the path which you set.

2/ Basic settings for configuring the slave server for MySQL replication

  • server-id – each server in your configuration needs its own unique id. Start at the lowest number greater than the master server_id up to 32^
  • relay-log – the slave will read it’s own relay log and apply the replicated commands to its copies of the databases. It isn’t critical that it’s set as the server will create it’s own relay log location but I always ensure that it’s set from the start so that I know where the relay logs are being output to. Once again the mysql user needs permission to the path where the relay logs are.

setup mysql master slave replication slave config file

Restart the slave mysql service and check that those settings have been persisted.

For some useful optional settings, check out the MySQL documentation for all that are available.

3/ Create a user for replication on the master

The slave needs to authenticate to the master. You create a new user on the master and specify the user created which the slave will authenticate as.

You could use an existing user but logically you wouldn’t right? It is not recommended for a couple of reasons:

  • If the user account has to be deactivated – in the event of someone leaving the company or some application login that is no longer required, it would break replication.
  • Permissions – replication needs specific permission to operate and you create your MySQL user with only the permissions required to perform the specific function. If the user account is compromised it serves as damage limitation to create user accounts with defined permissions only.
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'yourhostname.com' IDENTIFIED BY 'EnterSecurePasswordHere';

4/ Record binary log co-ordindates on the master

The purpose of the binary log is to record all changes to the database that need to be applied on the slave. At this point, you need to find the master binary log co-ordinates and record them which you will then apply to the slave. When replication is started, it will know at what point in the log to start reading and applying updates to its copies of the master server’s databases.

You should prevent any updates to the master and then record the log position. This is easy to do with a one liner command:

FLUSH TABLES WITH READ LOCK;

Now leave the connection window open, open up a new connection and run this to get the log position:

SHOW MASTER STATUS;

Here you can see the current file that the master is writing too and the current log position. This information will be used later to configure the slave.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      154 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5/ Configure the slave with master binary log details and start it running

Connect to the slave, run a CHANGE_MASTER_TO with the settings recorded in step 4

CHANGE MASTER TO
MASTER_HOST='master.yourdomain.com',
MASTER_USER='replication',
MASTER_PASSWORD='EnterSecurePasswordHere',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;

mysql replication change master to

On the slave, start it by issuing this :

START SLAVE;

6/ Unlock tables on the MASTER and check status on master and slave

On the master:

UNLOCK TABLES;

On the slave:

SHOW SLAVE STATUS;

setup mysql replication show slave status

Check to see if there are any errors reported and ensure that settings you added above are correctly set in. Check Master_Log_File and Read_Master_Log_Pos

Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154

7/ Testing

Create a new empty database and ensure that it appears on the slave.

You’re now ready to begin loading data onto your master if replication is working correctly.

This is a basic mysql master slave replication tutorial to get you going but there are more details config changes that you can make depending on your application and server requirements. I do encourage you to read the relevant documentation so that you are fully informed on all the possibilities and implications.

Filed Under: MySQL Administration Tagged With: mysql

How to Use SQL CASE for Conditional Logic in Your SQL Queries

Posted on May 15, 2017 Written by Andy Hayes Leave a Comment

SQL CASE provides the author of the query with the ability to perform conditional logic in their SQL queries for SELECT, INSERT, UPDATE, DELETE. It is also possible to use it with SET, IN, HAVING, ORDER BY and WHERE.

It comes in two formats:

  • simple case
  • search case

Simple SQL CASE

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

What that means in practice is that you can test a value such as “Labrador” and output a value such as “Dog”.

Let’s look at an example. Here is a table script and some data I added.

Table creation script

CREATE TABLE [dbo].[Breeds](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Breed] [varchar](50) NOT NULL,
  [Value] [int] NOT NULL,
 CONSTRAINT [PK_Breeds] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The data added is here:

SQL CASE sample data

We want to query this data and produce a new column that contains the type of animal as it was not supplied in the original data. Here is the code to do that:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed = 'Labrador' THEN 'Dog'
    WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
    WHEN Breed = 'Golden Retriever' THEN 'Dog'
    WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
    WHEN Breed = 'French Bulldog' THEN 'Dog'
    WHEN Breed = 'Siamese' THEN 'Cat'
    WHEN Breed = 'Persian' THEN 'Cat'
    WHEN Breed = 'Bengal' THEN 'Cat'
    WHEN Breed = 'Ragdoll' THEN 'Cat'
    WHEN Breed = 'American Shorthair' THEN 'Cat'
  END AS Animal
FROM Breeds;

Which produces:

SQL CASE example result

Having looked at the data and knowing that it only contains two types of animal, the SQL CASE could also be written in a more simple way using an ELSE:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed = 'Labrador' THEN 'Dog'
    WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
    WHEN Breed = 'Golden Retriever' THEN 'Dog'
    WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
    WHEN Breed = 'French Bulldog' THEN 'Dog'
  ELSE 'Cat'
  END AS Animal
FROM Breeds;

Another way to write this would be to use IN:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed IN ('Labrador', 'King Charles Spaniel','Golden Retriever','Yorkshire Terrier','French Bulldog') THEN 'Dog'
  ELSE 'Cat'
  END AS Animal
FROM Breeds;

Search SQL CASE

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

What that means in practice is that you can make a test such as <= 10 and output a value such as “£0 – 10”.

With the sample data we have been working with, this format can be used with it.

SELECT ID, Breed, Value 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal
FROM Breeds;

The test against the ID column produces a Boolean result and this enables us to produce the same result with less code. This Boolean matching can be really useful when dealing with numeric type data types as it allows rows to be grouped easily based on the numeric value.

For example:

SELECT ID, Breed, Value, 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal,
  CASE 
     WHEN Value < 400 THEN '£0 - £399' 
     WHEN Value < 700 THEN '£400 - £699' 
  ELSE '£700 +' 
  END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

This produces:

SQL CASE example result using Search CASE

Can you spot the bug in the SQL code? What if for some reason another record were added with a value of less than zero. This record would appear in the £0 – £399 price bracket.

So you could handle this in a couple of ways:

  • use between
  • use nested case

Between example:

SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

Nested case example:

SELECT 
  ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal,
  CASE WHEN Value > 0 THEN
    CASE 
      WHEN Value < 400 THEN '£0 - £399' 
      WHEN Value < = 700 THEN '£400 - £699' 
      ELSE '£700 +' END 
  END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

Using SQL CASE with ORDER BY

It is possible to use CASE with ORDER BY to order results based on if then else logic. Extending our last query to have an ORDER BY included, the criteria here is to ensure that the ‘Persian’ breed is the first row.

SELECT ID, Breed, 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal
FROM Breeds
ORDER BY CASE WHEN Breed = 'Persian' THEN 1 ELSE 2 END;

This produces:

SQL CASE example result using ORDER BY

Using SQL CASE in an UPDATE statement

If you remember, the data was supplied without an ‘Animal’ column and we’re having to calculate this in the SQL. It would be better to create a new table to contain the animal description and create a relationship between our Breeds table and the new Animals table.

Here is our new table:

CREATE TABLE [dbo].[Animals](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Animal] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

First we need to populate it with the animal values. We know we can do this with the help of our earlier use of CASE

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)

INSERT INTO Animals
SELECT DISTINCT Animal FROM cte_Breeds;

This is our data:

SQL CASE related table

We now need to add a new column to the Breeds table to house the ID from the Animals table to complete the relationship.

ALTER TABLE Breeds 
  ADD AnimalID INT NULL,
  FOREIGN KEY(AnimalID) REFERENCES Animals(ID);

Now we will run an UPDATE on our Breeds table using CASE

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)

UPDATE Br
SET AnimalID = CASE WHEN cte.Animal = 'Cat' THEN 1 ELSE 2 END
FROM cte_Breeds cte
JOIN Breeds Br ON cte.ID = Br.ID;

So this has worked successfully as we now have a relationship between the two tables:

SQL CASE with UPDATE

For the purposes of this example, the UPDATE SET CASE is hard coded. This would be a problem if there were many more breeds to update with different animal types and so in that case it would be more efficient not to use CASE. Instead one might do this:

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)


UPDATE Br 
SET AnimalID = An.ID
FROM Animals An 
JOIN cte_Breeds cte ON cte.Animal = An.Animal
JOIN Breeds Br ON cte.ID = Br.ID;

I hope you found this post useful. Check out the official documentation with more examples about SQL CASE here – CASE (Transact – SQL)

Filed Under: All Articles, SQL Tips and Tricks Tagged With: mysql, sql server, t-sql

  • 1
  • 2
  • 3
  • …
  • 22
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close