mysql multi设置

tset123

　　http://naveensnayak.wordpress.com/2013/11/10/mysql-multiple-instances-on-ubuntu/
MySQL multiple instances on Ubuntu
　　Assumptions

• 　　OS: Ubuntu 12.04 LTS server edition – up to date
  
• 　　Already has MySQL installed that comes default with 12.04 – you can easily install LAMP with the command tasksel
  
• 　　MySQL Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)
  
• 　　You have OS root privileges
  
• 　　Default MySQL is running on port 3306
  

　　What will we do

• 　　Set up 2 more MySQL instances on ports 3307 and 3308
  
• 　　Each instance will have their own config files, data directories and log directories
  

　　Stopping default MySQL instance
　　Icon
sudo service mysql stop　　
sudo ps -A | grep mysql
　　Creating data directories

• 　　MySQL cannot share data directories, so we need to set up new ones
  
• 　　default basedir = /usr, this can be shared across instances
  
• 　　default instance port = 3306 and data dir = /var/lib/mysql
  
• 　　new instance       port = 3307 and data dir = /var/lib/mysql3307
  
• 　　new instance       port = 3308 and data dir = /var/lib/mysql3308
  
• 　　MySQL must own data dirs
  
• 　　we need to set rules in apparmor to let MySQL access the new dirs
  

　　Icon
sudo mkdir /var/lib/mysql3307　　sudo mkdir
/var/lib/mysql3308　　sudo chown
-R mysql /var/lib/mysql3307　　sudo chown
-R mysql /var/lib/mysql3308Creating data directories

• 　　create separate log dirs for new MySQL instances
  
• 　　default log dir = /var/log/mysql
  
• 　　new log dir for 3307 = /var/log/mysql/mysql3307
  
• 　　new log dir for 3308 = /var/log/mysql/mysql3308
  
• 　　log dirs must be owned by MySQL
  
• 　　note that /var/log/mysql already has apparmor permissions for MySQL, so any dir under it also has access
  

　　
　　Icon
sudo mkdir /var/log/mysql/mysql3307sudo mkdir /var/log/mysql/mysql3308　　
sudo chown -R mysql /var/log/mysql/mysql3307
　　
sudo chown -R mysql /var/log/mysql/mysql3308
　　Creating config files

• 　　create the config files for new instances by copying default file
  
• 　　default config file = /etc/mysql/my.cnf
  
• 　　config file for 3307 = /etc/mysql/my3307.cnf
  
• 　　config file for 3308 = /etc/mysql/my3308.cnf
  
• 　　see config files on github
  
• 　　/etc/mysql/my3307.cnf
  
• 　　/etc/mysql/my3308.cnf
  
• 　　special care has to be taken so that these values are different
  
• 　　datadir
  
• 　　server-id
  
• 　　all port entries
  
• 　　all socket entries
  
• 　　all pid-file entries
  
• 　　all log file entries, general, error, binary etc
  

　　Icon
sudo cp /etc/mysql/my.cnf /etc/mysql/my3307.cnfsudo cp /etc/mysql/my.cnf /etc/mysql/my3308.cnf　　Apparmor settings ( skip if you dont have this )

• 　　apparmor is like an application firewall – comes installed default with Ubuntu server
  
• 　　command aa-status will show you if it is loaded
  
• 　　default apparmor profile for MySQL = /etc/apparmor.d/usr.sbin.mysqld
  
• 　　put additional rules in /etc/apparmor.d/local/usr.sbin.mysqld
  
• 　　specify the correct data dirs, pid and socket files for each instance - see file on github
  
• 　　/etc/apparmor.d/local/usr.sbin.mysqld
  
• 　　after modifying, restart apparmor
  

　　/etc/apparmor.d/usr.sbin.mysqld
　　Icon
　　# Site-specific additions and overrides for usr.sbin.mysqld.
　　# For more details, please see /etc/apparmor.d/local/README.
　　######### mysqld1 on port 3307 ###############
　　/var/lib/mysql3307/ r,
　　/var/lib/mysql3307/** rwk,
　　/var/run/mysqld/mysqld.pid3307 rw,
　　/var/run/mysqld/mysqld.sock3307 w,
　　/run/mysqld/mysqld.pid3307 rw,
　　/run/mysqld/mysqld.sock3307 w,
　　######### mysqld2 on port 3308 ###############
　　/var/lib/mysql3308/ r,
　　/var/lib/mysql3308/** rwk,
　　/var/run/mysqld/mysqld.pid3308 rw,
　　/var/run/mysqld/mysqld.sock3308 w,
　　/run/mysqld/mysqld.pid3308 rw,
　　/run/mysqld/mysqld.sock3308 w,
　　

　　
$sudo service apparmor reload
　　Installing new MySQL instances

• 　　install MySQL files into the new data dirs for port 3307 and port 3308
  
• 　　after this, under each new data dir, you will see the mysql, performance_schema and test dirs
  
• 　　this will install MySQL with default settings,  no root password
  
• 　　in the below commands, you can use the - -verbose flag to see more details
  

　　Icon
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/mysql/my3307.cnf　　
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3308 --defaults-file=/etc/mysql/my3308.cnf
　　Starting the mysql instances

• 　　start the default instance on 3306
  
• 　　start instances on 3307 and 3308 in the background
  

　　Icon
sudo service mysql start sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql　　
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
　　如果是centos系统，这样启动：/usr/bin/mysqld_safe --defaults-file= /etc/mysql/my3307.cnf --user=mysql &
　　Accessing the new instances

• 　　Note that the new instances on 3307 and 3308 will not have a root password
  
• 　　it is important to specify host and host=127.0.0.1
  
• 　　if host is omitted, or localhost is given, then default instance is assumed ( on 3306 )
  
• 　　remember to explicitly specify host and port for all commands
  

　　Icon
mysql -h 127.0.0.1 --port=3307 -u root　　
mysql -h 127.0.0.1 --port=3308 -u root
　　Shutting down the MySQL instances

• 　　We will use mysqladmin to cleanly shutdown
  
• 　　it is important to specify host and and port
  
• 　　no password for now
  

　　Icon
mysqladmin -h 127.0.0.1 --port=3307 -u root shutdown　　
mysqladmin -h 127.0.0.1 --port=3308 -u root shutdown
　　Starting new instances on boot and reboot

• 　　Put commands in the file /etc/rc.local to start new instances on boot
  
• 　　the rc.local file will look like this
  

　　Icon
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3307.cnf --user=mysql　　
sudo -b mysqld_safe --defaults-file=/etc/mysql/my3308.cnf --user=mysql
　　
exit 0
　　补充：

• 　　防火墙添加3307端口，重启防火墙，
  　　# vi /etc/sysconfig/iptables #编辑防火墙配置文件
  　　-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
  　　-A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT
  　　-A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT
  　　重启防火墙
  　　# /etc/init.d/iptables restart #最后重启防火墙使配置生效
  
• 　　登录多实例的mysql要指定.sock文件
  　　mysql -uroot -p -S /var/lib/mysql3307/mysql3307.sock
  　　mysql -uroot -prx5dbt2c -S /var/lib/mysql3307/mysql3307.sock
  
• 　　创建密码：
  　　mysqladmin -u root -S /var/lib/mysql3307/mysql3307.sock password 'rx5dbt2c'
  
• 　　授权远程登录
  
  　　mysql>grant all privileges on *.* to 'root'@'%'>　　mysql> flush privileges;