您的当前位置：首页正文

mysql 5.6 gtid复制

2023-11-10 来源：画鸵萌宠网

grant replication slave on *.* to repl@‘ip‘ identified by ‘xxx‘;

flush privileges;

change master to master_host=‘192.168.1.103‘,master_port=3306,master_user=‘repl‘,master_password=‘ffffff‘,master_auto_position=1;

start slave;

show slave statusG

my.cnf文件

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[client]

port = 3306

socket = /tmp/mysqld1.sock

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

basedir = /usr/local/mysql1

datadir = /data/mysql1

port = 3306

socket = /tmp/mysqld1.sock

server-id = 1

#binlog

log-bin = /data/mysql1/logs/mysql1-bin

binlog_format = row

binlog-row-image = minimal

binlog_cache_size = 512K

max_binlog_size = 100M

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=true

slave_parallel_workers = 2

sync_binlog = 1

#relay log

relay-log = /data/mysql1/logs/relay1-log

max_relay_log_size = 100M

#check binlog

binlog_checksum = CRC32

slave_allow_batching = 1

master_verify_checksum = 1

slave_sql_verify_checksum = 1

binlog_rows_query_log_events = 1

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

auto-increment-increment=10

auto-increment-offset=1

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

mysql 5.6 gtid复制

标签：mysql gtid

小编还为您整理了以下内容，可能对您也有帮助：

与MySQL传统复制相比，GTID有哪些独特的复制姿势

前言

GTID(Global Transaction ID)是MySQL5.6引入的功能，可以在集群全局范围标识事务，用于取代过去通过binlog文件偏移量定位复制位置的传统方式。借助GTID，在发生主备切换的情况下，MySQL的其它Slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID虽好，要想运用自如还需充分了解其原理与特性，特别要注意与传统的基于binlog文件偏移量复制方式不一样的地方。本文概述了关于GTID的几个常见问题，希望能对理解和使用基于GTID的复制有所帮助。

GTID长什么样

根据官方文档定义，GTID由source_id加transaction_id构成。

GTID = source_id:transaction_id

上面的source_id指示发起事务的MySQL实例，值为该实例的server_uuid。server_uuid由MySQL在第一次启动时自动生成并被持久化到auto.cnf文件里，transaction_id是MySQL实例上执行的事务序号，从1开始递增。例如：

e6954592-8dba-11e6-af0e-fa163e1cf111:1

一组连续的事务可以用‘-‘连接的事务序号范围表示。例如

e6954592-8dba-11e6-af0e-fa163e1cf111:1-5

更一般的情况是GTID的集合。GTID集合可以包含来自多个source_id的事务，它们之间用逗号分隔；如果来自同一source_id的事务序号有多个范围区间，各组范围之间用冒号分隔，例如：

e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,

e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27

即，GTID集合拥有如下的形式定义：

gtid_set:

uuid_set [, uuid_set] ...

| ‘‘

uuid_set:

uuid:interval[:interval]...

uuid:

hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh

[0-9|A-F]

interval:

n[-n]

(n >= 1)

如何查看GTID

可以通过MySQL的几个变量查看相关的GTID信息。

gtid_executed

在当前实例上执行过的GTID集合; 实际上包含了所有记录到binlog中的事务。所以，设置set sql_log_bin=0后执行的事务不会生成binlog 事件，也不会被记录到gtid_executed中。执行RESET MASTER可以将该变量置空。

gtid_purged

binlog不可能永远驻留在服务上，需要定期进行清理(通过expire_logs_days可以控制定期清理间隔)，否则迟早它会把磁盘用尽。gtid_purged用于记录已经被清除了的binlog事务集合，它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制，要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空，即始终保持gtid_purged是gtid_executed的子集。

gtid_next

会话级变量，指示如何产生下一个GTID。可能的取值如下:

1）AUTOMATIC：

自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID。

2）ANONYMOUS：

设置后执行事务不会产生GTID。

3）显式指定的GTID：

可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则，下次执行事务时会报错。

这些变量可以通过show命令查看，比如：

如何产生GTID

GTID的生成受gtid_next控制。在Master上，gtid_next是默认的AUTOMATIC,即在每次事务提交时自动生成新的GTID。它从当前已执行的GTID集合（即gtid_executed）中，找一个大于0的未使用的最小值作为下个事务GTID。同时在binlog的实际的更新事务事件前面插入一条set gtid_next事件。

以下是一条insert语句生成的binlog记录：

在Slave上回放主库的binlog时，先执行set gtid_next ...，然后再执行真正的insert语句，确保在主和备上这条insert对应于相同的GTID。

一般情况下，GTID集合是连续的，但使用多线程复制(MTS)以及通过gtid_next进行人工干预时会导致gtid空洞。比如下面这样：

继续执行事务，MySQL会分配一个最小的未使用GTID，也就是从出现空洞的地方分配GTID，最终会把空洞填上。

这意味着严格来说我们即不能假设GTID集合是连续的，也不能假定GTID序号大的事务在GTID序号小的事务之后执行，事务的顺序应由事务记录在binlog中的先后顺序决定。

GTID的持久化

GTID相关的信息存储在binlog文件中，为此MySQL5.6新增了下面2个binlog事件。

Previous_gtids_log_event在每个binlog文件的开头部分，记录在该binlog文件之前已执行的GTID集合。

Gtid_log_event即前面看到的set gtid_next ...,它出现在每个事务的前面，表明下一个事务的gtid。

示例如下：

MySQL服务器启动时，通过读binlog文件，初始化gtid_executed和gtid_purged,使它们的值能和上次MySQL运行时一致。

gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。

gtid_purged为最老的binlog文件中Previous_gtids_log_event。

由于这两个重要的变量值记录在binlog中，所以开启gtid_mode时必须同时在主库上开启log_bin在备库上开启log_slave_updates。

但是，在MySQL5.7中没有这个。MySQL5.7中，新增加一个系统表mysql.gtid_executed用于持久化已执行的GTID集合。当主库上没有开启log_bin或在备库上没有开启log_slave_updates时，mysql.gtid_executed会跟用户事务一起每次更新。否则只在binlog日志发生rotation时更新mysql.gtid_executed。

如何配置基于GTID的复制

MySQL服务器的my.cnf配置文件中增加GTID相关的参数

log_bin= /mysql/binlog/mysql_bin

log_slave_updates= true

gtid_mode= ON

enforce_gtid_consistency= true

relay_log_info_repository = TABLE

relay_log_recovery= ON

然后在Slave上指定MASTER_AUTO_POSITION = 1执行CHANGE MASTER TO即可。比如：

CHANGE MASTER TO MASTER_HOST=‘node1‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘repl‘,MASTER_AUTO_POSITION=1;

基于GTID的复制如何工作

在MASTER_AUTO_POSITION = 1的情况下，MySQL会使用COM_BINLOG_DUMP_GTID协议进行复制。过程如下：

备库发起复制连接时，将自己的已接受和已执行的gtids的并集(后面称为slave_gtid_executed)发送给主库。即下面的集合：

UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)

主库将自己的gtid_executed与slave_gtid_executed的差集的binlog发送给Slave。主库的binlog mp过程如下：

检查slave_gtid_executed是否是主库gtid_executed的子集，如否那么主备数据可能不一致，报错。

检查主库的purged_executed是否是slave_gtid_executed的子集，如否代表缺失备库需要的binlog,报错

从最后一个Binlog开始扫描，获取文件头部的PREVIOUS_GTIDS_LOG_EVENT，如果它是slave_gtid_executed的子集，则这是需要发送给Slave的第一个binlog文件，否则继续向前扫描。

从第3步找到的binlog文件的开头读取binlog记录，判断binlog记录是否已被包含在slave_gtid_executed中，如果已包含跳过不发送。

从上面的过程可知，在指定MASTER_AUTO_POSITION = 1时，Master发送哪些binlog记录给Slave，取决于Slave的gtid_executed和Retrieved_Gtid_Set以及Master的gtid_executed，和relay_log_info以及master_log_info中保存的复制位点没有关系。

如何修复复制错误

在基于GTID的复制拓扑中，要想修复Slave的SQL线程错误，过去的SQL_SLAVE_SKIP_COUNTER方式不再适用。需要通过设置gtid_next或gtid_purged完成，当然前提是已经确保主从数据一致，仅仅需要跳过复制错误让复制继续下去。比如下面的场景：

在从库上创建表tb1

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> create table tb1(id int primary key,c1 int);

Query OK, 0 rows affected (1.06 sec)

mysql> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

在主库上创建表tb1：

mysql> create table tb1(id int primary key,c1 int);

Query OK, 0 rows affected (1.06 sec)

由于从库上这个表已经存在，从库的复制SQL线程出错停止。

上面的输出可以知道，从库已经执行过的事务是‘e10c75be-5c1b-11e6-ab7c-000c296078ae:1-5‘,执行出错的事务是‘e10c75be-5c1b-11e6-ab7c-000c296078ae:6‘，当前主备的数据其实是一致的，可以通过设置gtid_next跳过这个出错的事务。

在从库上执行以下SQL：

mysql> set gtid_next=‘e10c75be-5c1b-11e6-ab7c-000c296078ae:6‘;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next=‘AUTOMATIC‘;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

设置gtid_next的方法一次只能跳过一个事务，要批量的跳过事务可以通过设置gtid_purged完成。假设下面的场景：

主库上已执行的事务

从库上已执行的事务

假设经过修复从库已经和主库的数据一致了，但由于复制错误Slave的SQL线程依然处于停止状态。现在可以通过把从库的gtid_purged设置为和主库的gtid_executed一样跳过不一致的GTID使复制继续下去，步骤如下。

在从库上执行

此时从库的Executed_Gtid_Set已经包含了主库上‘1-10‘的事务，再开启复制会从后面的事务开始执行，就不会出错了。

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

使用gtid_next和gtid_purged修复复制错误的前提是，跳过那些事务后仍可以确保主备数据一致。如果做不到，就要考虑pt-table-sync或者拉备份的方式了。

GTID与备份恢复

在做备份恢复的时候，有时需要恢复出来的MySQL实例可以作为Slave连上原来的主库继续复制，这就要求从备份恢复出来的MySQL实例拥有和数据一致的gtid_executed值。这也是通过设置gtid_purged实现的，下面看下mysqlmp做备份的例子。

1、通过mysqlmp进行备份

通过mysqlmp做一个全量备份：

[root@node1 ~]# mysqlmp --all-databases --single-transaction --routines --events --host=127.0.0.1 --port=3306 --user=root > mp.sql

生成的mp.sql文件里包含了设置gtid_purged的语句

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

...

SET @@GLOBAL.GTID_PURGED=‘e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10‘;

...

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

恢复数据前需要先通过reset master清空gtid_executed变量

[root@node2 ~]# mysql -h127.1 -e ‘reset master‘

[root@node2 ~]# mysql -h127.1 <mp.sql

否则执行设置GTID_PURGED的SQL时会报下面的错误

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

此时恢复出的MySQL实例的GTID_EXECUTED和备份时点一致：

由于恢复出的MySQL实例已经被设置了正确的GTID_EXECUTED，以master_auto_postion = 1的方式CHANGE MASTER到原来的主节点即可开始复制。

CHANGE MASTER TO MASTER_HOST=‘node1‘, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘repl‘, MASTER_AUTO_POSITION = 1

如果不希望备份文件中生成设置GTID_PURGED的SQL，可以给mysqlmp传入--set-gtid-purged=OFF关闭。

2、通过Xtrabackup进行备份

相比mysqlmp，Xtrabackup是效率更高并且被广泛使用的备份方式。使用Xtrabackup进行备份的举例如下。

通过Xtrabackup创一个全量备份（可以在Slave上创建备份，以避免对主库的性能冲击）

innobackupex --defaults-file=/etc/my.cnf --host=127.1 --user=root --password=mysql --no-timestamp --safe-slave-backup --slave-info /mysql/bak

应用日志

innobackupex --apply-log /mysql/bak

查看备份目录中的xtrabackup_binlog_info文件可以找到备份时已经执行过的gtids

[root@node2 ~]# cat /mysql/bak/xtrabackup_binlog_info

mysql_bin.000001191e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10

由于备份时添加了”--slave-info”选项并且从Slave节点拉取的备份，所以会生成xtrabackup_slave_info文件，也可以从这个文件里查找建立复制的SQL语句。

[root@node2 ~]# cat /mysql/bak/xtrabackup_slave_info

SET GLOBAL gtid_purged=‘e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10‘;

CHANGE MASTER TO MASTER_AUTO_POSITION=1

将备份文件传送到新的节点node3的/mysql/bak目录并恢复(如果直接把备份传输到数据目录了，这一步可以省略)。

[root@node3 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql/bak

启动MySQL。

[root@node3 ~]# mysqld --defaults-file=/home/mysql/etc/my.cnf --skip-slave-start &

如果是从Slave拉的备份，一定不能直接开启Slave复制，这时的gtid_executed是错误的。需要手动设置gtid_purged后再start slave

MASTER_HOST=‘node1‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘repl‘,MASTER_AUTO_POSITION=1;

start slave;

GTID与MHA

MHA是被广泛使用MySQL HA组件，MHA 0.56以后支持基于GTID的复制。 MHA在failover时会自动判断是否是GTID based failover，需要满足下面3个条件即为GTID based failover

所有节点gtid_mode=1

所有节点Executed_Gtid_Set不为空

至少一个节点Auto_Position=1

和之前的基于binlog文件位置的复制相比，基于GTID复制下，MHA在故障切换时的变化主要如下：

基于binlog文件位置的复制

在Master宕机后会尝试从Master上拷贝binlog日志进行补偿

如果候选Master不拥有最新的relay log，会从拥有最新relay log的Slave上生成差异的binlog传送到候选Master并实施补偿

新Master的日志补偿完成后，同样采用应用差异binlog的方式将其它Slave和新Master同步后再change master到新Master

基于GTID的复制

如果候选Master不拥有最新的relay log，让候选Master连上拥有最新relay log的Salve进行补偿。

尝试从binlog server上拉取缺失的binlog并应用

新Master的数据同步到最新后，让其它的Slave连上新Master并等待数据完成同步。并且可以给masterha_master_switch传入--wait_until_gtid_in_sync=1参数使其不等其它Slave完成数据同步，以加快切换速度。

GTID模式下MHA不会尝试从旧Master上拷贝binlog日志进行补偿，所以在MySQL进程crash而OS仍然健康的情况下，应尽量不要做主备切换而是原地重启MySQL，除非有其

如何复制mysql数据库到另一台电脑上？

有两种办法。

1、在B机器上装mysql。

将A机器上的mysql/data下的你的数据库目录整个拷贝下来。

将B机器上的mysql服务停止。

找到B机器上的mysql/data目录，将你拷贝的目录粘贴进去，然后启动mysql服务就可以了。

2、使用SQL语句备份和恢复

你可以使用SELECT INTO OUTFILE语句备份数据，并用LOAD DATA INFILE语句恢复数据。这种方法只能导出数据的内容，不包括表的结构，如果表的结构文件损坏，你必须要先恢复原来的表的结构。

语法：

SELECT * INTO {OUTFILE ¦ DUMPFILE} ’file_name’ FROM tbl_name

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE ’file_name.txt’ [REPLACE ¦ IGNORE]

INTO TABLE tbl_name

SELECT ... INTO OUTFILE ’file_name’

在dos命令提示符下使用mysqlmp命令进行备份.

如下:

C:\Documents and Settings\Administrator>mysqlmp yinshi >c:\\backup.txt -uroot

-p12142022

mysql数据库可以直接复制吗

如果从库上表 t 数据与主库不一致，导致复制错误，整个库的数据量很大，重做从库很慢，如何单独恢复这张表的数据？通常认为是不能修复单表数据的，因为涉及到各表状态不一致的问题。下面就列举备份单表恢复到从库会面临的问题以及解决办法：

场景 1

如果复制报错后，没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新，从库数据停滞在报错状态（假设 GTID 为 aaaa:1-100）。

修复步骤：

在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000）；

恢复到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:101，从库上表 t 的数据状态是领先其他表的。aaaa:101-10000 这些事务中只要有修改表 t 数据的事务，就会导致复制报错，比如主键冲突、记录不存在（而 aaaa:101 这个之前复制报错的事务必定是修改表 t 的事务）

解决办法：启动复制时跳过 aaaa:101-10000 这些事务中修改表 t 的事务。

正确的修复步骤：

1. 在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000），恢复到从库；

2. 设置复制过滤，过滤表 t：

3. 启动复制，回放到 aaaa:10000 时停止复制（此时从库上所有表的数据都在同一状态，是一致的）;

4. 删除复制过滤，正常启动复制。

注意事项：这里要用 mysqlmp --single-transaction --master-data=2，记录备份快照对应的 GTID

场景 2

如果复制报错后，使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。

修复步骤：

在主库上备份表 t （假设备份快照 GTID为 aaaa:1-10000）；

停止从库复制，GTID为 aaaa:1-20000；

恢复表 t 到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:20001，aaaa:10000-20000 这些事务将不会在从库上回放，如果这里面有修改表 t 数据的事务，从库上将丢失这部分数据。

解决办法：从备份开始到启动复制，锁定表 t，保证 aaaa:10000-20000 中没有修改表 t 的事务。

正确修复步骤：

对表 t 加读锁；

在主库上备份表 t；

停止从库复制，恢复表 t；

启动复制；

解锁表 t。

如果是大表，这里可以用可传输表空间方式备份、恢复表，减少锁表时间。

mysql数据库可以直接复制吗

场景 1

如果复制报错后，没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新，从库数据停滞在报错状态（假设 GTID 为 aaaa:1-100）。

修复步骤：

在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000）；

恢复到从库；

启动复制。

解决办法：启动复制时跳过 aaaa:101-10000 这些事务中修改表 t 的事务。

正确的修复步骤：

1. 在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000），恢复到从库；

2. 设置复制过滤，过滤表 t：

3. 启动复制，回放到 aaaa:10000 时停止复制（此时从库上所有表的数据都在同一状态，是一致的）;

4. 删除复制过滤，正常启动复制。

注意事项：这里要用 mysqlmp --single-transaction --master-data=2，记录备份快照对应的 GTID

场景 2

如果复制报错后，使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。

修复步骤：

在主库上备份表 t （假设备份快照 GTID为 aaaa:1-10000）；

停止从库复制，GTID为 aaaa:1-20000；

恢复表 t 到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:20001，aaaa:10000-20000 这些事务将不会在从库上回放，如果这里面有修改表 t 数据的事务，从库上将丢失这部分数据。

解决办法：从备份开始到启动复制，锁定表 t，保证 aaaa:10000-20000 中没有修改表 t 的事务。

正确修复步骤：

对表 t 加读锁；

在主库上备份表 t；

停止从库复制，恢复表 t；

启动复制；

解锁表 t。

如果是大表，这里可以用可传输表空间方式备份、恢复表，减少锁表时间。

MySQL如何复制表中的一条记录并插入

1、打开navicat软件，打开要复制表的数据库，如下图所示：

2、点击上方的“工具->数据传输”，如下图所示：

3、进去之后，左边选择的是要复制的表的数据库，右边选择的将表复制到目标数据库，如下图所示：

4、打开左边数据库对象中的“表”，选择要复制哪几张表，点击开始。

5、点击开始，会弹出一个框，点击是，等待一下，出现如下界面，复制成功，点击“关闭”。

6、可以看到表已经复制到另外一个数据库上了，如下图所示：

mysql 5.7多源复制如何去掉一个复制源

GTID 对于单源复制还是很方便，但是对于多源复制，这里就需要特别注意：

要先停止所有的从库 stop slave;

然后清理本机所有的 GTID，reset master;

再进行 SET @@GLOBAL.GTID_PURGED='xxxxx' gtid 设置

做 reset master 的时候，先看看下游的从库是否存在很大的延迟。如果存在，把当前的 binlog 和后面未同步的 binlog 全部备份下；

待添加好从库的 channel 后，再把未同步的 binlog 文件手动拷贝到 binlog 目录；

更新下 mysql-bin.index 文件；

mysql 5.7多源复制如何去掉一个复制源

GTID 对于单源复制还是很方便，但是对于多源复制，这里就需要特别注意：

要先停止所有的从库 stop slave;

然后清理本机所有的 GTID，reset master;

再进行 SET @@GLOBAL.GTID_PURGED='xxxxx' gtid 设置

做 reset master 的时候，先看看下游的从库是否存在很大的延迟。如果存在，把当前的 binlog 和后面未同步的 binlog 全部备份下；

待添加好从库的 channel 后，再把未同步的 binlog 文件手动拷贝到 binlog 目录；

更新下 mysql-bin.index 文件；

mysql怎么实现主从复制

mysql主从复制的配置

1. 准备工作

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器。

初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。

因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

环境：

Master和slave的MySQL数据库版本同为5.6.28

操作系统：CentOS release 6.5 (Final)

IP地址：master:120.77.153.204 slave:120.77.148.74

2. master的配置

（1）创建复制帐号

mysql> CREATE USER'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)

（2）主从复制授权

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';

（3）拷贝数据

（假如是你完全新安装mysql主从服务器，这一步就不需要。因为新安装的master和slave有相同的数据）

关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，

禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

3. 修改配置文件

对master进行配置，包括打开二进制日志，指定唯一的servr ID

[root@myserver ~]# find / -type f -name my.cnf/usr/my.cnf[root@myserver ~]# vim /usr/my.cnfserver_id=1log-bin=mysql-bin

4. 重启master，运行SHOW MASTER STATUS，输出如下：

[root@myserver ~]# service mysql stopShutting down MySQL.... SUCCESS! [root@myserver ~]# service mysql startStarting MySQL. SUCCESS! [root@myserver ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.6.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> mysql> show master status; mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql>

配置slave

1. 修改my.cnf文件

[root@wenhaijin mysql-5.6.28]# find / -type f -name my.cnf/usr/my.cnf[root@wenhaijin mysql-5.6.28]# vim /usr/my.cnf log_bin = mysql-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only = 1

server_id是必须的，而且唯一。

slave没有必要开启二进制日志，但是在一些情况下，必须设置。

例如，如果slave为其他slave的master，必须设置bin_log。

在这里，我们开启了二进制日志，而且显示了命名（默认名称为hostname，但是，如果hostname改变则会出现问题）。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志（后面会看到它的用处）。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。

所以，尽量使用read_only，它防止改变数据（除了特殊的线程）。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

2. 启动slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。

你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='120.77.153.204', -> MASTER_USER='masterslave', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=0;Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

mysql> SHOW SLAVE STATUSG*************************** 1. row *************************** Slave_IO_State: Master_Host: 120.77.153.204 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。

实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行START SLAVE；

mysql> START SLAVE; mysql> SHOW SLAVE STATUSG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 120.77.153.204 Master_User: masterslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1762 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 1925 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1762 Relay_Log_Space: 2098 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4df9a165-03fd-11e7-b234-00163e0243e8 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)

在这里主要是看：

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。

日志的位置增加了，意味着一些事件被获取并执行了。

如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可通过show processlistG；查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接。

3. 授权masterslave用户远程登入，然后使用navicat远程连接

在master的test数据库中创建user表。

然后在slave中进行刷新发现user表已经同步过来。

刷新前：

刷新后：

在master的test库中user表添加一条数据。

然后打开slave中test库的user表，发现该数据也被同步过来了。

说明已经成功实现mysql的主从复制。

mysql怎么实现主从复制

mysql主从复制的配置

1. 准备工作

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器。

要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。

因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

环境：

Master和slave的MySQL数据库版本同为5.6.28

操作系统：CentOS release 6.5 (Final)

IP地址：master:120.77.153.204 slave:120.77.148.74

2. master的配置

（1）创建复制帐号

mysql> CREATE USER'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec)

（2）主从复制授权

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'masterslave'@'120.77.148.74' IDENTIFIED BY '123456';

（3）拷贝数据

（假如是你完全新安装mysql主从服务器，这一步就不需要。因为新安装的master和slave有相同的数据）

关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，

禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

3. 修改配置文件

对master进行配置，包括打开二进制日志，指定唯一的servr ID

[root@myserver ~]# find / -type f -name my.cnf/usr/my.cnf[root@myserver ~]# vim /usr/my.cnfserver_id=1log-bin=mysql-bin

4. 重启master，运行SHOW MASTER STATUS，输出如下：

配置slave

1. 修改my.cnf文件

server_id是必须的，而且唯一。

slave没有必要开启二进制日志，但是在一些情况下，必须设置。

例如，如果slave为其他slave的master，必须设置bin_log。

在这里，我们开启了二进制日志，而且显示了命名（默认名称为hostname，但是，如果hostname改变则会出现问题）。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志（后面会看到它的用处）。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。

所以，尽量使用read_only，它防止改变数据（除了特殊的线程）。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

2. 启动slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。

实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行START SLAVE；

在这里主要是看：

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。

日志的位置增加了，意味着一些事件被获取并执行了。

如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可通过show processlistG；查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接。

3. 授权masterslave用户远程登入，然后使用navicat远程连接

在master的test数据库中创建user表。

然后在slave中进行刷新发现user表已经同步过来。

刷新前：

刷新后：

在master的test库中user表添加一条数据。

然后打开slave中test库的user表，发现该数据也被同步过来了。

说明已经成功实现mysql的主从复制。

mysql gtid同步能跳过多个gtid吗

一、普通跳过一个事务的方法。
通过show slave status\G找到冲突的GTID号.
然后执行
SET gtid_next = '冲突的GTID号';
BEGIN;COMMIT;
SET gtid_next = 'AUTOMATIC';
START SLAVE;
这就可以跳过一个事务了,原理在于通过执行一个空事务代替master传递过来的冲突事务.
二、通过备份的mp.sql文件搭建新的slave.
开启gtid以后,使用mysqlmp备份,一般系统会要求你使用–all-databases参数,避免主库和从库有部分数据不一致。
备份以后,mp.sql里面会有这样一条语句.
SET @@GLOBAL.GTID_PURGED='0140505e-4230-11e4-b7c9-000c29da163d:1-8,dd079e18-4244-11e4-b851-000c29da163e:1-2';

这个就是当前主库已经执行过的GTID，也就是mp.sql里面的数据已经包含的GTID，对于这些GTID,slave是不用执行了,所以我们需要设置slave从这些
GTID以后开始复制.方法就是上面这条sql.
将mp.sql直接导入到slave以后.就可以直接change master了.
如果你是想通过这个方法跳过某个或者某些GTID，那么有点不同了.
set @@GLOBAL.GTID_PURGED是有要求的,也就是GTID_PURGED必须为空才可以设置,如何把GTID_PURGED清空呢.也就是需要执行
(root:hostname)[test]> show global variables like '%gtid%';
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 0140505e-4230-11e4-b7c9-000c29da163d:1-25,
dd079e18-4244-11e4-b851-000c29da163e:1-2,

mysql gtid同步能跳过多个gtid吗

MySQL 5.7复制的一个小bug-XA事务

线上一个5.7从库复制中断：

查询具体报错：

第一感觉很奇怪，为什么会rollback失败呢？于是根据gtid去对应的主库binlog去看了下，并没有任何rollback语句：

看下本地的relay log，找到这个事务的gtid

到这里，这个relay log日志文件结束了。很显然问题也找到了，就是执行

出错了。

首先我们看到这个rollback是MySQL自己加上去的，那么为什么要加呢？
mysql为了保证一个事务只在一个binlog里，所以当Binlog或者relay log发生截断时，最后一个事务要么commit，要么rollback，如果rollback，那么下一个binlog或者relay log会把这个事务重做一遍，保证这个事务不会丢。
由于xa事务无法直接rollback，而需要xa rollback ‘XXX’，所以复制就停了。
怎么修复？是不是直接跳过这个rollback就行了？
我们来试一下，跳过这个rollback:

这里GTID_NEXT值不能用show slave status的里executed值，得用具体报错的停止的gtid
但是，show slave status看到，还是有报错：

为什么又报这个事务commit找不到XID呢？
之前说过，在relaylog截断的时候，如果事务没有commit,会自动在最后加rollback，在下一个relay log开始的时候重新做一次这个事务，按理说我们跳过这个rollback，在下个relaylog会被重做，为啥会在commit的时候找不到xid呢？
我们看到我们跳过的gtid原来就是重做这个事务到PREPARE阶段的gtid，原来，rollback是没有gtid的，所以我们实际上就是把这个事务到PREPARE阶段的gtid给跳过了，commit的时候肯定会找不到xid，接着怎么修复？

为什么这是5.7的一个bug呢？5.7之前的版本因为relaylog被截断并不会出现这个bug。
5.7对xa事务的binlog记录方式做了修改，把 xa start,xa end,xa prepare放到一个event里，xa commit又是另外一个event。而在之前的MySQL版本中，整个xa事务从start到commit都是在一个event中，所以其他版本并没有问题。
最后一个问题：5.7为啥要把xa事务拆成两个event？简单的讲是为了数据安全性。
5.5或者5.6假设下面一个场景：

MySQL在某个分布式事务prepare成功后宕机，宕机前操作该事务的连接并没有断开（如果在宕机前断开连接，事务会被MySQL自动回滚），这个时候已经prepare的事务并不会被回滚，所以在MySQL重新启动后，引擎层通过recover机制能恢复该事务。当然该事务的Binlog已经在宕机过程中被丢失，这个时候，如果去提交，则会造成主从数据的不一致，即提交没有记录Binlog，从上丢失该条数据。

正因为5.7之前版本的xa事务存在这个bug，5.7后做了修复。从XA START到XA PREPARE之间的操作都被记录到了Master的Binlog中，然后通过复制关系传到了Slave上。也就是说5.7开始，MySQL对于XA事务，在prepare的时候就完成了写Binlog的操作，通过新增一种叫 XA_prepare_log_event 的event类型来实现。

其实 MySQL5.7在xa事务上远不止这个bug，后面再来慢慢总结。

MySQL 5.7复制的一个小bug-XA事务

MySQL—online关闭/开启GTID

开启步骤:
SET GLOBAL ENFORCE_GTID_CONSISTENCY='WARN';
SET GLOBAL ENFORCE_GTID_CONSISTENCY='ON';
SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON';
配置文件中GTID_MODE = ON;
ENFORCE_GTID_CONSISTENCT

平滑关闭步骤：
（1）：stop slave;（如果是主库就不必执行此步骤）
（2）：SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
（3）：SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
（4）：SET GLOBAL GTID_MODE = 'OFF';
之后查看状态：
select @@GLOBAL.GTID_MODE
配置文件中GTID_MODE = OFF;

关闭ENFORCE_GTID_CONSISTENCY：
在线修改：SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;
配置文件中：ENFORCE_GTID_CONSISTENCY = off;

传统主从复制的binlog和position号选择切换时间的第一个binlog的第一号

MySQL—online关闭/开启GTID

如何将数据从Oracle数据库实时复制到MYSQL

1、可以用PB、PLSQL
Developer等软件，把表的数据倒成SQL文件，在MySql中执行！
2、也可以尝试写
数据迁移
程序，大概步骤是这样：
1）、根据双方数据库的
数据字典
，罗列出需要的字段
2）、编写相应的迁移程序，主要注意的地方就是Oracle的数据类...

如何将数据从Oracle数据库实时复制到MYSQL