MySQL multi-master one from sync

MySQL multiple master one from Sync

Lab Preparation: HostAand HostBas the main, itsIPthe addresses were192.168.131.129and the192.168.131.130, the hostCas slave server, configure from server aboveMySQLmulti-instance, itsIPaddress is192.168.131.136, three servers are shutting down the firewall andSELINUX,MySQLversion is5.6.26, a generic binary package

1. Host A and Host B primary server MySQL Universal binary package installation and initialization

   # 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 , host A and Host B Configuration

① Host 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;

② Host 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 , host C installing from a server MySQL Multi-instance

# 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/

4. # mysqld_multi--defaults-extra-file=/etc/my.cnf report
5. # ss-tunlp | grep

Note: For safety, you should give MySQL instance to set the password, the command is as follows

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

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

4. host c Multi-instance test operation

① # 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 , Test

① Login Host A

mysql> CREATE DATABASE Wdd;

Mysql> SHOW DATABASES;

② Login Host B

mysql> CREATE DATABASE info;

mysql> show databases;

③ View MySQL instance, instance 129 and the the

Example 129

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

Mysql> START SLAVE;

Example the

# mysql-u Root-s/data/3307/mysql.sock

mysql> show databases;

Note: MySQL When multiple instances are logged in, you do not need to specify a port, which means you can omit - P option, even if you use - P option, the port can also be arbitrarily specified, that is, you can write a port number, as long as the socket is unchanged, then the login is the same MySQL instance.

MySQL multi-master one from sync