Friday, December 26, 2014

Find/parse a string from within a string

So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.

For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.

CREATE TABLE `parse_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urldemo` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
+----+----------------------------+
| id | urldemo                    |
+----+----------------------------+
|  1 | http://www.mysql.com/      |
|  2 | http://www.percona.com/    |
|  3 | https://tools.percona.com/ |
|  4 | https://mariadb.com/       |
|  5 | http://planet.mysql.com/   |
|  6 | http://dev.mysql.com/doc/  |
+----+----------------------------+


The goal for this example is to disregard the http:// or https:// and anything after the .com. So we use LOCATE to find the locations.

The .com reference is easy since that is constant so we can start with that.

SELECT LOCATE('.com', urldemo), urldemo FROM parse_example;
+-------------------------+----------------------------+
| LOCATE('.com', urldemo) | urldemo                    |
+-------------------------+----------------------------+
|                      17 | http://www.mysql.com/      |
|                      19 | http://www.percona.com/    |
|                      22 | https://tools.percona.com/ |
|                      16 | https://mariadb.com/       |
|                      20 | http://planet.mysql.com/   |
|                      17 | http://dev.mysql.com/doc/  |
+-------------------------+----------------------------+


OK so we want to remove the / , what location is that?

SELECT LOCATE('.com', urldemo) as start, LOCATE('.com', urldemo) +4 as end, SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )  AS resulting , urldemo FROM parse_example;
+-------+-----+-----------+----------------------------+
| start | end | resulting | urldemo                    |
+-------+-----+-----------+----------------------------+
|    17 |  21 | /         | http://www.mysql.com/      |
|    19 |  23 | /         | http://www.percona.com/    |
|    22 |  26 | /         | https://tools.percona.com/ |
|    16 |  20 | /         | https://mariadb.com/       |
|    20 |  24 | /         | http://planet.mysql.com/   |
|    17 |  21 | /doc/     | http://dev.mysql.com/doc/  |
+-------+-----+-----------+----------------------------+

This gives us our end position, I only put the field aliases in to make the results easier to follow.

Now sorting out after http and https is actually very easy as well as they both have :// after them, so we just need the location of the second / in the string.


SELECT LOCATE('/', urldemo) as first, LOCATE('/', urldemo) +1 as second, urldemo
FROM parse_example;
+-------+--------+----------------------------+
| first | second | urldemo                    |
+-------+--------+----------------------------+
|     6 |      7 | http://www.mysql.com/      |
|     6 |      7 | http://www.percona.com/    |
|     7 |      8 | https://tools.percona.com/ |
|     7 |      8 | https://mariadb.com/       |
|     6 |      7 | http://planet.mysql.com/   |
|     6 |      7 | http://dev.mysql.com/doc/  |
+-------+--------+----------------------------+


These queries are just showing what the different aspects of the final query will be doing. So let us put it all together.


SELECT
TRIM(TRAILING SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )
FROM SUBSTRING(urldemo FROM LOCATE('/', urldemo) + 2 ) )  AS parsed_domain ,
urldemo as original_url
FROM parse_example;
+-------------------+----------------------------+
| parsed_domain     | original_url               |
+-------------------+----------------------------+
| www.mysql.com     | http://www.mysql.com/      |
| www.percona.com   | http://www.percona.com/    |
| tools.percona.com | https://tools.percona.com/ |
| mariadb.com       | https://mariadb.com/       |
| planet.mysql.com  | http://planet.mysql.com/   |
| dev.mysql.com     | http://dev.mysql.com/doc/  |
+-------------------+----------------------------+


Now hopefully that helps you be able to parse out whatever you need. This example is limited to a url. But since some examples of functions already here is my example of a function that you can use to parse whatever you need.



CREATE FUNCTION PARSE_STRING(delimiterA VARCHAR(50), delimiterB VARCHAR(50),  passed_string VARCHAR(255) )
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
TRIM(TRAILING SUBSTRING(passed_string FROM LOCATE(delimiterB, passed_string)  )
FROM SUBSTRING(passed_string FROM LOCATE(delimiterA, passed_string) + CHAR_LENGTH(delimiterA) ) ) ;

SELECT PARSE_STRING('//','.com', urldemo) FROM parse_example;
+------------------------------------+
| PARSE_STRING('//','.com', urldemo) |
+------------------------------------+
| www.mysql                          |
| www.percona                        |
| tools.percona                      |
| mariadb                            |
| planet.mysql                       |
| dev.mysql                          |
+------------------------------------+


Pull a last name from a full name field:

SELECT PARSE_STRING('John ','', 'John Smith') ;
+----------------------------------------+
| PARSE_STRING('John ','', 'John Smith') |
+----------------------------------------+
| Smith                                  |
+----------------------------------------+


Pull the first name

SELECT PARSE_STRING('',' Smith', 'John Smith') ;
+-----------------------------------------+
| PARSE_STRING('',' Smith', 'John Smith') |
+-----------------------------------------+
| John                                    |
+-----------------------------------------+


Granted with the name examples you would need to know the delimiter values. But this is just an example you can build on.

Thursday, December 18, 2014

A MySQL PARTITION and SUBPARTITION Example

So this is just a simple example of how to set up a PARTITION and a SUBPARTITION in MySQL. The concept here is that you have data in a table with numerous values in a datetime field. You might have data that is spread across numerous years  (most likely you do). So one way to partition this data is to sort it by year but then also sort it by month within that yearly partition.

Below is an example that you can use for consideration.

Consider the test table. Your table with have many more fields of course.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


First I will populate the test table with random values for the  date_time field.

delimiter //
CREATE PROCEDURE populate_t1( IN rowsofdata INT )
BEGIN

SET @A = 1;
SET @B = 25 - @A;

  WHILE rowsofdata > 0 DO
    SELECT  FLOOR( @A + (RAND() * @B )) INTO @randvalue;
    INSERT INTO t1
    SELECT NULL, NOW() - INTERVAL @randvalue MONTH;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_t1(1000);


Check to see what kind of values I ended up with:

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2012-01-01 00:00:00' AND '2013-01-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 43
1 row in set (0.00 sec)

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2013-01-01 00:00:00' AND '2014-01-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 529
1 row in set (0.00 sec)

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2014-01-01 00:00:00' AND NOW() \G
*************************** 1. row ***************************
COUNT(*): 428
1 row in set (0.00 sec)


Now I can alter the table so I can add my partitions and then test the values counts via the partition.

ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`date_time`), LOCK=SHARED;
ALTER TABLE t1
 PARTITION BY RANGE( YEAR(date_time) )
    SUBPARTITION BY HASH(MONTH(date_time) ) (

        PARTITION p2012 VALUES LESS THAN (2013) (
            SUBPARTITION dec_2012,
            SUBPARTITION jan_2012,
            SUBPARTITION feb_2012,
            SUBPARTITION mar_2012,
            SUBPARTITION apr_2012,
            SUBPARTITION may_2012,
            SUBPARTITION jun_2012,
            SUBPARTITION jul_2012,
            SUBPARTITION aug_2012,
            SUBPARTITION sep_2012,
            SUBPARTITION oct_2012,
            SUBPARTITION nov_2012
        ),

        PARTITION p2013 VALUES LESS THAN (2014) (
            SUBPARTITION dec_2013,
            SUBPARTITION jan_2013,
            SUBPARTITION feb_2013,
            SUBPARTITION mar_2013,
            SUBPARTITION apr_2013,
            SUBPARTITION may_2013,
            SUBPARTITION jun_2013,
            SUBPARTITION jul_2013,
            SUBPARTITION aug_2013,
            SUBPARTITION sep_2013,
            SUBPARTITION oct_2013,
            SUBPARTITION nov_2013

        ),
        PARTITION p2014 VALUES LESS THAN (2015) (
            SUBPARTITION dec_2014,
            SUBPARTITION jan_2014,
            SUBPARTITION feb_2014,
            SUBPARTITION mar_2014,
            SUBPARTITION apr_2014,
            SUBPARTITION may_2014,
            SUBPARTITION jun_2014,
            SUBPARTITION jul_2014,
            SUBPARTITION aug_2014,
            SUBPARTITION sep_2014,
            SUBPARTITION oct_2014,
            SUBPARTITION nov_2014
        ),

        PARTITION pmax VALUES LESS THAN MAXVALUE (
            SUBPARTITION dec_max,
            SUBPARTITION jan_max,
            SUBPARTITION feb_max,
            SUBPARTITION mar_max,
            SUBPARTITION apr_max,
            SUBPARTITION may_max,
            SUBPARTITION jun_max,
            SUBPARTITION jul_max,
            SUBPARTITION aug_max,
            SUBPARTITION sep_max,
            SUBPARTITION oct_max,
            SUBPARTITION nov_max
        )
 );


My Show create table is very different now.

> show create table t1;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(date_time))
SUBPARTITION BY HASH (MONTH(date_time))
(PARTITION p2012 VALUES LESS THAN (2013)
 (SUBPARTITION dec_2012 ENGINE = InnoDB,
  SUBPARTITION jan_2012 ENGINE = InnoDB,
  SUBPARTITION feb_2012 ENGINE = InnoDB,
  SUBPARTITION mar_2012 ENGINE = InnoDB,
  SUBPARTITION apr_2012 ENGINE = InnoDB,
  SUBPARTITION may_2012 ENGINE = InnoDB,
  SUBPARTITION jun_2012 ENGINE = InnoDB,
  SUBPARTITION jul_2012 ENGINE = InnoDB,
  SUBPARTITION aug_2012 ENGINE = InnoDB,
  SUBPARTITION sep_2012 ENGINE = InnoDB,
  SUBPARTITION oct_2012 ENGINE = InnoDB,
  SUBPARTITION nov_2012 ENGINE = InnoDB),
 PARTITION p2013 VALUES LESS THAN (2014)
 (SUBPARTITION dec_2013 ENGINE = InnoDB,
  SUBPARTITION jan_2013 ENGINE = InnoDB,
  SUBPARTITION feb_2013 ENGINE = InnoDB,
  SUBPARTITION mar_2013 ENGINE = InnoDB,
  SUBPARTITION apr_2013 ENGINE = InnoDB,
  SUBPARTITION may_2013 ENGINE = InnoDB,
  SUBPARTITION jun_2013 ENGINE = InnoDB,
  SUBPARTITION jul_2013 ENGINE = InnoDB,
  SUBPARTITION aug_2013 ENGINE = InnoDB,
  SUBPARTITION sep_2013 ENGINE = InnoDB,
  SUBPARTITION oct_2013 ENGINE = InnoDB,
  SUBPARTITION nov_2013 ENGINE = InnoDB),
 PARTITION p2014 VALUES LESS THAN (2015)
 (SUBPARTITION dec_2014 ENGINE = InnoDB,
  SUBPARTITION jan_2014 ENGINE = InnoDB,
  SUBPARTITION feb_2014 ENGINE = InnoDB,
  SUBPARTITION mar_2014 ENGINE = InnoDB,
  SUBPARTITION apr_2014 ENGINE = InnoDB,
  SUBPARTITION may_2014 ENGINE = InnoDB,
  SUBPARTITION jun_2014 ENGINE = InnoDB,
  SUBPARTITION jul_2014 ENGINE = InnoDB,
  SUBPARTITION aug_2014 ENGINE = InnoDB,
  SUBPARTITION sep_2014 ENGINE = InnoDB,
  SUBPARTITION oct_2014 ENGINE = InnoDB,
  SUBPARTITION nov_2014 ENGINE = InnoDB),
 PARTITION pmax VALUES LESS THAN MAXVALUE
 (SUBPARTITION dec_max ENGINE = InnoDB,
  SUBPARTITION jan_max ENGINE = InnoDB,
  SUBPARTITION feb_max ENGINE = InnoDB,
  SUBPARTITION mar_max ENGINE = InnoDB,
  SUBPARTITION apr_max ENGINE = InnoDB,
  SUBPARTITION may_max ENGINE = InnoDB,
  SUBPARTITION jun_max ENGINE = InnoDB,
  SUBPARTITION jul_max ENGINE = InnoDB,
  SUBPARTITION aug_max ENGINE = InnoDB,
  SUBPARTITION sep_max ENGINE = InnoDB,
  SUBPARTITION oct_max ENGINE = InnoDB,
  SUBPARTITION nov_max ENGINE = InnoDB))


So can we still count our value as expected?

> SELECT count(*) FROM t1 PARTITION (p2012) \G
*************************** 1. row ***************************
count(*): 43
> SELECT count(*) FROM t1 PARTITION (p2013) \G
*************************** 1. row ***************************
count(*): 529
> SELECT count(*) FROM t1 PARTITION (p2014) \G
*************************** 1. row ***************************
count(*): 428


So far so good, all the values matched up to the count we had before. So we can also count or select per the subpartition.


> SELECT * FROM t1 PARTITION (dec_2012) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  59 | 2012-12-19 00:59:57 |
|  68 | 2012-12-19 00:59:58 |
|  93 | 2012-12-19 00:59:59 |
| 105 | 2012-12-19 00:59:59 |
| 111 | 2012-12-19 00:59:59 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jan_2013) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|   6 | 2013-01-19 00:59:55 |
|  29 | 2013-01-19 00:59:56 |
|  55 | 2013-01-19 00:59:57 |
|  79 | 2013-01-19 00:59:58 |
| 100 | 2013-01-19 00:59:59 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jan_2014) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  16 | 2014-01-19 00:59:55 |
| 190 | 2014-01-19 01:00:04 |
| 191 | 2014-01-19 01:00:04 |
| 229 | 2014-01-19 01:00:05 |
| 234 | 2014-01-19 01:00:06 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jun_2014) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  13 | 2014-06-19 00:59:55 |
| 189 | 2014-06-19 01:00:04 |
| 221 | 2014-06-19 01:00:05 |
| 222 | 2014-06-19 01:00:05 |
| 238 | 2014-06-19 01:00:06 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (dec_2013) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  50 | 2013-12-19 00:59:57 |
|  74 | 2013-12-19 00:59:58 |
|  98 | 2013-12-19 00:59:59 |
| 107 | 2013-12-19 00:59:59 |
| 167 | 2013-12-19 01:00:02 |
+-----+---------------------+


This is great and handy but what happens when the year 2015 or  2016 comes around? All of that data would be in the pmax partition. So how do we add a new partition in between p2014 and pmax?

If you had no data in pmax you could drop it and add a new partition onto the end. But it is just as easy to reorganize the partition. This will take the pmax partition and alter it into our new partitions.


ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
     PARTITION p2015 VALUES LESS THAN (2016) (
            SUBPARTITION dec_2015,
            SUBPARTITION jan_2015,
            SUBPARTITION feb_2015,
            SUBPARTITION mar_2015,
            SUBPARTITION apr_2015,
            SUBPARTITION may_2015,
            SUBPARTITION jun_2015,
            SUBPARTITION jul_2015,
            SUBPARTITION aug_2015,
            SUBPARTITION sep_2015,
            SUBPARTITION oct_2015,
            SUBPARTITION nov_2015
        ),
       PARTITION pmax VALUES LESS THAN MAXVALUE (
            SUBPARTITION dec_max,
            SUBPARTITION jan_max,
            SUBPARTITION feb_max,
            SUBPARTITION mar_max,
            SUBPARTITION apr_max,
            SUBPARTITION may_max,
            SUBPARTITION jun_max,
            SUBPARTITION jul_max,
            SUBPARTITION aug_max,
            SUBPARTITION sep_max,
            SUBPARTITION oct_max,
            SUBPARTITION nov_max
        )
);


Hope this helps, best of luck. 

Thursday, November 27, 2014

Recover Lost MySQL data with mysqlbinlog point-in-time-recovery example

Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | /var/lib/mysql/binlogs/mysql-binlogs       |
| log_bin_index                   | /var/lib/mysql/binlogs/mysql-binlogs.index |

show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+


So this is just a simple example using mysqlbinlog to recover data from a binlog and apply it back to the database.

First we need something to loose. If something was to happen to our database we need to be able to recover the data or maybe it is just a way to recover from someones mistake.


CREATE TABLE `table_w_rdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 

We can pretend here and assume that we have developers/DBAs that are not communicating very well and/or saving copies of their code.


delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_dummydata(50);

> SELECT NOW() \G
*************************** 1. row ***************************
NOW(): 2014-11-27 17:32:25
1 row in set (0.00 sec)

> SELECT  * from table_w_rdata  WHERE id > 45;
+----+----------------------------+------------------+
| id | somedata                   | moredata         |
+----+----------------------------+------------------+
| 46 | b204e9800998ecf8427e       | 0998ecf8427e     |
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e        |
| 48 | b204e9800998ecf8427e       | 800998ecf8427e   |
| 49 | 98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 50 | 98f00b204e9800998ecf8427e  | 998ecf8427e      |
+----+----------------------------+------------------+

While one procedure is created it is later written over by someone else incorrectly. 

DROP PROCEDURE IF EXISTS populate_dummydata ;
delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;

call populate_dummydata(50);
> SELECT NOW(); SELECT  * from table_w_rdata  WHERE id > 95;
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-27 17:36:28 |
+---------------------+
1 row in set (0.00 sec)

+-----+-------------------+---------------------+
| id  | somedata          | moredata            |
+-----+-------------------+---------------------+
|  96 | 4e9800998ecf8427e | 00998ecf8427e       |
|  97 | 9800998ecf8427e   | 800998ecf8427e      |
|  98 | e9800998ecf8427e  | 204e9800998ecf8427e |
|  99 | e9800998ecf8427e  | 4e9800998ecf8427e   |
| 100 | 9800998ecf8427e   | 04e9800998ecf8427e  |
+-----+-------------------+---------------------+


The replaced version of the procedure is not generating  random values like the team wanted. The original creator of the procedure just quit from frustration. So what to do? A little time has past since it was created as well. We do know the database name, routine name and the general time frame when the incorrect procedure was created and lucky for us the bin logs are still around, so we can go get it.

We have to take a general look around since we just want a point-in-time-recovery of this procedure.We happen to find the procedure and the position in the binlog before and after it.


NOW(): 2014-11-27 19:46:17
# mysqlbinlog  --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001  | more

 at 253053
 at 253564

# mysql anothermysqldba --login-path=local  -e "DROP PROCEDURE populate_dummydata";
# mysqlbinlog  --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql --login-path=local  anothermysqldba


> SHOW CREATE PROCEDURE populate_dummydata\G
*************************** 1. row ***************************
           Procedure: populate_dummydata
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

NOW(): 2014-11-27 19:51:03
> call populate_dummydata(50);
> SELECT  * from table_w_rdata  WHERE id > 145;
+-----+-----------------------------+------------------+
| id  | somedata                    | moredata         |
+-----+-----------------------------+------------------+
| 146 | 98f00b204e9800998ecf8427e   | 800998ecf8427e   |
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e   |
| 148 | 204e9800998ecf8427e         | 98ecf8427e       |
| 149 | d98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 150 | 204e9800998ecf8427e         | 9800998ecf8427e  |
+-----+-----------------------------+------------------+


We recovered our procedure from the binary log via point-in-time-recovery.
This is a simple example but it is an example of the tools you can use moving forward.

This is why the binlogs are so valuable.

Helpful URL:

Tuesday, November 11, 2014

systemctl and MySQL

So some users complete a yum install of MySQL and expect to be able to use the following  command to start the MySQL server::  /etc/init.d/mysql start only to get "No such file or directory"

So this is a quick post to help use the systemctl command. 
You are likely to see this:
# systemctl list-unit-files | grep mysql
mysqld.service                              disabled

First I would recommend go to tools.percona.com and create a valid my.cnf file. 

So the solution is easy, we just need to enable this so the database can start on server start up.

#systemctl enable mysqld.service
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service'
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'
#systemctl start mysqld
#systemctl list-unit-files | grep mysql
mysql.service                               enabled
mysqld.service                              enabled 

Now you should be good to go...

Saturday, October 11, 2014

Rotating MySQL Slow Logs

While working with different clients I happen to run across very large slow log files from time to time.  While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command,  I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's blog post from years ago on  how to do this.
I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.

The script will do the following:
  • Gather current log file name
  • Gather current long query time value
  • Resets the long query time to a higher value
  • Copies the log while truncating it as well (See Ronald's Post)
  • Resets the long query time back to the original time
  • Executes a simple slow query so you can check the new slow log if you wish
  • Removes the older slow log so you can gain space back. 
    • You can comment this command out if you wish to review the log instead.
So does it all work ?
Well let us use this example.

I am currently using the mysql_config_editor over a .my.cnf file so I updated the script accordingly.
#  mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost

I can see that this slow query log is now 1G.
# ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 1.1G Oct 11 16:08 mysql-slow.log

So I execute the script
 # /root/rotate_slow_logs.sh
 # ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 5.8K Oct 11 16:11 mysql-slow.log

Ok good, it worked and I have a smaller log file now without flushing my bin logs or restarting MySQL.

This script can be added to a crontab so you can rotate as often as you would like.

Here is the script.
#!/bin/bash

# THIS IS BUILT WITH MYSQL 5.6 SECURITY IN MIND.
# SET THE LOGINPATHVALUE if you are using the mysql_config_editor
# IF YOU ARE NOT USING THE mysql_config_editor THEN IT IS ASSUMED YOU HAVE
# SET A .my.cnf FILE IN THE USER HOME DIR OR THIS USER HAS NO PASSWORD SET


# PLEASE SET THIS ACCORDINGLY TO YOUR SYSTEM.
LOGINPATHVALUE="local";

if [ -z "${LOGINPATHVALUE}" ]; then
LOGINPATH="";
fi

if [ -n "${LOGINPATHVALUE-unset}" ]; then
LOGINPATH="--login-path=$LOGINPATHVALUE "

fi

# GATHERS THE LOG FILE NAME
SLOWLOG=$(mysqladmin $LOGINPATH variables | grep slow | grep file |   awk '/[a-zA-Z]/ {print $4}' )

# GATHER CURRENT VALUE
LQT=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )
LQTB=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *200 AS LQTB;" |   awk '/[0-9]./ {print $1}'  )
LQTC=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *2 AS LQTC;" |   awk '/[0-9]./ {print $1}'   )

# GATHER MARKER
DATE=`date +"%m%d%Y"`

# RESET SLOW QUERY TIME
# SET GLOBAL long_query_time=10;
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQTB"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#MOVE THE LOG OUT
cp $SLOWLOG $SLOWLOG.$DATE; > $SLOWLOG

#SET THE TIMEBACK
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQT"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#PLACE A Slow query for log
SLOWQUERY=$(mysql $LOGINPATH -e "SELECT sleep($LQTC) " )

# REMOVE OLD LOG
/bin/rm -f $SLOWLOG.$DATE;




Tuesday, September 23, 2014

MySQL User connections

So I found myself explaining the differences with MySQL users and how they authenticate. First of all this information is not new but can be found here:
I will just show some real world examples to explain this. 

MySQL uses the username as well as the login when it evaluates the permissions of a user. This is why a best practice is to remove the anonymous users.

For this example I will start off with the following users 



MariaDB [(none)]> select user , host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | 127.0.0.1 |
| root    | ::1       |
|         | centos64  |
| root    | centos64  |
|         | localhost |
| root    | localhost |
+---------+-----------+

I can log into the server even thought I do not have a username because it defaults down to anonymous@localhost.


# mysql -u nobody 
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
MariaDB [(none)]> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

While I cannot do much I still got into the database. 

Now when I pass a known user:


# mysql -u root -p 
MariaDB [(none)]> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
MariaDB [(none)]>  show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8CD56861FDADF7A264741F27D502D1A8DAE0A8F7' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+


Currently the root user has 4 different accounts.

MariaDB [(none)]> select user , host from mysql.user where user = 'root';
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | centos64  |
| root | localhost |
+------+-----------+

These are all different accounts. I only need one of them though. I only use the root account via local host connections. 

MariaDB [(none)]> DROP USER 'root'@'127.0.0.1';
Query OK, 0 rows affected (0.33 sec)

MariaDB [(none)]> DROP USER 'root'@'centos64';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> DROP USER 'root'@'::1';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user , host from mysql.user where user = 'root';
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+

So what did I mean by 4 different accounts? They all the root user account. No.. They are root and whatever host. So each account (user + host) could have different permissions. 

So we will make an example next to show the difference. 
So the server in this example has two network interfaces. So I will create a user per all access points each with different permissions to show the differences. I will set the same password but those could be different as well. 

MariaDB [(none)]> GRANT SELECT  ON *.* TO 'anothermysqldba'@'10.0.2.15' IDENTIFIED BY  '<cleartext password>';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE , DROP, RELOAD, SHUTDOWN, PROCESS, FILE, INDEX, ALTER  ON *.* TO 'anothermysqldba'@'192.168.0.%' IDENTIFIED BY  '<cleartext password>';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'anothermysqldba'@'localhost' IDENTIFIED BY  '<cleartext password>' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user , host from mysql.user where user = 'anothermysqldba';
+-----------------+-------------+
| user            | host        |
+-----------------+-------------+
| anothermysqldba | 10.0.2.15   |
| anothermysqldba | 192.168.0.% |
| anothermysqldba | localhost   |
+-----------------+-------------+

So what does this mean? Even though the usernames are the same because MySQL uses the host as well to validate a user every user access point as different permissions. 

While you might trust some of your developers, this is often a good idea to limit some of their access if they are working remotely or via their scripts. If they need to do something that does not involve you and they have to ssh to the server itself and login to perform other tasks. This is of course dependent on the relationship and work flow of your business and/or applications. 

Using the 10.0.2.15 host the user only has select access. 

# mysql -h 10.0.2.15 -u anothermysqldba -p 
MariaDB [(none)]> select current_user();
+---------------------------+
| current_user()            |
+---------------------------+
| anothermysqldba@10.0.2.15 |
+---------------------------+
MariaDB [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@10.0.2.15                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'anothermysqldba'@'10.0.2.15' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' |
+-------------------------------------------------------------------------------------------------------------------------+

Using the 192.168.0.26 host the user has more access. This account is also set to access across anything under the 192.168.0/255 (%) subnet.

# mysql -h 192.168.0.26 -u anothermysqldba -p 
MariaDB [(none)]> select current_user();
+-----------------------------+
| current_user()              |
+-----------------------------+
| anothermysqldba@192.168.0.% |
+-----------------------------+
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@192.168.0.%                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, INDEX, ALTER ON *.* TO 'anothermysqldba'@'192.168.0.%' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

So as you will assume the localhost user has the full access account. 

# mysql -u anothermysqldba -p
MariaDB [(none)]> select current_user();
+---------------------------+
| current_user()            |
+---------------------------+
| anothermysqldba@localhost |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@localhost                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'anothermysqldba'@'localhost' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------+

Now as I said before it is best practice to remove the anonymous users. Using the mysql_secure_installation for installation will help with his as well. 

Why does it matter?

# mysql -u anothremysqldba -p
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00

The simple example... is above and just avoid simple mistakes. A typo allowed access to the database, it should not allow this. 

So remove then and test again. 

MariaDB [(none)]> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> DROP USER ''@'centos64';
Query OK, 0 rows affected (0.00 sec)

# mysql -u anothremysqldba -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'anothremysqldba'@'localhost' (using password: YES

So what does it mean if your database is created with mostly with % as the hostname or strict ip addresses and no wildcards ?

This would be a security and business decision on your part. Personally neither is a valid option in my opinion. Nothing but a wildcard (%) allows a robust access point but if the firewall has a failure it also opens the access to remote out of network users.  A strict ip address per user is also rather strict in my opinion but it depends on the user. For example even with an account that has been created for replication I prefer to still use the ip address with a % ie: 192.168.0.% .   IP addresses do change (even static changes for remote users the DBA is often not told) and it often results in people coming to the DBA with complaints over access failure.  

I once worked with a person that limited every user access locked to a static ip address. This person had to constantly alter grant statements.  People have to be able to work easily and effectively while you can ensure security on your end. 





Wednesday, August 13, 2014

MySQL Foreign Keys Example & ERROR 1452

So I ran across a situation today dealing with having to update a field but the user was unable to do so because of the related foreign key constraints.

This blog post with be a simple example showing a foreign key and how to update them if you have to do so.

First let us create a simple table and populate it with random data.

CREATE TABLE `table_w_code` (
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`NameofCode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`SOMECode`)
) ENGINE=InnoDB ;


Now we will need another table that has a foreign key tied to our previous table.

[anothermysqldba]> CREATE TABLE `table_with_fk` (
`SOMEID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`SOMECode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`Somemorefields` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`SOMEID`,`SOMECode`),
KEY `FK_Patient_Facility` (`SOMECode`),
CONSTRAINT `FK_CODE` FOREIGN KEY (`SOMECode`) REFERENCES `table_w_code` (`SOMECode`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;


So let us populate some random data into the tables so we can try and update them later.
previous post on random numbers if needed is here 

[anothermysqldba]> SET @A = 3; 
Query OK, 0 rows affected (0.00 sec)

 [anothermysqldba]> SET @B = 15 - @A; 
Query OK, 0 rows affected (0.00 sec)

 [anothermysqldba]> SET @C = 16; 
Query OK, 0 rows affected (0.00 sec)

[anothermysqldba]> SET @D = 25 - @C;
Query OK, 0 rows affected (0.00 sec)

[anothermysqldba]> INSERT INTO table_w_code VALUES
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'ABC' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'DEF' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'GHI' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'JKL' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'MNO' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'PQR' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'STU' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'VWX' ) ,
    -> ( SUBSTR(md5(''),FLOOR( @A + (RAND() * @B )) , FLOOR( @C + (RAND() * @D )) ) ,  'YZ' ) ;
Query OK, 9 rows affected (0.05 sec)
Records: 9  Duplicates: 0  Warnings: 0

[anothermysqldba]> SELECT * from table_w_code ORDER BY NameofCode;
+--------------------------+------------+
| SOMECode                 | NameofCode |
+--------------------------+------------+
| 204e9800998ecf8427e      | ABC        |
| f00b204e9800998e         | DEF        |
| 98f00b204e9800998ecf8427 | GHI        |
| 98f00b204e9800998e       | JKL        |
| 1d8cd98f00b204e9800      | MNO        |
| 1d8cd98f00b204e9800998ec | PQR        |
| 0b204e9800998ecf8427e    | STU        |
| cd98f00b204e9800998ec    | VWX        |
| d98f00b204e9800998ecf842 | YZ         |
+--------------------------+------------+
9 rows in set (0.00 sec)

 [anothermysqldba]> SET @D = 2; 
Query OK, 0 rows affected (0.00 sec)

 [anothermysqldba]> SET @E = 25 - @D;
Query OK, 0 rows affected (0.00 sec)

 [anothermysqldba]> INSERT INTO table_with_fk SELECT SUBSTR(md5(''),FLOOR( @D + (RAND() * @E ))),  SOMECode ,  NameofCode FROM table_w_code;
Query OK, 9 rows affected (0.08 sec)
Records: 9  Duplicates: 0  Warnings: 0

[anothermysqldba]> select * from table_with_fk ORDER BY Somemorefields;
+---------------------------------+--------------------------+----------------+
| SOMEID                          | SOMECode                 | Somemorefields |
+---------------------------------+--------------------------+----------------+
| 41d8cd98f00b204e9800998ecf8427e | 204e9800998ecf8427e      | ABC            |
| e9800998ecf8427e                | f00b204e9800998e         | DEF            |
| 98ecf8427e                      | 98f00b204e9800998ecf8427 | GHI            |
| 00b204e9800998ecf8427e          | 98f00b204e9800998e       | JKL            |
| 04e9800998ecf8427e              | 1d8cd98f00b204e9800      | MNO            |
| 04e9800998ecf8427e              | 1d8cd98f00b204e9800998ec | PQR            |
| b204e9800998ecf8427e            | 0b204e9800998ecf8427e    | STU            |
| b204e9800998ecf8427e            | cd98f00b204e9800998ec    | VWX            |
| 4e9800998ecf8427e               | d98f00b204e9800998ecf842 | YZ             |
+---------------------------------+--------------------------+----------------+


OK that is a round about way to generate some random data for this example.

So what happens if we needed to update data related to the ABC value in table_with_fk?

[anothermysqldba]> SELECT SOMEID , SOMECode , Somemorefields  FROM table_with_fk WHERE Somemorefields = 'ABC';
+---------------------------------+---------------------+----------------+
| SOMEID                          | SOMECode            | Somemorefields |
+---------------------------------+---------------------+----------------+
| 41d8cd98f00b204e9800998ecf8427e | 204e9800998ecf8427e | ABC            |
+---------------------------------+---------------------+----------------+

[anothermysqldba]>  SELECT   SOMECode , NameofCode  FROM table_w_code WHERE NameofCode = 'ABC';
+---------------------+------------+
| SOMECode            | NameofCode |
+---------------------+------------+
| 204e9800998ecf8427e | ABC        |
+---------------------+------------+

[anothermysqldba]>
  UPDATE table_with_fk SET SOMEID = 'I UPDATED THIS' , SOMECode = 'I UPDATED THIS' WHERE SOMECode = '204e9800998ecf8427e';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`anothermysqldba`.`table_with_fk`, CONSTRAINT `FK_CODE` FOREIGN KEY (`SOMECode`) REFERENCES `table_w_code` (`SOMECode`) ON DELETE NO ACTION ON UPDATE NO ACTION)


So it was blocked like it should have been. We do after all have "ON DELETE NO ACTION ON UPDATE NO ACTION" in the table definition.

All is not lost though.  A simple edit of the "FOREIGN_KEY_CHECKS" variable will allow that update statement to execute. However,  It is safer to execute this , in my opinion, within a transaction.


[anothermysqldba]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

[anothermysqldba]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

 [anothermysqldba]> UPDATE table_with_fk SET SOMEID = 'I UPDATED THIS' , SOMECode = 'I UPDATED THIS' WHERE SOMECode = '204e9800998ecf8427e';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[anothermysqldba]> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

[anothermysqldba]> COMMIT;
Query OK, 0 rows affected (0.07 sec)


Now the overall question is why would you want to break your foreign key values that you had set in place for a reason? That is entirely up to you.

Today it happened that somehow a value had been inserted with extra white space in the value into the database. So I did a similar transaction to update and remove the white space.

Overall ... this is just to show it can be done.

Friday, July 18, 2014

MySQL secure_auth error

I addressed the secure_auth errors before when it blocks replication in this blog post.

However, I figured I would make this blog post a more general fix when connecting via MySQL clients. This is for servers before MySQL 5.6.

So if you get a secure_auth error when connection to MySQL the following steps should clear this error.

+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | 34d22ac342c35af2                          
|   |+---------------+-------------------------------------------+

SELECT @@session.old_passwords, @@global.old_passwords;                                                            +-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+

mysql> SET @@session.old_passwords = 0;  SET @@global.old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       0 |                      0 |
+-------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'authdtestuser'@'localhost' = PASSWORD('sshthisisasecret');
Query OK, 0 rows affected (0.00 sec)

mysql> select User , Password from mysql.user where User = 'authdtestuser';
+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | *E48BD8BF1B9F29459E5284AD158443B5B33B70E4 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.old_passwords = 1;  SET @@global.old_passwords=1;

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)


Tuesday, July 15, 2014

MySQL, Ubuntu:: mysqld does not have the access rights

So today I happen to need to restore a MySQL database from backups so I could recover some tables. While I left he production database running via port 3306, I set up the backup via port 3307.

However, when I attempted to start another version via the 3307 port in a mysql_restore directory but  I ran into some errors....


/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf

[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
Can't find file: './mysql/plugin.frm' (errno: 13)

InnoDB: Completed initialization of buffer pool
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

# perror 13
OS error code  13:  Permission denied


So I first checked the permissions on the directory, ensured that it was set to 700 and owned by mysql.  I also ensured that the directories inside the data directory was set to 700 and owned by mysql. The rest of the files all set to 600 ( -rw-rw---- ).  Just to be safe I also ensured that the disk was not full.

So the error log shows permission issues but at first glance all permissions are correct. So what it is?

Well, if I was on RHEL, or related branches,   I would think of SELinux. Was it blocking it somehow.
The Ubuntu Apparmor needed to be reviewed in this case.


# cat /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
...


Notice that the default "var/lib/mysql" is being addressed. The restore directory I created was not though. So by adding that and a restart of apparmor I was then able to successfully start MySQL.


# vi /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql_restore/ r,
  /var/lib/mysql_restore/** rwk,
...
/etc/init.d/apparmor {start|stop|restart|reload|force-reload|status|recache}
/var/lib/mysql_restore# /etc/init.d/apparmor restart
...
/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf
...
# mysql -P 3307 --socket=/var/lib/mysql_restore/mysqld_3307.sock



Additional URLs for review if needed:

Wednesday, June 25, 2014

MySQL Table error 1064

So I came across an odd situation today.

I have a system that creates memory tables using the PHP  $_COOKIE['PHPSESSID'] value.

Once some work is done it then drops the table.

Two sample tables are below for my example .


@@VERSION: 5.6.19-log
CREATE TABLE `f7a7a8d3a7ba75b5eb1712864c9b27eb` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;

CREATE TABLE `8865e52c7e1bea515e7156f240729275` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;


Now some simple processing occurs on the real tables I used then like I said I dropped them.


DROP TABLE IF EXISTS f7a7a8d3a7ba75b5eb1712864c9b27eb;
Query OK, 0 rows affected (0.09 sec)


Simple enough until I ran across this..


desc 8865e52c7e1bea515e7156f240729275;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8865e52c7e1bea515e7156f240729275' at line 1

 DROP TABLE IF EXISTS 8865e52c7e1bea515e7156f240729275;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8865e52c7e1bea515e7156f240729275' at line 1


Now the fix is actually very simple. But the question is why does one work and one fail?

To fix this just backtick the table name. Which I should have done from the start anyway. That is just a good practice to get into.


DROP TABLE IF EXISTS `8865e52c7e1bea515e7156f240729275`;
Query OK, 0 rows affected (0.02 sec)


Now I first thought that maybe the issue was Alpha-numeric name but we can see that one version  works.  The difference though is the start of the table name. One is an integer and one is a Alpha character. So again .. follow best practices and quote table names to avoid such stupid mistakes.


After all the error code is a parse error..


perror 1064
MySQL error code 1064 (ER_PARSE_ERROR): %s near '%-.80s' at line %d


Monday, June 16, 2014

MySQL random integers

This is not a new feature by any means but it is a question I have happen to see pop up every now and then. So a quick example is following.

To generate a random integer within MySQL you can use the Floor and Rand functions. The MySQL manual documents this here: http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_rand

"To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i))"

So to give an example:

> SET @I = 3; # lower range
> SET @J = 43 - @I; # max range minus lower range

> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 4
1 row in set (0.00 sec)
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 8
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 15
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 16
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 23
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 42
1 row in set (0.00 sec

Saturday, June 14, 2014

Installing Percona XtraDB Cluster

So of course Percona has documentation that explain the process. The goal of this blog is to go into a little more detail in hopes that can help someone.

Hyperlinks for review:
Prerequisites
  • Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568
  • Stop iptables for internal local networks or adjust iptable rules.
/etc/init.d/iptables stop

  • SELinux is disabled
echo 0 >/selinux/enforce
vi /etc/selinux/config

  • Set Up SSH Keys and place into authorized_keys so all the id_rsa.pub values are in authorized_keys on all servers.
# ssh-keygen -t rsa
# cd /root/.ssh/
# cp id_rsa.pub authorized_keys
# chmod 600  /root/.ssh/authorized_keys
# chmod 700  /root/.ssh/


So I started with a basic server install of CentOS 6.5.
# yum -y install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum -y install http://mirror.pnl.gov/epel/6/x86_64/epel-release-6-8.noarch.rpm
# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
# yum -y install socat


To avoid any conflicts I removed the mysql-libs and related dependencies
# rpm -e mysql-libs postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat


Then I installed the Percona Cluster packages.
# yum -y install Percona-XtraDB-Cluster-full-56
[root@node1 ~]#  /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"


So we can replace the items we removed per node..
yum -y install postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat


So repeating the above steps will install the packages so that we can configure the cluster next.

[root@node2 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!
[root@node3 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........ SUCCESS!

While we have three instances of MySQL running it is not yet a cluster.

Configuring the nodes

Node 1 /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.33

# SST method
#wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW

# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:<password_here>"

# server_id
server_id=3232235553  #SELECT INET_ATON('192.168.0.33')

#[client]
socket=/var/lib/mysql/mysql.sock


Starting the 1st Cluster Node
 /etc/init.d/mysql start --wsrep-cluster-address="gcomm://"
Starting MySQL (Percona XtraDB Cluster)...................................... SUCCESS!

[root@node1 mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6
seqno:   -1
cert_index:


The cluster is only one node at the moment.
mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 1


OK now now one is up and running we can start node 2
Node 2 /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.34

# SST method
#wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW


# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:"

# to enable debug level logging, set this to 1
wsrep_debug=1

# server_id
server_id=3232235554  # SELECT INET_ATON('192.168.0.34')

#[client]
socket=/var/lib/mysql/mysql.sock

 [root@node2 mysql]#/etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!


Now compare our values on each node.
mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 2

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node2.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 2
1 row in set (0.18 sec)


Now we add node 3 into the mix.

Node 3 /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.35

# SST method
# wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW


# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:"

# to enable debug level logging, set this to 1
wsrep_debug=1

# server_id
server_id=3232235555 # SELECT INET_ATON('192.168.0.35')

#[client]
socket=/var/lib/mysql/mysql.sock

[root@node3 mysql]#/etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!

[root@node3 mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6
seqno:   -1
cert_index:


So how do all of our nodes look like now.
 mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node2.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node3.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3

Test the nodes
So now we can load some data and test it out..
[root@node2 ~]# wget http://downloads.mysql.com/docs/world_innodb.sql.gz
[root@node2 ~]# gzip -d world_innodb.sql.gz
[root@node2 ~]# mysql -e "create database world"
[root@node2 ~]# mysql world < world_innodb.sql


SO now that everything is loaded… is it all across the cluster?
     @@hostname: node1.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001

     @@hostname: node2.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001

     @@hostname: node3.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001

Looks like it is working.