Ansible自动化部署MySQL5.7.33主从复制

上山打老虎

上一篇文章简单介绍了使用ansible自动化不是zk集群，衔接上文，本篇文章简单介绍使用ansible自动化部署MySQL主从复制实现过程。还是那句话：“工欲善其事，必先利其器”。
ansible自动化部署zookeeper: 博文链接

目录结构

liheng@liheng-ThinkPad:~/桌面$ tree playbook 
<p>playbook</p>
<p>├── host</p>
<p>├── mysql.yml</p>
<p>├── roles</p>
<p>│   ├── mysql</p>
<p>│   │   ├── files</p>
<p>│   │   │   └── mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz</p>
<p>│   │   ├── handlers</p>
<p>│   │   ├── tasks</p>
<p>│   │   │   ├── add_user.yml</p>
<p>│   │   │   ├── change_slave_to_master.yml</p>
<p>│   │   │   ├── install_mysql.yml</p>
<p>│   │   │   ├── main.yml</p>
<p>│   │   │   └── pre_install.yml</p>
<p>│   │   ├── templates</p>
<p>│   │   │   ├── my.cnf.j2</p>
<p>│   │   │   └── mysql.service.j2</p>
<p>│   │   └── vars</p>
│   │     └── main.yml

部署文件
1、资产清单

liheng@liheng-ThinkPad:~/桌面/playbook$ cat host
<p>[mysql]</p>
<p>172.17.252.57 master=true</p>
<p>172.17.252.58 slave=true</p>
<p>[mysql:vars]</p>
<p>master_ip=172.17.252.57</p>
slave_ip=172.17.252.58

2、剧本入口

liheng@liheng-ThinkPad:~/桌面/playbook$ cat mysql.yml 
<hr />
<ul>
<li>hosts: mysql</li></ul>
<p>gather_facts: yes</p>
<p>roles:</p>
<ul>
<li>mysql

3、自定义变量

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/vars/main.yml 
<hr />
<p>mysql_version: mysql-5.7.33-linux-glibc2.12-x86_64</p>
<p>mysql_install_path: /opt/data/mysql_data</p>
<p>mysql_link: mysql</p>
<p>mysql_sock: /tmp/mysql.sock</p>
<p>mysql_port: 33306</p>
mysql_root_passwd: "Root_123^"<br><h1>master_ip: 172.17.252.1</h1><br><br><h1>slave_ip: 172.17.252.2</h1><br>
<p>repl_user: repl</p>
<p>repl_passwd: "Repl_123^"</p>
<p>user: mysql</p>
group: mysql

4、配置摸版文件

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/templates/my.cnf.j2 
<p>[client]</p>
<p>port = {{ mysql_port }}</p>
<p>socket = {{ mysql_sock }}</p>
<p>default-character-set=utf8mb4</p>
<p>[mysqldump]</p>
<p>single-transaction</p>
<p>[mysqld]</p>
<p>port = {{ mysql_port }}</p>
<p>socket = {{ mysql_sock }}</p>
character-set-server=utf8mb4<br><h1>skip-grant-tables</h1><br>
<p>sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</p>
<p>log_bin_trust_function_creators=1</p>
<p>innodb_flush_log_at_trx_commit=1</p>
<p>sync_binlog=1</p>
<p>gtid-mode = on</p>
<p>enforce_gtid_consistency</p>
<p>log-bin = on</p>
<p>log-slave-updates = on</p>
<br><h1>rpl_semi_sync_master_enabled=1</h1><br><br><h1>rpl_semi_sync_master_timeout=1000</h1><br><br><h1>rpl_semi_sync_slave_enabled=1</h1><br>
<p>master_info_repository = TABLE</p>
<p>relay_log_info_repository = TABLE</p>
<p>replicate-ignore-table=mysql.failover_console</p>
<p>datadir={{ mysql_install_path }}/{{ mysql_link }}/data</p>
<p>{% if master is defined %}</p>
<p>server-id=1</p>
<p>{% else %}</p>
<p>server-id=2</p>
<p>{% endif %}</p>
read-only=0<br><h1>relay_log_purge=0</h1><br>
<p>log_timestamps=SYSTEM</p>
<p>lower_case_table_names=1</p>
<p>log_slave_updates=on</p>
skip-name-resolve<br><h1>skip-networking</h1><br>
<p>back_log = 600</p>
<p>slave_parallel_workers = 16</p>
<p>slave-parallel-type = LOGICAL_CLOCK</p>
<p>master_info_repository = TABLE</p>
<p>relay_log_info_repository = TABLE</p>
<p>relay_log_recovery = ON</p>
<p>slave_preserve_commit_order = 1</p>
<p>innodb_undo_directory={{ mysql_install_path }}/{{ mysql_link }}/undolog</p>
<p>innodb_undo_tablespaces=4</p>
<p>innodb_undo_logs=128</p>
<p>innodb_max_undo_log_size=1024M</p>
<p>innodb_purge_rseg_truncate_frequency</p>
<p>innodb_undo_log_truncate=1</p>
<p>max_connections = 4000</p>
<p>max_connect_errors = 6000</p>
<p>open_files_limit = 65535</p>
<p>table_open_cache = 4096</p>
<p>table_open_cache_instances = 64</p>
<p>max_allowed_packet = 128M</p>
<p>binlog_cache_size = 32M</p>
<p>max_heap_table_size = 128M</p>
<p>tmp_table_size = 32M</p>
<p>read_buffer_size = 8M</p>
<p>read_rnd_buffer_size = 8M</p>
<p>sort_buffer_size = 8M</p>
<p>join_buffer_size = 8M</p>
<p>key_buffer_size = 8M</p>
<p>thread_cache_size = 64</p>
<p>query_cache_type = 0</p>
query_cache_size = 0<br><h1>query_cache_size = 16M  </h1><br><br><h1>query_cache_limit = 8M</h1><br>
<p>ft_min_word_len = 4</p>
<p>log_bin = mysql-bin</p>
<p>binlog_format = row</p>
<p>expire_logs_days = 15</p>
<p>log_error ={{ mysql_install_path }}/{{ mysql_link }}/error.log</p>
<p>slow_query_log = 1</p>
<p>long_query_time = 3</p>
<p>performance_schema = 0</p>
explicit_defaults_for_timestamp<br><h1>lower_case_table_names = 1</h1><br>
<p>skip-external-locking</p>
<p>default_storage_engine = InnoDB</p>
<p>innodb_flush_method = O_DIRECT</p>
<p>innodb_file_per_table = 1</p>
<p>innodb_stats_persistent_sample_pages = 64</p>
<p>innodb_open_files = 10000</p>
<p>innodb_buffer_pool_size = 90G</p>
<p>innodb_write_io_threads = 24</p>
<p>innodb_read_io_threads = 24</p>
<p>innodb_thread_concurrency = 0</p>
<p>innodb_purge_threads = 1</p>
<p>innodb_log_buffer_size = 64M</p>
<p>innodb_sort_buffer_size = 64M</p>
<p>innodb_log_file_size = 1024M</p>
<p>innodb_log_files_in_group = 3</p>
<p>innodb_max_dirty_pages_pct = 75</p>
<p>innodb_lock_wait_timeout = 120</p>
log_warnings=1<br><h1>local-infile=0</h1><br><br><h1>connection-control-failed-connections-threshold=10</h1><br><br><h1>connection-control-min-connection-delay=10800</h1><br>
<p>wait_timeout = 3600</p>
<p>interactive_timeout = 3600</p>
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:5G

以上MySQL配置文件，可根据业务场景及服务器硬件配置灵活调整
5、MySQL服务脚本

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/templates/mysql.service.j2 <br><h1>!/bin/sh</h1><br><br><h1>Copyright Abandoned 1996 TCX DataKonsult AB &amp; Monty Program KB &amp; Detron HB</h1><br><br><h1>This file is public domain and comes with NO WARRANTY of any kind</h1><br>
<br><h1>MySQL daemon start/stop script.</h1><br>
<br><h1>Usually this is put in /etc/init.d (at least on machines SYSV R4 based</h1><br><br><h1>systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.</h1><br><br><h1>When this is done the mysql server will be started when the machine is</h1><br><br><h1>started and shut down when the systems goes down.</h1><br>
<br><h1>Comments to support chkconfig on RedHat Linux</h1><br><br><h1>chkconfig: 2345 64 36</h1><br><br><h1>description: A very fast and reliable SQL database engine.</h1><br>
<br><h1>Comments to support LSB init script conventions<br><h3>BEGIN INIT INFO</h3><br></h1><br><br><h1>Provides: mysql</h1><br><br><h1>Required-Start: $local_fs $network $remote_fs</h1><br><br><h1>Should-Start: ypbind nscd ldap ntpd xntpd</h1><br><br><h1>Required-Stop: $local_fs $network $remote_fs</h1><br><br><h1>Default-Start:  2 3 4 5</h1><br><br><h1>Default-Stop: 0 1 6</h1><br><br><h1>Short-Description: start and stop MySQL</h1><br><br><h1>Description: MySQL is a very fast and reliable SQL database engine.<br><h3>END INIT INFO</h3><br></h1><br>
<br><h1>If you install MySQL on some other places than /usr/local/mysql, then you</h1><br><br><h1>have to do one of the following things for this script to work:</h1><br><br><h1># - Run this script from within the MySQL installation directory</h1><br><br><h1>- Create a /etc/my.cnf file with the following information:</h1><br><br><h1>[mysqld]</h1><br><br><h1>basedir=&lt;path-to-mysql-installation-directory&gt;</h1><br><br><h1>- Add the above to any other configuration file (for example ~/.my.ini)</h1><br><br><h1>and copy my_print_defaults to /usr/bin</h1><br><br><h1>- Add the path to the mysql-installation-directory to the basedir variable</h1><br><br><h1>below.</h1><br><br><h1># If you want to affect other MySQL variables, you should make your changes</h1><br><br><h1>in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.</h1><br>
<br><h1>If you change base dir, you must also change datadir. These may get</h1><br><br><h1>overwritten by settings in the MySQL configuration files.</h1><br>
<p>basedir={{ mysql_install_path }}/{{ mysql_link }}</p>
<p>datadir={{ mysql_install_path }}/{{ mysql_link }}/data</p>
<p>...</p>
...

以上配置为MySQL启动脚本，只需配置好basedir(数据库部署目录)和datadir(数据库数据目录)，其余配置保持默认即可。
6、部署主程序入口

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/main.yml 
<hr />
<ul>
<li>import_tasks: add_user.yml</li>
<li>import_tasks: install_mysql.yml</li>
<li>import_tasks: change_slave_to_master.yml

剧本会按照主程序中定义的任务(task)，自上而下执行。
7、剧本任务

<p>liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/add_user.yml</p>
<hr />
<ul>
<li>name: add_mysql_user</li></ul>
<p>user:</p>
<p>name: "{{ user }}"</p>
<p>shell: /bin/bash</p>
<p>tags:</p>
<ul>
<li>add_mysql_user      </li></ul>
<p>liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/install_mysql.yml</p>
<hr />
<ul>
<li>name: create workdir</li></ul>
<p>file:</p>
<p>path: "{{ mysql_install_path }}"</p>
<p>state: directory</p>
<p>owner: "{{ user }}"</p>
<p>group: "{{ group }}"</p>
recurse: yes

• name: copy_mysql_tar
  copy:
  src: "{{ mysql_version }}.tar.gz"
  dest: "{{ mysql_install_path }}"
  owner: "{{ user }}"
  group: "{{ group }}"
  tags:
  
  
  
  • copy_mysql_tar
    
  
  
• name:  unarchive_mysql_tar
  unarchive:
  src:  "{{ mysql_install_path }}/{{ mysql_version }}.tar.gz"
  dest: "{{ mysql_install_path }}"
  copy: no
  owner: "{{ user }}"
  group: "{{ group }}"
  tags:
  
  
  
  • unarchive_mysql_tar
    
  
  
• name: chown_mysql
  file:
  dest: "{{ mysql_install_path }}/{{mysql_version}}"
  owner: "{{ user }}"
  group: "{{ group }}"
  recurse: yes
  tags:
  
  
  
  • chown_mysql
    
  
  
• name: link_mysql
  file:
  src: "{{ mysql_install_path }}/{{mysql_version}}"
  dest: "{{ mysql_install_path }}/{{ mysql_link }}"
  owner: "{{ user }}"
  group: "{{ group }}"
  state: link
  tags:
  
  
  
  • link_mysql
    
  
  
• name: create undologdir
  file:
  path: "{{ mysql_install_path }}/{{ mysql_link }}/undolog"
  state: directory
  owner: "{{ user }}"
  group: "{{ group }}"
  
• name: create datadir
  file:
  path: "{{ mysql_install_path }}/{{ mysql_link }}/data"
  state: directory
  owner: "{{ user }}"
  group: "{{ group }}"
  
• name: copy_my.cnf
  template:
  src: my.cnf.j2
  dest: /etc/my.cnf
  tags:
  
  
  
  • copy_my.cnf
    
  
  
• name: init_mysql
  shell: ./bin/mysqld --initialize  --user=mysql
  args:
  chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags:
  
  
  
  • init_mysql
    
  
  
• name: get_mysql_passwd
  shell: cat ./error.log |grep localhost|grep "temporary password"|awk '{print $NF}'
  register: mysql_init_passwd
  args:  
  chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags:
  
  
  
  • get_mysql_passwd
    
  
  
• name: dispaly_passwd
  debug:
  msg: "{{ mysql_init_passwd.stdout }}"
  tags:
  
  
  
  • dispaly_passwd
    
  
  
• name: copy_mysql.server
  template:
  src: mysql.service.j2
  dest: "/etc/init.d/mysql.server"
  mode: 0755
  tags:
  
  
  
  • copy_mysql.server
    
  
  
• name: add_mysql_systemd
  template:
  src: mysql.service.j2
  dest: /etc/systemd/system/mysql-{{ mysql_port }}.service
  tags:
  
  
  
  • add_mysql_systemd
    
  
  
• name: start_mysql_service
  command: /etc/init.d/mysql.server start
  tags:
  
  
  
  • start_mysql_service
    
  
  
• name: alter_passwd
  shell: ./bin/mysqladmin -u root -p'{{mysql_init_passwd.stdout}}' password '{{ mysql_root_passwd }}'
  args:
  chdir: "{{ mysql_install_path }}/{{ mysql_link }}"
  tags:
  
  
  
  • alter_passwd
    
  
  

liheng@liheng-ThinkPad:~/桌面/playbook$ cat roles/mysql/tasks/change_slave_to_master.yml

• name: create_user
  mysql_user:
  login_host: localhost
  login_port: "{{ mysql_port }}"
  login_user: root
  login_unix_socket: "{{ mysql_sock }}"
  login_password: "{{ mysql_root_passwd }}"
  name: "{{ repl_user }}"
  password: "{{ repl_passwd }}"
  priv: ".:ALL"
  state: present
  host: "%"
  when: master is defined
  tags:
  
  
  
  • create_user
    
  
  
• name: change_slave_to_master
  mysql_replication:
  login_unix_socket: "{{ mysql_sock }}"
  login_host: localhost
  login_port: "{{ mysql_port }}"
  login_user: root     
  login_password: "{{ mysql_root_passwd }}"
  master_host: "{{ master_ip }}"
  master_user: "{{ repl_user }}"
  master_password: "{{ repl_passwd }}"
  master_port: "{{ mysql_port }}"
  master_auto_position: 1
  mode: changemaster
  when: slave is defined
  tags:
  
  
  
  • change_slave_to_master
    
  
  
• name: start_slave
  mysql_replication:
  login_unix_socket: "{{ mysql_sock }}"
  login_user: root
  login_host: localhost
  login_port: "{{ mysql_port }}"
  login_password: "{{ mysql_root_passwd }}"
  mode: startslave
  when: slave is defined
  tags:
  
  
  
  • start_slave
    
  
  
• name: get_slave_info
  mysql_replication:
  login_host: localhost
  login_user: root
  login_port: "{{ mysql_port }}"
  login_password: "{{ mysql_root_passwd }}"
  login_unix_socket: "{{ mysql_sock }}"
  mode: getslave
  when: slave is defined
  register: info
  tags:
  
  
  
  • get_slave_info
    
  
  
• name: dispaly_slave
  debug:
  msg: "Slave_IO_Running={{ info.Slave_IO_Running }}       Slave_SQL_Running={{ info.Slave_SQL_Running }}"
  when: slave is defined
  tags:
  
  
  
  • dispaly_slave
    
  
  
• name: clean_pkgs
  file:
  path: '/opt/{{ mysql_version }}.tar.gz'
  state: absent

  > 以上通过ansible部署MySQL主从复制所用到的模块，可以通过'ansible-doc 模块名'查看其具体含义和使用方法。

部署
现在已经准备好部署MySQL主从复制所需的所有清单及mysql5.7.33的二进制文件，只需要通过一条命令即可完成MySQL主从复制的部署工作：

liheng@liheng-ThinkPad:~/桌面/playbook$ ansible-playbook -i host mysql.yml

其实实现MySQL主从自动化部署的方式有很多种，以上只是其中的一种实现。

一些思考

• 如何实现一主多从？
  
• 如何实现主主复制？
  

实际要实现以上需求也是非常容易的，只需要在以上配置清单中稍作调整即可实现，这里就不展开说明了。有兴趣的同学，可以自行研究，也可留言交流。