Laravel Increment

 

Laravel Collection

 

Laravel Date Format

 

Laravel password rules

 

How to Schedule a Backup of All MySQL Databases on Ubuntu 16.04

By Tony Mackay

7 Nov 2017

This tutorial will show you how to create a shell script that will backup all of your MySQL databases and how to schedule the backup to run daily.

Step 1: Create the Backup Script

Create a folder to store your backup script in. I suggest /scripts for this example:

sudo mkdir /scripts

Create a file called mysql-backup.sh inside the scripts folder:

sudo vim /scripts/mysql-backup.sh

Add the following code to the file and save it:

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
USER='root'       # MySQL User
PASSWORD='webdev' # MySQL Password
DAYS_TO_KEEP=0    # 0 to keep forever
GZIP=1            # 1 = Compress
BACKUP_PATH='/backups/mysql'
#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

# Get list of database names
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`

for db in $databases; do

  if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
    echo "Skipping database: $db"
    continue
  fi
  
  date=$(date -I)
  if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"      
    mysqldump -u $USER -p$PASSWORD --databases $db > $BACKUP_PATH/$date-$db.sql
  else
    echo "Backing up database: $db with compression"
    mysqldump -u $USER -p$PASSWORD --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
  fi
done

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi

You will notice 5 configurable options at the beginning of this script. The main ones you will need to edit are the USER and PASSWORD. This should be changed to a MySQL user and password that has permissions to list and backup databases.

Once you have modified the options, make the script executable with the following command:

sudo chmod +x mysql-backup.sh

You can now test the backup script by running:

sudo ./mysql-backup.sh

Advertisement

Step 2: Create the Crontab Scheduled Task

Now we will schedule the backup script to be run daily. We will do this by adding a call to the mysql-backup.sh script to the root crontab.

Run the following command to open the root crontab file:

sudo crontab -e

Now add to the last line of the file the following:

@daily sh /scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Save the file and then wait for the script to run. You can check for errors in /var/log/mysql-backup.log. Once you are happy it is working, you can remove the >> /var/log/mysql-backup.log 2>&1 from the crontab file.

 

Laravel Special Query

 

$users = User::orderBy(Company::select('name')
    ->whereColumn('companies.user_id', 'users.id')
)->get();

date('d-m-y', strtotime($req->received_date)) 

$users = User::select('users.*')
    ->join('companies', 'companies.user_id', '=', 'users.id')
    ->orderBy('companies.name')
    ->get();

 $keyResult= DB::table('item_maps')
            ->join('suppliers','item_maps.sup_id', '=', 'suppliers.id')
            ->join('reqs', 'item_maps.id', '=' ,'reqs.item_id' )
            ->select('*')
            ->addselect(DB::raw('DATE_FORMAT(reqs.created_at, "%d/%m") as req_date'))
            ->where('order_status', 'no')
            ->where(function($query) use ($input){
                $query->where('item_name', 'LIKE', '%'.$input.'%')
                    ->orWhere('supplier_name', 'LIKE', '%'.$input.'%')
                    ->orWhere('req_id', 'LIKE', '%'.$input.'%');
            })
            ->get();
  

  $reqs= Req::orderBy(Item_map::select('sup_id')
            ->whereColumn('item_maps.id','reqs.item_id')
        )
            ->orderBy(Item_map::select('item_name')
                ->whereColumn('item_maps.id','reqs.item_id')

            )
            ->orderBy('req_id')
            ->where('order_status','no')
            ->where('outlet_name','Staff')
            ->get();
 

$purchase_adjustments = Purchase::where(Item::select('name')
    ->whereColumn('purchases.item_id', 'items.id'),"LIKE",$search)
    ->orWhere(Item::select('item_code')
    ->whereColumn('purchases.item_id', 'items.id'),"LIKE",$search)
    ->orderBy(Item::select('name')
        ->whereColumn('purchases.item_id', 'items.id'))
    ->paginate(10);