ASIX-M10-UF2. Administració d'un SGBD. MySQL

Introducció

A classe hem estudiat el següent document:

• Fitxer:Shell Scripting for MySQL Administration An Introduction Presentation.pdf

En aquest document es presenten tècniques i exemples de scripts programats amb bash script per automatitzar tasques relacionades amb l'administració d'una base de dades MySQL. El document dóna una panoràmica bastant completa dels avantatges i possibilitats de què disposa l'administració de sistemes per tal de controlar de forma automàtica, preventiva i desatesa el rendiment i seguretat del seu servidor de MySQL.

Per realitzar la pràctica has de disposar de la base de dades langtrainer.

Scripts d'administració amb Bash

Petits exemples. Executar una instrucció SQL

Si el que vols és executar una sola comanda, ho pots fer de dues maneres. La primera manera és utilitzar un pipe:

#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('10.16.54.29', '00:f8:e5:33:22:3f', 'marsara');" | mysql -uroot -ptest test;

La segona manera és utilitzar l'opció -e del mysql:

#!/bin/bash
mysql -uroot -ptest -e "INSERT INTO test (IP,MAC,SERVER) VALUES ('10.16.54.29', '00:f8:e5:33:22:3f', 'marsara');" test;

També podem executar vàries instruccions alhora:

#!/bin/bash

###Variables
username="user1"
password="user1"

databasename="database1"

sudo mysql -u rootuser -prootpassword mysql -e "CREATE DATABASE $databasename; GRANT ALL PRIVILEGES ON  $databasename.* TO $username@localhost IDENTIFIED BY $password; FLUSH PRIVILEGES;"

Petits exemples. Executar instruccions SQL per lots

Per importar un script SQL (fitxer de text que conté comandes SQL) ho pots fer de la següent manera:

$ mysql -u alumne -pkeiL2lai langtrainer < langtrainer.sql

Això també funcionarà dins d'un script bash.

Recorda que pots utiltizar les opcions > /dev/null 2>&1 per redirigir els errors a la consola.

Ara bé, si tens un fitxer de text amb múltiples files i dades separades per espais, aquest script també et funcionarà:

#!/bin/bash
inputfile="test.txt"
cat $inputfile | while read ip mac server; do
    echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');"
done | mysql -uroot -p test;

fitxer test.txt:

192.168.1.10 00:34:32:43:23 SER1
192.168.1.11 00:34:32:43:24 SER2
192.168.1.12 00:34:32:43:25 SER3
192.168.1.13 00:34:32:43:26 SER4
192.168.1.14 00:34:32:43:27 SER5
192.168.1.15 00:34:32:43:28 SER6

create database test;

use test;

create table test(
IP varchar(20),
MAC varchar(20),
SERVER varchar(20)
);

També ha de funcionar el següent exemple:

#!/bin/bash

mysql -uroot -ptest test << EOF

LOAD DATA INFILE 'test.txt'
    INTO TABLE tbl_name
    FIELDS TERMINATED BY ' ';

EOF

Proporcionar les dades de connexió

Anem a veure-ho de dues maneres. En la primera manera s'espera que l'usuari introdueixi els valors pel teclat.

script demanar_dades.sh:

#!/bin/bash
username=""
password=""
database=""

read -e -p "Username: " username
read -e -p "Password: " password
read -e -p "Database: " database

echo
mysql -ss -u $username -p$password -D $database -e 'show tables;'

Recorda que pots utilitzar les opcions -s o -ss (silent), per tal de què no et surtin les línies de separació, i per tal de què a més no et surti la capçalera.

També pots passar les dades de connexió en la línia de comandes:

script vocabulari.sh:

#!/bin/bash
mysql -s -u $1 -p$2 -D $3 -e 'select * from word;'

Per executar-lo:

$ ./vocabulari.sh alumne keiL2lai langtrainer
id_word	id_language	id_login	word	probability	day	day_learnt
2	1	1	weird	100	2009-09-01	NULL
3	1	1	gills	100	2009-09-01	NULL
4	1	1	draw breath (to)	100	2009-09-01	NULL
5	1	1	draw (to)	100	2009-09-01	NULL
6	1	1	snout	100	2009-09-01	NULL
...

Accedir al mysql sense que demani el password

Ho farem de dues maneres. La primera manera és editar el fitxer ocult .mysql_access en el home directori. Està explicat a la pàg 73 del pdf.

En la segona manera posarem el password en el script, però que estigui ofuscat (que no és el mateix que encriptat).

script password_ofuscat.sh:

#!/bin/bash
MYUSER=alumne
MYPASS=$(echo "9ke88iLbb2lbai4343" | tr -s "8" | tr -s "9" | tr -s "b" | tr -s "43" | tr -d "43" | tr -d "b" | tr -d "8" | tr -d "9" )
MYDB=langtrainer
#echo $MYPASS
mysql -s -u $MYUSER -p$MYPASS -D $MYDB -e 'show tables;'

Documentat amb la comanda tr i les opcions -s i -d. Fixa't com ofusquem el password de l'usuari alumne:

$ echo "9ke88iLbb2lbai4343" | tr -s "8" | tr -s "9" | tr -s "b" | tr -s "43" | tr -d "43" | tr -d "b" | tr -d "8" | tr -d "9"
keiL2lai

opció --defaults-extra-file

Podem posar les credencials per fer la connexió a mysql en un fitxer de configuració local:

$ sudo joe /etc/mysql/login.cnf

[client]
user = "root"
password = "********"
host = "localhost"

$ sudo chmod a+r /etc/mysql/login.cnf

(no li hem de donar permisos d'escriptura, només de lectura)

D'aquesta manera, ara la connexió del client al servidor és molt fàcil:

$ mysql --defaults-extra-file=/etc/mysql/login.cnf
mysql>

Utilitat mysqladmin en mode consola

font:

• http://www.xaviermedia.com/cloud-computing/bash-script-to-check-if-mysql-is-running-on-your-amazon-ec2-restart-if-needed/

mysqladmin és una utiltiat per realitzar tasques administratives des de la consola, i per tant és fàcilment integrable en els teus scripts bash.

$ man mysqladmin
NAME
       mysqladmin - client for administering a MySQL server

SYNOPSIS
       mysqladmin [options] command [command-options] [command  [command-options]]

Per exemple, aquí tens un script per arrencar el mysql en cas de què estigui apagat:

script arrencar_mysql.sh: (L'has d'executar com a sudo)

#!/bin/bash
/usr/bin/mysqladmin --defaults-extra-file=/etc/mysql/login.cnf ping | grep 'mysqld is alive' > /dev/null 2>&1
if [ $? != 0 ]
then
    /etc/init.d/mysql stop
    /etc/init.d/mysql start
    echo Starting at `date`
else
   echo mysql ja està arrencat
fi

En el cas de què el servidor estigui arrencat, el ping respon amb un mysqld is alive, i aleshores no s'executa el script. En el cas de què el servidor estigui apagat per algun motiu, respon amb algun missatge d'error (mysqladmin: connect to server at 'localhost' failed...) i això provoca que s'executi el script.

Veiem com arrenca:

$ sudo /etc/init.d/mysql stop
$ sudo arrencar_mysql.sh
...
Stopping mysql (via systemctl): mysql.service.
Starting mysql (via systemctl): mysql.service.
Starting at dilluns, 14 de desembre de 2020, 13:28:29 CET

Podem executar aquest script cada hora per tal de vigilar que el mysql estigui arrencat. En cas de què no ho estigui pels motius que sigui, arrenquem el servidor mysql.

cronificar el script

Per tal d'executar aquest script cada hora fem servir crontab:

$ sudo joe /etc/crontab

* */1 * * * root /home/joan/scripts/arrencar_mysql.sh

$ sudo /etc/init.d/cron restart

Còpies de seguretat

Aquí tens un exemple de script per fer còpies de seguretat automàtiques d'una base de dades.

Característiques a comentar d'aquest script:

• és desatès: no demana el password
• fa còpies de vàries bases de dades.
• guarda les còpies en una carpeta mysql_backup/
• el nom de la còpia porta incorporada la data del sistema com a sufix.
• s'utiltiza cron per tal d'automatitzar la còpia.
• Genera un log senzill per controlar que s'ha realitzar la còpia de seguretat.

script copia_seguretat.sh: (fa les còpies de seguretat de les bd: langtrainer, sakila, municipis)

#!/bin/bash
MYSQLUSER=root
MYSQLPASSWORD=******

export MYSQLUSER MYSQLPASSWORD

tdate=`date +%y%m%d`
#echo $tdate
     
if ! [ -d /home/joan/copia_seguretat/copia_mysql ] 
then
#if folder pg_backup_'databsename' does not exist in the current users home dierectory then create a new folder
mkdir /home/joan/copia_seguretat/copia_mysql
fi

#fem còpies individuals de les bases de dades
mysqldump --add-drop-table -i -C -u $MYSQLUSER -p$MYSQLPASSWORD -r /home/joan/copia_seguretat/copia_mysql/copia_langtrainer$tdate.dmp -v langtrainer
mysqldump --add-drop-table -i -C -u $MYSQLUSER -p$MYSQLPASSWORD -r /home/joan/copia_seguretat/copia_mysql/copia_sakila$tdate.dmp -v sakila
mysqldump --add-drop-table -i -C -u $MYSQLUSER -p$MYSQLPASSWORD -r /home/joan/copia_seguretat/copia_mysql/copia_municipis$tdate.dmp -v municipis
echo Còpia seguretat $tdate >> /home/joan/copia_seguretat/copia_mysql/copies.log

#reset MYSQLUSER and MYSQLPASSWORD  
MYSQLUSER=""
MYSQLPASSWORD=""
export MYSQLUSER MYSQLPASSWORD

#necessari el codi d'èxit per tal que no es torni a executar el script si el poso en el crontab
exit 0

Enviament de correus electrònics des de Bash amb un compte de gmail

NOTA: aquesta activitat és opcional. Actualment gmail/google bloqueja per defecte l'ús d'aplicacions no segures. I enviar un correu electrònic en nom de gmail (jaumebalmes.net) es considera no segur. Per tant, per tal de què funcioni s'ha de permetre les aplicacions menys segures (DESACTIVAT -> ACTIVAT) en la configuració del compte de Gmail.

Està explicat a:

• Scripting._Alertes._Accés_a_BD_i_enviament_de_mails_des_de_bash#Enviar_mails_des_de_la_consola_amb_Postfix_i_Gmail_SMTP

Però la manera més senzilla (comprovat el gener 2020) ha estat seguir el següent enllaç, que ha funcionat a la primera:

• http://askubuntu.com/questions/522431/how-to-send-an-email-using-command-line

En comptes d'utiltizar el certificador que proposen (Thawte_Premium_Server_CA.pem), s'ha utilitzat: /etc/ssl/certs/thawte_Primary_Root_CA_-_G2.pem

Si vols reconfigurar el postfix (torna a modificar el fitxer /etc/postfix/main.cf):

 
$ sudo dpkg-reconfigure postfix

Per veure els logs (et pot donar una pista de si no reps els correus):

$ sudo tail -f /var/log/syslog

Si obtenim un missatge tipus:

SASL authentication failed; server smtp.gmail.com[74.125.140.108] said: 535-5.7.8 Username and Password not accepted. Learn more at?535 5.7.8  https://support.google.com/mail/?p=BadCredentials m8sm31320034wmc.27 - gsmtp

significa que per motius de seguretat Gmail no envia el correu.

• https://support.google.com/mail/?p=BadCredentials

https://myaccount.google.com/u/0/security?hl=ca Configuració del compte de Google, seguretat. Permet aplicacions menys segures: DESACTIVAT -> ACTIVAT

Incorporar enviament de mail en els vostres scripts

Per als administradors de sistema és interessant aquesta opció. Per exemple, acabem de fer un script de còpia de seguretat. Podem enviar el log de la còpia a l'administrador de sistemes com a document adjunt. Abans també hem vist que podem detectar quan el servidor mysql està apagat. Podem enviar un mail a l'administrador quan detectem aquesta circumstància.

2a versió del script arrencar_mysql.sh:

#!/bin/bash
/usr/bin/mysqladmin --user=root --password=***** ping| grep 'mysqld is alive' > /dev/null 2>&1
if [ $? != 0 ]
then
    /etc/init.d/mysql stop
    /etc/init.d/mysql start
    echo Starting at `date`
    echo "Servidor mysql aturat. Es torna a arrencar" | mail -s "arrencar mysql" joanqc@gmail.com
fi

Inserció de logs de totes les tasques

Recorda que en tots els scripts que realitzis pots generar logs. Un exemple molt bàsic: script:

#!/bin/bash
tdate=`date +%d%m%y`
#echo $tdate

ping -c 3 192.168.0.15 > $HOME/logs/ping_$tdate.log

L'administrador de sistemes es pot organitzar de manera que cada dia a les 9 del matí la primera tasca que realitzi serà de revisar els logs que s'han generat per tal d'assegurar-se de què tot va bé.

nota. Millor fer >> per tal de fer un append en el fitxer de log:

ping -c 3 192.168.0.15 >> $HOME/logs/ping_$tdate.log

Scripts d'administració amb Python

El python està esdevenint un dels llenguatges de scripting preferits per als administradors de sistemes. Veurem un exemple de com podem accedir al mysql des de Python.

Seguim el següent tutorial:

• http://www.tutorialspoint.com/python/python_database_access.htm

$ sudo apt-cache search python | grep mysql
python-mysqldb - Python interface to MySQL

$ sudo apt-get install python3-mysqldb

compte!: suposem que tenim instal·lat el python3

En l'ordinador del professor no s'ha hagut d'instal.lar el mòdul de Mysql per a python. És possible que tu tampoc ho hagis de fer. N'hi ha prou en executar el següent script per saber si tens instal.lat el mòdul:

#!/usr/bin/python3

import MySQLdb

En el següent exemple farem una select de la taula langtrainer.word:

script python_langtrainer.py:

#!/usr/bin/python3

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","alumne","keiL2lai","langtrainer" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM word"
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      id = row[0]
      word = row[3]
      # Now print fetched result
      print('id %s, word: %s' %(id,word)) #python2 i 3

except:
   print ("Error: unable to fecth data")

# disconnect from server
db.close()

Per executar-lo:

$ ./python_mysql.py 
id=2,word=weird
id=3,word=gills
id=4,word=draw breath (to)
...

Entrega

Realitza els següents exercicis:

• Apartat Petits exemples. Realitza els exercicis que se't proposen. Has de crear la taula test.
• Apartat Proporcionar les dades de connexió.
• script per accedir al mysql sense que demani el password. Fes-ho de les dues maneres (dos versions del script).
• cronifica el script d'arrencada del servidor mysql. Executa'l cada minut perque puguis veure fàcilment de què funciona bé. Es tracta d'aturar manualment el mysql, i després d'un minut comprovar que està arrencat.
• Fés un script per fer còpies de seguretat automàtiques de la base de dades langtrainer. Copia els arxius de còpia $HOME/copia_langtrainer. Genera fitxers de log en la carpeta $HOME/copia_langtrainer/logs.
• script vocabulari_langtrainer.py. Partint del script python que se't proporciona, volcaràs en un fitxer de text el vocabulari que hi ha a la base de dades, juntament amb la traducció de les paraules. (combinar les taules word i translation com ja hem fet d'altres vegades).
• opcional. Fés la segona versió del script arrencar_mysql.sh per tal de notificar amb un correu electrònic quan el servidor mysql ha caigut. Pots enviar-te un correu electrònic a tu mateix, i també al professor: jquintana@jaumebalmes.net. Si el professor rep notificacions tindràs un punt positiu :-).
• opcional. Tots els exemples els has fet amb mysql. Agafa algun dels exemples i adapta'l per fer-lo amb una base de dades Postgres.

Referències

Mira't aquest resum de MySQL que ha fet un company. Encara que el document ja té uns anys, és plenament vàlid.

• http://www.xtec.cat/~acastan/textos/Administracion%20de%20MySQL.html

El pdf original que hem seguit a classe està a:

• http://cdn.oreillystatic.com/en/assets/1/event/21/Shell%20Scripting%20for%20MySQL%20Administration_%20An%20Introduction%20Presentation.pdf

ToDo

Cas 1

Si es detecta que el mysql està caigut, inserir una incidència automàticament en la taula d'un gestor d'incidències. De manera que si l'empresa/organització utilitza un gestor d'incidències (per exemple Mantis BT), aquesta queda ben registrada. insert into taula de incidències

Cas 2

Des de PHP es pot executar comandes de sistema, concretament executar scripts. Per tant, podem fer fàcilment un front-end amb PHP per realitzar tasques administratives, però en realitat les tasques administratives s'executen per sota llençant scripts bash.

---

creat per Joan Quintana Compte, gener 2019