SelectEtoile : Base de connaissance SGBD

Posts Tagged 'scripts'

Sauvegarde / Restauration

Sauvegarde / restauration d'une table : 

Bcp out :

  • mysql> SELECT a,b,a+b INTO OUTFILE '/tmp/jp1.txt'
  • -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  • -> LINES TERMINATED BY '\n'
  • -> FROM jp1;

 

Bcp in :

  • mysql> LOAD DATA INFILE "/tmp/jp1.txt" INTO TABLE jp1
  • -> FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
  • -> LINES TERMINATED BY '\n';
  • LOAD DATA INFILE 'ta_syb_aseconfig_total.out' INTO TABLE ta_syb_aseconfig
  • FIELDS TERMINATED BY ';' ENCLOSED BY '' ESCAPED BY '\\'
  • LINES TERMINATED BY '\n';

 

Sauvegarde d'une Base de données

mysqldump [options] db_name [tables]

Sauvegarde : mysqldump -u root -ppwd --opt mabase -h monServeur > backup-production.sql

Exemple : mysqldump --socket=mysql.sock --user=root --password=pwd testjp > /tmp/testjp.sql

La commande dump permet de générer le script de création des objets et de la base elle même. Ci-dessous, un exemple de résultat de la commande :

 

-- MySQL dump 10.9

--

-- Host: localhost Database: testjp

-- ------------------------------------------------------

-- Server version 4.1.11-max-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `jp1`

--

 

DROP TABLE IF EXISTS `jp1`;

CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `jp1`

--

 

 

/*!40000 ALTER TABLE `jp1` DISABLE KEYS */;

LOCK TABLES `jp1` WRITE;

INSERT INTO `jp1` VALUES (3,8),(1,1),(10,10),(6,7),(5,5),(8,9);

UNLOCK TABLES;

/*!40000 ALTER TABLE `jp1` ENABLE KEYS */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

Il faut prévoir un flush des logs binaire avant la sauvegarde full :

Soit lancer un 'flush logs' dans mysql

Soit ajouter l'option --flush-logs dans la commande mysqldump

 

Autre exemple de mysqldump :

mysqldump --opt ${DATABASE} --databases --routines --triggers --single-transaction --master-data=2 --hex-blob --flush-logs

--master-data=2 permet de mettre une ligne de commentaire dans le fichier généré donnant le binlog courant ainsi que la position
--single-transaction : permet de rendre transactionnel la sauvegarde et donc consistant
--hex-blob : Sauvegarde les colonnes binaires utilisant La notation hexadecimale
 
 

 

 

Restauration d'une base de données :

Restauration d'un dump full :

mysql -u root -ppwd -h monServeur -D mabase2 < backup-production.sql

Exemple : ‘mysql testjp < /tmp/testjp.dump’

 

Restauration des log binaire :

mysqlbinlog mysql-bin.000062 mysql-bin.000063 | mysql


Sauvegarde / restauration table (vont disparaître)

  • BACKUP TABLE table [, table] ... TO '/repertoire/de/sauvegarde/';
  • RESTORE TABLE table [, table] ... FROM '/repertoire/de/sauvegarde/';

 

 

Sauvegarde a l’aide de mysqlhotcopy (Prérequis : Perl et packages DBI)

  • mysqlhotcopy db_name [/répertoire/de/sauvegarde]
  • mysqlhotcopy -u root -ppwd mabase /mstoto/sgbd/sauvegarde/

 

 

 

 

 

Petit guide des commandes

 

Les fonctions MySql :

  • select version(), current_date;
  • SELECT nom, naissance, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(naissance)), (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5)) AS age FROM animal;

  • select user();

 

Les Commandes SHOW :

  • show databases;
  • show columns from monDataCache;
  • show create table monDataCache;
  • show create database monitor_db;
  • show databases;
  • SHOW ERRORS [LIMIT [offset,] row_count]
  • SHOW COUNT(*) ERRORS
  • show engines;
  • show engines\G;
  • show grant for monty;
  • show innodb status;
  • show logs;
  • show privileges;
  • show processlist (ou mysqladmin process-list) : permet de voir qui est connecté à la base
  • show status; (ou mysqladmin extended-status)
  • show tables;
  • show tables status;
  • show variables
  • show variables like 'max_error_count';
  • show warnings; (suite a une requete avec des warnings)
  • SHOW COUNT(*) WARNINGS;

 

Les options :
 

  • L'attribut AUTO_INCREMENT peut être utilisé pour générer un identifiant unique pour les nouvelles lignes
  • Set autocommit=1 : utilisé pour la recréation rapide de la table

Les scripts natifs :

  • mysqlcc (MySQL Control Center) est un client interactif graphique, pour exécuter des commandes SQL, et administrer le serveur
  • mysqladmin est un client d'administration
  • mysqlcheck effectue les opérations de maintenance sur les tables
  • mysqldump et mysqlhotcopy font les sauvegardes de bases
  • mysqlimport importe des fichiers de données
  • mysqlshow affiche des informations sur les bases et les tables
  • mysql_secure_installation : permet de securiser l'installation de mysql

ex  : mysqladmin extended-status  (-r diff; -i 1 intervalle)

Les variables :

  • SELECT @@warning_count;

 

Exemples de commandes :

mysql> show create database testjp;

+----------+-------------------------------------------------------------------+

| Database | Create Database |

+----------+-------------------------------------------------------------------+

| testjp | CREATE DATABASE `testjp` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> use testjp

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show create table jp1;

+-------+--------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------+

| jp1 | CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> SHOW GRANTS FOR fsarasy;

+-----------------------------------------------------------------------------------------------------------------+

| Grants for fsarasy@% |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'fsarasy'@'%' IDENTIFIED BY PASSWORD '*63D85DCA15EAFFC58C908FD2FAE50CCBC60C4EA2' |

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

Requetes divers sur SYBASE ASE

Autojointure à regroupement :

SELECT col1, col2, count(*)

FROM tab

GROUP BY col1, col2

ORDER BY col1, col2

compute sum(count(*)) BY col1

compute sum(count(*))

 

 

Modification format de date :

 

02/02/02 en 02-02-02 :

select object_name(id), stuff(stuff(convert(varchar, moddate, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), moddate, 108),

 

 

Feb 7 2005 12:53PM en 20050207121240

convert(varchar, moddate, 112) + substring(convert(char(8), moddate, 108) , 1, 2) + substring(convert(char(8), moddate, 108) , 4, 2) + substring(convert(char(8), moddate, 108) , 7, 2)

 

 

 

Commandes sur la gestion des dates

2 format de data différents

declare @dat datetime, @dat2 smalldatetime
select @dat = getdate()
select @dat2 = getdate()
select @dat, @dat2


Difference entre 2 dates :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select datediff (hh,@dat1,@dat2)


Ajout 1 heure :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select dateadd (hh,+1,@dat2)


Affichage avec un format particulier

select convert (char(26),getdate(),108)

 

Affichage de la date du jour :

- 3 mois : select convert (smalldatetime, Dateadd (mm, -3, getdate()))

- 2 jours : select convert (smalldatetime, Dateadd (dd, - 2, getdate()))



Mise a jour des threshold

select "use "+db_name(dbid)+char(10)

+"go"+char(10)+

"sp_addthreshold @dbname='"+db_name(dbid)+"',@segname='logsegment',@free_space="+ convert( char(8), sum(size)*10/100)

+" , @proc_name='sp_thresholdaction' "+

char(10)+"go"+char(10) as "--seuil 30%"

from master..sysusages where segmap=4 group by db_name(dbid)



Utilisation de patindex et substring :

extraire dans S_AnoDescription, les 50 caractères après la chaine @ModuleName.

select distinct

G_PROCESSUS.G_PrsName,S_LOG.G_PrsID,

Substring(convert (varchar(1500),S_AnoDescription), patindex('%@ModuleName%',S_ANOMALY.S_AnoDescription) ,50 ),

S_ANOMALY.S_AnoID --,S_ANOMALY.S_LogID

from S_ANOMALY, S_LOG, G_PROCESSUS

where S_AnoCode in (506, 5003) AND

S_ANOMALY.S_LogID = S_LOG.S_LogID

AND G_PROCESSUS.G_PrsID= S_LOG.G_PrsID




Visualisation des Logins Externes :

SELECT  Server = s.srvname,

            Login = l.name,

            Externlogin = a.object_cinfo

        FROM    master.dbo.sysattributes a,

            master.dbo.sysservers s,

            master.dbo.syslogins l

        WHERE   a.class = 9 AND

            a.object_type = "EL" AND

            a.object_info1 = s.srvid AND

--          s.srvname LIKE @server AND

            a.object = l.suid