欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL5.6基于GTID的主从复制_MySQL

程序员文章站 2022-05-04 13:39:08
...
MySQL 5.6 的新特性之一,是加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。

什么是GTID?

官方文档: 代码如下:

mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.88.205 IDENTIFIED BY 'replpassword';

说明:172.16.88.205是从节点服务器;如果想一次性授权更多的节点,可以自行根据需要修改;

七、启动从数据库上的复制线程

mysql> CHANGE MASTER TO MASTER_HOST='masterdb.example.com', 
MASTER_USER='repluser', MASTER_PASSWORD='replpassword', MASTER_AUTO_POSITION=1;
mysql>start slave;

八、在从数据库上查看复制状态

mysql> show slave status\G;
*************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
     Master_Host: masterdb.56xyl.com
     Master_User: repluser
     Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
   Read_Master_Log_Pos: 191
    Relay_Log_File: slavedb-relay-bin.000003
    Relay_Log_Pos: 401
  Relay_Master_Log_File: mysql-bin.000002
    Slave_IO_Running: Yes #IO线程已正常运行
   Slave_SQL_Running: Yes #SQL线程已正常运行
    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: 191
    Relay_Log_Space: 1899
    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: 1
     Master_UUID: 971d7245-c3f8-11e5-8b6b-000c2999e5a5
    Master_Info_File: mysql.slave_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: 971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6
   Executed_Gtid_Set: 89e78301-c3f4-11e5-8b51-00505624d26a:1-3,
971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6
    Auto_Position: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>


九、测试

在主库上创建数据库:

mysql> create database log_statics;
Query OK, 1 row affected (0.11 sec)
 
mysql> use log_statics;
Database changed
到从数据库上查看log_statics是否已经复制过去
mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| log_statics  |
| mysql    |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql>

可以看到log_statics数据库已经存在于从数据库上。

以上就是MySQL5.6基于GTID的主从复制_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!