mysqltools

一个用于快速构建大规模，高质量，全自动化的 mysql分布式集群环境的工具；包含mysql 安装、备份、监控、高可用、读写分离、优化、巡检、自行化运维

Stars
1.1K
View Code on GitHub
Ecosystems: Python, Ansible, MySQL

mysqltools

mysql-8.0 https://github.com/Neeky/dbm-agent

mysqltoolsdba SQL

1**** () 2**** (100   )

  1. KFC vs ****

    KFC80KFC808181

    KFC KFC

    DBA 1:) 2:) ****

  2. **** vs ****

    MySQL+MyCATMySQL()100+MySQL 100+ 1:1

    DBA----

  3. mysqltools &&MySQL

  4. 1mysqltools****playbook

    2mysqltoolsansible

    31 , 2 MySQL(master -->slavemysql-group-replication multi-source-replication)

    4mysqltoolsMySQLMHAMyCAT

    5mysqltools**** xtrabackup,mysql enterprise backup,mysqldump

    6mysqltools****MySQLzabbix

    7mysqltoolsmysqltools-python

    mysqltoolsmysqltoolsmysqltoolsPython-3.x python-3.xansible mysqltools**Pythonansible**

mysqltools

172.16.192.131.

**** ip **** 
      | 172.16.192.131     |centos-7.4    |
      | 172.16.192.132     |centos-7.4    |

... | ... |centos-7.x |

  1. 1): yummysqltoolsPython-3.6.2gcc ...

    2): root()

    3): yum

  2. mysqltoolsgithubhttps://github.com/Neeky/mysqltools/archive/master.zip

    linux/usr/local/

    cd /tmp/
wget https://github.com/Neeky/mysqltools/archive/master.zip &

ll -h /tmp/                                                                           
-rwxr-xr-x. 1 root  root  194M 3  23 11:52 master.zip

unzip master.zip

mv mysqltools-master /usr/local/mysqltools

  3. Python

    mysqltoolsPythonyum

    cd /usr/local/mysqltools/deploy/packages/python/
bash install.sh


    Collecting setuptools
Collecting pip
Installing collected packages: setuptools, pip
Successfully installed pip-9.0.1 setuptools-28.8.0

    python3

    source /etc/profile

python3 --version
Python 3.6.2

  4. ansible

    ansiblemysqltoolspython

    source /etc/profile
cd /usr/local/mysqltools/deploy/packages/ansible
bash install.sh 


    Using /usr/local/python-3.6.2/lib/python3.6/site-packages
Finished processing dependencies for ansible==2.4.0.0

  5. ansiblemysqltools

    1): ansible

    # ansible
mkdir -p /etc/ansible
touch /etc/ansible/hosts

    /etc/ansible/hosts

    host_131 ansible_user=root ansible_host=172.16.192.131
host_132 ansible_user=root ansible_host=172.16.192.132

    2): ssh

    ssh-keygen
ssh-copy-id [email protected]
ssh-copy-id [email protected]

    ssh-keygen # 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:D9kR6/ehu5O99p/LRJlZWNqwZ0tzU4+jvPegq7j/Pq8 root@studio2018
The keys randomart image is:
+---[RSA 2048]----+
|          .   . o|
|           o   Oo|
|          o   *+B|
|         + o ..+X|
|        S o + .* |
|         o . +.. |
|          . oo+. |
|         .  ++=o.|
|        ooo+EOo**|
+----[SHA256]-----+


ssh-copy-id [email protected] # yesroot
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '172.16.192.131 (172.16.192.131)' can't be established.
ECDSA key fingerprint is SHA256:qdoqi3B2aqO3ssOIphwOiWLywSlAoflX2YH+LCG7T/E.
ECDSA key fingerprint is MD5:8f:78:6e:20:ab:d0:2a:6b:c0:1a:e5:09:ac:82:7d:04.
Are you sure you want to continue connecting (yes/no)? 
[email protected]'s password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '[email protected]'"
and check to make sure that only the key(s) you wanted were added.

.... ....

    3): ansible

    ansible -m ping host_132

host_132 | SUCCESS => {
    "changed": false,
    "failed": false,
    "ping": "pong"
}

    4): mysqltools

    mysqltoolsmysqltools/config.yaml yamlmtls_base_dirmysql_packages_dirmysql_package

    1mtls_base_dirmysqltoolsmysqltools/usr/local/mtls_base_dir"/usr/local/mysqltools/"

    2mysql_packages_dirMySQLMySQL600+MBmysqltoolsMySQLmysql_packages_dirmysqltoolsMySQL

    3mysql_packageMySQLMySQL mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

    config.yaml

    mtls_base_dir: /usr/local/mysqltools/
mysql_packages_dir: /usr/local/src/mysql/
mysql_package: mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

    mysqltools'/'

    mysqltoolsconfig.yaml

    ---
#----------------------------------mysqltools---------------------
# section 1 #mysqltools
mtls_base_dir: /usr/local/mysqltools/
#           #mysqltool()
mtls_packages: deploy/packages/
#           #mysqltoolpython
mtls_client_base_dir: /usr/local/


#  section 2  #mysqltools mysql
#             #mysqlmysqltools         
mtls_apr: apr-1.6.2.tar.gz
mtls_apr_util: apr-util-1.6.0.tar.gz
mtls_httpd: httpd-2.4.28.tar.gz
mtls_php: php-5.6.31.tar.gz
#mtls_zabbix: zabbix-3.4.3.tar.gz
mtls_zabbix: zabbix-4.0.0.tar.gz
mtls_python: python-3.6.2.tar.xz
mtls_mysql_connector_python: mysql-connector-python-2.1.5.tar.gz
mtls_mycat: mycat-server-1.6.5-linux.tar.gz
mtls_mha_node: mhanode.tar.gz
mtls_mha_manager: mhamanager.tar.gz
mtls_git: git-2.9.5.tar.gz
mtls_nginx: nginx-1.13.7.tar.gz
mtls_sysbench: sysbench-1.1.0.tar.gz
mtls_meb: meb-4.1.0-linux-glibc2.5-x86-64bit.tar.gz
mtls_xtrb: percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
mtls_mysqlclient: mysqlclient-1.3.12.tar.gz
mtls_pytz: pytz-2018.4.tar.gz
mtls_django: django-2.0.4.tar.gz
mtls_uwsgi: uwsgi-2.0.17.tar.gz

#mysqlphp-5.6.x yesphpmysqclient_r.so
mtls_with_php: 1
#ansiblepython-3.xmysql-connector-python
mtls_with_mysql_conntor_python: 1
#mysql
mtls_make_mysql_secure: 1
#
mtls_with_mysql_group_replication: 0
#----------------------------------mysqltools---------------------


####
#### mysql 
####
#mysql 
mysql_packages_dir: /usr/local/src/mysql/
#mysql 
#mysql_package: mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
#mysql_package: mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
mysql_package: mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
#linux mysql
mysql_user: mysql
mysql_group: mysql
mysql_user_uid: 3306
mysql_user_gid: 3306
#mysql 
mysql_base_dir: /usr/local/mysql/
#mysql datadirmysql_data_dir_base+mysql_port
mysql_data_dir_base: /database/mysql/data/
mysql_port: 3306
mysql_root_password: mtls0352
mysql_zabbix_password: mtls
mysql_rple_user: repl
mysql_rple_password: repl0352
mysql_mha_user: mha
mysql_mha_password: mtls0352
mysql_app_user: appuser
mysql_app_password: mtls0352
mysql_monitor_user: monitor
mysql_monitor_password: monitor0352
mysql_backup_user: backup
mysql_backup_password: DX3906
#mysql 
mysql_binlog_format: row
mysql_innodb_log_files_in_group: 16
mysql_innodb_log_file_size: 256M
mysql_innodb_log_buffer_size: 64M
mysql_innodb_open_files: 65535
mysql_max_connections: 1000
mysql_thread_cache_size: 256
mysql_sync_binlog: 1
mysql_binlog_cache_size: 64K
mysql_innodb_online_alter_log_max_size: 128M
mysql_performance_schema: 'on'
use_write_set: 1

#mysql 

####
#### zabbix 
#####
zabbix_server_ip: 172.16.192.131

    mysqltoolslinuxmysqltools/config.yaml-for-linux cofnig.yaml

    5): MySQL

    MySQL**/usr/local/src/mysql/**mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

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

    cd /usr/local/src/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

mysqltools

mysqltools

  1. mysqltools

    cd /usr/local/mysqltools/
tree ./
 README.md                // 
 config.yaml              // Mac(Mac)
 config.yaml-for-linux    // linux
 deploy                   // 
 docs                     // 
 trashCan                 // 
 tuning                   // DBASQL

  2. mysql

    mysqltools/deploy/ansible/ MySQLmysql

    1. mysql

      cd /usr/local/mysqltools/deploy/ansible/mysql/
ll 

       24
drwxr-xr-x. 2 root root 4096 3  19 15:01 common                                 # 
-rw-r--r--. 1 root root  836 3  19 15:01 install_group_replication.yaml         # mysql group replication
-rw-r--r--. 1 root root  889 3  19 15:01 install_master_slaves.yaml             # mysql 
-rw-r--r--. 1 root root  924 3  19 15:01 install_multi_source_replication.yaml  # mysql 
-rw-r--r--. 1 root root  772 3  26 13:20 install_single_mysql.yaml              # mysql 
drwxr-xr-x. 3 root root  203 3  19 15:01 template                               #         
-rw-r--r--. 1 root root  892 3  19 15:01 upgrad_single_mysql.yaml               # MySQL()
drwxr-xr-x. 2 root root   99 3  19 15:01 vars                                   # (!)
-rw-r--r--  1 root root   99 3  19 15:01 uninstall.yaml                         # ()

      /usr/local/mysqltools/deploy/ansible//usr/local/mysqltools/deploy/ansible/****, .yamlinstall_single_mysql.yamlMySQL

    2. install_single_mysql.yaml

      ---
 - hosts: cstudio
   remote_user: root
   become_user: yes
   vars_files:
    - ../../../config.yaml
   tasks:
    - name: create user and config file
      import_tasks: common/create_user_and_config_file.yaml

      - hosts: cstudio cstudio install_single_mysql.yamlMySQL

      host_132MySQLcstudiohost_132host_132/etc/ansible/hostsinstall_single_mysql.yaml

      ---
 - hosts: host_132
   remote_user: root
   become_user: yes
   vars_files:
    - ../../../config.yaml
   tasks:
    - name: create user and config file
      import_tasks: common/create_user_and_config_file.yaml

    3. mysqltoolsansibleansibleMySQL

      ansible-playbook install_single_mysql.yaml 


      
PLAY [host_132] *********************************************************************************************

TASK [Gathering Facts] **************************************************************************************
ok: [host_132]

TASK [create mysql user] ************************************************************************************
changed: [host_132]

TASK [create and config /etc/my.cnf] ************************************************************************
changed: [host_132]

TASK [install libaio-devel] *********************************************************************************
ok: [host_132]

TASK [install numactl-devel] ********************************************************************************
ok: [host_132]

TASK [transfer mysql install package to remote host and unarchive to /usr/local/] ***************************
changed: [host_132]

TASK [change owner to mysql user] ***************************************************************************
changed: [host_132]

TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] **********************************************
changed: [host_132]

TASK [export mysql share object (*.os)] *********************************************************************
ok: [host_132]

TASK [load share object] ************************************************************************************
changed: [host_132]

TASK [export path env variable] *****************************************************************************
ok: [host_132]

TASK [export path env to /root/.bashrc] *********************************************************************
ok: [host_132]

TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] **********************************************
ok: [host_132]

TASK [create libmysqlclient_r.so file for php-5.6] **********************************************************
changed: [host_132]

TASK [create datadir] ***************************************************************************************
changed: [host_132]

TASK [initialize-insecure] **********************************************************************************
changed: [host_132]

TASK [create systemd config file] ***************************************************************************
changed: [host_132]

TASK [start mysql(sytemctl)] ********************************************************************************
changed: [host_132]

TASK [config mysql.service start up on boot] ****************************************************************
ok: [host_132]

TASK [config sysv start script] *****************************************************************************
skipping: [host_132]

TASK [start mysql(service)] *********************************************************************************
skipping: [host_132]

TASK [config mysql.service start up on boot] ****************************************************************
skipping: [host_132]

TASK [transfer sleep script to /tmp/] ***********************************************************************
changed: [host_132]

TASK [sleep 15 seconds] *************************************************************************************
changed: [host_132]

TASK [remove /tmp/sleep_15.sh] ******************************************************************************
changed: [host_132]

TASK [transfer sql statement to remonte] ********************************************************************
changed: [host_132]

TASK [make mysql secure] ************************************************************************************
changed: [host_132]

TASK [remove temp file /tmp/make_mysql_secure.sql] **********************************************************
changed: [host_132]

PLAY RECAP **************************************************************************************************
host_132                   : ok=25   changed=17   unreachable=0    failed=0

      MySQL

      mysql -uroot -pmtls0352 

      mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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>

  3. mysqltools

    mysql3

    cd 
vim
ansible-playbook

    1cd mysql 2vimplaybook 3playbook

    1):

    **2):hosts: **

  4. mysqltools

    1mysqltoolsMySQL20+

    **2mysqltools/etc/my.cnfcpu & **

    host_132/etc/my.cnf mysqltools

    [mysql]
auto-rehash


[mysqld]
####: for global
user                                =mysql                          #   mysql
basedir                             =/usr/local/mysql/              #   /usr/local/mysql/
datadir                             =/database/mysql/data/3306      #   /usr/local/mysql/data
server_id                           =653                            #   0
port                                =3306                           #   3306
character_set_server                =utf8                           #   latin1
explicit_defaults_for_timestamp     =off                            #    off
log_timestamps                      =system                         #   utc
socket                              =/tmp/mysql.sock                #   /tmp/mysql.sock
read_only                           =0                              #   off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #   1
auto_increment_offset               =1                              #   1
lower_case_table_names              =1                              #   0
secure_file_priv                    =                               #   null
open_files_limit                    =65536                          #   1024
max_connections                     =256                            #   151
thread_cache_size                   =128                              #   9
table_open_cache                    =4096                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16

####: for binlog
binlog_format                       =row                          #     row
log_bin                             =mysql-bin                      #   off
binlog_rows_query_log_events        =on                             #   off
log_slave_updates                   =on                             #   off
expire_logs_days                    =7                              #   0
binlog_cache_size                   =65536                          #   65536(64k)
binlog_checksum                     =none                           #   CRC32
sync_binlog                         =1                              #   1
slave-preserve-commit-order         =ON                             #   

####: for error-log
log_error                           =err.log                        #   /usr/local/mysql/data/localhost.localdomain.err

general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =on                             #    off
long_query_time                     =2.000000                       #    10.000000

####: for gtid
gtid_executed_compression_period    =1000                          #    1000
gtid_mode                           =on                            #    off
enforce_gtid_consistency            =on                            #    off


####: for replication
skip_slave_start                    =0                              #   
master_info_repository              =table                         #    file
relay_log_info_repository           =table                         #    file
slave_parallel_type                 =logical_clock                 #    database | LOGICAL_CLOCK
slave_parallel_workers              =4                             #    0
rpl_semi_sync_master_enabled        =1                             #    0
rpl_semi_sync_slave_enabled         =1                             #    0
rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
plugin_load_add                     =semisync_master.so            #
plugin_load_add                     =semisync_slave.so             #
binlog_group_commit_sync_delay      =500                          #    500(0.05%)0
binlog_group_commit_sync_no_delay_count = 13                        #    0


####: for innodb
default_storage_engine                          =innodb                     #   innodb
default_tmp_storage_engine                      =innodb                     #   innodb
innodb_data_file_path                           =ibdata1:64M:autoextend     #   ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_pool
innodb_log_group_home_dir                       =./                         #   ./
innodb_log_files_in_group                       =8                          #   2
innodb_log_file_size                            =128M                        #  50331648(48M)
innodb_file_per_table                           =on                         #   on
innodb_online_alter_log_max_size                =128M                  #   134217728(128M)
innodb_open_files                               =65535                       #   2000
innodb_page_size                                =16k                        #   16384(16k)
innodb_thread_concurrency                       =0                          #   0
innodb_read_io_threads                          =4                          #   4
innodb_write_io_threads                         =4                          #   4
innodb_purge_threads                            =4                          #   4
innodb_print_all_deadlocks                      =on                         #   off
innodb_deadlock_detect                          =on                         #   on
innodb_lock_wait_timeout                        =50                         #   50
innodb_spin_wait_delay                          =6                          #   6
innodb_autoinc_lock_mode                        =2                          #   1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #   on
innodb_stats_persistent_sample_pages            =20                         #   20
innodb_buffer_pool_instances                    =1
innodb_adaptive_hash_index                      =on                         #   on
innodb_change_buffering                         =all                        #   all
innodb_change_buffer_max_size                   =25                         #   25
innodb_flush_neighbors                          =1                          #   1
#innodb_flush_method                             =                           #  
innodb_doublewrite                              =on                         #   on
innodb_log_buffer_size                          =128M                        #  16777216(16M)
innodb_flush_log_at_timeout                     =1                          #   1
innodb_flush_log_at_trx_commit                  =1                          #   1
innodb_buffer_pool_size                         =1152M                  #       134217728(128M)
autocommit                                      =1                          #   1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON




####  for performance_schema
performance_schema                                                      =on    #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'

    mysqltools/etc/my.cnf(2core 2Gvm)

//

mysql

** 1MySQL 2 3 4(mysql-group-replication)** mysqltoolsmysqltoolsmysqltools

  1. mysql

    1):mysql

    cd /usr/local/mysqltools/deploy/ansible

cd mysql  #mysql

    2):install_single_mysql.yaml

    host_132mysqlinstall_single_mysql.yamlhostshost_132

    ---
 - hosts: host_132

    yaml:

    3):

    ansible-playbook install_single_mysql.yaml

    ... ...

    4):mysql

    mysql -uroot -pmtls0352

    mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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>

    rootmysqltools/config.yamlmysql_root_password

  2. mysql

    10.186.19.1510.186.19.1610.186.19.1710.186.19.15

    1):/etc/ansible/hosts

    /etc/ansible/hosts

    [repl]
replmaster15 ansible_host=10.186.19.15
replslave16 ansible_host=10.186.19.16
replslave17 ansible_host=10.186.19.17

    2):mysql

    cd /usr/local/mysqltools/deploy/ansible

cd mysql  #mysql

    3):install_master_slaves.yaml

    replmysqlinstall_master_slaves.yamlhostsrepl

    ---
 - hosts: repl

    vars/master_slaves.yaml mysqltoolsipipslave

    #
master_ip: 10.186.19.15
slave_ips:
  - 10.186.19.16
  - 10.186.19.17

    4):

    ansible-playbook install_master_slaves.yaml 

    PLAY [repl] *****************************************************************************************

TASK [Gathering Facts] ******************************************************************************
ok: [replmaster15]
ok: [replslave16]
ok: [replslave17]

... ... ... ... ... ...  ... ... ... ... ... ... 

PLAY RECAP ******************************************************************************************
replmaster15                : ok=28   changed=18   unreachable=0    failed=0   
replslave16                 : ok=28   changed=19   unreachable=0    failed=0   
replslave17                 : ok=28   changed=19   unreachable=0    failed=0

    5):

    mysql -uroot -pmtls0352
show slave status \G

    *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.186.19.15
                  Master_User: rple
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 595
               Relay_Log_File: actionsky16-relay-bin.000002
                Relay_Log_Pos: 800
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ... ... ... ... ... ... ... ... 
           Retrieved_Gtid_Set: 8b5ac555-37ec-11e8-b50e-5a3fdb1cf647:1-2
            Executed_Gtid_Set: 8b5ac555-37ec-11e8-b50e-5a3fdb1cf647:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

    Yes

  3. mysql

    10.186.19.1510.186.19.1610.186.19.171151617

    1):/etc/ansible/hosts

    /etc/ansible/hosts

    [repl]
replmaster15 ansible_host=10.186.19.15
replslave16 ansible_host=10.186.19.16
replslave17 ansible_host=10.186.19.17

    2):mysql

    cd /usr/local/mysqltools/deploy/ansible

cd mysql  #mysql

    3):install_multi_source_replication.yaml

    replmysqlinstall_multi_source_replication.yamlhostsrepl

    ---
 - hosts: repl

    vars/multi_source_replication.yaml mysqltoolsipipslave

    #master_ips masterip
master_ips:
 - '10.186.19.15'
 - '10.186.19.16'

#slaveip
slave_ip: '10.186.19.17'

    4):

    ansible-playbook install_multi_source_replication.yaml

    5):

    mysql -uroot -pmtls0352
show slave status \G

    *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.186.19.15
                  Master_User: rple_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 150
               Relay_Log_File: actionsky17-relay-bin-master1.000002
                Relay_Log_Pos: 355
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.186.19.16
                  Master_User: rple_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 150
               Relay_Log_File: actionsky17-relay-bin-master2.000002
                Relay_Log_Pos: 355
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

    Yes

  4. mysql

    10.186.19.1510.186.19.1610.186.19.17group replication

    1):/etc/ansible/hosts

    /etc/ansible/hosts

    [repl]
mgr15 ansible_host=10.186.19.15
mgr16 ansible_host=10.186.19.16
mgr17 ansible_host=10.186.19.17

    2):mysql

    cd /usr/local/mysqltools/deploy/ansible

cd mysql  #mysql

    3):install_group_replication.yaml

    replmysqlinstall_group_replication.yamlhostsrepl

    ---
 - hosts: repl

    var/group_replication.yaml.yaml mysqltoolsgroup replicationbinlogrowgroup replication mysqltoolsgroup replication

    mtls_with_mysql_group_replication: 1
mysql_binlog_format: row
mysql_mgr_port: 13306
mysql_mgr_hosts: 
    - '10.186.19.15'
    - '10.186.19.16'
    - '10.186.19.17'

    4):

    ansible-playbook install_group_replication.yaml 

    PLAY [repl] *****************************************************************************************

TASK [Gathering Facts] ******************************************************************************
ok: [mgr15]
ok: [mgr16]
ok: [mgr17]
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 
PLAY RECAP ******************************************************************************************
mgr15                : ok=28   changed=19   unreachable=0    failed=0   
mgr16                : ok=28   changed=18   unreachable=0    failed=0   
mgr17                : ok=28   changed=18   unreachable=0    failed=0

    5):group replication

    mysql -uroot -pmtls0352
select * from performance_schema.replication_group_members ;

    +---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 08de362c-3802-11e8-9e65-5a3fdb1cf647 | actionsky15 |        3306 | ONLINE       |
| group_replication_applier | ef7f3b61-3801-11e8-886d-9a17854b700d | actionsky17 |        3306 | ONLINE       |
| group_replication_applier | f1649143-3801-11e8-8fd3-5a1f0f06c50d | actionsky16 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

    onlinegroup replication

    mtls_with_mysql_group_replication: 1mysqltools/etc/my.cnfgroup replication

    ####: for mysql group replication 
loose-group_replication_recovery_retry_count          =10                                         #   10
loose-group_replication_recovery_reconnect_interval   =60                                         #   60
loose-group_replication_allow_local_disjoint_gtids_join=off                                       #   off
loose-group_replication_allow_local_lower_version_join=off                                        #   off
loose-group_replication_ip_whitelist                  =AUTOMATIC                                  #   AUTOMATIC
loose-transaction_write_set_extraction                =XXHASH64                                   # off
loose-group_replication_group_name                    ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"     #
loose-group_replication_start_on_boot                 =off                                        # off
loose-group_replication_bootstrap_group               =off                                        # off
loose-group_replication_single_primary_mode           =on                                         #   on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_gtid_assignment_block_size    =1000000                                    #   1000000
loose-group_replication_poll_spin_loops               =0                                          #   0
loose-group_replication_compression_threshold         =1024                                       #   1000000
loose-group_replication_flow_control_mode             =QUOTA                                      #   QUOTA
loose-group_replication_local_address                 ="10.186.19.15:13306"
loose-group_replication_group_seeds                   ="10.186.19.15:13306,10.186.19.16:13306,10.186.19.17:13306"

    mysqltoolsgroup replication mysqltoolsip

  5. mysql

    mysqltools

    1mysql_upgrademysqltoolsTB``mysql_upgrade

    **1):upgrad_single_mysql.yamlhosts **

    ---
 - hosts: sqlstudio

    2):

    ansible-playbook upgrad_single_mysql.yaml

    PLAY [sqlstudio] **************************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************
ok: [sqlstudio]

TASK [stop mysql service] *****************************************************************************************************
ok: [sqlstudio]

TASK [backup link file] *******************************************************************************************************
changed: [sqlstudio]

TASK [unarchive new package to /usr/local/] ***********************************************************************************
changed: [sqlstudio]

TASK [change owner and group] *************************************************************************************************
changed: [sqlstudio]

TASK [make new link file] *****************************************************************************************************
changed: [sqlstudio]

TASK [start mysql service] ****************************************************************************************************
changed: [sqlstudio]

PLAY RECAP ********************************************************************************************************************
sqlstudio                  : ok=7    changed=5    unreachable=0    failed=0

    3):

    1/usr/local/

    drwxr-xr-x   9 mysql mysql 129 6  18 14:46 mysql-5.7.21-linux-glibc2.12-x86_64
lrwxrwxrwx   1 root  root   35 6  18 14:53 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64

    lrwxrwxrwx   1 mysql mysql  46 6  18 15:30 mysql -> /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x   9 mysql mysql 129 6  18 14:46 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x   9 mysql mysql 129 6  18 15:30 mysql-5.7.22-linux-glibc2.12-x86_64
lrwxrwxrwx   1 root  root   35 6  18 14:53 mysql.backup.20180618 -> mysql-5.7.21-linux-glibc2.12-x86_64

    2

    mysql -uroot -pxxxxxx

    mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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>select @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)

yamlmycatmysqltoolsmycat

  1. mycat

    10.186.19.1510.186.19.1610.186.19.1715161710.186.19.14mycat(mycat)

    1):mycat

    mysqltools/config.yamlmysql_app_usermysql_app_password

    create user appuser@'%' identified by 'mtls0352';
create database appdb char set utf8;
grant all on appdb.* to appuser@'%';

    2):/etc/ansible/hosts /etc/ansible/hosts

    mycat ansible_host=10.186.19.14

    3):var/var_mycat.yaml

    mysqltools(master_ip)(slave_ips)schema(schemas)

    master_ip: "10.186.19.15"

slave_ips:
 - "10.186.19.16"
 - "10.186.19.17"

schemas:
 - "appdb"

    master_ipvipslave_ipsip

    4):install_mycat.yamlhosts

    ---
 - hosts: mycat

    5):mycat

    ansible-playbook install_mycat.yaml

    PLAY [mycat] ****************************************************************************************

TASK [Gathering Facts] ******************************************************************************
ok: [mycat]

TASK [install java-1.7.0-openjdk] *******************************************************************
changed: [mycat]

TASK [create mycat user] ****************************************************************************
changed: [mycat]

TASK [trasfer mycat-server-1.6.5-linux.tar.gz to remonte host] **************************************
changed: [mycat]

TASK [export MYCAT_HOME env to /etc/profile] ********************************************************
changed: [mycat]

TASK [config schema.xml] ****************************************************************************
changed: [mycat]

TASK [config server.xml] ****************************************************************************
changed: [mycat]

TASK [transfer start_mycat.sh to remonte /tmp/] *****************************************************
changed: [mycat]

TASK [start mycat] **********************************************************************************
changed: [mycat]

TASK [remove start_mycat.sh] ************************************************************************
changed: [mycat]

PLAY RECAP ******************************************************************************************
mycat                      : ok=10   changed=9    unreachable=0    failed=0

    6):mycat

    ps -ef | grep mycat

    root     24415     1  0 09:21 ?        00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root     24417 24415 12 09:21 ?        00:00:06 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/zookeeper-3.4.6.jar:lib/jline-0.9.94.jar:lib/ehcache-core-2.6.11.jar:lib/log4j-1.2.17.jar:lib/fastjson-1.2.12.jar:lib/curator-client-2.11.0.jar:lib/joda-time-2.9.3.jar:lib/log4j-slf4j-impl-2.5.jar:lib/libwrapper-linux-x86-32.so:lib/netty-3.7.0.Final.jar:lib/druid-1.0.26.jar:lib/log4j-api-2.5.jar:lib/mapdb-1.0.7.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/hamcrest-core-1.3.jar:lib/objenesis-1.2.jar:lib/leveldb-api-0.7.jar:lib/hamcrest-library-1.3.jar:lib/wrapper.jar:lib/commons-lang-2.6.jar:lib/reflectasm-1.03.jar:lib/mongo-java-driver-2.11.4.jar:lib/guava-19.0.jar:lib/curator-recipes-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/libwrapper-linux-ppc-64.so:lib/log4j-core-2.5.jar:lib/mysql-binlog-connector-java-0.6.0.jar:lib/netty-common-4.1.9.Final.jar:lib/leveldb-0.7.jar:lib/sequoiadb-driver-1.12.jar:lib/kryo-2.10.jar:lib/jsr305-2.0.3.jar:lib/commons-collections-3.2.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/disruptor-3.3.4.jar:lib/log4j-1.2-api-2.5.jar:lib/velocity-1.7.jar:lib/Mycat-server-1.6.5-release.jar:lib/libwrapper-linux-x86-64.so:lib/dom4j-1.6.1.jar:lib/minlog-1.2.jar:lib/asm-4.0.jar:lib/netty-buffer-4.1.9.Final.jar -Dwrapper.key=sexYToWnzGO4Glh1 -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=24415 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start

    7):mycat

    mysql -uappuser -pmtls0352 -h10.186.19.14 -P8066

    mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.200-mycat-1.6.5-release-20171117203123 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 |
+----------+
| appdb    |
+----------+
2 rows in set (0.00 sec)

create table person( id int auto_increment primary key,name varchar(16));
Query OK, 0 rows affected (0.02 sec)

insert into person(name) values('welson');
Query OK, 1 row affected (0.05 sec)

select * from person;
+----+--------+
| id | name   |
+----+--------+
|  1 | welson |
+----+--------+

    mycat .

mysqlmhainnodb clustermysqltools

  1. mha

    MHAmysqlmanagernodemanagermastermasternode https://www.cnblogs.com/gomysql/p/3675429.html

    mysqltoolsmhamhacentos-7.4

    1. 130131132mysql130master

      ip mysql mha vip
      mhamaster 192.168.29.130 centos-7.4 master node 192.168.29.100
      mhaslave1 192.168.29.131 centos-7.4 slave node
      mhaslave2 192.168.29.132 centos-7.4 slave manager

    2. mha

      1mysqlmysql

      2ssh(mhascpmasterbinlogssh)

      3gccmysqltoolsgcc gcc-c++ yum

    3. ansiblehosts

      mha/etc/ansible/hosts

      [mhacluster]
mhamaster ansible_host=192.168.29.130 ansible_user=root
mhaslave1 ansible_host=192.168.29.131 ansible_user=root
mhaslave2 ansible_host=192.168.29.132 ansible_user=root

    4. mysqltoolsmha

      mysqltools/config.yamlmha

      mtls_mha_node: mhanode.tar.gz
mtls_mha_manager: mhamanager.tar.gz

    5. mha

      mhamysqltools/deploy/ansible/mha/vars/var_mha.yaml

      master_ip: "192.168.29.130"
slave_ips:
 - "192.168.29.131"
 - "192.168.29.132"

manager_ip: "192.168.29.132"

net_work_interface: "ens33"
vip: "192.168.29.100"

os_release: '7.4'

      1master_ip mysql masterip

      2slave_ips mysql slaveip

      3manager_ip mha manageripmysqltoolsslaveipmangermysqltoolsslavemaster

      4net_work_inferface vip

      5vip vip

      6os_release "7.4"rhel

    6. mha

      **mysqltools/deploy/ansible/mha/install_mha.yamlhostansiblehosts: **

      ---
 - hosts: mhacluster

      mhaclusterinstall_mha.yaml

    7. mha

      cd mysqltools/deploy/ansible/mha
ansible-playbook install_mha.yaml

      PLAY [mhacluster] ********************************************************************
TASK [Gathering Facts] ***************************************************************
ok: [slave2]
ok: [slave1]
ok: [master]
TASK [install gcc] ********************************************************************
changed: [slave1]
changed: [slave2]
changed: [master]
TASK [install gcc-c++] ****************************************************************
changed: [slave2]
changed: [slave1]
changed: [master]
TASK [transfer mhanode.tar.gz to remote host and unarchive to /tmp/] ******************
changed: [master]
changed: [slave1]
changed: [slave2]
TASK [install mha node] ***************************************************************
changed: [master]
changed: [slave2]
changed: [slave1]
TASK [export path env to /root/.bashrc] ***********************************************
changed: [master]
changed: [slave2]
changed: [slave1]
TASK [stransfer create_mha_user.sql to master] ****************************************
skipping: [slave1]
skipping: [slave2]
changed: [master]
TASK [create mha user in mysql(master)] ***********************************************
skipping: [slave1]
skipping: [slave2]
changed: [master]
TASK [copy bind_vip.sh to /tmp/] ******************************************************
skipping: [slave1]
skipping: [slave2]
changed: [master]
TASK [bind vip] ***********************************************************************
skipping: [slave1]
skipping: [slave2]
changed: [master]
TASK [transfer mhamanager.tar.gz to remote host and unarchive to /tmp/] ***************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [install mha manager] ************************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [create directory(/etc/masterha/)] ***********************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [create directory(/var/log/masterha)] *********************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [config mha app.cnf] ***************************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [config master_ip_failover] ********************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [config master_ip_online_change] ***************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [copy start_mha.sh to /usr/local/] **************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
TASK [start mha manager] *****************************************************************
skipping: [master]
skipping: [slave1]
changed: [slave2]
PLAY RECAP ********************************************************************************
master                     : ok=10   changed=9    unreachable=0    failed=0   
slave1                     : ok=6    changed=5    unreachable=0    failed=0   
slave2                     : ok=15   changed=14   unreachable=0    failed=0

    8. **mysqltoolsmha/tmp/mhanode /tmp/mhamanager **

      1mastervip

      ifconfig

      ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.29.130  netmask 255.255.255.0  broadcast 192.168.29.255
        inet6 fe80::413c:fcac:858:64bc  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:e3:07:d3  txqueuelen 1000  (Ethernet)
        RX packets 10816  bytes 13581709 (12.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2535  bytes 310584 (303.3 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.192.100  netmask 255.255.0.0  broadcast 172.16.255.255
        ether 00:0c:29:e3:07:d3  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1  (Local Loopback)
        RX packets 203  bytes 29718 (29.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 203  bytes 29718 (29.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

      ens33:0 192.168.29.100 vipvip

      2mha-manager

      ps -ef | grep man

      root       3549      1  1 11:38 ?        00:00:00 perl /usr/local/bin//masterha_manager --conf=/etc/masterha/app.cnf --ignore_last_failover

      masterha_manager manager(/var/log/masterha/manager.log)

      mha/tmp/mhamanagercheck

      3ssh

      cd /tmp/mhamanager/
./check_ssh.sh 

      Thu May 17 11:33:49 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 11:33:49 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu May 17 11:33:49 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu May 17 11:33:49 2018 - [info] Starting SSH connection tests..
Thu May 17 11:33:50 2018 - [debug] 
Thu May 17 11:33:49 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.130:22) to [email protected](192.168.29.131:22)      ..
Thu May 17 11:33:49 2018 - [debug]   ok.
Thu May 17 11:33:49 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.130:22) to [email protected](192.168.29.132:22)      ..
Thu May 17 11:33:50 2018 - [debug]   ok.
Thu May 17 11:33:50 2018 - [debug] 
Thu May 17 11:33:49 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.131:22) to [email protected](192.168.29.130:22)      ..
Thu May 17 11:33:50 2018 - [debug]   ok.
Thu May 17 11:33:50 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.131:22) to [email protected](192.168.29.132:22)      ..
Thu May 17 11:33:50 2018 - [debug]   ok.
Thu May 17 11:33:51 2018 - [debug] 
Thu May 17 11:33:50 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.132:22) to [email protected](192.168.29.130:22)      ..
Thu May 17 11:33:50 2018 - [debug]   ok.
Thu May 17 11:33:50 2018 - [debug]  Connecting via SSH from [email protected](192.168.29.132:22) to [email protected](192.168.29.131:22)      ..
Thu May 17 11:33:51 2018 - [debug]   ok.
Thu May 17 11:33:51 2018 - [info] All SSH connection tests passed successfully.

      All SSH connection tests passed successfully.ssh

      **4mysql **

      cd /tmp/mhamanager/
./check_repl.sh 

      Thu May 17 11:33:24 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 11:33:24 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu May 17 11:33:24 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu May 17 11:33:24 2018 - [info] MHA::MasterMonitor version 0.57.
Thu May 17 11:33:25 2018 - [info] GTID failover mode = 1
Thu May 17 11:33:25 2018 - [info] Dead Servers:
Thu May 17 11:33:25 2018 - [info] Alive Servers:
Thu May 17 11:33:25 2018 - [info]   192.168.29.130(192.168.29.130:3306)
Thu May 17 11:33:25 2018 - [info]   192.168.29.131(192.168.29.131:3306)
Thu May 17 11:33:25 2018 - [info]   192.168.29.132(192.168.29.132:3306)
Thu May 17 11:33:25 2018 - [info] Alive Slaves:
Thu May 17 11:33:25 2018 - [info]   192.168.29.131(192.168.29.131:3306)  Version=5.7.22-log (oldest major version between slaves)       log-bin:enabled
Thu May 17 11:33:25 2018 - [info]     GTID ON
Thu May 17 11:33:25 2018 - [info]     Replicating from 192.168.29.130(192.168.29.130:3306)
Thu May 17 11:33:25 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 17 11:33:25 2018 - [info]   192.168.29.132(192.168.29.132:3306)  Version=5.7.22-log (oldest major version between slaves)       log-bin:enabled
Thu May 17 11:33:25 2018 - [info]     GTID ON
Thu May 17 11:33:25 2018 - [info]     Replicating from 192.168.29.130(192.168.29.130:3306)
Thu May 17 11:33:25 2018 - [info] Current Alive Master: 192.168.29.130(192.168.29.130:3306)
Thu May 17 11:33:25 2018 - [info] Checking slave configurations..
Thu May 17 11:33:25 2018 - [info]  read_only=1 is not set on slave 192.168.29.131(192.168.29.131:3306).
Thu May 17 11:33:25 2018 - [info]  read_only=1 is not set on slave 192.168.29.132(192.168.29.132:3306).
Thu May 17 11:33:25 2018 - [info] Checking replication filtering settings..
Thu May 17 11:33:25 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
Thu May 17 11:33:25 2018 - [info]  Replication filtering check ok.
Thu May 17 11:33:25 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu May 17 11:33:25 2018 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 17 11:33:26 2018 - [info] HealthCheck: SSH to 192.168.29.130 is reachable.
Thu May 17 11:33:26 2018 - [info] 
192.168.29.130(192.168.29.130:3306) (current master)
 +--192.168.29.131(192.168.29.131:3306)
 +--192.168.29.132(192.168.29.132:3306)

Thu May 17 11:33:26 2018 - [info] Checking replication health on 192.168.29.131..
Thu May 17 11:33:26 2018 - [info]  ok.
Thu May 17 11:33:26 2018 - [info] Checking replication health on 192.168.29.132..
Thu May 17 11:33:26 2018 - [info]  ok.
Thu May 17 11:33:26 2018 - [info] Checking master_ip_failover_script status:
Thu May 17 11:33:26 2018 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.29.130       --orig_master_ip=192.168.29.130 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig ens33:0 down==/sbin/ifconfig ens33:0 172.16.192.100===

Checking the Status of the script.. OK 
Thu May 17 11:33:26 2018 - [info]  OK.
Thu May 17 11:33:26 2018 - [warning] shutdown_script is not defined.
Thu May 17 11:33:26 2018 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

      MySQL Replication Health is OK.mysql

mysqldup,xtrabackup,mysqlbackupdbaxtrabackup,mysqlbackupmysqltoolspythondbamysqltoolsdba

mysqltools-pythonpipmysqltoolspython

mysqltools-python

1):Pypi: https://pypi.org/project/mysqltools-python/

2):Github: https://github.com/Neeky/mysqltools-python

mysqltools-pythonpythonbin

ll /usr/local/python/bin/ | grep mtls                                        
-rwxr-xr-x. 1 root root    11770 9  17 16:47 mtlsbackup     # mtlsbackup                            
-rwxr-xr-x. 1 root root    11211 9  17 16:47 mtlsmonitor    # mtlsbackup

mysqltoolspython-3.x.xmysqltools-python

pip3 install mysqltools-python

  1. mtlsbackup

    **1): mtlsbackup **

    python 1):mysqldump,xtrabackup,mysqlbackupmtlsbackup2):(dbadbadba33()mtlsbackup)

    **2): /etc/mtlsbackup.cnf **

    mtlsbackup.py

    [global]
backup_tool=xtrabackup                             #xtrabackup,mysqldump,meb (xtrabackup)
user=backup                                        #(mysql)
password=DX3906                                    #
host=127.0.0.1                                     #
port=3306                                          #
full_backup_days=6                                 #    6 -->  
diff_backup_days=0,1,2,3,4,5                       #  0 -->  1 --> 2 ... ...
backup_data_dir=/database/backups/3306/data/       #
backup_log_dir=/database/backups/3306/log/         #xtrackupcheck_point
backup_temp_dir=/database/backups/3306/temp/       #xtrabackup

[xtrabackup]
# xtrabackup 
full_backup_script=/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/my.cnf --host={self.host} --port={self.port} --user={self.user} --password={self.password} --no-version-check --compress --compress-threads=4 --use-memory=200M --stream=xbstream  --parallel=8 --backup  --extra-lsndir={self.lsndir} --target-dir={self.backup_temp_dir} > {self.full_backup_file} 2>{self.full_backup_log_file} &
diff_backup_script=/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/my.cnf --host={self.host} --port={self.port} --user={self.user} --password={self.password} --no-version-check --compress --compress-threads=4 --use-memory=200M --stream=xbstream  --parallel=8 --backup  --extra-lsndir={self.lsndir} --target-dir={self.backup_temp_dir} --incremental --incremental-lsn={self.tolsn} > {self.diff_backup_file}  2>{self.diff_backup_log_file} &

    mysqltools

    3): /database/backups/

    tree /database/backups/

    /database/backups/
 3306
  data
   2018-07-28T13:38:01
       2018-07-28T13:38:01-full.log
       2018-07-28T13:38:01-full.xbstream
       2018-07-28T13:40:05-diff.log
       2018-07-28T13:40:05-diff.xbstream
       2018-07-28T13:42:04-diff.log
       2018-07-28T13:42:04-diff.xbstream
  log
   2018-07-28T13:38:01
    xtrabackup_checkpoints
    xtrabackup_info
   2018-07-28T13:40:05
    xtrabackup_checkpoints
    xtrabackup_info
   2018-07-28T13:42:04
    xtrabackup_checkpoints
    xtrabackup_info

  2. 1): mtlsbackuppythonpython3python3python

    2): MySQL

    mysqltools/deploy/ansible/backup/template/mtlsbackup.cnf``full_backup_days``diff_backup_days

    full_backup_days=6                                 #    6--> 5--> 4-->... ...
diff_backup_days=0,1,2,3,4,5                      #   6--> 4--> 4-->... ...

    3): crontab

    mysqltools/deploy/ansible/backup/vars/mtlsbackup.yaml``backup_minutelinux crontab minute,backup_hourlinux crontab hour,backup_userlinux crontab user

    ---
backup_minute: "0" 
backup_hour: "2"
backup_user: "mysql"

#backup_minute linux crontab minute
#backup_hour   linux crontab hour
#backup_user   linux crontab user

# 02:00:00

  3. 1): mysqltools/deploy/ansible/backup

    config_backup.yamlhosts,sqlstudioconfig_backup.yaml

    ---
 - hosts: sqlstudio

    2):

    ansible-playbook config_backup.yaml

    PLAY [sqlstudio] **************************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************
ok: [sqlstudio]

TASK [transfer qperss to remonte host(rhel-7.x)] ******************************************************************************
changed: [sqlstudio]

TASK [install qpress(rhel-7.x)] ***********************************************************************************************
changed: [sqlstudio]

TASK [remove qpress install package(rhel-7.x)] ********************************************************************************
changed: [sqlstudio]

TASK [transfer extrabackup install package to remonte host] *******************************************************************
ok: [sqlstudio]

TASK [make link file fore percona-xtrabackup-2.4.9-Linux-x86_64] **************************************************************
ok: [sqlstudio]

TASK [export path env variable] ***********************************************************************************************
ok: [sqlstudio]

TASK [export path env to /root/.bashrc] ***************************************************************************************
ok: [sqlstudio]

TASK [transfer create_backup_user.sql file to remote host] ********************************************************************
skipping: [sqlstudio]

TASK [execute create_backup_user.sql] *****************************************************************************************
skipping: [sqlstudio]

TASK [remove /tmp/create_backup_user.sql] *************************************************************************************
skipping: [sqlstudio]

TASK [config /etc/mtlsbackup.cnf] *********************************************************************************************
ok: [sqlstudio]

TASK [config crontab] *********************************************************************************************************
ok: [sqlstudio]

PLAY RECAP ********************************************************************************************************************
sqlstudio                  : ok=10   changed=3    unreachable=0    failed=0

  4. 1): crond

    sudo -umysql crontab -l 

    #Ansible: mtlsbackup
0 2 * * * /usr/local/python/bin/python3 /usr/local/mysqltoolsclient/mtlsbackup.py 2>>/database/backups/mtlsbackup.log

    crontab

    2): mysql*

    crontab

    su mysql
/usr/local/python/bin/mtlsbackup 

    [2018-09-21 14:38:46,682] [mtlsbackup] [INFO] read config file /etc/mtlsbackup.cnf
[2018-09-21 14:38:46,683] [mtlsbackup] [INFO]  /database/backups/3306/data/ 
[2018-09-21 14:38:46,684] [mtlsbackup] [INFO]  /database/backups/3306/log/ 
[2018-09-21 14:38:46,684] [mtlsbackup] [INFO]  /database/backups/3306/temp/ 
[2018-09-21 14:38:46,684] [mtlsbackup] [INFO]  4 
[2018-09-21 14:38:46,684] [mtlsbackup] [INFO] 
[2018-09-21 14:38:46,684] [mtlsbackup] [INFO] ...
[2018-09-21 14:38:46,685] [mtlsbackup] [INFO] 2018-09-12T02:16:49
[2018-09-21 14:38:46,685] [mtlsbackup] [INFO] /database/backups/3306/data/2018-09-12T02:16:49/2018-09-12T02:16:49-full.log
[2018-09-21 14:38:46,686] [mtlsbackup] [WARNING]  
[2018-09-21 14:38:46,686] [mtlsbackup] [INFO] xtrabackup_checkpointstolsn=2589231
[2018-09-21 14:38:46,687] [mtlsbackup] [INFO] MySQL /usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --port=3306 --user=backup --password=DX3906 --no-version-check --compress --compress-threads=4 --use-memory=200M --stream=xbstream  --parallel=8 --backup  --extra-lsndir=/database/backups/3306/log/2018-09-21T14:38:46 --target-dir=/database/backups/3306/temp/ --incremental --incremental-lsn=2589231 > /database/backups/3306/data/2018-09-12T02:16:49/2018-09-21T14:38:46-diff.xbstream  2>/database/backups/3306/data/2018-09-12T02:16:49/2018-09-21T14:38:46-diff.log &

    mtlsbackupxtrabckupmtlsbackup.pyxtrabackup/database/backups/3306/data/2018-09-12T02:16:49/2018-09-21T14:38:46-diff.log

    tail -2 /database/backups/3306/data/2018-09-12T02:16:49/2018-09-21T14:38:46-diff.log

    xtrabackup: Transaction log of lsn (2663437) to (2663446) was copied.
180921 14:38:48 completed OK!

    **3): **

    tree /database/backups/


    /database/backups/
 3306
  data
   2018-09-12T02:16:49
       2018-09-12T02:16:49-full.log
       2018-09-12T02:16:49-full.xbstream
       2018-09-21T02:12:33-diff.log
       2018-09-21T02:12:33-diff.xbstream
       2018-09-21T13:19:23-diff.log
       2018-09-21T13:19:23-diff.xbstream
       2018-09-21T14:38:32-diff.log
       2018-09-21T14:38:32-diff.xbstream
       2018-09-21T14:38:46-diff.log
       2018-09-21T14:38:46-diff.xbstream
  log
   2018-09-12T02:16:49
    xtrabackup_checkpoints
    xtrabackup_info
   2018-09-21T02:12:33
    xtrabackup_checkpoints
    xtrabackup_info
   2018-09-21T13:19:23
    xtrabackup_checkpoints
    xtrabackup_info
   2018-09-21T14:38:32
    xtrabackup_checkpoints
    xtrabackup_info
   2018-09-21T14:38:46
       xtrabackup_checkpoints
       xtrabackup_info
  temp
 mtlsbackup.log

  5. 1): mtlsbakupxtrabackup,meb,mysqldump

    2): mysqltools-2.18.07.28mysqlbackupbackupSQLmysqltools/deploy/ansible/backup/template/create_backup_user.sqlbackupmysqltoolsconfig_backup.yamlcreate_user1

mysqltools;mysqltoolszabbixzabbixMySQL mysqltools-python

  1. python200+ 1): variable MySQL 2): status 3):ps MySQL mysql group replication 4):

    ** ** **
    mysql(variable) mysql(variable)
    -- ServerID server_id variable
    -- BaseDir basedir variable
    -- DataDir datadir variable
    -- Port port variable
    -- CharacterSetServer character_set_server variable
    -- Socket socket variable
    -- ReadOnly readonly variable
    -- SkipNameResolve skip_name_resolve variable
    -- LowerCaseTableNames lower_case_table_names variable
    -- ThreadCacheSize thread_cache_size variable
    -- TableOpenCache table_open_cache variable
    -- TableDefinitionCache table_definition_cache variable
    -- TableOpenCacheInstances table_open_cache_instance variable
    -- MaxConnections max_connections variable
    -- BinlogFormat binlog_format variable
    -- LogBin log_bin variable
    -- BinlogRowsQueryLogEvents binlog_rows_query_log_events variable
    -- LogSlaveUpdates log_slave_updates variable
    -- ExpireLogsDays expire_logs_days variable
    -- BinlogCacheSize binlog_cache_size variable
    -- SyncBinlog sync_binlog variable
    -- ErrorLog error_log variable
    -- GtidMode gtid_mode variable
    -- EnforceGtidConsistency enforce_gtid_consistency variable
    -- MasterInfoRepository master_info_repository variable
    -- RelayLogInfoRepository relay_log_info_repository variable
    -- SlaveParallelType slave_parallel_type variable
    -- SlaveParallelWorkers slave_parallel_workers variable
    -- InnodbDataFilePath innodb_data_file_path variable
    -- InnodbTempDataFilePath innodb_temp_data_file_path variable
    -- InnodbBufferPoolFilename innodb_buffer_pool_filename variable
    -- InnodbLogGroupHomeDir innodb_log_group_home_dir variable
    -- InnodbLogFilesInGroup innodb_log_file_in_group variable
    -- InnodbLogFileSize innodb_log_file_size variable
    -- InnodbFileformat innodb_fileformat variable
    -- InnodbFilePerTable innodb_file_per_table variable
    -- InnodbOnlineAlterLogMaxSize innodb_online_Alter_log_max_size variable
    -- InnodbOpenFiles innodb_open_files variable
    -- InnodbPageSize innodb_page_size variable
    -- InnodbThreadConcurrency innodb_thread_concurrency variable
    -- InnodbReadIoThreads innodb_read_io_threads variable
    -- InnodbWriteIoThreads innodb_write_io_threads variable
    -- InnodbPurgeThreads' innodb_purge_threads variable
    -- InnodbLockWaitTimeout innodb_lock_wait_timeout variable
    -- InnodbSpinWaitDelay innodb_spin_wait_delay variable
    -- InnodbAutoincLockMode innodb_autoinc_lock_mode variable
    -- InnodbStatsAutoRecalc innodb_stats_auto_recalc variable
    -- InnodbStatsPersistent innodb_stats_persistent variable
    -- InnodbStatsPersistentSamplePages innodb_stats_persistent_sample_pages variable
    -- InnodbBufferPoolInstances innodb_buffer_pool_instances variable
    -- InnodbAdaptiveHashIndex innodb_adaptive_hash_index variable
    -- InnodbChangeBuffering innodb_change_buffering variable
    -- InnodbChangeBufferMaxSize innodb_change_buffer_max_size variable
    -- InnodbFlushNeighbors innodb_flush_neighbors variable
    -- InnodbFlushMethod innodb_flush_method variable
    -- InnodbDoublewrite innodb_doublewrite variable
    -- InnodbLogBufferSize innodb_log_buffer_size variable
    -- InnodbFlushLogAtTimeout innodb_flushLog_at_timeout variable
    -- InnodbFlushLogAtTrxCommit innodb_flushLog_at_trx_commit variable
    -- InnodbBufferPoolSize innodb_buffer_pool_size variable
    -- Autocommit autocommit variable
    -- InnodbOldBlocksPct innodb_lld_blocks_pct variable
    -- InnodbOldBlocksTime innodb_old_blocks_time variable
    -- InnodbReadAheadThreshold innodb_read_ahead_threshold variable
    -- InnodbRandomReadAhead innodb_random_read_ahead variable
    -- InnodbBufferPoolDumpPct innodb_buffer_pool_dump_pct variable
    -- InnodbBufferPoolDumpAtShutdown innodb_buffer_pool_dump_at_shutdown variable
    *********************************
    mysql(status) statusmysql
    -- AbortedClients aborted_clients client status
    -- AbortedConnects borted_connects server status
    -- BinlogCacheDiskUse binlog_cache_disk_use status
    -- BinlogCacheUse binlog_cache_user binlog_cache status
    -- BinlogStmtCacheDiskUse binlog_stmt_cache_disk_use status
    -- BinlogStmtCacheUse binlog_stmt_cache_use binlog_cache status
    -- BytesReceived bytes_received status
    -- BytesSent bytes_sent status
    -- ComBegin com_begin status
    -- ComCallProcedure com_call_procedure status
    -- ComChangeMaster com_change_master status
    -- ComCommit com_commit status
    -- ComDelete com_delete status
    -- ComDeleteMulti com_delete_multi status
    -- ComInsert com_insert status
    -- ComInsertSelect com_insert_select status
    -- ComSelect com_select status
    -- ComUpdate com_update status
    -- ComUpdateMulti com_update_multi status
    -- Connections connections status
    -- CreatedTmpDiskTable created_tmp_disk_table status
    -- CreatedTmpFiles created_tmp_files status
    -- CreatedTmpTables created_tmp_tables status
    -- InnodbBufferPoolDumpStatus innodb_buffer_pool_dump_status innodb_xx_dump status
    -- InnodbBufferPoolLoadStatus innodb_buffer_pool_load_status innodb_xx_load status
    -- InnodbBufferPoolResizeStatus innodb_buffer_pool_resize_status status
    -- InnodbBufferPoolBytesData innodb_buffer_pool_bytes_data buffer_pool() status
    -- InnodbBufferPoolPagesData innodb_buffer_pool_pages_data buffer_pool status
    -- InnodbBufferPoolPagesDirty innodb_buffer_pool_pages_dirty buffer_pool status
    -- InnodbBufferPoolBytesDirty innodb_buffer_pool_bytes_dirty buffer_pool() status
    -- InnodbBufferPoolPagesFlushed innodb_buffer_pool_pages_flushed buffer_pool status
    -- InnodbBufferPoolPagesFree innodb_buffer_pool_pages_free buffer_pool status
    -- InnodbBufferPoolPagesMisc innodb_buffer_pool_pages_misc buffer_pool total_page -(free + data) status
    -- InnodbBufferPoolPagesTotal innodb_buffer_pool_pages_total buffer_pool status
    -- InnodbBufferPoolReadAhead innodb_buffer_pool_read_ahead read-ahead status
    -- InnodbBufferPoolReadAheadEvicted innodb_buffer_pool_read_ahead_evicted raed-ahead
    -- InnodbBufferPoolReadRequests innodb_buffer_pool_read_requests (buffer_pool) status
    -- InnodbBufferPoolReads innodb_buffer_pool_reads () status
    -- InnodbBufferPoolWaitFree innodb_buffer_pool_wait_free status
    -- InnodbBufferPoolWriteRequests innodb_buffer_pool_write_requests buffer_pool status
    -- InnodbDataFsyncs innodb_data_fsyncs fsyncs() status
    -- InnodbDataPendingFsyncs innodb_data_pending_fsyncs fsyncs status
    -- InnodbDataPendingReads innodb_data_pending_reads status
    -- InnodbDataPendingWrites innodb_data_pending_writes status
    -- InnodbDataRead innodb_data_read buffer_pool status
    -- InnodbDataReads innodb_data_reads buffer_pool status
    -- InnodbDataWrites innodb_data_writes buffer_pool status
    -- InnodbDataWritten innodb_data_written buffer_pool status
    -- InnodbDblwrPagesWritten innodb_dblwr_pages_written double_write status
    -- InnodbDblwrWrites innodb_dblwr_writes double_write status
    -- InnodbLogWaits innodb_log_waits status
    -- InnodbLogWriteRequests innodb_log_write_requests status
    -- InnodbLogWrites innodb_log_writes status
    -- InnodbOsLogFsyncs innodb_os_log_fsyncs fsync()(redo log file) status
    -- InnodbOsLogPendingFsyncs innodb_os_log_pending_fsyncs fsync status
    -- InnodbOsLogPendingWrites innodb_os_log_pending_writes write status
    -- InnodbOsLogWritten innodb_os_log_written status
    -- InnodbPagesCreated innodb_pages_created status
    -- InnodbPagesRead innodb_pages_read buffer_pool status
    -- InnodbPagesWritten innodb_pages_written buffer_pool status
    -- InnodbRowLockCurrentWaits innodb_row_lock_current_waits status
    -- InnodbRowLockTime innodb_row_lock_time status
    -- InnodbRowLockTimeAvg innodb_row_lock_time_avg status
    -- InnodbRowLockTimeMax innodb_row_lock_time_max status
    -- InnodbRowLockWaits innodb_row_lock_waits status
    -- InnodbRowsDeleted innodb_rows_deleted status
    -- InnodbRowsInserted innodb_rows_inserted status
    -- InnodbRowsRead innodb_rows_read status
    -- InnodbRowsUpdated innodb_rows_updated status
    -- OpenTableDefinitions open_table_definitions .frm status
    -- OpenTables open_tables status
    -- OpenedTableDefinitions opened_table_definitions .frm status
    -- OpenedTables opened_tables status
    -- TableOpenCacheOverflows table_open_cache_overflows status
    -- ThreadsCached threads_cached status
    -- ThreadsConnected threads_connected status
    -- ThreadsCreated threads_created status
    -- ThreadsRunning threads_running sleep status
    -- Uptime uptime status
    -- MgrTotalMemberCount mgr p_s
    -- MgrOnLineMemberCount mgronline p_s
    -- MgrMemberState mgr p_s
    -- MgrCountTransactionsInQueue mgr p_s
    -- MgrCountTransactionsChecked mgr p_s
    -- MgrCountConflictsDetected mgr p_s
    -- MgrTransactionsCommittedAllMembers mgr p_s
    -- RplSemiSyncMasterClients masterslave status
    -- RplSemiSyncMasterStatus master status
    -- RplSemiSyncMasterNoTx slave status
    -- RplSemiSyncMasterYesTx slave status
    -- RplSemiSyncSlaveStatus slave status
    -- SlaveIORunning IO(-1:master,0:Yes,1:Yes) show slave status
    -- SlaveSQLRunning SQL(-1:master,0:Yes,1:Yes) show slave status
    -- SecondsBehindMaster Seconds behind master show slave status

  2. mysqltools-python

    mtlsmonitor --user=monitor --password=monitor0352 --host=127.0.0.1 --port=3306 BinlogCacheDiskUse

    0

  3. zabbix

    zabbixserver/agentagentproxy(proxy)servermysqlzabbixphpwebserver

    zabbix

    1): linux 2): apache(httpd) 3): mysql 4): php 5):zabbix-server 6):zabbix-agent 7): zabbix-proxy

    mysqltoolszabbix-servermysqlapache(httpd)php zabbix-servermysqltoolszabbix-serverzabbix-agent

  4. zabbix

     | ip          |

    ----------|----------------|----- sqlstudio | 172.16.192.101 | zabbix-server mysqldb | 172.16.192.128 | zabbix-agent

  5. mysqlpyton-3.6.2

    mysql-8.0.xx mysql-8.0.11phpmysql-5.7.xmysql (mysql)

    (ansiblepython) mysqltoolspython-3.6.2mysqltools-pythonmysqltool-pythonmysqlzabbix

  6. zabbix_server_ip

    mysqltools/config.yamlzabbix_server_ipzabbix-serverip

    zabbix_server_ip: 172.16.192.101

  7. httpd

    1):httpd

    cd mysqltools/deploy/ansible/httpd

    2):install_httpd.yamlzabbixstudio

    ---
  - hosts: zabbixstudio
    vars_files:

    3):httpd

    ansible-playbook install_httpd.yaml

    PLAY [zabbixstudio] ***********************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************
ok: [zabbixstudio]

TASK [install gcc] ************************************************************************************************************
changed: [zabbixstudio]

TASK [install gcc-c++] ********************************************************************************************************
changed: [zabbixstudio]

TASK [install pcre-devel] *****************************************************************************************************
changed: [zabbixstudio]

TASK [openssl-devel] **********************************************************************************************************
changed: [zabbixstudio]

TASK [expat-devel] ************************************************************************************************************
changed: [zabbixstudio]

TASK [perl] *******************************************************************************************************************
ok: [zabbixstudio]

TASK [transfer apr-1.6.2.tar.gz to remote host] *******************************************************************************
changed: [zabbixstudio]

TASK [copy install script to remote] ******************************************************************************************
changed: [zabbixstudio]

TASK [install apr] ************************************************************************************************************
changed: [zabbixstudio]

TASK [remove /tmp/install_apr.sh] *********************************************************************************************
changed: [zabbixstudio]

TASK [remove /tmp/apr-1.6.2] **************************************************************************************************
changed: [zabbixstudio]

TASK [transfer apr-util-1.6.0.tar.gz to remote host] **************************************************************************
changed: [zabbixstudio]

TASK [copy install script to remote] ******************************************************************************************
changed: [zabbixstudio]

TASK [install apr_util] *******************************************************************************************************
changed: [zabbixstudio]

TASK [clear /tmp/ directory] **************************************************************************************************
changed: [zabbixstudio]

TASK [clear /tmp/ directory] **************************************************************************************************
changed: [zabbixstudio]

TASK [copy httpd-2.4.28.tar.gz to remonte host] *******************************************************************************
changed: [zabbixstudio]

TASK [copy install scripts to remonte host] ***********************************************************************************
changed: [zabbixstudio]

TASK [install httpd] **********************************************************************************************************
changed: [zabbixstudio]

TASK [remove /tmp/install_httpd.sh] *******************************************************************************************
changed: [zabbixstudio]

TASK [remove /tmp/httpd-2.4.28.tar.gz] ****************************************************************************************
changed: [zabbixstudio]

TASK [config httpd.service] ***************************************************************************************************
changed: [zabbixstudio]

TASK [start httpd.service] ****************************************************************************************************
changed: [zabbixstudio]

TASK [enable httpd.service] ***************************************************************************************************
changed: [zabbixstudio]

PLAY RECAP ********************************************************************************************************************
zabbixstudio               : ok=25   changed=23   unreachable=0    failed=0

    4):httpd

    ps -ef | grep httpd                                                                   
root      38860      1  0 15:11 ?        00:00:00 /usr/local/httpd/bin/httpd -DFOREGROUND                    
daemon    38861  38860  0 15:11 ?        00:00:00 /usr/local/httpd/bin/httpd -DFOREGROUND                    
daemon    38862  38860  0 15:11 ?        00:00:00 /usr/local/httpd/bin/httpd -DFOREGROUND                    
daemon    38863  38860  0 15:11 ?        00:00:00 /usr/local/httpd/bin/httpd -DFOREGROUND                    
daemon    43716  38860  0 15:15 ?        00:00:00 /usr/local/httpd/bin/httpd -DFOREGROUND

    5):

  8. php

    1):php

    cd mysqltools/deploy/ansible/php

    2):install_php.yamlzabbixstudio

    ---
  - hosts: zabbixstudio
    vars_files:

    3):php

    ansible-playbook install_php.yaml 

    PLAY [zabbixstudio] ************************************************************

TASK [Gathering Facts] *********************************************************
ok: [zabbixstudio]

TASK [install gcc] *************************************************************
ok: [zabbixstudio]

TASK [install gcc-c++] *********************************************************
ok: [zabbixstudio]

TASK [install bzip2-devel] *****************************************************
changed: [zabbixstudio]

TASK [install libjpeg-devel] ***************************************************
changed: [zabbixstudio]

TASK [install libpng-devel] ****************************************************
changed: [zabbixstudio]

TASK [install freetype-devel] **************************************************
changed: [zabbixstudio]

TASK [install freetype-devel] **************************************************
changed: [zabbixstudio]

TASK [stop httpd.service] ******************************************************
changed: [zabbixstudio]

TASK [unarchive php-5.6.31.tar.gz to remonte host] *****************************
changed: [zabbixstudio]

TASK [copy install_php.sh to remonte host] *************************************
changed: [zabbixstudio]

TASK [install php] *************************************************************
changed: [zabbixstudio]

TASK [copy php.ini to remote] **************************************************
changed: [zabbixstudio]

TASK [remove /usr/local/httpd/htdocs/index.html] *******************************
changed: [zabbixstudio]

TASK [copy index.php to remote] ************************************************
changed: [zabbixstudio]

TASK [remove /tmp/install_php.sh] **********************************************
changed: [zabbixstudio]

TASK [remove /tmp/php-5.6.31] **************************************************
changed: [zabbixstudio]

TASK [config httpd] ************************************************************
changed: [zabbixstudio]

TASK [start httpd.service] *****************************************************
changed: [zabbixstudio]

PLAY RECAP *********************************************************************
zabbixstudio               : ok=19   changed=16   unreachable=0    failed=0

    4):web

  9. zabbix

    1):mysqltools/config.yaml

    mysqltoolszabbix_server_ip: xxx.xxx.xxx.xxxzabbix_serveripzabbix_agentzabbix_serverzabbix_serverzabbix_agentmysqltoolszabbix_serverzabbix_agent zabbix_server172.16.192.101config.yaml

    zabbix_server_ip: 172.16.192.101

    2):install_zabbix_server.yamlzabbixstudio

    ---
  - hosts: zabbixstudio
    vars_files:

    3):zabbix_server

    cd mysqltools/deploy/ansible/zabbix
ansible-playbook install_zabbix_server.yaml

    PLAY [zabbixstudio] ***********************************************************************************************************

TASK [Gathering Facts] ********************************************************************************************************
ok: [zabbixstudio]

TASK [add zabbix user to system] **********************************************************************************************
changed: [zabbixstudio]
....

TASK [config zabbix_server start up on boot] **********************************************************************************
changed: [zabbixstudio]

PLAY RECAP ********************************************************************************************************************
zabbixstudio               : ok=32   changed=26   unreachable=0    failed=0

    4):webzabbix

    1zabbix-web

    2

    3zabbix

    next step

    4zabbix-web

    admin zabbix

    5zabbix-web

  10. zabbix

    1): yamlinstall_zabbix_agent.yaml

    ansible-playbook install_zabbix_agent.yaml 

    PLAY [sqlstudio] *******************************************************************************
TASK [Gathering Facts] *************************************************************************
ok: [sqlstudio]
TASK [transfer zabbix install package to remote host and unarchive to /tmp/] *******************
changed: [sqlstudio]
TASK [transfer install script to remonte host] *************************************************
changed: [sqlstudio]
TASK [install zabbix_agent_node] ***************************************************************
changed: [sqlstudio]
TASK [change owner to zabbix user] *************************************************************
changed: [sqlstudio]
TASK [make link] *******************************************************************************
changed: [sqlstudio]
TASK [transfer zabbix config file to remonte host] *********************************************
changed: [sqlstudio]
TASK [remove /tmp/install_zabbix_agent.sh] *****************************************************
changed: [sqlstudio]
TASK [remove /tmp/zabbix-3.4.3] ****************************************************************
changed: [sqlstudio]
TASK [export path env variable] ****************************************************************
ok: [sqlstudio]
TASK [export path env to /root/.bashrc] ********************************************************
ok: [sqlstudio]
TASK [transfer monitor script to remonte host] *************************************************
changed: [sqlstudio]
TASK [config file mode] ************************************************************************
changed: [sqlstudio]
TASK [transfer mtls.conf to remonte] ***********************************************************
changed: [sqlstudio]
TASK [config mtls.conf's mode] *****************************************************************
ok: [sqlstudio]
TASK [config zabbix_agent systemd] *************************************************************
ok: [sqlstudio]
TASK [start zabbix_agent] **********************************************************************
changed: [sqlstudio]
TASK [config zabbix_agent start up on boot] ****************************************************
ok: [sqlstudio]
PLAY RECAP *************************************************************************************
sqlstudio                  : ok=28   changed=12   unreachable=0    failed=0

  11. mysqltoolsmysql

    mysqltoolsMySQLMySQL

  12. mysqltoolsMySQL

    1): zbx_export_mysql_basic_templates.xml MySQL

    2): zbx_export_mysql_replication_templates.xml zbx_export_mysql_basic_templates.xmlreplication

lnmp

lnmp: linux + nginx + mysql + python django()

**** ip ****
uwsgiweb 172.16.192.133 centos-7.4 linux + nginx + mysql + python3.6.x + uwsgi + django

  1. mysql

    mysql

  2. ansiblepython

    mysqltoolspythonpython django2.0.xmysqlclientuwsgi

    1):deploy/ansible/python/install_python.yamlhosts

    ---
 - hosts: uwsgiweb

    2):python

    ansible-playbook install_python.yaml

    PLAY [uwsgiweb] *********************************************************************************

TASK [Gathering Facts] **************************************************************************
ok: [uwsgiweb]

... ... ... ... ... ... 

TASK [install python-3.6.2] *********************************************************************
changed: [uwsgiweb]

TASK [install mysqlclient and mysql-connector-python] *******************************************
changed: [uwsgiweb]

TASK [install django-2.0.4] *********************************************************************
changed: [uwsgiweb]

TASK [install uwsgi] ****************************************************************************
changed: [uwsgiweb]

TASK [install bs4] ******************************************************************************
changed: [uwsgiweb]

TASK [install requests-2.18.4] ******************************************************************
changed: [uwsgiweb]

PLAY RECAP **************************************************************************************
uwsgiweb                   : ok=39   changed=22   unreachable=0    failed=0

    lnmpmysqltoolsdjangouwsgi uwsgi

    ps -ef | grep uwsgi

    uwsgi     40729      1  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40731  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40732  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40733  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40734  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40735  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40736  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40737  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf           
uwsgi     40738  40729  0 13:04 ?        00:00:00 /usr/local/python/bin/uwsgi --ini=/etc/uwsgi.cnf

    uwsgi

  3. nginx

    nginx

    **1):deploy/ansible/nginx/install_nginx.yamlhosts **

    ---
 - hosts: uwsgiweb

    2):nginx

    ansible-playbook install_nginx.yaml 


    PLAY [uwsgiweb] *********************************************************************************

TASK [Gathering Facts] **************************************************************************
ok: [uwsgiweb]
... ... ... ... ... ... ... ... ... ...

TASK [install nginx] ****************************************************************************
changed: [uwsgiweb]

TASK [config nginx] *****************************************************************************
changed: [uwsgiweb]

TASK [create systemd config file for nginx] *****************************************************
ok: [uwsgiweb]

TASK [start nginx(sytemctl)] ********************************************************************
changed: [uwsgiweb]

TASK [config nginx.service start up on boot] ****************************************************
ok: [uwsgiweb]

PLAY RECAP **************************************************************************************
uwsgiweb                   : ok=17   changed=8    unreachable=0    failed=0

    3):nginx

    ps -ef | grep nginx

    root      45536      1  0 13:25 ?        00:00:00 nginx: master process /usr/local/nginx/sbin/nginx          
nginx     45537  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45538  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45539  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45540  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45541  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45542  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45543  45536  0 13:25 ?        00:00:00 nginx: worker process                                      
nginx     45544  45536  0 13:25 ?        00:00:00 nginx: worker process

Related Projects
nohang

A sophisticated low memory handler for Linux

08 Jun 2018 1,043