## 1. 安装Ansible

### 在Ubuntu/Debian上安装
$ sudo apt update
$ sudo apt install ansible

### 在CentOS/RHEL上安装
$ sudo yum install epel-release
$ sudo yum install ansible

## 2. 配置Ansible

### 编辑hosts文件
# /etc/ansible/hosts
[mysql_servers]
mysql1 ansible_host=192.168.1.101
mysql2 ansible_host=192.168.1.102
mysql3 ansible_host=192.168.1.103

### 配置SSH密钥
$ ssh-keygen -t rsa
$ ssh-copy-id root@192.168.1.101
$ ssh-copy-id root@192.168.1.102
$ ssh-copy-id root@192.168.1.103

## 3. 编写Ansible Playbook

### MySQL安装Playbook
# mysql_install.yml
—
– hosts: mysql_servers
become: yes
tasks:
– name: Install MySQL dependencies
apt:
name: [‘wget’, ‘curl’, ‘gnupg’]
state: present
when: ansible_os_family == ‘Debian’

– name: Add MySQL repository
shell: |
wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
dpkg -i mysql-apt-config_0.8.22-1_all.deb
apt update
when: ansible_os_family == ‘Debian’

– name: Install MySQL server
apt:
name: mysql-server
state: present
when: ansible_os_family == ‘Debian’

– name: Start MySQL service
service:
name: mysql
state: started
enabled: yes

– name: Set MySQL root password
shell: mysql -e “ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’;”

### MySQL配置Playbook
# mysql_config.yml
—
– hosts: mysql_servers
become: yes
tasks:
– name: Copy MySQL config file
template:
src: my.cnf.j2
dest: /etc/mysql/my.cnf
notify: restart mysql

– name: Create MySQL user
mysql_user:
name: app_user
password: password
priv: ‘*.*:ALL’
host: ‘%’
state: present

handlers:
– name: restart mysql
service:
name: mysql
state: restarted

### MySQL备份Playbook
# mysql_backup.yml
—
– hosts: mysql_servers
become: yes
tasks:
– name: Create backup directory
file:
path: /backup
state: directory
mode: ‘0755’

– name: Install Percona XtraBackup
apt:
name: percona-xtrabackup-80
state: present
when: ansible_os_family == ‘Debian’

– name: Run full backup
shell: xtrabackup –backup –target-dir=/backup/full-$(date +%Y%m%d)

– name: Clean old backups
shell: find /backup -type d -name “full-*” -mtime +7 -exec rm -rf {} \;

## 4. 运行Playbook

### 安装MySQL
$ ansible-playbook mysql_install.yml

### 配置MySQL
$ ansible-playbook mysql_config.yml

### 备份MySQL
$ ansible-playbook mysql_backup.yml

## 5. 常用Ansible模块

### mysql_db
– 功能：管理MySQL数据库
– 示例：
– name: Create database
mysql_db:
name: fgsales
state: present

### mysql_user
– 功能：管理MySQL用户
– 示例：
– name: Create user
mysql_user:
name: app_user
password: password
priv: ‘fgsales.*:ALL’
state: present

### mysql_replication
– 功能：管理MySQL复制
– 示例：
– name: Setup replication
mysql_replication:
mode: changemaster
master_host: 192.168.1.101
master_user: repl
master_password: password
master_log_file: mysql-bin.000001
master_log_pos: 123456

## 1. 安装Terraform

### 在Linux上安装
$ wget https://releases.hashicorp.com/terraform/1.5.0/terraform_1.5.0_linux_amd64.zip
$ unzip terraform_1.5.0_linux_amd64.zip
$ sudo mv terraform /usr/local/bin/

## 2. 编写Terraform配置

### AWS RDS配置
# main.tf
provider “aws” {
region = “us-east-1”
}

resource “aws_db_instance” “mysql” {
allocated_storage = 20
db_instance_class = “db.t3.micro”
engine = “mysql”
engine_version = “8.0”
name = “fgsales”
username = “admin”
password = “password”
parameter_group_name = “default.mysql8.0”
skip_final_snapshot = true
}

output “db_instance_endpoint” {
value = aws_db_instance.mysql.endpoint
}

### Azure Database for MySQL配置
# main.tf
provider “azurerm” {
features {}
}

resource “azurerm_resource_group” “rg” {
name = “mysql-rg”
location = “East US”
}

resource “azurerm_mysql_server” “mysql” {
name = “mysql-server”
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
version = “8.0”
administrator_login = “admin”
administrator_password = “Password12345!”

sku_name = “B_Gen5_1”
}

resource “azurerm_mysql_database” “db” {
name = “fgsales”
resource_group_name = azurerm_resource_group.rg.name
server_name = azurerm_mysql_server.mysql.name
charset = “utf8”
collation = “utf8_unicode_ci”
}

### Google Cloud SQL配置
# main.tf
provider “google” {
project = “my-project”
region = “us-central1”
}

resource “google_sql_database_instance” “mysql” {
name = “mysql-instance”
database_version = “MYSQL_8_0”
region = “us-central1”

settings {
tier = “db-f1-micro”
}
}

resource “google_sql_database” “db” {
name = “fgsales”
instance = google_sql_database_instance.mysql.name
}

resource “google_sql_user” “user” {
name = “admin”
instance = google_sql_database_instance.mysql.name
password = “password”
}

## 3. 初始化和部署

### 初始化Terraform
$ terraform init

### 预览部署计划
$ terraform plan

### 执行部署
$ terraform apply

### 销毁资源
$ terraform destroy

## 4. 变量和模块

### 变量定义
# variables.tf
variable “db_instance_class” {
type = string
default = “db.t3.micro”
description = “Database instance class”
}

variable “db_password” {
type = string
sensitive = true
description = “Database password”
}

### 模块使用
# main.tf
module “mysql” {
source = “./modules/mysql”
db_name = “fgsales”
db_instance_class = var.db_instance_class
db_password = var.db_password
}

### 模块定义
# modules/mysql/main.tf
resource “aws_db_instance” “mysql” {
allocated_storage = 20
db_instance_class = var.db_instance_class
engine = “mysql”
engine_version = “8.0”
name = var.db_name
username = “admin”
password = var.db_password
parameter_group_name = “default.mysql8.0”
skip_final_snapshot = true
}

output “db_instance_endpoint” {
value = aws_db_instance.mysql.endpoint
}

## 1. 安装Jenkins

### 使用Docker安装
$ docker run -d \
–name jenkins \
-p 8080:8080 \
-p 50000:50000 \
-v jenkins_home:/var/jenkins_home \
jenkins/jenkins:lts

### 安装插件
– MySQL Database Plugin
– Pipeline Plugin
– Git Plugin
– Credentials Plugin

## 2. 配置Jenkins

### 配置MySQL凭证
– 进入Jenkins > Credentials > System > Global credentials
– 添加新凭证：Username with password
– 输入MySQL用户名和密码

### 配置Jenkins项目

#### 自由风格项目
– 进入Jenkins > New Item > Freestyle project
– 配置源代码管理：Git
– 配置构建触发器：定时构建或代码提交触发
– 配置构建步骤：执行shell

#### Pipeline项目
– 进入Jenkins > New Item > Pipeline
– 配置Pipeline脚本

## 3. 编写Pipeline脚本

### 数据库部署Pipeline
pipeline {
agent any

environment {
DB_HOST = ‘localhost’
DB_PORT = ‘3306’
DB_NAME = ‘fgsales’
DB_USER = credentials(‘mysql-username’)
DB_PASSWORD = credentials(‘mysql-password’)
}

stages {
stage(‘Checkout’) {
steps {
git ‘https://github.com/your-username/mysql-schema.git’
}
}

stage(‘Create Database’) {
steps {
sh “mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -e ‘CREATE DATABASE IF NOT EXISTS ${DB_NAME};'”
}
}

stage(‘Apply Schema’) {
steps {
sh “mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME} < schema.sql" } } stage('Run Migrations') { steps { sh "mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME} < migrations.sql" } } stage('Run Tests') { steps { sh "python test.py" } } } post { success { echo 'Database deployment successful!' } failure { echo 'Database deployment failed!' } } } ### 数据库备份Pipeline pipeline { agent any environment { DB_HOST = 'localhost' DB_PORT = '3306' DB_NAME = 'fgsales' DB_USER = credentials('mysql-username') DB_PASSWORD = credentials('mysql-password') BACKUP_DIR = '/backup' } stages { stage('Create Backup Directory') { steps { sh "mkdir -p ${BACKUP_DIR}" } } stage('Run Backup') { steps { sh "mysqldump -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} --all-databases > ${BACKUP_DIR}/backup-$(date +%Y%m%d).sql”
}
}

stage(‘Compress Backup’) {
steps {
sh “gzip ${BACKUP_DIR}/backup-$(date +%Y%m%d).sql”
}
}

stage(‘Clean Old Backups’) {
steps {
sh “find ${BACKUP_DIR} -name ‘backup-*.sql.gz’ -mtime +7 -exec rm -f {} \;”
}
}
}

post {
success {
echo ‘Backup completed successfully!’
}
failure {
echo ‘Backup failed!’
}
}
}

## 4. 运行Jenkins项目
– 手动触发：点击项目页面的「Build Now」按钮
– 定时触发：配置构建触发器为定时构建
– 代码提交触发：配置构建触发器为代码提交触发

## 5. 监控Jenkins构建
– 查看构建日志：项目页面 > Build History > 构建编号 > Console Output
– 配置邮件通知：项目页面 > Configure > Post-build Actions > Email Notification
– 配置Slack通知：安装Slack Notification Plugin，配置通知