12.数据库mycat读写分离与ansible模块介绍

最新推荐文章于 2024-04-06 07:30:00 发布

江小宝

最新推荐文章于 2024-04-06 07:30:00 发布

阅读量320

点赞数

文章标签：数据库 mariadb mysql

本文链接：https://blog.csdn.net/weixin_50619719/article/details/124258130

版权

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

1.1 主从复制原理

主从复制相关线程

主节点，dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点： I/O Thread：向Master请求二进制日志事件，并保存于中继日志中； SQL
Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

1.2 实现主从复制配置

主节点配置：

启用二进制日志；
为当前节点设置一个全局惟一的ID号；
查看从二进制日志的文件和位置开始进行复制；
创建有复制权限的用户账号；

root@mariadbMaster:~# cat /etc/mysql/conf.d/mariadb-server.cnf
[mysqld]
server-id = 1
log-bin
root@mariadbMaster:~#
root@mariadbMaster:~# mysql -e 'show master status'
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000006 |      328 |              |                  |
+-------------------+----------+--------------+------------------+
root@mariadbMaster:~#
MariaDB [(none)]> select user,password from mysql.user ;
+------+-------------------------------------------+
| user | password                                  |
+------+-------------------------------------------+
| root |                                           |
| repl | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| jd   | *425A54CAD84C0D0B94382617463EBEF7EF4A2916 |
+------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

从节点配置：

启动中继日志；

使用有复制权限的用户账号连接至主服务器，并启动复制线程；

 CHANGE MASTER TO MASTER_HOST='masterhost',
 MASTER_USER='repluser',
 MASTER_PASSWORD='replpass',
 MASTER_LOG_FILE='mariadb-bin.xxxxxx',
 MASTER_LOG_POS=#;

root@ubuntu1804:~# cat /etc/mysql/conf.d/mariadb-server.cnf
[mysqld]
server-id = 2
root@ubuntu1804:~# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.118
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000006
          Read_Master_Log_Pos: 328
               Relay_Log_File: mysqld-relay-bin.000018
                Relay_Log_Pos: 617
        Relay_Master_Log_File: mysqld-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
               Master_SSL_Key:
        Seconds_Behind_Master: 0

检查数据是否同步：

在这里插入图片描述

1.3 实现主主复制配置

主节点配置：

各节点使用一个惟一server_id；
都启动binary log和relay log；
创建拥有复制权限的用户账号；
定义自动增长id字段的数值范围各为奇偶；

均把对方指定为主节点，并启动复制线程。

CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;

root@mariadbMaster:~# cat !$                 #第一个节点
cat /etc/mysql/conf.d/mariadb-server.cnf
[mysqld]
server-id = 1
log-bin
general_log=ON
auto_increment_offset=1
auto-increment_increment=2

root@mariadbMaster:~#

root@ubuntu1804:~# cat /etc/mysql/conf.d/mariadb-server.cnf  #第2个节点
[mysqld]
log-bin
server-id = 2
general_log=ON
auto_increment_offset=2
auto_increment_increment=2
root@ubuntu1804:~#

查看是否同步：

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

2.1 准备数据库

[root@localhost ~]# yum install -y mysql-server
[root@localhost ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log-bin=/data/mysql/mysql-bin
server-id=1
[root@localhost ~]# mkdir /data/mysql
[root@localhost ~]# chown -R mysql.mysql /data/mysql

2.2 安装xtrabackup并备份

先做一次完全备份
第一次修改数据库数据后做第一次增量备份
第二次修改数据库数据后做第二次增量备份

[root@localhost ~]# yum install -y percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@localhost ~]# mkdir /backup
[root@localhost ~]# xtrabackup -uroot -p'Test@123' -H'192.168.1.32' --backup --target-dir=/backup/base

mysql> insert teachers value(5,'jiangde',40,'M');
mysql> delete from teachers where name="Zhang Sanfeng";
[root@localhost ~]# xtrabackup -uroot -p'Test@123' -H'192.168.1.32' --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base


mysql> insert teachers value(6,"HY",32,'F');
mysql> delete from teachers where TID=3;
mysql> select * from teachers;
+-----+--------------+-----+--------+
| TID | Name         | Age | Gender |
+-----+--------------+-----+--------+
|   1 | Song Jiang   |  45 | M      |
|   4 | Lin Chaoying |  93 | F      |
|   5 | jiangde      |  40 | M      |
|   6 | HY           |  32 | F      |
+-----+--------------+-----+--------+
[root@localhost ~]# xtrabackup -uroot -p'Test@123' -H'192.168.1.32' --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

#后面和老板吵架，删掉了一个表及修改了数据。这部分需要用binlog来恢复。
mysql> drop table students;
mysql> insert teachers value(7,"felix",19,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+--------------+-----+--------+
| TID | Name         | Age | Gender |
+-----+--------------+-----+--------+
|   1 | Song Jiang   |  45 | M      |
|   4 | Lin Chaoying |  93 | F      |
|   5 | jiangde      |  40 | M      |
|   6 | HY           |  32 | F      |
|   7 | felix        |  19 | M      |
+-----+--------------+-----+--------+


查看量备份的位置
[root@localhost mysql]# cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000001        11222

mysql> show binlog events in 'mysql-bin.000001'\G
...省略部分输出...
*************************** 114. row ***************************
   Log_name: mysql-bin.000001
        Pos: 11287
 Event_type: Query
  Server_id: 1
End_log_pos: 11414
       Info: use `hellodb`; DROP TABLE `students` /* generated by server */
*******
...省略部分输出...
******************** 119. row ***************************
   Log_name: mysql-bin.000001
        Pos: 11662
 Event_type: Xid
  Server_id: 1
End_log_pos: 11693
       Info: COMMIT /* xid=247 */
119 rows in set (0.01 sec)
mysql> 

[root@localhost ~]# mysqlbinlog --start-position=11222 /data/mysql/mysql-bin.000001 > binlog.sql
#备份增量后的二进制数据

2.3 恢复数据

预准备完成备份，此选项–apply-log-only 阻止回滚未完成的事务
合并第1次增量备份到完全备份
合并第2次增量备份到完全备份：最后一次还原不需要加选项–apply-log-only
复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
使用binlog恢复删除的表及后面的数据。


[root@localhost mysql]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@localhost mysql]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
[root@localhost mysql]# xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2

#复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@localhost mysql]# systecmctl stop mysqld
[root@localhost mysql]# rm -rf ./*
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/backup/base
[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql
[root@localhost mysql]# systemctl start mysql


[root@localhost mysql]# sed -i.bak '/^DROP TABLE/d' /root/binlog.sql   #去掉删除表的语句

mysql> set sql_log_bin=0;
mysql> source /root/binlog.sql
mysql> set sql_log_bin=0;
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | jiangde       |  40 | M      |
|   7 | felix         |  19 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
mysql>

3、MyCAT实现MySQL读写分离

3.1 环境准备

mycat-server 192.168.1.33
mysql-master 192.168.1.42 MySQL 8.0 或者Mariadb 10.3.17
mysql-slave 192.168.1.41 MySQL 8.0 或者Mariadb 10.3.17

3.2 MySQL主从复制

前面已有，不再赘述。

3.3 安装mycat并配置

安装java环境
安装mycat(源码安装）

[root@CentOS7 apps]# yum install -y java
[root@CentOS7 apps]#tar xf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
[root@CentOS7 apps]#echo "PATH=/apps/mycat/bin:$PATH" > /etc/profile.d/mycat.sh
[root@CentOS7 apps]#source /etc/profile.d/mycat.sh
[root@CentOS7 profile.d]# cd /apps
[root@CentOS7 apps]# mycat start
Starting Mycat-server...
[root@CentOS7 apps]# ss -ntl
[root@CentOS7 apps]# mysql -uroot -p123456 -h 192.168.1.33 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...省略部分输入...
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

MySQL [(none)]>

修改mycat相关配置

#修改连接数据库的端口号为3306
[root@CentOS7 mycat]# cat conf/server.xml | grep serverPort
        <property name="serverPort">3306</property>

[root@CentOS7 mycat]# cat conf/schema.xml
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" DataNode="dn1">
        <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="192.168.1.42:3306" user="root" password="Test@123">
        <readHost host="host2" url="192.168.1.41:3306" user="root" password="Test@123" />
        </writeHost>
#balance改为1，表示读写分离。以上配置达到的效果就是192.168.1.42为主库，192.168.1.41为从库

对mycat进行授权

MariaDB [(none)]> grant all on *.* to 'root'@'192.168.1.%' identified by 'Test@123';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> flush privileges;

3.4 测试

可以开启general_log日志，也可以查看到相关信息。

[root@CentOS7 mycat]# mysql -uroot -p123456 -h192.168.1.33 -P3306

MySQL [(none)]> use TESTDB;
Database changed
MySQL [TESTDB]> create table t1(id int);      #在后端数据库上查看到已创建了表。
Query OK, 0 rows affected (0.04 sec)

MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)


#关闭从服务器，会调度至主服务器（停止主节点，MyCAT不会自动调度写请求至从节点，而是报错）
[root@localhost my.cnf.d]# systemctl stop mariadb

MySQL [(none)]> select @@server_id;   #mycat服务器上查看
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

4、ansible常用模块介绍

ansible模块众多，到目前为止已有3000多个，但是常用的模块也只有2，30个而已，针对特定业务只用10几个模块。

4.1 Command 模块

在远程主机执行命令，此为默认模块，可忽略 -m 选项。此命令不支持 $VARNAME < > | ; & 等，可能用shell模块实现。
注意：此模块不具有幂等性

参数	含义
chdir	执行命令前，切换至目录
creates	当该文件存在时，则不执行该步骤
executable	换用shell环境执行命令
free_form	需要执行的脚本
removes	当该文件不存在时，则不执行该步骤
warn	如果在ansible.cfg中存在告警，如果设定了False，不会警告此行。

[root@ansible ~]#ansible websrvs -m command -a 'chdir=/etc cat centos-release'
		10.0.0.7 | CHANGED | rc=0 >>
		CentOS Linux release 7.7.1908 (Core)
		10.0.0.8 | CHANGED | rc=0 >>
		CentOS Linux release 8.1.1911 (Core)
[root@ansible ~]#ansible websrvs -m command -a 'chdir=/etc creates=/data/f1.txt cat centos-release'
		10.0.0.7 | CHANGED | rc=0 >>
		CentOS Linux release 7.7.1908 (Core)
		10.0.0.8 | SUCCESS | rc=0 >>
		skipped, since /data/f1.txt exists

4.2 Shell模块

ansible shell模块主要用于远程客户端上执行各种shell命令或者运行脚本，支持比command更多的指令，shell模块使用详解：

[root@localhost ansible]# ansible h1 -m shell -a 'ls -l /etc/shadow'
192.168.1.42 | CHANGED | rc=0 >>
---------- 1 root root 849 Apr 19 23:13 /etc/shadow
192.168.1.41 | CHANGED | rc=0 >>
---------- 1 root root 809 May  6 22:43 /etc/shadow
[root@localhost ansible]# ansible h1 -m shell -a 'echo hello > /data/hello.log'
192.168.1.41 | CHANGED | rc=0 >>

192.168.1.42 | CHANGED | rc=0 >>

4.3 File模块

ansible file模块主要用于对文件的创建、删除、修改、权限、尾性的维护和管理，file模块使用详解：

参数	含义
src	ansible端源文件或者目录
follow	支持link文件拷贝
force	覆盖远程主机不一致的内容
group	设定远程主机文件夹的组名
mode	设定远程主机文件夹及文件的权限
owner	设定远程主机文件夹的用户名
path	目标路径，也可以用dest,name代替
state	状态包括：file、link、directory、hard、touch、absent;
attributes	文件或者目录特殊属性

[root@localhost ansible]# ansible h1 -m file -a "path=/data/test.txt state=touch mode=755"
192.168.1.41 | CHANGED => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/libexec/platform-python"
    },
    "changed": true,
    "dest": "/data/test.txt",
    "gid": 0,
    "group": "root",
    "mode": "0755",
    "owner": "root",
    "size": 0,
    "state": "file",
    "uid": 0
}
192.168.1.42 | CHANGED => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/libexec/platform-python"
    },
    "changed": true,
    ...省略部分输出...
    
[root@localhost ansible]#ansible all -m file -a "path=/data/mysql state=directory owner=mysql	group=mysql"
#创建目录,目标主机如果没有mysql会报错

4.4 Copy 模块

ansible copy 模块用于服务器主控端复制文件到远程主机
注意: src=file 如果是没指明路径,则为当前目录或当前目录下的files目录下的file文件

参数	含义
src	ansible端源文件或者目录，空文件夹不拷贝
content	用于将指定文件的内容，拷贝到远程文件内
force	覆盖远程主机不一致的内容
group	设定远程主机文件夹的组名
mode	设定远程主机文件夹及文件的权限
owner	设定远程主机文件夹的用户名
dest	客户端目标目录或文件，需要绝对路径
backup	拷贝之前，先备份远程节点上的原始文件
follow	支持link文件拷贝

#如目标存在，默认覆盖，此处指定先备份
ansible websrvs -m copy -a "src=/root/test1.sh dest=/tmp/test2.sh owner=wang mode=600 backup=yes"
	
#指定内容，直接生成目标文件
ansible websrvs -m copy -a "content='test line1\ntest line2\n' dest=/tmp/test.txt"
	
#复制/etc目录自身,注意/etc/后面没有/
ansible websrvs -m copy -a "src=/etc dest=/backup"
	
#复制/etc/下的文件，不包括/etc/目录自身,注意/etc/后面有/
ansible websrvs -m copy -a "src=/etc/ dest=/backup"

4.5 yum模块

ansible copy 模块用于软件的安装、升级、卸载，支持redhat rpm软件的管理，详解如下：

参数	含义
conf_file	设定远程yum执行时所依赖的yum配置文件
disable_gpg_check	安装软件包之前是否检查gpg key
name	需要安装的软件名称，支持软件组安装
update_cacke	安装软件前更新缓存
enablerepo	指定repo源名称
state	软件包状态，包括：installed、present、latest、absent、removed

#安装
ansible websrvs -m yum -a 'name=httpd state=present'

#启用epel源进行安装
ansible websrvs -m yum -a 'name=nginx state=present enablerepo=epel' 

#升级除kernel和foo开头以外的所有包
ansible websrvs -m yum -a 'name=* state=lastest exclude=kernel*,foo*' 

#删除
ansible websrvs -m yum -a 'name=httpd state=absent'

4.6 user模块

ansible copy 模块用于系统用户、组、权限、密码等操作。

参数	含义
system	默认创建为普通用户，yes则创建为系统用户
append	添加一个新的组
createhome	给用户创建家目录
group	创建用户主组
home	指定家目录
password	指定用户的密码，此处为加密密码
udi	设置用户uid
state	用户状态，默认为present，表示新建用户

#创建用户
ansible all -m user -a 'name=nginx comment=nginx uid=88 group=nginx groups="root,daemon" shell=/sbin/nologin system=yes create_home=no home=/data/nginx non_unique=yes'

#remove=yes表示删除用户及家目录等数据,默认remove=no
ansible all -m user -a 'name=nginx state=absent remove=yes'

ansible websrvs -m user -a 'name=test password="$6$salt$MktMKPZJ6t59GfxcJU20DwcwQzfMvOlHFVZiOVD71w."'

#创建用户test,并生成4096bit的私钥
ansible websrvs -m user -a 'name=test generate_ssh_key=yes ssh_key_bits=4096 ssh_key_file=.ssh/id_rsa'

4.6 unarchive 模块

ansible unarchive模块用于解包解压缩，实现有两种用法：
1、将ansible主机上的压缩包传到远程主机后解压缩至特定目录，设置copy=yes,此为默认值,可省略
2、将远程主机上的某个压缩包解压缩到指定路径下，设置copy=no

参数	含义
copy	默认为yes，当copy=yes，拷贝的文件是从ansible主机复制到远程主机上，如果设置为copy=no，会在远程主机上寻找src源文件
remote_src	和copy功能一样且互斥，yes表示在远程主机，不在ansible主机，no表示文件在ansible主机上
src	源路径，可以是ansible主机上的路径，也可以是远程主机(被管理端或者第三方主机)上的路径，如果是远程主机上的路径，则需要设置copy=no
dest	远程主机上的目标路径
mode	设置解压缩后的文件权限

ansible all -m unarchive -a 'src=/data/foo.tgz dest=/var/lib/foo owner=wang group=bin'
ansible all -m unarchive -a 'src=/tmp/foo.zip dest=/data copy=no mode=0777'
ansible all -m unarchive -a 'src=https://example.com/example.zip dest=/data	copy=no'
ansible websrvs -m unarchive -a 'src=https://releases.ansible.com/ansible/ansible-2.1.6.0-0.1.rc1.tar.gz dest=/data/ owner=root remote_src=yes'
ansible websrvs -m unarchive -a 'src=http://nginx.org/download/nginx-1.18.0.tar.gz dest=/usr/local/src/ copy=no

4.7 Get_url 模块

ansible get_url模块用于将文件从http、https或ftp下载到被管理机节点上，参数如下：

参数	含义
url	下载文件的URL,支持HTTP，HTTPS或FTP协议
dest	下载到目标路径（绝对路径），如果目标是一个目录，就用服务器上面文件的名称，如果目标设置了名称就用目标设置的名称
force	如果yes，dest不是目录，将每次下载文件，如果内容改变，替换文件。如果否，则只有在目标不存在时才会下载该文件
mode	指定权限
checksum	目标文件在下载后计算摘要，以确保其完整性
url_username	用于HTTP基本认证的用户名
url_password	用于HTTP基本认证的密码
timeout	URL请求的超时时间,秒为单位

[root@ansible ~]#ansible websrvs -m get_url -a 'url=http://nginx.org/download/nginx-1.18.0.tar.gz dest=/usr/local/src/nginx.tar.gz checksum="md5:b2d33d24d89b8b1f87ff5d251aa27eb8"'

4.8 Lineinfile 模块

一般在ansible当中去修改某个文件的单行进行替换的时候需要使用lineinfile模块。
regexp参数：使用正则表达式匹配对应的行，当替换文本时，如果有多行文本都能被匹配，则只有最后面被匹配到的那行文本才会被替换，当删除文本时，如果有多行文本都能被匹配，这么这些行都会被删除。如果想进行多行匹配进行替换需要使用replace模块
功能：相当于sed，可以修改文件内容

ansible websrvs -m lineinfile -a "path=/etc/httpd/conf/httpd.conf regexp='^Listen' line='Listen 80'"
ansible all -m lineinfile -a "path=/etc/selinux/config regexp='^SELINUX=' line='SELINUX=disabled'"