lamp(2)mysql主从策略

最新推荐文章于 2024-03-25 00:23:12 发布

qiao_qing

最新推荐文章于 2024-03-25 00:23:12 发布

阅读量276

点赞数

分类专栏： mysql及redis

本文链接：https://blog.csdn.net/qiao_qing/article/details/115563544

版权

mysql及redis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.mysql编译安装

[root@server1 ~]# ls
mysql-boost-5.7.31.tar.gz 
[root@server1 ~]# tar zxf mysql-boost-5.7.31.tar.gz
[root@server1 ~]# yum install cmake ncurses-devel  gcc-c++ bision -y 
[root@server1 mysql-5.7.31]# cd boost/boost_1_59_0/boost/
[root@server1 boost]# pwd
/root/mysql-5.7.31/boost/boost_1_59_0/boost
[root@server1 mysql-5.7.31]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_BOOST=/root/mysql-5.7.31/boost/boost_1_59_0/
####当cmake执行错误时，需要删除 rm -fr CMakeCache.txt，然后执行cmake
[root@server1 mysql-5.7.31]# make##时间久
[root@server1 mysql-5.7.31]# make install

config 参数

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \#安装目录
-DMYSQL_DATADIR=/usr/local/mysql/data \#数据库存放目录
-DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \ #Unix socket 文件路径
-DWITH_MYISAM_STORAGE_ENGINE=1 \#安装 myisam 存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \#安装 innodb 存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \#安装 archive 存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \#安装 blackhole 存储引擎
-DWITH_PARTITION_STORAGE_ENGINE=1 \#安装数据库分区
-DENABLED_LOCAL_INFILE=1 \#允许从本地导入数据
-DWITH_READLINE=1 \#快捷键功能
-DWITH_SSL=yes \#支持 SSL
-DDEFAULT_CHARSET=utf8 \#使用 utf8 字符
-DDEFAULT_COLLATION=utf8_general_ci \#校验字符
-DEXTRA_CHARSETS=all \#安装所有扩展字符集
-DMYSQL_TCP_PORT=3306 \#MySQL 监听端口

[root@server1 ~]# cd /usr/local/mysql/
[root@server1 mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files
docs  lib      man      README      share
[root@server1 mysql]# cd support-files/
[root@server1 support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@server1 support-files]# cp mysql.server /etc/init.d/mysqld##有X权限，#根据你的主机内存复制 mysql 配置文件
[root@server1 support-files]# find -name *my.cnf
[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

在这里插入图片描述

[root@server1 ~]# vim .bash_profile ##添加环境变量
PATH=$PATH:$HOME/bin:/usr/local/nginx/sbin:/usr/local/mysql/bin/
[root@server1 ~]# source .bash_profile #使之生效

在这里插入图片描述

[root@server1 ~]# useradd -u 1001 -M -d /usr/local/mysql/data -s /sbin/nologin mysql
[root@server1 ~]# cd /usr/local/mysql/
[root@server1 mysql]# mysqld --verbose --help |less
[root@server1 mysql]# mysqld --initialize --user=mysql####初始化
##临时密码
2021-03-28T07:59:47.949140Z 1 [Note] A temporary password is generated for root@localhost: #0Z5%G0Cl0q=
[root@server1 mysql]# /etc/init.d/mysqld start##只能start一次
[root@server1 mysql]# netstat -antlp|grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      13058/mysqld       

[root@server1 mysql]# mysql_secure_installation  #按提示完成mysql安全设置,生产环境推荐使用。临时密码；新密码；两个回车，一直y
Securing the MySQL server deployment.
Enter password for user root: #0Z5%G0Cl0q=
New password:westos

##临时密码；新密码；两个回车，一直y
在这里插入图片描述

[root@server1 mysql]# mysql -p##完成mysql部署
Enter password: westos
mysql> show databases;

在这里插入图片描述

2.mysql主从环境配置

mysql官方手册：https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
在这里插入图片描述

1.server1为master，server2为slave

[root@server1 ~]# cd /usr/local/
[root@server1 local]# scp -r mysql/ server2:/usr/local/
[root@server1 local]# cd /etc/
[root@server1 etc]# scp my.cnf server2:/etc/
[root@server1 etc]# cd /etc/init.d/
[root@server1 init.d]# scp mysqld server2:/etc/init.d/

[root@server2 ~]# vim .bash_profile 
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/
[root@server2 ~]# source .bash_profile 
[root@server2 ~]# useradd -M -d /usr/local/mysql/data/ -s /sbin/nologin mysql
[root@server2 ~]# mysqld --initialize --user=mysql
[root@server2 data]# /etc/init.d/mysqld start

[root@server2 ~]# mysql_secure_installation 
[root@server1 init.d]# vim /etc/my.cnf
#添加
server-id=1
log-bin=mysql-bin##启用二进制日志记录
[root@server1 init.d]# /etc/init.d/mysqld restart

[root@server1 init.d]# mysql -pwestos
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'westos';
#%是除了locahost都可以，用户授权
mysql> show master status;

在这里插入图片描述

[root@server2 data]# vim /etc/my.cnf
server-id=2
[root@server2 data]# /etc/init.d/mysqld restart
mysql> CHANGE MASTER TO  MASTER_HOST='172.25.3.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=437;##设置副本以与复制源进行通信
mysql> start slave;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在这里插入图片描述

####测试同步
##server1上mysql> create database westos;
##server2上
mysql> show databases;
+--------------------+
| Database           |
| westos             |
+--------------------+

2.server2为master，server3为slave

%%部署一个server3，server1为主，2，3为副,为减轻server1的压力，将server2作为备份的对象给server3

[root@server1 ~]# cd /usr/local/
[root@server1 local]# scp -r mysql/ server3:/usr/local/
[root@server1 local]# cd /etc/
[root@server1 etc]# scp my.cnf server3:/etc/
[root@server1 etc]# cd /etc/init.d/
[root@server1 init.d]# scp mysqld server3:/etc/init.d/

[root@server3 ~]# vim .bash_profile 
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/
[root@server3 ~]# source .bash_profile 
[root@server3 ~]# useradd -M -d /usr/local/mysql/data/ -s /sbin/nologin mysql
[root@server3 ~]# id mysql##最好与server2上的mysql的id一致
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server3 ~]# cd /usr/local/mysql/data/
[root@server3 data]# rm -fr *
[root@server2 ~]# mysqld --initialize --user=mysql
[root@server3 data]# /etc/init.d/mysqld start

[root@server3 ~]# mysql_secure_installation ##执行初始化脚本，临时密码，新密码，两个回车一直y

%%数据库的备份，server1上备份到server3上
[root@server1 ~]# mysqldump -uroot -pwestos westos > dump.db
[root@server1 ~]# scp dump.db server3:
[root@server3 ~]# mysqladmin create westos -pwestos#创建数据库
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@server3 ~]# mysql -pwestos westos < dump.db ##导入数据库
[root@server3 ~]# vim /etc/my.cnf##改id
[root@server3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=3

[root@server2 ~]# vim /etc/my.cnf##server2所作的步骤会写入自己的二进制日志中，备份时复制只复制二进制日志
[root@server2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
[root@server2 local]# /etc/init.d/mysqld restart 
mysql> show master status;

在这里插入图片描述

[root@server3 ~]# mysql -pwestos
mysql> change master to master_host='172.25.3.2',master_user='repl',master_password='westos',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

####测试同步
[root@server1 ~]# mysql -pwestos
mysql> use westos
mysql> insert into user_tb values ('user3','333');
mysql> insert into user_tb values ('user4','444');

[root@server3 ~]# mysql -pwestos
mysql> select * from westos.user_tb;

在这里插入图片描述

[root@server2 local]# cd /usr/local/mysql/data/
[root@server2 data]# ls
[root@server2 data]# mysqlbinlog server2-relay-bin.000007 -vv##查看二进制文件

3.GTID模式设置复制

官网：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html
##数据同步有延迟，当master端server1挂掉时，要看从端那个数据同步的最近，取它为master端;如下图就会取server2作为master
在这里插入图片描述

[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# cat /etc/my.cnf##所有节点都要强制激活，server1，2，3
##最后添加
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server1 ~]# /etc/init.d/mysqld restart 
[root@server1 ~]# mysql -pwestos
mysql> show master status;

[root@server2 ~]# vim /etc/my.cnf
##最后添加
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 data]# mysql -pwestos
mysql> show slave status\G;

              Master_Log_File: mysql-bin.000003##文件会跟着server1改变
mysql> stop slave;
mysql> change master to master_host='172.25.3.1',master_user='repl',master_password='westos',MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[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> stop slave;
mysql> change master to master_host='172.25.3.2',master_user='repl',master_password='westos',MASTER_AUTO_POSITION = 1;##=1时自动找log文件和位置
mysql> start slave;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

####测试同步

[root@server1 ~]# mysql -pwestos
mysql> use westos
mysql> insert into user_tb values ('user5','555');
mysql> insert into user_tb values ('user6','666');

[root@server3 ~]# mysql -pwestos
mysql> select * from westos.user_tb;

在这里插入图片描述

4.半同步模式

官网：https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
##当slave收到二进制日志文件时会法给master端ACK的确认，但不能做到无损同步，当master挂掉，从的做master就会丢失同步数据，所以要先收到ACK确认再引擎提交（异步模式,默认状态下就是异步模式）。具体如下：
默认情况下，MySQL复制是异步的。源将事件写入其二进制日志，副本将在事件就绪时请求它们。源不知道副本是否或何时检索和处理了事务，并且不能保证任何事件都会到达副本。使用异步复制，如果源崩溃，则它提交的事务可能不会传输到任何副本。在这种情况下，从源到副本的故障转移可能会导致故障转移到缺少相对于源的事务的服务器。
半同步复制介于异步复制和完全同步复制之间。源等待直到至少一个副本接收并记录了事件（所需数量的副本是可配置的），然后提交事务。源不等待所有副本都确认接收，它仅需要副本的确认，而不是事件已在副本端完全执行并提交。因此，半同步复制可确保如果源崩溃，则它已提交的所有事务都已传输到至少一个副本。此时该副本可作为master端。

在这里插入图片描述

[root@server1 ~]# mysql -pwestos
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> show variables like 'rpl%';

在这里插入图片描述

[root@server2 data]# mysql -pwestos
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> show status like 'rpl%';
| Rpl_semi_sync_master_status                | ON  |

[root@server3 data]# mysql -pwestos
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;##重启IO线程
mysql> show status like 'rpl%';
| Rpl_semi_sync_slave_status | ON    |

###测试同步

[root@server1 ~]# mysql -pwestos
mysql> use westos
mysql> insert into user_tb values ('user7','777');
##server2,3上都会同步
mysql> select * from westos.user_tb;
| user7    | 777      |

##模拟slave挂掉

[root@server2 ~]# mysql -pwestos
mysql> stop slave IO_THREAD;##模拟slave挂掉
[root@server1 ~]# mysql -pwestos
mysql> use westos
mysql> insert into user_tb values ('user8','888');
##从端slave异常时，master端等待10s后OK，但是从端数据没有同步；等从端slave正常时，数据会自动同步

[root@server2 ~]# mysql -pwestos
mysql> start slave IO_THREAD;##slave正常，数据会自动同步
mysql> show status like 'rpl%';
Rpl_semi_sync_master_yes_tx                | 2  ##半同步模式同步的文件数量

在这里插入图片描述

5.延迟复制

##从源接收到的事件要比在源上执行的事件至少晚30秒钟后才执行。SQL的延迟，与IO无关，等IO读入30s后再SQL写入

[root@server2 ~]# mysql -pwestos
mysql> stop slave sql_thread;
mysql> change master to master_delay = 30;
mysql> start slave sql_thread;
mysql> show slave status\G;
                    SQL_Delay: 30

###测试延时同步

[root@server1 ~]# mysql -pwestos
mysql> use westos
mysql> insert into user_tb values ('user9','999');
[root@server2 ~]# mysql -pwestos
mysql> show slave status\G;        
		Seconds_Behind_Master: 27##30s后再执行
           Retrieved_Gtid_Set: 90910c83-8f9b-11eb-9840-52540079bc41:1-5
            Executed_Gtid_Set: 90910c83-8f9b-11eb-9840-52540079bc41:1-4

在这里插入图片描述

7.并行复制

[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON

rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart 

[root@server2 ~]# mysql -pwestos
mysql> show variables like 'slave%';

在这里插入图片描述

mysql> show variables like '%info%';###TABLE模式，原先是FILE

在这里插入图片描述

mysql> show processlist;##有16个进程

在这里插入图片描述

8.MySQL组复制

##所有节点都可以写数据，且都保持数据同步

在这里插入图片描述

[root@server1 ~]# /etc/init.d/mysqld stop
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# rm -fr *
[root@server1 data]# vim /etc/my.cnf
[root@server1 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

[root@server1 data]# mysqld --initialize --user=mysql##临时密码
[root@server1 data]# vim /etc/my.cnf
[root@server1 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

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.3.1:33061"
group_replication_group_seeds= "172.25.3.1:33061,172.25.3.2:33061,172.25.3.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.3.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

[root@server1 data]# /etc/init.d/mysqld start
[root@server1 data]# mysql -p临时密码
mysql> alter user root@localhost identified by 'westos';##改密码
mysql> show databases;##可以查看数据库

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
[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 
[root@server2 ~]# cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 data]# vim /etc/my.cnf
[root@server2 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

[root@server2 data]# mysqld --initialize --user=mysql##临时密码twr,Ktw--5Qe

[root@server2 data]# vim /etc/my.cnf
[root@server2 data]# cat /etc/my.cnf##其他都与server1相同
server_id=2

group_replication_local_address= "172.25.3.2:33061"

[root@server2 data]# /etc/init.d/mysqld start
[root@server2 data]# mysql -p
password:
mysql> alter user root@localhost identified by 'westos';##改密码
mysql> show databases;
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> select * from performance_schema.replication_group_members;

在这里插入图片描述

##server3
[root@server3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 
[root@server3 ~]# cd /usr/local/mysql/data/
[root@server3 data]# rm -fr *
[root@server3 data]# vim /etc/my.cnf
[root@server3 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

[root@server3 data]# mysqld --initialize --user=mysql##临时密码twr,Ktw--5Qe

[root@server3 data]# vim /etc/my.cnf
[root@server3 data]# cat /etc/my.cnf##其他都与server1相同
server_id=3

group_replication_local_address= "172.25.3.3:33061"

[root@server3 data]# /etc/init.d/mysqld start
[root@server3 data]# mysql -p
password:
mysql> alter user root@localhost identified by 'westos';##改密码
mysql> show databases;
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> select * from performance_schema.replication_group_members;##都是ONline才部署成功

在这里插入图片描述
###测试

[root@server1 ~]# mysql -p
mysql> create database test;
mysql> use test
mysql> create table t1 (c1 int primary key,c2 text not null);
mysql> desc t1;
mysql> insert into t1 values (1,'111');
mysql> select * from t1;

在这里插入图片描述

[root@server2 ~]# mysql -pwestos
mysql> use test
mysql> select * from t1;
+----+-----+
| c1 | c2  |
+----+-----+
|  1 | 111 |
+----+-----+
mysql> insert into t1 values (2,'222');

[root@server3 ~]# mysql -pwestos
mysql> use test
mysql> select * from t1;

在这里插入图片描述

mysql> insert into t1 values (3,'333');

[root@server1 ~]# mysql -pwestos
mysql> create database test;
mysql> use test
mysql> select * from t1;
+----+-----+
| c1 | c2  |
+----+-----+
|  1 | 111 |
|  2 | 222 |
|  3 | 333 |
###三台都能做master写入数据，且都能同步

###模拟故障
%%三台能允许一台挂掉，挂掉后需要进入集群才会同步数据

[root@server1 data]# /etc/init.d/mysqld stop
[root@server2 ~]# mysql -pwestos
mysql> use test
mysql> insert into t1 values (4,'444');

[root@server1 data]# /etc/init.d/mysqld start
[root@server1 data]# mysql -pwestos
mysql> use test
mysql> select * from t1;
+----+-----+
| c1 | c2  |
+----+-----+
|  1 | 111 |
|  2 | 222 |
|  3 | 333 |
+----+-----+

mysql> START GROUP_REPLICATION;
mysql> select * from t1;

在这里插入图片描述

9.慢查询

MySQL慢查询就是在日志中记录运行比较慢的SQL语句，这个功能需要开启才能用。 long_query_time是指执行超过多久的SQL会被日志记录下来

[root@server1 ~]# mysql -pwestos
mysql> set global slow_query_log=ON;
mysql> show variables like "long%";

[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# cat server1-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.31-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/data/mysql.sock
Time                 Id Command    Argument
# Time: 2021-04-11T01:53:49.457560Z
# User@Host: root[root] @ localhost []  Id:    16
# Query_time: 10.000380  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1618106029;
select sleep(10);

在这里插入图片描述

3.mysql路由器

读写分离,访问不同的端口调用不同的后端，定义到读或写

在这里插入图片描述

1.安装配置mysqlrouter服务

[root@server4 ~]# ls
mysql-router-community-8.0.21-1.el7.x86_64.rpm ##mysql官网下载读写安装包
[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@server4 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
#最后添加,7001端口读，7002端口写
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.3.1:3306,172.25.3.2:3306,172.25.3.3:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.3.1:3306,172.25.3.2:3306,172.25.3.3:3306
routing_strategy = first-available
[root@server4 ~]# systemctl start mysqlrouter.service 
[root@server4 ~]# yum install net-tools -y
[root@server4 ~]# netstat -antlp         
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      4063/mysqlrouter       
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      4063/mysqlrouter

在这里插入图片描述

2.授权

[root@server1 data]# mysql -pwestos
mysql> grant select on *.* to user1@'%' identified by 'westos';
mysql> grant all on test.* to user2@'%' identified by 'westos';
mysql> flush privileges;

3.读

[root@zhenji Downloads]# mysql -h 172.25.3.4 -P 7001 -u user1 -pwestos
mysql> show databases;
mysql> use test
mysql> show tables;
mysql> select * from t1;

在这里插入图片描述

4.写

[root@zhenji Downloads]# mysql -h 172.25.3.4 -P 7002 -u user2 -pwestos
mysql> use test
mysql> insert into t1 values (6,'666');
mysql> select * from t1;

5.负载均衡、安全检查

[root@server1 data]# yum install lsof -y#查看打开的文件描述
[root@server1 data]# lsof -i :3306##负载均衡
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4132 mysql   14u  IPv6  27242      0t0  TCP *:mysql (LISTEN)
mysqld  4132 mysql   80u  IPv6  34072      0t0  TCP server1:mysql->server4:49648 (ESTABLISHED)

[root@zhenji Downloads]# mysql -h 172.25.3.4 -P 7001 -u user1 -pwestos
mysql> select * from test.t1;

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4331 mysql   22u  IPv6  28594      0t0  TCP *:mysql (LISTEN)
mysqld  4331 mysql   77u  IPv6  32862      0t0  TCP server2:mysql->server4:41160 (ESTABLISHED)


[root@zhenji Downloads]# mysql -h 172.25.3.4 -P 7001 -u user1 -pwestos
mysql> select * from test.t1;
[root@server3 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4331 mysql   22u  IPv6  28055      0t0  TCP *:mysql (LISTEN)
mysqld  4331 mysql   78u  IPv6  32669      0t0  TCP server3:mysql->server4:42516 (ESTABLISHED)
##有安全检查，server1挂掉后会自动调度到其他节点上
[root@server1 data]# /etc/init.d/mysqld stop
[root@zhenji Downloads]# mysql -h 172.25.3.4 -P 7001 -u user1 -pwestos
[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4331 mysql   22u  IPv6  28594      0t0  TCP *:mysql (LISTEN)
mysqld  4331 mysql   77u  IPv6  32862      0t0  TCP server2:mysql->server4:41160

4.MHA高可用

MHA 服务有两种角色： MHA Manager(管理节点)和 MHA Node(数据节点)

MHA Manager：通常单独部署在一台独立机器上管理多个 master/slave 集群(组)，每个 master/slave 集群称作一个 application，用来管理统筹整个集群。
MHA node：运行在每台 MySQL 服务器上(master/slave/manager)，它通过监控具备解析和清理 logs 功能的脚本来加快故障转移。简单讲 node 就是用来收集从节点服务器上所生成的 bin-log 。对比打算提升为新的主节点之上的从节点的是否拥有并完成操作，如果没有发给新主节点在本地应用后提升为主节点。

MHA高可用原理：

1）从宕机崩溃的master保存二进制日志事件（binlog events）;
2）识别含有最新更新的slave；
3）应用差异的中继日志（relay log）到其他的slave；
4）应用从master保存的二进制日志事件（binlog events）；
5）提升一个slave为新的master；
6）使其他的slave连接新的master进行复制；

在这里插入图片描述
Manager工具包主要包括以下几个工具：
masterha_check_ssh //检查MHA的SSH配置状况
masterha_check_repl //检查MySQL复制状况
masterha_manger //启动MHA
masterha_check_status //检测当前MHA运行状态
masterha_master_monitor //检测master是否宕机
masterha_master_switch //控制故障转移（自动或者手动）
masterha_conf_host //添加或删除配置的server信息

Node工具包（由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs //保存和复制master的二进制日志
apply_diff_relay_logs //识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog //去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs //清除中继日志（不会阻塞SQL线程）

1.重新配置mysql主从

##server1

[root@server1 data]# /etc/init.d/mysqld stop

[root@server1 data]# vim /etc/my.cnf
[root@server1 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server1 data]# pwd
/usr/local/mysql/data
[root@server1 data]# rm -fr *
[root@server1 data]# mysql --initialize --user=mysql
[root@server1 data]# /etc/init.d/mysqld start
[root@server1 data]# mysql_secure_installation 
[root@server1 data]# mysql -pwestos
mysql> show master status;
mysql> grant replication slave on *.* to repl@'%' identified by 'westos';

##server2

[root@server2 data]# vim /etc/my.cnf
[root@server2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server2 ~]# cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 data]# mysqld --initialize --user=mysql
[root@server2 data]# /etc/init.d/mysqld restart
[root@server2 data]# mysql -pwestos
mysql> change master to master_host='172.25.3.1',master_user='repl',master_password='westos',MASTER_AUTO_POSITION = 1;

mysql> start slave;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

##server3

[root@server3 data]# vim /etc/my.cnf
[root@server3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server3 ~]# cd /usr/local/mysql/data/
[root@server3 data]# rm -fr *
[root@server3 data]# mysqld --initialize --user=mysql
[root@server3 data]# /etc/init.d/mysqld restart
[root@server3 data]# mysql -pwestos
mysql> change master to master_host='172.25.3.1',master_user='repl',master_password='westos',MASTER_AUTO_POSITION = 1;

mysql> start slave;
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2.Manager工具包

[root@server4 ~]# ls
MHA-7  mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@server4 ~]# cd MHA-7/
[root@server4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@server4 MHA-7]# yum install *.rpm -y


[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:

[root@server1 ~]# ls
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y 
[root@server2 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y 
[root@server3 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

3.高可用环境配置

[root@server4 MHA-7]# rpm -ql mha4mysql-manager 
[root@server4 MHA-7]# masterha_check_ssh --help
[root@server4 conf]# pwd
/root/MHA-7/mha4mysql-manager-0.58/samples/conf
[root@server4 conf]# ls
app1.cnf  masterha_default.cnf
[root@server4 conf]# mkdir /etc/masterha
[root@server4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app.cnf
[root@server4 conf]# cd /etc/masterha
[root@server4 masterha]# ls
app.cnf
[root@server4 masterha]# vim app.cnf 
[root@server4 masterha]# cat app.cnf 
[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.3.1 -s 172.25.3.2
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
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log

[server1]
hostname=172.25.3.1

[server2]
hostname=172.25.3.2
candidate_master=1

[server3]
hostname=172.25.3.3
no_master=1
[root@server4 masterha]# mkdir app1

4.免密

[root@server4 masterha]# ssh-keygen 
[root@server4 masterha]# ssh-copy-id server1
[root@server4 masterha]# ssh-copy-id server2
[root@server4 masterha]# ssh-copy-id server3
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf##有错误，server1，2，3之间没有互相免密
[root@server4 ~]# scp -r .ssh/ server1:
[root@server4 ~]# scp -r .ssh/ server2:
[root@server4 ~]# scp -r .ssh/ server3:
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf##有错误，没有授权
[root@server1 ~]# mysql -pwestos
mysql> grant all on *.* to root@'%' identified by 'westos';
mysql> flush privileges;
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf

5.手动切换master（server1是master ，server1 stop后要手动将master切到server2上）

[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.3.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

在这里插入图片描述

[root@server1 ~]# mysql -pwestos
mysql> show slave status\G;##此时master已经切换到server2，且server1作为slave端
                  Master_Host: 172.25.3.2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在这里插入图片描述

[root@server1 ~]# /etc/init.d/mysqld stop
[root@server4 app1]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.3.2 --dead_master_port=3306 --new_master_host=172.25.3.1 --new_master_port=3306 --ignore_last_failover
[root@server3 ~]# mysql -pwestos
mysql> show slave status\G;##此时master已经切换到server1，且server1作为slave端
                  Master_Host: 172.25.3.1
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在这里插入图片描述

[root@server1 ~]# /etc/init.d/mysqld start##启动server2并加入集群开启slave
[root@server2 ~]# mysql -pwestos
mysql> change master to master_host='172.25.3.1',master_user='repl',master_password='westos',MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
                  Master_Host: 172.25.3.1
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在这里插入图片描述

[root@server4 app1]# masterha_check_ssh --conf=/etc/masterha/app.cnf
[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf

[root@server1 ~]# /etc/init.d/mysqld stop
root@server4 app1]# ls
app.failover.complete
[root@server4 app1]# rm -rf app.failover.complete 
[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[root@server4 app1]# ps ax
[root@server1 ~]# /etc/init.d/mysqld stop
[root@server4 app1]# ls
app.failover.complete  manager.log
[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# mysql -pwestos
mysql> CHANGE MASTER TO  MASTER_HOST='172.25.3.2',MASTER_USER='repl',MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
                  Master_Host: 172.25.3.2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6.自动切换master（server1是master ，server1 stop后集群会自动将master切到server2上）

[root@zhenji Downloads]# scp master_ip_failover master_ip_online_change server4:/usr/local/bin
[root@server4 masterha]# vim app.cnf 
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log

[root@server2 ~]# ip addr add 172.25.3.100/24 dev eth0
[root@zhenji Downloads]# mysql -h 172.25.3.100 -u root -pwestos##可以进去

[root@server4 bin]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.3.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
[root@server3 ~]# mysql -pwestos
mysql> show slave status\G;##此时master已经切换到server1，且server2作为slave端
                  Master_Host: 172.25.3.1
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf #检查免密是否做好
[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf #检查用户授权

[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[root@server4 app1]# ps ax
[root@server1 ~]# /etc/init.d/mysqld stop##server1停掉后master端就会自动转换成server2
[root@server3 ~]# mysql -pwestos
mysql> show slave status\G;##此时master已经切换到server2，且server1作为slave端
                  Master_Host: 172.25.3.2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# mysql -pwestos
mysql> CHANGE MASTER TO  MASTER_HOST='172.25.3.2',MASTER_USER='repl',MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
                  Master_Host: 172.25.3.2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@server4 bin]# cd /etc/masterha/app1/
[root@server4 app1]# ls
app.failover.complete  manager.log
[root@server4 app1]# ll
total 20
-rw-r--r-- 1 root root     0 Apr 11 15:51 app.failover.complete
-rw-r--r-- 1 root root 17257 Apr 11 15:51 manager.log

qiao_qing

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
lamp(2)mysql主从策略

文章目录1.mysql主从环境配置2.mysql参数配置延迟复制并行复制MySQL组复制1.mysql主从环境配置mysql官方手册：https://dev.mysql.com/doc/refman/5.7/en/replication-setup-replicas.html[root@server1 ~]# cd /usr/local/[root@server1 local]# scp -r mysql/ server2:/usr/local/[root@server1 local]# c.
复制链接

扫一扫