Mysql 一主多从配置

Mysql 一主多从配置

---

@(数据库)[Mysql]

在一台机器配置启动多个mysql实例

1. 运行环境

• 系统: Ubuntu14.04
• Mysql版本: mysql-5.5.54

2. 配置简述

• 修改/etc/my.cnf 添加多个实例
• 初始化多个实例的数据库到相应的数据目录
• 设置增加mysqld_mutli关闭mysql实例的权限

3. 详细操作

1./etc/my.cnf

[mysqld_multi]
user        = user_name # mysqld_multi操作mysql的用户名
password    = password  # mysqld_multi操作mysql的密码
mysqld      = /usr/local/mysql/bin/mysqld_safe
mysqladmin  = /usr/local/mysql/bin/mysqladmin
log         = /var/log/mysql/mysqld_multi.log

[mysqld]
# 多个实例中相同部分可放到此项默认设置中
skip-external-locking
user                    = mysql
binlog_format           = mixed
key_buffer_size         = 16M
table_open_cache        = 64
sort_buffer_size        = 512K
read_buffer_size        = 256K
net_buffer_length       = 8K
max_allowed_packet      = 1M
read_rnd_buffer_size    = 512K
myisam_sort_buffer_size = 8M

[mysqld1]
port            = 3306
socket          = /usr/local/mysqldata/data3306/mysql.sock
server-id       = 10
pid-file        = /usr/local/mysqldata/data3306/mysql.pid
datadir         = /usr/local/mysqldata/data3306
log-bin         = /usr/local/mysqldata/data3306/mysql-bin
log-error       = /usr/local/mysqldata/errlog/mysql3306.log

[mysqld2]
user            = mysql
port            = 3307
socket          = /usr/local/mysqldata/data3307/mysql.sock
server-id       = 11
datadir         = /usr/local/mysqldata/data3307
pid-file        = /usr/local/mysqldata/data3307/mysql.pid
log-bin         = /usr/local/mysqldata/data3307/mysql-bin
log-error       = /usr/local/mysqldata/errlog/mysql3307.log

2.初始化数据库

shell> cd /usr/local/mysql/
shell> ./scripts/mysql_install_db --datadir=/usr/local/mysqldata/data3306/ --user=mysql
shell> ./scripts/mysql_install_db --datadir=/usr/local/mysqldata/data3307/ --user=mysql

3.添加mysqld_mutli关闭mysql实例的权限

# mysql 3306设置
shell> mysql -S  /usr/local/mysqldata/data3306/mysql.sock
mysql> grant shutdown on *.* to 'multi'@'localhost' identified by 'your_password';
# mysql 3307设置
shell> mysql -S  /usr/local/mysqldata/data3307/mysql.sock
mysql> grant shutdown on *.* to 'multi'@'localhost' identified by 'your_password';

4.启动多个实例

shell> mysql_mutli start 1
shell> mysql_mutli start 2
# 查看进程是否启动
shell> netstat -apn | grep mysql
 ____________________________________________________________________
| tcp       0   0 0.0.0.0:3306  0.0.0.0:*   LISTEN      15002/mysqld |
| tcp       0   0 0.0.0.0:3307  0.0.0.0:*   LISTEN      15314/mysqld |
 --------------------------------------------------------------------

5.关闭实例

shell> mysql_mutli stop 1
shell> mysql_mutli stop 2

---

作者@Alex
2017 年 01月 19日