關(guān)系型數(shù)據(jù)庫(kù)之MariDB10.0.10多主一從的架構(gòu)及多線程復(fù)制架構(gòu)-創(chuàng)新互聯(lián)

一、MySQL 5.6 以后出現(xiàn)的GTID:
GTID概念：
1.GTID是一個(gè)由服務(wù)器的UUID和事務(wù)序號(hào)組成的唯一事務(wù)序號(hào)
     例如： UUID:N
         1122-3322-1122:1
         1122-3322-1122:2
2.GTID會(huì)被當(dāng)做唯每一個(gè)事務(wù)的首部，將會(huì)自動(dòng)生成并存到二進(jìn)制日志中
3.GTID可以用來(lái)追蹤主從之間的事務(wù)傳輸。
4.GTID主要應(yīng)用于HA的功能。在多主模型中，標(biāo)示某一個(gè)事務(wù)是來(lái)源于哪個(gè)特定的主服務(wù)器。
5.從服務(wù)器不會(huì)修改或者添加新的GTID，即便從服務(wù)器被配置為其他從服務(wù)器的主服務(wù)器，所以可以追蹤事務(wù)流。
6.GTID開啟后，會(huì)在gtid_executed予以顯示

跟復(fù)制相關(guān)的工具，需要python2.7環(huán)境
   1.mysqlreplicate: 添加從節(jié)點(diǎn)
   2.mysqlcheck：實(shí)現(xiàn)校驗(yàn)機(jī)制
   3.mysqlrplshow：發(fā)現(xiàn)并顯示，復(fù)制拓?fù)浣Y(jié)構(gòu)，幾級(jí)復(fù)制每一級(jí)有多少個(gè)服務(wù)器
   4.mysqlfailover: 講一個(gè)從節(jié)點(diǎn)提升為一個(gè)主節(jié)點(diǎn)
   5.mysqlrpladmin：管理工具，做手工調(diào)度的，把一個(gè)正常從節(jié)點(diǎn)調(diào)度為主節(jié)點(diǎn)

借助于GTID的多線程復(fù)制
  從服務(wù)器可以發(fā)出多個(gè)1/O線程對(duì)主服務(wù)器進(jìn)行對(duì)mysqldump請(qǐng)求會(huì)使得先后讀出來(lái)的信息順序混亂。因此通過分割數(shù)據(jù)庫(kù)來(lái)達(dá)到多線程，每一個(gè)數(shù)據(jù)庫(kù)的事務(wù)只能有一個(gè)線程復(fù)制。但即便如此如果只有一個(gè)主服務(wù)器，多線程并不能帶來(lái)性能提升，因?yàn)橹挥幸粋€(gè)二進(jìn)制服務(wù)器，并且網(wǎng)絡(luò)帶寬也有限。真正使性能提升，需要一從多主模型。多線程slave通常為多個(gè)sql，一個(gè)I/O線程，多個(gè)SQL線程。通過GTID機(jī)制，可以把不同的事務(wù)通過不同線程來(lái)應(yīng)用。

配置開啟GTID開啟復(fù)制的需需要的參數(shù)（配置在[mysqld]段下）
1.binlog-format：二進(jìn)制日志的格式，有row、statement和mixed幾種類型；
需要注意的是：當(dāng)設(shè)置隔離級(jí)別為READ-COMMITED必須設(shè)置二進(jìn)制日志格式為ROW，現(xiàn)在MySQL官方認(rèn)為STATEMENT這個(gè)已經(jīng)不再適合繼續(xù)使用；但mixed類型在默認(rèn)的事務(wù)隔離級(jí)別下，可能會(huì)導(dǎo)致主從數(shù)據(jù)不一致；
2.log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動(dòng)GTID及滿足附屬的其它需求；
    master-info-repository和relay-log-info-repository：?jiǎn)⒂么藘身?xiàng)，可用于實(shí)現(xiàn)在崩潰時(shí)保證二進(jìn)制及從服務(wù)器安全的功能；
3.sync-master-info：?jiǎn)⒂弥纱_保無(wú)信息丟失；
4.slave-paralles-workers：設(shè)定從服務(wù)器的SQL線程數(shù)；0表示關(guān)閉多線程復(fù)制功能；
5.binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：?jiǎn)⒂脧?fù)制有關(guān)的所有校驗(yàn)功能；
6.binlog-rows-query-log-events：?jiǎn)⒂弥捎糜谠诙M(jìn)制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度；
7.log-bin：?jiǎn)⒂枚M(jìn)制日志，這是保證復(fù)制功能的基本前提；
8.server-id：同一個(gè)復(fù)制拓?fù)渲械乃蟹?wù)器的id號(hào)必須惟一；
9.report-host：需要從服務(wù)器的主機(jī)名和IP地址在從服務(wù)器注冊(cè)的時(shí)候是否報(bào)告給主服務(wù)器。在主服務(wù)器上使用SHOW SLAVE HOSTS可以查看
   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:是否報(bào)告從服務(wù)器鏈接端口給主服務(wù)器
     The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
11.master-info-repository: 從服務(wù)是否把從服務(wù)器登錄和連接信息記錄在文件master.info或者記錄在mysql.slave_master_info表中
     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相關(guān)數(shù)據(jù)記錄文檔或者表
     This option causes the server to log its relay log info to a file or a table.
13.log_slave_updates：是否接受從服務(wù)器的更新信息
     Whether updates received by a slave server

建網(wǎng)站原本是網(wǎng)站策劃師、網(wǎng)絡(luò)程序員、網(wǎng)頁(yè)設(shè)計(jì)師等，應(yīng)用各種網(wǎng)絡(luò)程序開發(fā)技術(shù)和網(wǎng)頁(yè)設(shè)計(jì)技術(shù)配合操作的協(xié)同工作。成都創(chuàng)新互聯(lián)公司專業(yè)提供網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì),網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站制作(企業(yè)站、響應(yīng)式網(wǎng)站設(shè)計(jì)、電商門戶網(wǎng)站)等服務(wù),從網(wǎng)站深度策劃、搜索引擎友好度優(yōu)化到用戶體驗(yàn)的提升,我們力求做到極致!

使用mariadb 10 實(shí)現(xiàn)多線程復(fù)制

準(zhǔn)備環(huán)境:

1.系統(tǒng)環(huán)境：Centos6.5
2.數(shù)據(jù)庫(kù)版本：10.0.10-MariaDB-log MariaDB Server
3.Host:
Master主機(jī): master.samlee.com 172.16.100.7
Slave主機(jī): slave.samlee.com 172.16.100.8
----------------------------------------------------------------------------------------------------------

實(shí)現(xiàn)過程如下:

1.在Master安裝MariaDB及初始化數(shù)據(jù)庫(kù)服務(wù)，修改主機(jī)名，定義host文件:

--定義主機(jī)名 # vim /etc/sysconfig/network NETWORKING=yes HOSTNAME=master.samlee.com --定義host文件，實(shí)現(xiàn)主機(jī)名解析 # vim /etc/hosts 172.16.100.7 master.samlee.com master 172.16.100.8 slave.samlee.com slave --安裝MariaDB及初始化數(shù)據(jù)庫(kù)服務(wù) --規(guī)劃數(shù)據(jù)庫(kù)存儲(chǔ)目錄 # fdisk /dev/sda --新增一個(gè)分區(qū)大小為:20G 分區(qū)類型為: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 #在最后加入此行 /dev/myvg/mydata /mydata ext4 defaults,acl 0 0 # mount -a # mkdir /mydata/data # groupadd -r -g 3306 mysql #創(chuàng)建mysql服務(wù)用戶組 # useradd -r -g 3306 -d /mydata/data/ -s /sbin/nologin mysql #創(chuàng)建mysql服務(wù)用戶 # chown -R mysql.mysql /mydata/data/ # setfacl -m u:mysql:rwx /mydata/data/ --安裝MariaDB及配置初始化 # 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 修改/etc/mysql/my.cnf # vim /etc/mysql/my.cnf --在[mysqld]段加入以下選項(xiàng) datadir=/mydata/data # chown -R mysql.mysql /etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.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

2.在Slave安裝MariaDB及初始化數(shù)據(jù)庫(kù)服務(wù)，修改主機(jī)名，定義host文件:

--定義主機(jī)名 # vim /etc/sysconfig/network NETWORKING=yes HOSTNAME=slave.samlee.com --定義host文件，實(shí)現(xiàn)主機(jī)名解析 # vim /etc/hosts 172.16.100.7 master.samlee.com master 172.16.100.8 slave.samlee.com slave --安裝MariaDB及初始化數(shù)據(jù)庫(kù)服務(wù) --規(guī)劃數(shù)據(jù)庫(kù)存儲(chǔ)目錄 # fdisk /dev/sda --新增一個(gè)分區(qū)大小為:20G 分區(qū)類型為: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 #在最后加入此行 /dev/myvg/mydata /mydata ext4 defaults,acl 0 0 # mount -a # mkdir /mydata/data # groupadd -r -g 3306 mysql #創(chuàng)建mysql服務(wù)用戶組 # useradd -r -g 3306 -d /mydata/data/ -s /sbin/nologin mysql #創(chuàng)建mysql服務(wù)用戶 # chown mysql.mysql /mydata/data/ # setfacl -m u:mysql:rwx /mydata/data/ --安裝MariaDB及配置初始化 # 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 修改/etc/mysql/my.cnf # vim /etc/mysql/my.cnf --在[mysqld]段加入以下選項(xiàng) datadir=/mydata/data # chown -R mysql.mysql /etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.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.配置Master服務(wù)器為GTID主服務(wù)配置文件(主服務(wù)器上操作配置)

# vim /etc/mysql/my.cnf [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir=/mydata/data log-bin=/mydata/binlogs/master-bin binlog_format=row server-id = 1 log-slave-updates = True master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info = 1 slave-parallel-workers =2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1 binlog-rows-query-log-events = 1 report-port = 3306 report-host = master.samlee.com

4.測(cè)試Master-GTID服務(wù)是否配置成功:(主服務(wù)器上操作配置)

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)) | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------ --現(xiàn)在我們可以看到GTID已經(jīng)開始記錄了。

5.配置Slave服務(wù)器為GTID從服務(wù)配置文件(從服務(wù)器上操作配置)

[mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir=/mydata/data log-bin=/mydata/data/master-bin binlog_format=ROW server-id = 200 log-slave-updates=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-threads=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 report-port=3306 report-host=slave.samlee.com

6.在Master服務(wù)器上創(chuàng)建復(fù)制用戶(主服務(wù)器上操作配置)

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES; 注意:172.16.%.%是從節(jié)點(diǎn)服務(wù)器；如果想一次性授權(quán)更多的節(jié)點(diǎn)，可以自行根據(jù)需要修改；

7.為備節(jié)點(diǎn)提供初始數(shù)據(jù)集 (主服務(wù)器上操作配置)
鎖定主表，備份主節(jié)點(diǎn)上的數(shù)據(jù)，將其還原至從節(jié)點(diǎn)；如果沒有啟用GTID，在備份時(shí)需要在master上使用show master status命令查看二進(jìn)制日志文件名稱及事件位置，以便后面啟動(dòng)slave節(jié)點(diǎn)時(shí)使用。

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

8.為備節(jié)點(diǎn)恢復(fù)初始數(shù)據(jù)集，連接主節(jié)點(diǎn)服務(wù)器(從服務(wù)器上操作配置)

# mysql < /tmp/all.sql MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ --查詢主節(jié)點(diǎn)備份的二進(jìn)制日志名稱及事件位置 # head -n30 /tmp/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=379; --連接主節(jié)點(diǎn)服務(wù)器 MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=379; MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.100.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 379 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No --以上我們可以看到我們所指定的信息 -------------------------------------------------------------------------------------- --啟動(dòng)復(fù)制服務(wù) MariaDB [(none)]> START SLAVE; MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** 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: master-bin.000003 Read_Master_Log_Pos: 379 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 540 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ----------------------------------------------------------------------------------------- --查看SQL線程信息 MariaDB [(none)]> SHOW PROCESSLIST; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | 5 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 | | 6 | system user | | NULL | Connect | 214 | Waiting for master to send event | NULL | 0.000 | | 7 | system user | | NULL | Connect | 213 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+

9.驗(yàn)證GTIP復(fù)制狀況信息(主服務(wù)器與從服務(wù)器上操作配置)

10.查詢GTID狀態(tài)信息及應(yīng)用調(diào)試(從服務(wù)器上操作配置)

11.使用GTID連接主節(jié)點(diǎn)服務(wù)器實(shí)現(xiàn)主從復(fù)制(從服務(wù)器上操作配置)

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\G Using_Gtid: Current_Pos Gtid_IO_Pos: 0-1-39 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%gtid%'; +------------------------+------------------+ | Variable_name | Value | +------------------------+------------------+ | gtid_binlog_pos | 0-1-39 | | gtid_binlog_state | 0-200-128,0-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 | +------------------------+------------------+

使用mariadb 10 實(shí)現(xiàn)多源復(fù)制架構(gòu)

準(zhǔn)備環(huán)境:

1.系統(tǒng)環(huán)境：Centos6.5
2.數(shù)據(jù)庫(kù)版本：10.0.10-MariaDB-log MariaDB Server
3.Host:
Master1主機(jī): master.samlee.com 172.16.100.7

Master2主機(jī): master1.samlee.com 172.16.100.10

Slave主機(jī): slave.samlee.com 172.16.100.8

實(shí)現(xiàn)多分支機(jī)構(gòu)數(shù)據(jù)庫(kù)匯總架構(gòu)

----------------------------------------------------------------------
(1).Master1主機(jī)\Master2主機(jī)\Slave主機(jī)--定義hosts主機(jī)名解析文件，如下所示:

# vim /etc/hosts 172.16.100.7 master.samlee.com master 172.16.100.10 master2.samlee.com master2 172.16.100.8 slave.samlee.com slave

(2).Master1主機(jī)\Master2主機(jī)配置文件如下:

Master1主機(jī): # vim /etc/mysql/my.cnf server-id = 100 log-bin=mysql-bin Master2主機(jī): # vim /etc/mysql/my.cnf server-id = 200 log-bin=mysql-bin

(3)Slave主機(jī)配置文件如下:

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

(4)Master1主機(jī)\Master2主機(jī)上創(chuàng)建復(fù)制用戶(主服務(wù)器上操作配置)

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

(5)查詢Master1主機(jī)\Master2主機(jī)二進(jìn)制文件及事件位置

(6)在Slave主機(jī)上連接Master1、Master2主節(jié)點(diǎn)

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\G *************************** 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: Yes MariaDB [(none)]> SHOW SLAVE 'master' STATUS\G

測(cè)試如下:

經(jīng)測(cè)試顯示已經(jīng)完成多源復(fù)制架構(gòu)。

總結(jié)
1) 和mysql 5.6 相比，mariadb不支持的參數(shù)：
  gtid-mode=on
  enforce-gtid-consistency=true

2)修改的參數(shù)：
  slave-parallel-workers參數(shù)修改為slave-parallel-threads

3)連接至主服務(wù)使用的命令：
  一個(gè)新的參數(shù)：MASTER_USER_GTID={current_pos|slave_pos|no}
  這個(gè)參數(shù)在多主一從的試驗(yàn)中，總是不成功

4)才配置從服務(wù)器的時(shí)候，最好使用replicate_ignore_db 來(lái)忽略掉一些系統(tǒng)庫(kù)。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

網(wǎng)站題目：關(guān)系型數(shù)據(jù)庫(kù)之MariDB10.0.10多主一從的架構(gòu)及多線程復(fù)制架構(gòu)-創(chuàng)新互聯(lián)
當(dāng)前網(wǎng)址：http://bm7419.com/article44/iigee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、搜索引擎優(yōu)化、靜態(tài)網(wǎng)站、網(wǎng)站策劃、網(wǎng)站維護(hù)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容