Showing entries 101 to 110 of 142
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
Replace or Rename: that is the question.

Forget tedious hours of searching links to the renamed object and replacing them.

To save your efforts, dbForge Studio for MySQL automates renaming of tables, columns of tables, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script, right-click it and select the Rename option from the menu. The Rename dialog box opens where you can enter a new name and view all references to the renamed object in the expressions, strings, and even comments.

All you have to do is to select required references and press OK!

The refactoring functionality is indispensable while working with database projects. You can export a database to a project, rename …

[Read more]
How to pretty-print my.cnf with a one-liner

When I'm looking at a server, I often want to see the /etc/my.cnf file nicely formatted, and with comments stripped. This Perl one-liner will pretty-print the file:

PLAIN TEXT CODE:

  1. perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' /etc/my.cnf
  2. [client]                           
  3. port                               = 3306
  4. socket                             = /var/run/mysqld/mysqld.sock
  5. [mysqld_safe]                     
  6. socket      …
[Read more]
The feature I love in TokuDB

Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State.

PLAIN TEXT CODE:

  1. mysql> show processlist;
  2. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
  3. | Id | User | Host      | db     | Command | Time | State                     | Info                        |
  4. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
  5. |  3 | root | localhost | sbtest | Query   |   30 | Updated about 764000 rows | update sbtest set email=zip |
  6. ...
  7. mysql> …
[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being …

[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being …

[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being …

[Read more]
A rule of thumb for choosing column order in indexes

I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

PLAIN TEXT SQL:

  1. SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
  2.  AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
  3.  ORDER BY date ASC LIMIT 1;

Don't try to figure out the meaning of the query, because that'll add complexity to the example In the simplest case, we want to put the most …

[Read more]
Using netcat to copy MySQL Database

This is mainly a cheat sheet for me to remember. Nothing rocket science.

It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster.

Also note MySQL should be down when you copy data unless you're copying from snapshot etc.

So to copy go to the mysql data directory on both boxes; such as cd /var/lib/mysql . Make sure target directory is empty. Now on the TARGET server do nc -l 4000 | tar xvf - and on the SOURCE server do tar -cf - . | nc target_ip 4000

Also note - the port you're using should be open in the firewall.

Entry posted by peter | 22 comments

Add to: …

[Read more]
Designing Views With Query Builder

If you often need to create and modify views in your MySQL development you will like dbForge Studio for MySQL in-place query editing feature. This feature allows integration of powerful Query Builder tool with view editor without annoying copy/paste.

Suppose you have previously created view with some SELECT statement. To view and design this statement with Query Builder you need to perform these steps:

  1. Open view editor.
  2. Right-click on SELECT statement.
  3. In pop-up menu select ‘Design SQL…’ command.
  4. In opened Query Builder re-design you SELECT statement (See picture below).
  5. After you click OK new statement automatically gets pasted into the view  editor.

If you need to design new SELECT statement when you are creating view use ‘Insert SQL…’

[Read more]
Mass killing of MySQL Connections

Every so often I run into situation when I need to kill a lot of connections on MySQL server - for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

PLAIN TEXT SQL:

  1. mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
  2. +------------------------+
  3. | concat('KILL ',id,';') |
  4. +------------------------+
  5. | KILL 3101;             |
  6. | KILL 2946;             |
  7. +------------------------+
  8. 2 rows IN SET (0.00 sec) …
[Read more]
Showing entries 101 to 110 of 142
« 10 Newer Entries | 10 Older Entries »