Linux企业运维##LAMP架构-mysql主从复制、半同步复制、组复制

一、mysql主从复制

server1为master，server2和server3为slave

server1：

(1)编辑mysql主配置文件，设置server-id

vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0
# # 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
#
# #default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

server-id=1
log-bin=mysql-bin

 (2)重启mysql数据库并查看主机状态

/etc/init.d/mysqld restart

mysql -p 输入密码                    ##登陆数据库

在mysql中
show master status;

 (3)创建mysql用户并授权

CREATE USER 'repl'@'%' IDENTIFIED BY 'westos' #创建用户repl可以通过任何方式登陆，密码为westos
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #授权

server2:

(1)将mysql添加到环境变量中

vim ~/.bash_profile

source ~/.bash_profile      ##重新读取文件

 (2)创建用户mysql和数据目录/data/mysql，并修改/data/mysql的权限

chmod 750 /data/mysql

(3)编辑mysql配置文件

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 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

(3)初始化mysql

mysqld -initialize --user=mysql

得到临时密码

方便启动mysql

cp /usr/local/mysql/support-files/mysql.server	/etc/init.d/mysqld
/etc/init.d/mysqld start                 #开启mysql

使用临时密码安全初始化mysql

mysql_secure_installation

(4)添加server id

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0
# # 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
#
# #default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

server-id=2

(5)重启mysql

/etc/init.d/mysqld restart

(6)以repl用户身份登陆server1中的mysql，测试

mysql -h 172.25.9.1 -u repl -p

 server1：

(1)创建库表并插入数据

create database westos;		         #创建库

mysql> create table westos.user(         #创建表
    -> username varchar(10));

INSERT INTO westos.user VALUES('user1'); #插入数据

select * from user;	                 #查看数据

(2)将主机被操作的库备份并发送给server2

mysqldump -p westos > dbdump.db 
scp dbdump.db server2:~

server2：

(1)将备份文件导入数据库

mysql -pwestos westos < dbdump.db

(2)进入mysql设置master

CHANGE MASTER TO MASTER_HOST='172.25.9.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

(2)开启slave并查看slave状态

start slave;
show slave status\G;

 slave_IO_Running和Slave_SQL_Running都是yes即代表配置成功

 二、Gtid实现主从复制

server1：

(1)修改配置文件，打开gtid模式并重启

vim /etc/my.cnf

/etc/init.d/mysqld restart 

server2:

(1)修改配置文件，打开gtid模式并重启

/etc/init.d/mysqld restart

 (2)关闭slvae，配置参数，再开启slave，查看slave状态

stop slave;
CHANGE MASTER TO MASTER_HOST = '172.25.9.1', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;

 三、mysql半同步复制

server1：

(1)安装master模块

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  #安装master模块
SELECT PLUGIN_NAME, PLUGIN_STATUS       FROM INFORMATION_SCHEMA.PLUGINS
     WHERE PLUGIN_NAME LIKE '%semi%';			#查询模块状态

(2)启动master模块

SET GLOBAL rpl_semi_sync_master_enabled =1;  #启动模块
show variables like 'rpl%';		#查看复制

等待最长响应时间为10s(10000ms)

server2：

(1)安装半同步复制模块

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  
SELECT PLUGIN_NAME, PLUGIN_STATUS       FROM INFORMATION_SCHEMA.PLUGINS
     WHERE PLUGIN_NAME LIKE '%semi%';

(2)启动模块

SET GLOBAL rpl_semi_sync_slave_enabled =1; #启动模块

(3)开启slave

START SLAVE IO_THREAD;

(4)查看slave状态

show status like 'rpl%'   查看状态

 Value为ON即表示成功

四、组复制

先将server1，server2，server3中的mysql全部进行初始化

server1：

(1)修改配置文件并重启mysql

vim /etc/my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
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.4.3:33061"
group_replication_group_seeds= "172.25.4.1:33061,172.25.4.2:33061,172.25.4.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.4.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

/etc/init.d/mysqld restart

(2)安装组复制模块并查看

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

 (3)关闭二进制文件写入，创建用户并且授权，完成后打开二进制文件写入

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
SET SQL_LOG_BIN=1;

(4)设置master并刷新

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' 
	FOR CHANNEL 'group_replication_recovery';

FLUSH PRIVILEGES;

(5)设置相关参数

SET GLOBAL group_replication_bootstrap_group=ON;	
START GROUP_REPLICATION;				
SET GLOBAL group_replication_bootstrap_group=OFF;	

(6)查看模块状态，只有状态为ONLINE才能实现组复制

SELECT * FROM performance_schema.replication_group_members; # 查看

server2，server3：

(1)关闭mysql

/etc/init.d/mysqld stop

(2)修改mysql配置文件

#default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
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.9.2:33061"
group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

注意server3中 server-id=3     local_address="172.25.9.3"

然后初始化mysql

(3)开启mysql并设置相关参数，设置master

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';     ##授权
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' 
	FOR CHANNEL 'group_replication_recovery';

(4)开启组复制模块

START GROUP_REPLICATION;

server1：

(1)查看

SELECT * FROM performance_schema.replication_group_members;