I’m using Sequal Pro here, but this should work for almost any connection. I’m also going to lock to a single IP for security. You could us ‘%’ for any IP, but I wouldn’t recommend it if you are on a static ip at home of at work.
Log into ssh for the relevant LAMP instance using the browser tool on the Lightsail dashboard.
cat bitnami_application_password to get your application password. Copy it somewhere as you’ll need it shortly.
Comment out the line that starts with bind-address. So
Exit and save the file.
Update mysql permissions for root remote access with
/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'220.127.116.11' identified by 'PASSWORD' with grant option";
Replace 18.104.22.168 with internet connection IP address. Replace PASSWORD with the password you copied above.
Restart mysql with
sudo /opt/bitnami/ctlscript.sh restart mysql
Copy the IP address of your Lightsail instance. You may want to add a free static IP address, else the IP will change on restart and all this work will need doing again.
In Sequal Pro choose to add a new connection. Select the SSH tab. In both hosts, put the IP of your Lightsail server.
In mysql username put
root and in password, put your password from above.
In ssh username put
bitnami and in password, put your password from above.
Click to test your connection. All should connect as expected.
You’ll now need to add a database etc. Enjoy.
Sometimes when you pull back a whole load of results from a database table and order them by their timestamps you might want to either sum any result which happened at a time close to each other or you might want to only bring back one type of something within a period of time to stop that item flooding your results.
I’ve used this technique a couple of time now. The first time was to make lots and lots or data fit onto a relatively small graph, so I needed some form of average. The second time was because if the same event happened in a set period of time I only wanted to know about it once.
The query looks something like this once we include our timeslices / time periods in our select and then follow it up by grouping by them.
ROUND((CEILING(UNIX_TIMESTAMP(`timestamp`) / 600) * 600)) AS timeslice
GROUP BY timeslice
Here I have selected timeslices of 10 minutes (600 seconds) but you can group larger or shorter periods of time if you require them.
So there you have it, grouping by timeslices for summing readings or limiting duplicates in results.
Adding a new column to a table which needs to contain a different random hash for each row?
Mysql can easily generate md5 strings and update all rows in the table for you
SET thecolumn = MD5(RAND())
WHERE thecolumn IS NULL
You can delete from multiple tables (which don’t have foreign keys set up) using one query if you create it using the following syntax: Continue reading “Delete From Two Or More Tables With One MySQL Query”
SET `field_name` = (SELECT CASE `field_name` WHEN 1 THEN 0 ELSE 1 END)
WHERE `id_column` = 1
Or with text strings
SET `field_name` = (SELECT CASE `field_name` WHEN 'foo' THEN 'bar' ELSE 'foo' END)
WHERE `id_column` = 1