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

23dq1

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

1、docker版本

1 2 3 4 5 6 7 8 9

Client version: 1.3.2 Client API version: 1.15 Go version (client): go1.3.3 Git commit (client): 39fa2fa/1.3.2 OS/Arch (client): linux/amd64 Server version: 1.3.2 Server API version: 1.15 Go version (server): go1.3.3 Git commit (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 Storage Driver: devicemapper Pool Name: docker-253:1-1430610-pool Pool Blocksize: 65.54 kB Data file: /var/lib/docker/devicemapper/devicemapper/data Metadata file: /var/lib/docker/devicemapper/devicemapper/metadata Data Space Used: 9.855 GB Data Space Total: 107.4 GB Metadata Space Used: 6.095 MB Metadata Space Total: 2.147 GB Library Version: 1.02.84-RHEL7 (2014-03-26) Execution Driver: native-0.2 Kernel Version: 3.18.3-1.el7.elrepo.x86_64 Operating System: CentOS Linux 7 (Core)

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

1 2 3 4 5 6 7 8

[iyunv@docker-test3 ~]#  sh create_docker_container_use_static_ip.sh test1 docker.ops-chukong.com:5000/centos6-http:new /usr/bin/supervisord 10 {'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-05 14:49:19','Container_status':'running'} [iyunv@docker-test3 ~]#  sh create_docker_container_use_static_ip.sh test2 docker.ops-chukong.com:5000/centos6-http:new /usr/bin/supervisord 10 {'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-05 14:49:39','Container_status':'running'} [iyunv@docker-test3 ~]# docker ps -a CONTAINER ID        IMAGE                                          COMMAND                CREATED              STATUS              PORTS               NAMES 36b3b3643097        docker.ops-chukong.com:5000/centos6-http:new   "/usr/bin/supervisor   About a minute ago   Up About a minute                       test2 90f8ffc29d41        docker.ops-chukong.com:5000/centos6-http:new   "/usr/bin/supervisor   About a minute ago   Up About a minute                       test1

已经创建了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

[iyunv@puppet ansible]# route add -net 172.16.0.0 netmask 255.255.0.0 gw 10.10.17.3 ping那2个容器ip [iyunv@puppet ansible]# ping -c 2 172.16.1.2 PING 172.16.1.2 (172.16.1.2) 56(84) bytes of data. 64 bytes from 172.16.1.2: icmp_seq=1 ttl=63 time=0.846 ms 64 bytes from 172.16.1.2: icmp_seq=2 ttl=63 time=0.121 ms --- 172.16.1.2 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1001ms rtt min/avg/max/mdev = 0.121/0.483/0.846/0.363 ms You have mail in /var/spool/mail/root [iyunv@puppet ansible]# ping -c 2 172.16.1.3 PING 172.16.1.3 (172.16.1.3) 56(84) bytes of data. 64 bytes from 172.16.1.3: icmp_seq=1 ttl=63 time=0.672 ms 64 bytes from 172.16.1.3: icmp_seq=2 ttl=63 time=0.111 ms --- 172.16.1.3 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 999ms rtt min/avg/max/mdev = 0.111/0.391/0.672/0.281 ms

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

2、添加hosts

1 2 3 4

[iyunv@puppet ansible]# 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-playbook mysql_master_install.yml --extra-vars "host=172.16.1.2 user=root mysql_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@puppet ansible]# time ansible-playbook mysql_master_install.yml --extra-vars "host=172.16.1.2 user=root mysql_slave_ip=172.16.1.3"  -k SSH password: PLAY [172.16.1.2] ************************************************************* GATHERING FACTS *************************************************************** ok: [172.16.1.2] TASK: [common | Install initializtion require software] *********************** changed: [172.16.1.2] TASK: [mysql_master_install | Copy Mysql Software To Redhat Client] *********** changed: [172.16.1.2] TASK: [mysql_master_install | Create Mysql User In Redhat Client] ************* changed: [172.16.1.2] TASK: [mysql_master_install | Copy Mysql Start Script To Redhat Client] ******* changed: [172.16.1.2] TASK: [mysql_master_install | Copy Install Mysql Script  To Redhat Client] **** changed: [172.16.1.2] TASK: [mysql_master_install | Copy Mysql Config To Redhat Client] ************* changed: [172.16.1.2] TASK: [mysql_master_install | Copy Mysql Security Script To Redhat Client] **** changed: [172.16.1.2] TASK: [mysql_master_install | Create Mysql Install Dir] *********************** ok: [172.16.1.2] TASK: [mysql_master_install | Uncompression Mysql Software To Redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Modify Mysql Dir Permission In Redhat Client] *** ok: [172.16.1.2] => (item=/data/mysql/datadir) ok: [172.16.1.2] => (item=/data/mysql/basedir) TASK: [mysql_master_install | Install Mysql Script In Redhat Client] ********** changed: [172.16.1.2] TASK: [mysql_master_install | Wait Untils Mysql Service Avaiable In Redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Start Myql Security Script In Redhat Client] **** changed: [172.16.1.2] TASK: [mysql_master_install | Add Boot Start Mysql Service In Redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Copy Mysql Create Slave Script To Redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Create Mysql Master And Slave  In redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Delete Create Mysql Master And Slave Script  In redhat Client] *** changed: [172.16.1.2] TASK: [mysql_master_install | Delete Mysql compression Software In Redhat Client] *** changed: [172.16.1.2] PLAY RECAP ******************************************************************** 172.16.1.2                 : ok=19   changed=16   unreachable=0    failed=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

[iyunv@puppet ansible]# ssh 172.16.1.2 root@172.16.1.2's password: Last login: Mon Nov 17 14:10:39 2014 from 172.17.42.1 root@b8d17d0f3941:~ 15:02:39 # netstat -tlnp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name tcp        0      0 172.16.1.2:3306             0.0.0.0:*                   LISTEN      - tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      8/sshd tcp        0      0 :::80                       :::*                        LISTEN      14/httpd tcp        0      0 :::22                       :::*                        LISTEN      8/sshd root@b8d17d0f3941:~ root@b8d17d0f3941:~ 15:03:12 # mysql -h 172.16.1.2 -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.21-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 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. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> use mysql 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 mysql> select host,user,password from user; +------------+--------+-------------------------------------------+ | host       | user   | password                                  | +------------+--------+-------------------------------------------+ | localhost  | root   | *BE78618CBAFFF409CE17D81579C1678B94439BE1 | | 172.16.1.3 | root   | *BE78618CBAFFF409CE17D81579C1678B94439BE1 | | 172.16.1.2 | root   | *BE78618CBAFFF409CE17D81579C1678B94439BE1 | | %          | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 | +------------+--------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql>

可以看到数据库部署成功，并已经给予slave授予了权限。
B、部署从库
在test2节点部署

1	time ansible-playbook mysql_slave_install.yml --extra-vars "host=172.16.1.3 user=root mysql_master_ip=172.16.1.2 mysql_master_port=3306 mysql_master_user=root mysql_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

[iyunv@puppet ansible]# time ansible-playbook mysql_slave_install.yml --extra-vars "host=172.16.1.3 user=root mysql_master_ip=172.16.1.2 mysql_master_port=3306 mysql_master_user=root mysql_master_passwd=E4yR3WnoluSFTCBAI"  -k SSH password: PLAY [172.16.1.3] ************************************************************* GATHERING FACTS *************************************************************** ok: [172.16.1.3] TASK: [common | Install initializtion require software] *********************** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Mysql Software To Redhat Client] ************ changed: [172.16.1.3] TASK: [mysql_slave_install | Create Mysql User In Redhat Client] ************** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Mysql Start Script To Redhat Client] ******** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Install Mysql Script  To Redhat Client] ***** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Mysql Config To Redhat Client] ************** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Mysql Security Script To Redhat Client] ***** changed: [172.16.1.3] TASK: [mysql_slave_install | Create Mysql Install Dir] ************************ ok: [172.16.1.3] TASK: [mysql_slave_install | Uncompression Mysql Software To Redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Modify Mysql Dir Permission In Redhat Client] **** ok: [172.16.1.3] => (item=/data/mysql/datadir) ok: [172.16.1.3] => (item=/data/mysql/basedir) TASK: [mysql_slave_install | Install Mysql Script In Redhat Client] *********** changed: [172.16.1.3] TASK: [mysql_slave_install | Wait Untils Mysql Service Avaiable In Redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Start Myql Security Script In Redhat Client] ***** changed: [172.16.1.3] TASK: [mysql_slave_install | Add Boot Start Mysql Service In Redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Copy Mysql Create Slave Script To Redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Create Mysql Master And Slave  In redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Delete Create Mysql Master And Slave Script  In redhat Client] *** changed: [172.16.1.3] TASK: [mysql_slave_install | Delete Mysql compression Software In Redhat Client] *** changed: [172.16.1.3] PLAY RECAP ******************************************************************** 172.16.1.3                 : ok=19   changed=16   unreachable=0    failed=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 Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name tcp        0      0 172.16.1.3:3306             0.0.0.0:*                   LISTEN      - tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      8/sshd tcp        0      0 :::80                       :::*                        LISTEN      14/httpd tcp        0      0 :::22                       :::*                        LISTEN      8/sshd root@4ac2891ba3fd:~ 15:26:27 # mysql -h 172.16.1.3 -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.5.21-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 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. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | +--------------------+ 3 rows in set (0.01 sec) mysql> show processlist; +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ | Id | User        | Host             | db   | Command | Time | State                                                                       | Info             | Rows_sent | Rows_examined | Rows_read | +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ | 11 | system user |                  | NULL | Connect |  192 | Waiting for master to send event                                            | NULL             |         0 |             0 |         1 | | 12 | system user |                  | NULL | Connect |  192 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |         0 |             0 |         1 | | 14 | root        | 172.16.1.3:53445 | NULL | Query   |    0 | NULL                                                                        | show processlist |         0 |             0 |         4 | +----+-------------+------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+-----------+ 3 rows in set (0.00 sec) mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   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 1 row in set (0.00 sec)

可以看到从库数据库创建成功，并且主从（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

[iyunv@puppet ansible]# ssh 172.16.1.2 root@172.16.1.2's password: Last login: Thu Mar  5 15:02:39 2015 from 10.10.11.125 root@b8d17d0f3941:~ 15:27:20 # mysql -h 172.16.1.2 -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.5.21-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 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. mysql> create database mysql_master; Query OK, 1 row affected (0.00 sec) mysql> use mysql_master Database changed mysql> create table test(id int,name varchar(4)); Query OK, 0 rows affected (0.03 sec) mysql> desc test; +-------+------------+------+-----+---------+-------+ | Field | Type       | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id    | int(11)    | YES  |     | NULL    |       | | name  | varchar(4) | YES  |     | NULL    |       | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into test value(1,'test1'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into test value(2,'test2'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from test; +------+------+ | id   | name | +------+------+ |    1 | test | |    2 | test | +------+------+ 2 rows in set (0.00 sec)

然后去从库里查看

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

[iyunv@puppet ansible]# ssh 172.16.1.3 root@172.16.1.3's password: Last login: Thu Mar  5 15:25:06 2015 from 10.10.11.125 root@4ac2891ba3fd:~ 15:31:05 # mysql -h 172.16.1.3 -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.5.21-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 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. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | mysql_master       | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use mysql_master 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 mysql> show tables; +------------------------+ | Tables_in_mysql_master | +------------------------+ | test                   | +------------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+------+ | id   | name | +------+------+ |    1 | test | |    2 | test | +------+------+ 2 rows in set (0.00 sec)

可以看到从库也同步了数据，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