MySQL安装及主从异步、半同步、同步实现

虚拟机安装 Linux

参考文章{{VMware 安装 Linux 虚拟机}}

Linux 初始化及安装 MySQL

下载 mysql

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

在虚拟机 vmware 上面创建 linux centos8 安装 mysql5.7 的 rpm 压缩包 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

    #安装git
    3  yum install git
    4  yum list | grep java
    #安装java 
    5  yum install -y java-11-openjdk.x86_64
    6  mysql
   # 安装rz 、sz 上传下载
   10  yum -y install lrzsz
   15  mv mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar software/mysql/
   #解压 tar包
   31  tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
   #查看是否有mariadb，有则移除
   33  rpm -qa | grep mariadb
   #rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
   #开始安装mysql 按照顺序
   34  rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
   35  rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
   36  rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
   37  #rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm --nodeps --force
   38  rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
   39  #rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm --nodeps --force
   #这一步应该放在最前面
   40  #dnf install ncurses-compat-libs
       #yum install net-tools -y 
   41  rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
   42  rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
   # mysql初始化，并新建一个用户
   44  mysqld --initialize --user=mysql
   #查看mysql的临时密码# (HkA05W=i-Fl
   45  cat /var/log/mysqld.log
   #设置开启自启动
   46  systemctl start mysqld.service
   #查看开启自启动
   47  systemctl status mysqld.service
   #登陆mysql
   48  mysql -uroot -p
   # 在mysql命令端设置密码 set password = password('root')
   # mysql data信息位置 /var/lib/mysql
   # 关闭防火墙7之前
   52  systemctl stop iptables 
   # 关闭防火墙7之后
   53  systemctl stop firewalld
   # 虚拟机设置防火墙不开机自启动
   55  systemctl disable firewalld.service

MySQL 主从复制

基于主库的 binlog 进行异步复制到 从库 relaylog，从库执行

mysql 主库设置

修改 my.cnf

vim /etc/my.cnf

添加下面的内容

log_bin=mysql-bin
server-id=1
sync-binlog=1
#指定哪些库不同步，其他库默认都同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#指定哪些库同步
#binlog-do-db=lanebin

重启 mysql

systemctl restart mysqld

授权主库的 root 权限

#进入mysql
grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%' identified by 'root';
flush privileges;
grant replication slave on *.* to 'mha'@'%' identified by '123123';
grant all privileges on *.* to 'mha'@'%' identified by '123123';
flush privileges;

查看下主库的状态

mysql 从库设置

my.cnf 修改

# log_bin可以不开
# log_bin=mysql-bin
# 指定serverid
server-id=2
#指定中继日志名字，不指定默认
relay_log =mysql-relay-bin
#指定只读
read_only =1

重启 mysql

systemctl restart mysqld

进入 mysql 设置复制主库

#查看是否以前有配置信息，若有则去除掉
show slave status;
#设置主库信息
change master to master_host='172.16.94.5',master_port=3306,master_user='root',master_password='root',
master_log_file='mysql-bin.000001',master_log_pos=869;
#开启从库
start slave;
#再次查看从库配置信息
show slave status;

具体查看下配置信息

发现出现错误，主从数据库的 uuid 必须不一样，因为是通过虚拟机进行复制的，所有 uuid 一致，必须修改才行

查找下 auto.cnf 文件的位置

find / -name "auto.cnf"
./var/lib/mysql/auto.cnf

发现主库和从库都是

[auto]
server-uuid=356fc1ff-e339-11eb-bbea-000c29c18362

可以自己删除 auto.cnf，mysql 重启会自动生成，也可以直接修改后重启

重启之后再次查看从库信息正常了

验证主从复制效果

主库操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database lanebin;
Query OK, 1 row affected (0.01 sec)
mysql> use lanebin;
Database changed
mysql> create table dept (
    -> id int primary key auto_increment,
    -> name varchar(200) not null comment '名字',
    -> )engine =innodb charset=utf8;
uery OK, 0 rows affected (0.01 sec)
mysql> insert into dept values (1,'java');
Query OK, 1 row affected (0.01 sec)
mysql>

从库信息

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lanebin            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use lanebin
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 | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)
mysql>

MySQL 半同步复制

mysql 主从复制存在的问题：

主库宕机后，数据可能丢失

从库只有一个 SQL Thread，主库写压力大，复制很可能延时

解决方法：

半同步复制—-解决数据丢失的问题

实现原理：从库写入完成之后发送 ack 给主库，主库 commit;

并行复制——解决从库复制延迟的问题

实现原理：不同事务进行分组，互不影响的并行执行

主库设置

进入 mysql

#查看是否支持插件安装
mysql> select @@have_dynamic_loading;
#查看插件
mysql> show plugins;
#安装插件semi master
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
#查看semi是否开启
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)
#开启semi插件
mysql> set global rpl_semi_sync_master_enabled =1;
mysql> set global rpl_semi_sync_master_timeout= 1000;

从库设置

#安装插件semi slave 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
#查看semi是否开启
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.00 sec)
mysql> set global rpl_semi_sync_slave_enabled =1;
#重启slave库 
mysql> stop slave;
mysql> start slave;

查看是否半同步

#主库
insert into dept values(2,'h5');
#从库
mysql> select * from dept;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | h5   |
+----+------+c

查看日志信息

cd /var/log
cat mysqld.log

MySQL 同步复制

主库设置

进入 mysql

mysql> show variables like '%binlog_group%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+
# 设置延时时间
mysql> set global  binlog_group_commit_sync_delay=1000;
# 设置事务数
mysql> set global binlog_group_commit_sync_no_delay_count =100;

从库设置

进入 mysql

mysql> show variables like '%slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+
mysql> set global slave_parallel_type='LOGICAL_CLOCK';
ERROR 3017 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first
mysql> stop slave;
mysql> set global slave_parallel_type='LOGICAL_CLOCK';
#设置sql线程数建议4～8
mysql> set global slave_parallel_workers  =8;

修改 relay log 信息

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 | FILE                                 |
| relay_log_purge           | ON                                   |
| relay_log_recovery        | OFF                                  |
| relay_log_space_limit     | 0                                    |
| sync_relay_log            | 10000                                |
| sync_relay_log_info       | 10000                                |
+---------------------------+--------------------------------------+
11 rows in set (0.00 sec)
mysql> set global relay_log_info_repository='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> set global relay_log_recovery =1;
ERROR 1238 (HY000): Variable 'relay_log_recovery' is a read only variable
mysql>

进入 /etc/my.cnf 下设置

vim /etc/my.cnf 
#添加
#同步复制
relay_log_recovery =1
#重启mysql
systemctl restart mysqld

再次进入 mysql 命令

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 | FILE                                 |
| relay_log_purge           | ON                                   |
| relay_log_recovery        | ON                                   |
| relay_log_space_limit     | 0                                    |
| sync_relay_log            | 10000                                |
| sync_relay_log_info       | 10000                                |
+---------------------------+--------------------------------------+
mysql> show variables like '%slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+

发现修改的内容又恢复开始的样子

这样就需要进入 /etc/my.cnf 下设置才保证不会重启修改消失

vim /etc/my.cnf 
#添加
#同步复制
slave_parallel_type =LOGICAL_CLOCK
slave_parallel_workers =8
master_info_repository =TABLE
relay_log_info_repository=TABLE
relay_log_recovery =1

再次重启 mysql

systemctl restart mysqld
mysql -uroot -proot

进入 mysql 查看信息，更改已经生效了

mysql> show variables like '%slave_parallel%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 8             |
+------------------------+---------------+
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                                |
+---------------------------+--------------------------------------+

测试同步复制

数据层面查看

#主添加信息
insert into dept values(3,'ui');
#从库查看信息
select  * from dept;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | h5   |
|  3 | ui   |
+----+------+

查看线程信息

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 |        27 | ON            | ANONYMOUS             |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |        28 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         3 |        29 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         4 |        30 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         5 |        31 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         6 |        32 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         7 |        34 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         8 |        36 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
8 rows in set (0.00 sec)