1.MySQL主从复制
1.1 安装mysql
1.11 第一步：卸载旧版本
- 检查是否有旧版本：
rpm -qa|grep -i mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gSrcUPby-1659534371633)(E:\study_data\学习笔记\images\20200117151032830.png)]
- 依次卸载：
rpm -e –nodeps 包名
#如果提示错误，尝试用下列命令执行
rpm -ev 包名 --nodeps
rpm -e --noscripts 包名
- 查找之前老版本mysql的目录、并且删除老版本mysql的文件和库
# 查询
find / -name mysql
# 显示下列结果
/var/lib/mysql
/var/lib/mysql/mysql
/usr/lib64/mysql
# 依次删除
rm -rf /var/lib/mysql
rm -rf /var/lib/mysql/mysql
rm -rf /usr/lib64/mysql
1.1.2 卸载默认带的数据库
如果是新机，const7会自带mariadb。如果不卸载，安装MySQL的时候可能会产生冲突。
[root@k8s-node1 mysql]# rpm -qa|grep mariadb
mariadb-libs-5.5.65-1.el7.x86_64
[root@k8s-node1 mysql]# rpm -e mariadb-libs-5.5.65-1.el7.x86_64 --nodeps
[root@k8s-node1 mysql]# rpm -qa|grep mariadb
[root@k8s-node1 mysql]#
1.1.3 安装MySQL
MySQL官网下载rpm安装包：https://dev.mysql.com/downloads/mysql/5.7.html#downloads
- 将安装包上传到服务器，解压
[root@k8s-node1 mysql]# ls
mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar
[root@k8s-node1 mysql]# tar -xvf mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.19-1.el7.x86_64.rpm
mysql-community-client-5.7.19-1.el7.x86_64.rpm
mysql-community-server-5.7.19-1.el7.x86_64.rpm
mysql-community-test-5.7.19-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.19-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.19-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.19-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.19-1.el7.x86_64.rpm
mysql-community-common-5.7.19-1.el7.x86_64.rpm
mysql-community-embedded-5.7.19-1.el7.x86_64.rpm
mysql-community-devel-5.7.19-1.el7.x86_64.rpm
mysql-community-libs-5.7.19-1.el7.x86_64.rpm
[root@k8s-node1 mysql]#
执行安装：【注意】这些组件是有依赖关系的，所以安装顺序不能错。
1）首先安装common
[root@k8s-node1 mysql]# rpm -ivh mysql-community-common-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-5.7.19-1.e################################# [100%]
[root@k8s-node1 mysql]#
2）安装libs
[root@k8s-node1 mysql]# rpm -ivh mysql-community-libs-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-5.7.19-1.el7################################# [100%]
[root@k8s-node1 mysql]#
3）安装libs-compat
[root@k8s-node1 mysql]# rpm -ivh mysql-community-libs-compat-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-compat-5.7.1################################# [100%]
[root@k8s-node1 mysql]#
4）安装client
[root@k8s-node1 mysql]# rpm -ivh mysql-community-client-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-client-5.7.19-1.e################################# [100%]
5）安装server
[root@k8s-node1 mysql]# rpm -ivh mysql-community-server-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-server-5.7.19-1.e################################# [100%]
[root@k8s-node1 mysql]#
【报错解决】如果出现下面的报错，说明缺少依赖
[root@k8s-node1 mysql]# rpm -ivh mysql-community-server-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
/usr/bin/perl is needed by mysql-community-server-5.7.19-1.el7.x86_64
net-tools is needed by mysql-community-server-5.7.19-1.el7.x86_64
perl(Getopt::Long) is needed by mysql-community-server-5.7.19-1.el7.x86_64
perl(strict) is needed by mysql-community-server-5.7.19-1.el7.x86_64
[root@k8s-node1 mysql]#
- 下载perl依赖
[root@k8s-node1 mysql]# yum install -y perl-Module-Install.noarch
[root@k8s-node1 mysql]# yum install -y perl
- 下载net-tools依赖
[root@k8s-node1 mysql]# yum list|grep -i net-tools
net-tools.x86_64 2.0-0.25.20131004git.el7 base
[root@k8s-node1 mysql]# yum install net-tools.x86_64
6）安装开发工具devel
[root@k8s-node1 mysql]# rpm -ivh mysql-community-devel-5.7.19-1.el7.x86_64.rpm
warning: mysql-community-devel-5.7.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-devel-5.7.19-1.el################################# [100%]
[root@k8s-node1 mysql]#
7）初始化数据库实例
[root@k8s-node1 mysql]# mysqld --initialize --user=mysql
[root@k8s-node1 mysql]#
8）查看初始化用户和密码
[root@k8s-node1 mysql]# cat /var/log/mysqld.log
2022-05-12T14:23:26.343238Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-05-12T14:23:26.530354Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-05-12T14:23:26.559102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-05-12T14:23:26.614455Z 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: 168244e5-d1ff-11ec-a3b3-0800276666fb.
2022-05-12T14:23:26.615543Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-05-12T14:23:26.616052Z 1 [Note] A temporary password is generated for root@localhost: EX5Yc/b4Camj
[root@k8s-node1 mysql]#
9）启动MySQL服务
[root@k8s-node1 mysql]# systemctl start mysqld.service # 启动MySQL服务
[root@k8s-node1 mysql]# systemctl enable mysqld.service # 设置开机自启动
[root@k8s-node1 mysql]# systemctl status mysqld.service # 查看启动状态
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2022-05-12 14:25:51 UTC; 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 8101 (mysqld)
Tasks: 27
Memory: 203.4M
CGroup: /system.slice/mysqld.service
└─8101 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
May 12 14:25:50 k8s-node1 systemd[1]: Starting MySQL Server...
May 12 14:25:51 k8s-node1 systemd[1]: Started MySQL Server.
[root@k8s-node1 mysql]#
10）登录
[root@k8s-node1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19
Copyright (c) 2000, 2017, 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>
密码就是自动生成的： root@localhost: EX5Yc/b4Camj。
11）修改密码
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
1.2 系统设置
1.2.1 关闭防火墙
- 关闭iptables
[root@k8s-node2 mysql]# systemctl stop iptables
Failed to stop iptables.service: Unit iptables.service not loaded. # 这是因为没有安装所造成的
[root@k8s-node2 mysql]#
- 关闭firewalld
[root@k8s-node2 mysql]# systemctl stop firewalld
[root@k8s-node2 mysql]# systemctl disable firewalld.service
[root@k8s-node2 mysql]#
1.3 主从复制（异步复制方式，也是默认的方式）
1.3.1 主库配置
1）修改配置文件my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql-bin # 定义binlong文件，开启binlog
server-id=1 # 配置server-id
sync-binlog=100
binlog-ignore-db=performance_schema # 排除不同步的库
binlog-ignore-db=information_schema
binlog-ignore-db=sys
# binlog-do-db=study # 要同步的库，不配就默认同步
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
网上找的配置文件说明，这里没有用
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysql]
[mysqld]
#设置端口
port = 3306
#数据存储路径
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#错误日志路径
log-error=/var/log/mysqld.log
#日志路径
pid-file=/var/run/mysqld/mysqld.pid
disable-partition-engine-check=1
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
##数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server=utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names=1
#最大连接数
max_connections=400
##最大错误连接数
max_connect_errors=1000
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet=128M
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout=1800
wait_timeout=1800
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days=5
############################主从复制 设置########################################
#开启mysql binlog功能
#log-bin=mysql-bin
#binlog记录内容的方式，记录被操作的每一行
#binlog_format = ROW
##作为从库时生效,想进行级联复制，则需要此参数
#log_slave_updates
#作为从库时生效,中继日志relay-log可以自我修复
#relay_log_recovery = 1
2）修改完配置文件之后重启服务
[root@k8s-node1 mysql]# vi /etc/my.cnf
[root@k8s-node1 mysql]# systemctl restart mysqld
[root@k8s-node1 mysql]#
3）给用户授权
[root@k8s-node1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql>
查看授权后的状态；
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 | 869 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
1.3.2 从库配置
1）修改/etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
server-id=2 # 配置server-id
relay_log=mysql-relay-bin # 中继日志名称
read_only=1 # 只读（可选）
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
重启mysql服务：
[root@k8s-node2 mysql]# systemctl restart mysqld;
[root@k8s-node2 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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; # 检查状态
Empty set (0.00 sec)
mysql>
2）执行命令（注意这里的ip，端口，用户名，密码，一定要正确）
mysql> change master to master_host='10.0.2.5',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
master_log_file：master节点bin-log名称
master_log_pos：master节点bin-lon的pos值
3）开启从库
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: 10.0.2.5
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2560
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 482
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: 2560
Relay_Log_Space: 689
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: f1f4154b-d207-11ec-9998-0800276666fb
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> show databases;
4）验证结果
主库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaaa |
| lagou |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database hello;
Query OK, 1 row affected (0.00 sec)
mysql> use hello;
Database changed
mysql> create table dept(id int primary key, dname varchar(20))engine=innodb charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql>
从库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello |
| lagou |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use lagou;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> use hello;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| dept |
+-----------------+
1 row in set (0.00 sec)
mysql>
【查错】：error connecting to master ‘root@192.168.56.100:3306’ - retry-time: 60 retries: 1
#因为这里连接的host和密码错了
mysql> change master to master_host='10.0.2.5',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
【关闭从库】
mysql> stop slave;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave stop' at line 1
mysql> change master to master_host=' ';
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host:
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
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: 0
Relay_Log_Space: 154
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: 1045
Last_IO_Error: error connecting to master 'root@10.0.2.5:3306' - retry-time: 60 retries: 3
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 220516 12:11:27
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> reset slave all; # 清除所有从库信息
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql>
【添加从库】如果已经运行一段时间，有一定数据量了，需要添加从库，则可以先将主库的数据通过mysqldump命令备份，导入到从库中，再开始做主从同步。
这样做的好处：是不用从一开始的数据复制，增加主库负担。
1.3.3 mysqldump命令使用
[root@k8s-node1 ~]# ls
anaconda-ks.cfg k8s mysql original-ks.cfg
# 导出所有的数据库到mysql_backup_all.sql文件
[root@k8s-node1 ~]# mysqldump --help --all-databases > mysql_backup_all.sql
[root@k8s-node1 ~]# ls
anaconda-ks.cfg k8s mysql mysql_backup_all.sql original-ks.cfg
[root@k8s-node1 ~]#
1.4 主从复制（半同步复制）
半同步复制需要借助插件（semi）执行，所以在1.3主从复制的基础上还需要安装semi插件。
1.4.1 主库配置
1）查看是否有安装插件semi
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)
mysql>
2）安装插件semi：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
mysql>
3）开启semi插件
mysql> set global rpl_semi_sync_master_enabled=1; # 开启
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=1000; # 设置时间为1秒
Query OK, 0 rows affected (0.00 sec)
mysql>
1.4.2 从库配置
1）安装semi插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
mysql>
2）开启semi功能
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
3）重启从库
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
4）验证
查看mysql日志：cat /var/log/mysqld.log
...
2022-05-16T14:34:12.310249Z 11 [Note] Semi-sync replication initialized for transactions.
2022-05-16T14:34:12.310269Z 11 [Note] Semi-sync replication enabled on the master.
2022-05-16T14:34:12.310352Z 0 [Note] Starting ack receiver thread
2022-05-16T14:39:23.275187Z 12 [Note] While initializing dump thread for slave with UUID <85feaaa1-d201-11ec-a217-080027e6f410>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(9).
2022-05-16T14:39:23.275259Z 9 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)
2022-05-16T14:39:23.275276Z 12 [Note] Start binlog_dump to master_thread_id(12) slave_server(2), pos(mysql-bin.000002, 3210)
2022-05-16T14:39:23.275290Z 12 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000002, 3210)
[root@k8s-node1 log]#
有semi信息，说明半同步复制起作用了。
1.5 主从复制（并行复制）
1.5.1 主库配置
1）设置参数
mysql> show variables like '%binlog_group%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
mysql> set global binlog_group_commit_sync_delay=1000; # 设置延迟时间
Query OK, 0 rows affected (0.00 sec)
mysql> set global binlog_group_commit_sync_no_delay_count=100; # 设置组内事务数
Query OK, 0 rows affected (0.00 sec)
mysql>
1.5.2 从库配置
1）设置slave_parallel的参数，修改my.cnf配置文件
mysql> show variables like '%slave%';
+---------------------------------+-----------------------+
| Variable_name | Value |
+---------------------------------+-----------------------+
| init_slave | |
| log_slave_updates | OFF |
| log_slow_slave_statements | OFF |
| pseudo_slave_mode | OFF |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+---------------------------------+-----------------------+
25 rows in set (0.00 sec)
[root@k8s-node2 bin]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
server-id=2
relay_log=mysql-relay-bin
read_only=1
relay_log_recovery=1
slave_parallel_type='LOGICAL_CLOCK'
slave_parallel_workers=8
master_info_repository='TABLE'
relay_log_info_repository='TABLE'
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2）查看结果
mysql> show variables like '%relay_log%';
+---------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------+
| max_relay_log_size | 0 |
| relay_log | mysql-relay-bin |
| relay_log_basename | /var/lib/mysql/mysql-relay-bin |
| relay_log_index | /var/lib/mysql/mysql-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | ON |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+---------------------------+--------------------------------------+
11 rows in set (0.01 sec)
mysql> show variables like '%slave%';
+---------------------------------+-----------------------+
| Variable_name | Value |
+---------------------------------+-----------------------+
| init_slave | |
| log_slave_updates | OFF |
| log_slow_slave_statements | OFF |
| pseudo_slave_mode | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 8 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+---------------------------------+-----------------------+
25 rows in set (0.00 sec)
mysql>
3）验证信息
mysql> use hello;
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 dept;
+----+-------+
| id | dname |
+----+-------+
| 1 | jave |
| 2 | h5 |
| 3 | ui |
+----+-------+
3 rows in set (0.00 sec)
mysql> use performance_schema;
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 replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
| | 1 | 28 | ON | ANONYMOUS | 0 | | 0000-00-00 00:00:00 |
| | 2 | 31 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 3 | 32 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 4 | 33 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 5 | 34 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 6 | 35 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 7 | 36 | ON | | 0 | | 0000-00-00 00:00:00 |
| | 8 | 37 | ON | | 0 | | 0000-00-00 00:00:00 |
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
8 rows in set (0.00 sec)
mysql>
2.读写分离
这里使用的是MySQL官方提供的中间件MySQL Proxy
下载MySQL Proxy，官方不建议使用，这里就不记录了。
3. 双主模式
新加节点master2，安装MySQL。
3.1 修改master1节点
1）修改my.cnf配置文件
[root@k8s-node1 mysql]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql-bin
server-id=1
sync-binlog=100
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
relay_log=mysql-relay-bin # 开启relay日志
log_slave_updates=1 # 从库更新
auto_increment_offset=1 # 自增id从1开始
auto_increment_increment=2 #每次增量为2
# binlog-do-db=study
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
2）重启master1节点：
[root@k8s-node1 mysql]# systemctl restart mysqld
3）授权，参照主从复制授权步骤
3.2 修改master2节点
1）修改my.cnf配置文件
root@k8s-node3 mysql]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql-bin
server-id=3
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
relay-log=mysql-relay-bin
log-slave-updates=1
auto_increment_offset=2
auto_increment_increment=2
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2）重启MySQL服务
[root@k8s-node3 mysql]# systemctl restart mysqld
[root@k8s-node3 mysql]#
3）授权
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
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.000001 | 884 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
3.3 master1、master2互相指定
master1的IP：10.0.2.5
master2的IP：10.0.2.15
3.3.1 在master1节点执行
这里的信息是master2节点的信息
mysql> change master to master_host='10.0.2.15',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=884;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
启动slave：
mysql> change master to master_host='10.0.2.15',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=884;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql>
mysql>
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.2.15
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 884
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 1097
Relay_Master_Log_File: mysql-bin.000001
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: 884
Relay_Log_Space: 1304
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: 3
Master_UUID: babd96da-d6b3-11ec-9fef-080027303257
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>
3.3.2 在master2节点执行
mysql> change master to master_host='10.0.2.5',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1614;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
启动slave：
mysql> change master to master_host='10.0.2.5',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1614;
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: 10.0.2.5
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1614
Relay_Log_File: mysql-relay-bin.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: 1614
Relay_Log_Space: 527
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: f1f4154b-d207-11ec-9998-0800276666fb
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>
至此，双主配置完成。
1.5.3 测试
master1节点操作
mysql> create database mymaster1;
Query OK, 1 row affected (0.00 sec)
mysql> use mymaster1;
Database changed
mysql> create table test1(id int primary key atuo_increment,name varchar(20))engine=innodb charset=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'atuo_increment,name varchar(20))engine=innodb charset=utf8' at line 1
mysql> create table test1(id int primary key auto_increment,name varchar(20))engine=innodb charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1(name) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | 1 |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into test1(name) values ('b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | 1 |
| 3 | b |
| 4 | 2 |
+----+------+
4 rows in set (0.00 sec)
mysql>
2）master2操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mymaster1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mymaster1;
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_mymaster1 |
+---------------------+
| test1 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test1(name) values ('1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | 1 |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into test1(name) values ('2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | 1 |
| 3 | b |
| 4 | 2 |
+----+------+
4 rows in set (0.00 sec)
mysql>
4. MMM高可用架构
MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双 主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一 个节点进行写入操作。
5. 分库分表
MySQL单表控制在1000万以下。
垂直拆分和水平拆分。
分库分表方案：只分库，只分表，分库又分表
5.1 垂直分表
MySQL面试问题
1.索引数据结构红黑树，Hash，B+树详解
索引是帮助MySQL高效获取数据的排好序的数据结构。
索引数据结构：
- 二叉树
[外链图片转存中…(img-Ich67HnZ-1659534371635)]
- 红黑树
[外链图片转存中…(img-kuGbc6kU-1659534371635)]
- Hash表：少用，不支持范围查找
- B Tree
[外链图片转存中…(img-HjsYF1z3-1659534371636)]
- B+ 树
- 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问性能
[外链图片转存中…(img-9DQ1OVY5-1659534371636)]
每个索引页大小16kb
数据结构演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
2 存储引擎
参考文档：https://www.cnblogs.com/yxym2016/p/14766476.html
存储引擎是修饰数据库表的，不同表可以选择不同引擎，默认InnoDB
MyISAM引擎
MyISAM引擎的表有三个文件：
-
frm：存储表结构的文件
-
myd：存储数据的文件
-
myi：存储索引的文件
MyISAM索引文件和数据文件是分离的（非聚集索引）
MyISAM引擎索引查找：首先判断字段是否有索引，如果有则去MYI文件查找索引，折半查找，查找到叶子节点的data数据，data数据存储的是数据的内存地址，拿着这个内存地址再到MYD文件中查找数据（这一步也叫回表）。
[外链图片转存中…(img-DneFfWRq-1659534371637)]
InnoDB存储引擎
InnoDB引擎的表有两个文件：
- frm：存储表结构的文件
- ibd：存储数据和索引的文件
InnoDB索引和数据放在一个文件里面为聚集索引。
主键索引和非主键索引的区别：非主键索引中的叶子节点存放的数据是主键，主键索引中的叶子结点存放的数据是主键和数据。
主键索引：
[外链图片转存中…(img-YFAILuAl-1659534371637)]
非主键索引
[外链图片转存中…(img-aiMvn2iO-1659534371637)]
B树和B+树的区别
1）B树没有维护叶子节点之间的指针，不能很快的进行范围查找
2）B+树的所有数据都存放在叶子节点，而B树每个节点都存有数据。同样的数据量B树的树更高，层级更多，从而影响性能
扫一扫
301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
