一、MySQL 5.6 以后出现的GTID:
GTID概念：
1.GTID是一个由服务器的UUID和事务序号组成的唯一事务序号
例如： UUID:N
1122-3322-1122:1
1122-3322-1122:2
2.GTID会被当做唯每一个事务的首部，将会自动生成并存到二进制日志中
3.GTID可以用来追踪主从之间的事务传输。
4.GTID主要应用于HA的功能。在多主模型中，标示某一个事务是来源于哪个特定的主服务器。
5.从服务器不会修改或者添加新的GTID，即便从服务器被配置为其他从服务器的主服务器，所以可以追踪事务流。
6.GTID开启后，会在gtid_executed予以显示
跟复制相关的工具，需要python2.7环境
1.mysqlreplicate: 添加从节点
2.mysqlcheck： 实现校验机制
3.mysqlrplshow： 发现并显示，复制拓扑结构， 几级复制每一级有多少个服务器
4.mysqlfailover: 讲一个从节点提升为一个主节点
5.mysqlrpladmin： 管理工具，做手工调度的，把一个正常从节点调度为主节点
借助于GTID的多线程复制
从服务器可以发出多个1/O线程对主服务器进行对mysqldump请求会使得先后读出来的信息顺序混乱。因此通过分割数据库来达到多线程，每一个数据库的事务只能有一个线程复制。 但即便如此如果只有一个主服务器，多线程并不能带来性能提升， 因为只有一个二进制服务器，并且网络带宽也有限。真正使性能提升，需要一从多主模型。多线程slave通常为多个sql，一个I/O线程，多个SQL线程。 通过GTID机制，可以把不同的事务通过不同线程来应用。
配置开启GTID开启复制的需需要的参数（配置在[mysqld]段下）
1.binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
2.log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；
master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
3.sync-master-info：启用之可确保无信息丢失；
4.slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
5.binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
6.binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
7.log-bin：启用二进制日志，这是保证复制功能的基本前提；
8.server-id：同一个复制拓扑中的所有服务器的id号必须惟一；
9.report-host： 需要从服务器的主机名和IP地址在从服务器注册的时候是否报告给主服务器。 在主服务器上使用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:是否报告从服务器链接端口给主服务器
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
11.master-info-repository: 从服务是否把从服务器登录和连接信息记录在文件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相关数据记录文档或者表
This option causes the server to log its relay log info to a file or a table.
13.log_slave_updates：是否接受从服务器的更新信息
Whether updates received by a slave server
使用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.com4.测试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.com6.在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节点时使用。
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主机名解析文件，如下所示:
(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 来忽略掉一些系统库。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
重庆分公司
重庆分公司
