运维自动化之ansible playbook一键化安装mysql主从
今天qq群里有朋友讨论使用ansible创建mysql主从的问题，正好我公司之前有需求，我就写了这个模块，现在分享给大家。 一、各软件版本 1、docker版本 1 2 3 4 5 6 7 8 9 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存储 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Containers:2 Images:4 StorageDriver:devicemapper PoolName:docker-253:1-1430610-pool PoolBlocksize:65.54kB Data file : /var/lib/docker/devicemapper/devicemapper/data Metadata file : /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） 1 2 3 4 5 6 7 8 [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/supervisor AboutaminuteagoUpAboutaminutetest2 90f8ffc29d41docker.ops-chukong.com:5000 /centos6-http :new" /usr/bin/supervisor AboutaminuteagoUpAboutaminutetest1 已经创建了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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 [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=63 time =0.846ms 64bytesfrom172.16.1.2:icmp_seq=2ttl=63 time =0.121ms ---172.16.1.2 ping statistics--- 2packetstransmitted,2received,0%packetloss, time 1001ms rttmin /avg/max/mdev =0.121 /0 .483 /0 .846 /0 .363ms Youhavemail in /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=63 time =0.672ms 64bytesfrom172.16.1.3:icmp_seq=2ttl=63 time =0.111ms ---172.16.1.3 ping statistics--- 2packetstransmitted,2received,0%packetloss, time 999ms rttmin /avg/max/mdev =0.111 /0 .391 /0 .672 /0 .281ms 可以看到通了，把这2个ip放到ansible的hosts里 2、添加hosts 1 2 3 4 [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节点部署 1 time ansible-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进行账号授权 下面开始部署 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 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 real 3m2.646s user 0m14.250s sys 0m0.854s 可以看到3分钟部署完成，最浪费时间的是yum安装基础库 现在去test1里查看 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 [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 /Program name 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. YourMySQLconnection id is12 Serverversion:5.5.21-logSourcedistribution Copyright(c)2000,2011,Oracleand /or itsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand /or its affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type 'help;' or '\h' for help.Type '\c' to clear thecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| +--------------------+ 3rows in set (0.00sec) mysql>usemysql Readingtableinformation for completionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql> select host,user,passwordfromuser; +------------+--------+-------------------------------------------+ |host|user|password| +------------+--------+-------------------------------------------+ |localhost|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1| |172.16.1.3|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1| |172.16.1.2|root|*BE78618CBAFFF409CE17D81579C1678B94439BE1| |%|zabbix|*DEEF4D7D88CD046ECA02A80393B7780A63E7E789| +------------+--------+-------------------------------------------+ 4rows in set (0.00sec) mysql> 可以看到数据库部署成功，并已经给予slave授予了权限。 B、部署从库 在test2节点部署 1 time ansible-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的模块就行 下面是运行情况 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 [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 real 2m59.966s user 0m14.413s sys 0m0.987s 部署完成，下面测试一下 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 15:26:22 #netstat-tlnp ActiveInternetconnections(onlyservers) ProtoRecv-QSend-QLocalAddressForeignAddressStatePID /Program name 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. YourMySQLconnection id is16 Serverversion:5.5.21-logSourcedistribution Copyright(c)2000,2011,Oracleand /or itsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand /or its affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type 'help;' or '\h' for help.Type '\c' to clear thecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| +--------------------+ 3rows in set (0.01sec) mysql>showprocesslist; +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ |Id|User|Host|db|Command|Time|State|Info|Rows_sent|Rows_examined|Rows_read| +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ |11|systemuser||NULL|Connect|192|Waiting for mastertosendevent|NULL|0|0|1| |12|systemuser||NULL|Connect|192|Slavehas read allrelaylog;waiting for theslaveI /O threadtoupdateit|NULL|0|0|1| |14|root|172.16.1.3:53445|NULL|Query|0|NULL|showprocesslist|0|0|4| +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ 3rows in set (0.00sec) mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waiting for mastertosendevent 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 1row in set (0.00sec) 可以看到从库数据库创建成功，并且主从（sql与i这2个状态也是yes）创建成功。 C、测试主从状态 在master里创建一个数据库mysql_master，并创建表test插入数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 [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. YourMySQLconnection id is17 Serverversion:5.5.21-logSourcedistribution Copyright(c)2000,2011,Oracleand /or itsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand /or its affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type 'help;' or '\h' for help.Type '\c' to clear thecurrentinputstatement. mysql>createdatabasemysql_master; QueryOK,1rowaffected(0.00sec) mysql>usemysql_master Databasechanged mysql>createtable test ( id int,namevarchar(4)); QueryOK,0rowsaffected(0.03sec) mysql>desc test ; +-------+------------+------+-----+---------+-------+ |Field|Type|Null|Key|Default|Extra| +-------+------------+------+-----+---------+-------+ | id |int(11)|YES||NULL|| |name|varchar(4)|YES||NULL|| +-------+------------+------+-----+---------+-------+ 2rows in set (0.00sec) mysql>insertinto test value(1, 'test1' ); QueryOK,1rowaffected,1warning(0.00sec) mysql>insertinto test value(2, 'test2' ); QueryOK,1rowaffected,1warning(0.00sec) mysql> select *from test ; +------+------+ | id |name| +------+------+ |1| test | |2| test | +------+------+ 2rows in set (0.00sec) 然后去从库里查看 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 [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. YourMySQLconnection id is17 Serverversion:5.5.21-logSourcedistribution Copyright(c)2000,2011,Oracleand /or itsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand /or its affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type 'help;' or '\h' for help.Type '\c' to clear thecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |mysql_master| |performance_schema| +--------------------+ 4rows in set (0.00sec) mysql>usemysql_master Readingtableinformation for completionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged mysql>showtables; +------------------------+ |Tables_in_mysql_master| +------------------------+ | test | +------------------------+ 1row in set (0.00sec) mysql> select *from test ; +------+------+ | id |name| +------+------+ |1| test | |2| test | +------+------+ 2rows in set (0.00sec) 可以看到从库也同步了数据，mysql主从创建成功。 四、模块地址 mysql master 地址 1 https: //github .com /dl528888/ansible-examples/tree/master/mysql_master_install mysql slave地址 1 https: //github .com /dl528888/ansible-examples/tree/master/mysql_slave_install 本文转自 reinxu 51CTO博客，原文链接：http://blog.51cto.com/dl528888/1617692 ，如需转载请自行联系原作者
