Ansible 自动化运维工具部署主从数据库+读写分离
一、主从复制和读写分离介绍
数据库主从复制是一种常见的数据库架构，用于提高数据库的可用性、可扩展性和性能。它通过将写操作（主节点）复制到一个或多个从节点来实现数据的同步。
主从复制的工作原理如下：
- 首先，将一个数据库节点指定为主节点，所有的写操作都在主节点上执行。
- 主节点将写操作记录到二进制日志（binary log）中，并将这些日志发送到从节点。
- 从节点接收到二进制日志后，将其应用到自己的数据库上，实现数据的同步。
读写分离是基于主从复制的，核心思想是将读操作分发到多个从节点上，而将写操作集中在主节点上。Mycat是一个开源的数据库中间件，支持读写分离，将读操作和写操作分别分发到不同的数据库节点上
二、准备工作
（1）节点规划
准备三台服务器，centos7.9.2009
| IP | 节点 |
|---|---|
| 192.168.100.10 | ansible |
| 192.168.100.20 | master |
| 192.168.100.30 | slave |
| 192.168.100.40 | mycat |
（2）修改主机名
1. 修改主机名
# ansible节点
[root@localhost ~]# hostnamectl set-hostname ansible
[root@localhost ~]# bash
[root@ansible ~]#
# master节点
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]#
# slave节点
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]#
# mycat节点
[root@localhost ~]# hostnamectl set-hostname mycat
[root@localhost ~]# bash
[root@mycat ~]#
（3）免密
[root@ansible ~]# ssh-keygen
[root@master ~]# ssh-keygen
[root@slave ~]# ssh-keygen
[root@mycat ~]# ssh-keygen
[root@ansible ~]# ssh-copy-id 192.168.100.20
[root@ansible ~]# ssh-copy-id 192.168.100.30
[root@ansible ~]# ssh-copy-id 192.168.100.40
（4）配置IP映射
[root@ansible ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.10 ansible
192.168.100.20 master
192.168.100.30 slave
192.168.100.40 mycat
（5）安装ansible
[root@ansible ~]# yum install -y epel-release vim tree
[root@ansible ~]# yum install -y ansible
[root@ansible ~]# ansible --version
ansible 2.9.27
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/root/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, Oct 14 2020, 14:45:30) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]
（6）配置主机清单文件
[root@ansible ~]# vim /etc/ansible/hosts
[master]
192.168.100.20
[slave]
192.168.100.30
[mycat]
192.168.100.40
[root@ansible ~]# ansible all -m ping
192.168.100.30 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.100.20 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
三、目录结构
（1）创建项目目录
[root@ansible ~]# mkdir mycat_mariadb
[root@ansible ~]# cd mycat_mariadb/
（2）创建角色目录
在创建roles之前，我们将数据库读写分离部署的步骤分为 3个role执行，这样更加易懂
init（初始化）
mysql（主从复制）
mycat（读写分离）
[root@ansible mycat_mariadb]# mkdir -p roles/{mysql-master,mysql-slave,init,mycat,mysql-create-testdb}/{templates,tasks,files}
这次项目中会用到template、tasks、files目录，可以选择性创建，自己用到哪个创建哪个
（3）创建变量目录
创建该目录用来存放变量
[root@ansible mycat_mariadb]# mkdir group_vars
[root@ansible mycat_mariadb]# vim group_vars/all
PASSWD: '000000'
master_host: 192.168.100.20
slave_host: 192.168.100.30
mycat_host: 192.168.100.40
shujuku: testdb
（4）init角色
这一步操作是用来创建数据库主从复制，以及读写分离的配置文件
master-my.cnf.j2文件内容
[root@ansible mycat_mariadb]# vim roles/mysql-master/files/master-my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=20
log_bin = mysql-bin
binlog-ignore-db=mysql
binlog-do-db=testdb
binlog_format=STATEMENT
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
slave-my.cnf.j2 文件内容
[root@ansible mycat_mariadb]# vim roles/mysql-slave/files/slave-my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server_id=30
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
server.xml.j2 文件内容
[root@ansible mycat_mariadb]# vim roles/mycat/templates/server.xml.j2
<?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="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</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-->
<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">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">1m</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--
<firewall>
<whitehost>
<host host="127.0.0.1" user="mycat"/>
<host host="127.0.0.2" user="mycat"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<user name="root">
<property name="password">{{PASSWD}}</property>
<property name="schemas">TESTDB</property>
<!-- 表级 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">{{PASSWD}}</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
schema.xml.j2 文件内容
[root@ansible mycat_mariadb]# vim roles/mycat/templates/schema.xml.j2
<?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="host1" database="{{shujuku}}" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="{{master_host}}:3306" user="root"
password="{{PASSWD}}">
<readHost host="hostS1" url="{{slave_host}}:3306" user="root" password="{{PASSWD}}" />
</writeHost>
</dataHost>
</mycat:schema>
（5）编写剧本入口文件
将调用roles的顺序及哪些主机调用哪些roles在这个文件夹中体现出来
[root@ansible mycat_mariadb]# vim mycat_mariadb.yaml
- hosts: all
remote_user: root
roles:
- init
- hosts: master
remote_user: root
roles:
- mysql-master
- hosts: slave
remote_user: root
roles:
- mysql-slave
- hosts: mycat
remote_user: root
roles:
- mycat
四、编写角色任务文件
（1）init初始化任务文件
[root@ansible tasks]# vim /root/mycat_mariadb/roles/init/tasks/main.yaml
- name: 配置所有主机的host映射
copy: src=/etc/hosts dest=/etc/hosts
- name: 关闭防火墙
shell: systemctl stop firewalld && systemctl disable firewalld && setenforce 0
- name: 安装mariadb expect
shell: yum install -y mariadb-server expect
- name: 设置开机自启并开启mariadb
shell: systemctl enable mariadb --now
- name: 设置密码
shell: mysqladmin -uroot password '{{PASSWD}}'
- name: 安装MySQL-python
shell: yum install -y MySQL-python
（2）mysql-master任务文件
[root@ansible tasks]# vim /root/mycat_mariadb/roles/mysql-master/tasks/main.yaml
- name: 移动文件
copy: src=master-my.cnf dest=/etc/my.cnf
- name: 重新启动mariadb
shell: systemctl restart mariadb
- name: 设置root用户访问权限
shell: mysql -uroot -p{{PASSWD}} -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '{{PASSWD}}';flush privileges;"
- name: 创建数据库用户用于复制
shell: mysql -uroot -p{{PASSWD}} -e "grant replication slave on *.* to 'csq'@'192.168.100.%' identified by '{{PASSWD}}';"
- name: 存放变量Log_name
shell: mysql -uroot -p{{PASSWD}} -e "show master status;"|awk 'NR==2{print $1}'
register: file
- name: 存放变量File_size
shell: mysql -uroot -p{{PASSWD}} -e "show master status;"|awk 'NR==2{print $2}'
register: pot
- name: 设置全局变量
set_fact: masterbin={{ file.stdout_lines[0] }}
- name: 设置全局变量
set_fact: position={{ pot.stdout_lines[0] }}
（3）mysql-slave任务文件
[root@ansible mycat_mariadb]# vim roles/mysql-slave/tasks/main.yaml
- name: 移动文件
copy: src=slave-my.cnf dest=/etc/my.cnf
- name: 重启mariadb
shell: systemctl restart mariadb
- name: 设置root用户访问权限
shell: mysql -uroot -p{{PASSWD}} -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '{{PASSWD}}';flush privileges;"
- name: 设置主数据信息
mysql_replication:
login_user: root
login_password: '000000'
mode: changemaster
master_user: csq
master_password: '000000'
master_host: 192.168.100.20
master_log_file: "{{ hostvars['192.168.100.20']['masterbin'] }}"
master_log_pos: "{{ hostvars['192.168.100.20']['position'] }}"
- name: 开启slave
shell: mysql -uroot -p{{PASSWD}} -e "start slave;"
- name: 定义输出的信息
shell: mysql -uroot -p{{PASSWD}} -e "show slave status\G;" | grep -E "Slave_IO_Running|Slave_SQL_Running"
register: slave_status_output
- name: 打印输出的信息
debug:
var: slave_status_output.stdout
（4）mysql-create-testdb任务文件
[root@ansible mycat_mariadb]# vim roles/mysql-create-testdb/tasks/main.yaml
- name: 创建用于复制的testdb库
shell: mysql -uroot -p000000 -e "create database testdb;"
- name: 创建表插入数据
shell: mysql -uroot -p000000 -e "use testdb; create table mytbl(id int,name varchar(20));insert into mytbl values(1,'csq');"
（5）mycat任务文件
[root@ansible mycat_mariadb]# vim roles/mycat/tasks/main.yaml
- name: 设置root用户访问权限
shell: mysql -uroot -p{{PASSWD}} -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '{{PASSWD}}';flush privileges;"
- name: 安装openjdk
shell: yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel wget net-tools
- name: 下载Mycat软件包
shell: wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
- name: 解压Mycat软件包到/usr/local
shell: tar -zxvf /root/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
- name: 复制模板文件
template: src=schema.xml.j2 dest=/usr/local/mycat/conf/schema.xml
- name: 复制模板文件
template: src=server.xml.j2 dest=/usr/local/mycat/conf/server.xml
- name: 开启mycat
shell: /bin/bash /usr/local/mycat/bin/mycat start
五、执行playbook
[root@ansible mycat_mariadb]# ansible-playbook mycat_mariadb.yaml
PLAY [all] ************************************************************************************************************************************************************
TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.40]
ok: [192.168.100.30]
ok: [192.168.100.20]
TASK [init : 配置所有主机的host映射] *******************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.20]
changed: [192.168.100.30]
TASK [init : 关闭防火墙] ***************************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]
TASK [init : 安装mariadb expect] ****************************************************************************************************************************************
[WARNING]: Consider using the yum module rather than running 'yum'. If you need to use command because yum is insufficient you can add 'warn: false' to this command
task or set 'command_warnings=False' in ansible.cfg to get rid of this message.
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]
TASK [init : 设置开机自启并开启mariadb] ****************************************************************************************************************************************
changed: [192.168.100.30]
changed: [192.168.100.40]
changed: [192.168.100.20]
TASK [init : 设置密码] ****************************************************************************************************************************************************
changed: [192.168.100.30]
changed: [192.168.100.20]
changed: [192.168.100.40]
TASK [init : 安装MySQL-python] ******************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]
PLAY [master] *********************************************************************************************************************************************************
TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.20]
TASK [mysql-master : 移动文件] ********************************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 重新启动mariadb] *************************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 设置root用户访问权限] ************************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 创建数据库用户用于复制] *************************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 存放变量Log_name] ************************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 存放变量File_size] ***********************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-master : 设置全局变量] ******************************************************************************************************************************************
ok: [192.168.100.20]
TASK [mysql-master : 设置全局变量] ******************************************************************************************************************************************
ok: [192.168.100.20]
PLAY [slave] **********************************************************************************************************************************************************
TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.30]
TASK [mysql-slave : 移动文件] *********************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 重启mariadb] ****************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 设置root用户访问权限] *************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 设置主数据信息] ******************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 开启slave] ******************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 定义输出的信息] ******************************************************************************************************************************************
changed: [192.168.100.30]
TASK [mysql-slave : 打印输出的信息] ******************************************************************************************************************************************
ok: [192.168.100.30] => {
"slave_status_output.stdout": " Slave_IO_Running: Yes\n Slave_SQL_Running: Yes"
}
PLAY [master] *********************************************************************************************************************************************************
TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.20]
TASK [mysql-create-testdb : 创建用于复制的testdb库] ***************************************************************************************************************************
changed: [192.168.100.20]
TASK [mysql-create-testdb : 创建表插入数据] **********************************************************************************************************************************
changed: [192.168.100.20]
PLAY [mycat] **********************************************************************************************************************************************************
TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.40]
TASK [mycat : 设置root用户访问权限] *******************************************************************************************************************************************
changed: [192.168.100.40]
TASK [mycat : 安装openjdk] **********************************************************************************************************************************************
changed: [192.168.100.40]
TASK [mycat : 下载Mycat软件包] *********************************************************************************************************************************************
[WARNING]: Consider using the get_url or uri module rather than running 'wget'. If you need to use command because get_url or uri is insufficient you can add 'warn:
false' to this command task or set 'command_warnings=False' in ansible.cfg to get rid of this message.
changed: [192.168.100.40]
TASK [mycat : 解压Mycat软件包到/usr/local] **********************************************************************************************************************************
[WARNING]: Consider using the unarchive module rather than running 'tar'. If you need to use command because unarchive is insufficient you can add 'warn: false' to
this command task or set 'command_warnings=False' in ansible.cfg to get rid of this message.
changed: [192.168.100.40]
TASK [mycat : 复制模板文件] *************************************************************************************************************************************************
changed: [192.168.100.40]
TASK [mycat : 复制模板文件] *************************************************************************************************************************************************
changed: [192.168.100.40]
TASK [开启mycat] ********************************************************************************************************************************************************
changed: [192.168.100.40]
PLAY RECAP ************************************************************************************************************************************************************
192.168.100.20 : ok=19 changed=14 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.100.30 : ok=15 changed=12 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.100.40 : ok=15 changed=13 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
在执行过程中会打印出这两条信息，如果都是YES 说明你主从配置成功了
六、验证主从复制读写分离
（1）登录mycat管理窗口
[root@mycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P 9066
查看读写配置情况
MySQL [(none)]> show @@datasource;
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.100.20 | 3306 | W | 0 | 10 | 1000 | 57 | 0 | 1 |
| dn1 | hostS1 | mysql | 192.168.100.30 | 3306 | R | 0 | 5 | 1000 | 53 | 2 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
参数详解：
DATANODE：数据节点的名称
NAME：数据节点的标识名称
TYPE：数据节点的类型，这里是mysql
HOST：数据节点的主机地址
PORT：数据节点的端口号
W/R：数据节点的读写类型，W表示写入，R表示读取
ACTIVE：当前活跃连接数
IDLE：当前空闲连接数
ZE：连接池大小，即连接池中的最大连接数
EXECUTE：执行次数，表示该数据节点的执行操作次数
READ_LOAD：读取负载，表示该数据节点的读取负载情况
查看心跳信息
MySQL [(none)]> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.100.20 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2023-09-13 15:27:30 | false |
| hostS1 | mysql | 192.168.100.30 | 3306 | 1 | 0 | idle | 0 | 1,0,1 | 2023-09-13 15:27:30 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
参数详解：
NAME：心跳节点的名称
TYPE：心跳节点的类型，这里是mysql
HOST：心跳节点的主机地址
PORT：心跳节点的端口号
RS_CODE：复制状态码，用于判断复制是否正常进行 (1表示主从复制正常进行)
RETRY：重试次数，表示心跳节点尝试重连的次数
STATUS：心跳节点的状态，包括idle（空闲）和active（活跃）
TIMEOUT：超时时间，表示心跳节点的超时时间
EXECUTE_TIME：执行时间，表示心跳节点的执行时间
LAST_ACTIVE_TIME：最后活跃时间，表示心跳节点最后一次发送心跳的时间
（2）读写分离验证
[root@mycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P 8066
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> insert into mytbl values(1,@@hostname); # 插入数据
MySQL [TESTDB]> select * from mytbl; # 查询数据，自动跳转从主机进行查询
+------+-------+
| id | name |
+------+-------+
| 1 | csq |
| 1 | slave |
+------+-------+
# @@hostname是MySQL系统变量，表示当前数据库服务器的主机名。
# 由此可以推断，select * from mytbl;查询的是从库。
使用master节点访问一下
[root@master ~]# mysql -uroot -p000000 -e "use testdb;select * from mytbl;"
+------+--------+
| id | name |
+------+--------+
| 1 | csq |
| 1 | master |
使用slave节点访问一下
[root@slave ~]# mysql -uroot -p000000 -e "use testdb;select * from mytbl;"
+------+-------+
| id | name |
+------+-------+
| 1 | csq |
| 1 | slave |
+------+-------+
至此 Ansible部署主从数据库+读写分离结束
