Replication Techniques in depth

June 15, 2012

The third book of the Effective MySQL series provides the reader with the tools, knowledge and tips for managing a scalable MySQL Replication environment. Effective MySQL: Replication Techniques in Depth by Ronald Bradford and Chris Schneider

Chapters

The Five Minute DBA
Diagnosing Common Replication Problems
Improving Standard Replication Features
Understanding Multi-Master Replication
MySQL Replication Tools
Extending Replication for Practical Needs
MySQL Configuration Options
Monitoring Replication

Order Now

This title will be available print, and ebook formats from these retailers. You can pre-order now.

Other Titles

See other titles in our series including:

MySQL Disasters, and how to avoid yours

June 5, 2012

Download PDF Presentation

Organizations are always making improvements for scalability, however disaster preparedness is the poor cousin. This presentation will show you how to easily avoid the most common MySQL disaster situations.
Backup and recovery is critical for business continuity, many websites run the risk of data loss or corruption because existing procedures (if any) are generally flawed. Discussion includes:

The essential backup options
Why Binary logging for point in time recovery is important
How replication changes things
Recovery complexities

Presenter: Ronald Bradford
Schedule:

Effective MySQL Meetup – May 2012- New York, NY
South East Linux Fest 2012 – June 2012 – Charlotte, NC

Filed Under: Presentation

MySQL Backup & Recovery Essentials

May 16, 2012

Download PDF Presentation

A hardware, software or human failure can occur at any time. Are you prepared?
Many organizations take a risk of serious data loss and system downtime with inadequate procedures in place to support a disaster recovery. This presentation covers the essentials of MySQL backup and recovery options, identifying the necessary tools for an effective strategy to support data resilience and business continuity for your organization. MySQL has no one single unbreakable backup solution, so it is important to understand the impact of MySQL replication, storage engines, configuration options for durability, hardware configuration and the impact on locking and uptime for the various hot/warm/cold options available.

Short Url: http://j.mp/EM-BandR
Presenter: Ronald Bradford
Schedule: RMOUG QEW – May 2012- Denver, Colorado

Filed Under: Presentation

LAMP Performance Optimizations – Volume 1

May 1, 2012

Download PDF Presentation

Get a detailed introduction to the very broad topic of end-to-end performance optimization in web applications based on LAMP stack (Linux, Apache, MySQL, PHP).

Short Url: http://j.mp/EM-LAMP-opt
Presenter: Ronald Bradford
Schedule:

OTN MySQL Developer Day in May 2012 – New York.
Effective MySQL Meetup York and Web Performance Meetup. – July 2011 New York

Filed Under: Presentation

Setting up Google Cloud SQL

April 18, 2012

You can activate Google Cloud SQL from the Google APIs Console at https://code.google.com/apis/console. NOTE: At the time of this publication this was in limited beta.

From the Google APIs console, you can create a new instance. You can then use the Web interface to run SQL statements, import and export data. There is also a Google SQL command line client that can be configured with:

# Java 6 is a dependency of the Google SQL client
$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless
$ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip
$ cd $HOME
$ mkdir cloud-sql
$ cd cloud-sql/
$ wget http://dl.google.com/cloudsql/tools/google_sql_tool.zip
$ unzip ../google_sql_tool.zip
$ ./google_sql.sh 
Please authorize Google SQL Service for your Google Account at:

http://goo.gl/XXXX

Enter Authorization Code: *************************
sql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.10 sec)

NOTE: The *first time* you access your instance you need to confirm authorization by going to the provided URL.

Filed Under: Article

Setting up AWS RDS

April 18, 2012

Amazon Web Services (AWS) provides a managed MySQL solution via Relational Database Service (RDS). The following instructions will enable you to configure and run RDS. Refer to Using Amazon Web Services for initial information about AWS requirements.

The following instructions are for Ubuntu.

Pre-requisites

$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless
$ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip

Installation

# See http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/Welcome.html?r=8890
$ cd $HOME
$ mkdir -p aws
$ cd aws
$ wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip
$ unzip RDSCli.zip
$ ln -s RDSCli-*/ rds

Configuration

You need to create the $HOME/aws/credentials file with your specific account details.

AWSAccessKeyId=XXXXXXXXXXXXXXXXX
AWSSecretKey=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

The following should be added to a given startup profile

export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/jre
export AWS_RDS_HOME=$HOME/aws/rds
export PATH=$AWS_RDS_HOME/bin:$PATH
export AWS_CREDENTIAL_FILE=$HOME/aws/credentials

A number of different MySQL versions are available.

$ rds-describe-db-engine-versions -e mysql
VERSION  mysql  5.1.45  mysql5.1  MySQL Community Edition  Mysql 5.1.45
VERSION  mysql  5.1.49  mysql5.1  MySQL Community Edition  MySQL 5.1.49-R1 with innodb plugin
VERSION  mysql  5.1.50  mysql5.1  MySQL Community Edition  MySQL 5.1.50-R3
VERSION  mysql  5.1.57  mysql5.1  MySQL Community Edition  MySQL 5.1.57-R1
VERSION  mysql  5.1.61  mysql5.1  MySQL Community Edition  MySQL 5.1.61-R1
VERSION  mysql  5.5.12  mysql5.5  MySQL Community Edition  MySQL 5.5.12-R1
VERSION  mysql  5.5.20  mysql5.5  MySQL Community Edition  MySQL 5.5.20-R1
VERSION  mysql  5.5.8   mysql5.5  MySQL Community Edition  MySQL 5.5.8.R1 GA

Verification

$ rds-describe-db-instances
$ rds-describe-db-parameter-groups
DBPARAMETERGROUP  default.mysql5.5  mysql5.5  Default parameter group for mysql5.5

It is not possible to change the default parameter group so you need to create a copy for any future configuration modifications.

$ rds-create-db-parameter-group running-mysql55 -d 'Running MySQL 5.5' -f mysql5.5

Creating a new instance

$ rds-create-db-instance book2 --db-instance-class db.m1.large --allocated-storage 20 --engine mysql --master-username dba --master-user-password passwd --db-parameter-group-name running-mysql55
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync
$  rds-describe-db-instances
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  us-east-1a  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

It can take some time (e.g. 5-10 minutes for your instance to be available. When ready it will show available. In this case, the instance host is defined, however the instance is not yet accessible.

$ rds-describe-db-instances
DBINSTANCE  book2  2012-04-18T15:09:08.420Z  db.m1.large  mysql  20  dba  backing-up  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

While waiting, you can create the necessary permissions to access the RDS instance.

$ rds-describe-db-security-groups
SECGROUP  default  default
$ rds-authorize-db-security-group-ingress default --cidr-ip 10.194.163.1/21
SECGROUP  default  default
      IP-RANGE  10.194.163.1/21  authorizing
$ rds-describe-db-security-groups
SECGROUP  default  default
      IP-RANGE  10.194.163.1/21  authorized

$ rds-describe-db-instances
DBINSTANCE  book2  2012-04-18T15:09:08.420Z  db.m1.large  mysql  20  dba  available  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

The following can be used to monitor.

$ while [ : ]; do  date; rds-describe-db-instances; sleep 15; done
Wed Apr 18 19:27:14 UTC 2012
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync
...
Wed Apr 18 19:35:48 UTC 2012
DBINSTANCE  book2  2012-04-18T19:31:51.919Z  db.m1.large  mysql  20  dba  available  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1c  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

Confirmation of running MySQL Instance.

$ mysql -udba -ppasswd -hbook2.cqmcgpjfkies.us-east-1.rds.amazonaws.com -e "SELECT VERSION()"
+------------+
| VERSION()  |
+------------+
| 5.5.20-log |
+------------+

Terminating

$ rds-delete-db-instance book2 --skip-final-snapshot
    Once you begin deleting this database, it will no longer be able to accept
    connections.
    Are you sure you want to delete this database? [Ny]y
DBINSTANCE  book2  2012-04-18T14:58:54.251Z  db.m1.large  mysql  20  dba  deleting  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  default.mysql5.5  in-sync

You can pass -f to avoid the prompt.

Filed Under: Article

MySQL Idiosyncrasies That Bite

April 13, 2012

Download PDF Presentation

While MySQL is a popular and widely used database product, there are some default features and settings which can be foreign in comparison with other commercial RDBMS products such as Oracle. In this discussion, Ronald Bradford will discuss some of the MySQL defaults that are not what you may expect. These include understanding the concept of storage engines, and the default non-transactional state, how silent data truncations occur which affect your data, ideal practices for date management, and the MySQL transaction isolation options. These are all critical to clearly understand and implement correctly for data integrity and consistency. He will cover in-depth topics including SQL_MODE and the recommended best practices for default settings, the ideal user permissions and privileges including not what to do, and also the best practices for character sets and collations to ensure your UTF8 is stored and retrieved correctly.

Presenter: Ronald Bradford
Schedule:
Percona Live – Santa Clara, April 2012
New York MySQL Meetup, April 2011, New York

Filed Under: Presentation

Explaining the MySQL EXPLAIN

April 12, 2012

Download PDF Presentation

Determining the Query Execution Plan (QEP) of an SQL statement is the primary analysis tool for DBAs. Understanding how to interpret the information from the EXPLAIN command and what additional commands and tools exist to add supplementary information are essential skills that will be used daily in production operations.

The MySQL EXPLAIN QEP is significantly different from an Oracle QEP and Oracle DBAs need to understand and learn the most appropriate way to understand and navigate this information to effective performance tune a running MySQL environment.

In this presentation we will cover the following:

EXPLAIN & EXPLAIN EXTENDED Syntax
Additional commands including SHOW CREATE TABLE, SHOW INDEXES and ANALYZE TABLE
MySQL Primary Key, Unique Index, different Index Types and Index Cardinality specifics
Understand how different Storage Engines affect the QEP
The impact of multi-column indexes, partial indexes and covering indexes

Presenter: Ronald Bradford
Schedule:

Percona Live – Santa Clara, April 2012
EMEA Harmony – Helsinki, Finland May 2011
South East Linux Fest 2012 – June 2012 – Charlotte, NC

Filed Under: Performance Analysis, Presentation

Using Amazon Web Services

March 17, 2012

After Setting up Amazon Web Services you can follow these steps to use the EC2 CLI tools to manage EC2 instances.

As per the setup instructions you require the following environment variables to use the tools. It is recommend you add these to $HOME/.bashrc or appropriate shell startup script.

$ export EC2_HOME=$HOME/aws/ec2
$ export PATH=$EC2_HOME/bin:$PATH
# For Ubuntu
$ export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/
# For Mac OSX Use
# export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Home/

Necessary Access Controls

In addition you require the various AWS access keys before running any EC2 commands.

$ cd $HOME/aws
$ mv ~/Downloads/cert-EPE36OOAUD6QZZVFJCDJTEWFAG2EPWGA.pem  cert.pem
$ mv ~/Downloads/pk-EPE36OOAUD6QZZVFJCDJTEWFAG2EPWGA.pem  pk.pem
$ export EC2_CERT=$HOME/aws/cert.pem
$ export EC2_PRIVATE_KEY=$HOME/aws/pk.pem

Two more one off tasks are needed before you can launch a new instance. These are a keypair to access the instance, and a security group to maintain firewall rules.

$ ec2-add-keypair admin | grep -v "^KEYPAIR" > admin.pem
$ chmod 600 admin.pem
$ ec2-create-group db -d "DB Servers"
$ ec2-authorize db -p 22 -s XXX.XXX.XXX.XXX/32

In this example, an SSH keypair named “admin” has been created, and a security group called “db” with SSH only access from a given IP.

You should replace XXX.XXX.XXX.XXX with your current IP address.
If you do not know your current IP Address, goto http://www.whatismyip.com/.

Launching an EC2 Image

You must start with a pre-defined image to launch, known as an Amazon Machine Image (AMI). Amazon provides a number of images as do many third parties. As this example launches a Ubuntu, we use the official AMIs listed at https://help.ubuntu.com/community/EC2StartersGuide

$ ec2-run-instances ami-baba68d3 --instance-type t1.micro --region us-east-1 --group db --key admin
RESERVATION	r-c4ee13a7	336142022409	db
INSTANCE	i-91ef87f5	ami-baba68d3			pending	admin	0		t1.micro	2012-03-16T21:40:47+0000	us-east-1c	aki-805ea7e9			monitoring-disabled					ebs		paravirtual	xen		sg-cb2ef3a3	default

You can check the start of your running instances with

$ ec2-describe-instances
RESERVATION	r-c4ee13a7	336142022409	db
INSTANCE	i-91ef87f5	ami-baba68d3	ec2-23-20-96-190.compute-1.amazonaws.com	domU-12-31-38-01-6A-F6.compute-1.internal	running	admin	0		t1.micro	2012-03-16T21:40:47+0000	us-east-1c	aki-805ea7e9	monitoring-disabled	23.20.96.190	10.253.109.8			ebs					paravirtual	xen		sg-cb2ef3a3	default
BLOCKDEVICE	/dev/sda1	vol-9bc893f7	2012-03-16T21:41:17.000Z	true

When an instance is ready it will show as “running”, and there will be an ec2-XXX-XXX-XXX-XXX.compute-1.amazonaws.com hostname to connect to.

$ ssh -i admin.pem [email protected]
$ uname -a
$ free -m
$ df -h
$ exit

Removing an EC2 Instance

If the server you launched is not being used, this is costing you money (unless this is your only t1.micro instance on the free account). You can remove with

$ ec2-terminate-instances  i-91ef87f5
$ sleep 10
$ ec2-describe-instances
RESERVATION	r-c4ee13a7	336142022409	db
INSTANCE	i-91ef87f5	ami-a7f539ce			terminated	admin	0		t1.micro	2012-03-16T21:40:47+0000	us-east-1c	aki-805ea7e9			monitoring-disabled					ebs	paravirtual	xen		sg-cb2ef3a3	default

Conclusion

You have successfully configured and launched an EC2 AWS instance. You should now read up on the various Instance Types and also understand the use and benefits of the various regions and availability zones.

Filed Under: Article

Setting up Amazon Web Services

March 16, 2012

This tutorial will help you register and get ready to use Amazon Web Service (AWS). AWS provides a free tier so it is possible to use these services at ZERO cost.

These instructions are used by the Effective MySQL: Backup and Recovery book examples.

Step 1: Registration

Goto AWS site
Register email address
Enter Login Credentials
Enter Contact Information
Enter Credit Card details (while this is required, provided you follow free guidelines you will not be charged)
Identity verification by Telephone
Activation of account

Screenshots

Step 2: Signin

Following the confirmation email that your AWS account has been activated you can signin for accounts details. While waiting you can perform Step 3 below.

Confirm access to services
Obtain Security Credentials
1. Access Key ID
2. Secret Access Key
3. X.509 Certificate (both cert and pk files)
4. AWS Account ID
5. Canonical User ID

Step 3: Install EC2 API tools

These tools provide a CLI access to AWS EC2 functionality. It is also possible to use the web-based GUI AWS Management Console.

$ mkdir -p $HOME/aws
$ cd $HOME/aws
$ curl --silent -o ec2-api-tools.zip http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
$ [ ! `type -p unzip` ] && sudo apt-get install -y unzip
$ unzip -q ec2-api-tools.zip
$ mkdir -p archive
$ mv ec2-api-tools.zip archive/
$ ln -s ec2-api-tools-* ec2
$ ls -l
$ export EC2_HOME=$HOME/aws/ec2
$ export PATH=$EC2_HOME/bin:$PATH
$ [ ! `type -p java` ] && sudo apt-get update && sudo apt-get install -y openjdk-6-jre-headless
$ java -version
# For Ubuntu
$ export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/
# For Mac OSX Use
# export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Home/
$ ec2ver

You should see something similar to the following.

1.5.2.5 2012-03-01

See http://docs.amazonwebservices.com/AWSEC2/latest/UserGuide/ for more information on the CLI tools.

Conclusion

You are now ready to start using Amazon Web Services.

Filed Under: Article Tagged With: amazon web services, aws, cli