Linux企业运维--Mysql的主从复制

************Mysql组复制**************

把server1、2、3 /data/mysql 下的全部删除重新编辑

 

server1:

vim /etc/my.cnf           ----修改配置文件
------------------------------------------------------------------
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="server1:33061"
group_replication_group_seeds="server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.14.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

 

------------------------------------------------------------------
mysqld --initialize --user=mysql   ---初始化
cat mysqld.log                  -----找日志里的密码
/etc/init.d/mysqld start

mysql -p                     ----用日志密码登陆
----------------------------------------------------------
mysql> alter user root@localhost identified by 'westos';  --更改密码
mysql>  SET SQL_LOG_BIN=0;  ---- 不记录在日志里，关闭日志
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; --新建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; ---授权
mysql>  FLUSH PRIVILEGES;    --刷新
mysql> SET SQL_LOG_BIN=1;  -----开启日志
mysql> CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';      
mysql>SET GLOBAL group_replication_bootstrap_group=ON;  ---建立组
mysql> START GROUP_REPLICATION;   ---打开组复制
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;  ---关闭组  
mysql>select * from performance_schema.replication_group_members;--查看组状态

------------------------------

 

--------------------------------

 

=======================================================================

server2：

vi /etc/my.cnf
------------------------------------------------------------------
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="server2:33061"
group_replication_group_seeds="server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.14.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=1;-- 比server1多加一个配置

------------------------------------------------------------------
mysqld --initialize --user=mysql   ---初始化
cat mysqld.log                  -----找日志里的密码
/etc/init.d/mysqld start

mysql -p                     ----用日志密码登陆
----------------------------------------------------------
mysql> alter user root@localhost identified by 'westos';
mysql>  SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql>  FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql>  START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql>select * from performance_schema.replication_group_members;

 

 

--------------------------------------------------------------

========================================================================

server3：
与server2操作一模一样，仅需把配置文件部分修改/etc/my.cnf：
server-id=3
group_replication_local_address="server3:33061"

在server1里添加数据，在server2、3查看检测

 

 

 

******************Mysql路由配置******************************

 

加一个虚拟机server4：
rpm包下载
rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
cd /etc/mysqlrouter/
vi mysqlrouter.conf   ------编辑配置文件

 

------------------------------------------------
[routing:ro]     -----只读
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.14.2:3306,172.25.14.3:3306
routing_strategy = round-robin
 
[routing:rw]    -----读写
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.14.1:3306,172.25.14.2:3306
routing_strategy = first-available

 

------------------------------------------------------
server1：
mysql> grant all on test.* to haha@'%' identified by 'westos';
mysql> flush privileges;

真机操作：
mysql -h 172.25.14.4 -P 7001 -u haha -p test
MySQL [test]> select * from t1;

 

server2上检查：
lsof -i ：3306

 

*************************MHA高可用*************************************

 

配置部分

下载rpm包
yum install -y *.rpm

 
 scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  server1:
 scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  server2:
 scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  server3:
在server1、2、3：
yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

返回server4：
tar zxf mha4mysql-manager-0.58.tar.gz
cd /mha4mysql-manager-0.58/samples/conf/
mkdir /etc/mha
cp app1.cnf masterha_default.cnf /etc/mha/
cd  /etc/mha/
vi app1.cnf ----修改mha下的app1配置文件
----------------------------------------------------------
      [server default]
      user=root
      password=westos
      ssh_user=root
      repl_user=repl
      repl_password=westos
      master_binlog_dir= /data/mysql
      remote_workdir=/tmp
      secondary_check_script= masterha_secondary_check -s server2 -s server3
      ping_interval=3
     # master_ip_failover_script= /script/masterha/master_ip_failover
      # shutdown_script= /script/masterha/power_manager
      # report_script= /script/masterha/send_report
      # master_ip_online_change_script= /script/masterha/master_ip_online_change      
     [server default]
     manager_workdir=/etc/mha/app1
     manager_log=/etc/mha/app1/manager.log    
     [server1]
     hostname=172.25.14.1
     candidate_master=1
     [server2]
     hostname=172.25.14.2
     candidate_master=1
     check_repl_delay=0
     [server3]
     hostname=172.25.14.3
     no_master=1

 

 

--------------------------------------------------------
rm -f masterha_default.cnf     将default删除

masterha_check_ssh  --conf=/etc/mha/app1.cnf  结果发现需要密钥

 

ssh-keygen

 

 ssh-copy-id server4   把密钥生成公钥
 把密钥直接发给server1、2、3
 scp -r .ssh/ server1:
 scp -r .ssh/ server2:
 scp -r .ssh/ server3:

到server1：
登陆数据库  
mysql> create  user root@'%' identified by 'westos';   --建立用户root
mysql> grant all on *.* to root@'%';  -- 授权
mysql> flush privileges;   --刷新

 

再到server4：
masterha_check_repl  --conf=/etc/mha/app1.cnf

 

 

MHA的故障切换过程，共包括以下的步骤：
 
1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
 
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作
 
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下
 
4.识别含有最新更新的slave
 
5.应用从master保存的二进制日志事件（binlog events）
 
6.提升一个slave为新的master进行复制
 
7.使其他的slave连接新的master进行复制

==================================================================
手动切换

server4:

masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=172.25.14.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000     主机切换到server2

no yes yes

 

 

---------------------------
关闭server2的mysql
masterha_master_switch --master_state=dead --conf=/etc/mha/app1.cnf --dead_master_host=172.25.14.2 --dead_master_port=3306 --new_master_host=172.25.14.1 --new_master_port=3306 --ignore_last_failover
yes yes

 

在server3的数据库：
show slave status\G;
看到
主机又是server1的ip

 

====================================================================