Does anyone have any guidance on how to deploy PIWIK with a remote AWS RDS MySQL. I am able to install PIWIK just fine with a local MySQL. When I try installing with a remote MySQL instances I get connection errors. Help!!!
I am vary new to PIWIK and have vary limited php and MySQL skills. if you can provide an easy to read step-by-by instructuions, I would be vary great full to you.
Thanks,
GM.
Gerry,
I have Piwik running against RDS, and the only thing different for me was using the AWS DNS name for the instance (you aren’t able to rely on the IP address not changing). You will also need to ensure that security group rules allow the MySQL traffic from your Piwik webserver.
You can send me an email to jfudge@thedmsgrp.com if you want to get into more detail.
-Jeffjfudge,
I did that but piwik will not get me pass the initial webui implementation screen when i plugged in the RDS URL.
Any guidance on that?
-gerry
Gerry,
Look at the security group (SG) that your RDS instance uses. Make sure that the IP address that contains your Piwik application is permitted along with the MySQL port.
jfudge,
it contain ip address of my PIWIK host. it gives me this error
Error while trying to connect to the database server: SQLSTATE[HY000] [2003] Can’t connect to MySQL server on…
thoughts?
i think it has something to with DNS. i did a tcpdump and it appears as though it done not know how to route it.
Amazon recommends that you never use an IP address for RDS, and instead use the URL (it typically looks like this: yourRDSname.AWSidentifier.us-west-2.rds.amazonaws.com). So Piwik should be told to use the URL. To test connectivity, you should SSH into your Piwik server and then try to access your RDS instance via the command line.
mysql -h yourRDSurl -P 3306 -u username -p
By using the command line, this eliminates Piwik as a potential issue and will ensure that you have the username, password, port, and AWS security group settings working as expected. Only once you have connected from the command line of the Piwik host would I then try configuring Piwik to use the RDS instance.
jfudge,
i was able to connect from a mysql command line and even call for status just to be sure i made my connection. I also watch the traffic using tcpdump when making the mysql calls. I went back into the PIWIK URL and tried to connect again with the same parameters as the mysql command line and i get the same error:
Error while trying to connect to the database server: SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ’ ..us-east-1.rds.amazonaws.com’ (13)
i masked the rdsname and AWSidentifier
When i saw the traffic in tcpdump, i saw the ip it was going to and I tried inputting that in the database server field in the UI, same error.
thoughts?
By the way, thank you for the responses.
Gerry,
Can you confirm that the command line was from the actual Piwik server, and not from another box?
Also, is your Piwik server within your AWS environment, or external to AWS? Is it an EC2 instance? There is something blocking port 3306 (assuming you used the default MySQL port), and unfortunately you can’t ping the RDS instance as a test because Amazon blocks ICMP packets for RDS.
If you can connect via the command line on the Piwik server to your RDS instance, I don’t see any reason that Piwik wouldn’t (assuming you are using the same connection parameters).
If you have paid support, an AWS engineer would certainly review your security group settings and ensure you haven’t missed a rule somewhere.
jfudge,
do you know if there is something i need to configure on the backend (PIWIK server), i mean before the webui. to make the connection happen? I am quite sure AWS side is good to.
as i said, from the PIWIK server (an EC2 instance in the same VPC but different subnet) I can make a mysql connection with no problem. I am using the same connection parameters just like you guide me to. when i watched the traffic, using tcpdump, i see the connection attempting to connect to RDS by querying AWS DNS first but does not get anything which causes it to die. but when i use mysql, the tcpdump sees the connection just find when resolving the dns name
I will try to engage AWS. I really appreciate your responses
Gerry M.
Gerry,
Looking at my config.ini.php for Piwik, the only items I see tied to my RDS instance are username, password, dbname, tables_prefix, and adapter. We are using the MYSQLI adapter.
Perhaps the issue is the MySQL adapter you are asking Piwik to use on that box?
-Jeff
jfudge,
it may be, i was using the default connector. do know what changes in the adapter that i need to do?
-gerr
Any update on this issue? I am getting same error.
Thanks
-Aniket