mysqld_multi实现单机主从复制

本文主要介绍mysqld_multi实现单机主从复制。

1.使用mysqld_multi配置三个实例

# mysqld_multi start
# mysqld_multi report
# netstat -an|grep 330

2.设置一个复制使用的账户

# mysql -uroot -p123456 -S  /tmp/mysql.sock3306				主库
mysql> grant replication slave on *.* to 'repl'@'localhost' identified by '123';
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123';

3.开启BINLOG

# vi /etc/my.cnf
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user=root
pass=123456
log=/usr/local/mysql/mysql_multi.log 

[mysqld3306]
basedir=/usr
datadir=/usr/local/mysql/data3306
port=3306
user=mysql
socket=/tmp/mysql.sock3306
server_id=1
log_bin=/usr/local/mysql/mysql-bin-3306
 
[mysqld3307]
basedir=/usr
datadir=/usr/local/mysql/data3307
port=3307
user=mysql
socket=/tmp/mysql.sock3307
server_id=2
log_bin=/usr/local/mysql/mysql-bin-3307
 
[mysqld3308]
basedir=/usr
datadir=/usr/local/mysql/data3308
port=3308
user=mysql
socket=/tmp/mysql.sock3308
server_id=3
log_bin=/usr/local/mysql/mysql-bin-3308

4.主库配置

# mysql -uroot -p123456 -S  /tmp/mysql.sock3307
mysql> flush tables with read lock;
mysql> show master status \G
mysql> unlock tables;
mysql> start slave;

5.从库配置

# mysql -uroot -p123456 -S  /tmp/mysql.sock3307
mysql> show variables like '%log_bin%';
mysql> stop slave;
mysql> change master to master_host='127.0.0.1',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=0;
mysql> start slave;
mysql> show slave status \G
mysql> show processlist \G