Archives pour la catégorie ‘MySQL’

MySQL : installation multi-instances

Dans le cas ou vous gérez plusieurs serveurs MySQL (distants ou sur différents VLAN), il peut être utile de déporter tout traitement long sur un seul serveur SQL dédié pour peu de regrouper l’ensemble des bases de données.

En attendant MariaDB 10 qui permettra la réplication multi-master, il est nécessaire de mettre en place plusieurs instances MySQL sur la même machine. Une réplication pourra ensuite être lancée sur chaque instance vers chaque serveur SQL source, configuré évidemment en master.

Voici donc un guide rapide de mise en place d’instances multiples MySQL (l’ancienne méthode tout à la main n’est plus nécessaire et c’est tant mieux).

Installation

root@sqlbackend:~# apt-get install mysql-server-5.5

Configuration

Je vais créer ici trois nouvelles instances mysql1, mysql2 et mysql3 ; chacune aura donc sa directive serveur. A noter que je ne supprime pas l’instance principale pour ne pas me ramasser à la prochaine mise à jour OS. Il faut cependant commenter l’option user sinon les instances supplémentaires ne se lanceront pas.

root@sqlbackend:~# nano /etc/mysql/my.cnf
[mysqld]
#user = mysql

[mysqld1]
user            = mysql1
pid-file        = /var/run/mysqld1/mysqld.pid
socket          = /var/run/mysqld1/mysqld.sock
port            = 33061
basedir         = /usr
datadir         = /var/lib/mysql1
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1
general_log_file        = /var/log/mysql1/mysql.log
general_log             = 1
[mysqld2]
user            = mysql2
pid-file        = /var/run/mysqld2/mysqld.pid
socket          = /var/run/mysqld2/mysqld.sock
port            = 33062
basedir         = /usr
datadir         = /var/lib/mysql2
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1
general_log_file        = /var/log/mysql2/mysql.log
general_log             = 1

[mysqld3]
user            = mysql3
pid-file        = /var/run/mysqld3/mysqld.pid
socket          = /var/run/mysqld3/mysqld.sock
port            = 33063
basedir         = /usr
datadir         = /var/lib/mysql3
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1
general_log_file        = /var/log/mysql3/mysql.log
general_log             = 1

Chaque instance s’exécute sous un utilisateur dédié :

root@sqlbackend:~# useradd -r mysql1
root@sqlbackend:~# useradd -r mysql2
root@sqlbackend:~# useradd -r mysql3

Les répertoires de chaque instance sont à créer de cette manière :

root@sqlbackend:~# mkdir /var/lib/mysql{1,2,3}
root@sqlbackend:~# mkdir /var/log/mysql{1,2,3}
root@sqlbackend:~# chown mysql1:adm /var/log/mysql1
root@sqlbackend:~# chown mysql2:adm /var/log/mysql2
root@sqlbackend:~# chown mysql3:adm /var/log/mysql3
root@sqlbackend:~# mkdir /var/run/mysqld{1,2,3}
root@sqlbackend:~# chown mysql1 /var/run/mysqld1
root@sqlbackend:~# chown mysql2 /var/run/mysqld2
root@sqlbackend:~# chown mysql3 /var/run/mysqld3

Il reste à initialiser les datastores :

root@sqlbackend:~# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql1
root@sqlbackend:~# mysql_install_db --datadir=/var/lib/mysql2 --user=mysql2
root@sqlbackend:~# mysql_install_db --datadir=/var/lib/mysql3 --user=mysql3

L’exécution de chaque instance est désormais possible :

root@sqlbackend:~# mysqld_multi --verbose --no-log start 1
root@sqlbackend:~# mysqld_multi --verbose --no-log start 2
root@sqlbackend:~# mysqld_multi --verbose --no-log start 3

MySQL : convertir toutes les tables en InnoDB

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
    AND engine = 'MyISAM'
    AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

MySQL : utilisation d’un RAM-disque pour les tables temporaires

root@sql:~# mkdir /tmp/mysqltmp
root@sql:~# chown mysql:mysql /tmp/mysql
root@sql:~# nano /etc/fstab
tmpfs /tmp/mysqltmp tmpfs noatime,nodev,nosuid,noexec,uid=mysql,gid=mysql,mode=0700,size=768m,nr_inodes=10k 0 0
root@sql:~# nano /etc/mysql/my.cnf
tmpdir = /tmp/mysqltmp

Zabbix : mise en place du partitionnement MySQL

Dans le cas où vous monitorez un nombre important de serveurs et d’items avec Zabbix, le partitionnement des tables SQL est une solution viable afin d’optimiser les performances de votre serveur. Le principe est de scinder horizontalement chaque table en divisant l’ensemble des enregistrements en sous-ensembles plus légers, optimisant ainsi les traitements. Les partitions sont considérées en interne comme de nouvelles tables et bénéficient donc de leurs propres index. Lors des requêtes, MySQL consulte uniquement les partitions nécessaires (un EXPLAIN PARTITION d’une requête confirme ce point). Plusieurs types de partitionnement existent, le plus simple probablement à comprendre étant celui par plage (range).

Dans le cas d’une base de données Zabbix, le partitionnement par range s’effectue au niveau du champ clock des tables. Certaines tables sont à partitionner par jour (history) et d’autres par mois ( trends). Le gain direct consiste en une souplesse de suppression des données, limitées dans ce cas à la suppression des partitions les plus anciennes. Le processus de HouseKeeping de Zabbix, visant à nettoyer toutes les tables par un DELETE sur chaque item, n’est donc plus nécessaire. Gain associé : il est possible d’augmenter la période de rétention des trends.

Voici la procédure de partitionnement des tables Zabbix.

Modification des clés primaires

Le partitionnement par range nécessite certaines modifications au niveaux des clés de certaines tables :

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

Ajout des partitions sur les tables mensuelles

Les tables mensuelles sont :

  • acknowledges
  • alerts
  • auditlog
  • events
  • service_alarms
  • trends
  • trends_uint

Conservant 12 mois de données, il faut créer pour chaque table 12 partitions + 1 pour le mois suivant le temps de création. A chaque début de mois, une nouvelle partition sera à ajouter (pour le mois suivant) et la plus ancienne est à supprimer.

Sur chacune de ces tables, les partitions sont créées de cette façon (au 31/01/2013) :

ALTER TABLE `acknowledges` PARTITION BY RANGE(clock) (
   PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
   PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
   PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
   PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
   PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
   PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
   PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
   PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
   PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
   PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
   PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
   PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00")),
   PARTITION p201301 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);

Ajout des partitions sur les tables journalières

Les tables journalières sont :

  • history
  • history_log
  • history_str
  • history_text
  • history_uint

L’historique étant de 7 jours, chaque table nécessite 7 partitions + 1 pour le jour suivant le temps de création. Chaque jour, une nouvelle partition sera également à ajouter, la plus ancienne à supprimer.

Sur chacune de ces tables, les partitions sont créées de cette façon (au 31/01/2013) :

ALTER TABLE `history` PARTITION BY RANGE(clock) (
   PARTITION p20130124 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-25 00:00:00")),
   PARTITION p20130125 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-26 00:00:00")),
   PARTITION p20130126 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-27 00:00:00")),
   PARTITION p20130127 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-28 00:00:00")),
   PARTITION p20130128 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-29 00:00:00")),
   PARTITION p20130129 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-30 00:00:00")),
   PARTITION p20130130 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-31 00:00:00")),
   PARTITION p20130131 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);

Automatisation de la rotation des partitions

La rotation des partitions peut être effectuée par des triggers SQL. Ceux-si seront alors appelés par script cron.

DELIMITER //

CREATE PROCEDURE `zabbix`.`cron_monthly` ()
BEGIN
	CALL zabbix.create_monthly_partition("acknowledges");
	CALL zabbix.create_monthly_partition("alerts");
	CALL zabbix.create_monthly_partition("auditlog");
	CALL zabbix.create_monthly_partition("events");
	CALL zabbix.create_monthly_partition("service_alarms");
	CALL zabbix.create_monthly_partition("trends");
	CALL zabbix.create_monthly_partition("trends_uint");
	CALL zabbix.drop_monthly_partition("acknowledges");
	CALL zabbix.drop_monthly_partition("alerts");
	CALL zabbix.drop_monthly_partition("auditlog");
	CALL zabbix.drop_monthly_partition("events");
	CALL zabbix.drop_monthly_partition("service_alarms");
	CALL zabbix.drop_monthly_partition("trends");
	CALL zabbix.drop_monthly_partition("trends_uint");
END //

CREATE PROCEDURE `zabbix`.`cron_daily` ()
BEGIN
	CALL zabbix.create_daily_partition("history");
	CALL zabbix.create_daily_partition("history_log");
	CALL zabbix.create_daily_partition("history_str");
	CALL zabbix.create_daily_partition("history_text");
	CALL zabbix.create_daily_partition("history_uint");
	CALL zabbix.drop_daily_partition("history");
	CALL zabbix.drop_daily_partition("history_log");
	CALL zabbix.drop_daily_partition("history_str");
	CALL zabbix.drop_daily_partition("history_text");
	CALL zabbix.drop_daily_partition("history_uint");
END //

CREATE PROCEDURE `zabbix`.`create_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
	DECLARE NEXTCLOCK TIMESTAMP;
	DECLARE PARTITIONNAME VARCHAR(16);
	DECLARE CLOCK INT;

	SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 MONTH);
	SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m');
	SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 MONTH), '%Y-%m-01 00:00:00'));
	CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //

CREATE PROCEDURE `zabbix`.`drop_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
	DECLARE OLDCLOCK TIMESTAMP;
	DECLARE PARTITIONNAME VARCHAR(16);
	DECLARE CLOCK INT;

    SET @maxdays = 365;
    SET @old = @maxdays+1;
	SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
	SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m');
	CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //

CREATE PROCEDURE `zabbix`.`create_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
	DECLARE NEXTCLOCK timestamp;
	DECLARE PARTITIONNAME VARCHAR(16);
	DECLARE CLOCK INT;

	SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 DAY);
	SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m%d');
	SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'));
	CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //

CREATE PROCEDURE `zabbix`.`drop_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
	DECLARE OLDCLOCK TIMESTAMP;
	DECLARE PARTITIONNAME VARCHAR(16);
	DECLARE CLOCK INT;

    SET @maxdays = 7;
    SET @old = @maxdays+1;
	SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
	SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m%d');
	CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //

CREATE PROCEDURE `zabbix`.`create_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
	DECLARE RETROWS INT;
	SELECT COUNT(1) INTO RETROWS
		FROM `information_schema`.`partitions`
		WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

	IF RETROWS = 0 THEN
		SELECT CONCAT("create_partition(", PARTITIONNAME, ",", CLOCK, ")") AS msg;
     	SET @sql = CONCAT('ALTER TABLE `zabbix`.`', TABLENAME, '`', ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));');
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END //

CREATE PROCEDURE `zabbix`.`drop_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64))
BEGIN
	DECLARE RETROWS INT;
	SELECT COUNT(1) INTO RETROWS
		FROM `information_schema`.`partitions`
		WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

	IF RETROWS = 1 THEN
		SELECT CONCAT( "drop_partition(", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
     	SET @sql = CONCAT( 'ALTER TABLE `zabbix`.`', TABLENAME, '`',' DROP PARTITION ', PARTITIONNAME, ';' );
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	END IF;
END //

DELIMITER ;

Deux script à planifier en cron vont appeller ces triggers; le premier à chaque début de mois et le second chaque jour :

root@zabbix:~# crontab -l
0 1 1 * * /root/scripts/zabbix_monthly.sh
0 1 * * * /root/scripts/zabbix_daily.sh
root@zabbix:~# cat /root/scripts/zabbix_monthly.sh
#!/bin/sh

mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_monthly();"
root@zabbix:~# cat /root/scripts/zabbix_daily.sh
#!/bin/sh

mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_daily();"

Pour finaliser, le process HouseKeeper de Zabbix est à désactiver (option de configuration DisableHousekeeping).

MySQL : export de données

Pour produire un dump contenant uniquement les données, les options à passer à mysqldump sont :

# mysqldump --skip-triggers --compact --no-create-info [...]

Pour ensuite insérer les données sans prendre en compte les contraintes d’intégrité (clés étrangères) :

mysql> set FOREIGN_KEY_CHECKS = 0
mysql> source dump.sql;
mysql> set FOREIGN_KEY_CHECKS = 1

MySQL : vérifier l’occupation disque des tables & index

SELECT CONCAT(table_schema, '.', table_name) db_table,
       CONCAT(ROUND(table_rows/1000000, 2), 'M') rows,
       CONCAT(ROUND((data_length+index_length)/(1024*1024), 1), 'M') size,
       CONCAT(ROUND(data_length/(1024*1024), 1), 'M') data,
       CONCAT(ROUND(index_length/(1024*1024), 1), 'M') indx,
       ROUND(index_length/data_length, 2) ratio 
FROM   information_schema.TABLES 
ORDER  BY data_length + index_length DESC;

MySQL : script de sauvegarde par snapshot LVM

# cat mysql_backup.sh
#!/usr/bin/perl -w

my $host = 'localhost';
my $dbname = 'mysql';
my $user = 'backup';
my $passwd = 'kV6qcZUbH0';
my $vg = 'vg';
my $lv = 'sql';
my $size = '1G';
my $backupdir = '/home/backup/mysql';

### SCRIPT ###

use POSIX;
use DBI;

my $snapshot = $lv . "_" . strftime("%Y%m%d_%H%M%S", localtime);
print "Creating snapshot $snapshot...\n";

$dbh = DBI->connect("dbi:mysql:$dbname;host=$host", $user, $passwd)
or die "Connection error: $DBI::errstr\n";

$dbh->do("SET SESSION AUTOCOMMIT=0");
$dbh->do("FLUSH TABLES WITH READ LOCK");

system("lvcreate -s -L$size -n$snapshot $vg/$lv");

$sth = $dbh->prepare("SHOW MASTER STATUS");
$sth->execute();
while ($row = $sth->fetchrow_hashref) {
 foreach $col (keys %{$row}) {
 print "$col: " . $$row{$col}."\n";
 }
}

$dbh->do("UNLOCK TABLES");

system("mkdir -p $backupdir/tmp");
system("mount /dev/mapper/$vg-$snapshot $backupdir/tmp");
system("tar czf $snapshot.tar.gz -C $backupdir/tmp .");
system("umount /dev/mapper/$vg-$snapshot");
system("rm -fr $backupdir/tmp");
system("lvremove -f $vg/$snapshot");

print "Done.\n";

MySQL : script de sauvegarde par snapshot ZFS

# cat mysql_backup.sh
#!/usr/bin/perl -w

my $host = 'localhost';
my $dbname = 'mysql';
my $user = 'backup';
my $passwd = 'WD8Y2YOraM';
my $pool = 'rpool/sql';
my $backupdir = '/home/backup/mysql';

### SCRIPT ###

use POSIX;
use DBI;

my $snapshot = strftime("%Y%m%d_%H%M%S", localtime);
print "Creating snapshot $snapshot...\n";

$dbh = DBI->connect("dbi:mysql:$dbname;host=$host", $user, $passwd)
   or die "Connection error: $DBI::errstr\n";

$dbh->do("SET SESSION AUTOCOMMIT=0");
$dbh->do("FLUSH TABLES WITH READ LOCK");
system("zfs snapshot -r $pool\@$snapshot");
$sth = $dbh->prepare("SHOW MASTER STATUS");
$sth->execute();
while ($row = $sth->fetchrow_hashref) {
 foreach $col (keys %{$row}) {
 print "$col: " . $$row{$col}."\n";
 }
}

$dbh->do("UNLOCK TABLES");

system("zfs send -vR $pool\@$snapshot | gzip > $backup_dir/$snapshot.gz");
system("zfs destroy -r $pool\@$snapshot");

print "Done.\n"

MySQL : limiter le nombre de connexions simultanées de chaque utilisateur

Pour préserver l’accès aux données d’un serveur MySQL, il convient de limiter le nombre de connexions simultanées autorisées pour chaque utilisateur. Cette limite doit évidemment être inférieure à la limite globale max_connections.

Par exemple, pour une limite de 30 connexions simultanées :

mysql> UPDATE user SET max_user_connections=30 WHERE User != 'root';

 

MySQL : réinitialiser le mot de passe root

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
mysql> use mysql;
mysql> update user set password=PASSWORD("imsostupid") where User='root';
mysql> flush privileges;
mysql> quit
# mysqladmin -u root -p shutdown
# /etc/init.d/mysql start
Haut de page