Showing entries 31 to 40 of 139
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
How to work with a long process list in MySQL

I am generally a big fan of command line tools. This also applies to MySQL client software such as mysql or mysqladmin. To those spoiled by graphical interfaces, working in text mode may seem crude or even difficult. But the truth is that once you get used to these tools, you will be able to accomplish many things a lot faster than with any GUI client. Of course, using text terminal, which is the environment for any command line tool, has its drawbacks and limitations. For example on a relatively busy MySQL server, every so often when you run SHOW [FULL] PROCESSLIST, which lists client threads connected to a database, you can receive an output that will be many screens long. Sometimes it might be due to the high number of established connections – each takes at least one line on the screen, or sometimes due to some longish queries spanning over multiple lines. Finding relevant information there usually isn’t …

[Read more]
MySQL Backup and Restore

Making a compressed backup

mysqldump -u root -p database_name | bzip2 > output.sql.bz2
Restoring the compressed backup

shell> bunzip2 < output.sql.bz2 | mysql -u root -p
Copy database from one server to another

mysqldump db-name | ssh mysql -h db-name

mysqldump -u username -p'password' db-name | ssh mysql -u username -p'password -h db-name
Copy only table foo to database bar

mysqldump db-name foo | ssh mysql bar

mysqldump -u user -p'password' db-name foo | ssh mysql -u user -p'password' db-name foo

Add a Prefix to a fields value

Here's how to update your fields value by adding a prefix on it. You will have to use some functions in your WHERE statement to filter only those records that you want to be updated.

UPDATE `database`.`table`
SET field_value = CONCAT('PREFIX', field_value)
WHERE field_value = var;

Setting up XFS on Hardware RAID — the simple edition

There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options.  I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases.  This is not meant to cover every single tuning option, but rather to cover the important bases in a simple and easy to understand way.

Let’s say you have a server with standard hardware RAID setup running conventional HDDs.

RAID setup

For the sake of simplicity you create one single RAID logical volume that covers all your available drives.  This is the easiest setup to configure and maintain and is the best choice for operability in the majority of normal configurations.  Are there ways to squeeze more performance out of a server by dividing the logical volumes: perhaps, but it requires a lot of fiddling and custom tuning to …

[Read more]
Never say "there is no way"

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:
Unfortunately, there is no way to remove this from generated syntax with this command.(Emphasis mine).
Here's how it goes:

mysql> show create table mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE `mytest` (
`id` int(11) NOT NULL,
`description` varchar(50) DEFAULT NULL,
1 row in set (0.00 sec)

Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE …
[Read more]
TIL: Lookout For DEFINER

The Issue
I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.How can this be possible?
The ProblemFor a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:


So …

[Read more]
Fatal timeout !

There are several parameters to set a timeout on MySQL :

But I would like to focus on wait_timeout (or interactive_timeout depending on how you connect)

This timeout allows MySQL to automatically close a connection in case of non-activity during the time defined by this parameter (default value is 28000 seconds).

The problem I wish to explain here may happen when this timeout is set to a low value (about 10 to 30 seconds).

Indeed, in this case, this timeout may have serious consequences, look at that :
[The wait_timeout parameter is set to 10 seconds in this case]

You are connected through the standard MySQL client and you need to run a delete query (bypass autocommit) :

  • mysql> start transaction;
  • [ You …
[Read more]
Aligning IO on a hard disk RAID – the Theory

Now that flash storage is becoming more popular, IO alignment question keeps popping up more often than it used to when all we had were rotating hard disk drives. I think the reason is very simple – when systems only had one bearing hard disk drive (HDD) as in RAID1 or one disk drive at all, you couldn’t really have misaligned IO because HDDs operate in 512-byte sectors and that’s also the smallest amount of disk IO that systems can do. NAND flash on the other hand can have a page size of 512-bytes, 2kbytes or 4kbytes (and often you don’t know what size it is really) so the IO alignment question becomes more relevant.

It was and still is, however, relevant with HDD RAID storage – technology we have been using for many years – when there’s striping like in RAID0, 5, 6 or any variation of them (5+0, 1+0, 1+0+0 etc.). While IO inside the RAID is perfectly aligned to disk sectors (again due to the fact operations are done in …

[Read more]
Some More Replication Stuff

Listening to the OurSQL podcast: Repli-cans and Repli-can’ts got me thinking, what are the issues with MySQL replication that Sarah and Sheeri didn’t have the time to include in their episode. Here’s my list:
Replication Capacity Index This is a concept introduced by Percona in last year’s post: Estimating Replication Capacity which I revisited briefly during my presentation at this year’s MySQL Users Conference. Why is this important? Very …

[Read more]
Aspersa tools download shortcuts

I use Aspersa tools a lot and I find myself going to the website just to download one of the tools all the time. I love I can download maatkit with a simple wget command so I made shortcuts for all of the current aspersa tools. Here’s the full list with my favorite on the top and least favorite (but none the less very useful) on the bottom:

[Read more]
Showing entries 31 to 40 of 139
« 10 Newer Entries | 10 Older Entries »