mysq数据库五

实验：搭建主从级联复制：（既中间一个服务器只作为主服务器和从服务器同步的工具）

拓扑结构图：一个主服务器，中间一个中继服务器（他的作用就是将主服务器上的数据同步过来，在同步到下面的从服务器上去，这样可以避免主服务器同步多个从服务器时的负载过大）

以下为实验具体过程：

实验环境：selinux;iptables;

1 . 主服务器的配置
vim /etc/my.cnf  （添加的）

[mysqld]

log_bin     (开启二进制)
server_id=1    （主服务器的id号）

配置好重启服务：systemctl restart mariadb

2. show master logs; （查看此时主服务器在二进制日志所处的状态）

3  .进入数据库：grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’; （给从服务器授权并创建账号）

4 .  中继服务器的配置文件的设置

vim /etc/my.cnf   (添加的)

[mysqld]

server_id=2
log_bin
log_slave_updates
systemctl start mariadb  （配置好配置文件启动数据库的服务）

help change master to;（查询配置从服务器的命令语句） （在数据库中操作）

5  .CHANGE MASTER TO MASTER_HOST=’192.168.60.4′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245；

CHANGE MASTER TO
MASTER_HOST=’192.168.60.4′,    （主服务器的IP地址）
MASTER_USER=’lv’,                              （刚刚授权用户的名称）
MASTER_PASSWORD=’centos’,            （授权用户的密码）
MASTER_PORT=3306,
MASTER_LOG_FILE=’mariadb-bin.000001′,     （此位置文件查看刚才主服务器：show master logs;）
MASTER_LOG_POS=544,                                               (同上)
MASTER_CONNECT_RETRY=10

6 . show slave status\G;  （查看本机此时的状态）

7 . start slave      (开启同步服务)             reset slave (清掉复制过来的中继日志)reset all (清掉所有当你在主的服务器地址设置错误或者其他方面设置错误时全部清掉后重新配置)

8 .  在第三个从服务器上配置参数：

vim/etc/my.cnf

[mysql]

server_id=3
read_only   (服务器只提供读服务)

重启服务

9 . 此时再次确认再主服务器上授权的用户有没有同步过来：select user,host,password from mysql.user

如果没有同步过来在此授权同步给第三台主机从服务器。

10 .CHANGE MASTER TO MASTER_HOST=’192.168.60.20′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; （将中继服务器作为主服务器进行同步设置）

11  .  start slave;   show slave status\G;   （启动服务并查看状态）

小结：至此完成级联复制的搭建；如果主服务器宕机 再再中间服务器上首先stop slave,然后再清理：reset  slave  all ，之后重新指定就可以了。

 

 

 

实验：实现主主服务器的搭建；（两个都是主没有从，俩个主服务器互相同步，一般不推荐使用）

考虑要点：自动增长id 配置一个节点使用奇数id

auto_increment_offset=1 开始点 （主键开始位置为1）

auto_increment_increment=2 增长幅度 （1，3，5，7的递增）

另一个节点使用偶数id

auto_increment_offset=2   （主键的开始位置为2）

auto_increment_increment=2   （2，4，6，8 以增长幅度为2开始递增）

如上述设置避免造成两个主服务器数据的冲突。

主主复制的配置步骤： (1) 各节点使用一个惟一server_id (2) 都启动binary log和relay log (3) 创建拥有复制权限的用户账号 (4) 定义自动增长id字段的数值范围各为奇偶 (5) 均把对方指定为主节点，并启动复制线程

实验环境：selinux;iptables;

具体步骤如下：（思路：两边互相给对方授权，并互相同步信息，只是配置文件上略有不同而已）

1 . 修改第一个主服务器的配置文件：

vin /etc/my.cnf

server_id=1
log_bin
auto_increment_offset=1
auto_increment_increment=2

systemctl restart mariadb  （改完之后重启服务）

2 .修改第二个主服务器的配置文件：

vin /etc/my.cnf
server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2

systemctl restart mariadb （改完之后重启服务）

3 .  在一台主机授权相互权限并创建用户：(进入数据库输入下面命令)

show master logs;(查看此时二进制日志的位置状态以便后面使用)

grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’;  （再第一台主服务器上授权）

4 .在两台主机分别执行同步的任务：

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; (一台主服务器上)

（’mariadb-bin.000001′,MASTER_LOG_POS=245; 此位置的具体信息应该差对方的show master logs;来获得你想要的二进制日志的位置）

此时同步刚刚授权给自己的主机同步位置选在245位置既创建授权用户前，同步之后自己有了相同的授权创建用户的信息，否则需要这边重新创建授权信息，这样做省了这一步。

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245; （另一台主服务器上）

（’mariadb-bin.000002′,MASTER_LOG_POS=405; 此位置的具体信息应该差对方的show master logs;来获得你想要的二进制日志的位置）

5 .两边主机都开启同步服务：start slave;  并查看此时的同步状态show  slave status;

小结：上述实验 是在两台服务器都是干净系统的情况下实现的，现实中如果一台机器是已经使用的，并且有数据，再搭建一台新的服务器时，需要将旧服务器上的数据备份还原后再搭建主主系统。（还原只需还原最近一次全备份时的数据，以后的增量备份会在系统搭建好自动通过二进制日志进行同步）

 

 

实验：实现半同步复制：

半同步复制的意思就是：当一台主服务器带多台从服务器时，主从复制时等全部从同步后才提示同步完成，而半同步复制只保证一个从服务器同步完成就提示，提高效率。节省时间；

具体实现步骤：

1 . 首先实现一主一从服务器，并实现同步

主服务器修改配置文件：

vim/etc/my.cnf

server_id=1

log_bin

systemctl start  mariadb

2 . 从服务器修改配置文件：

vim/etc/my.cnf
server_id=2
log_bin
systemctl start mariadb

3 . 主服务器创建用户并授权给从服务器用户：
grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’;  (进入数据库中输入)

show master logs;  (查看此时二进制日志的位置状态给从服务器同步时使用)

4 . 从服务器同步主服务器的数据：

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′,MASTER_USER=’lv’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001′,MASTER_LOG_POS=245;

5 .开启从服务器的同步功能：start  slave  ;查看同步的实时状态：show slave status\G;

6 . 在主服务器上安装插件：

install plugin rpl_semi_sync_master soname ‘semisync_master.so’;

show plugins;  （查看插件是否已经安装好了；最后一行可以看见）

7 .  show global variables like ‘%semi%’; （查看插件的系统变量；没有启用）

show status like ‘%semi%’;  （查看插件的状态变量；也是没有启用的）

set global rpl_semi_sync_master_enabled=on;  （启用插件的系统变量）

8 . 在从服务器上安装插件：

install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;

show plugins; （查看插件是否已经安装好了；最后一行可以看见）

9 .  show global variables like ‘%semi%’; （查看插件的系统变量；没有启用）

show status like ‘%semi%’; （查看插件的状态变量；也是没有启用的）

set global rpl_semi_sync_slave_enabled=on;  （启用插件系统变量）

将插件变量开启后再次查看show status like ‘%semi%’; （查看插件的状态变量；也还是没有开启），我们要先stop slave；（停止同步服务）再start slave （再开启同步服务）就应该可以了。

10 . 实测，在主服务器上添加更改信息，查看从服务器是否同步。到此已经完成了。

11 . show status like ‘%semi%’;  （在服务器端查看状态变量，可以看到详细信息） 如下图：

小结：此实验是在一台从服务器上安装的插件实现的，实际工作中如果有多台从服务器的话，每台从服务器都需要按上述从服务器安装插件和启动插件变量。如果是级联复制结构的话则只需要在中间的服务器上安装从服务器插件就可以了，其他的从服务器就不需要安装插件了。

 

 

MySQL复制

复制过滤器

两种实现方式： (1) 主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件 问题：时间还原无法实现；不建议使用  （限定的二进制文件不会同步过来；这样做会使二进制数据造成丢失）

(2) 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数 据库(特定表)相关的事件并应用于本地 问题：会造成网络及磁盘IO浪费 （将所有的二进制日志文件都复制过来，但在同步到自己的数据库时，会选择不同步限制条件的内容）

复制过滤器在从服务器上的相关设置 

replicate_do_db= 指定复制库的白名单 

replicate_ignore_db= 指定复制库黑名单 （对整个库进行忽略过滤）

replicate_do_table= 指定复制表的白名单 

replicate_ignore_table= 指定复制表的黑名单 （对单个表进行忽略过滤）

replicate_wild_do_table= foo%.bar% 支持通配符 

replicate_wild_ignore_table=

这里选择第二种方法来模拟实现：

前提是实现主从服务器的搭建：

1 . 在从服务器上，进入数据库后添加限制条件；

设定条件前需要先将同步服务停止：stop slave ;

set global replicate_ignore_db=dbbb;  (将dbbb表过滤掉，此表的增删改都不同步)

show variables like ‘replicate_ignore_db’;  （设定完后查看限定条件的变量值）

2 . 将同步服务再次开启：start slave;

3 . 到此已经设置完了，做个测试在主服务器上去对库dbbb里的t1表进行添加内容，此时从服务器是看不到的。

小结：上述实验中，主从同步一定要确保实现，然后dbbb为库，t1为其下的一张表。

 

 

实战：实现主从SSL加密复制（在远程成数据传输备份时保证数据不会泄露）

实验环境：selinux ;iptables;

拓扑结构图：三台机器；CA授权机；主服务器；从服务器；

具体步骤如下：

CA主机执行以下操作：

1 .mkdir /etc/my.cnf.d/ssl/   （在此目录下建立新的文件夹ssl来存放证书文件，此文件夹为自定义目录）

2 . openssl genrsa 2048 > /etc/my.cnf.d/ssl/cakey.pem （生成私钥文件并存放到规划的目录下）

3 . cd /etc/my.cnf.d/ssl/ (进入到此目录下操作)

openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650  （生成自签名的证书文件）

国家：CN

省：beijing

城市：beijing
公司：magedu.com
组织：cpt

地址：可以为空

4 . openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr （生成证书请求文件，）

国家：CN
省：beijing
城市：beijing
公司：magedu.com
组织：cpt

hostname: master.magedu.com

后面的可以不用写；一下生成两个文件，一个是私钥文件另一个是请求文件。

5 . openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt（颁发证书文件）

6 .重复4和5 的操作给slave从服务器颁发证书文件；

openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr （如下图）

openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt （给从服务器颁发证书）

7 . /etc/my.cnf.d/ssl （此时文件夹下有如下文件）

cacert.pem  ；master.crt ；master.key ；slave.csr
cakey.pem ；master.csr ；slave.crt； slave.key

8 . 验证证书是否合法的
openssl verify -CAfile cacert.pem master.crt slave.crt
9 . 将证书文件整个目录都复制到主服务器和从服务器上去。

scp -r /etc/my.cnf.d/ssl/ 192.168.60.20:/etc/my.cnf.d

scp -r /etc/my.cnf.d/ssl/ 192.168.60.21:/etc/my.cnf.d

10 . 在主服务器上进入到复制过去的目录下将用不到的文件都删掉。

rm -rf cakey.pem master.csr slave.*

11 . 同上将从服务器的用不到的文件也给删除。

rm -rf cakey.pem slave.csr master.*

12 .  将主从服务器搭建起具体步骤找上面步骤就可以了

在master上
vim /etc/my.cnf
[mysqld]
log_bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

systemctl restart mariadb  （重启服务）

show variables like ‘%ssl%’;（查看变量）

13 .grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’ require ssl;（在主服务器上创建授权用户并强制它使用加密的方式）

show master logs; （查看此时二进制日志的位置状态）

14 . 此时如果从服务器想登录主服务器的数据库光输入密码是不行的。

mysql -ulv -pcentos -h192.168.60.4 –ssl-ca=cacert.pem –ssl-cert=slave.crt –ssl-key=slave.key

15 .从服务器的配置文件修改：
或者
vim /etc/my.cnf
[mysqld]
server_id=2
ssl

systemctl restart mariadb   (重启服务)

14 .进入数据库查看ssl变量的状态：show variables like ‘%ssl%’;  （应该是yes的）

15 . CHANGE MASTER TO MASTER_HOST=‘192.168.60.20’, MASTER_USER=’lv’,MASTER_PASSWORD=‘centos’,MASTER_PORT=3306,MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=406, MASTER_CONNECT_RETRY=10, MASTER_SSL=1, MASTER_SSL_CA = ‘/etc/my.cnf.d/ssl/cacert.pem’, MASTER_SSL_CERT = ‘/etc/my.cnf.d/ssl/slave.crt’, MASTER_SSL_KEY = ‘/etc/my.cnf.d/ssl/slave.key’;（在从服务器上同步主服务器设置）

show slave status\G;(查看状态)

16 . start slave （开启同步）

 

 

 

实战：实现主从高可用MHA （在主服务器宕机时，自动将从服务器切换成主服务器来使用）

此功能只是一次性的，切换之后，功能就停用了，如果想启用需要再次开启此功能。

结构拓扑图：四台主机；manager,master,slave1,slave2 （管理者，主服务器，两个从服务器）

具体实验步骤：

1 准备
selinux ,iptables ,time  （此实验各台服务器的时间要保持一致）

在vim /etc/ntp.conf  添加server 172.20.0.1   iburst

systemctl  start ntpd

2 . 实现主从服务器的搭建

master:上

vim /etc/my.cnf

[mysqld]
server_id=1
log_bin

skip_name_resolve   (跳过名字解析)

systemctl start mariadb  （重启服务）

show master logs;  （查看此时的二进制日志状态为后面从服务器同步时使用）

grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’; （授权用户账号)

3 .在slave1服务器上

vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1

systemctl start mariadb  （重启服务）

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245;   （连接同步）

start slave (开启同步)      show slave status\G; (查看同步状态)

4 在slave2服务器上：
[mysqld]
server_id=3
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1
systemctl start mariadb （重启服务）

CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=394;    （连接同步）

start slave (开启同步) show slave status\G; (查看同步状态)

5 . 在manager主机上：

实现基于key的验证：

ssh-keygen  （生成私钥对）

ssh-copy-id 192.168.60.4  （把自己的公钥复制到自己的机器上）

cd   .ssh  (进到此目录下)

scp -pr .ssh 192.168.60.20:/root/

scp -pr .ssh 192.168.60.21:/root/

scp -pr .ssh 192.168.60.22:/root/ （将公钥都复制到其他主机的家目录下）

6 . 在master服务器上新建授权用户：
grant all on *.* to mhauser@’192.168.60.%’ identified by ‘centos’;

（此账号由于是现在建立的他会自动同步到其他两个从服务器上）

7 .  在管理节点上安装两个包： mha4mysql-manager   mha4mysql-node （manager服务器上安装）

在被管理节点安装： mha4mysql-node  （主从服务器上安装）

mha4mysql-manager-0.56-0.el6.noarch.rpm Public
Documents mha4mysql-node-0.56-0.el6.noarch.rpm  （网上下载两个包）

yum install mha*    （安装两个包，要依赖于epel源）

8 . 在主从的三个服务器上安装mha4mysql-manager-0.56-0.el6.noarch.rpm Public包

9.  在manager服务器上创建一个文件：mkdir /etc/mha

vim /etc/mha/app1.conf
[server default]
user=mhauser    （此为在第6步创建授权的用户）
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=lv
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.60.20   (主服务器的地址)
candidate_master=1
[server2]
hostname=192.168.60.21  （从服务器的地址）
candidate_master=1     （添加此行代表，主服务器宕机时此为备用的）
[server3]
hostname=192.168.60.22

10 Mha验证和启动   (检查和验证)

masterha_check_ssh –conf=/etc/mha/app1.conf  (探测ssh协议能否连接成功)

masterha_check_repl –conf=/etc/mha/app1.conf   (检查复制功能)

检查复制功能时出现错误，提示lv用户没有复制的权限，添加上权限，在主服务器上：grant all on *.* to lv@’192.168.60.%’; 添加和后再查看一下权限：show grants for lv@’192.168.60.%’;

masterha_manager –conf=/etc/mha/app1.conf   （开启监控功能此命令为前台运行命令，开启后是不会退出的，以此执行词条命令应该在终端上行，如果在远程上执行，连接断开会造成监控失败）

排错日志： /data/mha/app1/manager.log

11 .只要不出错监控会持续下去，现在模拟将主服务器掉电，看看会不会自动切换。

12 . 原来的主服务器修好之后，变成从服务器，需要再配置文件 /etc/my.cnf添加两行：

read_only=1
relay_log_purge=0

重启服务重新加载修改后的配置文件。

12 . 重新从现在的新主上获得授权同步新主的文件；需要查看现在主服务器的二进制日志以便同步

CHANGE MASTER TO MASTER_HOST=’192.168.60.21‘, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000002′, MASTER_LOG_POS=245;

执行完了以后；start slave;  (开启同步功能)

13 . 再次将manager主机上的程序跑起来：

masterha_manager –conf=/etc/mha/app1.conf  （里面的配置文件不需要更改）

14 . 这时再将现在的主服务器断电，此时又应该切换到定义备用的主服务器上。

修复完成后，想重启mha监控要删除 /data/mastermha/app1/目录下的complete结尾的文件，否则无法重启监控