Currently Browsing: MySql

Docker Compose LAMP demo project

Project showing a basic LAMP set-up using a networked multi-container configuration.

docker-compose.yml

version: '2'
services:
  php:
     image: phpmyadmin/phpmyadmin
     links:
       - mysql:db
     depends_on:
       - mysql

  mysql:
    image: k0st/alpine-mariadb
    volumes:
      - ./data/mysql:/var/lib/mysql
    environment:
      - MYSQL_DATABASE=mydb
      - MYSQL_USER=myuser
      - MYSQL_PASSWORD=mypass 

  nginx:
    image: nginx:stable-alpine
    ports:
      - "81:80"
    volumes:
      - ./nginx/log:/var/log/nginx
      - ./nginx/nginx.conf:/etc/nginx/nginx.conf:ro
      - ./nginx/files:/var/www/nginx:ro
    depends_on:
      - php

nginx/nginx.conf

worker_processes  1;
events {
  worker_connections  1024;
}
http {
  sendfile  off;
  server {
    listen 80;
 
    location / {
      proxy_pass  http://php;
      proxy_set_header Host $host;
      proxy_redirect     off;
    }
  }
}

The nginx config is simplified but that should work for testing — basically all it’s doing is proxying the php app. Maps to port 81 to avoid conflicts on the host. (Note this is just a rough demo, would need to be fleshed out for any use more than that.)

Regarding linking, you can see that if you run: docker-compose exec mysql ping -c2 nginx to ping from the mysql container to the nginx container, you will succeed even though there are no links specified between these containers. Docker Compose will maintain those links in the default network for you.

If you like, you can fetch a working version from this repo and run docker-compose up, and (assuming you don’t have anything running on port 81) see results on http://localhost:81/ (or whatever your corresponding hostname/IP is).

For more info on Docker Compose networking see: https://docs.docker.com/compose/networking/

By default Compose sets up a single network for your app. Each container for a service joins the default network and is both reachable by other containers on that network, and discoverable by them at a hostname identical to the container name.

Links allow you to define extra aliases by which a service is reachable from another service. They are not required to enable services to communicate Рby default, any service can reach any other service at that service’s name.

Install MariaDB via Yum, Apt, etc.

To get the latest MariaDB for your distribution (Yum, Apt, etc.), use:

https://downloads.mariadb.org/mariadb/repositories/

It will generate a config entry like:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

which you can install by saving to /etc/yum.repos.d/mariadb.repo and run: yum install MariaDB-server MariaDB-client

MySql select from nested query with max date constraint

Example of MySql select from nested query with max (or min) constraint.

 
SELECT company_id FROM (
	SELECT co.company_id, DATE(tsk.modified) mm
        FROM company co
        LEFT JOIN project p ON p.company_id = co.company_id
        LEFT JOIN task tsk ON tsk.project_id = p.project_id
        WHERE 1
            AND co.active < 1
            AND co.pay_status < 0
            AND co.test_account < 1
) AS tmpt
GROUP BY company_id
HAVING MAX(mm) < DATE(DATE_SUB(NOW(),INTERVAL 1 YEAR))
ORDER BY `tmpt`.`company_id`  DESC

Get Mysql database size via query

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema;

Rackspace API: Trove install to enable root user for cloud DB

Rackspace Trove set-up:

Mac OSX:

sudo easy_install pip
sudo pip install python-troveclient

# trove:
# http://docs.rackspace.com/cdb/api/v1.0/cdb-getting-started/content/Install_Trove_Client.html

trove list
trove root-enable

« Previous Entries