MariaDB 10.0.10 Gtid Copy

One: Concept understanding:

1.tid:transaction ID, which is the transaction ID number of the MySQL server.

2.gtid:global Transaction ID, global transaction ID, no two thing IDs are the same in the entire master-slave replication schema.

3. The global thing ID is the ID number of the Mster server that generates a 128-bit uuid+ thing, and the UUID identifies the identity of the primary server, which is absolutely unique throughout the master-slave replication architecture. And even if the main server is replaced, the UUID will not change but inherit the current master server's UUID identity.

4. What is the use of the global transaction ID? Simply put, Gtid ensures that a data copy is made from the server to the other from the server and that data is integrated. Gtid can guarantee the consistency of data in a distributed architecture. This also enables high availability of MySQL.

5.GTID related actions: By default, when a transaction is logged into a binary file, its gtid is recorded first, and the Gtid and transaction-related information is sent to the slave server from the server for local application authentication but will never change the original transaction ID number.

6. Therefore, in the Gtid architecture, even if there is an n-tier architecture, replication is an n-level architecture, but the global transaction ID will remain unchanged, effectively ensuring the integrity and security of the data.

Two: Main options:

1, MY.CNF configuration:

Master:

[Email protected] ~]# grep-v ' # '/ETC/MYSQL/MY.CNF

[Client]

Port= 3306

Socket=/tmp/mysql.sock

[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 = 4

DataDir =/data/mydata

innodb_file_per_table = 1

Log-bin=/data/binlogs/master-bin

Binlog_format=row

The following are the main additions:

Server-id= 100

Log-slave-updates=true #Slave update is logged

Master-info-repository=table

Relay-log-info-repository=table #此两项为打开从服务器崩溃二进制日志功能, information is recorded in the things table instead of saved in the file

Sync-master-info=1 #值为1确保信息不会丢失

Slave-parallel-threads=2 #同时启动多少个复制线程, up to the same number of databases to replicate

BINLOG-CHECKSUM=CRC32 #效验码

Master-verify-checksum=1 #启动主服务器效验

Slave-sql-verify-checksum=1 #启动从服务器效验

Binlog-rows-query-log-events=1 #用于在二进制日志详细记录事件相关的信息, can reduce the complexity of troubleshooting;

report-port=3306 #请求的主机端口

Report-host=node4.a.com #请求的主机名, must be the full hostname and DNS can be resolved

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

Verify: View the current host's binary file progress:

Mysql> Show master status;

+-------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------+----------+--------------+------------------+

|      master-bin.000008 |              446 |                  | |

+-------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

View the details of the current binary file:

Mysql> show Binlog events in ' master-bin.000008 ';

+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+

|   master-bin.000008 | 4 |       Format_desc |         100 | 248 | Server ver:10.0.10-mariadb-log, Binlog ver:4 |

| master-bin.000008 | 248 |       Gtid_list |         100 | 277 | []                                             |

| master-bin.000008 | 277 |       Binlog_checkpoint |         100 | 321 | master-bin.000008 |

| master-bin.000008 | 321 |       Gtid |         100 | 363 | GTID 0-100-1 |

| master-bin.000008 | 363 |       Query |         100 | 446 | Create DATABASE SD |

+-------------------+-----+-------------------+-----------+-------------+-------------------------------------- ----------+

5 rows in Set (0.00 sec)

2. Create a copy account and authorize:

mysql> GRANT REPLICATION slave,replication CLIENT on * * to ' jack ' @ ' 192.168.%.% ' identified by ' 123456 ';

mysql> FLUSH privileges;

Slave:

[Email protected] ~]# grep-v ' # '/ETC/MYSQL/MY.CNF

[Client]

Port= 3306

Socket=/tmp/mysql.sock

[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 = 4

Innodb_file_per_table = On

DataDir =/data/mydata

Log-bin=/data/binlogs/master-bin

Server-id= 1

The following are the key configuration sections:

Binlog-format=row

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=node5.a.com

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

To connect to the primary server:

mysql> change MASTER to master_host= ' 192.168.10.204 ', master_user= ' Jack ', master_password= ' 123456 ', Master_log_ File= ' master-bin.000013 ', master_log_pos=379;

mysql> start slave;

Then confirm that the replication process has started successfully:

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.10.204

Master_user:jack

master_port:3306

Connect_retry:60

master_log_file:master-bin.000013

read_master_log_pos:725

relay_log_file:node5-relay-bin.000002

relay_log_pos:714

relay_master_log_file:master-bin.000013

Slave_io_running:yes

Slave_sql_running:yes

After the synchronization succeeds, it shuts down from the process:

mysql> stop Slave;

mysql> change MASTER to master_host= ' 192.168.10.204 ', master_user= ' Jack ', master_password= ' 123456 ', master_use_ Gtid=current_pos;

mysql> start slave;

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.10.204

Master_user:jack

master_port:3306

Connect_retry:60

master_log_file:master-bin.000013

read_master_log_pos:725

relay_log_file:node5-relay-bin.000002

relay_log_pos:714

relay_master_log_file:master-bin.000013

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:725

relay_log_space:1015

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:0

Master_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:100

MASTER_SSL_CRL:

Master_ssl_crlpath:

Using_gtid:current_pos

Gtid_io_pos:0-100-4

Has started to complete:

This article is from the "Linux" blog, so be sure to keep this source http://zhangshijie.blog.51cto.com/806066/1608883

MariaDB 10.0.10 Gtid Copy