一个用于快速构建大规模,高质量,全自动化的 mysql分布式集群环境的工具;包含mysql 安装、备份、监控、高可用、读写分离、优化、巡检、自行化运维
mysql-8.0 https://github.com/Neeky/dbm-agent
mysqltoolsdba SQL
1**** () 2**** (100 )
KFC vs ****
KFC80KFC808181
KFC KFC
DBA 1:) 2:) ****
**** vs ****
MySQL+MyCATMySQL()100+MySQL 100+ 1:1
DBA----
mysqltools &&MySQL
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**
172.16.192.131.
**** | ip | **** |
---|
| 172.16.192.131 |centos-7.4 |
| 172.16.192.132 |centos-7.4 |
... | ... |centos-7.x |
1): yummysqltoolsPython-3.6.2gcc ...
2): root()
3): yum
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
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
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
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
cd /usr/local/mysqltools/
tree ./
README.md //
config.yaml // Mac(Mac)
config.yaml-for-linux // linux
deploy //
docs //
trashCan //
tuning // DBASQL
mysqltools/deploy/ansible/ MySQLmysql
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
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
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>
cd
vim
ansible-playbook
1cd mysql 2vimplaybook 3playbook
1):
**2):hosts: **
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)
//
** 1MySQL 2 3 4(mysql-group-replication)** mysqltoolsmysqltoolsmysqltools
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
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
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
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
mysqltools
1mysql_upgrade
mysqltoolsTB``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
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
MHAmysqlmanagernodemanagermastermasternode https://www.cnblogs.com/gomysql/p/3675429.html
mysqltoolsmhamhacentos-7.4
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 |
1mysqlmysql
2ssh(mhascpmasterbinlogssh)
3gccmysqltoolsgcc gcc-c++ yum
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
mysqltools/config.yamlmha
mtls_mha_node: mhanode.tar.gz
mtls_mha_manager: mhamanager.tar.gz
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
**mysqltools/deploy/ansible/mha/install_mha.yamlhostansiblehosts: **
---
- hosts: mhacluster
mhaclusterinstall_mha.yaml
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
**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-python
pythonbin
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 **
python 1):mysqldump,xtrabackup,mysqlbackupmtlsbackup
2):(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
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_minute
linux crontab minute,backup_hour
linux crontab hour,backup_user
linux 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
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
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
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
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 |
mysqltools-python
mtlsmonitor --user=monitor --password=monitor0352 --host=127.0.0.1 --port=3306 BinlogCacheDiskUse
0
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
| ip |
----------|----------------|----- sqlstudio | 172.16.192.101 | zabbix-server mysqldb | 172.16.192.128 | zabbix-agent
mysql-8.0.xx mysql-8.0.11phpmysql-5.7.xmysql (mysql)
(ansiblepython) mysqltoolspython-3.6.2mysqltools-pythonmysqltool-pythonmysqlzabbix
mysqltools/config.yamlzabbix_server_ipzabbix-serverip
zabbix_server_ip: 172.16.192.101
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):
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
1):mysqltools/config.yaml
mysqltoolszabbix_server_ip: xxx.xxx.xxx.xxx
zabbix_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
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
mysqltoolsMySQLMySQL
1): zbx_export_mysql_basic_templates.xml MySQL
2): zbx_export_mysql_replication_templates.xml zbx_export_mysql_basic_templates.xmlreplication
lnmp: linux + nginx + mysql + python django()
**** | ip | **** | |
---|---|---|---|
uwsgiweb | 172.16.192.133 | centos-7.4 | linux + nginx + mysql + python3.6.x + uwsgi + django |
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
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