1、主从复制及主主复制的实现
主从复制
- 完成主设备配置
[root@centos8mini ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin #开启二进制日志
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=202 #配置serverid
#完成主设备数据库的全量备份
mysqldump -A -F --single-transaction --master-data=1 > /data/fullbackup_`date +%F`.sql
#将备份文件复制给从服务器
rsync -a /data/fullbackup_2021-12-04.sql 192.168.156.204:/data/
#在主设备上创建用于同步的账号，这里没加密码
create user 'mxx2'@'192.168.156.%';
grant replication slave on *.* to 'mxx2'@'192.168.156.%';
- 完成从设备配置
[root@centos8mini ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=204 #配置从设备的server id
read-only #将从设备设置为只读
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
#编辑主设备复制过来的数据库备份文件，修改MASTER CHANGE TO，指定主设备的服务器IP和用于复制的账号等信息；
[root@centos8mini ~]# vim /data/fullbackup_2021-12-04.sql
CHANGE MASTER TO
MASTER_HOST='192.168.156.202',
MASTER_USER='mxx2',
MASTER_PASSWORD='',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos8mini-bin.000008', #从文件中已存在的MASTER CHANGE TO记录里复制；
MASTER_LOG_POS=156; #从文件中已存在的MASTER CHANGE TO记录里复制；
[root@centos8mini ~]# cat .mysql_history | sed -r -n 's/\\040/ /g;p'
_HiStOrY_V2_
exit
select @@sql_log_bin; #查看目前二进制日志的开启状态
set sql_log_bin=0; #恢复前先将二进制日志功能临时关闭
system ls /data
source /data/fullbackup_2021-12-04.sql #从文件中恢复数据
set sql_log_bin=1; #重新开启二进制日志
show slave status \134G #此时查看状态，两个线程都是off的
start slave; #开启IO_THREAD和SQL_THREAD
12:28:59(root@localhost) [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.156.202
Master_User: mxx2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos8mini-bin.000008
Read_Master_Log_Pos: 1445
Relay_Log_File: centos8mini-relay-bin.000002
Relay_Log_Pos: 1619
Relay_Master_Log_File: centos8mini-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1445
Relay_Log_Space: 1834
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #与主设备同步的时间差
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 202
Master_UUID: 2de26d69-54ae-11ec-97a1-000c29489326
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
#通过主从复制，同步了mxx2账号
12:30:17(root@localhost) [(none)]> select user,host from mysql.user;
+------------------+---------------+
| user | host |
+------------------+---------------+
| mxx2 | 192.168.156.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+---------------+
5 rows in set (0.00 sec)
主主复制：在主从复制的基础上
- 首先，调整主备设备的配置文件，防止主键冲突
#master设备配置：
[root@centos8mini ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=202
auto_increment_offset=1
auto_increment_increment=2 #主设备使用奇数执行主键递增
#slave配置
[root@centos8mini ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin #slave开启二进制日志，因为它也将成为Master可以执行写操作
server-id=204
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
auto_increment_offset=2 #Slave使用偶数执行主键自动递增
auto_increment_increment=2
- 查看备机上的二进制日志状态，直接show master status\G查看目前正在使用的二进制日志文件及Position即可。
因为主从的数据库已经是同步的，现在要做的只是让后续从节点的所有写操作能被同步给主节点，所以只需要查看当前最新的二进制日志记录：
12:40:31(root@localhost) [(none)]> show master status\G
*************************** 1. row ***************************
File: centos8mini-bin.000003 #记录下最新的即可
Position: 9997643
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
- 在主节点上配置CHANGE MASTER TO，指向从节点：
mysql
CHANGE MASTER TO
MASTER_HOST='192.168.156.204',
MASTER_USER='mxx02',
MASTER_PASSWORD='',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos8mini-bin.000003',
MASTER_LOG_POS=9997643;
slave start，启动IO和SQL线程
确认状态：
12:46:38(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.156.204
Master_User: mxx2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos8mini-bin.000003
Read_Master_Log_Pos: 9997643
Relay_Log_File: centos8mini-relay-bin.000008
Relay_Log_Pos: 383
Relay_Master_Log_File: centos8mini-bin.000003
Slave_IO_Running: Yes #线程已启动
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 9997643
Relay_Log_Space: 651
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #同步已完成
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 204
Master_UUID: 33e02530-54ae-11ec-8b77-000c297ca49a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
2、xtrabackup实现全量+增量+binlog恢复库
1、执行全量备份
yum -y install percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
[root@centos8 ~]#mkdir /data/backup
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
2、执行增量备份
#模拟数据变更
[root@localhost ~]# mysql
MariaDB [hellodb]> insert into students(name,age,classid,teacherid) values('mxx',18,3,1);
MariaDB [hellodb]> create database db1;
#执行第1次增量备份
xtrabackup -uroot -proot --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
3、最后的binlog备份的准备
#模拟在最后一次增量备份后产生了新的写操作
MariaDB [db1]> create table t1(id smallint unsigned primary key auto_increment, name varchar(20), age smallint);
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]>
MariaDB [db1]>
MariaDB [db1]> insert into db1.t1(name,age) values('mxx2',19);
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]>
#记录
[root@localhost ~]# cat /data/backup/inc1/xtrabackup_binlog_info
mariadb-bin.000001 7510
4、将完全备份和增量备份的文件先复制给新主机，将数据库先还原到最后一次备份的时间点
scp -r /data/backup/base 192.168.11.5:/data/backup/
scp -r /data/backup/inc1 192.168.11.5:/data/backup/
yum -y install mariadb-server #安装的5.5版本
yum -y install percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
systemctl stop mariadb
#第一次全量备份的预准备，确保数据一致，但不要执行回滚，因为增量中可能包含未完成事务的另外一部分
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
#对增量备份执行回滚，将增量备份的数据合并到全量备份中
xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
#将数据库目录下的所有内容都删除
ls /var/lib/mysql
rm -rf /var/lib/mysql/*
ls /var/lib/mysql
#将合并增量后的全量备份恢复到数据库中
xtrabackup --copy-back --target-dir=/backup/base
xtrabackup --copy-back --target-dir=/data/backup/base
#启动mariadb数据库，需要先调整数据库目录内所有文件的权限，否则服务无法启动
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ~]# systemctl restart mariadb
5、信息查看
#可以看到之前增量备份的mxx用户数据已经恢复成功了
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | mxx | 18 | F | 3 | 1 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
#但增量备份后的数据没有恢复
MariaDB [db1]> show tables;
Empty set (0.00 sec)
6、在主服务器上备份最后一次增量后的二进制日志
#开始文件和position信息都是之前记录过的
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mariadb-bin.000001 --start-position=7510 > /data/backup/inc.sql
[root@localhost ~]# scp /data/backup/inc.sql 192.168.11.5:/data/backup/
root@192.168.11.5's password:
inc.sql 100% 2210 2.9MB/s 00:00
[root@localhost ~]#
7、恢复二进制日志备份的数据
[root@localhost ~]# mysql < /data/backup/inc.sql
#二进制日志的数据也完成了恢复
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from t1;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | mxx2 | 19 |
+----+------+------+
1 row in set (0.00 sec)
3、MyCAT实现MySQL读写分离
- 文件的下载和安装
yum -y install java，确认版本是1.8.0
- 初始化准备
mkdir /apps
tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh
- 修改server.xml配置文件，调整端口为3306，以及账号的配置
[root@centos8mini ~]# cat /apps/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
<property name="useHandshakeV10">1</property>
<property name="removeGraveAccent">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒-->
<property name="sequnceHandlerType">1</property>
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
-->
<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<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> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<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="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">0</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">false</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
<property name="parallExecute">0</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<user name="root" defaultAccount="true">
<property name="password">root</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
<property name="defaultSchema">TESTDB</property>
</user>
</mycat:server>
- 修改schema.xml配置文件
[root@centos8mini ~]# cat /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>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.156.202:3306" user="root"
password="root">
<readHost host="hostM2" url="192.168.156.204:3306" user="root" password="root"/>
</writeHost>
</dataHost>
</mycat:schema>
- 启动mycat
mycat start
- 测试与确认
#客户端先修改一下配置文件，以方便之后的连接
[root@centos8mini ~]# vim /etc/my.cnf.d/client.cnf
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#
[mysql]
prompt="\\R:\\m:\\s(\\u@\\h) [\\d]>\\_"
[client]
user=root
host=192.168.156.207
password=root
# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
#通过客户端连接到Mycat中间件
[root@centos8mini ~]# mysql
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-20210303094759 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.
13:38:36(root@192.168.156.207) [(none)]>
13:38:37(root@192.168.156.207) [(none)]>
13:38:37(root@192.168.156.207) [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
13:38:44(root@192.168.156.207) [(none)]> 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
13:38:51(root@192.168.156.207) [TESTDB]>
13:38:52(root@192.168.156.207) [TESTDB]>
13:38:52(root@192.168.156.207) [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| t1 |
| teachers |
| testlog |
| toc |
+-------------------+
9 rows in set (0.00 sec)
13:38:56(root@192.168.156.207) [TESTDB]> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | mxx |
| 2 | mxx3 |
| 4 | mxx4 |
+----+------+
3 rows in set (0.13 sec)
3.1 启动mycat服务时出现了好几种错误
通过查看/apps/mycat/logs/wrapper.log可以看到服务启动的报错信息，特别长的那句就是：
WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionInInitializerError
- 这种错误可能是schema.xml语法错误，比如第一行前多了空格;或者使用的对象不存在等，我遇到的是初始配置文件中使用的
randomDataNode，改成dataNode就ok了，另外table等没有使用的都全部注释掉（可能因为randomDataNode和dataNode是两个不同的属性，而我后续配置中使用的是dataNode）
- 使用randomDataNode会提示错误，元素未声明，可能因为使用了randomData必须要配置table
Caused by: io.mycat.config.util.ConfigException: schema TESTDB didn't config tables,so you must set dataNode property!
关于randomDataNode：
randomDataNode是新加的属性，也就是在配置上有dataNode属性也有randomDataNode属性
一些非DQL语句在在没有randomDataNode属性前是随机发送的
有了randomDataNode语句可以指定一个节点而不是随机发送
- 还有些人是java版本过低与mycat不匹配，安装到1.8的JDK后就正常了
Caused by: org.xml.sax.SAXParseException; lineNumber: 4; columnNumber: 80; Attribute "DataNode" must be declared for element type "schema".
- DataNode元素在schema中没有声明？这个是因为在schema元素下的dataNode属性错误写成了DataNode导致的
Caused by: org.xml.sax.SAXParseException; lineNumber: 14; columnNumber: 16; The content of element type "dataHost" must match "(heartbeat,connectionInitSql*,writeHost+)".
- 这是因为写readHost和writeHost时，写错了，writeHost应该是如下写法，将
<readHost>标签写在<writeHost>和</writeHost>里面；readHost是与writeHost绑定的； - 另外这句错误提示也告知了，dataHost的内容是有顺序的，应该是按照(heartbeat,connectionInitSql*,writeHost+)这样的顺序来写才行
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.156.202:3306" user="root" password="root">
<readHost host="hostM2" url="192.168.156.204:3306" user="root" password="root"/>
</writeHost>
</dataHost>
Caused by: org.xml.sax.SAXParseException; lineNumber: 16; columnNumber: 16; The content of element type "mycat:schema" must match "(schema*,dataNode*,dataHost*)".
- 同样是顺序问题，我把
<table>标签写错位置了，放到了dataNode里，这句主要是提示顺序问题，配置文件中的元素都是有顺序的
其他的比如什么标签未终结之类的简单排查就不用多说了；
#启动成功的日志
[root@centos8mini ~]# tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2021/12/05 09:29:32 | at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO | jvm 1 | 2021/12/05 09:29:32 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO | jvm 1 | 2021/12/05 09:29:32 | ... 13 more
STATUS | wrapper | 2021/12/05 09:29:34 | <-- Wrapper Stopped
STATUS | wrapper | 2021/12/05 09:31:59 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/12/05 09:31:59 | Launching a JVM...
INFO | jvm 1 | 2021/12/05 09:32:00 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/12/05 09:32:00 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/12/05 09:32:00 |
INFO | jvm 1 | 2021/12/05 09:32:03 | MyCAT Server startup successfully. see logs in logs/mycat.log #确定启动成功
4、ansible常用模块介绍
Command和shell模块：用于执行shell命令，建议使用shell模块，可以支持"|、$VARNAME、<、>、;、&"等符号；
Script模块：可用于执行脚本，并且脚本文件无需执行权限
Copy模块：Control主机文件复制到被控主机，用于执行文件复制
get_url模块：通过http、https或ftp下载文件到被控主机
fetch模块：反向复制文件，从被控主机复制到control主机，但是只能复制文件，不能复制目录
file模块：touch、mkdir、chmod、ln等的集合体，能够用于创建文件、目录、链接等；
stat模块：能够获取文件的各种状态信息，这些信息可以存放到变量中，可以配合返回的值执行不同的操作
unarchive模块：将包传到远程主机并解压缩，包括三种用途：将control主机的包发送到被控主机并解压缩；将被控主机本地的包解压缩；将其他远程主机的包复制到被控主机并解压缩；
archive模块：用于执行打包压缩的模块，打包压缩并保存在被管理节点，包不会放到control主机
Hostname模块：可以修改被控主机的hostname，但指定多台被控主机时，会一次性修改掉所有的主机为相同的hostname
Cron模块：为被控主机设置计划任务
Yum和Apt模块：Ubuntu和CentOS分开执行的包管理模块
yum_repository模块：为被控主机配置软件仓库
service模块：用于启动被控主机上的服务
user、group模块：用于创建或删除被控主机上的用户和组
lineinfile模块：执行多行匹配并替换最后匹配到的行，或者删除所有匹配到的行
replace模块：基于正则表达式执行匹配和替换，能够执行多行匹配并全部替换
SELinux模块：启用和禁用SELinux
reboot模块：执行远程重启
mount：执行挂载和卸载
setup模块：手机主机的系统信息，这些facts信息可以直接以变量的形式使用，但如果主机过多时会影响执行速度
debug模块：用于输出信息，或者输出变量的值
