运维自动化之ansible playbook安装mysql tpcc测试OLTP能力-原创手记-慕课网

上次介绍了如何使用ansible playbook安装zabbix客户端（http://dl528888.blog.51cto.com/2382721/1436745），这次介绍一下如何使用playbook安装mysql。

下面是安装mysql的信息：

mysql_basedir: /data/mysql/basedir 源码目录

mysql_datadir: /data/mysql/datadir 数据目录

mysql_user: mysql mysql用户

mysql_database_user: root 数据库用户

mysql_passwd: 'E4yR3WnoluSFTCBAI' 数据库密码

mysql_port: 3306 mysql监听端口

mysql_sock: /data/mysql/datadir/mysql.sock mysql的sock

mysql_charset: utf8 mysql字符集

mysql_collation: utf8_general_ci mysql排序方式

mysql_version: Percona-Server-5.5.21-rel25.1.tar.gz mysql版本

下面是安装mysql的playbook结构

10:24:06 # tree mysql_*

mysql_delete

├── files

├── handlers

├── meta

│ └── main.yml

├── tasks

│ ├── delete.yml

│ └── main.yml

├── templates

└── vars

└── main.yml

mysql_install

├── files

│ └── mysql.tar.gz

├── handlers

├── meta

│ └── main.yml

├── tasks

│ ├── copy.yml

│ ├── delete.yml

│ ├── install.yml

│ └── main.yml

├── templates

│ ├── install_mysql.sh

│ ├── my.cnf

│ ├── mysqld

│ └── mysql_security.sh

└── vars

└── main.yml

12 directories, 15 files

playbook的mysql安装的是

10:32:06 # cat mysql_install.yml

---

- hosts: "`host`"

remote_user: "`user`"

gather_facts: True

roles:

- common

- mysql_install

playbook的mysql协助的是

10:32:44 # cat mysql_delete.yml

---

- hosts: "`host`"

remote_user: "`user`"

gather_facts: True

roles:

- mysql_delete

由于在上篇安装zabbix客户端已经介绍了结构，所以现在不介绍了。

1、安装mysql

10:26:00 # time ansible-playbook mysql_install.yml --extra-vars "host=192.168.240.17 user=root" --private-key=/root/test.pem

PLAY [192.168.240.17] *********************************************************

GATHERING FACTS ***************************************************************

ok: [192.168.240.17]

TASK: [common | Install initializtion require software] ***********************

changed: [192.168.240.17]

TASK: [mysql_install | Copy Mysql Software To Redhat Client] ******************

changed: [192.168.240.17]

TASK: [mysql_install | Create Mysql User In Redhat Client] ********************

changed: [192.168.240.17]

TASK: [mysql_install | Copy Mysql Start Script To Redhat Client] **************

changed: [192.168.240.17]

TASK: [mysql_install | Copy Install Mysql Script To Redhat Client] ***********

changed: [192.168.240.17]

TASK: [mysql_install | Copy Mysql Config To Redhat Client] ********************

changed: [192.168.240.17]

TASK: [mysql_install | Copy Mysql Security Script To Redhat Client] ***********

changed: [192.168.240.17]

TASK: [mysql_install | Create Mysql Install Dir] ******************************

ok: [192.168.240.17]

TASK: [mysql_install | Uncompression Mysql Software To Redhat Client] *********

changed: [192.168.240.17]

TASK: [mysql_install | Modify Mysql Dir Permission In Redhat Client] **********

ok: [192.168.240.17] => (item=/data/mysql/datadir)

ok: [192.168.240.17] => (item=/data/mysql/basedir)

TASK: [mysql_install | Install Mysql Script In Redhat Client] *****************

changed: [192.168.240.17]

TASK: [mysql_install | Start Myql Security Script In Redhat Client] ***********

changed: [192.168.240.17]

TASK: [mysql_install | Add Boot Start Mysql Service In Redhat Client] *********

changed: [192.168.240.17]

TASK: [mysql_install | Delete Mysql compression Software In Redhat Client] ****

changed: [192.168.240.17]

PLAY RECAP ********************************************************************

192.168.240.17 : ok=15 changed=12 unreachable=0 failed=0

real 2m1.596s

user 0m8.815s

sys 0m0.848s

2、安装后测试

登陆192.168.240.17的测试机查看mysql安装情况

[root@ip-10-10-240-21 tmp]# ll

total 16

lrwxrwxrwx 1 root root 30 Jul 13 22:27 mysql.sock -> /data/mysql/datadir/mysql.sock

-rw------- 1 root root 256 Jul 10 06:07 tmp.0PLkgCq81n

-rw------- 1 root root 197 Jul 9 05:35 yum_save_tx-2014-07-09-09-35ibcBiO.yumtx

-rw-rw-r-- 1 zabbix zabbix 320 Jul 10 21:39 zabbix_agentd.log

-rw-rw-r-- 1 zabbix zabbix 5 Jul 10 21:39 zabbix_agentd.pid

[root@ip-10-10-240-21 tmp]# ps -ef|grep mysql

root 21333 1 0 22:27 ? 00:00:00 /bin/sh /data/mysql/basedir/bin/mysqld_safe --defaults-file=/data/mysql/datadir/my.cnf --datadir=/data/mysql/datadir --pid-file=/data/mysql/datadir/mysql.pid

mysql 22156 21333 2 22:27 ? 00:00:08 /data/mysql/basedir/bin/mysqld --defaults-file=/data/mysql/datadir/my.cnf --basedir=/data/mysql/basedir --datadir=/data/mysql/datadir --plugin-dir=/data/mysql/basedir/lib/plugin --user=mysql --log-error=/data/mysql/datadir/mysql-error.log --open-files-limit=10240 --pid-file=/data/mysql/datadir/mysql.pid --socket=/data/mysql/datadir/mysql.sock --port=3306

root 22440 20233 0 22:33 pts/0 00:00:00 grep mysql

[root@ip-10-10-240-21 tmp]# ll /data/mysql/

total 8

drwxr-xr-x 13 mysql mysql 4096 Jun 26 03:59 basedir

drwxr-xr-x 4 mysql mysql 4096 Jul 13 22:28 datadir

[root@ip-10-10-240-21 tmp]# ll /etc/init.d/mysqld

-rwxr-xr-x 1 root root 10905 Jul 13 22:27 /etc/init.d/mysqld

[root@ip-10-10-240-21 tmp]# /etc/init.d/mysqld status

MySQL (Percona Server) running (22156) [ OK ]

[root@ip-10-10-240-21 tmp]# chkconfig --list|grep mysql

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

[root@ip-10-10-240-21 tmp]# mysql -h 192.168.240.17 -u root -pE4yR3WnoluSFTCBAI

ERROR 1045 (28000): Access denied for user 'root'@'192.168.240.17' (using password: YES)

[root@ip-10-10-240-21 tmp]# mysql -h 10.10.240.21 -u root -pE4yR3WnoluSFTCBAI

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.21-log Source distribution

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 |

+--------------+--------+-------------------------------------------+

| 10.10.240.21 | root | *BE78618CBAFFF409CE17D81579C1678B94439BE1 |

| % | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |

+--------------+--------+-------------------------------------------+

2 rows in set (0.00 sec)

可以看到已经按照我的要求安装完成，下面在进行删除测试。

3、删除

10:49:18 # time ansible-playbook mysql_delete.yml --extra-vars "host=192.168.240.17 user=root" --private-key=/root/test.pem

PLAY [192.168.240.17] *********************************************************

GATHERING FACTS ***************************************************************

ok: [192.168.240.17]

TASK: [mysql_delete | Stop Mysql Service] *************************************

changed: [192.168.240.17]

TASK: [mysql_delete | Delete Mysql Boot Start Script] *************************

changed: [192.168.240.17]

TASK: [mysql_delete | Delete Mysql Dir And Socket] ****************************

changed: [192.168.240.17]

TASK: [mysql_delete | Delete Mysql User] **************************************

changed: [192.168.240.17]

TASK: [mysql_delete | Delete Mysql Service Start Script] **********************

changed: [192.168.240.17]

PLAY RECAP ********************************************************************

192.168.240.17 : ok=6 changed=5 unreachable=0 failed=0

real 0m25.248s

user 0m0.632s

sys 0m0.102s

4、删除后测试

[root@ip-10-10-240-21 tmp]# ll /tmp/

total 16

-rw------- 1 root root 256 Jul 10 06:07 tmp.0PLkgCq81n

-rw------- 1 root root 197 Jul 9 05:35 yum_save_tx-2014-07-09-09-35ibcBiO.yumtx

-rw-rw-r-- 1 zabbix zabbix 320 Jul 10 21:39 zabbix_agentd.log

-rw-rw-r-- 1 zabbix zabbix 5 Jul 10 21:39 zabbix_agentd.pid

[root@ip-10-10-240-21 tmp]# ll /data/

total 4

drwxr-xr-x 3 root root 4096 Jul 10 22:11 webroot

[root@ip-10-10-240-21 tmp]# ps -ef|grep mysql

root 22955 20233 0 22:50 pts/0 00:00:00 grep mysql

[root@ip-10-10-240-21 tmp]# ll /etc/init.d/mysql

ls: cannot access /etc/init.d/mysql: No such file or directory

[root@ip-10-10-240-21 tmp]# chkconfig --list|grep mysql

[root@ip-10-10-240-21 tmp]#

可以看到都删除完成。

如果大家想使用我的例子，可以从github里下载（地址是https://github.com/dl528888/ansible-examples/tree/master/mysql_install），然后放到/etc/ansible目录里，下面是压缩包里的内容

11:20:08 # unzip -v mysql_install.zip

Archive: mysql_install.zip

Length Method Size Cmpr Date Time CRC-32 Name

-------- ------ ------- ---- ---------- ----- -------- ----

113 Defl:N 89 21% 07-14-2014 10:32 aaed0763 mysql_install.yml

99 Defl:N 82 17% 07-14-2014 10:32 70c2a028 mysql_delete.yml

0 Stored 0 0% 07-03-2014 17:29 00000000 roles/common/

0 Stored 0 0% 06-30-2014 15:58 00000000 roles/common/meta/

267 Defl:N 178 33% 06-30-2014 15:58 31ee20ec roles/common/meta/main.yml

0 Stored 0 0% 07-11-2014 09:31 00000000 roles/common/tasks/

586 Defl:N 268 54% 07-08-2014 10:18 1c0af2a1 roles/common/tasks/main.yml

0 Stored 0 0% 06-19-2014 13:43 00000000 roles/common/handlers/

58 Defl:N 48 17% 06-19-2014 13:43 8d058053 roles/common/handlers/main.yml

0 Stored 0 0% 06-19-2014 13:30 00000000 roles/common/vars/

0 Stored 0 0% 06-19-2014 13:30 00000000 roles/common/templates/

0 Stored 0 0% 06-19-2014 13:30 00000000 roles/common/files/

0 Stored 0 0% 06-24-2014 11:27 00000000 roles/mysql_install/

0 Stored 0 0% 07-08-2014 14:12 00000000 roles/mysql_install/meta/

198 Defl:N 149 25% 07-08-2014 14:12 b03e00eb roles/mysql_install/meta/main.yml

0 Stored 0 0% 07-14-2014 09:27 00000000 roles/mysql_install/tasks/

201 Defl:N 153 24% 07-14-2014 09:27 51de730b roles/mysql_install/tasks/delete.yml

65 Defl:N 41 37% 07-14-2014 09:27 a3197ca7 roles/mysql_install/tasks/main.yml

1281 Defl:N 329 74% 07-14-2014 09:27 73d60454 roles/mysql_install/tasks/copy.yml

1162 Defl:N 375 68% 07-14-2014 09:27 e5a7341c roles/mysql_install/tasks/install.yml

0 Stored 0 0% 06-19-2014 13:30 00000000 roles/mysql_install/handlers/

0 Stored 0 0% 07-14-2014 09:51 00000000 roles/mysql_install/vars/

313 Defl:N 177 44% 07-14-2014 09:51 bf4476a7 roles/mysql_install/vars/main.yml

0 Stored 0 0% 07-14-2014 11:11 00000000 roles/mysql_install/templates/

518 Defl:N 241 54% 07-14-2014 09:15 529052bf roles/mysql_install/templates/install_mysql.sh

10905 Defl:N 3765 66% 06-26-2014 14:06 e35e7b82 roles/mysql_install/templates/mysqld

2122 Defl:N 823 61% 07-14-2014 09:49 f91c74ab roles/mysql_install/templates/my.cnf

794 Defl:N 275 65% 07-03-2014 13:15 560ee63e roles/mysql_install/templates/mysql_security.sh

0 Stored 0 0% 06-26-2014 13:59 00000000 roles/mysql_install/files/

61805835 Defl:N 60413846 2% 06-26-2014 16:18 f61e317d roles/mysql_install/files/mysql.tar.gz

0 Stored 0 0% 06-24-2014 11:27 00000000 roles/mysql_delete/

0 Stored 0 0% 07-08-2014 14:12 00000000 roles/mysql_delete/meta/

197 Defl:N 147 25% 07-08-2014 14:12 fe6b0ef8 roles/mysql_delete/meta/main.yml

0 Stored 0 0% 07-14-2014 09:39 00000000 roles/mysql_delete/tasks/

692 Defl:N 245 65% 07-14-2014 09:39 dccd57b4 roles/mysql_delete/tasks/delete.yml

22 Stored 22 0% 06-27-2014 13:30 728ce4aa roles/mysql_delete/tasks/main.yml

0 Stored 0 0% 06-19-2014 13:30 00000000 roles/mysql_delete/handlers/

0 Stored 0 0% 07-03-2014 13:16 00000000 roles/mysql_delete/vars/

313 Defl:N 177 44% 07-14-2014 10:56 bf4476a7 roles/mysql_delete/vars/main.yml

0 Stored 0 0% 06-27-2014 13:30 00000000 roles/mysql_delete/templates/

0 Stored 0 0% 06-27-2014 13:30 00000000 roles/mysql_delete/files/

-------- ------- --- -------

61825741 60421430 2% 41 files

如果觉得好，给个赞并多评论，谢谢。

ansible安装mysql运维自动化专题