Showing entries 121 to 130 of 992
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Uncategorized (reset)
The Latest and Greatest MySQL Replication Features in MySQL 5.7.5

The latest and greatest MySQL 5.7 development milestone release (DMR) is out (changelog). It is great to see such a strong and steady cadence of development releases.  The latest one, 5.7.5, is packed with awesome features. Let me highlight a few ones related to replication.

Storing Global Transaction Identifiers History in a system table.

MySQL 5.7.5 introduces a new replication system table that is used by the server to save global transaction identifiers (GTIDs) execution history. This means that the user can setup slaves without binary logs and still use GTIDs. Such slaves may not be candidates to replace the master in the event a fail-over needs to be done – they do not have the binary log enabled – but since they save GTID history means that they can auto position themselves in the replication …

[Read more]
MySQL Security: Encrypting within MySQL. The new Enterprise Encryption plugin.

Keeping things safe and within MySQL now:

Will be speaking at Perconal Live London 2014

10 common replication problems

Below are 10 common replication problems :

1)Session binlog :

Setting sql_log_bin = 0 will disable binary logging for the session. So any DML/DDL query executed within that session on the master will not be replicated to slave


Turn off binary logging on master :

mysql> set sql_log_bin = 0 ;
Query OK, 0 rows affected (0.00 sec)

Create table in reptest database ( on master )

mysql> create table reptest(ID int) ;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables ;
| Tables_in_reptest |
| reptest           |
1 row in set (0.00 sec)

Validate to see that table is not created on slave :

mysql> use reptest ;
Database changed
mysql> show tables ;
Empty set (0.00 sec)

2) Updating slave directly

Master and slave will get out of …

[Read more]
Using information_schema tables – Part 1

Below are some of the common scenarios where information_schema is useful :

Tables table

How to check the database size using information_schema ?

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

How to list the tables created before 4 days ?

 select * from information_schema.tables where CREATE_TIME < NOW() - INTERVAL 4 DAY ;

Columns table :

List all the tables that have a column called birth_date

select table_name from information_schema.columns where table_schema='employees' and column_name='birth_date';

List all tables without primary/unique key:

group by 
    sum(if(column_key in ('PRI','UNI'), …
[Read more]
Tracing down a problem, finding sloppy code

Daniel was tracking down what appeared to be a networking problem….

  • server reported 113 (No route to host)
  • However, an strace did not reveal the networking stack ever returning that.
  • On the other side, IP packets were actually received.
  • When confronted with mysteries like this, I get suspicious – mainly of (fellow) programmers.
  • I suggested a grep through the source code, which revealed  return -EHOSTUNREACH;
  • Mystery solved, which allowed us to find what was actually going on.


  1. Don’t just believe or presume the supposed origin of an error.
  2. Programmers often take shortcuts that cause grief later. I fully appreciate how the above code came about, but I still think it was wrong. Mapping a “similar” situation onto an existing …
[Read more]
MySQL process list – Customized connection counts

You got a production support call related to connection usage. Max_connections is set to 400 and  connection usage has reached the maximum limit.

Below are some of the customized reports that can be used to trace the problem

Count by host :

mysql> select count(*),host from information_schema.processlist group by HOST;
| count(*) | host |
| 1 | localhost |
1 row in set (0.03 sec)

Count by database:

mysql> select count(*),host from information_schema.processlist group by DB;
| count(*) | host      |
|        1 | localhost |
1 row in set (0.00 sec)

Count by username:

mysql> select count(*),user from information_schema.processlist group by user; …
[Read more]
MySQL Workbench 6.2.1 BETA has been released

Dear MySQL Users,

The MySQL Workbench team is announcing availability of the first beta
release of its upcoming major product update, MySQL Workbench 6.2.

MySQL Workbench 6.2 focuses on support for innovations released in MySQL
5.6 and MySQL 5.7 DMR (Development Release) as well as MySQL Fabric 1.5,
with features such as:

* A new spatial data viewer, allowing graphical views of result sets
containing GEOMETRY data and taking advantage of the new GIS
capabilities in MySQL 5.7.
* Support for new MySQL 5.7.4 SQL syntax and configuration options.
* Metadata Locks View shows the locks connections are blocked or waiting
* MySQL Fabric cluster connectivity – Browsing, view status, and connect
to any MySQL instance in a Fabric Cluster.
* MS Access migration Wizard – easily move to MySQL Databases.

Other significant usability …

[Read more]
NDB – Forced node shutdown completed. Caused by error 2341: ‘Internal program error (failed ndbrequire)

recently while working on MySQL cluster 7.3.5, we came across this error

Forced node shutdown completed. Caused by error 2341: 'Internal program error (failed ndbrequire)(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.

The management nodes would start up but

the data nodes would complete phase 1 & shutdown, some times at phase 2 and some times at phase 5.

The error one would see in the trace file would be as follows:

Status: Temporary error, restart node
Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug)
Error: 2341
Error object: NDBCNTR (Line: 2493) 0x00000003
Program: ndbd

How did we resolve it ?

First, few of our data nodes had an incorrect connect-string

Second, we had pre-assigned NodeGroups for the …

[Read more]
Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.

In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.

If you want to upgrade from a …

[Read more]
Showing entries 121 to 130 of 992
« 10 Newer Entries | 10 Older Entries »