本文最后更新于 421 天前,其中的信息可能已经过时,如有错误请发送邮件到 wuxianglongblog@163.com
| MySQL主从复制(Replication)指的是两台或以上数据库实例服务器,通过二进制日志实现数据的"同步"关系。 |
| |
| 需要注意的是,MySQL主从复制并不是说其工作模式是同步的,实际上,MySQL主从复制(Replication)是一个异步工作模式。 |
| MySQL主从复制(Replication)部署的前提有哪些呢,你是否会想到以下的信息呢? |
| (1)MySQL主从复制(Replication)的前提是主库必须开启二进制日志功能; |
| (2)至少有两台以上MySQL实例,至少一个主库,至少有一个从库,要有不同角色切换,需要配置"server_id"属性; |
| (3)数据库实例之间时间要同步; |
| (4)网络要通畅; |
| (5)开启专用复制线程; |
| (6)确认复制起点,从库需要从主库中拉取数据; |
| (7)从库需要复制指定数据库的权限; |
| ... |
| |
| 综上所述,MySQL主从复制(Replication)的前提总结为以下几点: |
| (1)需要2台以上数据库实例,时间同步,网络通畅,server_id不同,区分不同角色(即主库和从库); |
| (2)主库开启binlog,建立专用复制用户; |
| (3)从库需要提前"补课"(将之前"落下"的数据补全); |
| (4)从库需要确认主库的链接信息,复制起点等; |
| (5)从库需要开启专用的复制线程; |
| 主库: |
| (1)binlog文件 |
| 默认存储在数据目录下,用于存储用户的操作记录,我们可以基于"log_bin_basename"来制定存储的路径及文件名前缀。 |
| mysql> SELECT @@log_bin; |
| + |
| | @@log_bin | |
| + |
| | 1 | |
| + |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| mysql> SELECT @@log_bin_basename; |
| + |
| | @@log_bin_basename | |
| + |
| | /oldboyedu/data/mysql3308/mysql-bin | |
| + |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| 需要注意的是,我们可以不配置"log_bin_basename"参数,但"log_bin"参数的值必须为1,表示开启状态。否则主从复制将无法进行! |
| |
| 从库: |
| (1)relay-log文件: |
| 作用: |
| 用于接收存储binlog,也称为"中继日志"。 |
| 默认存储路径: |
| 默认存储在数据目录下。 |
| 手动定义存储路径: |
| 如下所示,我们可以基于"relay_log_basename"来手动指定存储路径。 |
| mysql> SELECT @@relay_log_basename; |
| + |
| | @@relay_log_basename | |
| + |
| | /oldboyedu/data/mysql3308/docker201-relay-bin | |
| + |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| 如果我们在配置文件中不指定"relay_log_basename"参数,则默认的文件名为"docker201-relay-bin.000001,docker201-relay-bin.000002,docker201-relay-bin.000003,..." |
| |
| (2)master.info文件: |
| 作用: |
| 用于存储主库的链接信息,已经接受的binlog位置点信息等数据。 |
| 默认存储路径: |
| 默认存储在数据目录下。 |
| 手动定义存储路径: |
| 如下所示,我们可以基于"master_info_repository"来手动指定该文件存储位置。默认以文件方式存储,我们也可以将其存储在MySQL的表中以提升性能。 |
| mysql> SELECT @@master_info_repository; |
| + |
| | @@master_info_repository | |
| + |
| | FILE | |
| + |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| |
| (3)relay-log.info文件: |
| 作用: |
| 总所周知,从库会单独开启一个I/O线程从主库拉取二进制日志并存储中继日志(上面提到的"relay-log"文件)中,而后从库开启一个SQL线程基于中继日志进行回放,以达到和主库同样的数据。 |
| 而relay-log.info文件就是用于记录从库回放到relay-log的位置点,这是为了防止从库突然宕机后,重启服务器后知道上一次回放的位置点,而后基于该记录的位置点继续往下执行SQL。 |
| 默认存储路径: |
| 默认存储在数据目录下。 |
| 手动定义存储路径: |
| 如下所示,我们可以基于"relay_log_info_repository"来手动指定该文件存储位置。默认以文件方式存储,我们也可以将其存储在MySQL的表中以提升性能。 |
| mysql> SELECT @@relay_log_info_repository ; |
| + |
| | @@relay_log_info_repository | |
| + |
| | FILE | |
| + |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| 主库: |
| binlog_dump线程: |
| 作用: |
| 用来接收从库请求,并且投递binlog给从库。 |
| 查看方式: |
| mysql> SHOW PROCESSLIST; # 不难发现有两个从库用户,分配的binlog_dump线程ID为5和6。 |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| | Id | User | Host | db | Command | Time | State | Info | |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| | 5 | copy | docker201.oldboyedu.com:49590 | NULL | Binlog Dump | 33278 | Master has sent all binlog to slave; waiting for more updates | NULL | |
| | 6 | copy | docker201.oldboyedu.com:49592 | NULL | Binlog Dump | 33274 | Master has sent all binlog to slave; waiting for more updates | NULL | |
| | 10 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| 3 rows in set (0.00 sec) |
| |
| mysql> |
| |
| 从库: |
| IO线程: |
| 用于向主库请求,接收和存储binlog日志。 |
| SQL线程: |
| 用于回放中继日志(上面提到的"relay-log"文件),执行"relay-log"文件的SQL语句并将执行的位置点记录在"relay-log.info"文件中。 |
| 查看方式: |
| [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:" |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~]# |
| 主从复制原理: |
| (1)如下所示,从库执行"CHANGE MASTER TO ..."命令,执行命令成功后会将这些主库的链接信息记录在"master.info"文件中; |
| mysql> CHANGE MASTER TO |
| -> MASTER_HOST='172.200.1.201', |
| -> MASTER_USER='copy', |
| -> MASTER_PASSWORD='oldboyedu', |
| -> MASTER_PORT=3307, |
| -> MASTER_LOG_FILE='mysql-bin.000004', |
| -> MASTER_LOG_POS=908, |
| -> MASTER_CONNECT_RETRY=10; |
| Query OK, 0 rows affected, 2 warnings (0.31 sec) |
| |
| mysql> |
| |
| (2)执行"START SLAVE"命令后,从库会开启IO线程和SQL线程这两个线程,其中IO线程负责发送请求到主库,有关主库的链接信息在"master.info"文件中都有记录; |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| |
| (3)主库分配了一个binlog_dump线程来响应从库的IO线程,我们可以通过"SHOW PROCESSLIST"命令来查看响应的binlog_dump线程; |
| mysql> SHOW PROCESSLIST; |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| | Id | User | Host | db | Command | Time | State | Info | |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| | 5 | copy | docker201.oldboyedu.com:49590 | NULL | Binlog Dump | 33278 | Master has sent all binlog to slave; waiting for more updates | NULL | |
| | 6 | copy | docker201.oldboyedu.com:49592 | NULL | Binlog Dump | 33274 | Master has sent all binlog to slave; waiting for more updates | NULL | |
| | 10 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | |
| +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+ |
| 3 rows in set (0.00 sec) |
| |
| mysql> |
| |
| (4)从库的IO线程会请求新日志,有关向主库请求的日志位置点信息在"master.info"文件中都有记录; |
| |
| (5)主库的binlog_dump线程接收从库的IO线程请求后,会截取主库的二进制日志文件,并将结果返回给从库的IO线程; |
| |
| (6)从库的IO线程接收到主库的binlog后,日志先发送到网卡的缓存区域中,此时由网络层返回ACK给主库,主库工作完成; |
| |
| (7)从库的IO线程最终会将数据从网卡的缓冲区拉取并写入中继日志文件(上面提到的"relay-log"文件)中以落地到本地磁盘,于此同时会更新"master.info"文件中记录的位置点信息,以便于下一次从库IO线程知道从哪个位置点请求"新日志",I/O线程工作完成。 |
| |
| (8)从库的SQL线程读取"relay-log.info"文件,获取上一次中继日志文件(上面提到的"relay-log"文件)执行到的位置点; |
| |
| (9)从库的SQL线程根据上一步获得中继日志文件(上面提到的"relay-log"文件)的位置点后,在该位置点继续向后执行新的"relay-log"日志,而后会更新"relay-log.info"文件,,以便于下一次从库SQL线程知道从哪个位置点读取"新日志",主从复制流程基本结束。 |
| |
| 主从复制中的一些小细节: |
| (1)从库的relay_log_purge如果是开启状态,则从库会定期删除已经应用过的中继日志文件(上面提到的"relay-log"文件),以节省空间; |
| mysql> SELECT @@relay_log_purge; |
| +-------------------+ |
| | @@relay_log_purge | |
| +-------------------+ |
| | 1 | |
| +-------------------+ |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| |
| (2)主库的binlog_dump线程会实时监控主库的binlog变化,如果有变化,会发送信号给从库,而后从库会发起IO线程向主库的binlog_dump线程发起请求以拉取新日志。 |
| MySQL主从复制(Replication)架构原理如下图所示。 |
| [root@docker201.oldboyedu.com ~]# ss -ntl |
| State Recv-Q Send-Q Local Address:Port Peer Address:Port |
| LISTEN 0 128 172.200.1.201:6379 *:* |
| LISTEN 0 128 *:22 *:* |
| LISTEN 0 128 [::]:80 [::]:* |
| LISTEN 0 128 [::]:22 [::]:* |
| [root@docker201.oldboyedu.com ~]# |
| [root@docker201.oldboyedu.com ~]# systemctl start mysqld3309 mysqld3308 mysqld3307 |
| [root@docker201.oldboyedu.com ~]# |
| [root@docker201.oldboyedu.com ~]# ss -ntl |
| State Recv-Q Send-Q Local Address:Port Peer Address:Port |
| LISTEN 0 128 172.200.1.201:6379 *:* |
| LISTEN 0 128 *:22 *:* |
| LISTEN 0 80 [::]:3307 [::]:* |
| LISTEN 0 80 [::]:3308 [::]:* |
| LISTEN 0 80 [::]:3309 [::]:* |
| LISTEN 0 128 [::]:80 [::]:* |
| LISTEN 0 128 [::]:22 [::]:* |
| [root@docker201.oldboyedu.com ~]# |
| [root@docker201.oldboyedu.com ~] |
| +-------------+ |
| | @@server_id | |
| +-------------+ |
| | 7 | |
| +-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +-------------+ |
| | @@server_id | |
| +-------------+ |
| | 8 | |
| +-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +-------------+ |
| | @@server_id | |
| +-------------+ |
| | 9 | |
| +-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +-----------+ |
| | @@log_bin | |
| +-----------+ |
| | 1 | |
| +-----------+ |
| [root@docker201.oldboyedu.com ~] |
| MySQL 8.0之前版本创建复制用户: |
| [root@docker201.oldboyedu.com ~] |
| +---------------+-----------+ |
| | user | host | |
| +---------------+-----------+ |
| | mysql.session | localhost | |
| | mysql.sys | localhost | |
| | root | localhost | |
| +---------------+-----------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +---------------+-------------+ |
| | user | host | |
| +---------------+-------------+ |
| | repl | 172.200.1.% | |
| | mysql.session | localhost | |
| | mysql.sys | localhost | |
| | root | localhost | |
| +---------------+-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +--------------------------------------------------------+ |
| | Grants for repl@172.200.1.% | |
| +--------------------------------------------------------+ |
| | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.200.1.%' | |
| +--------------------------------------------------------+ |
| [root@docker201.oldboyedu.com ~] |
| |
| MySQL 8.0之后版本创建复制用户: |
| [root@docker201.oldboyedu.com ~] |
| +---------------+-------------+ |
| | user | host | |
| +---------------+-------------+ |
| | repl | 172.200.1.% | |
| | mysql.session | localhost | |
| | mysql.sys | localhost | |
| | root | localhost | |
| +---------------+-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +---------------+-------------+ |
| | user | host | |
| +---------------+-------------+ |
| | copy | 172.200.1.% | |
| | repl | 172.200.1.% | |
| | mysql.session | localhost | |
| | mysql.sys | localhost | |
| | root | localhost | |
| +---------------+-------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +--------------------------------------------+ |
| | Grants for copy@172.200.1.% | |
| +--------------------------------------------+ |
| | GRANT USAGE ON *.* TO 'copy'@'172.200.1.%' | |
| +--------------------------------------------+ |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| +--------------------------------------------------------+ |
| | Grants for copy@172.200.1.% | |
| +--------------------------------------------------------+ |
| | GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.200.1.%' | |
| +--------------------------------------------------------+ |
| [root@docker201.oldboyedu.com ~] |
| |
| 测试用户是否正常登录: |
| [root@docker201.oldboyedu.com ~] |
| mysql: [Warning] Using a password on the command line interface can be insecure. |
| Welcome to the MySQL monitor. Commands end with ; or \g. |
| Your MySQL connection id is 2 |
| Server version: 5.7.31-log MySQL Community Server (GPL) |
| |
| Copyright (c) 2000, 2020, 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> |
| mysql> SHOW DATABASES; |
| +--------------------+ |
| | Database | |
| +--------------------+ |
| | information_schema | |
| +--------------------+ |
| 1 row in set (0.01 sec) |
| |
| mysql> QUIT |
| Bye |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| mysql: [Warning] Using a password on the command line interface can be insecure. |
| Welcome to the MySQL monitor. Commands end with ; or \g. |
| Your MySQL connection id is 3 |
| Server version: 5.7.31-log MySQL Community Server (GPL) |
| |
| Copyright (c) 2000, 2020, 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> |
| mysql> SHOW DATABASES; |
| +--------------------+ |
| | Database | |
| +--------------------+ |
| | information_schema | |
| +--------------------+ |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| mysql> QUIT |
| Bye |
| [root@docker201.oldboyedu.com ~] |
| |
| 绑定MySQL实例的IP地址: |
| [root@docker201.oldboyedu.com ~] |
| State Recv-Q Send-Q Local Address:Port Peer Address:Port |
| LISTEN 0 128 172.200.1.201:6379 *:* |
| LISTEN 0 128 *:22 *:* |
| LISTEN 0 80 [::]:3307 [::]:* |
| LISTEN 0 80 [::]:3308 [::]:* |
| LISTEN 0 80 [::]:3309 [::]:* |
| LISTEN 0 128 [::]:80 [::]:* |
| LISTEN 0 128 [::]:22 [::]:* |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [mysqld] |
| bind-address=172.200.1.201 |
| basedir=/oldboyedu/softwares/mysql |
| datadir=/oldboyedu/data/mysql3307 |
| socket=/tmp/mysql3307.sock |
| log_error=/oldboyedu/data/mysql3307/mysql-err.log |
| port=3307 |
| server_id=7 |
| log_bin=/oldboyedu/data/mysql3307/mysql-bin |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| State Recv-Q Send-Q Local Address:Port Peer Address:Port |
| LISTEN 0 80 172.200.1.201:3307 *:* |
| LISTEN 0 128 172.200.1.201:6379 *:* |
| LISTEN 0 80 172.200.1.201:3308 *:* |
| LISTEN 0 80 172.200.1.201:3309 *:* |
| LISTEN 0 128 *:22 *:* |
| LISTEN 0 128 [::]:80 [::]:* |
| LISTEN 0 128 [::]:22 [::]:* |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| -rw-r--r-- 1 root root 861076 2月 13 22:58 /tmp/all_db.sql |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| (1)查看官方提供的"CHANGE MASTER TO"命令帮助信息: |
| mysql> HELP CHANGE MASTER TO |
| |
| ... |
| |
| CHANGE MASTER TO |
| MASTER_HOST='master2.example.com', |
| MASTER_USER='replication', |
| MASTER_PASSWORD='password', |
| MASTER_PORT=3306, |
| MASTER_LOG_FILE='master2-bin.001', |
| MASTER_LOG_POS=4, |
| MASTER_CONNECT_RETRY=10; |
| |
| The next example shows an operation that is less frequently employed. |
| It is used when the slave has relay log files that you want it to |
| execute again for some reason. To do this, the master need not be |
| reachable. You need only use CHANGE MASTER TO and start the SQL thread |
| (START SLAVE SQL_THREAD): |
| |
| CHANGE MASTER TO |
| RELAY_LOG_FILE='slave-relay-bin.006', |
| RELAY_LOG_POS=4025; |
| |
| URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html |
| |
| mysql> |
| |
| (2)获取主库的复制点起始位置信息: |
| [root@docker201.oldboyedu.com ~] |
| -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=908; |
| [root@docker201.oldboyedu.com ~] |
| |
| (3)两个从库建立主库的链接信息,并确定复制的起点 |
| [root@docker201.oldboyedu.com ~] |
| Welcome to the MySQL monitor. Commands end with ; or \g. |
| Your MySQL connection id is 2 |
| Server version: 5.7.31-log MySQL Community Server (GPL) |
| |
| Copyright (c) 2000, 2020, 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> |
| mysql> CHANGE MASTER TO |
| -> MASTER_HOST='172.200.1.201', |
| -> MASTER_USER='copy', |
| -> MASTER_PASSWORD='oldboyedu', |
| -> MASTER_PORT=3307, |
| -> MASTER_LOG_FILE='mysql-bin.000004', |
| -> MASTER_LOG_POS=908, |
| -> MASTER_CONNECT_RETRY=10; |
| Query OK, 0 rows affected, 2 warnings (0.31 sec) |
| |
| mysql> |
| mysql> QUIT |
| Bye |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Welcome to the MySQL monitor. Commands end with ; or \g. |
| Your MySQL connection id is 2 |
| Server version: 5.7.31-log MySQL Community Server (GPL) |
| |
| Copyright (c) 2000, 2020, 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> |
| mysql> CHANGE MASTER TO |
| -> MASTER_HOST='172.200.1.201', |
| -> MASTER_USER='copy', |
| -> MASTER_PASSWORD='oldboyedu', |
| -> MASTER_PORT=3307, |
| -> MASTER_LOG_FILE='mysql-bin.000004', |
| -> MASTER_LOG_POS=908, |
| -> MASTER_CONNECT_RETRY=10; |
| Query OK, 0 rows affected, 2 warnings (0.29 sec) |
| |
| mysql> |
| mysql> QUIT |
| Bye |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| 需要注意的是,如果主从复制为搭建成功才会关心该步骤,如果已经搭建成功,请直接忽略该步骤。 |
| |
| 重置主从复制的配置信息: |
| [root@docker201.oldboyedu.com ~] |
| 查看原始的主库,从库信息: |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| + |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| + |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| + |
| [root@docker201.oldboyedu.com ~] |
| |
| 主库操作: |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| | oldboyedu | |
| + |
| [root@docker201.oldboyedu.com ~] |
| |
| 从库操作: |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| | oldboyedu | |
| + |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| + |
| | Database | |
| + |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | sys | |
| | oldboyedu | |
| + |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: No |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: Yes |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Slave_IO_Running: No |
| Slave_SQL_Running: Yes |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| [root@docker201.oldboyedu.com ~] |
| Welcome to the MySQL monitor. Commands end with ; or \g. |
| Your MySQL connection id is 57 |
| Server version: 5.7.31-log MySQL Community Server (GPL) |
| |
| Copyright (c) 2000, 2020, 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> |
| mysql> STOP SLAVE; |
| Query OK, 0 rows affected (0.00 sec) |
| |
| mysql> |
| mysql> SHOW SLAVE STATUS\G |
| *************************** 1. row *************************** |
| Slave_IO_State: |
| Master_Host: 172.200.1.201 |
| Master_User: copy |
| Master_Port: 3307 |
| Connect_Retry: 10 |
| Master_Log_File: mysql-bin.000005 |
| Read_Master_Log_Pos: 350 |
| Relay_Log_File: docker201-relay-bin.000005 |
| Relay_Log_Pos: 320 |
| Relay_Master_Log_File: mysql-bin.000005 |
| 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: 350 |
| Relay_Log_Space: 697 |
| 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: NULL |
| 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: 7 |
| Master_UUID: 7c5bfd7b-52db-11eb-b842-000c29820c67 |
| Master_Info_File: /oldboyedu/data/mysql3309/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: 0 |
| Replicate_Rewrite_DB: |
| Channel_Name: |
| Master_TLS_Version: |
| 1 row in set (0.00 sec) |
| |
| mysql> |
| mysql> RESET SLAVE ALL; |
| Query OK, 0 rows affected (0.29 sec) |
| |
| mysql> |
| mysql> SHOW SLAVE STATUS\G |
| Empty set (0.00 sec) |
| |
| mysql> |
| 问题原因: |
| |
| 解决方案: |
| ALTER USER copy@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'oldboyedu'; |
| 问题原因: |
| RESET SLAVE ALL;前记得 |
| |
| 解决方案: |
| STOP SLAVE; |