さんきゅう堂的_バックアップ
MySQL複数起動
- Next Page: Kernelバージョンアップ
- Prev Page: MailServer
[mysqld_multi]で[mysqld]を3つ起動させる！！
参考先
まずは、現状確認
# ps aux | grep mysql
root 3648 0.0 0.0 7972 520 ? S Dec10 0:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql
mysql 3739 0.0 0.6 127452 6480 ? Sl Dec10 0:11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root 4741 0.0 0.0 5892 864 pts/0 S+ 08:31 0:00 grep mysql
MySQLを止めます。
# /etc/init.d/mysql stop
Shutting down service MySQL done
新しくdbフォルダを作成
所有者、グループをmysql
mysqlを各dbフォルダにコピー
linux:/home/sunq # mkdir /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
linux:/home/sunq # chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
linux:/home/sunq # cp -Rp /var/lib/mysql/mysql /var/lib/mysql1/
linux:/home/sunq # cp -Rp /var/lib/mysql/mysql /var/lib/mysql2/
linux:/home/sunq # cp -Rp /var/lib/mysql/mysql /var/lib/mysql3/
/etc/multi_my.cnf を作成
# configuration principale de mysqld_multi
# l'utilisateur multi_user est necessaire pour que
# le programme puisse arreter les moteurs.
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_user
password = mypass
# configuration d'un premier serveur
[mysqld1]
# repertoire de stockage des fichiers de base
datadir=/var/lib/mysql1
# chemin du socket ( imperativement different pour chaque serveur )
socket=/var/lib/mysql1/mysql.sock1
# port ( imperativement different pour chaque serveur )
port=3306
# pour compatibilite avec anciens serveurs
old_passwords=1
# chemin des fichiers de log
err-log=/var/log/mysqld1.log
# chemin des processus
pid-file=/var/run/mysqld/mysqld.pid1
# utilisateur proprietaire du processus
user = mysql
# configuration d'un second serveur
[mysqld2]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql.sock2
port=3307
old_passwords=1
err-log=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld.pid2
user = mysql
[mysqld3]
datadir=/var/lib/mysql3
socket=/var/lib/mysql2/mysql.sock3
port=3308
old_passwords=1
err-log=/var/log/mysqld3.log
pid-file=/var/run/mysqld/mysqld.pid3
user = mysql
# configuration serveur maître
[mysql.server]
user=mysql
basedir=/var/lib
起動させてみる、、
# /usr/bin/mysqld_multi --config-file=/etc/multi_my.cnf start 1,2,3？？動いてるの？？
# tail -f /var/log/mysqld1.log
InnoDB: buffer...
070124 10:33:06 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
070124 10:33:06 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
070124 10:33:07 InnoDB: Started; log sequence number 0 43655
070124 10:33:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql1/mysql.sock1' port: 3306 SUSE MySQL RPM
# tail -f /var/log/mysqld2.log
InnoDB: buffer...
070124 10:33:06 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
070124 10:33:06 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
070124 10:33:07 InnoDB: Started; log sequence number 0 43655
070124 10:33:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql2/mysql.sock2' port: 3307 SUSE MySQL RPM
# tail -f /var/log/mysqld3.log
InnoDB: buffer...
070124 10:33:06 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
070124 10:33:06 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
070124 10:33:07 InnoDB: Started; log sequence number 0 43655
070124 10:33:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql3/mysql.sock3' port: 3308 SUSE MySQL RPM
/var/lib/mysql1/、/var/lib/mysql2/、/var/lib/mysql3/にそれぞれ
mysql.sock1＆mysql.sock1、mysql.sock2＆mysql.sock2、mysql.sock3＆mysql.sock3
が出来上がりました。
# mysql -u root -S /var/lib/mysql1/mysql.sock1 -p -e "GRANT SHUTDOWN ON *.* TO multi_user@localhost IDENTIFIED BY 'mypass'"
Enter password:
パスワードを聞いてくるのでrootのパスでいいのかな？？
# mysql -u root -S /var/lib/mysql2/mysql.sock2 -p -e "GRANT SHUTDOWN ON *.* TOmulti_user@localhost IDENTIFIED BY 'mypass'"
Enter password:
# mysql -u root -S /var/lib/mysql3/mysql.sock3 -p -e "GRANT SHUTDOWN ON *.* TO multi_user@localhost IDENTIFIED BY 'mypass'"
Enter password:
/etc/init.d/multi_mysqld 起動ファイル
#!/bin/bash
#
# multi_mysqld Ce script permet de demarrer ou d'arreter
# le programme MySQL (mysqld_multi).
#
# chkconfig: - 64 36
# description: MySQL database servers.
# processname: mysqld_multi
# config: /etc/multi_my.cnf
# Source function library.
. /etc/rc.d/init.d/functions
# Source networking configuration.
. /etc/sysconfig/network
# Nom du programme
prog="MySQL multi-serveurs"
# Chemin du fichier de configuration
conf="/etc/multi_my.cnf"
# Chemin du fichier de log
err_m="/var/log/multi_mysqld.log"
# deco
sep="*--------------------------------*"
test_var()
{variable="$1"
type_rech="$2"
msg=""if [ $type_rech = "x" ];then
msg="exécutable"
else
msg="lisible"
fi# extraction des donnees
awk 'BEGIN{FS="="}($1 ~ /^'$variable'/){print $2;}' "$conf" | sed 's/[ \t]*//' 2>/dev/null > /tmp/.tmpfic# test d'existence des fichiers en lecture et repertoires
retour=`awk 'function TestDir(Rep)
{
Cmd = "test -'$type_rech' " Rep ";echo $?"
Cmd|getline Result
close(Cmd)
if (Result==1) return 0
if (Result==0) return 1
print Result
}
{
if (TestDir($1)) printf "%-40s %s\n","'$variable'="$1,"OK"
else printf "%-40s %s %s\n","'$variable'="$1,"non trouvé ou non","'$msg'"
}' /tmp/.tmpfic 2>/dev/null`
echo "$retour"
}# fonction qui verifie l'existence d'une variable de configuration
get_mysql_option()
{result=`sed -n "s/^[ \t]*$2[ \t]*=[ \t]*//p" "$1" 2>/dev/null | tail -n 1`
if [ -z "$result" ]; then
# non trouve retourne KO
result="KO"
else
# retire les quotes et commentaires
dequoted=`echo "$result" | sed "s/^'\([^']*\)'.*$/\1/"`
if [ x"$dequoted" != x"$result" ]; then
result="$dequoted"
else
dequoted=`echo "$result" | sed 's/^"\([^"]*\)".*$/\1/'`
if [ x"$dequoted" != x"$result" ]; then
result="$dequoted"
else
result=`echo "$result" | sed 's/^\([^ \t#]*\).*$/\1/'`
fi
fi
fi
}# teste des variables de configuration
get_mysql_option $conf datadir
datadir="$result"
get_mysql_option $conf socket
socketfile="$result"
get_mysql_option $conf err-log
errlogfile="$result"
get_mysql_option $conf pid-file
mypidfile="$result"# fonction qui etablit le message d'erreur
get_params_ok()
{
retour=""
if [ ! -r "$1" ]; then
retour="Fichier de configuration introuvable : $1"
return -1
fi
if [ "$2" = "KO" ]; then
retour="Aucun paramètre datadir trouvé !"
return -1
fi
if [ "$3" = "KO" ]; then
retour="Aucun paramètre socket trouvé !"
return -1
fi
if [ "$4" = "KO" ]; then
retour="Aucun paramètre err-log trouvé !"
return -1
fi
if [ "$5" = "KO" ]; then
retour="Aucun paramètre pid-file trouvé !"
return -1
fi
if [ ! -d "$2" ]; then
retour="Répertoire fichiers MySQL ( datadir ) introuvable !"
return -1
fi
}# demarrage
start()
{
# teste si au moins un serveur peut demarrer
get_params_ok $conf $datadir $socketfile $errlogfile $mypidfile# si test ko
if [ ! -z "$retour" ]; then
# log multi_mysqld.log
touch "$err_m"
echo `LC_ALL=fr_FR date '+%A %d %B %Y %H:%M:%S'` " : $retour">>"$err_m"
echo $"Démarrage $prog: "
action $"$retour" /bin/false
return -1
fi# execution de mysqld_multi
/usr/bin/mysqld_multi --config-file="$conf" start >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
# si tout ok
sleep 2
action $"Démarrage $prog: " /bin/true
else
# si non
action $"Démarrage $prog: " /bin/false
fi
return $ret
}# arret
stop()
{
# teste les parametres de configuration
get_params_ok $conf $datadir $socketfile $errlogfile $mypidfile# si test ko
if [ ! -z "$retour" ]; then
# log multi_mysqld.log
touch "$err_m"
echo `LC_ALL=fr_FR date '+%A %d %B %Y %H:%M:%S'` " : $retour">>"$err_m"
echo $"Démarrage $prog: "
action $"$retour" /bin/false
return -1
fi# execution de mysqld_multi
/usr/bin/mysqld_multi --config-file="$conf" stop >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
# si tout ok
sleep 1
action $"Arrêt $prog: " /bin/true
else
# si non
action $"Arrêt $prog: " /bin/false
fi
return $ret
}restart()
{
stop
start
}config()
{
# si fichier de configuration existe
if [ ! -r "$conf" ]; then
echo "fichier de configuration introuvable : $conf"
echo $sep
action $"Lecture fichier de configuration $prog" /bin/false
else
# affichage sans les commentaires et sans espaces ni tabulations
echo $sep
echo "| Lecture fichier configuration |"
echo $sep
awk '! (/^ *#/ || /^$/) { print $0 }' "$conf" 2>/dev/null | sed 's/[ \t]*=[ \t]*/=/'
echo $sep
action $"Lecture fichier de configuration $prog" /bin/true
fi
}config_test()
{
# si fichier de configuration existe
if [ ! -r "$conf" ]; then
echo "fichier de configuration introuvable : $conf"
echo $sep
action $"Test fichier de configuration $prog" /bin/false
else# verification de l'existence des valeurs de chaque variable
echo $sep
echo "| Test des variables nécessaires |"
echo $sep
test_var "err-log" "r"
test_var "datadir" "d"
test_var "mysqld" "x"
test_var "mysqladmin" "x"
test_var "basedir" "d"
rm -f /tmp/.tmpfic 2>/dev/null
echo $sep
action $"Test du fichier de configuration $prog" /bin/true
fi
}case "$1" in
start)
start
;;
stop)
stop
;;
status)
status mysqld
;;
restart)
restart
;;
config)
config
;;
config_test)
config_test
;;
*)
echo $"Usage: $0 {start|stop|status|restart|config|config_test}"
exit 1
esac
exit $?
# chmod +x /etc/init.d/multi_mysqld
さぁ＠＠どうかな？？？
# /etc/init.d/multi_mysqld stop
/etc/init.d/multi_mysqld: line 11: /etc/rc.d/init.d/functions: そのようなファイ ルやディレクトリはありません
/etc/init.d/multi_mysqld: line 13: .: /etc/sysconfig/network: is a directory
/etc/init.d/multi_mysqld: line 172: action: command not found
そらそうだ、、、そんなに簡単にいくわけはないでしょう(>_<)
# /etc/init.d/multi_mysqld config_test
*--------------------------------*
| Test des variables nécessaires |
*--------------------------------*
err-log=/var/log/mysqld1.log OK
err-log=/var/log/mysqld2.log OK
err-log=/var/log/mysqld3.log OK
err-log=/var/lib/mysql/mysqld.log OK
datadir=/var/lib/mysql1 OK
datadir=/var/lib/mysql2 OK
datadir=/var/lib/mysql3 OK
mysqld=/usr/bin/mysqld_safe OK
mysqladmin=/usr/bin/mysqladmin OK
basedir=/var/lib OK
*--------------------------------*
/etc/init.d/multi_mysqld: line 225: action: command not found
/etc/init.d/multi_mysqldでエラーが出てます。
各dbは動いてました、、
multi_mysqldを修正しないと、うちの環境では使えませんが私には無理っしょ(･_･;
何となくは見えてきましたが・・・
まだまだ、道のりは遠い・・・・・
元の環境に戻しました・・・・・もう少し勉強(..)ψﾒﾓﾒﾓ