This article introduces how to use Ansible automation to deploy MySQL master/slave replication. As the saying goes, “To do a good job, you must sharpen your tools.”
Ansible Automated ZooKeeper Deployment: Link to this blog post
The directory structure
Liheng @ liheng - ThinkPad: ~ / desktop $tree playbook playbook ├ ─ ─ the host ├ ─ ─ mysql. Yml ├ ─ ─ roles │ ├ ─ ─ mysql │ │ ├ ─ ─ files │ │ │ └ ─ ─ Mysql - 5.7.33 - Linux - glibc2.12 - x86_64. Tar. Gz │ │ ├ ─ ─ handlers │ │ ├ ─ ─ the tasks │ │ │ ├ ─ ─ add_user, yml │ │ │ ├ ─ ─ Change_slave_to_master. Yml │ │ │ ├ ─ ─ install_mysql. Yml │ │ │ ├ ─ ─ the main, yml │ │ │ └ ─ ─ pre_install. Yml │ │ ├ ─ ─ templates │ │ │ ├ ─ ─ my. CNF. J2 │ │ │ └ ─ ─ mysql. Service. J2 │ │ └ ─ ─ vars │ │ └ ─ ─ the main, ymlCopy the codeDeployment file
1. List of assets
liheng@liheng-ThinkPad:~/ desktop /playbook$cat host [mysql] 172.17.252.57 master=true 172.17.252.58 slave=true [mysql:vars] Master_ip = 172.17.252.57 slave_ip = 172.17.252.58Copy the code2. Script entrance
liheng@liheng-ThinkPad:~/ desktop /playbook$cat mysql.yml --- - hosts: mysql Gather_facts: yes roles: - mysqlCopy the code3. Customize variables
Liheng @ liheng - ThinkPad: ~ / desktop/playbook $cat roles/mysql/vars/main yml - mysql_version: Mysql -5.7.33-linux-glibc2.12-x86_64 mysql_install_path: /opt/data/mysql_data mysql_link: mysql_sock: / TMP /mysql.sock mysql_port: 33306 mysql_root_passwd: "Root_123^" #master_ip: 172.17.252.1 #slave_ip: 172.17.252.2 repl_user: repl repl_passwd: "Repl_123^" user: mysql group: mysqlCopy the code4. Configuration template file
Liheng @ liheng - ThinkPad: ~ / desktop/playbook $cat roles/mysql/templates/my CNF. J2 [client] port = {{mysql_port}} socket = {{ mysql_sock }} default-character-set=utf8mb4 [mysqldump] single-transaction [mysqld] port = {{ mysql_port }} socket = {{ mysql_sock }} character-set-server=utf8mb4 #skip-grant-tables sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBST ITUTION log_bin_trust_function_creators=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 gtid-mode = on enforce_gtid_consistency log-bin = on log-slave-updates = on #rpl_semi_sync_master_enabled=1 #rpl_semi_sync_master_timeout=1000 #rpl_semi_sync_slave_enabled=1 master_info_repository = TABLE relay_log_info_repository = TABLE replicate-ignore-table=mysql.failover_console datadir={{ mysql_install_path }}/{{ mysql_link }}/data {% if master is defined %} server-id=1 {% else %} server-id=2 {% endif %} read-only=0 #relay_log_purge=0 log_timestamps=SYSTEM lower_case_table_names=1 log_slave_updates=on skip-name-resolve #skip-networking back_log = 600 slave_parallel_workers = 16 slave-parallel-type = LOGICAL_CLOCK master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON slave_preserve_commit_order = 1 innodb_undo_directory={{ mysql_install_path }}/{{ mysql_link }}/undolog innodb_undo_tablespaces=4 innodb_undo_logs=128 innodb_max_undo_log_size=1024M innodb_purge_rseg_truncate_frequency innodb_undo_log_truncate=1 max_connections = 4000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 4096 table_open_cache_instances = 64 max_allowed_packet = 128M binlog_cache_size = 32M max_heap_table_size = 128M tmp_table_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 8M thread_cache_size = 64 query_cache_type = 0 query_cache_size = 0 #query_cache_size = 16M #query_cache_limit = 8M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = row expire_logs_days = 15 log_error ={{ mysql_install_path }}/{{ mysql_link }}/error.log slow_query_log = 1 long_query_time = 3 performance_schema = 0 explicit_defaults_for_timestamp #lower_case_table_names = 1 skip-external-locking default_storage_engine = InnoDB innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_stats_persistent_sample_pages = 64 innodb_open_files = 10000 innodb_buffer_pool_size = 90G innodb_write_io_threads = 24 innodb_read_io_threads = 24 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_log_buffer_size = 64M innodb_sort_buffer_size = 64M innodb_log_file_size = 1024M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 75 innodb_lock_wait_timeout = 120 log_warnings=1 #local-infile=0 #connection-control-failed-connections-threshold=10 #connection-control-min-connection-delay=10800 wait_timeout = 3600 interactive_timeout = 3600 innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:5GCopy the codeThe preceding MySQL configuration files can be flexibly adjusted based on service scenarios and server hardware configurations
MySQL service script
Liheng @ liheng - ThinkPad: ~ / desktop/playbook $cat roles/mysql/templates/mysql service. J2 #! /bin/sh # Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB # This file is public domain and comes with NO WARRANTY of any kind # MySQL daemon start/stop script. # Usually this is put in /etc/init.d (at least on machines SYSV R4 based # systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. # When this is done the mysql server will be started when the machine is # started and shut down when the systems goes down. # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36 # description: A very fast and reliable SQL database engine. # Comments to support LSB init script conventions ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs # Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: start and stop MySQL # Description: MySQL is a very fast and reliable SQL database engine. ### END INIT INFO # If you install MySQL on some other places than /usr/local/mysql, then you # have to do one of the following things for this script to work: # # - Run this script from within the MySQL installation directory # - Create a /etc/my.cnf file with the following information: # [mysqld] # basedir=<path-to-mysql-installation-directory> # - Add the above to any other configuration file (for example ~/.my.ini) # and copy my_print_defaults to /usr/bin # - Add the path to the mysql-installation-directory to the basedir variable # below. # # If you want to affect other MySQL variables, you should make your changes # in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files. # If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. basedir={{ mysql_install_path }}/{{ mysql_link }} datadir={{ mysql_install_path }}/{{ mysql_link }}/data ... .Copy the codeThe preceding configuration is the MySQL startup script. You only need to configure basedir(database deployment directory) and datadir(database data directory), and keep other Settings as default.
6. Deploy the main program entry
Liheng @ liheng - ThinkPad: ~ / desktop/playbook $cat roles/mysql/tasks/main yml - import_tasks: add_user. Yml - import_tasks: install_mysql.yml - import_tasks: change_slave_to_master.ymlCopy the codeScripts are executed from the top down according to the tasks defined in the main program.
7. Script assignments
liheng@liheng-ThinkPad:~/ desktop /playbook$cat roles/mysql/tasks/add_user.yml --- - name: add_mysql_user user: name: "{{ user }}" shell: /bin/bash tags: - add_mysql_user liheng @ liheng - ThinkPad: ~ / desktop/playbook $cat roles/mysql/tasks/install_mysql yml -- -- -- -- name: create workdir file: path: "{{ mysql_install_path }}" state: directory owner: "{{ user }}" group: "{{ group }}" 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:~/ desktop /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: absentCopy the codeYou can use ‘ansible-doc module name ‘to check the meaning and usage of the master/slave replication module for MySQL.
The deployment of
Now that you have all the listings and binaries for mysql5.7.33 ready to deploy the MySQL primary/secondary replication, you can deploy the MySQL primary/secondary replication with a single command:
liheng@liheng-ThinkPad:~/ desktop /playbook$ansible-playbook -i host mysql.ymlCopy the codeIn fact, there are many ways to achieve the automatic deployment of MySQL master-slave, the above is just one of them.
Some think
-
How to realize one master and many slaves?
-
How do I implement master master replication?
In fact, the above requirements are very easy to implement, just need to make a few adjustments in the above configuration list, which will not be explained here. Interested students, you can study by yourself, also can leave a message exchange.