Usage with Bitbucket Pipelines

Set up:

• create an ssh account which your developers can access, to be used for storing anonymised files, eg. developers@some.host
• enable pipelines on the repository
• in repository settings, create an ssh key for the pipeline
• create an sftp-only account on your production server, which can read the production DB backups
• add that key to an account on your production server where the MySQL backups can be accessed, and to the developers account you set up
• in 'repository variables', add:
• • MYSQL_DB - the production database name
• • SERVER_PROD - the production server
• • SERVER_PROD_SFTP_USER - the sftp username on the production server
• • DEV_FILES_USER - the username for your developers ssh account
• • DEV_FILES_HOST - the hostname for your developers ssh account

Pulling in your anonymised data

For Magento systems, do something like this:

scp -B $DEV_FILES_USER@$DEV_FILES_HOST:db/$DB-anon.sql.gz $HOME/$DB-from-prod-$DATE.sql.gz && \
        magerun2 maint:en && \
        magerun2 db:import --compression gzip $HOME/$DB-from-prod-$DATE.sql.gz && \
        mysql --no-auto-rehash -e "update core_config_data set value = replace(value, '.live-server.com', '.test-server.com') where path like '%/base_url';" && \
        mysql --no-auto-rehash -e "delete from flag where flag_code in ('system_config_snapshot', 'config_hash');" && \
        magerun2 setup:up && \
        magerun2 set:di:compile && \
        magerun2 ca:cl && \
        magerun2 maint:dis && \
        magerun2 sys:store:config:base-url:list && \
        echo "Completed successfully - you are now running production data from the latest backup!"

Create bitbucket-pipelines.yml:

definitions:
  services:
    mysql:
      image: mariadb:latest
      variables:
        MYSQL_DATABASE: $MYSQL_DB
        MYSQL_USER: 'mysqluser'
        MYSQL_PASSWORD: 'rootpassword'
        MYSQL_ROOT_PASSWORD: 'rootpassword'

image: php:7.2-fpm

pipelines:
  custom:
    anonymise-prod-db:
      - step:
          name: masquerade the prod DB
          script:
            - apt-get update && apt-get install -y unzip mariadb-client ssh git
            - echo "get mysql/${MYSQL_DB}.sql.gz" | sftp $SERVER_PROD_SFTP_USER@$SERVER_PROD
            - gunzip -c ./${MYSQL_DB}.sql.gz | mysql --host=127.0.0.1 --user=root --password=rootpassword $MYSQL_DB
            - docker-php-ext-install mbstring mysqli pdo pdo_mysql
            - docker-php-ext-enable mbstring mysqli pdo pdo_mysql
            - curl -L -o masquerade.phar https://github.com/elgentos/masquerade/releases/latest/download/masquerade.phar
            - chmod +x ./masquerade.phar
            - ./masquerade.phar run --platform=magento2 --database=$MYSQL_DB --host=127.0.0.1 --username=mysqluser --password=rootpassword --locale=en_GB
            - mkdir -p var/tmp/output/db
            - mysqldump --host=127.0.0.1 --user=root --password=rootpassword $MYSQL_DB |gzip -c - >./var/tmp/output/db/${MYSQL_DB}-anon.sql.gz
            - scp -B ./var/tmp/output/db/${MYSQL_DB}-anon.sql.gz $DEV_FILES_USER@$DEV_FILES_HOST:db/
          services:
            - mysql

Set it to run daily/weekly/monthly

• configure this in the Pipelines section of the bitbucket repository

Thanks to @johnorourke for writing this up