Feed Aggregator

When a MySQL Slave is set-up with mysqldump you have 2 possibilities:

• You dump into a file and then load the data into the Slave with the mysql client utility.
• You dump directly into the mysql client utility.

The first possibility has the advantage that you can start the load again if it failed. You can look into the file (and do some changes if needed).
The second possibility has the advantage that you do not need disk space and that it is possibly faster. But when the load fails you have to start from the very beginning.

What I was looking for is a way to combine everything in one step: Dumping to a file including compression and in the same step load the database to a slave. This is what I found to solve these requirements:

mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --quick <br>
--flush-logs --triggers --routines --events | tee >(gzip > /tmp/full_backup.sql.gz) | mysql --user=root --host=192.168.1.60 --port 3306

With this command you can …

Important: Before you start any operation mentioned below do a proper file system backup of your XFS file system you want to resize. If MySQL is running on this mount point do this with a stopped mysqld. Alternatively you can also use mysqldump to do the MySQL backup but test the restore time before continuing to not experience ugly surprises…

All these operations have to be performed as the root user. First we want to see what mount points are available:

shell> df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             485M   77M  383M  17% /
/dev/sdb1             496M  314M  157M  67% /var/lib/mysql

Our MySQL data are located on /dev/sdb1.

After the file system backup unmount /dev/sdb1 and resize the disk, partition or volume (works for VMware, NetApp filer and similar equipment, for LVM use lvextend):

shell> tar cvf /backup/mysql.tar /var/lib/mysql
shell> umount /var/lib/mysql
shell> fdisk /dev/sdb

Change the units in fdisk to have a better overview over your …

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

Tables created in memory are typically faster than tables created on disk. Thus we want as many as possible tables to be created in memory.

To achieve this we can configure the variables accordingly:

mysql> SHOW GLOBAL VARIABLES LIKE '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 25165824 |
| tmp_table_size      | 25165824 |
+---------------------+----------+

All result sets which are smaller than these values can be handled as MEMORY tables. …

Hi, I have now installed your great zabbix script on our 3 galera nodes. One thing that is strange is that the trigger MySQL is down doesn’t seem to work. Shutting down MySQL on one of the nodes should actually trigger this but there is just nothing… Is there something wrong in my configuration? Best Regards, Oli

Will come soon…

Please let us know your intention to for choosing this page!

Will come soon…

Please let us know your intention to for choosing this page!

Hallo MySQL Gemeinde,

Im neuen iX (2012-10) hat es zwei nette Artikel über: MySQL HA-Lösungen (S. 116) und MySQL 5.6 (S. 66).

Viel Spass beim Lesen!

Based on customer feedback we have decided to add a plugin Galera Cluster for MySQL to our MySQL Nagios/Icinga Plugins.

The module checks, if the node is in status Primary and if the expected amount of Galera Cluster nodes is available. If not, a warning or an alarm is returned.

The script is written in Perl and is Nagios Plugin API v3.0 compatible.

You can download it from our download page.

If you have suggestions for improvements, please contact us. Bugs can be reported at our bugs database.

The following modules are contained in the package:

• check_db_mysql.pl
• check_errorlog_mysql.pl
• check_galera_nodes.pl
• check_repl_mysql_cnt_slave_hosts.pl
• check_repl_mysql_hearbeat.pl
• check_repl_mysql_io_thread.pl
• check_repl_mysql_read_exec_pos.pl
• check_repl_mysql_readonly.pl
• check_repl_mysql_seconds_behind_master.pl
• check_repl_mysql_sql_thread.pl
• perf_mysql.pl

Hello Frazer,

Thank you very much for your valuable feedback. Good that Oracle is doing something in this direction and make MySQL even easier to use!

Oli

Hi Oli,

Good writeup!

We've recently modified the schema of the ndb_binlog_index table to include some extra per-epoch information - specifically the next_file and next_position columns.

The existing File and Position columns which you use above indicate the binlog file + position of the start of the epoch transaction. The new next_file and next_position columns indicate the first binlog position *after* the epoch transaction.

These new columns can be used during channel cutover with a slightly modified SELECT statement in the steps above :

master2> SELECT SUBSTRING_INDEX(next_file, '/', -1) AS master_log_file
  , next_position AS master_log_pos
  FROM mysql.ndb_binlog_index
 WHERE epoch = ;

Note that this is a simpler SELECT statement (no ORDER BY or LIMIT), and it has the following desirable properties :

1. 1) If there is no epoch transaction following the last applied epoch, we still get a start file and position. No need for a special case using SHOW MASTER STATUS.
2. 2) If the …

The dates for the next MySQL Cluster (ndb) trainings with the Linux Hotel are scheduled now: March 11 - 12 2013 and September 23 - 24 2013. You can book your training here.

Hello Roger,

The item FromDual.MySQL.check (MPM Agent is alive) is triggering the agent. Its interval is by default set to 10 seconds. Try to set the interval to 30 seconds and let us know if it helps…

Best regards, Oli

If you have questions related to FromDual GmbH please get in contact with us:

Photos

Photos about FromDual you can fint at Flickr

Blogs and Social Media

We provide you the following Company Blog aggregates:

• Our company technical blog in English
• Our company technical blog in German
• Our company information blog in English
• Our company information blog in German

You can follow FromDual at Twitter, Xing, LinkedIn and Facebook.

FromDual videos you can find at the FromDual TV channel.

Press Releases

• Performance Tuning für MySQL Datenbanken – FromDual wird Partner von HGSt / Virident

Other source

• News about FromDual
• Our quarterly Newsletter
• And our Press Releases in German and English

Hi, I have monitored my MySQL server with FPMMM v0.9 since one month ago, and it work very well. But the FPMMM collecting and sending data 10 seconds per time. I want to adjust the rate from 10s to 30s. But I don’t how to do this on my server. Could you help me please, Thanks a lot in advance.

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they are not available. With Galera v1.x that was not a problem because after a crash a SST would have happened and the corrupted InnoDB block are corrected. But now with IST in Galera v2.0 MySQL will start without noticing the corruption (as usual) and only an IST is performed. This leads to a running MySQL database with possibly corrupted InnoDB blocks. And this might cause you later troubles for example if this node is used as a donor. Then the corrupted page is inherited to other nodes (using rsync or …

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

*** (1) TRANSACTION:

TRANSACTION 22723019234, fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 14 row lock(s)
update location set expires='2012-08-10 04:50:29' where username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2203904 n bits 136 index `GEN_CLUST_INDEX` of table `location` trx id 22723019234 lock_mode X locks rec but not gap waiting


*** (2) TRANSACTION:

TRANSACTION 22723019222, fetching rows, thread declared inside InnoDB 225
mysql tables in use 1, locked 1
192 lock struct(s), heap size 30704, 9483 row lock(s)
delete from location where expires<'2012-08-10 04:49:30' AND expires!='1969-12-31 19:00:00'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no …

PHP Java bytecode

PHP Compiler

// Call with php -f test.php

error_reporting(E_ALL);

// Class (blueprint) = package => object (instantiated class)
class ParentClass {

  // variable = property

  // encapsulation
  // visible anywhere
  public $public       = 'Public';
  // visible only within the class, and in inherited and parent classes
  protected $protected = 'Protected';
  // visible only within class
  private $private     = 'Private';

  // function = methode
  public function printItem($string) {

    // ParentClass
    print get_class($this) . ': ' . $string . PHP_EOL;
    print $this->public . PHP_EOL;
    print $this->protected . PHP_EOL;
    print $this->private . PHP_EOL;
  }

  const CONST_VALUE = 'PHP is great!';

  public function printPHP() {

    $classname = get_class($this);
    print constant($classname . '::CONST_VALUE') . PHP_EOL;
    print $classname::CONST_VALUE . PHP_EOL;
    print ParentClass::CONST_VALUE . PHP_EOL;
  }
}

class ChildClass extends ParentClass { …

Hello Mahesh,

Thanks for the suggestion. I have added MONyog...

Oli

I think MONyog should have been in this list as it offers MySQL monitoring solution through - 200+ Advisors, Query Analysis, Email Notification etc..

There is a product out in the wild which is called SchoonerSQL
[ 1
] from an American company called Schooner Information Technology
[ 2
]. They claim to have top-performance
[ 3
] and high-availability features with up 99.999% availability
[ 1
].
The technology seems to be based on InnoDB, SSD and some kind of replication with failover mechanism.

Unfortunately this product is neither open source nor is it really transparent what they are doing. Which is quite suspicious… Because this lack of transparency not everybody is happy with the product and the company.

Thanks to informations we have received and found in the web we know:

• There were at least SchoonerSQL v3
  [ 4
  ] and SchoonerSQL v5
  [ 5
  ] on the market
• SchoonerSQL v3 could be based on simple MMM technology
  [ 4
  ]
• SchoonerSQL v5 could be based on Galera Cluster
  [ 3
  ]
• SchoonerSQL v5.1 IS based on Galera Cluster
  [ 3
  ]

Unfortunately we do not know much more about this product for the moment. If you have some more technical information we would be …