数据库代理服务和集群管理

集群技术

数据库集群环境配置

1. 准备4个全新未安装MySQL且能互相通信的服务器，mater1,master2,salve1,salve2
2. 分别在master1\master2\slave1\slave2服务器安装mysql，参考[https://www.cnblogs.com/xiaodunan/p/18239367]
3. 配置域名解析

vim /etc/hosts

把hosts文件拷贝到其它的服务器

scp /etc/hosts IP：/etc/

案例1：一主一从（M-S）

数据库集群.mysql主服务器配置

1. 主（master1）
   （1）部署一台新mysql服务器，准备好域名解析
   （2）准备数据1（验证主从同步使用）

create database master1db;
create table master1db.master1tab(name char(50));
desc master1db.master1tab;
insert into master1db.master1tab values (111111);
insert into master1db.master1tab values (222222);
select * from master1db.master1tab;

（3）开启二进制日志

• 配置文件

vim /etc/my.cnf
log_bin
server-id=1

• 重启mysql

systemctl restart mysqld

（4）创建复制用户，用于从服务器访问使用

create user 'rep'@'10.18.41.%' identified by '密码';
grant replication slave,replication client on *.* to 'rep'@'10.18.41.%';

（5）备份master数据库的数据

mysqldump -uroot -p'密码' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

（6）准备数据2（验证主从同步使用）

insert into master1db.master1tab values(33333);
insert into master1db.master1tab values(44444);
select * from master1db.master1tab;

数据库集群.mysql从服务器配置

1. 从（master2）
   （1）测试rep用户是否可用

mysql -h master1 -urep -p'密码';

（2）启动服务器序号

• 配置文件

vim /etc/my.cnf
server-id=2

• 重启mysql
• 测试rep用户是否可用
  （3）手动同步数据
• 在主服务器master1把备份文件复制到从服务器master2

scp 2024-07-07-mysql-all.sql master2:/tmp/ #在master1上操作

• 设置数据库日志二进制关闭

mysql>set sql_log_bin=0; #在master2上操作

• 读取文件 ,在master2上操作

mysql> source /tmp/2024-07-07-mysql-all.sql;

• 检查数据是否同步,在master2上操作

select * from  master1db.master1tab;

（4）设置主服务器，在master2上操作

mysql> change master to master_host='master1',master_user='rep',master_password='密码',master_log_file='localhost-bin.000002',master_log_pos=154;
# master_log_file和master_log_pos的值在master1使用`show master status;`命令查

（5）启动从服务器

mysql>start slave;

（6）查看启动状态（IO-YES/SQL-YES）

mysql>show slave status\G;

（7）返回主服务器（master1）更新数据，在从服务器（master2）观察是否同步

案例2：一主一从（M-S），主从自动协商

需求：
案例2与案例1需求基本相同。master1作为主mysql，master2作为从mysql，不同之处是使用了“gtid_mode=ON enforce_gtid_consistency=1”该属性自动记录position位置。不需要手动指定了。
环境：
因与案例1功能相同，只需要重置master2数据库重新配置即可

1. 停止master2数据库

systemctl stop mysqld

2. 删除数据

rm -rf /var/lib/mysql/*

3. 启动数据库

systemctl start mysqld

4. 查看默认密码

grep password /var/log/mysqld.log

5. 修改数据库密码

mysqladmin -uroot -p'默认密码' password '新密码'

主

1. 启动二进制日志，服务器ID，GTID

vim /etc/my.cnf
log_bin
server-id
gtid_mode=ON
enforce_gtid_consistency=1

2. 重启服务器

systemctl restart mysqld

3. 授权复制用户rep（案例1已做过，此处可以忽略）
4. 备份数据

mysqldump -p'密码' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F-%H`-mysql-all.sql
scp 2023-7-11-mysql-all.sql master2:/tmp

5. 模拟数据变化

mysql>insert into master1db.master1tab values (666666);

从

1. 测试rep用户是否可用

mysql -h master1 -urep -p'密码'

2. 启动二进制日志，服务器ID，GTID

vim /etc/my.cnf
log_bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

3. 重启服务器

systemctl restart mysqld

4. 手动恢复master1数据

set sql_log_bin=0; #临时关闭二进制日志
source /tmp/2024-7-11-mysql-full.sql
select * from master1db.master1tab;

5. 设置主服务器

mysql> change master to
       master_host='master1',
       master_user='rep',
       master_password='密码',
       master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;

6. 返回主服务器（master1）更新数据，在从服务器(master2)观察是否同步

数据库集群.双主双从环境介绍

• 背景：避免单一主服务器宕机，集群写入能力缺失
  从1复制主1，从2复制主2，主1复制主2，主2复制主1，也就是主1和主2互为主从。主1主2互为主从，是为了以下情景，主1挂了，主2自动升级为主数据库，当主1恢复后，主1则变成次主数据库。
  
  四台服务器，每台服务器上安装了MySQL
• 举例说明

1. 在主1创建了一个mydb2数据库，从1自动“复制”主1生成数据库
2. 因为主2也是主1的从数据库，所以主2也“复制”主1生成数据库
3. 主2有了数据库，从2自动“复制”主2生成数据库
   最后，四台服务器数据库都创建了数据库

• 操作步骤

1. 清理四台数据库服务器数据

systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysql.log
mysqladmin修改默认密码

2. 主1 my.cnf 添加如下配置,改完要重启mysql

vim /etc/my.cnf
# bin log 日志
log-bin=/var/lib/mysql/binlog
# 服务id
server-id=1
# 主从复制忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 开启主从复制的数据库
binlog-do-db=mydb2
# bin log日志格式
# STATEMENT：记录主库执行的SQL复制到从库
调用时间函数时会导致主从数据不一致
# ROW：记录主库每一行的变化：效率低
# MIXED：修复一些主从数据不一致情况；本地变量调用还会存在问题；@@hostname
binlog_format=statement
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误
slave_skip_errors=1062
# 在作为从数据库时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 标识自增长字段每次递增的量，也就是步长
auto-increment-increment=2
# 表示自增长 表示自增长从哪个数开始
auto-increment-offset=1

3. 主2 my.cnf 添加如下配置,改完要重启mysql

# bin log 日志
log-bin=/var/lib/mysql/binlog
# 服务id
server-id=3
# 主从复制忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 开启主从复制的数据库
binlog-do-db=mydb2
# bin log日志格式
# STATEMENT：记录主库执行的SQL复制到从库；调用时间函数时会导致主从数据不一致
# ROW：记录主库每一行的变化：效率低
# MIXED：修复一些主从数据不一致情况；本地变量调用还会存在问题；@@hostname
binlog_format=statement
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误
slave_skip_errors=1062
# 在作为从数据库时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 标识自增长字段每次递增的量，也就是步长
auto-increment-increment=2
# 标识自增长从哪个数开始
auto-increment-offset=2

4. 从1 my.cnf 添加如下配置,改完要重启mysql

# 服务id
server-id=2
# 启用中继日志
relay-log=mysql-relay

5. 从2 my.cnf 添加如下配置,改完要重启mysql

# 服务id
server-id=4
# 启用中继日志
relay-log=mysql-relay

6. 创建同步账号并授权

• 主1、主2数据库
  （1）创建主主同步账号repl_user
  （2）主从同步账号slave_sync_user

mysql> create user 'repl_user'@'%' identified with mysql_native_password by '密码';
mysql> create user 'slave_sync_user'@'%' identified with mysql_native_password by '密码'；
mysql> grant replication slave on *.* to 'repl_user'@'%';
mysql> grant replication slave on *.* to 'slave_sync_user'@'%';

7. 配置主从同步

• 主1-->从1
  （1）登录master1服务器查看binlog文件和偏移量

mysql> show master status;

（2）登录slave1服务器，修改配置

mysql> change master to master_host='master1 ip',master_user='slave_sync_user',master_password='密码',master_log_file='binlog.000003',master_log_pos=952;
mysql> start slave;
mysql> show slave status\G;

• 主2-->从2
  （1）登录master2服务器查看binlog文件和偏移量 （file和position）

mysql> show master status;

（2）登录slave2服务器，修改配置

mysql> change master to master_host='master2 ip',master_user='slave_sync_user',master_password='密码',master_log_file='binlog.000004',master_log_pos=953;
mysql> start slave;
mysql> show slave status\G;

• 主1-->主2
  （1）登录master1服务器查看binlog文件和偏移量 （file和position）

mysql> show master status;

（2）登录master2服务器，修改配置

mysql> change master to master_host='master1 ip',master_user='slave_sync_user',master_password='密码',master_log_file='binlog.000003',master_log_pos=952;
mysql> start slave;
mysql> show slave status\G;

• 主2-->主1
  （1）登录master2服务器查看binlog文件和偏移量 （file和position）

mysql> show master status;

（2）登录master1服务器，修改配置

mysql> change master to master_host='master2 ip',master_user='slave_sync_user',master_password='密码',master_log_file='binlog.000004',master_log_pos=953;
mysql> start slave;
mysql> show slave status\G;

8. 主1 mysql下创建数据库mydb2,创建表books，插入一条数据,查看主1，主2，从1，从2是否同步生成数据库、表、数据。