MariDB 10.0.10 Multi-Master and one-Slave Architecture and Multi-thread replication Architecture of Relational Database 02/24 Update SLTechnology News&Howtos

MariDB 10.0.10 Multi-Master and one-Slave Architecture and Multi-thread replication Architecture of Relational Database

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. GTID that appears after MySQL 5.6:

GTID concept:

1.GTID is a unique transaction sequence number consisting of the server's UUID and transaction sequence number.

For example: UUID:N

1122-3322-1122purl

1122-3322-1122purl 2

2.GTID will be treated as the first part of each transaction and will be automatically generated and stored in the binary log

3.GTID can be used to track transaction transfers between master and slave.

4.GTID is mainly used in the function of HA. In a multi-master model, to indicate which specific master server a transaction originated from.

5. The slave server does not modify or add a new GTID, even if the slave server is configured as the master server for other slave servers, so the transaction flow can be tracked.

When 6.GTID is enabled, it will be displayed in gtid_executed.

Replication-related tools that require a python2.7 environment

1.mysqlreplicate: adding slave nod

2.mysqlcheck: implement the verification mechanism

3.mysqlrplshow: discover and display the replication topology, how many servers are there at each level of replication

4.mysqlfailover: promote a slave node to a master node

5.mysqlrpladmin: a management tool for manual scheduling, dispatching a normal slave node to the master node

Multithread replication with the help of GTID

The slave server can send multiple 1amp O threads to the master server to make mysqldump requests to the master server, which will make the information read out one after another out of order. Therefore, by dividing the database to achieve multithreading, each database transaction can only be replicated by one thread. But even so, if there is only one primary server, multithreading does not improve performance because there is only one binary server and the network bandwidth is limited. To really improve the performance, we need a slave multi-master model. Multithreaded slave is usually multiple sql, one I / O thread, and multiple SQL threads. Through the GTID mechanism, different transactions can be applied through different threads.

Configure the parameters required to enable GTID to enable replication (configured under the [mysqld] paragraph)

1.binlog-format: binary log format, available as row, statement, and mixed

It should be noted that when setting the isolation level to READ-COMMITED, the binary log format must be set to ROW. Now MySQL officials think that STATEMENT is no longer suitable for further use, but the mixed type under the default transaction isolation level may lead to inconsistency between master and slave data.

2.log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port and report-host: used to launch GTID and meet other ancillary needs

Master-info-repository and relay-log-info-repository: enable these two items, which can be used to secure binary and slave servers in the event of a crash

3.sync-master-info: enable to ensure that no information is lost

4.slave-paralles-workers: set the number of SQL threads from the server; 0 means to turn off multithreaded replication

5.binlog-checksum, master-verify-checksum, and slave-sql-verify-checksum: enable all validation features related to replication

6.binlog-rows-query-log-events: enabled to record event-related information in binary logs, reducing the complexity of troubleshooting

7.log-bin: enable binary logging, which is a basic prerequisite for ensuring replication

8.server-id: the id number of all servers in the same replication topology must be unique

9.report-host: whether the hostname and IP address of the slave server are reported to the master server when registering with the slave server. Using SHOW SLAVE HOSTS on the primary server, you can view

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

10.report-port: whether to report the slave server link port to the master server

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

11.master-info-repository: from whether the service records the login and connection information from the server in the file master.info or in the mysql.slave_master_info table

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

12.relay-log-info-repository: relay log related data record document or table

This option causes the server to log its relay log info to a file or a table.

13.log_slave_updates: whether to accept updates from the server

Whether updates received by a slave server

Implementing multithreaded replication with mariadb 10

Prepare the environment:

1. System environment: Centos6.5

two。 Database version: 10.0.10-MariaDB-log MariaDB Server

3.Host:

Master host: master.samlee.com 172.16.100.7

Slave host: slave.samlee.com 172.16.100.8

The implementation process is as follows:

1. Install MariaDB and initialize the database service in Master, modify the hostname, and define the host file:

-define the hostname # vim / etc/sysconfig/networkNETWORKING=yesHOSTNAME=master.samlee.com-- definition host file Implement hostname resolution # vim / etc/hosts172.16.100.7 master.samlee.com master172.16.100.8 slave.samlee.com slave-- install MariaDB and initialize database service-plan database storage directory # fdisk / dev/sda-add a new partition size: 20g partition type: LVM# kpartx / dev/sda# partx-a / dev/sda# kpartx-af / dev/sda# pvcreate / dev/sda3 # vgcreate myvg/ dev/sda3 # lvcreate-L 10G-n mydata myvg# mke2fs-t ext4 / dev/myvg/mydata # mkdir / mydata# vim / etc/fstab # add this line / dev/myvg/mydata / mydata ext4 defaults at the end Acl 0 "mount-a # mkdir / mydata/data# groupadd-r-g 3306 mysql # create mysql service user group # useradd-r-g 3306-d / mydata/data/- s / sbin/nologin mysql # create mysql service user # chown-R mysql.mysql / mydata/data/# setfacl-m u:mysql:rwx / mydata/data/-- install MariaDB and configuration initialization # cd / root/# tar-xf mariadb -10.0.10-linux-x86_64.tar.gz-C / usr/local/# cd / usr/local/# ln-sv mariadb-10.0.10-linux-x86_64 mysql# mkdir / mydata/ {binlogs Relaylogs}-pv# chown-R mysql.mysql / mydata/*# mkdir / etc/mysql# cp / usr/local/mysql/support-files/my-large.cnf / etc/mysql/my.cnf modify / etc/mysql/my.cnf# vim / etc/mysql/my.cnf-add the following option datadir=/mydata/data# chown-R mysql.mysql / etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > / etc/profile in the [mysqld] paragraph .d / mysql.sh# source / etc/profile.d/mysql.sh# mkdir / var/lib/mysql# chown-R mysql.mysql / var/lib/mysql/# cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld# chmod + x / etc/init.d/mysqld# scripts/mysql_install_db-user=mysql-datadir=/mydata/data/# service mysqld start

two。 Install MariaDB and initialize the database service in Slave, modify the hostname, and define the host file:

-define the hostname # vim / etc/sysconfig/networkNETWORKING=yesHOSTNAME=slave.samlee.com-- definition host file Implement hostname resolution # vim / etc/hosts172.16.100.7 master.samlee.com master172.16.100.8 slave.samlee.com slave-- install MariaDB and initialize database service-plan database storage directory # fdisk / dev/sda-add a new partition size: 20g partition type: LVM# kpartx / dev/sda# partx-a / dev/sda# kpartx-af / dev/sda# pvcreate / dev/sda3 # vgcreate myvg/ dev/sda3 # lvcreate-L 10G-n mydata myvg# mke2fs-t ext4 / dev/myvg/mydata # mkdir / mydata# vim / etc/fstab # add this line / dev/myvg/mydata / mydata ext4 defaults at the end Acl 0 "mount-a # mkdir / mydata/data# groupadd-r-g 3306 mysql # create mysql service user group # useradd-r-g 3306-d / mydata/data/- s / sbin/nologin mysql # create mysql service user # chown mysql.mysql / mydata/data/# setfacl-m u:mysql:rwx / mydata/data/-- install MariaDB and configuration initialization # cd / root/# tar-xf mariadb-10 .0.10-linux-x86_64.tar.gz-C / usr/local/# cd / usr/local/# ln-sv mariadb-10.0.10-linux-x86_64 mysql# mkdir / mydata/ {binlogs Relaylogs}-pv# chown-R mysql.mysql / mydata/*# mkdir / etc/mysql# cp / usr/local/mysql/support-files/my-large.cnf / etc/mysql/my.cnf modify / etc/mysql/my.cnf# vim / etc/mysql/my.cnf-add the following option datadir=/mydata/data# chown-R mysql.mysql / etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > / etc/profile in the [mysqld] paragraph .d / mysql.sh# source / etc/profile.d/mysql.sh# mkdir / var/lib/mysql# chown-R mysql.mysql / var/lib/mysql/# cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld# chmod + x / etc/init.d/mysqld# scripts/mysql_install_db-user=mysql-datadir=/mydata/data/# service mysqld start

3. Configure the Master server as the GTID master service profile (operational configuration on the master server)

# vim / etc/mysql/ my.cnf [mysqld] port = 3306socket = / tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8queryroomcachesisize= 16Mthread_concurrency = 8datadir=/mydata/datalog-bin=/mydata/binlogs/master-binbinlog_format=rowserver-id = 1log-slave-updates = Truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = 1slave-sql-verify-checksum = 1binlog-rows-query-log-events = 1report-port = 3306report-host = master.samlee.com

4. Test whether the Master-GTID service is configured successfully: (operational configuration on the master server)

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | | gtid_binlog_state | | gtid_current_pos | gtid_domain_id | | 0 | | gtid_ignore_duplicates | OFF | | gtid_slave_pos | gtid_strict_mode | OFF | +-+ MariaDB [(none)] > CREATE DATABASE mydb | MariaDB [(none)] > CREATE TABLE mydb.t1 (Name CHAR (30)); MariaDB [(none)] > SHOW BINARY LOGS +-+-+ | Log_name | File_size | +-+-+ | master-bin.000001 | 344 | | master-bin.000002 | 590 | +-- -+ MariaDB [(none)] > SHOW BINLOG EVENTS IN 'master-bin.000002' +- -+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +- -+-- + | master-bin.000002 | 4 | Format_desc | 1 | 248 | Server ver: 10.0.10-MariaDB-log Binlog ver: 4 | master-bin.000002 | 248 | Gtid_list | 1 | 277 | [] | master-bin.000002 | 277 | Binlog_checkpoint | 1 | 321 | master-bin.000002 | | master-bin.000002 | 321 | Gtid | | 1 | 363 | GTID 0-1-1 | | master-bin.000002 | 363 | Query | 1 | 450 | CREATE DATABASE mydb | | master-bin.000002 | 450 | Gtid | 1 | 492 | GTID 0-1-2 | | | master-bin.000002 | 492 | Query | 1 | 590 | CREATE TABLE mydb.t1 (Name CHAR (30)) | +-- | -+-now we can see that GTID has started recording.

5. Configure the Slave server for GTID slave service profile (operate the configuration from the server)

[mysqld] port = 3306socket = / tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8queryRepositoryAchesisize= 16Mthread_concurrency = 8datadir=/mydata/datalog-bin=/mydata/data/master-binbinlog_format=ROWserver-id = 200log Master-info=1slave-parallel-threads=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-port=3306report-host=slave.samlee.com

6. Create a replication user on the Master server (manipulate the configuration on the primary server)

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES; Note: 172.16%.% is a slave node server; if you want to authorize more nodes at once, you can modify it as needed.

7. Provide the initial dataset for the standby node (operational configuration on the primary server)

Lock the master table, back up the data on the master node, and restore it to the slave node; if GTID is not enabled, you need to use the show master status command on master to check the binary log file name and event location for later use when starting the slave node.

# mysqldump-all-databases-lock-all-tables-flush-logs-master-data=2 > all.sql# scp all.sql 172.16.100.8:/tmp/

8. Restore the initial dataset for the standby node, connect to the primary node server (operate the configuration on the slave server)

# mysql

< /tmp/all.sql MariaDB [(none)]>

9. Verify GTIP replication status information (operational configuration on master and slave servers)

< hellodb.sql (2)执行(1)操作后马上在Slave服务器进行监控# watch -n .5 "mysql -e 'show processlist\G'" 10.查询GTID状态信息及应用调试(从服务器上操作配置) MariaDB [(none)]>

11. Use GTID to connect to the master node server for master-slave replication (manipulate the configuration on the slave server)

MariaDB [(none)] > STOP SLAVE;MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_USE_GTID=current_pos;MariaDB [(none)] > START SLAVE;MariaDB [(none)] > SHOW SLAVE STATUS\ GUsing_Gtid: Current_PosGtid_IO_Pos: 0-1-39MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% gtid%' +-+-+ | Variable_name | Value | +-+-+ | gtid_binlog_pos | 0-1-39 | | gtid_binlog_state | 0-200-128Power0-1-39 | | gtid_current_pos | 0-1-39 | | gtid_domain_id | 0 | gtid_ignore_duplicates | OFF | | gtid_slave_pos | 0-1-39 | | gtid_strict_mode | OFF | +-| -+

Implementing a multi-source replication architecture using mariadb 10

Prepare the environment:

1. System environment: Centos6.5

two。 Database version: 10.0.10-MariaDB-log MariaDB Server

3.Host:

Master1 host: master.samlee.com 172.16.100.7

Master2 host: master1.samlee.com 172.16.100.10

Slave host: slave.samlee.com 172.16.100.8

Implement the summary architecture of multi-branch database

(1) .Master1 host\ Master2 host\ Slave host-defines the hosts hostname resolution file, as follows:

# vim / etc/hosts172.16.100.7 master.samlee.com master172.16.100.10 master2.samlee.com master2172.16.100.8 slave.samlee.com slave

(2) .Master1 host\ Master2 host configuration file is as follows:

Master1 host: # vim / etc/mysql/my.cnfserver-id = 100log-bin=mysql-binMaster2 host: # vim / etc/mysql/my.cnfserver-id = 200log-bin=mysql-bin

(3) the Slave host configuration file is as follows:

# vim / etc/mysql/my.cnfserver-id = 300relay-log=relay-bin

(4) create a replication user on the Master1 host\ Master2 host (operation configuration on the primary server)

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES

(5) query Master1 host\ Master2 host binaries and event location

(6) connect Master1 and Master2 master nodes on the Slave host

MariaDB [(none)] > CHANGE MASTER 'master' TO MASTER_HOST='172.16.100.7',MASTER_PORT=3306,MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=867;MariaDB [(none)] > CHANGE MASTER' master1' TO MASTER_HOST='172.16.100.10',MASTER_PORT=3306,MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=867 MariaDB [(none)] > START SLAVE 'master';MariaDB [(none)] > START SLAVE' master1';MariaDB [(none)] > SHOW ALL SLAVES STATUS\ gateway * 1. Row * * Connection_name: master 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: 172.16.100.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 867 Relay_Log_File: relay-bin-master.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes** 2. Row * * Connection_name: master1 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: 172.16.100.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 867 Relay_Log_File: relay-bin-master1.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: YesMariaDB [(none)] > SHOW SLAVE 'master' STATUS\ G

The tests are as follows:

Tests show that the multi-source replication architecture has been completed.

Summary

1) compared with mysql 5.6, mariadb does not support parameters:

Gtid-mode=on

Enforce-gtid-consistency=true

2) modified parameters:

Parameter slave-parallel-workers is changed to slave-parallel-threads.

3) commands used to connect to the main service:

A new parameter: MASTER_USER_GTID= {current_pos | slave_pos | no}

This parameter is always unsuccessful in multi-master and one-slave experiments.

4) when configuring the slave server, it is best to use replicate_ignore_db to ignore some system libraries.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.