運維自動化之ansible playbook一鍵化安裝mysql主從
作者：dl528888
使用ansible創建mysql主從的問題
今天qq群里有朋友討論使用ansible創建mysql主從的問題，正好我公司之前有需求，我就寫了這個模塊，現在分享給大家。
一、各軟件版本
1、docker版本
- 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存儲
- 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）
- [root@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'}
- [root@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'}
- [root@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
- [root@puppet ansible]# route add -net 172.16.0.0 netmask 255.255.0.0 gw 10.10.17.3
- ping那2個容器ip
- [root@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
- [root@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
- [root@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節點部署
- 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進行賬號授權
下面開始部署
- 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里查看
- [root@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節點部署
- 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的模塊就行
下面是運行情況
- [root@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
部署完成，下面測試一下
- 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插入數據
- [root@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)
然后去從庫里查看
- [root@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 地址
- https://github.com/dl528888/ansible-examples/tree/master/mysql_master_install
mysql slave地址
- https://github.com/dl528888/ansible-examples/tree/master/mysql_slave_install
責任編輯：chenqingxiang
來源：
51CTO
