A simple case for MySQL master-slave replication, dual master Model

write in front: If this article is fortunate enough to be seen by a friend and found wrong, I hope to criticize. If you do not understand the place, we would like to discuss together.

Fundamentals of MySQL Replication

Basic issues that MySQL replication solves

Keep the data of one MySQL server in sync with the data from other MySQL servers.

How MySQL replication works

The working schematic of MySQL replication is shown below (figure from high-performance MySQL version 3rd)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/E1/wKioL1QdO8Py89y1AAIWXlt9l-I392.jpg "title=" Figure 1 "alt = "Wkiol1qdo8py89y1aaiwxlt9l-i392.jpg"/>

Basic steps for MySQL master-slave replication:

1. Start the binary files on the main library and record the data changes to the binary log;

2, the repository copies the binary log on the main library to its own relay log;

3. The standby library reads events from its own trunk log and replays them onto the repository data.

Case topology diagram

Master-slave replication

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/E1/wKioL1QdPkGz3FyWAACCmnTpd1Q681.jpg "title=" Figure 2 "alt = "Wkiol1qdpkgz3fywaaccmntpd1q681.jpg"/>

Dual Master Model

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/E0/wKiom1QdUN3C-5oIAACDUQB7CRg365.jpg "title=" Figure 3 "alt = "Wkiom1qdun3c-5oiaacduqb7crg365.jpg"/>

Prerequisite : Installation of mariadb-5.5.36 on master and slave two hosts

Installing mariadb-5.5.36

Create a directory where database-related files are stored

# mkdir-pv/mysql/data# mkdir/mysql/binlogs# mkdir/mysql/relaylogs

Create user and user groups for data runtime

# groupadd-r mysql# useradd-g mysql-s/sbin/nologin-m-d/mysql/data/-R MySQL

Modify permissions for database-related file directories

# Chown-r mysql:mysql/mysql/*

Compiling and installing maridb-5.5.36

# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -c /usr/local/# cd /usr/local/#  ln -sv mariadb-5.5.36-linux-x86_64/ mysql# cd mysql/# chown -R  root:mysql ./*# mkdir /etc/mysql# cp support-files/my-large.cnf /etc/mysql/ my.cnf# vim /etc/mysql/my.cnf     datadir=/mysql/data# cp  Support-files/mysql.server /etc/rc.d/init.d/mysqld# chkconfig --add mysqld# chkconfig  mysqld on# vim /etc/man.config     manpath /usr/local/mysql /man# ln -sv /usr/local/mysql/include/ /usr/include/mysql# echo  "/usr/local/ mysql/lib/" > /etc/ld.so.conf.d/mysql.conf# ldconfig# vim /etc/profile.d/mysql.sh     export path=/usr/local/mysql/bin: $PATH # source /etc/profile.d/mysql.sh#  scripts/mysql_install_Db --user=mysql --datadir=/mysql/data/# /etc/rc.d/init.d/mysqld start 

Configuring Master-slave replication

Configuring the master server

To configure the basic process:

1. Enable the binary log

2. Define Server-id

3. Create an account with copy rights

Edit configuration file, start binary log and define Server-id

[[email protected] ~]# Vim/etc/mysql/my.cnflog-bin=/mysql/binlogs/master-binserver-id = 1

Create an account with copy permissions

[[email protected] ~]# mysqlmariadb [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 1 0.170.%.% ' identified by ' [email protected] '; MariaDB [(None)]> FLUSH privileges;

Configuring the Slave server

To configure the basic process:

1. Turn off the binary log and start the relay log

2. Define Server-id

3. Connect the master server with an account with copy rights

4. Start the I/O thread and SQL thread

Edit the configuration file, start the trunk log function and define Server-id

[Email protected] ~]# vim/etc/mysql/my.cnf #log-bin=mysql-binrelay-log=/mysql/relaylogs/relay-logserver-id = 11

View status information for the master node

MariaDB [(None)]> SHOW MASTER STATUS;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      master-bin.000001 |              495 |                  | |

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

1 row in Set (0.07 sec)

Connect to the master server using an account with replication privileges

[[email protected] ~]# MySQL

MariaDB [(None)]> change MASTER to master_host= ' 10.170.2.36 ', master_user= ' repluser ', master_password= ' [email Protected] ', master_log_file= 'master-bin.000001', master_log_pos=495;

Starting the I/O thread and SQL thread

MariaDB [(None)]> START SLAVE;

Test Master-slave replication

To add a database on the master server

# MySQL < Mydb.sql

# MySQL < Hellodb.sql

View the database on the master server

[[email protected] ~]# MySQL

MariaDB [(None)]> SHOW DATABASES;

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

| Database |

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

| Information_schema |

| Hellodb |

| MyDB |

| MySQL |

| Performance_schema |

| Test |

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

6 rows in Set (0.05 sec)

Viewing the database on a slave server

[[email protected] ~]# MySQL

MariaDB [(None)]> SHOW DATABASES;

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

| Database |

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

| Information_schema |

| Hellodb |

| MyDB |

| MySQL |

| Performance_schema |

| Test |

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

6 rows in Set (0.00 sec)

Configuring a dual-master model

Preparatory work

Restore the two hosts that have already installed the MARIADB database to a state that has just been installed

Specific process:

1, stop the MySQL service;

2. Delete all data in/mysql/data/,/mysql/binlogs/,/mysql/relaylogs/directory;

3. Initialize MySQL and start the MySQL service.

Configuring Master1 and MASTER2 Servers

Basic Configuration requirements:

1, Master1 and Master2 have to create an account with copy rights;

2, Master1 and Master2 have to start the relay log and binary log;

3, in order to ensure that the field with automatic growth function can generate the ID correctly, you need to configure Master1 and Master2 to use even and odd ID numbers respectively;

4, Master1 and Master2 are to configure each other as their own master node.

Configuring the Master1 Server

Editing a configuration file

[Email protected] ~]# VIM/ETC/MYSQL/MY.CNF

Log-bin=/mysql/data/master1-bin

Relay-log=/mysql/relaylogs/relay1-bin

Auto-increment-increment = 2

Auto-increment-offset = 1

Server-id = 1

Restart MySQL Service

#/etc/init.d/mysqld Restart

Create an account with copy permissions

[[email protected] ~]# MySQL

MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 10.170.%.% ' identified by ' [E Mail protected] ';

View status information for Master2

MariaDB [(None)]> SHOW MASTER STATUS;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      master2-bin.000001 |              425 |                  | |

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

Connect to Master2

MariaDB [(None)]> change MASTER to master_host= '10.170.2.49', master_user= ' repluser ', master_password= ' [ Email protected] ', master_log_file= 'master2-bin.000001', master_log_pos=425;

Starting the I/O thread and SQL thread

MariaDB [(None)]> START SLAVE;

Configuring the Master2 Server

Editing a configuration file

[Email protected] ~]# VIM/ETC/MYSQL/MY.CNF

Log-bin=/mysql/data/master2-bin

Relay-log=/mysql/relaylogs/relay2-bin

Auto-increment-increment = 2

Auto-increment-offset = 2

Server-id = 11

Restart MySQL Service

#/etc/init.d/mysqld Restart

Create an account with copy permissions

[[email protected] ~]# MySQL

MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 10.170.%.% ' identified by ' [E Mail protected] ';

View status information for Master1

MariaDB [(None)]> SHOW MASTER STATUS;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      master1-bin.000001 |              425 |                  | |

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

Connect to Master1

MariaDB [(None)]> change MASTER to master_host= '10.170.2.36', master_user= ' repluser ', master_password= ' [ Email protected] ', master_log_file= 'master1-bin.000001', master_log_pos=425;

Starting the I/O thread and SQL thread

MariaDB [(None)]> START SLAVE;

Test the Dual master model

Adding the Hellodb.sql database on the Master1 server

Mys[[email protected] ~]# MySQL < hellodb.sql

On the Master2 server, view the database

[[email protected] ~]# MySQL

MariaDB [(None)]> SHOW DATABASES;

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

| Database |

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

| Information_schema |

| Hellodb |

| MySQL |

| Performance_schema |

| Test |

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

5 rows in Set (0.01 sec)

Adding the Mydb.sql database on the Master2 server

[[email protected] ~]# MySQL < mydb.sql

On the Master1 server, view the database

[[email protected] ~]# MySQL

MariaDB [(None)]> SHOW DATABASES;

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

| Database |

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

| Information_schema |

| Hellodb |

| MyDB |

| MySQL |

| Performance_schema |

| Test |

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

6 rows in Set (0.05 sec)

Note: Throughout the configuration process, this article uses the master server as a Master1 server and uses the slave server as a Master2 server.

A simple case for MySQL master-slave replication, dual master Model