MySQL多主一从同步


实验准备：主机A和主机B作为主，其IP地址分别为192.168.131.129和192.168.131.130，主机C作为从服务器，在从服务器上面配置MySQL多实例，其IP地址为192.168.131.136，三台服务器均关闭防火墙和SELINUX，MySQL版本为5.6.26，为通用二进制包

1.主机A和主机B主服务器MySQL通用二进制包安装和初始化

# tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz

# mv mysql-5.6.26-linux-glibc2.5-x86_64 /usr/local/mysql

# useradd -M -s /sbin/nologin mysql

# chown -R root:root /usr/local/mysql/*

# chown -R mysql:mysql /usr/local/mysql/data/

# yum -y install libaio

# cd /usr/local/mysql/scripts/

# ./mysql_install_db --datadir=/usr/local/mysql/data/ --user=mysql --basedir=/usr/local/mysql/

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

# echo 'export PATH=$PATH:/usr/local/mysql/bin' >>/etc/profile

# source /etc/profile

# vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

user=mysql

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

# service mysqld start

#chkconfig mysqld on

# ss -tunlp | grep 3306

# mysqladmin -uroot password '123456' #设置数据库密码

2、主机A和主机B配置

①主机A

# vim /etc/my.cnf

server-id=129

port=3306

log-bin=mysql-bin

log-bin-index=mysql-bin.index

max-binlog-size=100M

# service mysqld restart

# mysql -uroot –p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'wdd'@'192.168.131.136' IDENTIFIED BY '123456';

mysql> SHOW MASTER STATUS;

②主机B

# vim /etc/my.cnf

server-id=130

port=3306

log-bin=mysql-bin

log-bin-index= mysql-bin.index

max-binlog-size=100M

# service mysqld restart

# mysql -u root –p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'wdd'@'192.168.131.136' IDENTIFIED BY '123456';

mysql> SHOW MASTER STATUS;

3、主机c从服务器安装MySQL多实例

# yum -y install libaio

# tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz

# mv mysql-5.6.26-linux-glibc2.5-x86_64 /usr/local/mysql

# useradd -M -s /sbin/nologin mysql

# mkdir /data/{3306,3307} –pv

# chown -R mysql:mysql /data/

#vim /etc/my.cnf

[mysqld_multi]

mysqld=/usr/local/mysql/bin/mysqld_safe

mysqladmin=/usr/local/mysql/bin/mysqladmin

#user=mysql

#password=123456

log=/data/multi.log

[mysqld129]

port=3306

pid-file=/data/3306/mysql.pid

datadir=/data/3306/data

socket=/data/3306/mysql.sock

server-id=136

user=mysql

[mysqld130]

port=3307

pid-file=/data/3307/mysql.pid

datadir=/data/3307/data

socket=/data/3307/mysql.sock

server-id=136

user=mysql

1.# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql/

2.# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql/

3.# mysqld_multi --defaults-file=/etc/my.cnf start 129

# mysqld_multi --defaults-file=/etc/my.cnf start 130

4.# mysqld_multi --defaults-extra-file=/etc/my.cnf report

5.# ss -tunlp | grep 330

注：为了安全应该给MySQL实例设置密码，命令如下

# mysqladmin -uroot -S /data/3306/mysql.sock password '123456'

# mysqladmin -uroot -S /data/3307/mysql.sock password '123456'

4.主机c多实例试验操作

①# mysql -uroot -S /data/3306/mysql.sock

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.131.129',

MASTER_USER='wdd',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=120;

mysql> START SLAVE;

mysql>SHOW SLAVE STATUS;

②# mysql -u root -S /data/3307/mysql.sock

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.131.130',

MASTER_USER='wdd',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=331;

mysql> START SLAVE;

mysql>SHOW SLAVE STATUS;

5、测试

①登录主机A

mysql> create database wdd;

mysql> SHOW DATABASES;

②登录主机B

mysql> create database info;

mysql> show databases;

③查看MySQL实例，实例129和130

实例129

# mysql -uroot -S /data/3306/mysql.sock

mysql> START SLAVE;

实例130

# mysql -u root -S /data/3307/mysql.sock

mysql> show databases;

注：MySQL多实例登录时，其实不需要指定端口，也就是说可以省略-p选项，即使使用-p选项，端口也可以随意指定，也就是说可以随便写一个端口号，只要套接字不变，那么登录的还是同一个MySQL实例。

MySQL 多主多活 Galera 集群部署使用 

MySQL 5.5主从关于‘复制过滤’的深入探究 

MySQL数据库主从不同步的解决方法 

MySQL主从同步配置记录 

MySQL 5.5 主从双向同步

MySQL 5.5主从同步排错

MySQL主从复制异步半同步实例

本文永久更新链接地址：

相关内容