1、主从复制及主主复制的实现
主从实现
环境准备:两台centos8;
一台为master:10.0.0.18
一台为slave:10.0.0.28
1.1master配置
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
mkdir -p /data/logbin
chown -R mysql:mysql /data/logbin
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 status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 681 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.2 slave配置
yum -y install mysql-server
systemctl enable --now
vim /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
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=681;
mysql > start slave;
mysql > show slave statusG
*************************** 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
...省略...
验证主从复制
#在master创建数据库;
mysql> create database test;
#slave验证;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
主主复制
在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;
2、xtrabackup实现全量+增量+binlog恢复库
案例: 利用xtrabackup8.0 完全，增量备份及还原MySQL8.0
范例: 最新版本下载安装：
https://www.percona.com/downloads/XtraBackup/LATEST/
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
3、MyCAT实现MySQL读写分离
1 环境准备
时间同步
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 statusG
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> 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。配置如下：
2）更改mycat的默认密码
/password #配置如下：
3）修改schema.xml实现读写分离策略
vi /apps/mycat/conf/schema.xml
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
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 |
+-----+---------------+-----+--------+
4、ansible常用模块介绍
4.1 command 模块
功能：在远程主机执行命令，此为默认模块，可忽略 -m 选项
注意：此命令不支持 $VARNAME < > | ; & 等，可能用shell模块实现
4.2 shell 模块
功能：和command相似，用shell执行命令,支持各种符号,比如:*,$, >是增强版的command模块.
默认情况下需要 -m shell 来指定,可以修改配置文件将默认模块换成shell(也可以默认其他模块)
4.3 script模块
功能：在远程主机上运行ansible服务器上的脚本(无需执行权限)
4.4 copy模块
功能：从ansible服务器主控端复制文件到远程主机
4.5fetch模块
功能：从远程主机提取文件至ansible的主控端，copy相反，目前不支持目录
4.6 file 模块
功能：设置文件属性,创建软链接等
创建文件和文件夹
文件 state=touch
文件夹 state=directory
软连接 state=link src=xxx dest=xxx state=link
递归修改 recurse=yes
删除 state=absent
4.7get_url 模块
功能:从网络下载,可以加checksum校验md5码
4.8stat 模块
功能:查看文件属性
4.9unarchive 模块
功能:将ansible服务器上文件传送到远程主机,并进行解压缩
如果是需要将文件传到远程copy=yes(默认值,可省略)
如果解压远程文件 copy=no
4.10archive模块
功能:打包压缩文件并保存在被管理节点,和unarchive正好相反
4.11hostname 模块
功能：管理主机名
4.12 cron模块
功能：计划任务
4.13 yum和apt模块
功能:安装,卸载,查看yum或apt包
4.14yum_repository模块
功能:用来直接在playbook中配置yum仓库
4.15 service 模块
功能:启停服务
4.16USER模块
功能:创建删除用户
4.17 group 模块
功能:创建删除组
4.18 lineinfile 模块
功能:类似sed,修改时多行匹配,只匹配修改最后一个,删除是多行匹配,全部删除
4.19Replace模块
功能: 和lineinfile类似,但可以匹配替换所有行,可以使用正则
ansible 192.168.31.31 -m replace -a “path=/etc/fstab regexp=’^(UUID.*)’ replace=’#1’”
4.20 SELinux模块
功能:配置selinux
4.21reboot模块
功能:重启被控服务器
4.22mount模块
功能:挂载设备
4.23setup 模块
功能:显示远程服务器的状态
4.24 debug模块
功能:此模块可以用于输出信息,并且通过 msg 定制输出的信息内容
