Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 72 Next 12 Older Entries

Displaying posts with tag: configuration (reset)

Reviewing your MySQL installation on Oracle Enterprise Linux
+5 Vote Up -1Vote Down

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27:

Binaries

MySQL binaries are found in /usr/bin.

$ ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root  314568 Feb 16 17:45 /usr/bin/mysql
-rwxr-xr-x 1 root root  110776 Feb 16 14:39 /usr/bin/mysqlaccess
-rwxr-xr-x
  [Read more...]
Verifying GROUP_CONCAT limit without using variables
+4 Vote Up -0Vote Down

I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here.

Normally, I would simply:

SELECT @@group_concat_max_len

However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid stored routines. So here’s a very simple test case: is the current group_concat_max_len long enough or not? I’ll present the long version and the short version.

The long version

  [Read more...]
Those oversized, undersized variables defaults
+2 Vote Up -0Vote Down

Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.

Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to 5M again. These settings are still the same on 5.5.

I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.

  • group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using
  [Read more...]
Replication configuration checklist
+1 Vote Up -1Vote Down

This post lists the essential and optional settings for a replication environment.

It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change.

Here are the settings for a basic Master/Slave(s) replication architecturee.

Essential

  • log-bin: enable binary logs on the master. Replication is based on the master logging all modifying queries (INSERT/CREATE/ALTER/GRANT etc.), and the slaves being able to replicate them.
  • server-id: each machine must have a unique server-id. A slave will not replay
  [Read more...]
Kontrollkit – new version available for download
+0 Vote Up -0Vote Down
Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/
Tuning your Cluster with ndbinfo (7.1) part 1 of X
+2 Vote Up -0Vote Down
The new ndbinfo interface in 7.1 is really useful to assist in tuning MySQL Cluster. Here is an example (more will follow):

I started with one test where I inserted two blobs (1KB + 1KB) in one table.
From 16 threads (colocated with one mysqld, two data nodes, separate computers) and one application driving the load I reached about 6960TPS, and the utilization of the redo buffers (controlled by the parameter RedoBuffer in config.ini) looked like:

mysql< select * from ndbinfo.logbuffers;
+---------+----------+--------+----------+----------+--------+
| node_id | log_type | log_id | log_part | total | used |
+---------+----------+--------+----------+----------+--------+
| 3 | REDO | 0 | 1 | 50331648 | 196608 |
| 3 | REDO | 0 | 2 | 50331648 | 294912









  [Read more...]
But I DO want MySQL to say “ERROR”!
+4 Vote Up -0Vote Down

MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

  • Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
  • Calling CREATE TEMPORARY TABLE? You get silent commit.
  • Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
  • Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
  • Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
  • Inserting 300 to a TINYINT column in a
  [Read more...]
Building MySQL Server with CMake on Linux/Unix
Employee +4 Vote Up -0Vote Down

CMake is a cross-platform, open-source build system, maintained by Kitware, Inc.

From the CMake.org home page:

CMake is a family of tools designed to build, test and package software. CMake is used to control the software compilation process using simple platform and compiler independent configuration files. CMake generates native makefiles and workspaces that can be used in the compiler environment of your choice.

It has been used for building the MySQL Server on Windows since MySQL 5.0 – the initial CMake build support was added in August 2006.

For

  [Read more...]
Quick reminder: avoid using binlog-do-db
+2 Vote Up -1Vote Down

Nothing new about this warning; but it’s worth repeating:

Using binlog-do-db is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database.

Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database.

Which is why a customer, who was using Toad for MySQL as client interface to MySQL, and by default connected to the mysql schema, did not see his queries being replicated. In fact, he later on got replication errors. If you do:

USE test;
INSERT INTO world.City VALUES (...)

Then the statement is assumed to be in the

  [Read more...]
To not yum or to not apt-get?
+11 Vote Up -1Vote Down

I’ve written shortly on this before. I like yum; I love apt-get; I prefer not to use them for MySQL installations. I consider a binary tarball to be the best MySQL installation format (source installations being a different case altogether).

Why?

I use yum and apt-get whenever I can and for almost all needs (sometimes preferring CPAN for Perl installations). But on a MySQL machine, I avoid doing so. The reason is either dependency hell or dependency mismatch.

Package managers are supposed to solve the dependency hell issue. But package managers will rarely have an up to date MySQL version.

I’ve had several experiences where a simple yum

  [Read more...]
MySQL Cluster Configurator v3
+2 Vote Up -0Vote Down
Version 3.0 of the severalnines/Configurator has been released:
  • Improved scripts (a lot of cleanup), prefixing the script output with the hostname:
    E.g,:
    Cluster: Cluster Start
    Cluster: STARTING MANAGEMENT SERVERS
    ps-ndb01: Starting management server (nodeid=1)
    ps-ndb01: Copying ../config/config.ini to /etc/mysql
    ps-ndb01: Started management server (nodeid=1, pid=28253)
    ...

  • Reduced number of scripts
    start-cluster-initial.sh --> start-cluster.sh --initial
    rolling-restart-initial.sh --> rollling-restart.sh --initial










  [Read more...]
Be sure to know your my.cnf [sections]
+3 Vote Up -0Vote Down

The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1

...

[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...

In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+

Be sure to add the right options to the

  [Read more...]
Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL
+4 Vote Up -0Vote Down

I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.

mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.

It is then that you do not need it anymore: everything is laid at

  [Read more...]
MySQL and hardware information
+8 Vote Up -6Vote Down

People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware.

If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.

We define “large data set” as over 100 Gb, mostly because smaller data sets have an easier time with the available memory on a machine (even if it’s only 8 Gb) and backups are less intrusive — InnoDB Hot Backup and Xtrabackup are not really “hot” backups, they are “warm” backups, because there is load on the machine to copy the data files, and on large, active servers we have found that this load impacts query performance. As for how active

  [Read more...]
Configuring for large databases in MySQL Cluster
+0 Vote Up -0Vote Down
If you need to create a big database into MySQL Cluster with:
  • A lot of tables indexes, columns, and tables
  • A lot of records
there are a few things to think about:
  • If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
    CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
    This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
  • Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
    • Each table you create will use one







  [Read more...]
MySQL related bookmark collection
+2 Vote Up -0Vote Down

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

Have you checked your MySQL error log today?
+2 Vote Up -0Vote Down

As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

Today’s special found in a 13GB MySQL server error log.

090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101071 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB:
  [Read more...]
Character sets: latin1 vs. ascii
+0 Vote Up -0Vote Down

Unless specified otherwise, latin1 is the default character set in MySQL.

What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.)

I find latin1 to be improper for such purposes and suggest that ascii be used instead. The reason being that latin1 implies a European text (with swedish collation). It is unclear for an outsider, when finding a latin1 column, whether it should actually contain West European characters, or is it just being used for ascii text, utilizing the fact that a character

  [Read more...]
Reasons to use innodb_file_per_table
+1 Vote Up -0Vote Down

When working with InnoDB, you have two ways for managing the tablespace storage:

  • Throw everything in one big file (optionally split).
  • Have one file per table.
  • I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable.

    A single tablespace

    Having everything in one big file means all tables and indexes, from all schemes, are ‘mixed’ together in that file.

    This allows for the following nice property: free space can be shared between different tables and different schemes. Thus, if I purge many rows from my log table, the now unused space can be occupied by new rows of any other table.

    This same nice property also translates to a not so nice one: data can be greatly fragmented across the

      [Read more...]
    The importance of report_host & report_port
    +0 Vote Up -0Vote Down

    The two server variables, report_host and report_port, are not commonly used. One of the reasons, I suspect, is that they do not appear in any of the standard .cnf files provided with a MySQL installation.

    For a replication environment, I find these two variables to be very useful.

    Where are these variables used?

    Here’s a slightly modified exerpt from a MySQL master node I have, and which has two slaves:

    mysql> SHOW SLAVE HOSTS;
    +-----------+------------+------+-------------------+-----------+
    | Server_id | Host       | Port | Rpl_recovery_rank | Master_id |
      [Read more...]
    More on variables ambiguities
    +2 Vote Up -0Vote Down

    Continuing Variables ambiguities in names and values, there are two more issues I wish to present.

    First, I’ve shown that variable values may be ambiguous. Thus, 1 and ON are interchangeable. But also 1 and YES.

    The important thing to note is that it’s not always like that. You can’t just swap 1 for ON or YES as you will: it depends on the variable. Thus, a specific variable (e.g. query_cache_type) may accept ON for 1 - but will not accept YES. Another may accept YES for 1 - bot not ON.

    Confused? Here’s the second

      [Read more...]
    Variables ambiguities in names and values
    +1 Vote Up -0Vote Down

    Writing up some scripts, I see more and more ambiguities with regard to global variables.

    For one thing, the names ambiguity between the hyphen (’-') and the underscore (’_'). So wait_timeout and wait-timeout are the same variable.

    But just check out the many levels of inconsistency:

    • Command line arguments (e.g. run mysqld with option variables) use the hyphen convention
    • mysql –verbose –help shows the hyphen convention
    • SHOW GLOBAL VARIABLES uses the underscore convention
    • The MySQL supplied sample configuration files use both conventions interchangeably

    Enough? Not quite: there are ambiguities in values, as well. For example, you may set query_cache_type to

      [Read more...]
    What Exactly is Swappiness?
    +1 Vote Up -0Vote Down

    This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.

    What can you find on the web?

    A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material—the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.

    So, what is swappiness?

    Towards the end of the email thread quoted in the article, you’ll find this definition (sort

      [Read more...]
    Lost and Found ?
    +0 Vote Up -0Vote Down
    Sometimes you just have to laugh at the crazy things that can kill a good evening. I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.So it
    Linux and open source no puff in the clouds
    +0 Vote Up -0Vote Down

    UPDATED - I had to update this post after a conversation with RightScale founder and CTO Thorsten von Eicken and for Sun’s Open Cloud announcement, which are both now included below.

    There has been some substantial technology and news regarding open source software in cloud computing lately. More proof that open source is reaching into nearly all aspects of enterprise and broader IT, and also reinforcement of the idea that open source software will continue to have a pervasive and disruptive impact on the way organizations of all shapes and sizes do their computing and deal with their data.

    First up is RightScale, which as detailed by 451 colleague and Principal Analyst William Fellows, is up and running across the pond on

      [Read more...]
    MySQL User Group Meetings in Israel
    +0 Vote Up -0Vote Down

    This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).

    Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in Herzlia; second one, held last week, was at Interbit (a MySQL training center) in Ramat Gan. We hope to hold these meetings on a monthly basis, and the next ones are expected to be held at Interbit.

    A new (blessed) law in Israel forbids us from sending invitations for these meetings via email without prior consent of the recepient (this law has passed as means of stopping spam). We do realize there are many users out there who would be interested in these meeting. For those users: please stay tuned to

      [Read more...]
    Concluded my first MySQL University Session about MySQL backups using file system snapshots - some questions remained unanswered...
    Employee +0 Vote Up -0Vote Down

    Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.

      [Read more...]
    Manually installing multiple MySQL instances on Linux: HOWTO
    +0 Vote Up -0Vote Down

    Installing a single MySQL instance on a linux machine is a very simple operation. It may be as simple as:

    apt-get install mysql-server

    But you cannot use this method to install another MySQL instance. Moreover, if you try to manually install another instance, you may find that some collisions occur.

    For example, when trying to install two 5.0 servers, apt-get or yum will just tell me “package is already installed”.

    When trying to install a new 5.1 server along with 5.0, an implicit upgrade is performed.

    But even if we try forcing the installation, there are collisions:

    • A dpkg or rpm will install my.cnf under /etc. Two installations will override one another. With RPM you may get a .rpmsave backup file, but that doesn’t help too much.
      [Read more...]
    cmon 0.13 released
    +0 Vote Up -0Vote Down
    The main fixes in cmon 0.13 are:
    • filtering of clusterlog
    • better graphs interface with filtering
    • fixed bug in load average graphs. Now the loadavg graphs works
    • added replication role, socket when adding a mysql server to be monitored from the web interface.
    • corrected bug with how often mysql_variables are collected
    • fixed problems with graphs
    • added images directory
    • revamped graphs - now producing files in images directory,
    • ajax support for Storage graphs
    • divided helpers into smaller files
    Upgrade from cmon 0.12
    • Stop cmon 0.12 (e.g. killall cmon from the command line)
    • Execute this sql script on the cmon




      [Read more...]
    MySQL security: data integrity issues
    +0 Vote Up -0Vote Down

    MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.

    I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.

    This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.

    I will consider three such issues.

    We will assume a database with two tables, and two users.

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
    GRANT SELECT, INSERT, UPDATE, DELETE ON `w2`.* TO 'w2user'@'%';

    We have one ‘root’ user, and one very simple ‘w2user’, which can’t be accused of having too many privileges. The schema, with some sample

      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 72 Next 12 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.