Configure MySQL Master primary replication step

MySQL version: Mysql-5.6.24-solaris10-sparc-64bit.tar

Operating system: Solaris 11g U10

Operation User: Use non-root operation installation, a-way server IP address is 192.168.1.1 B IP address is 192.168.1.2 (should be changed to actual IP address)

1. Install MySQL Database

　　

1) Create the MySQL database directory under the predefined directory (I use the directory for/usr/db/)

$mkdir MySQL

2) Create the data directory under MySQL directory

$mkdir –p Mysql_3306/data

3) Unzip the MySQL file in the MySQL directory

$tar –XVF Mysql-5.6.24-solaris10-sparc-64bit.tar $MV mysql-5.6.24-solaris10-sparc-64bit MySQL

4) Initialize the data directory

$./mysql/scripts/mysql_install_db--basedir=/usr/sjes/mysql/mysql--datadir=/usr/sjes/mysql/mysql_3306/data/-- User=mor

5) Configure the database startup file

$vi/mysql/my.cnf

A-way MySQL configuration content: (user changed to correspond to the actual running user)

Sql_mode=no_engine_substitution,strict_trans_tables Socket=/tmp/mysql.sock Pid-file=/usr/db/mysql_3306/mysql.pid Datadir=/usr/db/mysql_3306/data basedir=/usr/db/mysql/ User=test Character-set-server=utf8 wait_timeout=31536000 interactive_timeout=31536000 Server_id=1

The B-way MySQL configuration reads as follows:

Sql_mode=no_engine_substitution,strict_trans_tables Socket=/tmp/mysql.sock Pid-file=/usr/db/mysql_3306/mysql.pid Datadir=/usr/db/mysql_3306/data basedir=/usr/db/mysql/ User=test Character-set-server=utf8 wait_timeout=31536000 interactive_timeout=31536000 server_id=2

6) Start the MySQL database and change the root default password

$./bin/mysqld_safe--defaults-file=/usr/db/mysql/my.cnf--user=test 2>&1 & $mysql –u root–p (enter without entering the password) Mysql>use MySQL; mysql> Update user Set Password=password (' 123456 ') where user= ' root '; Mysql>flush privileges;

7) Create a DB instance in two databases (create a database to synchronize in two databases)

Mysql> CREATE DATABASE test1 default character set ' UTF8 '; Mysql> CREATE DATABASE test2 default character set ' UTF8 '; Mysql> CREATE DATABASE test3 default character set ' UTF8 ';

The above steps are a specific procedure for creating a/b two-way database, with the following configuration of the primary master replication steps:

2 Configuring primary primary primary master replication

1) Stop MySQL Database

$./bin/mysqladmin shutdown–p 3306–s/tmp/mysql.sock

2) Edit the MySQL configuration file of A/b two ways respectively

$vi my.cnf

A my.cnf configuration file is added as follows: (auto-increment-increment = 2 Auto-increment-offset = 1 To prevent the self-increment field from adding data in the two-way database, In general, only one way to add, update the operation, the other side only to do as read)

################# #replication config####################### Log-bin = Mysql-bin Auto-increment-increment = 2 Auto-increment-offset = 1 Replicate-do-db = Test1 Replicate-do-db = Test2 Replicate-do-db = Test3 binlog-ignore-db = MySQL Binlog-ignore-db = Information_schema Slave-skip-errors=all Log-slave-updates Symbolic-links=0 Skip-name-resolve

The new additions to the B-way my.cnf configuration file are as follows:

################# #replication config####################### Log-bin = Mysql-bin Auto-increment-increment = 2 Auto-increment-offset = 2 Replicate-do-db = Test1 Replicate-do-db = Test2 Replicate-do-db = Test3 binlog-ignore-db = MySQL Binlog-ignore-db = Information_schema Slave-skip-errors=all Log-slave-updates Symbolic-links=0 Skip-name-resolve

3) Start MySQL database

$./bin/mysqld_safe--defaults-file=/usr/db/mysql/my.cnf--user=test 2>&1 &

4) access to the database and mutual authorization

A road executes the following command

Mysql>grant all privileges on * * to ' root ' @ ' 192.168.1.2 ' identified by ' 123456 '; Mysql>flush privileges;

Route B executes the following command

Mysql>grant all privileges on * * to ' root ' @ ' 192.168.1.1 ' identified by ' 123456 '; Mysql>flush privileges;

5) See if authorization is successful

Mysql> Show grants for [email protected]' 192.168.1.1 ';

6) Mutual Bin-log information

A road executes the following command:

Mysql> Show master status;

Route B executes the following command:

Mysql> Show master status;

A road executes the following command

mysql> Change Master to master_host= ' 192.168.1.2 ', master_user= ' root ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=2626;

Route B executes the following command

mysql> Change Master to master_host= ' 192.168.1.1 ', master_user= ' root ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=3625;

7) Start replication on A/b two road

Mysql>start slave;

8) View replication status on a, B, two road

A-Path replication status:

Mysql> Show Slave Status\g

B-Way Replication status

Slave_io_running:yes
Slave_sql_running:yes A two value of Yes indicates that the replication link is normal .

9) Test Replication

Execute the following command in the A-path database:

Mysql>use test; mysql> CREATE TABLE ' Admin_info ' ( ' username ' varchar (+) not NULL, ' Password ' varchar (+) DEFAULT NULL, PRIMARY KEY (' username ') ) Engine=innodb DEFAULT Charset=utf8; Mysql>show tables;

See if the table structure is synchronized in the B-way database;

Execute the following command in the B-way database:

Mysql>insert into ' admin_info ' VALUES (' admin ', ' system ', ' 25d55ad283aa400af464c76d713c07ad ');

See if the records are synchronized in the A-path database.

The above is the entire process of configuring primary master replication for MySQL, and allows you to re-execute the steps in 2-(6) when there are problems with replication on both sides.

Configure MySQL Master primary replication step