4.lamp-----MySQL主从

最新推荐文章于 2024-09-27 12:05:02 发布

♚北秋

最新推荐文章于 2024-09-27 12:05:02 发布

阅读量125

点赞数 1

分类专栏： lamp架构文章标签： linux

本文链接：https://blog.csdn.net/qq_44060147/article/details/118223627

版权

lamp架构专栏收录该内容

8 篇文章 1 订阅

订阅专栏

MySQL主从

一、在三台虚拟机分别装上数据库
二、一主一从（gtid模式复制）
二、一主多从
- 测试
三、半同步
四、并行复制
- server1
- server2

一、在三台虚拟机分别装上数据库

server1 主机端
已经装好了MySQL
server2 客户端
server3客户端
客户端直接复制主机数据库

 scp -r /usr/local/mysql/ server2:/usr/local/ 	#复制数据库文件
 scp /etc/my.cnf server2:/etc/	#复制配置文件
 scp /etc/init.d/mysqld  server2:/etc/init.d/	#复制启动文件
 
 scp -r /usr/local/mysql/ server3:/usr/local/ 	#复制数据库文件
 scp /etc/my.cnf server3:/etc/	#复制配置文件
 scp /etc/init.d/mysqld  server3:/etc/init.d/	#复制启动文件
添加环境变量
[root@sever2 ~]# vim .bash_profile 
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@sever2 ~]# source  .bash_profile 
[root@sever3 ~]# vim .bash_profile 
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@sever3 ~]# source  .bash_profile

新建用户server2和server3相同操作

[root@sever2 ~]# useradd -M -d /usr/local/mysql/data/ -s /sbin/nologin mysql
[root@sever2 ]#mkdir -p  /data/mysql/
[root@sever2 ]# cd /data/mysql/
[root@sever2 mysql]# ls
bin   docs     lib      man         README       share
data  include  LICENSE  mysql-test  README-test  support-files
[root@sever2 mysql]# rm -fr *
[root@sever2 mysql]# ls
[root@sever2 mysql]# mysqld --initialize --user=mysql
[root@sever2 mysql]# /etc/init.d/mysqld start
[root@sever2 mysql]# mysql_secure_installation
[root@sever2 mysql]# mysql -pwestos

在这里插入图片描述

二、一主一从（gtid模式复制）

Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是以全部事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。
配置

[root@server1 ~]# vim /etc/my.cnf
	gtid_mode=ON
	enforce-gtid-consistency=ON
[root@server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# mysql -p 
mysql> show master status;
[root@server2 mysql]# vim /etc/my.cnf
[root@server2 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server2 mysql]# mysql -pwestos
mysql> show slave status\G;
mysql> stop slave;
mysql> change master to master_host='172.25.15.1', master_user='repl', master_password='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;

在这里插入图片描述

二、一主多从

[root@server3 ~]# vim /etc/my.cnf
gtid_mode=ON  ##添加内容
enforce-gtid-consistency=ON
[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -pwestos
MySQL [(none)]> stop slave;
MySQL [(none)]> change master to master_host='172.25.15.2', master_user='repl', master_password='westos',
MASTER_AUTO_POSITION = 1;
MySQL [(none)]> start slave;

在这里插入图片描述

测试

##server1上在mysql数据库中添加内容，server2和server3上自动同步
[root@server1 ~]# mysql -p
mysql> insert into user_tb values ('user5','888');
mysql> insert into user_tb values ('user6','999');

[root@server3 ~]# mysql -p
MySQL [(none)]> select * from westos.user_tb;
MySQL [(none)]> show slave status\G;

在这里插入图片描述

三、半同步

传统的复制模式属于异步复制，主库只管发送二进制日志，而不关心从库是否收到，这种复制方式虽然能够保证复制的速度很快，但无法保证主从数据的一致性，这在生产环境中是不可取的。而半同步方式是指当主库发送二进制日志到从库中时，从库收到数据并存储到relay-log之后会发送一个ack确认给master，这样就保证了主从数据的一致性.

server1master上安装插件：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

每个副本上安装插件：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

[root@server1 ~]# mysql -p
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; ##设置最大超时时间，默认为10秒，我们可以不用更改
mysql> show variables like 'rpl%';

[root@server2 data]# mysql -pwestos
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> show status like 'rpl%';
mysql> STOP SLAVE IO_THREAD;  ##重启IO线程
mysql> START SLAVE IO_THREAD;
mysql> show status like 'rpl%';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
[root@server3 data]# mysql -pwestos
MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MySQL [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
MySQL [(none)]> STOP SLAVE IO_THREAD;
MySQL [(none)]> START SLAVE IO_THREAD;

在这里插入图片描述

[root@server1 ~]# mysql -p
mysql> insert into user_tb values ('user7','7777');
 
[root@server3 data]# mysql -pwestos
MySQL [(none)]> select * from westos.user_tb;
[root@server2 data]# mysql -pwestos
mysql> show status like 'rpl%';

测试

server2
mysql> STOP SLAVE IO_THREAD;

server1
mysql> insert into user_tb values ('user8','8888');
mysql> select * from westos.user_tb

server2
mysql> select * from westos.user_tb;
mysql> START SLAVE IO_THREAD;
mysql> show status like 'rpl%';
mysql> select * from westos.user_tb;

四、并行复制

server1

[root@server1 ~]# cd /data/mysql/
[root@server1 mysql]# ls
auto.cnf         ib_logfile0         public_key.pem
ca-key.pem       ib_logfile1         server1.err
ca.pem           mysql               server-cert.pem
client-cert.pem  mysql-bin.000001    server-key.pem
client-key.pem   mysql-bin.index     sys
ib_buffer_pool   performance_schema  westos
ibdata1          private_key.pem
[root@server1 mysql]# rm -rf *
[root@server1 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 

[root@server1 mysql]# vim /etc/my.cnf
[root@server1 mysql]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql

datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=mysql-bin
log-slave-updates
#Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

character-set-server = utf8
collation-server = utf8_general_ci
gtid_mode=ON
enforce-gtid-consistency=ON
# include all files from the config directory
#
!includedir /etc/my.cnf.d
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.15.1:33061"
group_replication_group_seeds= "172.25.15.1:33061,172.25.15.2:33061,172.25.15.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.15.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
[root@server1 mysql]# 

[root@server1 mysql]# mysqld --initialize --user=mysql
2021-06-29T09:00:11.138718Z 1 [Note] A temporary password is generated for root@localhost: O/DHp2eekMpd

[root@server1 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server1.err'.
 SUCCESS! 
[root@server1 mysql]# mysql -pO/DHp2eekMpd
....
mysql>alter user root@localhost identified by 'westos';		#修改密码
Query OK, 0 rows affected (0.00 sec)
mysql> create user rpl_user@'%' identified by 'westos';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> SET GLOBAL group_replication_bootstrap_group = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;

在这里插入图片描述

server2

[root@server2 mysql]# rm -rf *
[root@server2 mysql]# ls
[root@server2 mysql]# vim /etc/my.cnf
[root@server2 mysql]# mysqld --initialize --user=mysql

2021-06-29T08:56:00.440038Z 1 [Note] A temporary password is generated for root@localhost: qnPpq34B4g/n
[root@server2 mysql]# /etc/init.d/mysqld start
[root@server2 mysql]# mysql -pwestos
mysql> show slave status\G ;
mysql> show variables like 'slave%';
mysql> show variables like '%info%';

mysql> use mysql;
mysql> select * from slave_master_info;