Another MySQL DBA

Thursday, May 9, 2013

Setup MySQL Proxy

So this is just a very simple example of using MySQL Proxy ..

The MySQL Proxy has been in the Alpha stages for what feels like years on end.

MySQL Proxy Documentation :

Whatever is left from the MySQL forge site for MySQL Proxy WIKI can be found here: https://wikis.oracle.com/display/mysql/MySQL+Proxy

Install MySQL Proxy:
Download and unpack from dev.mysql.com. Current Alpha version is mysql-proxy 0.8.3.

Make sure you also have Lua installed

 

yum install lua-devel lua-static lua

You will then find mysql-proxy in the bin directory.



[root@localhost bin]# ./mysql-proxy --help

Usage:

  mysql-proxy [OPTION...] - MySQL Proxy

MySQL command options:

First need to make sure you are aware of the out of date documentation.

You might think that adding daemon=true to your configuration file is valid.
It does after all point out how true is the valid for the following option via the documentation.

For example, the following is invalid:

[mysql-proxy]
proxy-skip-profiling

But this is valid:

[mysql-proxy]
proxy-skip-profiling = true

http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html#option_mysql-proxy_daemon

However daemon or daemon=true results with the following issue so use daemon=1



./bin/mysql-proxy --defaults-file=mysql_proxy.cnf

(critical) Key file contains key 'daemon' which has value that cannot be interpreted.

The same is true for keepalive=true



./bin/mysql-proxy --defaults-file=mysql_proxy.cnf

(critical) Key file contains key 'keepalive' which has value that cannot be interpreted.

Using the Administration Interface:

The admin.lua file is available under the mysql-proxy directory:

/lib/mysql-proxy/lua/admin.lua

This failed for me though so I used the docs version.

you can cut at paste it via the documentation

http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-using-admin.html

The reporter.lua file can also be placed into the /lib/mysql-proxy/lua/ directory:

Can be downloaded:

ftp://ftp.sandy.ru/pub/FreeBSD/ports/databases/mysql-proxy/files/patch-lib-reporter.lua

Or you can cut at paste it via the documentation

http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-using-admin.html

So I end up with the following configuration example

vi mysql_proxy.cnf

[mysql-proxy]

admin-address =127.0.0.1:3308

proxy-address=127.0.0.1:3307

proxy-skip-profiling = true

daemon=1

pid-file = /var/run/mysql-proxy.pid



log-file = /var/log/mysql-proxy.log

log-level = debug

proxy-backend-addresses=127.0.0.1:3306

#proxy-read-only-backend-addresses =127.0.0.1:3306

keepalive=1

admin-username=root

admin-password=secretpassword

admin-lua-script=/usr/local/src/MySQL/mysql-proxy/admin.lua

proxy-lua-script=/usr/local/src/MySQL/mysql-proxy/reporter.lua

plugin_dir=/usr/lib/mysql/plugin/

plugins=proxy,admin

This time it starts....

./bin/mysql-proxy --defaults-file=mysql_proxy.cnf

Tail you log to confirm that proxy is up and running then log into the admin port (3308)



(message) proxy listening on port 127.0.0.1:3307

# mysql -u root -p -P3308 -h127.0.0.1

Once you are logged in you can start to test it:



# mysql -u root -p -P3308 -h127.0.0.1



show querycounter;

+---------------+

| query_counter |

+---------------+

|          NULL |

+---------------+




SELECT * FROM backends;

+-------------+----------------+-------+------+

| backend_ndx | address        | state | type |

+-------------+----------------+-------+------+

|           1 | 127.0.0.1:3306 | 1     | 1    |

+-------------+----------------+-------+------+

# Type 1 = Master and Type 2 would be a slave.






> SHOW PROXY PROCESSLIST;

ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT ... got something else

Remember always check your logs...



(critical) (read_query) [string "/usr/local/src/MySQL/mysql-proxy/admin.lua"]:152: bad argument #1 to 'pairs' (table expected, got nil)

OK the MySQL Proxy is up...
Now the issues and complains over Lua and why is it part of MySQL-Proxy can begin...

http://lua.2524044.n2.nabble.com/Beginner-from-Python-starting-to-MySQL-Proxy-td7636807.html

FOLLOW UP:
http://anothermysqldba.blogspot.com/2018/05/proxy-mysql-haproxy-proxysql-keepalived.html

Comparing the Databases :: MySQL :: Percona :: MariaDB with the MySQL Sandbox

Do you often find yourself curious yet to busy to explore?

Often people stay with what they are comfortable with and started with. MySQL has a very loyal following of users. It is ok to be curious and explore the forks as well. The MySQL Sandbox makes it extremely easy for you to do just that.

MySQL Sandbox is a great tool and the documentation is available if you need help.
http://mysqlsandbox.net/docs.html. The MySQL Sandbox also allows you to check against forks and different versions of the MySQL server easily.

I already have a MySQL 5.6 version running on my server.

$ mysql -p
Enter password:
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

I used the MySQL Sandbox to compare three similar versions of MySQL:

mariadb-5.5.30
Percona-Server-5.5.30
mysql-5.5.31

I decided make my sandboxes this way:
$ make_multiple_custom_sandbox mariadb-5.5.30-linux-i686.tar.gz Percona-Server-5.5.30-rel30.2-500.Linux.i686.tar.gz mysql-5.5.31-linux2.6-i686.tar.gz

node1]$ ./use
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.30-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
> select VERSION();
+--------------------+
| VERSION() |
+--------------------+
| 5.5.30-MariaDB-log |
+--------------------+

Ok this is as expected.

node2]$ ./use
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4

Server version: 5.5.30-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others
> select VERSION();
+--------------------+
| VERSION() |
+--------------------+
| 5.5.30-MariaDB-log |
+--------------------+

Ah? ok not as expected.

node3]$ ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
> select VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.31-log |
+------------+

I expected or at was at least hopeful it was going result with this.

I still think that MySQL Sandbox is a great tool and makes some comparisons and tests very easy to do. I just happen to run into a conflict when trying to run all 3 versions at once. It could completely be my error as well and I have just overlooked something. I think it would be a valid test for some people to want to do though. Regardless I moved and built from source instead. More on that is available here:

http://anothermysqldba.blogspot.com/2013/05/building-from-source-mysql-mariadb.html

MySQL ERROR 1 (HY000): Can't create/write to file

> desc foo_table;


ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_3ff6_0.MYI' (Errcode: 13 - Permission denied)

Now this error is documented : http://dev.mysql.com/doc/refman/5.6/en/cannot-create.html

This is a straight forward fix. What happened to the permissions on the /tmp folder ? Because it is not allowing writes. So first have to fix that then start looking into what or who changed permissions on the directory.

 

chmod 1777 /tmp

I will use this error as an example, even though it is pretty straight forward to see and then fix.

First look at the entire error message and do not focus on the first error you see.
For example if you have an Errcode:

do not focus on ERROR 1
do not focus on HY000

You would be wasting your time when the Errcode gives you all the information you need.
If that happened to be the only error message information that was passed to you then you do have resources available to look up errors :

I would also stress that you should double check the error log to confirm all error messages.
Just because someone sends you an error does not mean it is the entire story, always check your logs.

If you do run across an error that gives you little description it is true that you have the ability to learn more about the error.

"describing the last error encountered during a call to a system or library function." -- http://man7.org/linux/man-pages/man3/perror.3.html

 



# perror 13

OS error code  13:  Permission denied

BTW.. related to the error above, it is also possible to change your tmpdir location if that was required. In this case it was not but it you ever need to change or override the defaults you can find your current tmpdir with this:

 
> select @@tmpdir;

+----------+

| @@tmpdir |

+----------+

| /tmp     |

+----------+

You can edit the my.cnf and place tmpdir=/tmp whatever location you prefer.

Tuesday, May 7, 2013

Upgrading directly from MySQL 5.1 : The server quit without updating PID file

I ran across this the other day...

System was a very basic Oracle Linux install that was installed and created by someone else.
( Oracle Linux Server Unbreakable Enterprise Kernel (2.6.39-400.17.1.el6uek.x86_64) )
It appeared that they wanted the system up and running then have me come in to do the rest.

By the way, if Oracle is going to take RedHat Linux and enhance it to make their own version, could they at least update their products to work with it? MySQL became popular because it was easy for people to get from the distributions, having 5.1 still in their own distribution is just odd.

Well the first thing to do is upgrade since Oracle Linux comes with MySQL 5.1.


# rpm -qa |grep mysql 

mysql-server-5.1.66-2.el6_3.x86_64

mysql-5.1.66-2.el6_3.x86_64

mysql-libs-5.1.66-2.el6_3.x86_64

I used 5.5 for this example....


-rw-r--r--. 1 root root 14976016 Apr 30 06:07 MySQL-client-5.5.31-2.el6.x86_64.rpm

-rw-r--r--. 1 root root  4968092 Apr 30 06:08 MySQL-devel-5.5.31-2.el6.x86_64.rpm

-rw-r--r--. 1 root root 41827172 Apr 30 06:09 MySQL-server-5.5.31-2.el6.x86_64.rpm

-rw-r--r--. 1 root root  3970056 Apr 30 06:09 MySQL-shared-compat-5.5.31-2.el6.x86_64.rpm


# rpm -Uhv *.rpm

error: Failed dependencies

So I had to remove the dependency packages first, and not even sure why that really had them installed.


# rpm -Uhv *.rpm

Now you comes the sad fact that many of us have seen...

"A manual dump and restore using mysqldump is recommended.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf

files

- Shut down the MySQL server cleanly

- Remove the existing MySQL packages. Usually this command will

list the packages you should remove:

rpm -qa | grep -i '^mysql-'

You may choose to use 'rpm --nodeps -ev <package-name>' to remove

the package which contains the mysqlclient shared library. The

library will be reinstalled by the MySQL-shared-compat package.

- Install the new MySQL packages supplied by Oracle and/or its affiliates

- Ensure that the MySQL server is started

- Run the 'mysql_upgrade' program "

This is why so many people could be stuck in MySQl 5.1 because they are scared to death to make these changes. I was lucky since this was a fresh install otherwise yes keeping a backup would be the next step.

Since it wanted me to remove everything, then no reason to say with MySQL 5.5 so I moved to MySQL 5.6. After removing the previous packages (rpm -e) and installing the new (rpm -ihv), I had the following


# rpm -qa | grep MySQL

MySQL-client-5.6.11-2.el6.x86_64

perl-DBD-MySQL-4.013-3.el6.x86_64

MySQL-shared-compat-5.6.11-2.el6.x86_64

MySQL-server-5.6.11-2.el6.x86_64

MySQL-devel-5.6.11-2.el6.x86_64

So I check for a my.cnf file first. Since I might want to make edits if someone had placed on in place.

 
# ls -al /etc/my.cnf 

ls: cannot access /etc/my.cnf: No such file or directory

I decide to keep to defaults for the purpose of this example and I left it missing.

 
# /etc/init.d/mysql start

Starting MySQL..The server quit without updating PID file ([FAILED]/mysql/localhost.localdomain.pid).

It does have to be able to start before mysql_upgrade can be applied.

I tried skip grants

 
#  /etc/init.d/mysql start --skip-grant

Starting MySQL..The server quit without updating PID file ([FAILED]/mysql/localhost.localdomain.pid).

In reality the error log showed the true issue : "InnoDB: Could not open or create the system tablespace"

The issue was they built the partitions horrifically and just had no space for the database. When should you realize this ? Right away. First thing you should do is to check what the partitions of the system are for a system you are building. But that would void the point of this blog post if I pointed that out first.

What is the moral of all this? If you ever see the error "The server quit without updating PID file " the very first thing you should do is check the error log. It will tell you exactly what the problem is.

Load Data example

I saw a recent question about LOAD DATA on the forums.mysql.com site so I thought I would post my solution example here as well.

The user in question was getting a lot of skipped rows and no warnings. The user also wanted to skip the header row and I assume set some of the fields as it was imported. Since I did not see any of the related data or schema I just posted the following working example:

 


CREATE TABLE `example` (

  `Id` int(11) NOT NULL AUTO_INCREMENT,

  `Column2` varchar(14) NOT NULL,

  `Column3` varchar(14) NOT NULL,

  `Column4` varchar(14) NOT NULL,

  `Column5` DATE NOT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB



Column1 Column2 Column3 Column4 Column5

1 A Foo sdsdsd 4/13/2013

2 B Bar sdsa 4/12/2013

3 C Foo wewqe 3/12/2013

4 D Bar asdsad 2/1/2013

5 E FOObar wewqe 5/1/2013



# more /tmp/example.csv

Column1,Column2,Column3,Column4,Column5

1,A,Foo,sdsdsd,4/13/2013

2,B,Bar,sdsa,4/12/2013

3,C,Foo,wewqe,3/12/2013

4,D,Bar,asdsad,2/1/2013

5,E,FOObar,wewqe,5/1/2013



> LOAD DATA LOCAL INFILE '/tmp/example.csv'

    -> INTO TABLE example

    -> FIELDS TERMINATED BY ','

    -> LINES TERMINATED BY '\n'

    -> IGNORE 1 LINES

    -> (id, Column2, Column3,Column4, @Column5)

    -> set

    -> Column5 = str_to_date(@Column5, '%m/%d/%Y');

Query OK, 5 rows affected (0.04 sec)

Records: 5  Deleted: 0  Skipped: 0  Warnings: 0



> select * from example;

+----+---------+---------+---------+------------+

| Id | Column2 | Column3 | Column4 | Column5    |

+----+---------+---------+---------+------------+

|  1 | A       | Foo     | sdsdsd  | 2013-04-13 |

|  2 | B       | Bar     | sdsa    | 2013-04-12 |

|  3 | C       | Foo     | wewqe   | 2013-03-12 |

|  4 | D       | Bar     | asdsad  | 2013-02-01 |

|  5 | E       | FOObar  | wewqe   | 2013-05-01 |

+----+---------+---------+---------+------------+

5 rows in set (0.00 sec)

Pages