Bash Script: Create MySQL Database & User with Optional Password

January 3, 2017  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Mysql

I have been using DigitalOcean for my personal projects. And one repeated things that I had to do is the creation of MySQL database & user. I know I could have used some CPanel like GUIs and even some of them came to my mind like VestaCP, Ajenti, ServerPilot etc. But all comes with sort of things and not so developer friendly.

So I decided to create a simple bash script that can simply the database & user.

Bash Script Code

You can find the script at Gist:

How to use?

1# Download
Download the script from Gist – Bash Script: Create MySQL Database & User

2# Set Permission
You need to set the executable permission in order to execute the shell script:

chmod +x

3# Usage
Use the script as:

./ [--host="<host-name>"] --database="<db-name>" [--user="<db-user>"] [--pass="<user-password>"]

So the only required parameter is database name. In the case of empty values for other parameters:

  • ‘host’ becomes localhost
  • ‘user’ takes value from database name
  • ‘password’ is randomly generated

For the following command:

./ --database=bash_db2

The output looks like:

Bash Script: Create MySQL Database & User with Optional Password

Bash Script Console: Create MySQL Database & User with Optional Password

Hope you find this script useful.
Please do let us know any feedback in the comments below.

Configure your Bash Shell for Magento 2 CLI Commands

June 29, 2016  |  No Comments  |  by Raj (MagePsycho)  |  Mac OS-X, Magento 2

One of the major improvement in Magento 2 is the introduction of CLI commands based on Symfony Console Component.

CLI commands can perform multiple tasks. Some of them are:

  • Installing Magento (and related tasks such as creating or updating the database schema, creating the deployment configuration, and so on)
  • Clearing the cache
  • Managing indexes, including reindexing
  • Creating translation dictionaries and translation packages
  • Generating non-existent classes such as factories and interceptors for plug-ins, generating the dependency injection configuration for the object manager.
  • Deploying static view files
  • Creating CSS from LESS
  • Providing “plug in” capability for third party developers

To invoke the CLI command:

php /path/to/your/magento2/bin/magento group:[subject:]action

To list all the available console commands:

php /path/to/your/magento2/bin/magento --list

which will output similar to:
Magento2 CLI Commands

In this article, we will discuss on how to configure bash shell for Magento 2 CLI to increase your productivity.

Bash Configuration Tips for Magento 2 Console

1. Install Bash Completion

Refer to our previous article – Install Bash Completion for Mac OS-X, which shows how to enable the completion feature for shell commands just by pressing [TAB]

2. Create useful bash aliases

Alias for bin/magento: mage2
Edit your ~/.bash_profile and add the following code:

alias mage2 = "./bin/magento"

Now instead of using:

php bin/magento [options]

You can simply use:

mage2 [options]

(Must be run from root of Magento2 path)

Alias for enabling module: mage2-module-enable
In order to enable module in Magento2, you have to run following series of command:

php bin/magento module:enable Vendor1_Module1 VendorN_ModuleN --clear-static-content
php bin/magento setup:upgrade

To simplify this process, you can simply add the alias as:

mage2ModuleEnable() {
	mage2 module:enable "$@" --clear-static-content && mage2 setup:upgrade
alias mage2-module-enable="mage2ModuleEnable"

Now you can simply enable the module(s) by running:

mage2-module-enable Vendor1_Module1 Vendor2_Module2

These are just a few examples. You can add similarly if needed.

Isn’t that a smarter way of executing CLI command?
Note: Don’t forget to reload the bash after adding aliases:

source ~/.bash_profile

3. Install Magento2 Bash Completion

Some of the cool dudes in Magento community are really doing amazing work. One of such work is magento2-bash-completion plugin.

In order to install this magento2-bash-completion, proceed as follow.
Download plugin to bash-completion hook folder

curl -o /usr/local/etc/bash_completion.d/magento2-bash-completion

Reload the shell

source /usr/local/etc/bash_completion.d/magento2-bash-completion

Time for Magic
Just write few letters of command and press [TAB], you will see as per the following gif:
Magento2 Bash Completion
(Image Source:

Do you have any useful tips on executing Magento2 CLI commands? Please do share in the comment.
(We will keep on updating this post with more cool bash tips for Magento 2 CLI, so keep your eye on it.)

Run Magento database repair tool from CLI using Ruby’s Mechanize

June 11, 2015  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Magento, Ruby

Shell Scripting which is great for task automation, falls short when it comes to web browser automation.
In case of Magento, using Shell script you can do many operations like Installation, Migration, Deployment, Backup etc. & even more. But when it comes to run the Database Repair Tool(a great tool for repairing Magento database while upgrading) it becomes trickier and complex.

Magento Database Repair Tool

Magento Database Repair Tool

While Upgrading Magento say from to, running DB repair tool for every version manually from browser is repetitive & hectic. So I thought of writing a Ruby CLI script to automate it which will be a simple command with just two parameters(DB repair Url and Magento version).

This console script is a Ruby script which uses Mechanize gem/library.

The Mechanize library is used for automating interaction with websites. Mechanize automatically stores and sends cookies, follows redirects, and can follow links and submit forms. Form fields can be populated and submitted. Mechanize also keeps track of the sites that you have visited as a history.

Install Ruby

Ruby comes pre-installed in Mac. If you are in other operating system please refer to the Installation Document
To test if ruby is already installed, run the following command:

ruby -v

Which will give you the output like:

ruby 2.0.0p481 (2014-05-08 revision 45883) [universal.x86_64-darwin14]

Install Mechanize Gem

You need to install RVM(Ruby Version Manager) first.
First install the stable version of RVM stable with ruby:

\curl -sSL | bash -s stable --ruby

Update your gems:

sudo gem update --system

Now you can instally any gems. Here we will be installing mechanize gem:

gem install mechanize

Prepare & Run the Script

After installation of Ruby, RVM and all it’s required gems, we are ready to go for coding the script.
OS: Mac OS X Yosemite 10.10.4
Ruby: 2.0
Magento Reference Database: magento[version]_vanilla

1. Prepare the Script
Copy the following ruby script to the root of your Magento Dir:
File: mage-db-repair-tool.rb

#!/usr/bin/env ruby

# Magento DB Repair tool using CLI
# Uses Ruby's Mechanize gem
# @author   Raj KB <>
# @website
# Tested on Mac OS-X 10.X

require 'mechanize'
require 'fileutils'

def checkError(page)
  if'.msg_error').length > 0
    puts "[ERROR]"'.msg_error li').each do |li|
      puts li.text.strip

def checkResult(page)
  puts "[RESULT]"'.msg_success li').each do |li|
    puts li.text.strip

def checkNotice(page)
  puts "[NOTICE]"'.msg-note').each do |note|
    puts note.text.strip

  if'.msg-note').length > 1
    puts "See log for more details"

abort "#{$0} Argument Missing" if (ARGV.size < 1)

dbRepairUrl     = ARGV[0]
mageVersion     = ARGV[1]
mageDir         = Dir.pwd
dbRepairLogDir  = "#{mageDir}/var/dp-repair-tool"

FileUtils.mkdir_p "#{dbRepairLogDir}"
fp ="#{dbRepairLogDir}/mage-#{mageVersion}-result.html", "a+")

agent =
page = agent.get(dbRepairUrl)
puts "Loading page: #{page.title}..."
form = page.forms.first

puts "Setting db repair form values..."

# Get DB value from app/etc/local.xml
xmlFile ="#{mageDir}/app/etc/local.xml")
doc = Nokogiri::XML(xmlFile)
corruptedHostname = doc.xpath('/config/global/resources/default_setup/connection/host').text()
corruptedUsername = doc.xpath('/config/global/resources/default_setup/connection/username').text()
corruptedPassword = doc.xpath('/config/global/resources/default_setup/connection/password').text()
corruptedDatabase = doc.xpath('/config/global/resources/default_setup/connection/dbname').text()

form['post_form']           = 'true'
form['corrupted[hostname]'] = corruptedHostname
form['corrupted[database]'] = corruptedDatabase
form['corrupted[username]'] = corruptedUsername
form['corrupted[password]'] = corruptedPassword

# Edit reference database credentials
form['reference[hostname]'] = corruptedHostname
form['reference[database]'] = "magento#{mageVersion}_vanilla"
form['reference[username]'] = corruptedUsername
form['reference[password]'] = corruptedPassword

#p form; exit

puts "Submitting db repair form..."
result_page = form.submit(form.button_with(:id => "button-continue"))

# Check if there is an error & exit

# Continue if there is not an error
# Check if it further requires submission
if'button#button-continue').length > 0

    # Form found again?
    puts "Submitting again..."
    form = result_page.forms.first
    result_page = form.submit(form.button_with(:id => "button-continue"))


You can also download the script from: Magento DB Repair Tool Using CLI – Ruby + Mechanize

2. Run the Script
Give script the executable permission:

cd /path/to/mage-db-repair-tool.rb
chmod +x ./mage-db-repair-tool.rb

Now, You can run the script either by using command:

ruby mage-db-repair-tool.rb <magento-db-repair-url> <magento-version>

or simply using:

./mage-db-repair-tool.rb 1702


./mage-db-repair-tool.rb 1702
Ruby DB Repair Tool Console Output

Ruby DB Repair Tool Console Output

Apart from the results shown at the console output (refer to the above snapshot), You can also check the detailed output logged in your /path/to/magento/var/dp-repair-tool/mage-[magento-version]-result.html

Bonus Tips:

If you want to make system-wide command, then copy the file to the system-wide paths like /usr/local/bin, /usr/bin, /bin etc:

sudo cp /path/to/your/mage-db-repair-tool.rb /usr/local/bin/mageDbRepairToolRb

Now you can run the command from anywhere

mageDbRepairToolRb 1702

Shall you have any issues please post a comment below and I’ll try and help you out.

Backup Magento project files / db using bash script

July 1, 2013  |  8 Comments  |  by Raj (MagePsycho)  |  Linux, Magento

I have googled for the bash script to backup Magento sites/db but none of them worked for me the way I wanted.
So I decided to create a custom bash script which is simple and does the job perfectly.

I have developed the script for my own need. But I thought it would be helpful if shared with you guys as well.
Here goes the overall backup script:

#@author		MagePsycho <>
#@version		0.1.0

#/************************ EDIT VARIABLES ************************/
#/************************ //EDIT VARIABLES **********************/

host="$(echo "cat /config/global/resources/default_setup/connection/host/text()" | xmllint --nocdata --shell $dbXmlPath | sed '1d;$d')"
username="$(echo "cat /config/global/resources/default_setup/connection/username/text()" | xmllint --nocdata --shell $dbXmlPath | sed '1d;$d')"
password="$(echo "cat /config/global/resources/default_setup/connection/password/text()" | xmllint --nocdata --shell $dbXmlPath | sed '1d;$d')"
dbName="$(echo "cat /config/global/resources/default_setup/connection/dbname/text()" | xmllint --nocdata --shell $dbXmlPath | sed '1d;$d')"

fileName=$projectName-$(date +"%Y-%m-%d")
printf "What kind of backup you would like?\n[ d ] DB backup only\n[ f ] Files backup only\n[ b ] Files backup with DB\n"
read backupType
if [[ $backupType = @(d|b) ]]; then
	echo "----------------------------------------------------"
	echo "Dumping MySQL..."
	mysqldump -h $host -u $username -p$password $dbName > $fileName.sql
	echo "Done!"

if [[ $backupType = @(f|b) ]]; then
	echo "----------------------------------------------------"
	echo "Archiving Files..."
	printf "Skip /media folder?\ny: Yes\nn: No\n"
	read skipMedia
	if [ $skipMedia == y ]; then
		tar -zcf $fileName.tar.gz --exclude=var --exclude=includes --exclude=media * .htaccess
		tar -zcf $fileName.tar.gz --exclude=var --exclude=includes * .htaccess
	echo "Done!"
	echo "----------------------------------------------------"
	echo "Cleaning..."
	rm -f $fileName.sql
	echo "Done!"

if [[ $backupType = @(d|f|b) ]]; then
	echo "----------------------------------------------------"
	mkdir -p $backupDir;
	echo "Moving file to backup dir..."
	if [ $backupType == d ]; then
		mv $fileName.sql $backupDir

	if [[ $backupType = @(f|b) ]]; then
		mv $fileName.tar.gz $backupDir
	echo "Done!"
	echo "Invalid selection!"

Or you can download it from [here]
Notes: If you get the following error:

syntax error in conditional expression: unexpected token `(‘
line 24: syntax error near `@(d’
line 24: `if [[ $backupType = @(d|b) ]]; then’

then this means you are using older version of bash (< 4.0). And you need to patch the script by adding the following line after bash script declaration:[code language="bash"] shopt -s extglob [/code]

What does this script do?

1. Gives options for backup type which are:
– DB backup only
– Files backup only
– Files backup with DB
2. Dumps the database by taking DB info from XML configuration.
3. Makes a copy of project files and compresses it in .tar.gz format.
While copying it also gives options whether to exclude media folder or not.
Note that by default ‘var’ & ‘includes’ folder are excluded by the script.
4. Deletes the dumped SQL file as it’s already copied in the compressed file.
5. Creates backup dir if not exists
6. Copies the compressed project file to the backup dir.

Besides, you can also setup Cron job to run this backup script at regular intervals.

How to run backup script?

1. Edit to configure variables ‘projectName’ & ‘backupDir’
2. Upload the edited to the root of your Magento installation
3. Run following series of commands in terminal:

cd /path/to/magento/root
chmod +x
ex -sc $'%s/\r$//e|x'

4. You will get the compressed backup file at backup dir

Snapshots for different backup types

DB backup only

Files backup only

Files backup with DB

Let’s comment if there’s any room for improvement in this script.
Thanks for reading & sharing.