Mysql

Installation

Web site MySql

Package Installation

• MySql Server

apt-get install mysql-server

• Recommended

apt-get install mysql-workbench phpmyadmin

Setup

• To be accessible from another server remove the bind from /etc/mysql/mysql.conf.d/mysqld.cnf

###Nr001 bind-address           = 127.0.0.1

Syntax

Data Manipulation

Basic

• Select

SELECT columna, columnb FROM mytable;
SELECT columna as alias1, columnb as alias2 FROM mytable;

Special

• Dummy Table

SELECT 1+1;
SELECT 1+1 FROM DUAL;

• Concatenate

SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable;

Joint

SELECT m.MenuName, o.MenuOption FROM `menuoption` o left outer join `menu` m   on o.MenuId=m.id;

Data Definition

Basic

Create Database

CREATE DATABASE mydatabase;
CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

Create User

CREATE USER username IDENTIFIED BY 'userpassword';

GRANT ALL PRIVILEGES ON mydatabase.* TO myuser@localhost IDENTIFIED BY 'myuser_password';

Special Case

TimeStampe

CREATE TABLE test_table( 
  Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  CreatedTime TIMESTAMP DEFAULT '0000-00-00 00:00:00', 
  UpdatedTime TIMESTAMP DEFAULT NOW() ON UPDATE NOW() 
);

Maintenance

Binary File

• Binary file are usefull to recuperate a database in any point of time
• There are usefull for replication
• But as all informationare written into the logfile before updating the database, you may expect an impact on the perfomance

Setup

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 250M
binlog_do_db            = include_database_name
binlog_format           = 'MIXED'

The binary logs in mysql-bin.index is expected to be numerically consecutive. If the binary logs are not numerically consecutive, log rotation is disabled.

Script for removing binary file

Config File

#/bin/bash
#DB Connection paramters
DBHOST=0.0.0.0
DBNAME=test
DBUSER=dbuser
DBUSERPWD=userpassword
DBPORT=3306

Script

#!/bin/bash

CONFIG_FILE='/exploit/sys_mysql_purgelog.conf'

DBHOST=$(cat $CONFIG_FILE | grep -w DBHOST | awk -F'=' '{ print $2 }' )
DBUSER=$(cat $CONFIG_FILE | grep -w DBUSER | awk -F'=' '{ print $2 }' )
DBUSERPWD=$(cat $CONFIG_FILE | grep -w DBUSERPWD | awk -F'=' '{ print $2 }' )
DBPORT=$(cat $CONFIG_FILE | grep -w DBPORT | awk -F'=' '{ print $2 }' )

purgedt=$(mysql -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "select date_sub(current_date(), interval 7 day)" -s -N )
echo "Purge binary logs before $purgedt started at $(date +%m%d%y_%H:%M)"
str="PURGE BINARY LOGS BEFORE '"$purgedt"'"
mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "$str"

success=$?

if [ $success = 0 ];
then
   echo "Purge binary logs before $purgedt completed at $(date +%m%d%y_%H:%M)"
else
   echo "!!!Failed!!!"
fi     #if [ $success = 0 ] then

exit 0

Backup Restore

Backup all database

mysqldump --all-databases --single-transaction --user=root --password > all-database.sql

Backup a specific database

mysqldump -h server -p database --add-drop-table --single-transaction --user=root --password