3

i am trying to setup mysql-proxy on ubuntu on amazon ec2 i have done following:

sudo apt-get install mysql-proxy --yes
vi /etc/default/mysql-proxy

i put following content on "/etc/default/mysql-proxy"

ENABLED="true"
OPTIONS="--proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua
     --proxy-address=127.0.0.1:3306
     --proxy-backend-addresses=private_ip_of_another_ec2_db_server:3306,private_ip_of_another_ec2_db_server:3306"

also tied with "--proxy-address=private_ip_or_public_ip_of_proxy-server:3306 or 4040" and "--proxy-backend-addresses=public_ip_of_another_ec2_db_server:3306,public_ip_of_another_ec2_db_server:3306"

after that i tried to connect proxy server from another pc using mysql like:

mysql -u some_user -pxxxxx -h proxy_server_ip
or 
mysql -u some_user -pxxxxx -h proxy_server_ip -P 4040

but its not working its showing error: 

ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' (10061)

i want to tell you can connect the db server remotely where i allowed remote connection to any host

i also tried /etc/init.d/mysql-proxy start or /etc/init.d/mysql-proxy restart but no result

just to inform you that /etc/init.d/mysql-proxy stop is showing failed

can anyone please help me to setup and configure mysql-proxy on ubuntu

===

Edit

i found some help from other question of stackoverflow and also according to a suggestion in the comments, have done following procedure. and it seems its working now.

i installed mysql-client and mysql-server locally(on proxy server) then i tried to run mysql-proxy using following command:

mysql-proxy --proxy-backend-addresses=10.73.151.244:3306 --proxy-backend-addresses=10.73.198.7:3306 --proxy-address=:4040 --admin-username=root --admin-password=root --admin-lua-script=>/usr/lib/mysql-proxy/lua/admin.lua

then i tried to connect remotely to the proxy server and its working. but it seems i need to run this command under screen because when i close the terminal proxy stops working.

Can you please tell me that do i need to run this command under screen or is there any other way to make it alive all time?

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
user3011768
  • 191
  • 2
  • 3
  • 11
  • 1
    *Win32 error code 10061: No connection could be made because the target machine actively refused it.* ... which is exactly consistent with `--proxy-address=127.0.0.1:3306`. The proxy will reject connections from external machines with that option included in the configuration. Don't test remotely. Test locally *then* remotely, once local is working. – Michael - sqlbot Feb 19 '14 at 20:51
  • to test remotely which configuration you suggest? to inform you that i haven't installed mysql-client or mysql-server locally(where i installed mysql-proxy) – user3011768 Feb 20 '14 at 05:26
  • You are making it more difficult on yourself if you don't begin with local testing. Install mysql-client... you'll need it sooner or later. Separately, do not specify `--proxy-address` at all when starting the proxy, and the proxy should listen in all possible IP addresses, TCP port 4040, by default. Note the code at the end of the error you get when testing (10061, in the example you posted). C:\>perror 10061 (substitute any different code you see) will give you a translation of this error in the future. – Michael - sqlbot Feb 20 '14 at 05:47
  • Michael, please check my edit section of my question and please give me suggestion. – user3011768 Feb 20 '14 at 06:38

2 Answers2

2

There is no need to install Mysql client or Mysql Server on your mysql-proxy.

Installing mysql-proxy does have "full daemon capabilities" compiled into it.

If your are running Ubuntu Server, you may wish to use an UPSTART service script.

This script can be copied into /etc/init/mysql-proxy.conf

# mysql-proxy.conf (Ubuntu 14.04.1) Upstart proxy configuration file for AWS RDS
# mysql-proxy - mysql-proxy job file

description "mysql-proxy upstart script"
author "shadowbq <shadowbq@gmail.com>"

# Stanzas
#
# Stanzas control when and how a process is started and stopped
# See a list of stanzas here: http://upstart.ubuntu.com/wiki/Stanzas#respawn

# When to start the service
start on runlevel [2345]

# When to stop the service
stop on runlevel [016]

# Automatically restart process if crashed
respawn

# Essentially lets upstart know the process will detach itself to the background
expect daemon

# Run before process
pre-start script
    [ -d /var/run/mysql-proxy ] || mkdir -p /var/run/mysql-proxy
    echo "starting mysql-proxy"
end script

# Start the process
exec /usr/bin/mysql-proxy --plugins=proxy --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua --log-level=debug --proxy-backend-addresses=private_ip_of_another_ec2_db_server:3306,private_ip_of_another_ec2_db_server:3306 --daemon --log-use-syslog --pid-file=/var/run/mysql-proxy/mysql-proxy.pid

In the above example I hard coded the AWS RDS server into script, instead of fiddling with defaults and config file

shadowbq
  • 1,232
  • 1
  • 16
  • 29
  • Logs are redirect to /var/log/syslog in full debug to help troubleshoot. Be sure to change the log level in production. – shadowbq Oct 01 '14 at 04:09
1

Install Upgraded version 0.8.5

Note:

apt repo does not have 0.8.5 so we need to download tar from mysql official site

Prerequisite :-

Create file /etc/default/mysql-proxy with following content ENABLED="true" OPTIONS="--defaults-file=/etc/mysql/mysql-proxy.cnf"

Installation Procedure :-

  1. Download mysql-proxy 0.8.x
  2. Untar in /usr/local

  3. Update PATH environment with /usr/local/mysql-proxy-0.8.5-linux-debian6.0-x86-64bit/bin vim /etc/environment (to update environment path) cd /usr/local/mysql-proxy-0.8.5-linux-debian6.0-x86-64bit/bin

  4. Run command sudo ./mysql-proxy --defaults-file=/etc/mysql/mysql-proxy.cnf

Sample mysql-proxy.cnf file

[mysql-proxy]
log-level=debug
log-file=/var/log/mysql-proxy.log
pid-file = /var/run/mysql-proxy.pid
daemon = true
--no-proxy = false
admin-username=ADMIN
admin-password=ADMIN
proxy-backend-addresses=RDS-ENDPOINT:RDS-PORT
admin-lua-script=/usr/lib/mysql-proxy/lua/admin.lua
proxy-address=0.0.0.0:4040
admin-address=localhost:4041

  1. change host ip and port of RDS or mysql

  2. connect to Mysql server via proxy with

    mysql -h{proxy-host-ip} -P 4040 -u{mysql_username} -p

devzone
  • 49
  • 1
  • 7
  • Only upgraded version of mysql proxy 0.8.5 works well with latest mysql. So after upgrade its functioning well. Follow above mentioned steps. – devzone Mar 17 '17 at 10:33