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/datasocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-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.cnfserver-id=129port=3306log-bin=mysql-binlog-bin-index=mysql-bin.indexmax-binlog-size=100M# service mysqld restart# mysql -uroot –pmysql> GRANT REPLICATION SLAVE ON *.* TO "wdd"@"192.168.131.136" IDENTIFIED BY "123456";mysql> SHOW MASTER STATUS;②主机B# vim /etc/my.cnfserver-id=130port=3306log-bin=mysql-binlog-bin-index= mysql-bin.indexmax-binlog-size=100M# service mysqld restart# mysql -u root –pmysql> 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_safemysqladmin=/usr/local/mysql/bin/mysqladmin#user=mysql#password=123456log=/data/multi.log[mysqld129]port=3306pid-file=/data/3306/mysql.piddatadir=/data/3306/datasocket=/data/3306/mysql.sockserver-id=136user=mysql[mysqld130]port=3307pid-file=/data/3307/mysql.piddatadir=/data/3307/datasocket=/data/3307/mysql.sockserver-id=136user=mysql1.# /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 1304.# mysqld_multi --defaults-extra-file=/etc/my.cnf report5.# 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.sockmysql> CHANGE MASTER TOMASTER_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.sockmysql> CHANGE MASTER TOMASTER_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、测试①登录主机Amysql> create database wdd;mysql> SHOW DATABASES;②登录主机Bmysql> create database info;mysql> show databases;③查看MySQL实例，实例129和130实例129# mysql -uroot -S /data/3306/mysql.sockmysql> START SLAVE;实例130# mysql -u root -S /data/3307/mysql.sockmysql> show databases;注：MySQL多实例登录时，其实不需要指定端口，也就是说可以省略-p选项，即使使用-p选项，端口也可以随意指定，也就是说可以随便写一个端口号，只要套接字不变，那么登录的还是同一个MySQL实例。MySQL 多主多活 Galera 集群部署使用 http://www.linuxidc.com/Linux/2015-11/125100.htmMySQL 5.5主从关于‘复制过滤’的深入探究 http://www.linuxidc.com/Linux/2015-10/124181.htmMySQL数据库主从不同步的解决方法 http://www.linuxidc.com/Linux/2015-09/123018.htmMySQL主从同步配置记录 http://www.linuxidc.com/Linux/2015-07/119939.htmMySQL 5.5 主从双向同步 http://www.linuxidc.com/Linux/2012-12/75973.htmMySQL 5.5主从同步排错 http://www.linuxidc.com/Linux/2014-08/105416.htmMySQL主从复制异步半同步实例 http://www.linuxidc.com/Linux/2014-10/107608.htm本文永久更新链接地址