MySQL Master master replication test

MySQL Dual Master replication configuration test process

Server environment:

+-------------------+---------------------------------------------------

| | server Node 1 | server Node 2 |

+-------------------+------------------------+-------------------------+

| OS | RHEL 5.6 | RHEL 5.6 |

+-------------------+------------------------+-------------------------+

| DB Version | MySQL 5.7.10 | MySQL 5.7.10 |

+-------------------+------------------------+-------------------------+

| HostName | Linux01 | Linux02 |

+-------------------+------------------------+-------------------------+

| IPAddr |10.10.88.11 |10.10.88.12 |

+-------------------+------------------------+-------------------------+

1. Install MySQL to the database server node 1

1.1 Create MySQL user, MySQL installation directory, data directory

Useradd MySQL

Mkdir-p/mysql

Mkdir-p/mysql/mysqldata

Chown Mysql:mysql-r/mysql

1.2 Upload the MySQL installation package to the server and unzip

Cd/mysql

#上传安装包到此目录

Unzip Mysql.zip

Tar-xvzf mysql.tar.gz

Chown Mysql:mysql-r mysql-advanced-5.7.10-linux-glibc2.5-x86_64

#创建ln for easy management of MySQL

Ln-s mysql-advanced-5.7.10-linux-glibc2.5-x86_64 MySQL

1.3 Adding a MySQL service

CP./support-files/mysql.server/etc/init.d/mysql

#修改/etc/init.d/mysql in Basedir and DataDir as the actual directory

Chkconfig--add MySQL

1.4 Copy the default profile to/ETC/MY.CNF and edit the configuration file

CP./SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF

Configure the required parameters in the/ETC/MY.CNF configuration file, such as: Basedir, DataDir, Log-bin, server_id, etc.

1.5 Initializing the database

Cd/mysql/mysql

./bin/mysqld--initialize--user=mysql

./bin/mysql_ssl_rsa_setup

./bin/mysqld_safe--user=mysql &

Log in using the temporary password provided in the initialization library and run the following command to change the password:

Set Password=password (' 123456 ');

Flush Privileges

2. Copy a node's database to the 2 node and make the parameter modification, delete the/mysql/mysqldata/auto.cnf file, and automatically generate the file when the database restarts.

3. Configuring Primary master Replication

3.1 Create replication required users, users need slave permissions (necessary), File,select permissions (optional)

Grant replication slave, file, select On *. * to ' mysql_sync ' @ ' linux01 ' identified by ' 123456 '

Grant replication slave, file, select On *. * to ' mysql_sync ' @ ' linux02 ' identified by ' 123456 '

Note: If you want to have permission to execute the "load TABLE from master" or "Load DATA from master" statement on Slave, you must grant global FILE and select permissions.

3.2 Modify the configuration files on both servers separately

Server 1:

Log-bin=mysql-bin-db01

server-id=11

Log-slave-updates

Slave-skip-errors=all

auto_increment_increment=2

Auto_increment_offset=1

Server 2:

Log-bin=mysql-bin-db02

Server-id=12

Log-slave-updates

Slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=2

Where the Server-id two servers must be configured differently,

Auto_increment_increment Configuring the Autogrow field in each node increments per increment

Auto_increment_offset Configuring the initial value of the auto-grow field for each node starts from how much

If we do not set these two variables, the table containing the autogrow field will be maintained on multiple servers, and duplication will result in duplicate errors.

As set above, the value of the A-node self-growing field will be 1,3,5 .... and the B-node will be 2,4,6 ...

3.3 Restarting two DB instances

Server MySQL Restart

3.4 Performed in two databases: Show master status; View Primary server Status:

Node 1:

Mysql> Show master status;

+-----------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-----------------------+----------+--------------+------------------+-------------------+

|     mysql-bin-db01.000001 |              1096 |                  |                   | |

+-----------------------+----------+--------------+------------------+-------------------+

1 row in Set (3.71 sec)

Node 2:

Mysql> Show master status;

+-----------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-----------------------+----------+--------------+------------------+-------------------+

|      mysql-bin-db02.000001 |              625 |                  |                   | |

+-----------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

3.5 Configuration from server slave (two nodes executed separately)

Node 1:

mysql> Change Master to master_host= ' 10.10.88.12 ', master_user= ' Mysql_sync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin-db02.000001 ', master_log_pos=625;

mysql> start slave; To start the Copy from Server feature

Node 2:

mysql> Change Master to master_host= ' 10.10.88.11 ', master_user= ' Mysql_sync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin-db01.000001 ', master_log_pos=1096;

mysql> start slave; To start the Copy from Server feature

3.6 Check Status:

Show Slave Status\g

This article is from the "Database Road" blog, make sure to keep this source http://dbaway.blog.51cto.com/7099215/1747646

MySQL Master master replication test