Question 21544 in Mysql Question posted in
The official MySQL documentation can be found here.

bash script to extract text from user's env.php? – Mysql

I work for a web-hosting company. I’m rather green, and being new, I only have non-root access to our clients’ servers. Often, I need to troubleshoot mysql. Since I don’t have root, I must use the clients’ mysql credentials. These can be found in

~/<domain>/html/app/etc/env.php

an example block:

'db' => [
        'table_prefix' => '',
        'connection' => [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'xxxxx',
                'username' => 'xxxxx',
                'password' => 'xxxxx',
                'model' => 'mysql4',
                'engine' => 'innodb',
                'initStatements' => 'SET NAMES utf8;',
                'active' => '1',
                'driver_options' => [
                    1014 => false

I need to extract dbname, username, and password from this block of the file on each unique server. Until now, I’ve just been using cat, but since each server has its env.php in a unique path (based on domain name) I don’t know how to do this in a one-shot. Using find to search ~/ for env.php doesn’t work since clients will often copy their site for backup and dev purposes, creating multiple files with that name.

My bash is very basic. Any tips are appreciated and will make my work much more efficient.

Until now, I have been moving into the site directories manually and navigating to the file I need. This is time consuming.

Edit: using basic grep to find these fields in the file doesn’t work since it returns credentials from all blocks (redis, etc), and not just the mysql username/password.

Tags:

3

Answers


  1. 0

    One grep solution that depends on the format of the ‘db’ configuration blocks matching your example would be to use grep’s -B switch to ask for 3 lines preceding the line that matches => 'mysql[0-9]*'. This assumes that the 3 lines preceding are the ‘dbname, username, and password’. Thus, this could be fairly fragile.

     grep -B 3 "=> 'mysql[0-9]*'" /path/to/your/env.php | grep 'dbname|username|password'
                'dbname' => 'xxxxx',
                'username' => 'xxxxx',
                'password' => 'xxxxx',

    Another option (that will likely be much less fragile) would be to use awk:

    awk -F'=>' '/^'''db''' => [/ {flag=1} /dbname|password|username/ && flag==1 {gsub(/'''|,/,""); print $2} /^]/ && flag==1 {flag=0}' /path/to/your/env.php 
 xxxxx
 xxxxx
 xxxxx

    This awk command will explicitly look inside the db configuration block for the dbname, password and username lines and then print the values for those configurations. The command will also strip the single quotes and trailing commas from the output.

    Login or Signup to reply.
  2. 0

    sed can be a great addition to your toolbox, as you can specify things like range toggles and blocks to execute. For example –

    $: sed -En "/^'db' *=>/,/]/{ /'connection' *=>/,/]/{ /'(dbname|username|password)' *=>/p }; }" file
            'dbname' => 'xxxxx',
            'username' => 'xxxxx',
            'password' => 'xxxxx',

    Obviously, awk is great for a more programmatic solution, but someone already addressed that.

    Login or Signup to reply.
  3. 0

    Use the fact that the file is source code and is likely to be delimited by single or double quotes.

    Given:

    $ cat env.php 
<?php return [
  "memcache" => [
    "count" => "1",
    "host1" => "cluster.memes.db.example.com",
    "port1" => "1111",
    "password" => "f1313123",
  ],
  "mysql" => [
    "database" => "my_db",
    "host" => "server.mysql.eu.company.example.net",
    "password" => "J[VwNWJISuUGx09RI2hn6",
    "port" => "3306",
    "user" => "my_user",
  ],
  "redis" => [
    "count" => "1",
    "host1" => "redis.example.tld",
    "port1" => "1231",
    "password" => "EEEEEEE",
  ],
]

    So the essential clue is:

    $ <env.php  awk '-F"' '{ print $2, $4}'
 
memcache 
count 1
host1 cluster.memes.db.example.com
port1 1111
password f1313123
 
mysql 
database my_db
host server.mysql.eu.company.example.net
password J[VwNWJISuUGx09RI2hn6
port 3306
user my_user
 
redis 
count 1
host1 redis.example.tld
port1 1231
password EEEEEEE

    You may have to do some preprocessing, like convert all " to ‘ or vice versa.
    And probably some post-processing for comfort:

    $ <env.php  awk '-F"' '{ print $2, $4}' 
   | tr   = 
   | sed -n '/mysql=/,$p' 
   | sed -E '/(redis|memcached|mailchimp)/q'
mysql=
database=my_db
host=server.mysql.eu.company.example.net
password=J[VwNWJISuUGx09RI2hn6
port=3306
user=my_user
=
redis=

    Obviously you need to adjust mysql= -> db= for your case.
    And it’s possible to be clever in the awk script about which fields you print, but this should get you started, I believe.

    With your input I get:

    $ <env2.php  awk "-F'" '{ print $2, $4, $6, $8, $10, $12 }' | tr   = | sed -n '/db=/,$p' | sed -E '/(redis|memcached|mailchimp)/q'
db=====
table_prefix=====
connection=====
default=====
host=localhost====
dbname=xxxxx====
username=xxxxx====
password=xxxxx====
model=mysql4====
engine=innodb====
initStatements=SET=NAMES=utf8;====
active=1====
driver_options=====
=====

    Pro Tip: ask someone who has worked there for a while how they do it. You might be in for a pleasant surprise!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search