Article about bug report #68814 related to testing count(*) explain plan.

Or sales table huge enough to play with.

mysql> select count(*) from sales;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.56 sec)

First with regular count(*) without where clause:

mysql> explain select count(*) from sales\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: index
possible_keys: NULL
          key: sales_cust_idx
      key_len: 4
          ref: NULL
         rows: 2489938
        Extra: Using index
1 row in set (0.00 sec)

Estimated rows -> rows: 2489938

Then with {where sales_id > 0}:

mysql> explain select count(*) from sales where sales_id > 0\G
*************************** 1. row …

Want to introduce our MySQL-AutoXtraBackup command line tool, using Percona Xtrabackup in core. Looking for contributor

Due to reported BUG report(BUG 73365) i am testing full disk error conditions with MySQL 5.6.19-debug-log.

There were some interesting error messages in error log:

2014-07-23 08:09:59 7f44500f0700 InnoDB: Error: Write to file ./xxx/news.ibd failed at offset 218103808.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.

InnoDB: 1048576 bytes should have been written, only -1 were written.
So what is “-1″ here?

The answer from:

[24 Jul 16:02] David Bennett

The -1 value is an error return from the os_file_pwrite() function in …

Recently in my slave server there was a full disk error because of the huge size of binary logs.Which is lead to critical error and BUG report which is still stays as open:

http://bugs.mysql.com/bug.php?id=72437

So the issue was a partition problem which in partitioning stage haven’t been specified for “/” (root) filesystem. But in “/home” there were left enough space.
The quick decision was to change binary and relay log location(path) to “/home”.
But there was a great challenge to get real working slave again.

Now we will explore all steps in our test virtual machine.
Test conditions for me:

1. CentOS 6.5 (x86_64) (both master and slave)
2. MySQL 5.6.19 GA (from official yum repo) (both master and slave)

Master my.cnf:

# BINARY LOGGING #
server_id                      = …

If you want take several actions on tables? It is really very exciting

Dear all, our today’s topic is about finding out the cause of MySQL crash and in depth examining issue for BUG reporting.
So what is exact mean of this article?
While i reported BUGs for MySQL, developers(experts) every time ask for a core dump (with gdb) and full stack-trace. Yeap they are right, finding an issue is only one step. You must also provide all necessary information to MySQL developers if you really interested in fixing bugs in open source. You have to help to find the core of problem.
For this purpose you must have a DEBUG MySQL edition on your hand to generate core dump and to see full stack-trace. Yes as you think, we will install MySQL from source with DEBUG option and will create core dump.

But first of all let to tell a few theory:
* There are several kinds of logging in MySQL:
1. General Log -> will log all kind of database activity. Surely you will not enable it in …

Secure connection? Today we will explore an interesting task about activating and using SSL connection in MySQL.
We will provide all necessary scripts and commands to re-play all steps.

So assume that we have CentOS 6.5 with OpenSSL and MySQL already installed:

[root@linuxsrv3 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using  EditLine wrapper

[root@linuxsrv3 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.17-log MySQL Community Server (GPL)

[root@linuxsrv3 ~]# openssl version
OpenSSL 1.0.1e-fips 11 Feb 2013

[root@linuxsrv3 ~]# rpm -qf `which openssl`
openssl-1.0.1e-16.el6_5.7.x86_64

Our test host is a Virtual Machine with static ip address: 192.168.1.77.

The rest of contents of this article is quite straight just follow commands:

###  Create Environment ###
[root@linuxsrv3 ~]# …

Recently i encounter an uprising among PHP Developers that , they must write a script additionally checking the strength of MySQL user password at creation time.
So, just for note that there is ready to use plugin in MySQL named: validate_password.so

The file location is default in plugin_dir in Linux:

mysql> select @@plugin_dir;
+--------------------------+
| @@plugin_dir             |
+--------------------------+
| /usr/lib64/mysql/plugin/ |
+--------------------------+
1 row in set (0,00 sec)

List this directory:

[root@linuxsrv3 plugin]# ls
adt_null.so     auth_test_plugin.so  innodb_engine.so      mypluglib.so          qa_auth_server.so   validate_password.so
auth.so         daemon_example.ini   libdaemon_example.so  qa_auth_client.so     semisync_master.so
auth_socket.so  debug                libmemcached.so       qa_auth_interface.so  semisync_slave.so

As you see there is a …

This topic is not for all situations but in my situation it helps.
The problem is with Slave is lagging behind master in very large numbers and the relay logs are continously growing.

Slave is using:

Relay_Log_File: mysql-relay-bin.000031

But in folder where relay log resides, was created up to:

mysql-relay-bin.000135

And it is continously growing.
So what i decide to do, again step-by-step i edited my.cnf file as follows:

1. I have 13 database so give to slave paralel workers 13:
slave-parallel-workers = 13

2. Disabled sync_binlog:
sync_binlog = 0

3. Changed at_trx_commit from 1 to 0:
innodb_flush_log_at_trx_commit = 0

4. Gave log_at_timeout (only from 5.6.6>) to 10:
innodb_flush_log_at_timeout = 10

5. Disabled slow query log( or commented out)

So …

Users are complaining about slowness in your system, MySQL load is always high… The more your database has access, the more it may get slow or worse: slowness even if it is running with low load. You are starting to get desperate! The consequences of slowness and high load are disastrous: If your site is slow,... Read More

The post MySQL Performance and Tuning Best Practices appeared first on Devops for Dummies.