LinuxSEE

mysql多主一从多实例配置

原理：利用mysql实例功能添加多个实例，把所有实例数据放入同一文件夹，对外各实例分别同步各自mysql主服务器，并在某一实例中包含所有数据库，从而实现多主一从功能。

———————————————————————————————————————

配置文件/opt/mysql/my.cnf

[mysqld_multi]
mysqld = /opt/mysql/bin/mysqld_safe
mysqladmin = /opt/mysql/bin/mysqladmin

[mysqld1]
port     = 3306
socket   = /opt/mysql/var/mysql.sock3306
skip-locking
pid-file=/opt/mysql/var/3306.pid
datadir = /opt/mysql/var
log=/opt/mysql/var/3306.log
log-slow-queries=/opt/mysql/var/slowquery3306.log

server-id     = 1371
master-connect-retry = 60
log-bin
log-slave-updates

[mysqld2]
port     = 3307
socket   = /opt/mysql/var/mysql.sock3307
skip-locking
pid-file=/opt/mysql/var/3307.pid
datadir = /opt/mysql/var
log=/opt/mysql/var/3307.log
log-slow-queries=/opt/mysql/var/slowquery3307.log

server-id     = 1372
master-connect-retry = 60
log-bin
log-slave-updates

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

---------------------------------------------------------------------------------------------------------------------

启动命令：mysqld_multi --defaults-extra-file=/opt/mysql/my.cnf start 1,2

停止命令：mysqld_multi --defaults-extra-file=/opt/mysql/my.cnf stop 1,2

进入实例命令：mysql -S/opt/mysql/var/mysql.sock3306 -uroot -p

在某一mysql主上添加同步账号：grant replication slave on *.* to 'repl' identified by 'repl';

在某一mysql从实例中同步命令：change master to master_host='ip',master_port=3306,master_user='repl',master_password='repl',master_log_file='logbin.00001',master_log_pos=12345;

转载请注明来自：LinuxSEE