Saturday 29 March 2014

How to Install and configure mysql multi instance in ubuntu 12.04/13.04 server

Create multi MySQL slave on single Ubuntu server

Step-1:Login to your server as root user

Step-2:Login to your mysql server as root and execute the following command

mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'password';

mysql>FLUSH PRIVILEGES;

Step-3:come out of mysql prompt and stop mysql server.To stop you can execute
[root@localhost ~]# service mysql stop

#cd /etc/mysql
#vi my.cnf    
[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = password

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysqlerp.err
server-id = 4
relay-log= /var/lib/mysql/relay-bin.log
log-bin = /var/lib/mysql/mysql-bin.log
binlog_do_db = dbname
binlog-format=mixed

[mysqld2]
port       = 3307
datadir    = /var/lib/mysql2
pid-file   = /var/lib/mysql2/mysql.pid
socket     = /var/lib/mysql2/mysql.sock
user       = mysql
server-id = 3
relay-log= /var/lib/mysql2/relay-bin.log
log-bin = /var/lib/mysql2/mysql-bin.log
binlog_do_db = dbname
binlog-format=mixed
log-error  = /var/log/mysqlcrm.err

vi /etc/apparmor.d/usr.sbin.mysqld
# vim:syntax=apparmor
# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>
/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>
  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,
  network tcp,
  /etc/hosts.allow r,
  /etc/hosts.deny r,
  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
+ /var/log/mysql[1-9].log rw,
+ /var/log/mysql[1-9].err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
+ /var/lib/mysql[1-9]/ r,
+ /var/lib/mysql[1-9]/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
+ /var/log/mysql[1-9]/ r,
+ /var/log/mysql[1-9]/* rw,
+ /var/run/mysqld/mysqld.pid rw,
+ /var/run/mysqld/mysqld.sock w,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,
  /sys/devices/system/cpu/ r,
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}


#/etc/init.d/apparmor restart

#mkdir -p /var/lib/mysql2
cp -r /var/lib/mysql/ /var/lib/mysql2/
chown -R mysql:mysql /var/lib/mysql2

mysqld_multi command
check status :
mysqld_multi report
Start All Mysql Instance
mysqld_multi start
Stop All Mysql Instance
mysqld_multi stop
Start specific Mysql Instance
mysqld_multi start 1 , 2 , 3.... and so on
Stop specific Mysql Instance
mysqld_multi stop 1 , 2 ,3.... and so on

Check MySQL service :
ps -aux | grep mysql | grep -v grep

Access mysql instance :
mysql -uroot -h127.0.0.1 -P3306 -p
mysql -uroot -h127.0.0.1 -P3307 -p

 

No comments:

Post a Comment

Install XRDP in Centos 7

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum update yum groupinstall "GNOME Desktop" ...