I have addressed previously how multi-node writing causes unexpected deadlocks in PXC, at least, it is unexpected unless you know how Galera replication works.  This is a complicated topic and I personally feel like I’m only just starting to wrap my head around it.

The magic of Galera replication

The short of it is that Galera replication is not a doing a full 2-phase commit to replicate (where all nodes commit synchronously with the client), but it is instead something Codership calls “virtually synchronous” replication.  This could either be clever marketing or clever engineering, at least at face value.   However, I believe it really is clever engineering and that it is probably the best compromise for performance and data protection out there.

…

I would like to make a quick MySQL 101 about numeric types in MySQL and especially explain the difference between the numeric type attributes and the maximum value that a numeric type can store.

Someone asked me yesterday why when he inserts a value in a table, the value 127 appears instead of the value 160 that he’s just inserted.
Ok, let me explain, consider this table :

What happens if you want to insert this row in the table :

sql > insert into product values (160, 554, "iPad mini");
Query OK, 1 row affected, 1 warning (0.01 sec)
sql > show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 | …

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, and that master/master is not write scaling, but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go …

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, and that master/master is not write scaling, but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go …

Just setting up MySQL replication is not enough, you would need to periodically monitor your slaves to ensure they continue to work seamlessly. Here is a basic overview of the Slave variables to monitor and the tools that will help you monitor those with ease.

Top variables to monitor on your slaves

Replication is best monitored by checking the following variables-

1. SLAVE_RUNNING: This is a global status variable and its value can be checked using SHOW GLOBAL STATUS like 'slave_running'. It can either be ‘ON’ or ‘OFF’.

   If slave_running is ‘ON’, then the slave is up and working fine, which means both the SQL thread and the IO thread are running. If either the SQL thread or the IO thread is not running then this variable would be ‘OFF’. Use SHOW SLAVE STATUS and try to determine whether there was an error or the slave was stopped manually. Check …

Backup is one of the most important, part of the database administration. There are several ways to backup MySQL data. I'm discussing a very simple script, easy script  to take backup of multiple databases. This script can be customized as, per the requirement.

Script

#!/bin/bash
     
    MyUSER="backup" # USERNAME
    MyPASS="backup_pass" # PASSWORD
    MyHOST="localhost" # Hostname
     
    # Linux bin paths, change this if it can not be autodetected via which command
    MYSQL="$(which mysql)"
    MYSQLDUMP="$(which mysqldump)"
    CHOWN="$(which chown)"
    CHMOD="$(which chmod)"
    GZIP="$(which gzip)"
     
    # Backup Dest directory, change this if you have someother location
    DEST="/backup"
    
    Month="$(date +"%b")"
    Day="$(date +"%a")" 
    # Main directory where backup will be stored
    MBD="$DEST/data/$Month/$Day"
     
    # Get hostname
    HOST="$(hostname)"
     
    # Get data in dd-mm-yyyy …

SkySQL’s live in the UK – come and check us out at Oxford University - devfest

There will be a nice contrast in the air as the ancient institution of Oxford University plays home to the latest trends in open source software at the All Your Base conference, taking place at the Said Business School on the 23rd November. And we are prepared to show off our new Cloud and Enterprise products, allowing developers to get hands-on with the solutions.

read more

OurSQL, the MySQL Database Community Podcast, lists events, conferences and training that are relevant to MySQLers around the world. Gerry and Sheeri get the information for events/conferences/training from Planet MySQL and Oracle, SkySQL and Percona websites. Often, webinars are posted to Planet MySQL a week or 2 before they happen, which is not enough lead time for the podcast. We usually record on Tuesdays and publish on Fridays, with the understanding that not everyone listens to the podcast on Friday. So on the podcast, we talk about events that are at least 1 week from publication date. For example, we are recording today (Tue 20 Nov) for a podcast that will be published Friday (23 Nov). We will talk about events, conferences and training that will happen Sunday, December 2nd or later.

…

Have you been using Percona Server ? Interested to Try ? Using Percona Server in Production Tutorial at Percona Live, London is a great talk for you.

In my experience working with customers I discovered many people are not aware about number of unique features Percona Server offers and how they can help you to achieve better performance and stability in your MySQL environment. I start this talk with many general features which MySQL and Percona Server share giving advice on best production configuration and best practices and then move to special features which are unique to Percona Server. I provide specific examples how to use the features and what benefits they provide.

See you there !

I didn’t participate in 2012, but in 2013, I’m back on the conference committee for Percona Live Santa Clara. We have an awesome Program Chair in Shlomi Noach, and after much deliberation & commentary, we have a tutorials schedule out now. Expect that the rest of the conference content to be awesome too. Remember, April 22-25 2013 are the magical dates, so register now!

Related posts:

1. MariaDB at Percona Live …