时间：2021-07-01 10:21:17 帮助过：9人阅读
使用mariadb 10 实现多线程复制
准备环境:
1.系统环境：Centos6.5
2.数据库版本：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
----------------------------------------------------------------------------------------------------------
实现过程如下:
1.在Master安装MariaDB及初始化数据库服务，修改主机名，定义host文件:
--定义主机名
# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=master.samlee.com
--定义host文件，实现主机名解析
# vim /etc/hosts
172.16.100.7 master.samlee.com master
172.16.100.8 slave.samlee.com slave
--安装MariaDB及初始化数据库服务
--规划数据库存储目录
# fdisk /dev/sda --新增一个分区大小为:20G 分区类型为: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 #创建mysql服务用户组
# useradd -r -g 3306 -d /mydata/data/ -s /sbin/nologin mysql #创建mysql服务用户
# 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及初始化数据库服务，修改主机名，定义host文件:
--定义主机名
# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=slave.samlee.com
--定义host文件，实现主机名解析
# vim /etc/hosts
172.16.100.7 master.samlee.com master
172.16.100.8 slave.samlee.com slave
--安装MariaDB及初始化数据库服务
--规划数据库存储目录
# fdisk /dev/sda --新增一个分区大小为:20G 分区类型为: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 #创建mysql服务用户组
# useradd -r -g 3306 -d /mydata/data/ -s /sbin/nologin mysql #创建mysql服务用户
# 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服务器为GTID主服务配置文件(主服务器上操作配置)
# 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服务是否配置成功:(主服务器上操作配置)
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)) | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------ --现在我们可以看到GTID已经开始记录了。
5.配置Slave服务器为GTID从服务配置文件(从服务器上操作配置)
[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服务器上创建复制用户(主服务器上操作配置)
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘172.16.%.%‘ IDENTIFIED BY ‘replpass‘; MariaDB [(none)]> FLUSH PRIVILEGES; 注意:172.16.%.%是从节点服务器；如果想一次性授权更多的节点，可以自行根据需要修改；
7.为备节点提供初始数据集 (主服务器上操作配置)
锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。
# mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > all.sql # scp all.sql 172.16.100.8:/tmp/
8.为备节点恢复初始数据集，连接主节点服务器(从服务器上操作配置)
# mysql < /tmp/all.sql MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ --查询主节点备份的二进制日志名称及事件位置 # head -n30 /tmp/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE=‘master-bin.000003‘, MASTER_LOG_POS=379; --连接主节点服务器 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 --以上我们可以看到我们所指定的信息 -------------------------------------------------------------------------------------- --启动复制服务 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复制状况信息(主服务器与从服务器上操作配置)
--查询连接上从节点服务器数 MariaDB [(none)]> SHOW SLAVE HOSTS; +-----------+------------------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------------------+------+-----------+ | 200 | slave.samlee.com | 3306 | 1 | +-----------+------------------+------+-----------+ --怎么验证从服务器启动多个线程呢？ (1)进行大批量写入操作 # mysql < hellodb.sql (2)执行(1)操作后马上在Slave服务器进行监控 # watch -n .5 "mysql -e ‘show processlist\G‘"
10.查询GTID状态信息及应用调试(从服务器上操作配置)
MariaDB [(none)]> SHOW SLAVE STATUS\G Using_Gtid: No --查询并行复制机制是否启动 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 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ --出现以上内容，我们已经实现了多线程复制功能了
11.使用GTID连接主节点服务器实现主从复制(从服务器上操作配置)
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 实现多源复制架构
准备环境:
1.系统环境：Centos6.5
2.数据库版本：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
实现多分支机构数据库汇总架构
----------------------------------------------------------------------
(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主机上创建复制用户(主服务器上操作配置)
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主节点
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 | +--------------------+
经测试显示已经完成多源复制架构。
总结
1) 和mysql 5.6 相比，mariadb不支持的参数：
gtid-mode=on
enforce-gtid-consistency=true
2)修改的参数：
slave-parallel-workers参数修改为slave-parallel-threads
3)连接至主服务使用的命令：
一个新的参数：MASTER_USER_GTID={current_pos|slave_pos|no}
这个参数在多主一从的试验中，总是不成功
4)才配置从服务器的时候，最好使用replicate_ignore_db 来忽略掉一些系统库。
本文出自 “Opensamlee” 博客，请务必保留此出处http://gzsamlee.blog.51cto.com/9976612/1792686
关系型数据库之MariDB 10.0.10多主一从的架构及多线程复制架构
标签：服务器 管理工具 数据库 二进制 标示