一、主从复制及主主复制的实现

主从复制

master配置

# yum -y install mysql-server

# systemctl enable --now

# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id =18

log-bin=/data/logbin/mysql-bin

binlog_format=row

# systemctl restart mysqld

mysql> create user repluser@'10.0.0.%' identified by '123456';

mysql> grant replication slave on *.* to repluser@'10.0.0.%';

mysql> show master logs;

+------------------+-----------+-----------+

| Log_name         | File_size | Encrypted |

+------------------+-----------+-----------+

| mysql-bin.000001 |       179 | No        |

| mysql-bin.000002 |       681 | No        |

+------------------+-----------+-----------+

mysql > source hellodb_innodb.sql

slave配置

# yum -y install mysql-server

# systemctl enable --now

# vi /etc/my.cnf.d/mysql-server

[mysqld]

server-id=28

log-bin=/data/logbin/mysql-bin

binlog_format=row

read_only=on

# mkdir /data/logbin -pv

# chown mysql.mysql /data/logbin

# systemctl restart mysqld

mysql > CHANGE MASTER TO \

MASTER_HOST='10.0.0.18', \

MASTER_USER='repluser', \

MASTER_PASSWORD='123456', \

MASTER_PORT=3306, \

MASTER_LOG_FILE='mysql-bin.000002', \

MASTER_LOG_POS=681;

mysql > start slave;

mysql > show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 10.0.0.18

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 24432

               Relay_Log_File: slave-relay-bin.000002

                Relay_Log_Pos: 24075

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

                    ...省略...

验证主从复制

[root@slave ~]# mysql

mysql > showdatabases;

+--------------------+

| Database           |

+--------------------+

| hellodb            |

| information_schema |

| mycat              |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

6 rows in set (0.00 sec)

# 从节点上可以查看到数据库hellodb,说明已经完成主从复制

主主复制

两个主，主主复制。但实际上只用一个主，当有一个主挂了。只需要将虚拟ip指向剩下的主，因为本身是主，无需配置。配置一个触发器，触发加上一个从就实现无缝连接且自动。

在master1配置

# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id=8

log-bin

auto_increment_offset=1 	#开始点

auto_increment_increment=2 	#增长幅度

# systemctl start mysqld

mysql > show master logs;

+--------------------+-----------+

| Log_name | File_size           |

+--------------------+-----------+

| mariadb-bin.000001 | 28303     |

| mariadb-bin.000002 | 386       |

+--------------------+-----------+

mysql> create user repluser@'10.0.0.%' identified by 'magedu';

mysql> grant replication slave on *.* to repluser@'10.0.0.%';

实现单向主从复制

在master2配置

# vim /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

server-id=18

log-bin

auto_increment_offset=2 	#开始点

auto_increment_increment=2 	#增长幅度

# systemctl start mysqld

mysql > CHANGE MASTER TO

-> MASTER_HOST='10.0.0.8',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='magedu',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mariadb-bin.000002',

-> MASTER_LOG_POS=386;

mysql > start slave;	# 此时单向复制已完成

mysql > show master logs; #查看二进制位置

+--------------------+-----------+

| Log_name           | File_size |

+--------------------+-----------+

| mariadb-bin.000001 | 28303     |

| mariadb-bin.000002 | 344       |

+--------------------+-----------+

2 rows in set (0.001 sec)

实现双向复制

在master1配置

mysql > CHANGE MASTER TO

-> MASTER_HOST='10.0.0.18',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='magedu',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mariadb-bin.000002',

-> MASTER_LOG_POS=344;

mysql > start slave;

二、xtrabackup实现全量+增量+binlog恢复库

1 备份过程

  1）完全备份：

	[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm

	[root@centos8 ~]#mkdir /backup/

	[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base

  2）第一次修改数据，第一次增量备份

	[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 \ --incremental-basedir=/backup/base

  3）第二次修改数据，第二次增量备份

	[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 \ --incremental-basedir=/backup/inc1

  4）[root@centos8 ~]#scp -r /backup/* 10.0.0.18:/backup/

2还原过程

  1）预准备完成备份

	[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm

	[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base

  2）合并第1次增量备份到完全备份

	[root@centos8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

  3）合并第2次增量备份到完全备份

	[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2

  4）复制到数据库目录

	[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base

  5）还原属性

	[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql

  6）启动服务

	[root@centos8 ~]#service mysqld start

三、MyCAT实现MySQL读写分离

1 环境准备

# systemctl stop firewalld

# setenforce 0

# 时间同步

2 搭建主从

# master配置

# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id =18

log-bin=/data/logbin/mysql-bin

binlog_format=row

# systemctl restart mysqld

mysql> create user repluser@'10.0.0.%' identified by '123456';

mysql> grant replication slave on *.* to repluser@'10.0.0.%';

mysql> show master logs;

+------------------+-----------+-----------+

| Log_name         | File_size | Encrypted |

+------------------+-----------+-----------+

| mysql-bin.000001 |       179 | No        |

| mysql-bin.000002 |       681 | No        |

+------------------+-----------+-----------+

mysql > source hellodb_innodb.sql

# slave配置

# vi /etc/my.cnf.d/mysql-server

[mysqld]

server-id=28

log-bin=/data/logbin/mysql-bin

binlog_format=row

read_only=on

# mkdir /data/logbin -pv

# chown mysql.mysql /data/logbin

# systemctl restart mysqld

mysql > CHANGE MASTER TO \

MASTER_HOST='10.0.0.18', \

MASTER_USER='repluser', \

MASTER_PASSWORD='123456', \

MASTER_PORT=3306, \

MASTER_LOG_FILE='mysql-bin.000002', \

MASTER_LOG_POS=681;

mysql > start slave;

mysql > show slave status\G

mysql > show databases;

3 安装并启动MyCAT代理

# yum -y install java

# java -version

openjdk version "1.8.0_312"

OpenJDK Runtime Environment (build 1.8.0_312-b07)

OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

# mkdir /apps

# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps

# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

# source /etc/profile.d/mycat.sh

# mycat start

Starting Mycat-server..

# tail /apps/mycat/logs/wrapper.log

...

successfully. see logs in logs/mycat.log

# 在client端测试连接mycat

[root@client ~]#mysql -uroot -p123456 -h 10.0.0.8 -P8066

mysql > show databases;

+----------+

| DATABASE |

+----------+

| TESTDB   |

+----------+

mysql > mysql> use TESTDB	#还并没有真正的与后端数据库建立连接

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

4 修改mycat配置文件

1)# 修改连接端口

# vi /apps/mycat/conf/server.xml

/serverPort		#搜索serverPort，将注释段符号删掉: <!--   -->，将端口号改成3306。配置如下：

	<property name="serverPort">3306</property> <property name="managerPort">9066</property>

	<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>

	<property name="dataNodeIdleCheckPeriod">300000</property>  <!--5 * 60 * 1000L; //连接空闲检查 -->

	<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> 

2）# 更改mycat的默认密码

/password    #配置如下：

    <user name="root" defaultAccount="true">			#连接Mycat的用户名

        <property name="password">magedu</property>		#连接Mycat的密码

        <property name="schemas">TESTDB</property>		#数据库名要和schema.xml相对应

3）# 修改schema.xml实现读写分离策略

# vi /apps/mycat/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="hellodb" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" >

			  writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

		<heartbeat>select user()</heartbeat>

		<writeHost host="host1" url="10.0.0.18:3306" user="root" password="hellodb">

		 <readHost host="host2" url="10.0.0.28:3306" user="root" password="hellodb" />

		</writeHost>

	</dataHost>

</mycat:schema>

4）#重新启动mycat

# mycat restart

5 创建授权用户连接mycat

# 在master配置

mysql > create database mycat;

mysql > create user 'root'@'10.0.0.%' identified by 'hellodb';

mysql > grant all on *.* to root@'10.0.0.%';

6 在client端测试

[root@clietn ~]# mysql -uroot -pmagedu -h10.0.0.8

`验证读操作

mysql> select @@server_id;

+-------------+

| @@server_id |

+-------------+

|          28 |

+-------------+

1 row in set (0.01 sec)

mysql> select @@hostname;

+------------+

| @@hostname |

+------------+

| slave      |

+------------+

1 row in set (0.00 sec)

`验证写操作

mysql > select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.01 sec)

mysql > update teachers set age=@@server_id where tid=4;

mysql> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  18 | F      |

+-----+---------------+-----+--------+

ansible常用模块介绍

ping 模块

检查指定节点机器是否能连通，用法很简单，不涉及参数。如果被检测的主机在线，则返回pong

# ansible 192.168.137.102 -m ping

192.168.137.102 | SUCCESS => {

    "changed": false,

    "ping": "pong"

}

setup模块

setup模块用于收集远程主机的一些基本信息。

常用参数：

filter ：用于进行条件过滤。如果设置，仅返回匹配过滤条件的信息。

#获取ip地址

[root@localhost ~]# ansible 192.168.137.102 -m setup -a "filter=ansible_all_ipv4_addresses"

192.168.137.102 | SUCCESS => {

    "ansible_facts": {

        "ansible_all_ipv4_addresses": [

            "192.168.137.102"

        ]

    },

    "changed": false

}



##获取系统版本

[root@localhost ~]# ansible 192.168.137.102 -m setup -a "filter=ansible_distribution"

192.168.137.102 | SUCCESS => {

    "ansible_facts": {

        "ansible_distribution": "CentOS"

    },

    "changed": false

}

command 模块

command 模块可以帮助我们在远程主机上执行命令，使用的时候可以不用加 -m 指定。command 是ansible 默认使用的模块。（可以在配置文件中修改默认模块）

# default module name for /usr/bin/ansible

#module_name = command

注意：使用command在远程主机执行命令的时候，不会经过shell处理。如果命令带有重定向，管道符等会失效。

# ansible 192.168.137.102 -a 'uptime'

192.168.137.102 | SUCCESS | rc=0 >>

 15:44:41 up  1:33,  2 users,  load average: 0.00, 0.01, 0.05



# ansible 192.168.137.102 -a 'ls /root/'

192.168.137.102 | SUCCESS | rc=0 >>

anaconda-ks.cfg

shell 模块

shell 模块可以帮助我们在远程主机上执行命令。与 command 模块不同的是，shell 模块在远程主机中执行命令时，会经过远程主机上的 /bin/sh 程序处理。

与command模块使用方法类似，只不过支持管道，重定向，变量符等等。由于command比较安全有可预知性，所以我们平时用的时候最好用command。command无法满足需求时，在使用shell。

 首先创建一个shell脚本

 vim  /tmp/test.sh  //加入内容

 #!/bin/bash

 echo `date` > /tmp/ansible_test.txt

 然后把该脚本分发到各个机器上

 ansible testhost -m copy -a "src=/tmp/test.sh dest=/tmp/test.sh mode=0755"

 最后是批量执行该shell脚本

 ansible testhost -m shell -a "/tmp/test.sh"

巴特西

linux_12