Ansible常用模块介绍、mysql日志管理、xtrabackup备份实践、二进制恢复数据、mysql主从复制、mycat读写分离实践

ansible的常用模块

shell模块

功能:用shell执行命令.支持各种符号例:“*,$,>”

PS:此模块不具有幂等性

例:

[root@ansible ~]#ansible websrvs -m shell -a 'echo $HOSTNAME'
10.0.0.8 | CHANGED | rc=0 >>
centos8.localdomain

script

功能:在linux是什么操作系统远程主机上执行anslinuxible服务器上的脚本,无需执行权限

PS:此模块不具有幂等性

[root@ansible ~]# ansible websrvs -m script -a /data/test.sh 

copy

功能:从ansible服务器主控端复制文件到远程主机

PS:src=file 如果时没指明路径,则为当前目录或当前目录下的files目录下的linuxfile文件

#如目标存在,默认覆盖,此处指定先备份
ansible websrvs -m copy -a "src=/root/test1.sh dest=/tmp/test2.sh   owner=shi 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"

file

功能:设置文件属性,创建软连接等

#创建空文件
ansible all -m file  -a 'path=/data/test.txt state=touch'
ansible all -m file  -a 'path=/data/test.txt state=absent'
ansible all -m file -a "path=/root/test.sh owner=wang mode=755"
#创建目录
ansible all -m file -a "path=/data/mysql state=directory owner=mysql group=mysql"
#创建软链接
ansible all -m file -a 'src=/data/testfile path|dest|name=/data/testfile-link state=link'
#创建目录
ansible all -m file  -a 'path=/data/testdir state=directory'
#递归修改目录属性,但不递归至子目录
ansible all -m file -a "path=/data/mysql state=directory owner=mysql group=mysql"
#递归修改目录及子目录的属性
ansible all -m file -a "path=/data/mysql state=directory owner=mysql group=mysql recurse=yes"

stat

功能:检查文件或文件系统的状态

PS:对于windows目标,请改用win_stat模块

linux删除文件命令项:

path:文件/对象的完整路径(必须)

常用的返回值判断

exists: 判断是否存在
isuid: 调用用户的ID与所有者ID是否匹配

例:

- name: install | Check if file is already configured.
 stat: path={{ nginx_file_path }}
 connection: local
 register: nginx_file_result
- name: install | Download nginx file
 get_url: url={{ nginx_file_url }} dest={{ software_files_path }} 
validate_certs=no
 connection: local
 when:,not. nginx_file_result.stat.exists

un数据库系统概论archlinux命令ive

功能:解包解压缩

实现有俩种用法:

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/nginx1.18.0.tar.gz dest=/usr/local/src/ copy=no

Archive

功能:打包压缩保存在被管理节点

ansible websrvs -m archive  -a 'path=/var/log/ dest=/data/log.tar.bz2 format=bz2 owner=wang mode=0600'

Hostname

功能:管理主机名

ansible node1 -m hostname -a "name=websrv"
ansible 10.0.0.18 -m hostname -a 'name=node18.magedu.com'

Yum和Apt模块

功能:yum 管理软件包,只支持 RHEL,CentOS,fedora,不支持Ubuntu其它版本

alinuxpt模块管理Debian相关版本的软件包

[root@ansible ~]#ansible websrvs -m yum -a
"name=https://mirror.tuna.tsinghua.edu.cn/zabbix/zabbix/5.2/rhel/7/x86_64/zabbixagent-5.2.5-1.el7.x86_64.rpm

Servmysql怎么读ice

功能:管理

ansible all -m service -a 'name=httpd state=started enabled=yes'
ansible all -m service -a 'name=httpd state=stopped'
ansible all -m service -a 'name=httpd state=reloaded'
ansible all -m shell -a "sed -i 's/^Listen 80/Listen 8080/' /etc/httpd/conf/httpd.conf"
ansible all -m service -a 'name=httpd state=restarted'

User数据库系统概论第五版课后答案

功能:管理用户

#创建用户
ansible all -m user -a 'name=user1 comment="test user" uid=2048 home=/app/user1 group=root'
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'
#生成123456加密的密码
ansible localhost -m debug -a "msg={{ '123456'| password_hash('sha512','salt')}}"
localhost | SUCCESS => {
    "msg": "$6$salt$MktMKPZJ6t59GfxcJU20DwcwQzfMvOlHFVZiOVD71w."
}
#用上面创建的密码创建用户
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'

Group

功能:管理组

#创建组
ansible websrvs -m group  -a 'name=nginx gid=88 system=yes'
#删除组
ansible websrvs -m group  -a 'name=nginx state=absent'

reboot

[root@ansible ~]#ansible websrvs -m reboot

setup

功能:setup模块来手机系统信息,这些facts信息可以变量的形式使用,但是如果主机较多,会影响执行速度

可以使用 gather_facts: no 来禁止 Ansible 收集 facts 信息

ansible all -m setup
ansible all -m setup -a "filter=ansible_nodename"
ansible all -m setup -a "filter=ansible_hostname"
ansible all -m setup -a "filter=ansible_domain"
ansible all -m setup -a "filter=ansible_memtotal_mb"
ansible all -m setup -a "filter=ansible_memory_mb"
ansible all -m setup -a "filter=ansible_memfree_mb"
ansible all -m setup -a "filter=ansible_os_family"
ansible all -m setup -a "filter=ansible_distribution_major_version"
ansible all -m setup -a "filter=ansible_distribution_version"
ansible all -m setup -a "filter=ansible_processor_vcpus"
ansible all -m setup -a "filter=ansible_all_ipv4_addresses"
ansible all -m setup -a "filter=ansible_architecture"
ansible all -m setup -a "filter=ansible_uptime_seconds"
ansible all -m setup -a "filter=ansible_processor*"
ansible all -m setup -a 'filter=ansible_env'

mysql的日志管理

mysql 的日志分为以下类型

事务日志:transaction log
错误日志:error log
通用日志:general log
慢查询日志:slow query log
二进制日志:binary log
中继日志:reley log 在主从复制架构中用于保存主从服务器的二进制日志中的读取事件

错误日志

mysqld启动和关闭过程中输出的事件信息

mysqld运行中产生的错误信息

在主从复制架构中的mysql安装从服务器上启动从服务器线程时产生的信息

event scheduler运行一个event时产生的日志信息

查看路径

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error' ;
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.02 sec)

自定义日志路径

[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
log-error=/var/log/mysql/mysqld.log   #自定义路径

通用日志

记录对数据库的通用操作,包括错误的sql语句

通用日志可以保存在文件或者mysql.general_log表中

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

二进制日志

记录导致数据库改变过着潜在导致数据库改linux系统变的sql语句

记录已提交的日志

不依赖与存储引擎类型

功能:通过“数据库系统的核心是重放"日centos安装志文件中的时间来生成数据副本

二进制日centos7重置root密码志记录的三种格式

1、基于语句记录:statement.默认模式(mariadb 10.2.3版本一下)日志量较少
2、基于行记录:ROW,记录日志,日志量较大,更加安全建议使用,mysql8.0以上默认格式
3、混合模式:mixed.让系统自行判定基于那种方式进行,默认(mariadb 10.2.4 版本以上)

查看格式

#mysql8.0
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

二进制文件的构成

1、日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002
2、索引文件:mysql|mariadb-bin.index,记录当前已有的魏晋至文件列表

相关配置

sql_log_bin=ON|OFF:#是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项
log_bin=/PATH/BIN_LOG_FILE:#指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED:#二进制日志记录的格式,mariadb5.5默认STATEMENT
max_binlog_size=1073741824:#单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G
#说明:文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0:#设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
expire_logs_days=N:#二进制日志可以自动删除的天数。 默认为0,即不自动删除

二进制日志的查看

mysqlbinlog:系统运维主要做什么二进制日志的客户端命令工具,支持离线查看二进制日志

[root@centos8 rc.d]# ll /usr/bin/mysqlbinlog
-rwxr-xr-x 1 root root 7485304 Sep  1  2021 /usr/bin/mysqlbinlog  #主程序

语法

mysqlbinlog [OPTIONS] log_file…
option:
 --start-position=# 指定开始位置
 --stop-position=#  结束位置
 --start-datetime=  #时间格式:YYYY-MM-DD hh:mm:ss
 --stop-datetime= 结束时间
 --base64-output[=name]
        -v -vvv

[root@centos8 mysql]# mysqlbinlog centos8-bin.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220316 11:12:51 server id 1  end_log_pos 125 CRC32 0xa5f782ff  Start: binlog v 4, server v 8.0.26 created 220316 11:12:51 at startup
ROLLBACK/*!*/;
BINLOG '
c/4xYg8BAAAAeQAAAH0AAAAAAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABz/jFiEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB/4L3pQ==
'/*!*/;
# at 125
#220316 11:12:51 server id 1  end_log_pos 156 CRC32 0xeef94eb1  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

日志格式

# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间:151105 16:31:40
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 431
事件的类型:Query 
事件发生时所在服务器执行此事件的线程的ID:thread_id=1
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
错误代码:error_code=0
事件内容:
GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID

手动切换二进制文件

FLUSH LOGS;

mysql的备份

​ MyISAM:温备,不支持热备

​ InnoDB:都支持

注意:二进制日志文件不应该与数据文件放在同一磁盘

冷备恢复

1、停止数据库服务
2、将数据库所在文件夹拷贝至需恢复的节点,二进制文件相关有特殊设置都需拷贝
3、在新节点配置服务及环境
4、将拷贝数据覆盖新的数据目录

xtrabackup备份

xtrabackup时perlinux操作系统基础知识cona公司提供得一款对mysql数据库得备份工具,是唯一开源可以针数据库原理对数据库进行热备的工具

xtrabackup2.4版本支持mysql5.1linux,5.5,5.6和5.7

xtrabacmysql索引kup8.0版本支持mysql8.0

xtrabackup的安装及使用

#在centos7中EPEL源提供了安装
yum install percona-xtrabackup 
#centos8中取消了提供
[root@centos8 ~]# yum info percona-xtrabackup
Last metadata expiration check: 0:02:37 ago on Fri 18 Mar 2022 05:51:30 AM EDT.
Error: No matching Packages to list
下载链接:https://www.percona.com//XtraBackup/LATEST/
手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

例:xtrabackup8.0实现完全备份数据库及还原

环境mysql8.0.21、xtrabackup8.centos7安装教程0.23

#安装mysql8.0并准备基础数据库
[root@centos8 ~]# yum -y install mysql-server
[root@centos8 ~]# mysql < hellodb_innodb.sql 
[root@centos8 ~]# systemctl enable --now mysqld
[root@centos8 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)
mysql> exit
#准备软件包
[root@centos8 ~]# ls
percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
#安装
[root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm 
#创建备份目录
[root@centos8 ~]# mkdir /backup
#运行程序-u用户名-p密码--backup备份选项--target-dir目标目录只需父目录存在即可
[root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/mysql 
#备份成功后
[root@centos8 ~]# ls /backup/mysql/
backup-my.cnf  ib_buffer_pool  performance_schema  xtrabackup_binlog_info  xtrabackup_tablespaces
binlog.000002  ibdata1         sys                 xtrabackup_checkpoints
binlog.index   mysql           undo_001            xtrabackup_info
hellodb        mysql.ibd       undo_002            xtrabackup_logfile
#将备份数据目录拷贝到目标服务器也可以是备份存放服务器以便恢复
[root@centos8 ~]# scp -r /backup/ 192.168.1.50:/
#在目标服务器还原数据库
#1、预准备:确保数据一直,提交完成的事务,回滚未完成的事务
[root@centos8 /]# yum -y install percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm 
[root@centos8 /]# du -sh /backup/mysql/   #查看源文件大小
74M /backup/mysql/
[root@centos8 /]# xtrabackup --prepare --target-dir=/backup/mysql  #预处理
[root@centos8 /]# du -sh /backup/mysql/   #预处理后文件大小
190M    /backup/mysql/
#2、复制到数据库目录必须保证数据库目录为空,mysqld服务不能启动
[root@centos8 /]# xtrabackup --copy-back --target-dir=/backup/mysql
[root@centos8 /]# ll /var/lib/mysql
total 183332
-rw-r----- 1 root root      156 Mar 18 08:32 binlog.000002
-rw-r----- 1 root root       14 Mar 18 08:32 binlog.index
drwxr-x--- 2 root root      132 Mar 18 08:32 hellodb
-rw-r----- 1 root root     6083 Mar 18 08:32 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 18 08:32 ibdata1
-rw-r----- 1 root root 50331648 Mar 18 08:32 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 18 08:32 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 18 08:32 ibtmp1
drwxr-x--- 2 root root      143 Mar 18 08:32 mysql
-rw-r----- 1 root root 28311552 Mar 18 08:32 mysql.ibd
drwxr-x--- 2 root root     8192 Mar 18 08:32 performance_schema
drwxr-x--- 2 root root       28 Mar 18 08:32 sys
-rw-r----- 1 root root 16777216 Mar 18 08:32 undo_001
-rw-r----- 1 root root 16777216 Mar 18 08:32 undo_002
-rw-r----- 1 root root      459 Mar 18 08:32 xtrabackup_info
-rw-r----- 1 root root        1 Mar 18 08:32 xtrabackup_master_key_id
#还原属性
[root@centos8 /]# chown -R mysql:mysql /var/lib/mysql
#启动服务
[root@centos8 /]# systemctl enable --now mysqld
[root@centos8 /]# mysql    
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

例:xtrabackup8.0实现完全、增量备份数据库及还原

环境mysql8.0.21、xtrabackup8.0.23

#安装mysql8.0并准备基础数据库
[root@centos8 ~]# yum -y install mysql-server
[root@centos8 ~]# mysql < hellodb_innodb.sql 
[root@centos8 ~]# systemctl enable --now mysqld
#准备软件包
[root@centos8 ~]# ls
percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
#安装
[root@centos8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm 
#创建备份目录
[root@centos8 ~]# mkdir /backup
#运行程序 -u用户名 -p密码  --backup备份选项  --target-dir目标目录只需父目录存在即可
[root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/mysql 
#第一次修改数据
mysql> insert teachers values(null,'zhang','25','M');
#第一次增量备份
[root@centos8 backup]# xtrabackup -uroot --backup --target-dir=/backup/zeng1 --incremental-basedir=/backup/mysql
[root@centos8 backup]# ls
mysql  zeng1
#第二次数据修改
mysql> insert teachers values(null,'shi','25','M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers values(null,'ka','25','M');
Query OK, 1 row affected (0.01 sec)
#第二次增量备份
[root@centos8 backup]# xtrabackup -uroot --backup --target-dir=/backup/zeng2 --incremental-basedir=/backup/zeng1
[root@centos8 backup]# ls
mysql  zeng1  zeng2
#将备份数据目录拷贝到目标服务器也可以是备份存放服务器以便恢复
[root@centos8 ~]# scp -r /backup/ 192.168.1.50:/
#在目标服务器还原数据库
#1、预准备:增量备份第一次恢复完全备份添加--apply-log-only 阻止回滚为完成事务
[root@centos8 /]# yum -y install percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm 
[root@centos8 /]# xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql
#合并第一次增量备份到完全备份
[root@centos8 /]# xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql --incremental-dir=/backup/zeng1
#合并第二次增量备份到完全备份,无需加--apply-log-only选项
[root@centos8 /]# xtrabackup --prepare --target-dir=/backup/mysql --incremental-dir=/backup/zeng2
#2、复制到数据库目录必须保证数据库目录为空,mysqld服务不能启动
[root@centos8 /]# xtrabackup --copy-back --target-dir=/backup/mysql
[root@centos8 /]# ll /var/lib/mysql
total 183332
-rw-r----- 1 root root      156 Mar 18 09:02 binlog.000005
-rw-r----- 1 root root       14 Mar 18 09:02 binlog.index
drwxr-x--- 2 root root      132 Mar 18 09:02 hellodb
-rw-r----- 1 root root     6083 Mar 18 09:02 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 18 09:02 ibdata1
-rw-r----- 1 root root 50331648 Mar 18 09:02 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 18 09:02 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 18 09:02 ibtmp1
drwxr-x--- 2 root root      143 Mar 18 09:02 mysql
-rw-r----- 1 root root 28311552 Mar 18 09:02 mysql.ibd
drwxr-x--- 2 root root     8192 Mar 18 09:02 performance_schema
drwxr-x--- 2 root root       28 Mar 18 09:02 sys
-rw-r----- 1 root root 16777216 Mar 18 09:02 undo_001
-rw-r----- 1 root root 16777216 Mar 18 09:02 undo_002
-rw-r----- 1 root root      502 Mar 18 09:02 xtrabackup_info
-rw-r----- 1 root root        1 Mar 18 09:02 xtrabackup_master_key_id
[root@centos8 /]# chown -R mysql:mysql /var/lib/mysql
[root@centos8 /]# systemctl enable --now mysqld
[root@centos8 /]# mysql
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      |
|   5 | zhang         |  25 | M      |
|   6 | shi           |  25 | M      |
|   7 | ka            |  25 | M      |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)

利用二进制日志备份恢复数据库

#安装mysql并启动二进制日志单独存放
[root@centos8 ~]# yum -y install mysql-server
#更改mysql配置文件
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin
[root@centos8 ~]# mkdir /data/mysql -p
[root@centos8 ~]# chown -R mysql:mysql /data/mysql
[root@centos8 ~]# systemctl restart mysqld
[root@centos8 ~]# ll /data/mysql/
total 12
-rw-r----- 1 mysql mysql 179 Mar 18 11:08 mysql-bin.000001
-rw-r----- 1 mysql mysql 156 Mar 18 11:08 mysql-bin.000002
-rw-r----- 1 mysql mysql  58 Mar 18 11:08 mysql-bin.index
#导入测试数据库
[root@centos8 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
#开始备份数据库
[root@centos8 ~]# mysqldump -uroot -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /data/backup/all_`date +%F`.sql.gz
[root@centos8 ~]# ll /data
total 0
drwxr-xr-x 2 root  root   54 Mar 18 11:15 backup
drwxr-xr-x 2 mysql mysql 173 Mar 18 11:15 mysql
[root@centos8 ~]# 
#开始更改数据库
mysql> insert teachers value(null,'zhang',23,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers value(null,'li',23,'F');
Query OK, 1 row affected (0.00 sec)
#破坏数据库
[root@centos8 mysql]# systemctl stop mysqld
[root@centos8 ~]# rm -rf /var/lib/mysql/*
#开始恢复模拟一个全新的数据库
[root@centos8 backup]# systemctl start  mysqld 
[root@centos8 ~]# cd /data/backup/
[root@centos8 backup]# gzip -d all_2022-03-18.sql.gz 
#查看文件中记录的二进制日志序号
[root@centos8 backup]# grep '^-- CHANGE MASTER TO' all_2022-03-18.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=156;
#按照查看序号之后的二进制文件制作.sql
[root@centos8 mysql]# mysqlbinlog mysql-bin.000006 --start-position=156 > /data/backup/zen1.sql
[root@centos8 backup]# mysql
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
mysql> set sql_log_bin=0;      #临时关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/backup/all_2022-03-18.sql    #先恢复全量备份
mysql> source /data/backup/zen1.sql
mysql> set sql_log_bin=1;    #恢复二进制日志的记录
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb;
Database changed
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      |
|   5 | zhang         |  23 | M      |
|   6 | li            |  23 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> 
#日志全部恢复

mysql的主从复制

复制:每个节点都有相同的数据集,向外扩展,基于二进制日志的单向复制

复制的架构

1、一主一从
2、一主多从
3、双主

例:新建主从复制架构

#主节点
[root@centos8 ~]# yum -y install mysql-server 
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
server-id=1     #添加唯一的server-id
log-bin         #开启二进制日志生成中建议将二进制日志单独存放
[root@centos8 ~]# systemctl enable --now mysqld
[root@centos8 ~]# mysql
mysql> show master logs;    #查看二进制文件和位置
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| centos8-bin.000001 |       179 | No        |
| centos8-bin.000002 |       156 | No        |
+--------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> create user master@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'master'@'192.168.1.%';   #账号授权
Query OK, 0 rows affected (0.00 sec)
#从节点
[root@centos8 ~]# yum -y install mysql-server 
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
server-id=2     #添加唯一的server-id
log-bin
[root@centos8 ~]# systemctl enable --now mysqld
[root@centos8 ~]# mysql
mysql>  CHANGE MASTER TO   MASTER_HOST='192.168.1.44',
    -> MASTER_USER='master',   MASTER_PASSWORD='123456',   MASTER_PORT=3306, 
    -> MASTER_LOG_FILE='centos8-bin.000002',   MASTER_LOG_POS=156;   #根据实际情况修改参数
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;  开启复制线程
#测试
#主节点导入数据测试
[root@master ~]# mysql < hellodb_innodb.sql 
[root@slave ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

例:双主复制架构

#主节点
[root@centos8 ~]# yum -y install mysql-server 
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
server-id=1     #添加唯一的server-id
log-bin         #开启二进制日志生成中建议将二进制日志单独存放
auto_increment_offset=1    #开始点
auto_increment_increment=2 #增长点
[root@centos8 ~]# systemctl restart mysqld
[root@centos8 ~]# mysql
mysql> show master logs;     #查看二进制文件和位置
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| centos8-bin.000001 |       179 | No        |
| centos8-bin.000002 |       649 | No        |
| centos8-bin.000003 |       179 | No        |
| centos8-bin.000004 |     11312 | No        |
| centos8-bin.000005 |       156 | No        |
+--------------------+-----------+-----------+
5 rows in set (0.00 sec)
mysql> create user master@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'master'@'192.168.1.%';   #账号授权
Query OK, 0 rows affected (0.00 sec)
#第二个主节点
[root@centos8 ~]# yum -y install mysql-server 
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
server-id=2     #添加唯一的server-id
log-bin
auto_increment_offset=2
auto_increment_increment=2
[root@centos8 ~]# systemctl restart mysqld
[root@centos8 ~]# mysql
mysql>  CHANGE MASTER TO   MASTER_HOST='192.168.1.44',
    -> MASTER_USER='master',   MASTER_PASSWORD='123456',   MASTER_PORT=3306, 
    -> MASTER_LOG_FILE='centos8-bin.000005',   MASTER_LOG_POS=156;   #根据实际情况修改参数
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;  开启复制线程
mysql> show master logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| centos8-bin.000001 |     10990 | No        |
| centos8-bin.000002 |       179 | No        |
| centos8-bin.000003 |       156 | No        |
+--------------------+-----------+-----------+
3 rows in set (0.00 sec)
#在第一个主节点
[root@centos8 ~]# mysql
mysql> CHANGE MASTER TO   MASTER_HOST='192.168.1.50',
    -> MASTER_USER='master',   MASTER_PASSWORD='123456',   MASTER_PORT=3306,
    -> MASTER_LOG_FILE='centos8-bin.000003',   MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.01 sec)7y
mysql> start slave;
#测试在俩个节点分别修改数据库都可以复制到对端

mycat实现读写分离

环境

centos8.0  mycat-server 10.0.0.8
centos8.0  mysql-master 10.0.0.18   mysql8.0
centos8.0  mysql-slave 10.0.0.28    mysql8.0
#关闭防火墙及SELINUX
systemctl  stop firewalld
setenforce 0
# 搭建主节点 10.0.0.18
[root@master ~]# yum -y install mysql-server
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin   
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql
mysql> create user master@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'master'@'10.0.0.%';   #账号授权
Query OK, 0 rows affected (0.00 sec)
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 |       179 | No        |
| master-bin.000002 |       683 | No        |
+-------------------+-----------+-----------+
2 rows in set (0.00 sec)
#搭建从节点 10.0.0.28
[root@slave ~]# yum -y install mysql-server
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin   
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# mysql
mysql> CHANGE MASTER TO   MASTER_HOST='10.0.0.18',
    -> MASTER_USER='master',   MASTER_PASSWORD='123456',   MASTER_PORT=3306, 
    -> MASTER_LOG_FILE='master-bin.000002',   MASTER_LOG_POS=683;
mysql> start slave;
#可以通过show slave status\G;查看状态
#安装mycat 10.0.0.8
[root@mycat ~]#  yum -y install java 
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20220221174943/Mycat-server-1.6.7.6-release-20220221174943-linux.tar.gz  #下载mycat包
[root@mycat ~]# tar xf Mycat-server-1.6.7.6-release-20220221174943-linux.tar.gz -C /usr/local/src/    #解压缩
[root@mycat ~]# echo 'PATH=/usr/local/src/mycat/bin:$PATH' > /etc/profile.d/mycat.sh      #更改环境变量方便使用
[root@mycat ~]# source /etc/profile.d/mycat.sh
[root@mycat ~]# mycat start
[root@mycat ~]# ss -ntl     #8066为链接端口需要等一会
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port   Process    
LISTEN    0         100              127.0.0.1:25               0.0.0.0:*                 
LISTEN    0         1                127.0.0.1:32000            0.0.0.0:*                 
LISTEN    0         128                0.0.0.0:111              0.0.0.0:*                 
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*                 
LISTEN    0         100                  [::1]:25                  [::]:*                 
LISTEN    0         50                       *:1984                   *:*                 
LISTEN    0         128                      *:8066                   *:*                 
LISTEN    0         128                      *:9066                   *:*                 
LISTEN    0         128                   [::]:111                 [::]:*                 
LISTEN    0         50                       *:44435                  *:*                 
LISTEN    0         50                       *:33171                  *:*                 
LISTEN    0         128                   [::]:22                  [::]:*                 
[root@mycat ~]# tail /usr/local/src/mycat/logs/wrapper.log   #也可以查看日志看到成功提示
STATUS | wrapper  | 2022/03/19 11:09:21 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/03/19 11:09:21 | Launching a JVM...
INFO   | jvm 1    | 2022/03/19 11:09:23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/03/19 11:09:23 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/03/19 11:09:23 | 
INFO   | jvm 1    | 2022/03/19 11:09:29 | MyCAT Server startup successfully. see logs in logs/mycat.log
#修改配置文件
[root@mycat ~]# vim /usr/local/src/mycat/conf/server.xml 
    <property name="serverPort">8066</property>   #此行为服务端口删掉注释或重新复制可修改
    <user name="root" defaultAccount="true">     #连接mycat的用户名
        <property name="password">123456</property>   #连接mycat的密码                             <property name="schemas">TESTDB</property>    #显示的虚拟数据库要和schema.xml文件中对应
        <property name="defaultSchema">TESTDB</property>  
    </user>
#
[root@mycat /]# cd /usr/local/src/mycat/conf/
[root@mycat conf]# mv schema.xml schema.xml.bak  #将元配置文件备份
[root@mycat conf]# vim /usr/local/src/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">    #name="TESTDB"要与server.xml文件中的虚拟数据库对应
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="hellodb" />    #database后参数需写入真是数据库名                                                                                                       
 <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="123456">         #连接主mysql
         <readHost host="host2" url="10.0.0.28:3306" user="root"
password="123456" />       # 链接从mysql
 </writeHost>
   </dataHost>
</mycat:schema>
[root@mycat conf]# mycat restart
#在后端服务器添加授权
[root@master ~]# mysql    #应后端主从复制架构所以无需再从服务器创建账号
mysql> create user root@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to 'root'@'10.0.0.%';
#测试链接
[root@mycat conf]# mysql -uroot -p123456 -h 10.0.0.8 -P 8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20220221174943 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show database;
ERROR 1064 (HY000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;   #只能查看到虚拟数据库
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
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
Database changed
mysql> show tables;   #已经可以查看数据
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows 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> insert teachers values(null,'zhang','25','M');
Query OK, 1 row affected (0.05 sec)
mysql> 
[root@master ~]# tail /var/lib/mysql/master.log -f   #主节点
2022-03-19T04:03:12.311866Z    11 Query SET @slave_uuid = 'e389a078-a72d-11ec-9fc2-000c29c6016f', @replica_uuid = 'e389a078-a72d-11ec-9fc2-000c29c6016f'
2022-03-19T04:03:12.312935Z    11 Binlog Dump   Log: 'master-bin.000003'  Pos: 156
2022-03-19T04:03:16.061535Z     8 Query select user()
2022-03-19T04:03:26.061775Z     8 Query select user()   #select user()为健康性检查
2022-03-19T04:05:23.306772Z    12 Query insert teachers values(null,'zhang','25','M')  
2022-03-19T04:05:26.071122Z    13 Query select user()
[root@slave ~]# tail /var/lib/mysql/slave.log -f     #从节点
/usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-03-19T04:03:12.292089Z     5 Connect Out   master@10.0.0.18:3306
2022-03-19T04:03:16.156325Z    10 Connect   root@10.0.0.8 on hellodb using TCP/IP
2022-03-19T04:03:16.157411Z    10 Query select user()
2022-03-19T04:03:26.051781Z    10 Query select user()
2022-03-19T04:03:36.053920Z    10 Query select user()
2022-03-19T04:03:36.058657Z    11 Connect   root@10.0.0.8 on hellodb using TCP/IP
2022-03-19T04:03:36.059709Z    13 Connect   root@10.0.0.8 on hellodb using TCP/IP
2022-03-19T04:03:36.059857Z    12 Connect   root@10.0.0.8 on hellodb using TCP/IP
2022-03-19T04:03:46.052893Z    10 Query select user()
2022-03-19T04:03:56.051841Z    11 Query select user()
2022-03-19T04:04:06.054366Z    13 Query select user()
2022-03-19T04:04:10.421571Z    12 Query select * from teachers
2022-03-19T04:04:16.052390Z    10 Query select user()