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

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Introducció

A classe hem estudiat el següent document:

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

Utilitat mysqladmin en mode consola

font:

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 --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`
fi

Veiem com arrenca:

$ sudo /etc/init.d/mysql stop
$ sudo arrencar_mysql.sh
...
Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the start(8) utility, e.g. start mysql
mysql start/running, process 13900
Starting at dv gen 23 16:23:39 CET 2015

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:

La única cosa que es troba a faltar és la generació d'un log per tal de què l'administrador de sistemes pugui certificar de què la còpia ha anat bé.

Per generar un log pots utilitzar l'opció:

 $ man mysqldump | grep log
 ... 
 --log-error=file_name

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

Està explicat a:

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

En comptes d'utiltizar el certificador que proposen (Thawte_Premium_Server_CA.pem), s'ha utilitzat: thawte_Primary_Root_CA.pem

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:

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

$ sudo apt-get install python-mysqldb

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/python

import MySQLdb

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

script python_langtrainer.py:

#!/usr/bin/python

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 )
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:

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.

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

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

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines