从0到1的学习Ansible（6）-----MySQL数据库的主从复制（GTID）

川长思鸟来 2023-06-20 06:29 72阅读 0赞

### 说在前面 ###

ansible真的是好用，一劳永逸～  
今天讲解的是用ansible实现批量安装MySQL数据库（二进制文件），并且自动搭建基于GTID的主从复制～  
各为读者在看其他博主的博客时，最好先看一下他们的运行环境，因为有可能你和他的运行环境有差别，导致各种报错～  
所以如果实验操作对运行环境有要求的话，我都会先介绍运行环境～

本文中有很多基础知识在前面的ansible系列博文中已经讲得很清楚了，如果读者还有什么不懂的，请学习我的ansible系列，保证你能学会～

### 自动化部署MySQL的主从复制 ###

##### 运行环境介绍 #####

SELinux 所有节点都需要关闭，如果没有，你可以使用ansible去将各节点的SELInux关闭，以防在后面的搭建中，因为安全上下文，SELinux域的问题而导致报错～

各节点都能上网，因为我有一行代码是在线安装 mysql-python

使用的用户：  
各节点都需要有此用户，最好uid相同  
如果用户跟我不一样，也没关系，后面的 yml文件中写的部分路径适当修改即可～  
***如果不想麻烦，请跟我设置一模一样的环境（使用用户，各节点的IP，ip配置在eth0网卡下，子网掩码24位，以及下面的几个文件，环境跟我一样，在copy学习，就容易多了～特别是初学者！）***

$ id
    uid=1005(ansible) gid=1005(ansible) groups=1005(ansible)

当前目录的：

*  ansible.cfg 配置文件

$ cat ansible.cfg 
    [defaults]
    
    inventory      = ./hosts
    roles_path    = ./roles
    
    [privilege_escalation]
    become=True
    become_method=sudo
    become_user=root
    become_ask_pass=False

*  hosts文件  
    很重要，关系着集群中 谁是主，谁是从

$ cat hosts 
    [master]
    172.25.1.1
    
    [slave]
    172.25.1.2
    172.25.1.5
    
    [cluster:children]
    master
    slave

先建立一个专门用于管理MySQL的目录

$ mkdir mysql

创建一个用于存放脚本，配置文件的目录

$ mkdir mysql/files

ansible控制机必须对所有节点进行ssh免密，实现ssh 其他主机，免密登陆～

$ ssh-keygen
    $ ssh-copy-id 集群中的所有主机

由于我使用的是MySQL二进制文件，所以如果你已经安装了mariadb，请卸载干净，否则会有影响～具体操作，是 yum remove xxxx ,rm -fr /var/log/mariadb (这个得根据你配置文件写的路径)

到此环境介绍结束～

##### yml文件的编写 #####

$ cat mysql/install.yml 
    ---
    - hosts: cluster 
      tasks:
      - name: get mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
        copy:
          src: mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
          dest: /home/ansible/
    
      - name: get my.cnf
        copy:
          src: my.cnf
          dest: /etc/my.cnf
    
      - name: get a script
        copy:
          src: "{ { item }}"
          dest: /home/ansible/
          owner: root
          group: ansible
          mode: '0770'
        loop:
          - install_mysql.sh
          - restart_mysql.sh
    
      - name: stop firewalld
        service:
          name: firewalld
          state: stopped
          enabled: no
    
      - name: use a script for install,start and change root password the mysql
        shell: sh install_mysql.sh
        args:
          chdir: /home/ansible/
    
      - name: install mysql-python
        yum:
          name: https://mirrors.aliyun.com/centos/7.7.1908/os/x86_64/Packages/MySQL-python-1.2.5-1.el7.x86_64.rpm?spm=a2c6h.13651111.0.0.776e2f70Q9eRQy&file=MySQL-python-1.2.5-1.el7.x86_64.rpm
          state: installed
    
    
    - hosts: master
      tasks:
      - name: add user repl for replication
        mysql_user:
          login_user: root
          login_password: 'root123'
          login_unix_socket: /tmp/mysql.sock
          name: repl
          password: 'repl123'
          priv: '*.*:ALL'
          host: 172.25.1.%
          state: present
        
    - hosts: slave
      tasks:
      - name: edit my.cnf to change server-id and server-id -eq ip'last eight bit
        shell: sed "/^server-id/c server-id=`ifconfig  eth0 | grep netmask | awk '{ print $2}' |awk -F'.' '{ print $4}'`" /etc/my.cnf -i
    
      - name: restart mysql
        shell: sh restart_mysql.sh 
        args:
          chdir: /home/ansible/
    
      - name: become a slave
        shell: /usr/local/mysql/bin/mysql -uroot -proot123 -e "change master to master_host='172.25.1.1',master_user='repl',master_password='repl123',master_auto_position=1"
      - name: start slave
        shell: /usr/local/mysql/bin/mysql -uroot -proot123 -e 'start slave'

*  我觉得最叼的模块就是 shell 模块了，可以使用一切能够使用的 shell 命令

#### file目录下的文件 ####

$ ls mysql/files/
    install_mysql.sh  mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
    my.cnf            restart_mysql.sh

*  **install\_mysql.sh 是mysql二进制文件的安装脚本，并且修改了 mysql 的 root 用户密码**

$ cat mysql/files/install_mysql.sh 
    #! /bin/bash
    #MySQL5.7二进制文件安装脚本
    
    id mysql &> /dev/null || useradd mysql -s /sbin/nologin
    
    [ -d /data/mysql ] || { 
    mkdir /data/mysql -p
    chmod 775 /data/mysql -R
    chown mysql.mysql /data/mysql -R
    }
    
    if [ ! -e /usr/local/mysql ]
    then
    	cd /home/ansible/
    	tar zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    	cd /usr/local/
    	ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql
    	chown mysql.mysql -R mysql
    	sed '/^export/a export PATH=$PATH:/usr/local/mysql/bin/' /etc/profile -i
    	source /etc/profile
    	mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql \
    		 --datadir=/data/mysql/ --user=mysql --initialize     
    	mysqld_safe --defaults-file=/etc/my.cnf &
    	sleep 5
    	echo '' &> /dev/null  	
    	passwd=`cat /data/mysql/error.log | grep password \
                     | awk -F'root@localhost: ' '{print $2}'`
    	mysqladmin -uroot -p$passwd password "root123"
    fi

*  **restart\_mysql.sh 是重启 mysql 的脚本**

$ cat mysql/files/restart_mysql.sh 
    #! /bin/bash
    netstat -tnlp | grep 3306  &> /dev/null && {
    /usr/local/mysql/bin/mysqladmin -uroot -proot123 shutdown
    /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf & 
    echo "" > /dev/null
    } || {
    /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
    echo "" > /dev/null
    }

*  **my.cnf 是数据库的配置文件**

$ cat mysql/files/my.cnf 
    #my.cnf
    [client]
    port            = 3306
    socket          = /tmp/mysql.sock
    
    [mysql]
    prompt="\\u@\\h [\\d]>" 
    #pager="less -i -n -S"
    #tee=/opt/mysql/query.log
    no-auto-rehash
    
    [mysqld_multi]
    mysqld = /usr/local/mysql/bin/mysqld_safe
    mysqladmin = /usr/local/mysql/bin/mysqladmin
    log = /data/mysql/mysqld_multi.log
    
    [mysqld]
    #misc
    user = mysql
    basedir = /usr/local/mysql
    datadir = /data/mysql/
    port = 3306
    socket = /tmp/mysql.sock
    event_scheduler = 0
    
    tmpdir = /data/mysql/
    #timeout
    interactive_timeout = 300
    wait_timeout = 300
    
    #character set
    character-set-server = utf8
    
    open_files_limit = 65535
    max_connections = 100
    max_connect_errors = 100000
    #lower_case_table_names =1
    #logs
    log-output=file
    slow_query_log = 1
    slow_query_log_file = /data/mysql/slow.log
    log-error = /data/mysql/error.log
    log_warnings = 2
    pid-file = mysql.pid
    long_query_time = 1
    #log-slow-admin-statements = 1
    #log-queries-not-using-indexes = 1
    log-slow-slave-statements = 1
    
    #binlog
    #binlog_format = STATEMENT
    binlog_format = row
    server-id = 1
    log-bin = /data/mysql/mysql-bin
    binlog_cache_size = 4M
    max_binlog_size = 256M
    max_binlog_cache_size = 1M
    sync_binlog = 0
    expire_logs_days = 10
    #procedure 
    log_bin_trust_function_creators=1
    
    #
    gtid_mode = on
    enforce_gtid_consistency = on
    
    
    #relay log
    skip_slave_start = 1
    max_relay_log_size = 128M
    relay_log_purge = 1
    relay_log_recovery = 1
    relay-log=relay-bin
    relay-log-index=relay-bin.index
    log_slave_updates
    #slave-skip-errors=1032,1053,1062
    #skip-grant-tables
    
    #buffers & cache
    table_open_cache = 2048
    table_definition_cache = 2048
    table_open_cache = 2048
    max_heap_table_size = 96M
    sort_buffer_size = 128K
    join_buffer_size = 128K
    thread_cache_size = 200
    query_cache_size = 0
    query_cache_type = 0
    query_cache_limit = 256K
    query_cache_min_res_unit = 512
    thread_stack = 192K
    tmp_table_size = 96M
    key_buffer_size = 8M
    read_buffer_size = 2M
    read_rnd_buffer_size = 16M
    bulk_insert_buffer_size = 32M
    
    #myisam
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    
    #innodb
    innodb_buffer_pool_size = 1024M
    innodb_buffer_pool_instances = 1
    innodb_data_file_path = ibdata1:100M:autoextend
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 100M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 50
    innodb_file_per_table = 1
    innodb_rollback_on_timeout
    innodb_status_file = 1
    innodb_io_capacity = 2000
    transaction_isolation = REPEATABLE-READ
    innodb_flush_method = O_DIRECT

*  **mysql-5.7.26-linux-glibc2.12-x86\_64.tar.gz 是mysql的二进制包**

请自行下载：

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

如果命令行不好使的话，请去以下网站下载，超简单：  
[点我查看如何下载mysql二进制包][mysql]

#### 执行看效果 ####

$ ansible-playbook mysql/install.yml

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]\* 运行成功

**master端 登陆mysql**

*  我在脚本中设置的，修改mysql的root密码为 root123

$ mysql -uroot -proot123

*  可以看到由两个slave

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]在主库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]从库查看是否同步成功：

*  同步成功  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]

### 说在后面 ###

其时实话实说，有时候ansible真的是玄学，我在编写的时候，运行测试一点问题都没有，然后就突然出问题了，各种奇怪的报错～比如我的slave机器的shell命令行直接崩了，无法正常使用，所以我将他们都恢复了快照，使用的是脚本纯净的环境，然后报错解决了（没有修改任何代码）。可见使用一个纯净的环境是有多么的重要！

[mysql]: https://blog.csdn.net/weixin_44297303/article/details/92772315
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191208231205148.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191208231235491.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191208231622988.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191208231812751.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191208232215510.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw==,size_16,color_FFFFFF,t_70