Showing entries 11 to 20 of 31
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: database administration (reset)
LDAP Authentication in MySQL

We manage hundreds of servers and have a need to add and remove DBAs, application developers, and so on regularly. Doing this manually is just beyond the scope of what we can realistically manage. Since we are already using LDAP, we wanted to find a way to integrate it with MySQL.

After reading many, many articles and getting diverse opinions as to whether or not it was even possible, we decided to give it a try. From what we read, it appears that MySQL Enterprise edition does exactly what we want; unfortunately, we don’t have an Enterprise support contract ruling that out as an option. Knowing that Percona tends to mimic many of the Enterprise features, we decided to pursue doing this with Percona Server. Further reading showed this could be possible with the PAM plugin which would then authenticate via LDAP. A little indirect, but seemed like our only solution.

Since MySQL does not provide a community version of its LDAP …

[Read more]
Analyst for MySQL v1.1: Database Performance, Security, & Best Practices Auditing Tool Released – Download for FREE!

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.

Make More Efficient Use of Your Time

Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.

Cross-Platform

Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The …

[Read more]
How to Tell If It’s MySQL Swapping

On servers that are into the swap file and have multiple processes running, I often wonder if it’s MySQL that is in the swap or some other process.  With Linux this is a fairly easy thing to figure out.  Below is the format of a command to do just that:

echo 0 $(cat /proc/`pidof process`/smaps | grep TYPE | awk '{print $2}' | sed 's#^#+#') | bc

 

In the above command, TYPE refers to what type of Memory you want to examine.  Below are some of the options for this value:

TYPE Explanation
Private Private memory used by the process.  You may be able to determine memory leaks by looking at this value over time.
Pss Proportional Set Size.  This is the Rss adjusted for sharing.  For example, if a process has …
[Read more]
MySQL Installation Process Checklist

MySQL Installation Process Checklist

All DBAs, regardless of experience level, should follow a written process when setting up a new server.  There are just too many steps to neglect doing so and many of the steps you are likely to forget have little to do with MySQL.

Naturally, every company has a different process.  The process we outline below is one we have used in the past and focuses on working through the Change Management process, setting up backups and monitoring, and focusing on good communication with team members and clients as well as ensuring documentation of your work.  Hopefully this article will give you some ideas on implementing your own process document.

Below are the steps we have documented in the past when creating a new installation of MySQL:

  • Initial Change Management Processes
    • Edit the ticket and set to Waiting on Customer
[Read more]
Load Balancing a Galera Cluster

Introduction

This article details the proper method of load balancing either a Percona XTRADB Cluster (PXC) or MariaDB Cluster.  Both of these solutuions utilize the same Galera plugin to facilitate clustering.  This information deatails what ports must be open and how they are to be configured as well as how to route traffic to the database nodes.

There are generally a total of three (3) nodes in this cluster, along with a single load balancer. This is the minimum number of nodes required for a cluster. This does not mean that the cluster is down if one or two nodes are lost – it only means that at least three nodes are required for normal cluster operation.

 

Data Centers

One of the most common questions we receive is whether the cluster can span two data centers.  The short answer is “Yes, but…”   While it is possible for a cluster to span two data centers, it …

[Read more]
Performance Tuning a Data Loading Process

One common job for a DBA is working with a Development Team member on a batch loading process that is taking more time than expected.

Often once you start probing into what has already been done to performance tune the load process, it seems you often learn that very little has, in fact, been done to improve efficiency and speed.

Since there are so many things that can be done to improve this process, it really is outside the scope of this article to cover them all.  Hopefully this will serve as a guide to get you started in the process.

Let’s outline best practices for loading data into MySQL very quickly.  While this is not a comprehensive list of loading methods and configuration, it is a good starting point.

 

MySQL Configuration

Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly …

[Read more]
Syncing a Broken Slave With Percona XTRABackup

Sometimes data sets are so large, a mysqldump to load a slave is just not practical.  With some of the systems we have administrated, we have had data so large it would have taken days to load the slave when it became out of sync with the master.  When this happens, we usually rely upon Percona’s XTRABackup utility which allows us to make a hot/online backup of the master to use for loading the slave.

In the old days we had to rely upon a third-party tool called ibbackup, or InnoDB Hot Backup utility to do this task. In many ways XTRABackup is a replacement for this tool and has in fact surpassed the ibbackup utility in features and function.

The most efficient way we have found to transfer that data to the slave is the use of the netcat utility.

We also use the screen command since we expect this could take quite some time and don’t want to take the chance that a network connection issue, or a dropped VPN, …

[Read more]
Script to Convert Storage Engine on All Tables

Sometimes you encounter a server with multiple tables of a particular storage engine which you need to convert to another storage engine.  For us, this often happens when we find systems running MyISAM and we want to get these over to InnoDB.

There are a number of reasons to consider converting a table to another storage engine, such as performance, gaining additional features such as Foreign Keys, and so on.  You should, however, stop to consider that not all storage engines are created the same and do not offer the same features.

If there are hundreds of tables, the process can be very time consuming so we put together a simple bash script to automate this process.

#!/bin/sh

MY_USER="root"
MY_PASSWORD="mypassword"
MY_HOST="127.0.0.1"
MY_PORT=3306
NEW_ENGINE="InnoDB"

TABLES=`mysql -u$MY_USER -p$MY_PASSWORD -h$MY_HOST -P$MY_PORT -e"SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS 'TABLE' FROM …
[Read more]
Which App is Using a Port?

Have you ever tried to start a server like MySQL and been amazed to see an error that the port is already in use? You rack your brain and try to figure out what it would be to no avail. Sometimes you do a “ps” in Linux and don’t even see anything that you think would be using the port. Well, forutantely, there are some tricks to help you find out without doing a reboot. If it is a production server, a reboot may not be an option anyway!

Below are some methods to help. We will start by looking at the “fuser” utility provided with many Linux distros:

fuser -n tcp 80
80/tcp:               1029  1030  1824  1838  1839  1840  1841 13972 14136 14137 14712

This example shows a simple check of everything using port 80. What you see above is a list of PIDs that are using that port. Now we could probably just do a simple “ps” to figure out what it is. You might also want to get more info by doing something like the …

[Read more]
Monitoring Disk Space

Some time back, when a client wanted us to setup MySQL Enterprise Monitor, we were surprised to find out that disk monitoring was not available! We worked hard to come up with a solution. Eventually, we decided to setup a custom agent to monitor the disk. Below is the result of that.

While this script may not work as-is for everyone, it should at least provide a basis for such a script. This script has been modified to send an email instead of plug directly into the MySQL Enterprise Monitor. But, it hopefully will get our creative juices flowing…

#!/bin/bash
#
# This script does a very simple test for checking disk space.
#
# Itchy Ninja Software: http://www.itchyninja.com
#

CHECKDISK=`df -h | awk '{print $5}' | grep % | grep -v Use | sort -n | tail -1 | cut -d "%" -f1 -`
ALERT_VALUE="80"
MAIL_USER="root@localhost.com"
MAIL_SUBJECT="Daily Disk Check"

if [ "$CHECKDISK" -ge "$ALERT_VALUE" ]; then
echo "At least one of my disks is nearly …
[Read more]
Showing entries 11 to 20 of 31
« 10 Newer Entries | 10 Older Entries »