1818IP-服务器技术教程,云服务器评测推荐,服务器系统排错处理,环境搭建,攻击防护等

当前位置：首页 - 运维 - 正文

君子好学，自强不息！

运维自动化之ansible playbook一键化安装mysql主从

2022-11-07 | 运维 | gtxyzz | 607°c
A+ A-

今天qq群里有朋友讨论使用ansible创建mysql主从的问题，正好我公司之前有需求，我就写了这个模块，现在分享给大家。

一、各软件版本

1、docker版本

Clientversion:1.3.2
ClientAPIversion:1.15
Goversion(client):go1.3.3
Gitcommit(client):39fa2fa/1.3.2
OS/Arch(client):linux/amd64
Serverversion:1.3.2
ServerAPIversion:1.15
Goversion(server):go1.3.3
Gitcommit(server):39fa2fa/1.3.2

2、docker存储

Containers:2
Images:4
StorageDriver:devicemapper
PoolName:docker-253:1-1430610-pool
PoolBlocksize:65.54kB
Datafile:/var/lib/docker/devicemapper/devicemapper/data
Metadatafile:/var/lib/docker/devicemapper/devicemapper/metadata
DataSpaceUsed:9.855GB
DataSpaceTotal:107.4GB
MetadataSpaceUsed:6.095MB
MetadataSpaceTotal:2.147GB
LibraryVersion:1.02.84-RHEL7(2014-03-26)
ExecutionDriver:native-0.2
KernelVersion:3.18.3-1.el7.elrepo.x86_64
OperatingSystem:CentOSLinux7(Core)

二、docker方面

1、新建立2个容器test1（mysql master）与test2（mysql slave）

[root@docker-test3~]#shcreate_docker_container_use_static_ip.shtest1docker.ops-chukong.com:5000/centos6-http:new/usr/bin/supervisord10
{'Physics_ip':'10.10.17.3','Container_name':'test1','Container_ip':'172.16.1.2/24','Container_vlan':'10','Container_vlan_gateway':'172.16.1.1/24','Container_create':'2015-03-0514:49:19','Container_status':'running'}
[root@docker-test3~]#shcreate_docker_container_use_static_ip.shtest2docker.ops-chukong.com:5000/centos6-http:new/usr/bin/supervisord10
{'Physics_ip':'10.10.17.3','Container_name':'test2','Container_ip':'172.16.1.3/24','Container_vlan':'10','Container_vlan_gateway':'172.16.1.1/24','Container_create':'2015-03-0514:49:39','Container_status':'running'}
[root@docker-test3~]#dockerps-a
CONTAINERIDIMAGECOMMANDCREATEDSTATUSPORTSNAMES
36b3b3643097docker.ops-chukong.com:5000/centos6-http:new"/usr/bin/supervisorAboutaminuteagoUpAboutaminutetest2
90f8ffc29d41docker.ops-chukong.com:5000/centos6-http:new"/usr/bin/supervisorAboutaminuteagoUpAboutaminutetest1

已经创建了2个实例，test1的ip是172.16.1.2，test2的ip是172.16.1.3

使用这个创建是使用了持久化固定ip原因，各位使用其他方式创建也可以。

宿主机的ip是10.10.17.3

三、ansible方面

1、添加路由

在ansible里添加一条到这个主机的路由，方便直接ansible对容器的ip进行部署mysql

[root@puppetansible]#routeadd-net172.16.0.0netmask255.255.0.0gw10.10.17.3
ping那2个容器ip
[root@puppetansible]#ping-c2172.16.1.2
PING172.16.1.2(172.16.1.2)56(84)bytesofdata.
64bytesfrom172.16.1.2:icmp_seq=1ttl=63time=0.846ms
64bytesfrom172.16.1.2:icmp_seq=2ttl=63time=0.121ms

---172.16.1.2pingstatistics---
2packetstransmitted,2received,0%packetloss,time1001ms
rttmin/avg/max/mdev=0.121/0.483/0.846/0.363ms
Youhavemailin/var/spool/mail/root
[root@puppetansible]#ping-c2172.16.1.3
PING172.16.1.3(172.16.1.3)56(84)bytesofdata.
64bytesfrom172.16.1.3:icmp_seq=1ttl=63time=0.672ms
64bytesfrom172.16.1.3:icmp_seq=2ttl=63time=0.111ms

---172.16.1.3pingstatistics---
2packetstransmitted,2received,0%packetloss,time999ms
rttmin/avg/max/mdev=0.111/0.391/0.672/0.281ms

可以看到通了，把这2个ip放到ansible的hosts里

2、添加hosts

[root@puppetansible]#tail-n3/etc/ansible/hosts
[container_mysql]
172.16.1.2:22
172.16.1.3:22

3、使用ansible部署mysql

A、部署mysql master，在test1节点部署

timeansible-playbookmysql_master_install.yml--extra-vars"host=172.16.1.2user=rootmysql_slave_ip=172.16.1.3"-k

mysql_slave_ip是从库的ip，弄这个是在master里给slave进行账号授权

下面开始部署

root@puppetansible]#timeansible-playbookmysql_master_install.yml--extra-vars"host=172.16.1.2user=rootmysql_slave_ip=172.16.1.3"-k
SSHpassword:

PLAY[172.16.1.2]*************************************************************

GATHERINGFACTS***************************************************************
ok:[172.16.1.2]

TASK:[common|Installinitializtionrequiresoftware]***********************
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyMysqlSoftwareToRedhatClient]***********
changed:[172.16.1.2]

TASK:[mysql_master_install|CreateMysqlUserInRedhatClient]*************
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyMysqlStartScriptToRedhatClient]*******
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyInstallMysqlScriptToRedhatClient]****
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyMysqlConfigToRedhatClient]*************
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyMysqlSecurityScriptToRedhatClient]****
changed:[172.16.1.2]

TASK:[mysql_master_install|CreateMysqlInstallDir]***********************
ok:[172.16.1.2]

TASK:[mysql_master_install|UncompressionMysqlSoftwareToRedhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|ModifyMysqlDirPermissionInRedhatClient]***
ok:[172.16.1.2]=>(item=/data/mysql/datadir)
ok:[172.16.1.2]=>(item=/data/mysql/basedir)

TASK:[mysql_master_install|InstallMysqlScriptInRedhatClient]**********
changed:[172.16.1.2]

TASK:[mysql_master_install|WaitUntilsMysqlServiceAvaiableInRedhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|StartMyqlSecurityScriptInRedhatClient]****
changed:[172.16.1.2]

TASK:[mysql_master_install|AddBootStartMysqlServiceInRedhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|CopyMysqlCreateSlaveScriptToRedhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|CreateMysqlMasterAndSlaveInredhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|DeleteCreateMysqlMasterAndSlaveScriptInredhatClient]***
changed:[172.16.1.2]

TASK:[mysql_master_install|DeleteMysqlcompressionSoftwareInRedhatClient]***
changed:[172.16.1.2]

PLAYRECAP********************************************************************
172.16.1.2:ok=19changed=16unreachable=0failed=0


real3m2.646s
user0m14.250s
sys0m0.854s

可以看到3分钟部署完成，最浪费时间的是yum安装基础库

现在去test1里查看

[root@puppetansible]#ssh172.16.1.2
root@172.16.1.2'spassword:
Lastlogin:MonNov1714:10:392014from172.17.42.1
root@b8d17d0f3941:~
15:02:39#netstat-tlnp
ActiveInternetconnections(onlyservers)
ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programname
tcp00172.16.1.2:33060.0.0.0:*LISTEN-
tcp000.0.0.0:220.0.0.0:*LISTEN8/sshd
tcp00:::80:::*LISTEN14/httpd
tcp00:::22:::*LISTEN8/sshd
root@b8d17d0f3941:~
root@b8d17d0f3941:~
15:03:12#mysql-h172.16.1.2-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis12
Serverversion:5.5.21-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
+--------------------+
3rowsinset(0.00sec)

mysql>usemysql
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>selecthost,user,passwordfromuser;
+------------+--------+-------------------------------------------+
|host|user|password|
+------------+--------+-------------------------------------------+
|localhost|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1|
|172.16.1.3|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1|
|172.16.1.2|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1|
|%|zabbix|*DEEF4D7D88CD046ECA02A80393B7780A63E7E789|
+------------+--------+-------------------------------------------+
4rowsinset(0.00sec)

mysql>

可以看到数据库部署成功，并已经给予slave授予了权限。

B、部署从库

在test2节点部署

timeansible-playbookmysql_slave_install.yml--extra-vars"host=172.16.1.3user=rootmysql_master_ip=172.16.1.2mysql_master_port=3306mysql_master_user=rootmysql_master_passwd=E4yR3WnoluSFTCBAI"-k

mysql_master_ip是mysql master的ip

mysql_master_port是mysql master的mysql端口

mysql_master_user是mysql master的mysql用户

mysql_master_passwd是mysql 的密码

如果你使用我模块部署mysql的master了，直接运行命令就行，如果没有，只想单独弄个从库，主库不弄，那么***步创建mysql master就不需要，只需要你在mater里授予slave的权限，然后运行mysql slave的模块就行

下面是运行情况

[root@puppetansible]#timeansible-playbookmysql_slave_install.yml--extra-vars"host=172.16.1.3user=rootmysql_master_ip=172.16.1.2mysql_master_port=3306mysql_master_user=rootmysql_master_passwd=E4yR3WnoluSFTCBAI"-k
SSHpassword:

PLAY[172.16.1.3]*************************************************************

GATHERINGFACTS***************************************************************
ok:[172.16.1.3]

TASK:[common|Installinitializtionrequiresoftware]***********************
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyMysqlSoftwareToRedhatClient]************
changed:[172.16.1.3]

TASK:[mysql_slave_install|CreateMysqlUserInRedhatClient]**************
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyMysqlStartScriptToRedhatClient]********
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyInstallMysqlScriptToRedhatClient]*****
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyMysqlConfigToRedhatClient]**************
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyMysqlSecurityScriptToRedhatClient]*****
changed:[172.16.1.3]

TASK:[mysql_slave_install|CreateMysqlInstallDir]************************
ok:[172.16.1.3]

TASK:[mysql_slave_install|UncompressionMysqlSoftwareToRedhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|ModifyMysqlDirPermissionInRedhatClient]****
ok:[172.16.1.3]=>(item=/data/mysql/datadir)
ok:[172.16.1.3]=>(item=/data/mysql/basedir)

TASK:[mysql_slave_install|InstallMysqlScriptInRedhatClient]***********
changed:[172.16.1.3]

TASK:[mysql_slave_install|WaitUntilsMysqlServiceAvaiableInRedhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|StartMyqlSecurityScriptInRedhatClient]*****
changed:[172.16.1.3]

TASK:[mysql_slave_install|AddBootStartMysqlServiceInRedhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|CopyMysqlCreateSlaveScriptToRedhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|CreateMysqlMasterAndSlaveInredhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|DeleteCreateMysqlMasterAndSlaveScriptInredhatClient]***
changed:[172.16.1.3]

TASK:[mysql_slave_install|DeleteMysqlcompressionSoftwareInRedhatClient]***
changed:[172.16.1.3]

PLAYRECAP********************************************************************
172.16.1.3:ok=19changed=16unreachable=0failed=0


real2m59.966s
user0m14.413s
sys0m0.987s

部署完成，下面测试一下

15:26:22#netstat-tlnp
ActiveInternetconnections(onlyservers)
ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programname
tcp00172.16.1.3:33060.0.0.0:*LISTEN-
tcp000.0.0.0:220.0.0.0:*LISTEN8/sshd
tcp00:::80:::*LISTEN14/httpd
tcp00:::22:::*LISTEN8/sshd
root@4ac2891ba3fd:~
15:26:27#mysql-h172.16.1.3-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis16
Serverversion:5.5.21-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
+--------------------+
3rowsinset(0.01sec)

mysql>showprocesslist;
+----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+
|Id|User|Host|db|Command|Time|State|Info|Rows_sent|Rows_examined|Rows_read|
+----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+
|11|systemuser||NULL|Connect|192|Waitingformastertosendevent|NULL|0|0|1|
|12|systemuser||NULL|Connect|192|Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit|NULL|0|0|1|
|14|root|172.16.1.3:53445|NULL|Query|0|NULL|showprocesslist|0|0|4|
+----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+
3rowsinset(0.00sec)

mysql>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:172.16.1.2
Master_User:mysql_sync
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000003
Read_Master_Log_Pos:1663
Relay_Log_File:mysql-relay-bin.000002
Relay_Log_Pos:253
Relay_Master_Log_File:mysql-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:1663
Relay_Log_Space:409
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:2
1rowinset(0.00sec)

可以看到从库数据库创建成功，并且主从（sql与i这2个状态也是yes）创建成功。

C、测试主从状态

在master里创建一个数据库mysql_master，并创建表test插入数据

[root@puppetansible]#ssh172.16.1.2
root@172.16.1.2'spassword:
Lastlogin:ThuMar515:02:392015from10.10.11.125
root@b8d17d0f3941:~
15:27:20#mysql-h172.16.1.2-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis17
Serverversion:5.5.21-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>createdatabasemysql_master;
QueryOK,1rowaffected(0.00sec)

mysql>usemysql_master
Databasechanged
mysql>createtabletest(idint,namevarchar(4));
QueryOK,0rowsaffected(0.03sec)

mysql>desctest;
+-------+------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|name|varchar(4)|YES||NULL||
+-------+------------+------+-----+---------+-------+
2rowsinset(0.00sec)
mysql>insertintotestvalue(1,'test1');
QueryOK,1rowaffected,1warning(0.00sec)

mysql>insertintotestvalue(2,'test2');
QueryOK,1rowaffected,1warning(0.00sec)

mysql>select*fromtest;
+------+------+
|id|name|
+------+------+
|1|test|
|2|test|
+------+------+
2rowsinset(0.00sec)

然后去从库里查看

[root@puppetansible]#ssh172.16.1.3
root@172.16.1.3'spassword:
Lastlogin:ThuMar515:25:062015from10.10.11.125
root@4ac2891ba3fd:~
15:31:05#mysql-h172.16.1.3-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis17
Serverversion:5.5.21-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|mysql_master|
|performance_schema|
+--------------------+
4rowsinset(0.00sec)

mysql>usemysql_master
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>showtables;
+------------------------+
|Tables_in_mysql_master|
+------------------------+
|test|
+------------------------+
1rowinset(0.00sec)

mysql>select*fromtest;
+------+------+
|id|name|
+------+------+
|1|test|
|2|test|
+------+------+
2rowsinset(0.00sec)

可以看到从库也同步了数据，mysql主从创建成功。

四、模块地址

mysql master 地址

https://github.com/dl528888/ansible-examples/tree/master/mysql_master_install

mysql slave地址

https://github.com/dl528888/ansible-examples/tree/master/mysql_slave_install

本文来源：1818IP

本文地址：https://www.1818ip.com/post/7604.html

免责声明：本文由用户上传，如有侵权请联系删除！

相关文章
标签：
上一篇

12 个最佳的免费网络监控工具
下一篇

能Ping通，能DNS解析，不能打开网页的解决办法

发表评论

必填

选填

选填

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。


本站资源均来自原创及互联网，仅供学习与参考，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。


苏ICP备2022008563号-1

欢迎您关注我

扫码关注微信