Mysql, Postgres reminder

Postgres

Backup / Restore

DATABASE=foo
USER=foo
HOSTNAME=127.0.0.1
# backup
pg_dump -U $USERANME -h $HOSTNAME -W dbname | gzip > backup.gz
# backup only the data without the schema
pg_dump --column-inserts --data-only -U $USERANME -h $HOSTNAME -W dbname | gzip > backup.gz

# restore
gunzip -c backup.gz | psql -U $USER -h $HOSTNAME -W -d $DATABASE

Create user

sudo -u postgres psql 
#psql postgres `whoami` # on osx


--- CREATE USER
CREATE USER username;
ALTER USER username WITH LOGIN PASSWORD 'password';
-- or
CREATE USER username WITH LOGIN PASSWORD 'password'

-- GRANT ACCESS
\c database
GRANT ALL ON ALL TABLES IN SCHEMA public TO username;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO username;
ALTER USER username CREATEDB;


## Remove User
DROP OWNED BY username;
DROP USER IF EXISTS username;

Tricks and tips

Display tricks

\pset format wrapped
\x auto | off | on

Tricks ```
Redis like PUB/SUB with Notifiy and listen: https://www.youtube.com/watch?v=mCG-AtwMMtM
PLV8 extension or how to create postgres function in js: https://github.com/plv8/plv8 ```

Mysql

Backup

1 database

DATABASE=FOO
mysqldump -u root $DATABASE > dump.sql

all database
```
mysqldump --all-databases > dump.sql
```

Restore

DATABASE=FOO
mysql -u root -p $DATABASE < dump.sql

Manage users

List users

ME=foo
HOST=10.0.0.0
mysql -u $ME -p -h $HOST -e "SELECT Host, User from mysql.user"

Add a user

CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD'

Remove a user

DROP USER 'USERNAME'@'%';

Update password

ALTER USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD'

Simple backup script for docker

#!/bin/bash

function main {
     echo "> START BACKUP SCRIPT";
     mkdir /backup 2>/dev/null;

     # catch app we want to backup. Backup everything by default
     if [ $1 ]; then
         LIST_OF_APPS=`docker ps | awk '{if(NR>1) print $NF}' | grep "^$1$"`
     else
         LIST_OF_APPS=`docker ps | awk '{if(NR>1) print $NF}'`
     fi

     for container in $LIST_OF_APPS; do
         backup $container;
     done
 }

 function backup {
     # 0) INITIALIZE
     CONTAINER_NAME=$1 # name of the container to backup
     echo ">> backing up: "$CONTAINER_NAME
     PACKAGE_NAME=`date +"%Y-%m-%d_%Hh%M"`_$CONTAINER_NAME".tar.gz"; # filename as it will appear at the end
     CONTAINER_NAME_EXPORT=$CONTAINER_NAME"_export.tar.gz";
     CONTAINER_NAME_VOLUME=$CONTAINER_NAME"_volume.tar.gz";


     # 1) CREATE A SCREENSHOT OF THE CONTAINER
     docker export --output=$CONTAINER_NAME_EXPORT $CONTAINER_NAME && \
         mv -f $CONTAINER_NAME_EXPORT /backup;


     # 2) BACKUP THE ATTACHED DOCKER VOLUME
     CONTAINER_NAME_VOLUME_SRC=`docker inspect -f \{\{.Mounts\}\} $CONTAINER_NAME | awk '{print $2}' | sed -s 's/_data//';`
     if [ $CONTAINER_NAME_VOLUME_SRC ]; then
         cd $CONTAINER_NAME_VOLUME_SRC && \
             tar -zcf $CONTAINER_NAME_VOLUME _data && \
             mv -f $CONTAINER_NAME_VOLUME /backup/;
     fi


     # 3) CREATE THE BACKUP PACKAGE
     # => MERGE DOCKER EXPORT + VOLUME
     cd /backup && \
         mkdir $CONTAINER_NAME 2>/dev/null && \
         mv $CONTAINER_NAME_VOLUME $CONTAINER_NAME && \
         mv $CONTAINER_NAME_EXPORT $CONTAINER_NAME && \
         tar -zcf $PACKAGE_NAME $CONTAINER_NAME && \
         rm -rf $CONTAINER_NAME;


     # 4) SHIP THE BACKUP SOMEWHERE

     # 5) LOG WHAT JUST HAPPENNED
     echo -e `date +"%Y-%m-%d_%Hh%M"` \\t $CONTAINER_NAME \\t $PACKAGE_NAME > /var/log/docker_backup.log
 }

main $1