MySQL dual-master configuration _ MySQL

Prepare the environment: the server operating system is RHEL6.4x86 _ 64, to minimize installation. Both host A and host B disable the firewall and SELINUX. the IP addresses are 192.168.131.129 and 192.168.131.130, and the MySQL version is 5.6.26, which is A common binary package. Install MySQL1. prepare the environment for host A operation: the server operating system is RHEL6.4 x86_64, to minimize installation. Both host A and host B disable the firewall and SELINUX. the IP addresses are 192.168.131.129 and 192.168.131.130, and the MySQL version is 5.6.26, which is A common binary package.

Install MySQL

1. host A operation

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

# Music 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. pid

# Service mysqld start

# Chkconfig mysqld on

# Ss-tunlp | grep 3306

# Mysqladmin-uroot password '000000' # set the database password

2. host B Operations (same as above)

II. configure dual-Master

1. host A operation

# Vim/etc/my. cnf

Server-id = 20

Log-bin = mysql-bin

Log-bin-index = mysql-bin.index

Replicate-ignore-db = test

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

Auto-increment = 2

Auto-increment-offset = 1

# Service mysqld restart

# Mysql-uroot-p123456

Mysql> flush tables with read lock;

Open a remote terminal window and perform the following operations:

# Mysqldump-uroot-p123456-B test> test. SQL

Return to the previous terminal window and perform the following operations:

Mysql> unlock tables;

Mysql> grant replication slave on *. * to 'wdd' @ '192. 168.131.130 'identified by '123 ';

Mysql> flush privileges;

# Scp test. SQL 192.168.131.130:/root

2. host B Operations

# Vim/etc/my. cnf

Server-id = 21

Log-bin = mysql-bin

Log-bin-index = mysql-bin.index

Replicate-do-db = test

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

Auto-increment = 2

Auto-increment-offset = 2

# Service mysqld restart

# Mysql-uroot-p123456 <test. SQL

# Mysql-uroot-p123456

Mysql> grant replication slave on *. * to 'wdd' @ '192. 168.131.129 'identified by '123 ';

Mysql> flush privileges;

3. perform the following operations on host A and host B respectively:

Host

① # Mysql-uroot-p123456

Mysql> show master status;

Mysql> change master

Master_host = '1970. 168.131.130 ',

Master_user = 'wdd ',

Master_password = '000000 ',

Master_log_file = 'mysql-bin.000002 ',

Master_log_pos = 420;

Mysql> start slave;

Mysql> show slave status \ G;

② # Mysql-uroot-p123456

Mysql> show master status;

Mysql> change master

Master_host = '1970. 168.131.129 ',

Master_user = 'wdd ',

Master_password = '000000 ',

Master_log_file = 'MySQL-bin.000002 ',

Master_log_pos = 689;

Mysql> start slave;

Mysql> show slave status \ G;

III. test

1. perform the following operations on host.

Mysql> use test;

Mysql> create table info (

-> Id int (10) not null auto_increment primary key,

-> Name char (20 ));

Mysql> show tables;

2. perform operations on host B

Mysql> use test;

Mysql> show tables;

Mysql> create table person (

-> Id int (20) unsigned not null auto_increment primary key,

-> Name char (20 ));

Mysql> show tables;

3. perform operation inspection on host