Everything MySQL

A great place to be!

October 2009 Blog Posts (8)

Data type confusion: what is an int(11)?

Over and over I see customers that don't understand what int(11) really means. Their confusion is understandable. Many know what defining a char(10) means (a fixed-sized character string that allows up to 10 characters). However, ints are different.



First of all, there are 5 types of integer. They are all fixed size.

Type # of…
Continue

Added by Sarah Sproehnle on October 30, 2009 at 8:04pm — 1 Comment

Secure Your Server – Tips for DBAs

I have complied a list of a good tips for DBAs to consider when implementing security policies in MySQL.



-- The List

1. Make sure that ALL root accounts (ALL PRIVILEGED accounts) have strong passwords and apply them using hashes not plain text. If you leave the password blank anyone can connect as root without a password and be granted all privileges.



2. For ALL other accounts use passwords and apply them using hashes not plain text.

A good… Continue

Added by Chris on October 22, 2009 at 2:30pm — 2 Comments

Security Sensitive Grants

Consider excluding the following grants from users on any production MySQL server.



-- GRANT OPTION

"The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_grant-option)



-- RELOAD

"The RELOAD privilege enables use of the… Continue

Added by Chris on October 21, 2009 at 6:00pm — No Comments

Basics of MySQL failover (using replication)

For easy MySQL failover, replication is a great solution. This post will cover the basics of performing a failover in case your master dies.

First, setup one-way replication (or two-way, but don't plan to use both servers for writes at the same time). Next, you'll want to direct all activity, or at least the writes, to the master. If the master dies, there are two major concerns:

  1. Redirecting the clients to the slave. There are several ways to handle this, such as…
Continue

Added by Sarah Sproehnle on October 21, 2009 at 8:50am — 2 Comments

No Password – No Problem

In the past I have needed to gain access to MySQL servers when the password had been strangely forgotten. Below there are thee different processes to gain access to MySQL if you just don’t have the right kind of access you need or want.



Option 1: --skip-grant-tables



This will allow you to login to the server and change what you need to in the mysql.user table. Of course you will need to restart the server again without --skip-grant-tables if you want the… Continue

Added by Chris on October 20, 2009 at 6:49pm — 3 Comments

How MySQL Cluster executes queries

This post describes how MySQL Cluster executes queries. First of all, Cluster is a storage engine. It doesn't actually execute queries because it doesn't speak SQL. That is why you use a MySQL server, which parses your queries and sends low-level storage engine API calls to the Cluster data nodes. The data nodes know how to retrieve or store data. Or you can talk to the data nodes directly using the NDB API(s).



MySQL Cluster has various means of executing queries. They boil down… Continue

Added by Sarah Sproehnle on October 19, 2009 at 3:44am — No Comments

A MySQL problem or something else?

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.



The Problem



You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state,… Continue

Added by Chris on October 5, 2009 at 3:53pm — 2 Comments

Pager - but not on call!

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.



-- Example 1


Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…



Mysql> pager less –S



By running the… Continue

Added by Chris on October 2, 2009 at 5:25pm — 2 Comments

RSS

© 2024   Created by Chris.   Powered by

Badges  |  Report an Issue  |  Terms of Service