热门标签
热门文章
- 1linux 修改22默认端口和扩展其他端口_22端口和其他端口
- 2git@gitee.com: Permission denied (publickey)_git上传了公钥还是permission denied (publickey).
- 3CorelDRAW Graphics Suite2024简体中文版本发布更新介绍_coreldraw 2024 25.1.1.328
- 4【MySQL 基础知识】
- 5如何在 PyTorch 上进行性能分析_vtune和torch profiler
- 6区块链开发者观点：来自 Carbon 的 Nick Pai_hedera postgresql
- 7crt批量增加session模板与脚本_crt.session
- 8解决因jbd2导致mysql性能过低问题
- 9XML的三大解析方式解析_解析xml文件的三种方式
- 10什么是提示词注入攻击？大语言模型为Web安全带来的新风险！
当前位置: article > 正文
GTID主从配置（一从一从、一主多从、多主一从）_物理层主从配置都是从,可以吗
作者：空白诗007 | 2024-08-10 16:26:19
赞
踩
物理层主从配置都是从,可以吗
GTID主从搭建
什么是GTID
- GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。
- GTID实际上是由UUID+TID组成的。
- 其中UUID是一个MySQL实例的唯一标识，保存在mysql数据目录下的auto.cnf文件里。
- TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
GTID与传统主从的区别:
- 传统的主从是基于二进制日志文件的复制,必须配置唯一的服务器id,并且服务器的id主必须大于从,否则主设备将拒绝来自从设备的任何连接
- 传统的主从复制从端，binlog是不用开启的，但是在GTID中从端的binlog是必须开启的，目的是记录执行过的GTID
- GTID不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标,并且不在使用binlog+pos开启复制.而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制.
GTID的工作原理：
-
master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
-
slave端的i/o线程将变更的binlog，写入到本地的relay log中。
-
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
-
如果有记录，说明该GTID的事务已经执行，slave会忽略。
-
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
-
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
一主一从
需求
- 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
| 数据库角色 | IP地址 | 应用与系统版本 |
|---|---|---|
| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 从：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
安装MySQL
master：
/推荐下载MySQL5.7.34的版本 [root@master ~]# cd /usr/src [root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz ... //解压软件至/usr/local目录下，目录的位置可以是其他的地方 [root@master ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@master ~]# ls /usr/local/ bin games lib libexec sbin src etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share //创建用户和组 [root@master ~]# useradd -r -M -s /sbin/nologin mysql //创建软连接 [root@master ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql [root@master local]# ll -d /usr/local/mysql* lrwxrwxrwx. 1 root root 46 8月 30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x. 9 root root 129 8月 30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 //修改修改目录/usr/local/mysql的属主属组 [root@master ~]# chown -R mysql.mysql /usr/local/mysql* [root@master ~]# ll -d /usr/local/mysql* lrwxrwxrwx. 1 mysql mysql 46 8月 30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x. 9 mysql mysql 129 8月 30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 //添加环境变量 [root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@master ~]# source /etc/profile.d/mysql.sh [root@master ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin //建立数据存放目录 [root@master ~]# mkdir -p /opt/data/ [root@master ~]# chown -R mysql.mysql /opt/data/ [root@master ~]# ll /opt/data 总用量 0 //初始化数据库（不要密码） [root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql 2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-31T06:33:24.529250Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 57d51369-0a25-11ec-8346-000c2931864c. 2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. //安装依赖包 [root@master ~]# yum -y install ncurses-compat-libs ... //生成配置文件 [root@master data]# vim /etc/my.cnf #如果这个文件存在，请先备份再修改 [root@master data]# cat /etc/my.cnf [mysqld] port = 3306 datadir = /opt/data/ basedir = /usr/local/mysql socket = /tmp/mysql.sock pid-file = /opt/data/mysql.pid skip-name-resolve #跳过名称解析 //配置服务启动脚本 [root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@master ~]# vim /etc/init.d/mysqld #修改文件中的这两个地方 basedir=/usr/local/mysql datadir=/opt/data //启动mysql服务 [root@master ~]# service mysqld start Starting MySQL. SUCCESS! [root@master ~]# ss -antl|grep 3306 LISTEN 0 80 *:3306 *:* //设置新密码 [root@master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.34 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> set password = password('redhat123!'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
slave：
//在master将mysql-5.7包传输到slave上 [root@master ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.129.135:/usr/src/ The authenticity of host '192.168.129.135 (192.168.129.135)' can't be established. ECDSA key fingerprint is SHA256:rp+LMeFjAIapu3y+MH6bQa8QZpl/XRlUd1PammVI9E0. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added '192.168.129.135' (ECDSA) to the list of known hosts. root@192.168.129.135's password: mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100% 635MB 102.6MB/s 00:06 //解压软件至/usr/local目录下，目录的位置可以是其他的地方 [root@slave ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@slave ~]# ls /usr/local/ apr bin games include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share apr-util etc httpd lib libexec sbin src //创建用户和组 [root@slave ~]# useradd -r -M -s /sbin/nologin mysql //创建软连接 [root@slave ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql [root@slave ~]# ll -d /usr/local/mysql* lrwxrwxrwx 1 root root 46 8月 30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x 9 root root 129 8月 30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 //修改修改目录/usr/local/mysql的属主属组 [root@slave ~]# chown -R mysql.mysql /usr/local/mysql* [root@slave ~]# ll -d /usr/local/mysql* lrwxrwxrwx 1 mysql mysql 46 8月 30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x 9 mysql mysql 129 8月 30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 //添加环境变量 [root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@slave ~]# source /etc/profile.d/mysql.sh [root@slave ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin //建立数据存放目录 [root@slave ~]# mkdir -p /opt/data/ [root@slave ~]# chown -R mysql.mysql /opt/data/ [root@slave ~]# ll /opt/data 总用量 0 //初始化数据库（不要密码） [root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql 2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-31T06:33:24.529250Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 57d51369-0a25-11ec-8346-000c2931864c. 2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. //安装依赖包 [root@master ~]# yum -y install ncurses-compat-libs ... //生成配置文件 [root@slave ~]# vim /etc/my.cnf [root@slave ~]# cat /etc/my.cnf [mysqld] port = 3306 datadir = /opt/data/ basedir = /usr/local/mysql socket = /tmp/mysql.sock pid-file = /opt/data/mysql.pid skip-name-resolve //配置服务启动脚本 [root@slave ~]# cp -a /usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld [root@slave ~]# vim /etc/init.d/mysqld basedir=/usr/local/mysql datadir=/opt/data //启动mysql服务 [root@slave ~]# service mysqld start Starting MySQL. SUCCESS! [root@slave ~]# ss -antl|grep 3306 LISTEN 0 80 *:3306 *:* //设置新密码 [root@master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.34 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> set password = password('redhat123!'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
关闭防火墙和SElinux
master：
[root@master ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config
[root@master ~]# setenforce 0
- 1
- 2
- 3
- 4
- 5
- 6
slave：
[root@slave ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config
[root@slave ~]# setenforce 0
- 1
- 2
- 3
- 4
- 5
- 6
配置主库文件
//在主数据库里创建一个同步账号授权给从数据库使用 mysql> grant replication slave on *.* to 'hhr'@'192.168.129.135' identified by 'hhr123!'; #无需提前创建用户，执行授权的时候会自动创建用户 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@master ~]# vim /etc/my.cnf server-id = 10 #主库的server_id必须小于从库 gtid-mode = on #开启gtid enforce-gtid-consistency = on #强制gtid一致性 log-bin= mysql_bin #开启二进制 binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志 skip-slave-start = 1 #跳过slave复制线程 //重启MySQL服务 [root@master ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! //检查gtid模式状态 mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
配置从库文件
[root@slave ~]# vim /etc/my.cnf server-id = 20 #从库的server_id必须大于主库 gtid-mode = on #开启gtid enforce-gtid-consistency = on #强制gtid一致性 log-bin = mysql_bin #开启二进制 binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志 skip-slave-start = 1 #跳过slave复制线程 //重启MySQL服务 [root@slave ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! //检查gtid模式状态 mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec) //配置并启动主从复制 mysql> change master to -> master_host='192.168.129.133', -> master_user='hhr', -> master_password='hhr123!', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) //查看从服务器状态 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.129.133 Master_User: hhr Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql_bin.000003 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: 574 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: 10 Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c Master_Info_File: /opt/data/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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
测试
//主服务器创建数据库 mysql> create database hhr; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hhr | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use hhr; Database changed mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint); Query OK, 0 rows affected (0.01 sec) mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15),('zhangsan',19),('lisi',25); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | tom | 20 | | 2 | jerry | 20 | | 3 | wnagermazi | 15 | | 4 | zhangsan | 19 | | 5 | lisi | 25 | +----+------------+------+ 5 rows in set (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000003 | 870 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) //查看从库中的数据是否同步成功 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hhr | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> select * from hhr.student; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | tom | 20 | | 2 | jerry | 20 | | 3 | wnagermazi | 15 | | 4 | zhangsan | 19 | | 5 | lisi | 25 | +----+------------+------+ 5 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000001 | 862 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
停止
//从库停止服务 mysql> stop slave; Query OK, 0 rows affected (0.00 sec) //主库 mysql> insert student (name,age) values('qq',20),('ww',20),('ee',15),('rr',19),('lisi',25); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | tom | 20 | | 2 | jerry | 20 | | 3 | wnagermazi | 15 | | 4 | zhangsan | 19 | | 5 | lisi | 25 | | 6 | qq | 20 | | 7 | ww | 20 | | 8 | ee | 15 | | 9 | rr | 19 | | 10 | lisi | 25 | +----+------------+------+ 10 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000003 | 1172 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-4 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) //从库开启服务 mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> select * from hhr.student; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | tom | 20 | | 2 | jerry | 20 | | 3 | wnagermazi | 15 | | 4 | zhangsan | 19 | | 5 | lisi | 25 | | 6 | qq | 20 | | 7 | ww | 20 | | 8 | ee | 15 | | 9 | rr | 19 | | 10 | lisi | 25 | +----+------------+------+ 10 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000001 | 1156 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-4 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
一主多从
需求
- 搭建三台MySQL服务器，一台作为主服务器，一台作为从服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
| 数据库角色 | IP地址 | 应用与系统版本 |
|---|---|---|
| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 从：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
| 从：slave2 | 192.168.129.250 | Redhat 7/ MySQL 5.7 |
安装MySQL
master
略
slave
//在slave将mysql-5.7包传输到slave2上 [root@slave ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.129.137:/usr/src/ The authenticity of host '192.168.129.137 (192.168.129.137)' can't be established. ECDSA key fingerprint is SHA256:u29tZ8NW9v2uYAf2bAK7FOZwaxtjsxa0NVjgDTcaNTg. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added '192.168.129.137' (ECDSA) to the list of known hosts. root@192.168.129.137's password: mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100% 635MB 134.5MB/s 00:04 [root@slave2 ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@slave2 ~]# ls /usr/local/ bin games lib libexec sbin src etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share [root@slave2 ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql [root@slave2 ~]# ll -d /usr/local/mysql* lrwxrwxrwx. 1 root root 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x. 9 root root 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 [root@slave2 ~]# chown -R mysql.mysql /usr/local/mysql* [root@slave2 ~]# ll -d /usr/local/mysql* lrwxrwxrwx. 1 mysql mysql 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 drwxr-xr-x. 9 mysql mysql 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 root@slave2 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@slave2 ~]# source /etc/profile.d/mysql.sh [root@slave2 ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@slave2 ~]# mkdir -p /opt/data/ [root@slave2 ~]# chown -R mysql.mysql /opt/data/ [root@slave2 ~]# ll /opt/data/ total 0 [root@slave2 ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql 2021-08-31T09:15:31.711025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-31T09:15:31.866033Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-31T09:15:31.889223Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-31T09:15:31.945429Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fdd2fc8c-0a3b-11ec-82ba-000c29209bda. 2021-08-31T09:15:31.947661Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-31T09:15:32.598937Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-31T09:15:32.652206Z 1 [Note] A temporary password is generated for root@localhost: Qq73AjyWw2%/ [root@slave2 ~]# echo 'Qq73AjyWw2%/' > pass //安装依赖包 [root@slave2 ~]# yum -y install ncurses-compat-libs //位置文件 [root@slave2 ~]# vim /etc/my.cnf [mysqld] port = 3306 datadir = /opt/data/ basedir = /usr/local/mysql socket = /tmp/mysql.sock pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve //配置服务启动脚本 [root@slave2 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@slave2 ~]# vim /etc/init.d/mysqld basedir=/usr/local/mysql datadir=/opt/data //启动mysql服务 [root@slave2 ~]# service mysqld start Starting MySQL. SUCCESS! [root@slave2 ~]# ss -antl|grep 3306 LISTEN 0 80 *:3306 *:* //设置新密码 [root@slave2 ~]# mysql -uroot -p'Qq73AjyWw2%/' mysql> set password = password('redhat123!'); Query OK, 0 rows affected, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
关闭防火墙和selinux
master、slave
略
配置主库文件
略
配置从库文件
[root@slave2 ~]# vim /etc/my.cnf server-id = 21 #从库的server_id必须大于主库 gtid-mode = on #开启gtid enforce-gtid-consistency = on #强制gtid一致性 log-bin = mysql_bin #开启二进制 binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志 skip-slave-start = 1 #跳过slave复制线程 //重启MySQL服务 [root@slave2 ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! //检查gtid模式状态 mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.01 sec) //配置并启动主从复制 mysql> change master to -> master_host='192.168.129.133', -> master_user='hhr', -> master_password='hhr123!', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) //查看从服务器状态 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.129.133 Master_User: hhr Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000003 Read_Master_Log_Pos: 1618 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 1831 Relay_Master_Log_File: mysql_bin.000003 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: 1618 Relay_Log_Space: 2039 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: 10 Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c Master_Info_File: /opt/data/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: 57d51369-0a25-11ec-8346-000c2931864c:1-6 Executed_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
测试
//从库查看 mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000001 | 1602 | | | 57d51369-0a25-11ec-8346-000c2931864c:1-6 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hhr | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use hhr; 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 student; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | tom | 20 | | 2 | jerry | 20 | | 3 | wnagermazi | 15 | | 4 | zhangsan | 19 | | 5 | lisi | 25 | | 6 | qq | 20 | | 7 | ww | 20 | | 8 | ee | 15 | | 9 | rr | 19 | | 10 | lisi | 25 | +----+------------+------+ 10 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
多主一从
需求
- 搭建三台MySQL服务器，两台作为主服务器，一台作为从服务器，从服务器进行读操作
| 数据库角色 | IP地址 | 应用与系统版本 |
|---|---|---|
| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |
| 主：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |
| 从：slave2 | 192.168.129.250 | Redhat 7/ MySQL 5.7 |
主库设置
//主库1设置： mysql> grant replication slave on *.* to 'hhr'@'192.168.129.250'identified by 'hhr123!'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) //主库2设置： mysql> grant replication slave on *.* to 'hhr'@'192.168.129.250'identified by 'hhr123!'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql_bin.000003 | 194 | | | 51dda494-0a42-11ec-9fb3-000c299f4129:1-6 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 从库设置：
mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.01 sec) mysql> reset slave; Query OK, 0 rows affected (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 在从库上配置主库信息
//下面两条命令不执行，后续会报错 mysql> set global master_info_repository='table'; Query OK, 0 rows affected (0.00 sec) mysql> set global relay_log_info_repository='table'; Query OK, 0 rows affected (0.00 sec) mysql> change master to -> master_host='192.168.129.133', -> master_user='hhr', -> master_password='hhr123!', -> master_log_file='mysql_bin.000008', -> master_log_pos=194 -> for channel 'master-1'; Query OK, 0 rows affected, 2 warnings (0.00 sec) //配置主库2 mysql> change master to -> master_host='192.168.129.135', -> master_user='hhr', -> master_password='hhr123!', -> master_log_file='mysql_bin.000003', -> master_log_pos=194 -> for channel 'master-1'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 查看从库状态
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.129.133 Master_User: hhr Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: slave2-relay-bin.000004 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql_bin.000002 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: 788 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: 10 Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c 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 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: 51dda494-0a42-11ec-9fb3-000c299f4129:1-5 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: master-1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.129.135 Master_User: hhr Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000003 Read_Master_Log_Pos: 194 Relay_Log_File: slave2-relay-bin-master@002d1.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000003 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: 194 Relay_Log_Space: 541 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: 11 Master_UUID: 51dda494-0a42-11ec-9fb3-000c299f4129 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 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: 51dda494-0a42-11ec-9fb3-000c299f4129:1-5 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: master-2 Master_TLS_Version: 2 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
效果演示
//主库1插入数据： mysql> create database hhr; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hhr | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) //主库2插入数据 mysql> create database hang; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hang | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
查看从库变化
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hang |
| hhr |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/959331
推荐阅读
相关标签
