mysql multi设置
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;
页:
[1]