运维自动化之ansible playbook一键化安装mysql主从
今天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
|
[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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[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
|
1
2
3
4
|
[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节点部署
|
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
|
[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节点部署
|
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
|
[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
|
部署完成，下面测试一下
|
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
|
[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)
|
然后去从库里查看
|
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@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
|
本文转自 reinxu 51CTO博客，原文链接：http://blog.51cto.com/dl528888/1617692
，如需转载请自行联系原作者
