MySQL : Mutualisation de bases de données

Utilisation et configuration multiples bases mysql sur une même machine

cas de vmps60 par exemple:

Il faut commencer par créer les répertoires de chaque bases de données supplémentaires puis les initialiser avec la commande mysql_install_db

mkdir /var/lib/mysql-databases/mysqld2
 
mkdir /var/lib/mysql-databases/mysqld3
 
mkdir /var/lib/mysql-databases/mysqld4

Configuration du fichier my.cnf

Ajouter le paragraphe mysqld_multi

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = multipass

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql

[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql

[mysqld3]
port       = 3308
datadir    = /var/lib/mysql-databases/mysqld3
pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
user       = mysql

[mysqld4]
port       = 3309
datadir    = /var/lib/mysql-databases/mysqld4
pid-file   = /var/lib/mysql-databases/mysqld4/mysql.pid
socket     = /var/lib/mysql-databases/mysqld4/mysql.sock
user       = mysql

Connexion sur une base ( par exemple mysqld4 )

mysql -p -P 3309 -S /var/lib/mysql-databases/mysqld4/mysql.sock

Sur chaque base il faut créer l'utilisateur qui a les droits d’arrêter la base :

vmps60:/etc/rc.d# mysql -p -P 3309 -S /var/lib/mysql-databases/mysqld4/mysql.sock
Enter password:
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.33 SUSE MySQL package
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'multipass';

utilisation :

vmps60:/etc/rc.d# mysqld_multi report
 
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

Arret de la base mysqld4 :

vmps60:/etc/rc.d# mysqld_multi stop 4
 
vmps60:/etc/rcd # mysqld_multi report
 
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is not running

Démarrage de la base 4

vmps60:/etc/rc.d# mysqld_multi start 4
 
vmps60:/etc/rc.d# mysqld_multi report
 
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running