devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql和Gtid的主从复制

作者：IT小白 | 2024-08-10 16:32:21

赞

踩

mysql和gtid

实验环境：Server3:master

                  Server4:slave

一、mysql主从复制

Mysql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（insert,update,delete,ALTER TABLE,grant等等）。
主从复制的原理其实就是把主服务器上的ＢＩＮ日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

复制流程图：

这里写图片描述

复制过程：

  1. 主节点必须启用二进制日志，记录任何修改数据库数据的事件。
  2. 从节点开启一个线程（I/O Thread)把自己扮演成mysql的客户端，通过mysql协议，请求主节点的二进制日志文件中的事件
  3. 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
  4. 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具哪个二进制日志文件的哪个位置。
  5. 从节点启动另外一个线程（sql Thread ），把replaylog中的事件读取出来，并在本地再执行一次。

复制中线程的作用：

从节点：

  • I/O Thread:从Master请求二进制日志事件，并保存于中继日志中。
  • Sql Thread:从中继日志中读取日志事件，在本地完成重放。

主节点：

  • Dump Thread:为每个Slave的I/O Thread启动一个dump线程，用于向从节点发送二进制事件。

思考：从节点需要建立二进制日志文件吗？
看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

Mysql复制特点：

  1. 异步复制：主节点中一个用户请求一个写操作时，主接点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，在响应用 户，主机点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主服务，可以使用（很多软件来检查是否落后）
  2. 主从数据不一致。

主从复制配置过程：

主节点：

  1. 启用二进制日志。
  2. 为当前节点设置一个全局唯一的server_id。
  3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

  1. 启动中继日志。
  2. 为当前节点设置一个全局唯一的server_id。
  3. 使用有复制权限的用户账号连接至主节点，并启动复制线程。
  1. [root@server3 ~]# ls
  2.  
  3. mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
  4.  
  5. mysql-community-client-5.7.17-1.el6.x86_64.rpm
  6.  
  7. mysql-community-common-5.7.17-1.el6.x86_64.rpm
  8.  
  9. mysql-community-devel-5.7.17-1.el6.x86_64.rpm
  10.  
  11. mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
  12.  
  13. mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
  14.  
  15. mysql-community-libs-5.7.17-1.el6.x86_64.rpm
  16.  
  17. mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
  18.  
  19. mysql-community-server-5.7.17-1.el6.x86_64.rpm
  20.  
  21. mysql-community-test-5.7.17-1.el6.x86_64.rpm
  22.  
  23. [root@server3 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y 
  24.  
  25. [root@server3 ~]# scp mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm  root@172.25.26.5:/root

[root@server3 ~]# vim /etc/my.cnf


 

  1. [root@server3 ~]# /etc/init.d/mysqld  start
  2.  
  3. Initializing MySQL database:                               [  OK  ]
  4.  
  5. Installing validate password plugin:                       [  OK  ]
  6.  
  7. Starting mysqld:                                           [  OK  ]
  8.  
  9. [root@server3 ~]# grep password  /var/log/mysqld.log
  10.  
  11. 2018-08-08T01:33:54.815320Z 1 [Note] A temporary password is generated for root@localhost: 8-Hq;jp>MCME
  12.  
  13. [root@server3 ~]# mysql_secure_installation
  14.  
  15. Securing the MySQL server deployment.
  16.  
  17. Enter password for user root: 
  18.  
  19. Error: Access denied for user 'root'@'localhost' (using password: YES)
  20.  
  21. [root@server3 ~]# mysql_secure_installation
  22.  
  23. Securing the MySQL server deployment.
  24.  
  25. Enter password for user root: 
  26.  
  27. The existing password for the user account root has expired. Please set a new password.
  28.  
  29. New password: XDwestos+007
  30.  
  31. Re-enter new password: 
  32.  
  33. The 'validate_password' plugin is installed on the server.
  34.  
  35. The subsequent steps will run with the existing configuration
  36.  
  37. of the plugin.
  38.  
  39. Using existing password for root.
  40.  
  41. Estimated strength of the password: 100 
  42.  
  43. Change the password for root ? ((Press y|Y for Yes, any other key for No) : 
  44.  
  45.  ... skipping.
  46.  
  47. By default, a MySQL installation has an anonymous user,
  48.  
  49. allowing anyone to log into MySQL without having to have
  50.  
  51. a user account created for them. This is intended only for
  52.  
  53. testing, and to make the installation go a bit smoother.
  54.  
  55. You should remove them before moving into a production
  56.  
  57. environment.
  58.  
  59. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
  60.  
  61. Success.
  62.  
  63. Normally, root should only be allowed to connect from
  64.  
  65. 'localhost'. This ensures that someone cannot guess at
  66.  
  67. the root password from the network.
  68.  
  69. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
  70.  
  71. Success.
  72.  
  73. By default, MySQL comes with a database named 'test' that
  74.  
  75. anyone can access. This is also intended only for testing,
  76.  
  77. and should be removed before moving into a production
  78.  
  79. environment.
  80.  
  81. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 
  82.  
  83.  ... skipping.
  84.  
  85. Reloading the privilege tables will ensure that all changes
  86.  
  87. made so far will take effect immediately.
  88.  
  89. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
  90.  
  91. Success.
  92.  
  93. All done! 
  94.  
  95. [root@server3 ~]# mysql -p
  96.  
  97. Enter password: 
  98.  
  99. Welcome to the MySQL monitor.  Commands end with ; or \g.
  100.  
  101. Your MySQL connection id is 9
  102.  
  103. Server version: 5.7.17-log MySQL Community Server (GPL)
  104.  
  105. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  106.  
  107. Oracle is a registered trademark of Oracle Corporation and/or its
  108.  
  109. affiliates. Other names may be trademarks of their respective
  110.  
  111. owners.
  112.  
  113. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  114.  
  115. mysql> show databases;
  116.  
  117. +--------------------+
  118.  
  119. | Database           |
  120.  
  121. +--------------------+
  122.  
  123. | information_schema |
  124.  
  125. | mysql              |
  126.  
  127. | performance_schema |
  128.  
  129. | sys                |
  130.  
  131. +--------------------+
  132.  
  133. 4 rows in set (0.00 sec)
  134.  
  135. mysql> grant replication slave on *.* to repl@'172.25.26.%' identified by 'XDwestos+007';
  136.  
  137. Query OK, 0 rows affected, 1 warning (0.08 sec)
  138.  
  139. mysql> show databases;
  140.  
  141. +--------------------+
  142.  
  143. | Database           |
  144.  
  145. +--------------------+
  146.  
  147. | information_schema |
  148.  
  149. | mysql              |
  150.  
  151. | performance_schema |
  152.  
  153. | sys                |
  154.  
  155. +--------------------+
  156.  
  157. 4 rows in set (0.00 sec)
  158.  
  159. mysql> show master status;
  160.  
  161. +------------------+----------+--------------+------------------+-------------------+
  162.  
  163. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  164.  
  165. +------------------+----------+--------------+------------------+-------------------+
  166.  
  167. | mysql-bin.000004 |      447 |              |                  |                   |
  168.  
  169. +------------------+----------+--------------+------------------+-------------------+
  170.  
  171. 1 row in set (0.00 sec)

Server4:

[root@server4 ~]# vim /etc/my.cnf


 

  1. [root@server4 ~]# /etc/init.d/mysqld  start
  2.  
  3. Stopping mysqld:                                           [  OK  ]
  4.  
  5. Starting mysqld:                                           [  OK  ]
  6.  
  7. [root@server4 ~]# grep password  /var/log/mysqld.log 
  8.  
  9. 2018-08-08T01:45:23.555632Z 1 [Note] A temporary password is generated for root@localhost: CUJQ3CH*m&g+
  10.  
  11. [root@server4 ~]# mysql_secure_installation 
  12.  
  13. Securing the MySQL server deployment.
  14.  
  15. Enter password for user root: 
  16.  
  17. The existing password for the user account root has expired. Please set a new password.
  18.  
  19. New password: XDwestos+007
  20.  
  21. Re-enter new password: 
  22.  
  23. The 'validate_password' plugin is installed on the server.
  24.  
  25. The subsequent steps will run with the existing configuration
  26.  
  27. of the plugin.
  28.  
  29. Using existing password for root.
  30.  
  31. Estimated strength of the password: 100 
  32.  
  33. Change the password for root ? ((Press y|Y for Yes, any other key for No) : 
  34.  
  35.  ... skipping.
  36.  
  37. By default, a MySQL installation has an anonymous user,
  38.  
  39. allowing anyone to log into MySQL without having to have
  40.  
  41. a user account created for them. This is intended only for
  42.  
  43. testing, and to make the installation go a bit smoother.
  44.  
  45. You should remove them before moving into a production
  46.  
  47. environment.
  48.  
  49. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
  50.  
  51. Success.
  52.  
  53. Normally, root should only be allowed to connect from
  54.  
  55. 'localhost'. This ensures that someone cannot guess at
  56.  
  57. the root password from the network.
  58.  
  59. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
  60.  
  61. Success.
  62.  
  63. By default, MySQL comes with a database named 'test' that
  64.  
  65. anyone can access. This is also intended only for testing,
  66.  
  67. and should be removed before moving into a production
  68.  
  69. environment.
  70.  
  71. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 
  72.  
  73.  ... skipping.
  74.  
  75. Reloading the privilege tables will ensure that all changes
  76.  
  77. made so far will take effect immediately.
  78.  
  79. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
  80.  
  81. Success.
  82.  
  83. All done! 
  84.  
  85. [root@server4 ~]# mysql -p
  86.  
  87. Enter password: 
  88.  
  89. Welcome to the MySQL monitor.  Commands end with ; or \g.
  90.  
  91. Your MySQL connection id is 7
  92.  
  93. Server version: 5.7.17 MySQL Community Server (GPL)
  94.  
  95. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  96.  
  97. Oracle is a registered trademark of Oracle Corporation and/or its
  98.  
  99. affiliates. Other names may be trademarks of their respective
  100.  
  101. owners.
  102.  
  103. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  104.  
  105. mysql> show databases;
  106.  
  107. +--------------------+
  108.  
  109. | Database           |
  110.  
  111. +--------------------+
  112.  
  113. | information_schema |
  114.  
  115. | mysql              |
  116.  
  117. | performance_schema |
  118.  
  119. | sys                |
  120.  
  121. +--------------------+
  122.  
  123. 4 rows in set (0.00 sec)
  124.  
  125. mysql> change master to master_host='172.25.26.4', master_user='repl', master_password='XDwestos+007', master_log_file='mysql-bin.000004', master_log_pos=447;
  126.  
  127. Query OK, 0 rows affected, 2 warnings (0.49 sec)
  128.  
  129. mysql> start slave;
  130.  
  131. Query OK, 0 rows affected (0.03 sec)
  132.  
  133. mysql> show slave status\G;
  134.  
  135. *************************** 1. row ***************************
  136.  
  137.                Slave_IO_State: Waiting for master to send event
  138.  
  139.                   Master_Host: 172.25.26.4
  140.  
  141.                   Master_User: repl
  142.  
  143.                   Master_Port: 3306
  144.  
  145.                 Connect_Retry: 60
  146.  
  147.               Master_Log_File: mysql-bin.000004
  148.  
  149.           Read_Master_Log_Pos: 447
  150.  
  151.                Relay_Log_File: server4-relay-bin.000002
  152.  
  153.                 Relay_Log_Pos: 320
  154.  
  155.         Relay_Master_Log_File: mysql-bin.000004
  156.  
  157.              Slave_IO_Running: Yes
  158.  
  159.             Slave_SQL_Running: Yes
  160.  
  161.               Replicate_Do_DB: 
  162.  
  163.           Replicate_Ignore_DB: 
  164.  
  165.            Replicate_Do_Table: 
  166.  
  167.        Replicate_Ignore_Table: 
  168.  
  169.       Replicate_Wild_Do_Table: 
  170.  
  171.   Replicate_Wild_Ignore_Table: 
  172.  
  173.                    Last_Errno: 0
  174.  
  175.                    Last_Error: 
  176.  
  177.                  Skip_Counter: 0
  178.  
  179.           Exec_Master_Log_Pos: 447
  180.  
  181.               Relay_Log_Space: 529
  182.  
  183.               Until_Condition: None
  184.  
  185.                Until_Log_File: 
  186.  
  187.                 Until_Log_Pos: 0
  188.  
  189.            Master_SSL_Allowed: No
  190.  
  191.            Master_SSL_CA_File: 
  192.  
  193.            Master_SSL_CA_Path: 
  194.  
  195.               Master_SSL_Cert: 
  196.  
  197.             Master_SSL_Cipher: 
  198.  
  199.                Master_SSL_Key: 
  200.  
  201.         Seconds_Behind_Master: 0
  202.  
  203. Master_SSL_Verify_Server_Cert: No
  204.  
  205.                 Last_IO_Errno: 0
  206.  
  207.                 Last_IO_Error: 
  208.  
  209.                Last_SQL_Errno: 0
  210.  
  211.                Last_SQL_Error: 
  212.  
  213.   Replicate_Ignore_Server_Ids: 
  214.  
  215.              Master_Server_Id: 1
  216.  
  217.                   Master_UUID: 1cbf9ff3-9aab-11e8-9613-5254006cc83b
  218.  
  219.              Master_Info_File: /var/lib/mysql/master.info
  220.  
  221.                     SQL_Delay: 0
  222.  
  223.           SQL_Remaining_Delay: NULL
  224.  
  225.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  226.  
  227.            Master_Retry_Count: 86400
  228.  
  229.                   Master_Bind: 
  230.  
  231.       Last_IO_Error_Timestamp: 
  232.  
  233.      Last_SQL_Error_Timestamp: 
  234.  
  235.                Master_SSL_Crl: 
  236.  
  237.            Master_SSL_Crlpath: 
  238.  
  239.            Retrieved_Gtid_Set: 
  240.  
  241.             Executed_Gtid_Set: 
  242.  
  243.                 Auto_Position: 0
  244.  
  245.          Replicate_Rewrite_DB: 
  246.  
  247.                  Channel_Name: 
  248.  
  249.            Master_TLS_Version: 
  250.  
  251. 1 row in set (0.00 sec)
  252.  
  253. ERROR: 
  254.  
  255. No query specified
  256.  
  257. Server3:
  258.  
  259. mysql> create database  test;
  260.  
  261. Query OK, 1 row affected (0.17 sec)
  262.  
  263. mysql> show databases;
  264.  
  265. +--------------------+
  266.  
  267. | Database           |
  268.  
  269. +--------------------+
  270.  
  271. | information_schema |
  272.  
  273. | mysql              |
  274.  
  275. | performance_schema |
  276.  
  277. | sys                |
  278.  
  279. | test               |
  280.  
  281. +--------------------+
  282.  
  283. 5 rows in set (0.00 sec)
  284.  
  285. mysql> use test
  286.  
  287. Database changed
  288.  
  289. mysql> create table userlist(
  290.  
  291.     -> username varchar(15) not null,
  292.  
  293.     -> password varchar(15) not null);
  294.  
  295. Query OK, 0 rows affected (0.71 sec)
  296.  
  297. mysql> desc userlist
  298.  
  299.     -> ;
  300.  
  301. +----------+-------------+------+-----+---------+-------+
  302.  
  303. | Field    | Type        | Null | Key | Default | Extra |
  304.  
  305. +----------+-------------+------+-----+---------+-------+
  306.  
  307. | username | varchar(15) | NO   |     | NULL    |       |
  308.  
  309. | password | varchar(15) | NO   |     | NULL    |       |
  310.  
  311. +----------+-------------+------+-----+---------+-------+
  312.  
  313. 2 rows in set (0.01 sec)
  314.  
  315. mysql> insert into userlist values ('user1','123');
  316.  
  317. Query OK, 1 row affected (0.21 sec)
  318.  
  319. mysql> insert into userlist values ('user2','456');
  320.  
  321. Query OK, 1 row affected (0.14 sec)
  322.  
  323. mysql> insert into userlist values ('user3','789');
  324.  
  325. Query OK, 1 row affected (0.15 sec)
  326.  
  327. Server4:
  328.  
  329. mysql> use test
  330.  
  331. Reading table information for completion of table and column names
  332.  
  333. You can turn off this feature to get a quicker startup with -A
  334.  
  335. Database changed
  336.  
  337. mysql> select * from userlist;
  338.  
  339. +----------+----------+
  340.  
  341. | username | password |
  342.  
  343. +----------+----------+
  344.  
  345. | user1    | 123      |
  346.  
  347. | user2    | 456      |
  348.  
  349. | user3    | 789      |
  350.  
  351. +----------+----------+
  352.  
  353. 3 rows in set (0.00 sec)

二、gtid主从复制

基于GTID的复制是从Mysql5.6开始支持 的一种新的复制方式，此方式与传统基于日志的方式存在很大的差异，在原来的基于日志的复制中，从服务器连接到主服务器并告诉主服务器要从哪个二进制日志的 偏移量开始执行增量同步，这时我们如果指定的日志偏移量不对，这与可能造成主从数据的不一致，而基于GTID的复制会避免。
在 基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库 上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。
什么是GTID，也就是全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。
一 个GITD由两部分组成的，分别是source_id 和transaction_id，GTID=source_id:transaction_id，其中source_id就是执行事务的主库的 server-uuid值，server-uuid值是在mysql服务首次启动生成的，保存在数据库的数据目录中，在数据目录中有一个 auto.conf文件，这个文件保存了server-uuid值（唯一的）。而事务ID则是从1开始自增的序列，表示这个事务是在主库上执行的第几个事 务，Mysql会保证这个事务和GTID是一比一的关系。

配置主数据库服务器需要做的大概和传统的主从配置差不多，需要起码的在主服务器上建立复制账号，还要配置数据库日志文件的目录，这是必须的启动bin_log日志。
可 以指定bin_log存放目录，而不是用数据目录，分开存储是个好习惯，特别是如果把日志和数据放在不同的磁盘分区上，这样不但可以避免日志的增长把数据 磁盘分区占满，也可以提高了磁盘IO。如bin_log = /usr/local/mysql/log/mysql-bin。

优点
   A：很方便的进行故障转移，因为GTID是全局唯一的标识符，所以就很简单知道哪些事务在从服务器没有执行，在多个从服务器也没必要进行多个日志偏移量配置了.

   B：从库和主库的数据一致性。

缺点
  A:故障处理比日志处理复杂。
  B:执行语句的一些限制。

Server3:

[root@server3 ~]# vim /etc/my.cnf

[root@server3 ~]# /etc/init.d/mysqld  restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

Server4:

[root@server4 ~]# vim /etc/my.cnf

[root@server4 ~]# /etc/init.d/mysqld  restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

Server3:

[root@server3 ~]# mysql -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000005 |      154 |              |                  |                   |

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

1 row in set (0.00 sec)

Server4:

[root@server4 ~]# mysql -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.26.4

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 154

               Relay_Log_File: server4-relay-bin.000005

                Relay_Log_Pos: 367

        Relay_Master_Log_File: mysql-bin.000005

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

              Relay_Log_Space: 742

              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: 1cbf9ff3-9aab-11e8-9613-5254006cc83b

             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 more updates

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

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> stop slave;

Query OK, 0 rows affected (0.06 sec)

mysql> change master to master_host='172.25.26.4', master_user='repl', master_password='XDwestos+007', MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.26.4

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 154

               Relay_Log_File: server4-relay-bin.000002

                Relay_Log_Pos: 367

        Relay_Master_Log_File: mysql-bin.000005

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

              Relay_Log_Space: 576

              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: 1cbf9ff3-9aab-11e8-9613-5254006cc83b

             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 more updates

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

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

ERROR: 

No query specified

Server3:

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| userlist       |

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

1 row in set (0.00 sec)

mysql> delete from userlist where username='user3';

Query OK, 1 row affected (0.08 sec)

Server4:

mysql> use  test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from userlist;

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

| username | password |

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

| user1    | 123      |

| user2    | 456      |

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

2 rows in set (0.00 sec)

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/959356
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号