一、MySQL 5.6 以后出現(xiàn)的GTID:
GTID概念：
1.GTID是一個由服務(wù)器的UUID和事務(wù)序號組成的唯一事務(wù)序號
例如： UUID:N
1122-3322-1122:1
1122-3322-1122:2
2.GTID會被當做唯每一個事務(wù)的首部，將會自動生成并存到二進制日志中
3.GTID可以用來追蹤主從之間的事務(wù)傳輸。
4.GTID主要應(yīng)用于HA的功能。在多主模型中，標示某一個事務(wù)是來源于哪個特定的主服務(wù)器。
5.從服務(wù)器不會修改或者添加新的GTID，即便從服務(wù)器被配置為其他從服務(wù)器的主服務(wù)器，所以可以追蹤事務(wù)流。
6.GTID開啟后，會在gtid_executed予以顯示
跟復(fù)制相關(guān)的工具，需要python2.7環(huán)境
1.mysqlreplicate: 添加從節(jié)點
2.mysqlcheck： 實現(xiàn)校驗機制
3.mysqlrplshow： 發(fā)現(xiàn)并顯示，復(fù)制拓撲結(jié)構(gòu)， 幾級復(fù)制每一級有多少個服務(wù)器
4.mysqlfailover: 講一個從節(jié)點提升為一個主節(jié)點
5.mysqlrpladmin： 管理工具，做手工調(diào)度的，把一個正常從節(jié)點調(diào)度為主節(jié)點
借助于GTID的多線程復(fù)制
從服務(wù)器可以發(fā)出多個1/O線程對主服務(wù)器進行對mysqldump請求會使得先后讀出來的信息順序混亂。因此通過分割數(shù)據(jù)庫來達到多線程，每一個數(shù)據(jù)庫的事務(wù)只能有一個線程復(fù)制。 但即便如此如果只有一個主服務(wù)器，多線程并不能帶來性能提升， 因為只有一個二進制服務(wù)器，并且網(wǎng)絡(luò)帶寬也有限。真正使性能提升，需要一從多主模型。多線程slave通常為多個sql，一個I/O線程，多個SQL線程。 通過GTID機制，可以把不同的事務(wù)通過不同線程來應(yīng)用。
配置開啟GTID開啟復(fù)制的需需要的參數(shù)（配置在[mysqld]段下）
1.binlog-format：二進制日志的格式，有row、statement和mixed幾種類型；
需要注意的是：當設(shè)置隔離級別為READ-COMMITED必須設(shè)置二進制日志格式為ROW，現(xiàn)在MySQL官方認為STATEMENT這個已經(jīng)不再適合繼續(xù)使用；但mixed類型在默認的事務(wù)隔離級別下，可能會導(dǎo)致主從數(shù)據(jù)不一致；
2.log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動GTID及滿足附屬的其它需求；
master-info-repository和relay-log-info-repository：啟用此兩項，可用于實現(xiàn)在崩潰時保證二進制及從服務(wù)器安全的功能；
3.sync-master-info：啟用之可確保無信息丟失；
4.slave-paralles-workers：設(shè)定從服務(wù)器的SQL線程數(shù)；0表示關(guān)閉多線程復(fù)制功能；
5.binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：啟用復(fù)制有關(guān)的所有校驗功能；
6.binlog-rows-query-log-events：啟用之可用于在二進制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度；
7.log-bin：啟用二進制日志，這是保證復(fù)制功能的基本前提；
8.server-id：同一個復(fù)制拓撲中的所有服務(wù)器的id號必須惟一；
9.report-host： 需要從服務(wù)器的主機名和IP地址在從服務(wù)器注冊的時候是否報告給主服務(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:是否報告從服務(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ù)有：成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認證、湯陰ssl等。為數(shù)千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的湯陰網(wǎng)站制作公司
使用mariadb 10 實現(xiàn)多線程復(fù)制
準備環(huán)境:
1.系統(tǒng)環(huán)境：Centos6.5
2.數(shù)據(jù)庫版本：10.0.10-MariaDB-log MariaDB Server
3.Host:
Master主機: master.samlee.com 172.16.100.7
Slave主機: slave.samlee.com 172.16.100.8
----------------------------------------------------------------------------------------------------------
實現(xiàn)過程如下:
1.在Master安裝MariaDB及初始化數(shù)據(jù)庫服務(wù)，修改主機名，定義host文件:
重慶分公司
重慶分公司
--定義主機名
# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=master.samlee.com
--定義host文件，實現(xiàn)主機名解析
# vim /etc/hosts
172.16.100.7 master.samlee.com master
172.16.100.8 slave.samlee.com slave
--安裝MariaDB及初始化數(shù)據(jù)庫服務(wù)
--規(guī)劃數(shù)據(jù)庫存儲目錄
# fdisk /dev/sda --新增一個分區(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]段加入以下選項
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 start2.在Slave安裝MariaDB及初始化數(shù)據(jù)庫服務(wù)，修改主機名，定義host文件:
--定義主機名
# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=slave.samlee.com
--定義host文件，實現(xiàn)主機名解析
# vim /etc/hosts
172.16.100.7 master.samlee.com master
172.16.100.8 slave.samlee.com slave
--安裝MariaDB及初始化數(shù)據(jù)庫服務(wù)
--規(guī)劃數(shù)據(jù)庫存儲目錄
# fdisk /dev/sda --新增一個分區(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]段加入以下選項
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 start3.配置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.測試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é)點服務(wù)器；如果想一次性授權(quán)更多的節(jié)點，可以自行根據(jù)需要修改；
7.為備節(jié)點提供初始數(shù)據(jù)集 (主服務(wù)器上操作配置)
鎖定主表，備份主節(jié)點上的數(shù)據(jù)，將其還原至從節(jié)點；如果沒有啟用GTID，在備份時需要在master上使用show master status命令查看二進制日志文件名稱及事件位置，以便后面啟動slave節(jié)點時使用。
# mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > all.sql # scp all.sql 172.16.100.8:/tmp/
8.為備節(jié)點恢復(fù)初始數(shù)據(jù)集，連接主節(jié)點服務(wù)器(從服務(wù)器上操作配置)
# mysql < /tmp/all.sql MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ --查詢主節(jié)點備份的二進制日志名稱及事件位置 # head -n30 /tmp/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=379; --連接主節(jié)點服務(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 --以上我們可以看到我們所指定的信息 -------------------------------------------------------------------------------------- --啟動復(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.驗證GTIP復(fù)制狀況信息(主服務(wù)器與從服務(wù)器上操作配置)
--查詢連接上從節(jié)點服務(wù)器數(shù) MariaDB [(none)]> SHOW SLAVE HOSTS; +-----------+------------------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------------------+------+-----------+ | 200 | slave.samlee.com | 3306 | 1 | +-----------+------------------+------+-----------+ --怎么驗證從服務(wù)器啟動多個線程呢？ (1)進行大批量寫入操作 # mysql < hellodb.sql (2)執(zhí)行(1)操作后馬上在Slave服務(wù)器進行監(jiān)控 # watch -n .5 "mysql -e 'show processlist\G'"
10.查詢GTID狀態(tài)信息及應(yīng)用調(diào)試(從服務(wù)器上操作配置)
MariaDB [(none)]> SHOW SLAVE STATUS\G Using_Gtid: No --查詢并行復(fù)制機制是否啟動 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%parallel%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | slave_domain_parallel_threads | 0 | | slave_parallel_max_queued | 131072 | | slave_parallel_threads | 2 | +-------------------------------+--------+ --查詢sql線程 MariaDB [(none)]> SHOW PROCESSLIST; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | 3 | system user | | NULL | Connect | 174 | Waiting for work from SQL thread | NULL | 0.000 | | 4 | system user | | NULL | Connect | 174 | Waiting for work from SQL thread | NULL | 0.000 | | 6 | system user | | NULL | Connect | 173 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | | 7 | system user | | NULL | Connect | 173 | Waiting for master to send event | NULL | 0.000 | | 28 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ --出現(xiàn)以上內(nèi)容，我們已經(jīng)實現(xiàn)了多線程復(fù)制功能了
11.使用GTID連接主節(jié)點服務(wù)器實現(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 實現(xiàn)多源復(fù)制架構(gòu)
準備環(huán)境:
1.系統(tǒng)環(huán)境：Centos6.5
2.數(shù)據(jù)庫版本：10.0.10-MariaDB-log MariaDB Server
3.Host:
Master1主機: master.samlee.com 172.16.100.7
Master2主機: master1.samlee.com 172.16.100.10
Slave主機: slave.samlee.com 172.16.100.8
實現(xiàn)多分支機構(gòu)數(shù)據(jù)庫匯總架構(gòu)
----------------------------------------------------------------------
(1).Master1主機\Master2主機\Slave主機--定義hosts主機名解析文件，如下所示:
# 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主機\Master2主機配置文件如下:
Master1主機: # vim /etc/mysql/my.cnf server-id = 100 log-bin=mysql-bin Master2主機: # vim /etc/mysql/my.cnf server-id = 200 log-bin=mysql-bin
(3)Slave主機配置文件如下:
# vim /etc/mysql/my.cnf server-id = 300 relay-log=relay-bin
(4)Master1主機\Master2主機上創(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主機\Master2主機二進制文件及事件位置
Master1主機: MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 867 | | | +------------------+----------+--------------+------------------+ Master2主機: MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 867 | | | +------------------+----------+--------------+------------------+
(6)在Slave主機上連接Master1、Master2主節(jié)點
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
測試如下:
master操作: MariaDB [(none)]> CREATE DATABASE masterdb; master1操作: MariaDB [(none)]> CREATE DATABASE master1db; slave操作: MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | master1db | | masterdb | | mysql | | performance_schema | | test | +--------------------+
經(jīng)測試顯示已經(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ù)使用的命令：
一個新的參數(shù)：MASTER_USER_GTID={current_pos|slave_pos|no}
這個參數(shù)在多主一從的試驗中，總是不成功
4)才配置從服務(wù)器的時候，最好使用replicate_ignore_db 來忽略掉一些系統(tǒng)庫。
