MySQL—Some Handy Know-How

From an article on Linux Journal http://www.linuxjournal.com/content/mysql%E2%80%94some-handy-know-how below are the commands to get you quickly up and running with MySQL. But the Linux Journal Site will provide many more examples and sample data etc.

Create database phplogcon and assign rsyslog access rights:

mysql -u root -p
create database phplogcon;
GRANT ALL ON phplogcon.* TO [email protected] IDENTIFIED BY "password";

Check database and connection with rsyslog works:

mysql -u rsyslog -p
connect phplogcon;
show tables;
quit

Create User:

CREATE USER 'keith'@'localhost' IDENTIFIED BY 'mypass';

Some basic / useful commands are as follows :

- connect to MySQL 
 
   mysql -uUsername -pPassword 
 
- connect to MySQL , directly to a database 
   
   mysql -uUsername -pPassword DbName 
 
- upload a MySQL schema into my Database 
 
   mysql -uUsername -pPassword DbName < schema.sql 
 
- dump a DB (copy DB for backup) 
 
   mysql -uUsername -pPassword DbName > contents-of-db.sql 
 
While connected to MySQL : 
 
- display all databases 
 
   show databases; 
 
- connect to a Database 
 
   use DbName; 
 
- view tables of a Database (must be connected to the Database) 
 
   show tables;

Create MySQL User for Backups e.g. sqlbu

CREATE USER 'sqlbu'@'localhost' IDENTIFIED BY  '***';
GRANT SELECT, SHOW VIEW, RELOAD, SHOW DATABASES, LOCK TABLES, EVENT, TRIGGER ON *.* TO 'sqlbu'@'localhost';

MySQL Backup Script to backup all Databases including new DBs added in the future:

#!/bin/bash
#=================================================================
# Backup script for MySQL Databases - this script will
# backup all MySQL Databases including any future additional DB's
#=================================================================
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/var/mysqlbu/$TIMESTAMP"
MYSQL_USER="sqlbu"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="Some really long complex password"
MYSQLDUMP=/usr/bin/mysqldump
MAILTO="[email protected]"

mkdir -p "$BACKUP_DIR/mysql"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --events --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done

ls -lh $BACKUP_DIR/* > /var/mysqlbu/mysqlbu.rpt
mail -s "MySQL BU Notification - $TIMESTAMP" -a "From: [email protected]" $MAILTO < /var/mysqlbu/mysqlbu.rpt
# Cleanup older directories than 60 days
find /var/mysqlbu/ -type d -mtime +60 -prune -exec rm -rf {} \;
#find $BACKUP_DIR/ -type d -mtime +60 -exec rm -rf {} \;

How to block network traffic by country on Linux

As a system admin who maintains production Linux servers, there are circumstances where you need to selectively block or allow network traffic based on geographic locations. For example, you are experiencing denial-of-service attacks mostly originating from IP addresses registered with a particular country. In other cases, you want to block SSH logins from unknown foreign countries for security reasons. Or your company has a distribution right to online videos, which allows it to legally stream to particular countries only. Or you need to prevent any local host from uploading documents to any non-US remote cloud storage due to geo-restriction company policies.

Instructions and more details from this site:

http://xmodulo.com/block-network-traffic-by-country-linux.html

 

How to remove unused kernel images from CentOS Linux system

Every time you update your CentOS Linux and the update includes a new kernel image update the system will not remove your old kernel but it will cumulatively add new kernel to the top of your Linux kernel installed list. Normally, this does not present any issue to your running system and you are not required to take any action to remove any old and unused kernel images.

The reason why you may wish to remove/uninstall unused kernel images is that you need to reduce disk usage space of your system, especially if your /boot mount point is mounted separately and has a limited disk space

df -h /boot/
rpm -q kernel
uname -r
yum install yum-utils
package-cleanup --oldkernels --count=1
rpm -q kernel

Reference:

http://linuxconfig.org/how-to-remove-unused-kernel-images-from-centos-linux-system

 

Sysdig

Sysdig is open source, system-level exploration: capture system state and activity from a running Linux instance, then save, filter and analyze. Sysdig is scriptable in Lua and includes a command line interface and a powerful interactive UI, csysdig, that runs in your terminal. Think of sysdig as strace + tcpdump + htop + iftop + lsof + awesome sauce.
With state of the art container visibility on top.

http://www.sysdig.org/

How To Verify SSL Certificate From A Shell Prompt

How do I verify and diagnosis SSL certification installation from a Linux / UNIX shell prompt? How do I validate SSL Certificate installation and save hours of troubleshooting headaches without using a browser? How do I confirm I’ve the correct and working SSL certificates?

http://www.cyberciti.biz/faq/test-ssl-certificates-diagnosis-ssl-certificate/

Nginx Error_log Configuration

Nginx serves millions of applications on a daily basis, and those millions of apps generate errors. Guess what? So do yours. Some of them are critical, while others are just notices. The important thing here is to get access to those errors to debug and have a clear view of what’s happening with your web applications.

In this article, you will explore the Nginx error_log configuration and how Nginx handles the types of errors that can be logged into the files.

http://www.nginxtips.com/nginx-error_log-configuration/

Duplicati Backup Software

Duplicati is a backup client that securely stores encrypted, incremental, compressed backups on cloud storage services and remote file servers. It works with Amazon S3, Windows Live SkyDrive, Google Drive (Google Docs), Rackspace Cloud Files or WebDAV, SSH, FTP (and many more). Duplicati is open source and free.

Duplicati has built-in AES-256 encryption and backups can be signed using GNU Privacy Guard. A built-in scheduler makes sure that backups are always up-to-date. Last but not least, Duplicati provides various options and tweaks like filters, deletion rules, transfer and bandwidth options to run backups for specific purposes.

Reference and Download:
http://www.duplicati.com

Backup and Restore permissions in Linux

You can use getfacl and setfacl to take a backup of permissions from a directory and then restore those permissions back if required at a later date.

The following directory of /perms contains these permissions:

/perms/
|-- [-rw-r--r--] permfile1.txt
|-- [-rw-r--r--] permfile2.txt
|-- [drwxr-xr-x] subperm1
| |-- [-rwxr-xr-x] permfile1.txt
| `-- [-rw-r--r--] permfile2.txt
`-- [drwxr-xr-x] subperm2
|-- [-rw-r--r--] permfile1.txt
`-- [-rwxr-xr-x] permfile2.txt

To Backup this directory and sub-directory permissions you can run:

# getfacl -R /perms > perms_bu

The contents of the perms_bu file will contain all the permission details for both directories and files. Should any file or directory permissions be changed or modified in the future, we can restore as show below with the perms_bu file

To restore the permissions:

# setfacl --restore=perms_bu

NOTE: When you restore the permissions be sure you are in the relevant path area e.g. for the above example run the restore from the root area /

Reference:
Linux Man pages or http://linux.die.net/man/1/getfacl

 

Rootcheck

Rootcheck is an open source command line tool that looks for indicators of compromise on Linux or BSD systems. It tries to find known backdoors, kernel-level rootkits, malware and insecure configuration settings. It is included as part of OSSEC, but can also be executed separately from here as needed. If you suspect your server has been compromised it will certainly help with your investigation.

Install and run details can be found here: http://dcid.me/rootcheck

Also read this blog post titled “Investigating a Compromised Server with Rootcheck