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 mysql-create-db-user.sh

3# Usage
Use the script as:

./mysql-create-db-user.sh [--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:

./mysql-create-db-user.sh --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.

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:

#!/bin/bash
#@author		MagePsycho <magepsycho@gmail.com>
#@website		http://www.magepsycho.com
#@version		0.1.0

#/************************ EDIT VARIABLES ************************/
projectName=magepsycho
backupDir=/home/magepsycho/_backups
#/************************ //EDIT VARIABLES **********************/

dbXmlPath="app/etc/local.xml"
{
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!"
fi

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
	else
		tar -zcf $fileName.tar.gz --exclude=var --exclude=includes * .htaccess
	fi
	echo "Done!"
	echo "----------------------------------------------------"
	echo "Cleaning..."
	rm -f $fileName.sql
	echo "Done!"
fi

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

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

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 _magebackup.sh to configure variables ‘projectName’ & ‘backupDir’
2. Upload the edited _magebackup.sh to the root of your Magento installation
3. Run following series of commands in terminal:

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

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.

How to find the size / rows of Magento database & tables?

February 17, 2013  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

Magento is a giant e-commerce application having more than 300 tables. It uses an eav model concept and provides different complex features which makes the database huge.

Sometimes you may wonder what’s the database size of your Magento database or individual tables so that you can work on some optimization task or freeing some of your server space.
Here we are going to discuss some SQL queries which seems to be helpful.

SQL Snippets

1. Find size & rows of Magento database

SELECT 
  TABLE_SCHEMA AS "Database",
  SUM(TABLE_ROWS) AS "Rows #",
  ROUND(
    SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
 GROUP BY TABLE_SCHEMA;

2. Find size & rows of Magento database tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 

3. Find size & rows of Magento log tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
  AND (
    TABLE_NAME LIKE 'log_%' 
    OR TABLE_NAME LIKE 'report_%' 
    OR TABLE_NAME LIKE 'dataflow_%' 
    OR TABLE_NAME = 'catalog_compare_item'
  ) 
ORDER BY TABLE_ROWS DESC 

Magento log tables info

These queries can be useful for optimizing your database. Suppose say if log tables are huge with large no of data then you can optimize the log tables by truncating them using following SQL:

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
TRUNCATE catalog_compare_item;

Caution: Always take a DB backup before performing truncate operation.

Hope you found this article useful.
Thanks for reading!