Invalid datetime when converting to timestamp

Liked? Help and Share!

Hi there.
Today I faced an issue that from first looking at it I was almost sure I`ve found a bug, but putting a bit more research on it, it makes totally sense.

I had a table, that I`ll call here as t1. This table has a field that is datetime. Due to a normalization project that we are working on, we are changing some datetime fields to timestamp. When I tried to convert t1, I got the bellow error:

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from t1;
+---------------------+
| date                |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:28:20' for column 'date' at row 1

This date looked perfectly fine. Then I realized that we use Europe/Dublin as timezone, I went to http://www.timeanddate.com/time/change/ireland?year=2010 to checkout when Daylight Savings started on 2010 in Ireland, and guess what ?!?!?!
Bingo, that is exactly the day DST started on my server timezone. From the above website:

When local standard time was about to reach
Sunday, 28 March 2010, 01:00:00 clocks were turned forward 1 hour to
Sunday, 28 March 2010, 02:00:00 local daylight time instead

Which means 01:28:20 never existed on Europe/Dublin for that specific day. Why the data was there in the first place is another discussion. But why it did not complained before ?
FROM: http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

So, how to fix it ?
Identify the records that have an invalid date and fix it(In my case, fix it means add an hour to it`s time). One way to do it is using the bellow approach:

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
+---------------------+
| date |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> UPDATE t1 SET date=DATE_ADD(date, INTERVAL 1 hour) WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Query OK, 1 row affected (0,10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Empty set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
Query OK, 1 row affected (0,05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;
+---------------------+
| date |
+---------------------+
| 2010-03-28 02:28:20 |
+---------------------+
1 row in set (0,00 sec)

We have identified all records, fixed it and then mysql allowed us to convert it to Timestamp.

That is it for today.

Liked? Help and Share!
Posted in MySQL | 1 Comment

Forcing deadlock rollback victim transaction

Liked? Help and Share!

If you use a storage engine that supports transactions, you probably have faced or heard of deadlock's.

From MySQL Documentation:
“Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.”

At work, we had an important job that sometimes were failing due to dead lock. I wanted to enhance it, so it will do what the documentation says ( Retry the transaction ). In order to do that, I wanted to have a scenario where I was able to reproduce the deadlock and the victim transaction was the one from the job I was fixing.

Create a deadlock is simple, you just need to have 2 sessions that each one holds a lock that the other is waiting for. For example:
We have a table that has 4 entries on it (entry 1, entry 2, entry 3, entry 4) and we have 2 transactions that do the follow in time order:

T1: locks entry 1;
T2: locks entry 4;
T1: require a lock on entry 4 (it will wait until T2 release the lock);
T2: require a lock on entry 1 (it will wait until T1 release the lock);

At this point each transaction will wait for each other and MySQL detects the deadlock. Let’s see it in practice:

T1 > CREATE TABLE t (i INT, PRIMARY KEY(i)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

T1 > INSERT INTO t VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >  UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (9.54 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > UPDATE t SET i = 1 WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

How MySQL choose the victim transaction to be rolled back ?

MySQL does an internal calculation based on transaction weight. It calculates it based on the amount of rows altered and the number of rows locked on the transaction.
This value can be retrieved on trx_weight column from information_schema.innodb_trx table:

T1 > SELECT * FROM  information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 23326
                 trx_state: RUNNING
               trx_started: 2016-02-19 10:10:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 5
                 trx_query: SELECT * FROM  information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

The tip:
One important factor on the above calculation, is if any transaction have made changes to non-transactional tables, it is considered heavier than the ones that haven’t.
So for my test, I’ve create an MyISAM table and I’ve inserted a row on this table to make it “heavier”:

T2 > CREATE TABLE t2 (i INT, PRIMARY KEY(i)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

T1 > UPDATE t SET i = 4 WHERE i = 4;  -- it will wait for T2 lock be released ...

T2 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

As you can see, the victim from rollback was T1.

That is it for today guys. I hope you enjoy it.

Liked? Help and Share!
Posted in InnoDB, MySQL | 4 Comments

MySQL 5.7 auto-generated root password

Liked? Help and Share!

Hi There.

You may have noticed something different when you install a fresh copy of MySQL 5.7 either via yum or using binary source. The root password is auto-generated.
Ok and where can I find it ?

Centos
cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
Example:

[root@master ~]# cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
a3BGf#TY.pBj

Binary distribution
Once you initialization the datadir mysqld --initialize you will see the bellow log printed on screen:

. . .
2016-01-13T21:05:03.070322Z 1 [Note] A temporary password is generated for root@localhost: vL8n>Hs%kr>s
. . .

You will be required to change the password on the first login. You can do it following step 2 of this article.

That’s it.
Now you guys know how to find the auto generated root password for MySQL 5.7.

See you next time.

Liked? Help and Share!
Posted in install, MySQL, password, Security | 2 Comments

Adding your own collation to MySQL

Liked? Help and Share!

I’m the kind of DBA that prefers to keep everything simple, BUT, sometimes it’s not possible. Few days ago I’ve faced an issue where none of the collations shipped by default with MySQL would guarantee integrity of my database, and to avoid a massive re-write of application code, we have explored an option that up to the day, I didn’t know about.
Add your own collation to MySQL.

This option is described on this section of MySQL documentation . On this post I will show how to make MySQL identify volves with acute accent (fada) as a different letter:

First, let’s find out where is our character-set folder:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'character_sets_dir';
+--------------------+---------------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------------+
| character_sets_dir | /mysql/sources/5.6.26/share/charsets/ |
+--------------------+---------------------------------------+
1 row in set (0.00 sec)

In this case, I’ll be working on /mysql/sources/5.6.26/share/charsets/ it may be different on your environment.
You will find a file named Index.xml inside your character_sets_dir.

On this file, we will be using the locate data markup language syntax (LDML), more specifically reset, p and t elements.
You can have more information about LDML and the complete list of elements LDML Syntax Supported in MySQL.

Let’s edit this file and add our collation inside :

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
 . . .
  <collation name="utf8_test_ci" id="1122" version="5.2.0">
    <rules>
      <reset>A</reset>
      <p>\u00c1</p>
      <t>\u00e1</t>
      <reset>E</reset>
      <p>\u00c9</p>
      <t>\u00e9</t>
      <reset>I</reset>
      <p>\u00cd</p>
      <t>\u00ed</t>
      <reset>O</reset>
      <p>\u00d3</p>
      <t>\u00f3</t>
      <reset>U</reset>
      <p>\u00da</p>
      <t>\u00fa</t>
    </rules>
  </collation>
</charset>

So, let’s explain the first rule:

<reset>A</reset>
<p>\u00c1</p>
<t>\u00e1</t>
  • reset – is reseting the ordering an rules for the A (and a) character
  • p – is saying that \u00c1 (Á) will be treated as a different letter
  • t – is saying that \u00e1 (á) will be treated as the lower case version of \u00c1 (Á)

The same applies for the other letters (E / I / O / U). Now all we need is to restart MySQL service.

Let’s do some testing to find out how it works, I’ll create 2 tables one with utf8 default collation and 1 with our test collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8` ( `name` varchar(50), PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('Á');
ERROR 1062 (23000): Duplicate entry 'Á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8;
+------+
| name |
+------+
| A |
+------+
1 row in set (0.00 sec)

MySQL only allowed 1 variation of the letter A, all subsequent failed due to duplication of the PRIMARY KEY , let’s see how it looks on our test collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8_test` ( `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_test_ci , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('Á');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8_test;
+------+
| name |
+------+
| A |
| Á |
+------+
2 rows in set (0.00 sec)

In this case, MySQL allowed 2 entries, A and Á, it blocked a because it’s a duplicate of A and it blocked á because it’s a duplicate of Á.

That is it, I hope it will be of any usage for you.

Liked? Help and Share!
Posted in Charset, Collation, MySQL | Leave a comment

MySQL & NoSQL – Memcached Plugin

Liked? Help and Share!

Many of you have already heard about NoSQL databases and one of the the most used tool is Memcached, where you add a cache layer between the application and database. Since MySQL version 5.6, a new plugin is available to do the integration between MySQL and Memcached. On this article, we will learn how to install it on linux, and some basic configurations of it.

Pre-requirements:
Install libevent

Installation:
To install memcached support we will need to create a few tables responsible for MySQL and memcached integration. MySQL already includes the file which creates those tables (innodb_memcached_config.sql), you can find this file in a sub folder of your basedir. To discover where is your basedir, run the bellow command:

mysql> SHOW VARIABLES LIKE 'basedir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
1 row in set (0.00 sec)

If you have installed MySQL via your distro repository, the path will be as follow:

$basedir/share/mysql/innodb_memcached_config.sql

In case you have used MySQL binaries, the path will be as follow:

$basedir/share/innodb_memcached_config.sql

Now we will run this .sql file. By default, the script creates a test table on test database, but on our tests we will use the memcached db:

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql>

Now let’s create our table to store memcached data:

mysql> CREATE DATABASE IF NOT EXISTS memcached;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE memcached.dados LIKE test.demo_test;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE innodb_memcache.containers SET db_schema = 'memcached', db_table = 'dados' WHERE name = 'aaa' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DROP DATABASE test;
Query OK, 1 row affected (0.07 sec)

Next step is to install memcached plugin on MySQL. To do it we will use INSTALL PLUGIN command:

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.01 sec)

To verify if the plugin was successful installed, we can run the bellow command:

mysql> \! netstat -tunap | grep LIST | grep mysql
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      2032/mysqld         
tcp        0      0 :::11211                    :::*                        LISTEN      2032/mysqld         
tcp        0      0 :::3306                     :::*                        LISTEN      2032/mysqld         
mysql> 

Configuring and Using:

Now we will test memcached via some programming language, PHP in this case:

 [root@localhost memcache]# cat test1.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('key1', 'Testing memcached');
echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>
[root@localhost memcache]# php test1.php 
Value of key1 is:Testing memcached
[root@localhost memcache]# 

Now, let’s see what is stored on MySQL?

mysql> SELECT * FROM memcached.dados;
+------+-------------------+------+------+------+
| c1   | c2                | c3   | c4   | c5   |
+------+-------------------+------+------+------+
| key1 | Testing memcached |    0 |   19 |    0 |
+------+-------------------+------+------+------+
1 row in set (0.00 sec)

mysql> 

What happens if we change an entry manually on MySQL?

mysql> UPDATE memcached.dados SET c2 = 'Entry modified  directly on MySQL';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


[root@localhost memcache]# cat test2.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test2.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 


What about if we want to store the entries on a different MySQL table?
We just need to create a new table, add a new container and use the delimiter which is configured on config_options table from innodb_memcache database:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'table_map_delimiter';
+---------------------+-------+
| name                | value |
+---------------------+-------+
| table_map_delimiter | .     |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE memcached.dados2 LIKE memcached.dados;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('bbb', 'memcached', 'dados2', 'c1', 'c2', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.01 sec)

We have created a new table named dados2 and added a new container to refer to that table, which we named as bbb, now we just need to use that as prefix on memcached:

 [root@localhost memcache]# cat test3.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('@@bbb.key1', 'Should be stored on dados2 table');
echo 'Value of bbb.key1 is:' . $m->get('@@bbb.key1') . "\n";
?>
[root@localhost memcache]# php test3.php 
Value of bbb.key1 is:Should be stored on dados2 table
[root@localhost memcache]# 


mysql> SELECT * FROM memcached.dados2;
+------+----------------------------------+------+------+------+
| c1   | c2                               | c3   | c4   | c5   |
+------+----------------------------------+------+------+------+
| key1 | Should be stored on dados2 table |    0 |   22 |    0 |
+------+----------------------------------+------+------+------+
1 row in set (0.00 sec)

We also can map the table to store values into separate fields on the table.
Verify that on the config_options table we already have a separator configured:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'separator';
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+
1 row in set (0.00 sec)

This character we will use to store values into multiple columns. Let’s create a table and add it to a new container(we will specify the columns we want the values separated by comma ‘,’):

mysql> CREATE TABLE products (id varchar(128), name varchar(255), value varchar(15), c3 int, c4 bigint, c5 int, PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('products', 'memcached', 'products', 'id', 'name,value', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.00 sec)

Now, lets create an array of products and add them to memcached:

[root@localhost memcache]# cat test4.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$products = array(
array('1', 'TV', '1999,00'),
array('2', 'Hack', '399,00'),
array('3', 'Table', '599,00'),
array('4', 'Chair', '99,00')
);

foreach($products as $product)
{
	$key = '@@products.' . $product[0];
	$value = $product[1] . '|' . $product[2];
	$m->set($key, $value);
}

?>
[root@localhost memcache]# php test4.php 



mysql> SELECT * FROM memcached.products;
+----+----------+---------+------+------+------+
| id | nome     | valor   | c3   | c4   | c5   |
+----+----------+---------+------+------+------+
| 1  | TV       | 1999,00 |    0 |   23 |    0 |
| 2  | Hack     | 399,00  |    0 |   24 |    0 |
| 3  | Table    | 599,00  |    0 |   25 |    0 |
| 4  | Chair    | 99,00   |    0 |   26 |    0 |
+----+----------+---------+------+------+------+
4 rows in set (0.00 sec)

Server/Service restart:

Let’s see what happens if we have to restart MySQL service(Same apply to server reboot)?
Will the data remain available on memcached after it?

[root@localhost memcache]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost memcache]# cat test5.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test5.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 

That is it! Entries will remain available even after reboot’s/restart’s.

SELinux:

On environments where we have SELinux enabled, it can block memcached integration because MySQL is not allowed to listen on memcached port, where is a tip on how to allow it(I’m using CentOS as linux distro):

Search for entries containing mysqld and dinied keywords on /var/log/audit/audit.log , if you find any, type the bellow commands to create a new SELinux module to allow it:

type=AVC msg=audit(1421080542.433:31): avc:  denied  { name_bind } for  pid=1360 comm="mysqld" src=11211 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:memcache_port_t:s0 tclass=tcp_socket
type=SYSCALL msg=audit(1421080542.433:31): arch=c000003e syscall=49 success=no exit=-13 a0=2d a1=7f5fcc0409a0 a2=10 a3=7f5fe0ee275c items=0 ppid=1123 pid=1360 auid=0 uid=27 gid=27 euid=27 suid=27 fsuid=27 egid=27 sgid=27 fsgid=27 tty=pts0 ses=2 comm="mysqld" exe="/usr/sbin/mysqld" subj=unconfined_u:system_r:mysqld_t:s0 key=(null)


audit2why < /var/log/audit/audit.log

cd /root/
mkdir selinux-custom
cd selinux-custom
audit2allow -a -M mysql-memcache
semodule -i mysql-memcache.pp

Opcoes do memcached: In case you want to modify any memcached specific option, you can add the daemon_memcached_option to MySQL config file, for example, to change memcached port:

#no arquivo de configuração (normalmente my.cnf)
daemon_memcached_option="-p11222"

That is it, I hope you’ve learned how to install and configure MySQL and memcached plugin integration.

Liked? Help and Share!
Posted in InnoDB, install, MySQL, optimize, performance | Leave a comment

MySQL Backup Strategy – Slides

Liked? Help and Share!

Hi there, it’s been a few months since Percona Live London has finished, but I managed just now to get sometime to post the slides from my presentation about backups. Please, take a look and feel free to comment.

Pdf Version


Liked? Help and Share!
Posted in backup, MySQL, Security | Leave a comment

MySQL Fabric – Part 1 – Installing

Liked? Help and Share!

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances.
It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a high available system.

For More information about what is MySQL Fabric, please follow the documentation.

To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial:

fabric1 (192.168.0.200) - fabric
mysql1 (192.168.0.201) - mysql master
mysql2 (192.168.0.202) - mysql slave
mysql3 (192.168.0.203) - mysql slave

Note: I’m running CentOS 6.5 on all servers.

1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Install mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. On mysql1,mysql2,mysql3 add the follow to my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. On mysql1 add a replication user for each mysql ip:


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. On mysql1 add the privileges to fabric user from fabric node ip:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. On mysql2 and mysql3, setup replication:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. On fabric1, add the fabric MySQL user:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. On fabric1,, configure user and password on [storage] and [servers] group on /etc/mysql/fabric.cfg :


[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. On fabric1, start the fabric db, it will ask to create a password, this password will be used on all next mysqlfabric commands:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. On fabric1, start fabric:


mysqlfabric manage start &

11. On fabric1,, add a group:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. On fabric1,, add mysql1, mysql2 and mysql3 to GLOBAL1 group:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. On fabric1, get the uuid of your master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. On fabric1, add your master as master on GLOBAL1 group:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

That is it, we now have our MySQL Fabric environment working and ready.
Watch out for my next few posts to learn more about MySQL Fabric.

Liked? Help and Share!
Posted in install, MySQL, performance, replication | 2 Comments

MySQL Sandbox

Liked? Help and Share!

Hi there, today we will learn about an amazing tool that every single MySQL dba must know, I’m talking about MySQL Sandbox.

MySQL Sandbox is developed by Giuseppe Maxia (The Data Charmer), it’s a tool that make the installation of MySQL servers very easy. If you need to quickly create a MySQL instance for test or a replication setup(it supports master slave, circular and master master replication), this it the tool.

INSTALLATION:

Go to http://mysqlsandbox.net/ and get the latest version (I got from launchpad):

yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple
wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz
tar -zxvf MySQL-Sandbox-3.0.44.tar.gz
cd MySQL-Sandbox-3.0.44
perl Makefile.PL
make
make test
make install

CREATING A SINGLE SANDBOX:

To create a single sandbox, all you need is the mysql package that you want install and the make_sandbox command:

[root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz
Executing low_level_make_sandbox --basedir=/root/5.6.17 \
	--sandbox_directory=msb_5_6_17 \
	--install_version=5.6 \
	--sandbox_port=5617 \
	--no_ver_after_name \
	--my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.44
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.6
basedir                        = /root/5.6.17
tmpdir                         = 
my_file                        = 
operating_system_user          = root
db_user                        = msandbox
remote_access                  = 127.%
bind_address                   = 127.0.0.1
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n) 
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

To use it you can call the use script inside the sandbox folder:

[root@localhost ~]# $HOME/sandboxes/msb_5_6_17/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 

You can manage your sandbox by calling the start / stop / restart / status script inside the sandbox folder

CREATING A MASTER SLAVE REPLICATION:

To create a master slave replication topology (by default is set to 1 master and 2 slaves but it can be changed passing the --how_many_nodes parameter) we will use the make_replication_sandbox command:

[root@localhost ~]# make_replication_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17

To use it we can call the use script, for replication, the use script will be located inside the nodeN/Master folder:

[root@localhost ~]# #MASTER
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/master/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 1
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node1/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 2
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node2/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) > 

On replication sandbox, you can manage the individual sandbox by calling the start / stop / restart / status script inside the node / master folder or you can call the scripts ending with _all located on the sandbox folder (start_all / stop_all / restart_all / status_all).

CREATING A MULTI MASTER REPLICATION:

To create a multi-master sandbox we will use the make_replication_sandbox with --master_master option:

[root@localhost ~]# make_replication_sandbox --master_master mysql-5.6.17-linux-glibc2.5-i686.tar.gz
installing node 1
installing node 2
# server: 1: 
# server: 2: 
# server: 1: 
# server: 2: 
Circular replication activated
group directory installed in $HOME/sandboxes/rcsandbox_mysql-5_6_17

That is it, you can find more information using the help parameter on make_multiple_custom_sandbox make_multiple_sandbox make_replication_sandbox make_sandbox make_sandbox_from_installed and make_sandbox_from_source

Liked? Help and Share!
Posted in install, MySQL, replication | 1 Comment

Warm-up InnoDB Buffer Pool

Liked? Help and Share!

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Now, the next time we stop MySQL, a file named ib_buffer_pool will be created under the MySQL datadir (you can change the file name by using the innodb_buffer_pool_filename config).

Next step is to tell MySQL to read the this file and load the actual page data into the buffer pool, we will do it by adding a new entry named innodb_buffer_pool_load_at_startup on my.cnf (and also add innodb_buffer_pool_dump_at_shutdown to it, this way it will automatically dump it every time we shutdown the server):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Now we can restart our mysql server, as mention before, it will create a file with a content similar to the above:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

When we restart our server, we can see 2 new messages being written to MySQL logs:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

This means that MySQL have wrote the file with the buffer pool pages.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

This means that MySQL have finished to reload the pages content back to the InnoDB buffer pool.

You can controller the dump / reload progress through the Innodb_buffer_pool_dump_status and Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

If you wish, you can tell MySQL to dump/reload the buffer pool right now using innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.

If you use InnoDB as storage engine of any of you tables, that is a very cool practice to put in place to minimize the side effects of a server/service restart.

Liked? Help and Share!
Posted in InnoDB, MySQL, optimize, performance | 6 Comments

Installing latest version of MySQL via yum

Liked? Help and Share!

Usually when you try to install MySQL via yum, by default, the version that comes with your OS is always an old version (not the latest version available from mysql.com/downloads).
In order to have the latest version, you always needed to go to mysql.com/downloads and manually download and install MySQL packages right? Not anymore, MySQL recently released the MySQL YUM Repository.
Basically, it will allows you to install the latest GA version of MySQL Community Server, Workbench and Connector/ODBC.

HOW TO INSTALL?
You can basically install it in 2 ways:

1. Using the RPM package
Download the .rpm from dev.mysql.com/downloads/repo/ and install it:

wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/
sudo yum localinstall -y mysql-community-release-el6-3.noarch.rpm

2. Manually adding a file under /etc/yum.repos.d/ folder
Add the bellow content to a new file named mysql-community.repo :

[mysql-community]
name=MySQL Community Server 
baseurl=URL
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Replace baseurl=URL according to your OS:
EL6 (Centos6 / Redhat 6):

baseurl=http://repo.mysql.com/yum/mysql-community/el/6/$basearch/

Fedore 18 and 19:

baseurl=http://repo.mysql.com/yum/mysql-community/fc/$releasever/$basearch/

Note that we have gpgcheck=1, if we use it as 1 in this install method, we will need to add the GnuPG key (see http://dev.mysql.com/doc/refman/5.6/en/checking-gpg-signature.html for more details).

INSTALLING PACKAGES:
Now that we have our repo enabled, it’s just a normal yum command to install the latest version of MySQL Community Server:

yum install mysql-server

LISTING AVAILABLE PACKAGES:
To list the packages available in the yum repo, use the bellow command:

sudo yum --disablerepo=\* --enablerepo=mysql-community list available
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
mysql-community-client.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.i686                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.i686                                                                                                                                        5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.x86_64                                                                                                                                      5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.i686                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.x86_64                                                                                                                                   5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.i686                                                                                                                               5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.x86_64                                                                                                                             5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.i686                                                                                                                                         5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.i686                                                                                                                                  5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.x86_64                                                                                                                                5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-server.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-test.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-connector-odbc.x86_64                                                                                                                                       5.2.6-1.el6                                                                                                                               mysql-community
mysql-workbench-community.x86_64                                                                                                                                  6.0.7-4.el6                                                                                                                               mysql-community

Remember, before change anything on a production server, test, test, test first :)

Liked? Help and Share!
Posted in install, MySQL | 1 Comment