MariaDB's GTID replication and multi-source replication, mariadbgtid Replication

Source: Internet
Author: User

MariaDB's GTID replication and multi-source replication, mariadbgtid Replication

What is GTID?

GTID is the global transaction ID (global transaction identifier). It was initially implemented by google and officially added to MySQL in MySQL 5.6. GTID is actually composed of UUID + TID. UUID is the unique identifier of a MySQL instance. TID indicates the number of transactions committed on the instance, and increases monotonically as the transaction is committed.

What is multi-source replication?

Multi-source replication means that one server can replicate from multiple slave servers. This is a new feature of MariaDB 10.0.

Experimental System: CentOS 6.6 _ x86_64

Prerequisites: Firewall and selinux are both disabled.

Tutorial Description: There are three hosts in this experiment, and IP addresses are allocated as topology.

Experimental software: mariadb-10.0.20

Tutorial topology:

    

I. Preparations

1. Modify the names of the three hosts as follows:

    

2. Configure the same hosts file for the three hosts as follows:

    

3. Install mariadb:

tar xf mariadb-10.0.20-linux-x86_64.tar.gz  -C /usr/local/cd /usr/local/ln -sv mariadb-10.0.20-linux-x86_64 mysqlmkdir -pv /mydata/datauseradd -r mysqlchown -R mysql.mysql /mydata/data/cd mysql/chown -R root.mysql .scripts/mysql_install_db --user=mysql --datadir=/mydata/data/cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld on

4. modify the configuration file:

vim /etc/my.cnf--------------------------------->[mysqld]datadir = /mydata/data

5. Create a virtual machine image (optional ):

To avoid interference in the test, I will restore the three hosts to their initial state after completing the GTID test.

Ii. GTID Replication

1. Configure mysql1 as master:

[mysqld]server-id       = 1binlog-format=ROWlog-bin=/mydata/data/master-binlog-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql1

2. Configure mysql2 as slave:

[mysqld]server-id       = 2binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql2

3. Configure mysql3 as slave:

[mysqld]server-id       = 3binlog-format=ROWlog-bin=/mydata/data/mysql-binlog-slave-updates=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-host=mysql3

4. Create a copy user on the master node:

service mysqld start
/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'jason'@'192.168.19.%' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

5. operate on two slave nodes simultaneously:

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------->CHANGE MASTER TO MASTER_HOST='mysql1',MASTER_USER='jason',MASTER_PASSWORD='123456',MASTER_USE_GTID=slave_pos;START SLAVE;

6. view the status on the slave node:

SHOW SLAVE STATUS\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: jason                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000007          Read_Master_Log_Pos: 321               Relay_Log_File: mysql2-relay-bin.000002                Relay_Log_Pos: 613        Relay_Master_Log_File: master-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes
... Using_Gtid: Slave_Pos
SHOW GLOBAL VARIABLES LIKE '%gtid%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| gtid_binlog_pos        | 0-1-3 || gtid_binlog_state      | 0-1-3 || gtid_current_pos       | 0-1-3 || gtid_domain_id         | 0     || gtid_ignore_duplicates | OFF   || gtid_slave_pos         | 0-1-3 || gtid_strict_mode       | OFF   |+------------------------+-------+

7. Test: Create a database on the master node and view the database on the other two nodes:

Create database jjj; // CREATE the database show databases on the master node; // view the DATABASE from the slave Node

We can see that both slave servers can copy the data of the master server. The test was successful. In the following multi-source replication experiment, I will restore all hosts to the original state from a virtual snapshot.

     

Iii. Multi-source Replication

1. Configure mysql1 and mysql2 as the master host, configure mysql3 as the slave host, and edit the configuration file:

Mysql1:

[mysqld]server-id       = 1log-bin=/mydata/data/mysql-bin

Mysql2:

[mysqld]server-id       = 2log-bin=/mydata/data/mysql-bin

Mysql3:

[mysqld]
#log-bin=mysql-bin#binlog_format=mixedserver-id = 3relay_log=/mydata/data/relay-log

2. Create and copy users for mysql1 and mysql2:

service mysqld start/usr/local/mysql/bin/mysql-------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.19.66' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

2. view the binary log location of the two hosts. The two locations are exactly the same:

SHOW MASTER LOGS;

3. Configure change master on mysql3:

service mysqld start/usr/local/mysql/bin/mysql----------------------------------------------->CHANGE MASTER 'm1' TO MASTER_HOST='mysql1',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;CHANGE MASTER 'm2' TO MASTER_HOST='mysql2',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=647;START ALL SLAVES;
SHOW ALL SLAVES STATUS\G*************************** 1. row ***************************              Connection_name: m1              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it               Slave_IO_State: Waiting for master to send event                  Master_Host: mysql1                  Master_User: slave                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 647               Relay_Log_File: relay-log-m1.000002                Relay_Log_Pos: 535        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes
               ...*************************** 2. row *************************** Connection_name: m2 Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_IO_State: Waiting for master to send event Master_Host: mysql2 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 647 Relay_Log_File: relay-log-m2.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
               ...

4. test:

Mysql1:

CREATE DATABASE mydb1;
CREATE TABLE mydb1.jjj (id int);

Mysql2:

CREATE DATABASE mydb2;
CREATE TABLE mydb2.jjj (id int);

Mysql3:

SHOW DATABASES;
SHOW TABLES FROM mydb1;
SHOW TABLES FROM mydb2;

Now, multi-source replication has been demonstrated. Thank you! If you have any questions, contact me at QQ: 82800452.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.