def get_oracle_alert(tags, db_conn, oracle_params, linux_params):
db_version = oracle_params['db_version']
host = oracle_params['host']
# 取alert日志
sql = "select alert_log,alert_log_seek from oracle_list where tags='{}' ".format(
tags)
alert_log, alert_log_seek = mysql_query(sql)[0]
if not alert_log:
sql = "select value from v$diag_info where name = 'Diag Trace'"
alert_dir = query_one(db_conn, sql)
# 取实例名
sql = "select instance_name from v$instance"
instance_name = query_one(db_conn, sql)
alert_log = '{}/alert_{}.log'.format(alert_dir[0], instance_name[0])
alert_log_seek = 0
sql = "delete from alert_log where tags='{}' and type=1 ".format(tags)
mysql_exec(sql)
# ssh获取日志内容
linux_oper = LinuxBase(linux_params)
# 日志解析
alert_content = linux_oper.readfile(alert_log, seek=alert_log_seek)
alert_log_seek = parse_oracle_alert_logs(tags, host, alert_content,
db_version)
# 更新配置表中日志路径,日志偏移量
sql = "update oracle_list set alert_log='{}',alert_log_seek={} where tags='{}' ".format(
alert_log, alert_log_seek, tags)
mysql_exec(sql)
def clear_rac(self):
self.log('开始清理Oracle RAC')
# 删除目录
cmd_list = [
'rm -rf /u01', 'rm -f /usr/local/bin/dbhome',
'rm -f /usr/local/bin/oraenv', 'rm -f /usr/local/bin/coraenv',
'rm -f /etc/oratab', 'rm -f /etc/oraInst.loc',
'rm -rf /etc/oracle', 'rm -rf /etc/ora*',
'rm -rf /etc/init/oracle*', 'rm -rf /etc/init.ohasd',
'rm -rf /etc/ohasd', 'rm -rf /etc/init.tfa',
'rm -rf /var/tmp/.oracle', 'rm -rf /tmp/CVU*',
'rm -rf /tmp/OraInstall*', 'rm -rf /var/tmp/.oracle',
'rm -rf /opt/oracle'
]
# 删除用户组
cmd_list.extend([
'groupdel {}'.format(group[1]) for group in self.linux_group_list
])
# 删除用户
cmd_list.extend(['userdel -rf oracle'], )
# 清理完成后重启服务器
cmd_list.extend(['reboot'])
linux_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': self.node_info['node_password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.log('{}：开始清理Oracle RAC！'.format(self.node_info['node_ip']))
res = [
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('Oracle RAC清理完成！')
def generate_mysql_cnf(self, linux_conn, linux_params):
self.log('开始生成MySQL配置文件..')
mysql_path = self.node_info['mysql_path']
data_path = self.node_info['data_path']
mysql_port = self.node_info['port']
# innodb buffer pool大小设置为物理内存*0.7
memory_size = float(self.node_info['memory'])
innodb_buffer_pool_size = str(int(memory_size * 0.7 * 1024)) + 'M'
cnf_template = '{}my.cnf.template.5.7'.format(
self.local_path) if self.node_info[
'version'] == 'MySQL5.7' else '{}my.cnf.template.8.0'.format(
self.local_path)
LinuxBase(linux_params).sftp_upload_file(cnf_template, '/tmp/my.cnf')
cmd_list = [
'mv /tmp/my.cnf {}/my.cnf'.format(mysql_path),
"sed -i 's#MYSQL_PATH#{}#g' {}/my.cnf".format(
mysql_path,
mysql_path), "sed -i 's#DATA_PATH#{}#g' {}/my.cnf".format(
data_path, mysql_path),
"sed -i 's/INNODB_SIZE/{}/g' {}/my.cnf".format(
innodb_buffer_pool_size,
mysql_path), "sed -i 's/MYSQL_PORT/{}/g' {}/my.cnf".format(
mysql_port, mysql_path)
]
res = [
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('{}：MySQL配置文件生成完成'.format(self.node_info['ip']))
def do_linux_config(self, node1, node2):
self.log('开始进行linux基础配置..')
for node in self.node_list:
self.log('{} {}：开始进行linux操作系统配置！'.format(node['ip'], node['hostname']))
cmd_list = self.linux_config(node1, node2)
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': node['password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.upload_software(linux_params)
# 修改主机名
cmd_list.extend([
'hostnamectl set-hostname {}'.format(self.rac_info['hostname'] + node['node_id'])
])
# 修改profile文件(需单独处理)
cmd_list.extend([
'mv /tmp/grid_profile /home/grid/.bash_profile',
'chown grid:oinstall /home/grid/.bash_profile',
"sed -i 's/NODE_ASM_SID/{}/g' /home/grid/.bash_profile".format('+ASM' + node['node_id']),
'mv /tmp/oracle_profile /home/oracle/.bash_profile',
'chown oracle:oinstall /home/oracle/.bash_profile',
"sed -i 's/NODE_ORACLE_SID/{}/g' /home/oracle/.bash_profile".format(
self.rac_info['dbname'] + node['node_id'])
])
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
self.log('{} {}：linux操作系统配置完成'.format(node['ip'], node['hostname']))
self.log('所有节点linux基础配置完成！')
def oracle_install(self):
self.log('开始进行oracle软件安装..')
linux_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.log('{} {}：开始上传oracle安装包..'.format(self.node_info['node_ip'],
self.node_info['hostname']))
LinuxBase(linux_params).sftp_upload_file(
'{}LINUX.X64_193000_db_home.zip'.format(self.local_path),
'/tmp/LINUX.X64_193000_db_home.zip')
self.log('oracle安装包上传完成！')
self.log('开始解压缩oracle安装包..')
cmd = 'unzip -q -o /tmp/LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1/'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('oracle安装包解压缩完成！')
cmd = 'rm -f /tmp/LINUX.X64_193000_db_home.zip'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('oracle安装包清理完成！')
self.log('开始生成oracle安装响应文件..')
LinuxBase(linux_params).sftp_upload_file(
'{}db_install_onenode.rsp'.format(
os.getcwd() + '/utils/oracle_onenode_install/'),
'/tmp/db_install.rsp')
self.log('oracle安装响应文件生成完成！')
self.log(
'oracle软件安装前配置成功，请在节点{}上使用oracle用户执行静默安装脚本：'
'/u01/app/oracle/product/19.0.0/dbhome_1/runInstaller -silent -ignorePrereqFailure -responsefile /tmp/db_install.rsp '
' 并根据提示执行后续脚本'.format(self.node_info['node_ip']))
def oracle_execute_scripts(self):
cmd = '/u01/app/oracle/product/19.0.0/dbhome_1/root.sh'
linux_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': self.node_info['node_password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
LinuxBase(linux_params).exec_command_res(cmd)
def create_home_dir(linux_params, linux_conn, port, data_path, mysql_base):
# 创建MySQL目录
dirs = get_dir_for_mysql(mysql_base, port)
for dir in dirs:
cmd = 'mkdir -p %s' % dir
LinuxBase(linux_params).exec_command(cmd, linux_conn)
print('创建目录%s！' % dir)
# 创建数据目录
datadir = '%s/my%s' % (data_path, port)
cmd = 'mkdir -p %s' % datadir
LinuxBase(linux_params).exec_command(cmd, linux_conn)
print('创建目录%s！' % datadir)
print('创建目录完成！')
def grid_execute_scripts(self):
linux_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': self.node_info['node_password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
cmd = '/u01/app/oraInventory/orainstRoot.sh'
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
cmd = '/u01/app/19.0.0/grid/root.sh'
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
def clear_rac(self):
# 删除目录
cmd_list = [
'rm -rf /u01',
'rm -f /usr/local/bin/dbhome',
'rm -f /usr/local/bin/oraenv',
'rm -f /usr/local/bin/coraenv',
'rm -f /etc/oratab',
'rm -f /etc/oraInst.loc',
'rm -rf /etc/oracle',
'rm -rf /etc/ora*',
'rm -rf /etc/init/oracle*',
'rm -rf /etc/init.ohasd',
'rm -rf /etc/ohasd',
'rm -rf /etc/init.tfa',
'rm -rf /var/tmp/.oracle',
'rm -rf /tmp/CVU*',
'rm -rf /tmp/OraInstall*',
'rm -rf /var/tmp/.oracle',
'rm -rf /opt/oracle'
]
# 删除用户组
cmd_list.extend(
['groupdel {}'.format(group[1]) for group in self.linux_group_list]
)
# 删除用户
cmd_list.extend(
['userdel -rf grid',
'userdel -rf oracle'],
)
# 清除ASM信息
cmd_list.extend(
['dd if=/dev/zero of={} bs=8192 count=2147'.format(self.rac_info['ocr_disk']),
'dd if=/dev/zero of={} bs=8192 count=2147'.format(self.rac_info['data_disk'])]
)
# 清理完成后重启服务器
cmd_list.extend(
['reboot']
)
for node in node_list:
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': node['password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.log('{}：开始清理Oracle RAC！'.format(node['ip']))
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
self.log('Oracle RAC清理完成！')
def upload_software(self, linux_params):
# 文件上传
LinuxBase(linux_params).sftp_upload_file(
'{}oracle_profile'.format(self.local_path), '/tmp/oracle_profile')
LinuxBase(linux_params).sftp_upload_file(
'{}compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm'.format(
self.local_path),
'/tmp/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm')
LinuxBase(linux_params).sftp_upload_file(
'{}97-oracle-database-sysctl.conf'.format(self.local_path),
'/tmp/97-oracle-database-sysctl.conf')
LinuxBase(linux_params).sftp_upload_file(
'{}oracle-database-preinstall-19c.conf'.format(
os.getcwd() + '/utils/oracle_onenode_install/'),
'/tmp/oracle-database-preinstall-19c.conf')
def oracle_dbca(self):
self.log('开始进行dbca建库..')
grid_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': '******'
}
self.log('开始创建ASM磁盘组：DATA..')
# 创建ASM磁盘组
cmd = "/u01/app/19.0.0/grid/bin/asmca -silent -createDiskGroup -diskGroupName DATA -disk '{}' " \
"-redundancy EXTERNAL -au_size 4 -compatible.asm '19.0.0.0.0' " \
"-compatible.rdbms '19.0.0.0.0' -compatible.advm '19.0.0.0.0'".format(self.node_info['data_disk'])
LinuxBase(grid_params).exec_command_res(cmd)
self.log('ASM磁盘组创建完成！')
oracle_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(oracle_params).connection()
self.log('开始生成dbca建库响应文件..')
LinuxBase(oracle_params).sftp_upload_file(
'{}dbca_onenode.rsp'.format(self.local_path), '/tmp/dbca.rsp')
# 修改响应文件
cmd_list = [
"sed -i 's/NODE_HOSTNAME/{}/g' /tmp/dbca.rsp".format(
self.node_info['hostname']),
"sed -i 's/NODE_DBNAME/{}/g' /tmp/dbca.rsp".format(
self.node_info['dbname']),
"sed -i 's/NODE_PDBNAME/{}/g' /tmp/dbca.rsp".format(
self.node_info['pdbname'])
]
res = [
LinuxBase(oracle_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('dbca建库响应文件生成完成！')
self.log(
'dbca配置成功，请在节点{}上使用oracle用户执行dbca建库脚本：/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbca -silent -createDatabase -ignorePrereqFailure '
'-responseFile /tmp/dbca.rsp'
' 并根据提示执行后续脚本'.format(self.node_info['node_ip']))
def get_mysql_slowquery(tags,mysql_params,linux_params):
host = mysql_params['host']
# slow query log location
sql = "select slowquery_log,slowquery_log_seek from mysql_list where tags='{}' ".format(tags)
slowquery_log,slowquery_log_seek = mysql_query(sql)[0]
if not slowquery_log:
slowquery_log = Mysql_Do(mysql_params).get_para('slow_query_log_file')
slowquery_log_seek = 0
# get slowquery log content
linux_oper = LinuxBase(linux_params)
slowquery_content = linux_oper.readfile(slowquery_log,seek=slowquery_log_seek)
# parse log
slowquery_log_seek = parse_mysql_slowquery_logs(tags,host,slowquery_content)
# update alert log info to mysqlinfo
sql = "update mysql_list set slowquery_log='{}',slowquery_log_seek={} where tags='{}' " .format(slowquery_log,slowquery_log_seek,tags)
mysql_exec(sql)
def generate_conf(linux_params, port, data_path, mysql_base, mysql_home,
extra_params):
"""
:param conf_dict, a dict
:return:
"""
mycnf = 'my.cnf_new'
conf_tmpl = read_cnf_template()
conf_dict = gen_conf_for_instance(linux_params['hostname'], port,
data_path, mysql_base)
if not conf_tmpl.has_section('mysqld'):
conf_tmpl.add_section('mysqld')
for param_name, param_value in conf_dict.items():
conf_tmpl.set('mysqld', param_name, str(param_value))
for param in extra_params:
param_name = param['param_name']
param_value = param['param_value']
conf_tmpl.set('mysqld', param_name, param_value)
with open(mycnf, 'w') as cnf:
conf_tmpl.write(cnf)
print('生成配置文件%s！' % mycnf)
LinuxBase(linux_params).sftp_upload_file(mycnf, '%s/my.cnf' % mysql_home)
print(mysql_home)
print('上传配置文件至%s/my.cnf！' % mysql_home)
def get_mysql_alert(tags,mysql_params,linux_params):
host = mysql_params['host']
# logfile loglocation
sql = "select alert_log,alert_log_seek from mysql_list where tags='{}' ".format(tags)
alert_log,alert_log_seek = mysql_query(sql)[0]
if not alert_log:
alert_log = Mysql_Do(mysql_params).get_para('log_error')
alert_log_seek = 0
sql = "delete from alert_log where tags='{}' and type=2 ".format(tags)
mysql_exec(sql)
# get alert log content
linux_oper = LinuxBase(linux_params)
alert_content = linux_oper.readfile(alert_log,seek=alert_log_seek)
# parse log
alert_log_seek = parse_mysql_alert_logs(tags,host,alert_content)
# update alert log info to mysqlinfo
sql = "update mysql_list set alert_log='{}',alert_log_seek={} where tags='{}' " .format(alert_log,alert_log_seek,tags)
mysql_exec(sql)
def create_mysql_dir(self,linux_conn,linux_params):
mysql_path = self.node_info['mysql_path']
data_path = self.node_info['data_path']
mysql_run = '{}/run' .format(mysql_path)
mysql_tmp = '{}/tmp'.format(mysql_path)
mysql_undo = '{}/undo'.format(mysql_path)
dirs = (mysql_path,data_path,mysql_run,mysql_tmp,mysql_undo)
cmd_list = ['mkdir -p {}'.format(dir) for dir in dirs]
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
def do_mysql_install(self ):
linux_params = {
'hostname': self.node_info['ip'],
'port': 22,
'username': '******',
'password': self.node_info['password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.linux_config(linux_conn,linux_params)
self.create_mysql_dir(linux_conn,linux_params)
self.generate_mysql_cnf()
def get_redis_log(tags,redis_params,linux_params):
host = redis_params['host']
# logfile loglocation
sql = "select log,log_seek from redis_list where tags='{}' ".format(tags)
log,log_seek = mysql_query(sql)[0]
if not log:
redis_conn = RedisBase(redis_params).connection()
log_dir = redis_conn.config_get('dir')['dir']
log = redis_conn.config_get('logfile')['logfile']
log = os.path.join(log_dir,log)
log_seek = 0
sql = "delete from alert_log where tags='{}' and type=3 ".format(tags)
mysql_exec(sql)
# get log content
linux_oper = LinuxBase(linux_params)
alert_content = linux_oper.readfile(log,seek=log_seek)
# parse log
log_seek = parse_redis_logs(tags,host,alert_content)
# update alert log info to mysqlinfo
sql = "update redis_list set log='{}',log_seek={} where tags='{}' " .format(log,log_seek,tags)
mysql_exec(sql)
def mysql_initialize(self, linux_conn, linux_params):
mysql_path = self.node_info['mysql_path']
data_path = self.node_info['data_path']
mysql_package = self.mysql_soft_config[self.node_info['version']]
# 上传
self.log('开始上传MySQL安装包：{}..'.format(mysql_package))
LinuxBase(linux_params).sftp_upload_file(
'{}{}'.format(self.local_path, mysql_package),
'/tmp/{}'.format(mysql_package))
self.log('{}：MySQL安装包上传完成'.format(self.node_info['ip']))
# 解压
self.log('开始解压MySQL安装包..')
cmd = 'tar xvf /tmp/{} -C {}/'.format(mysql_package, mysql_path)
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
self.log('{}：MySQL安装包解压完成'.format(self.node_info['ip']))
# 授权
cmd_list = [
'mv {}/mysql*/* {}'.format(mysql_path, mysql_path),
'chown -R mysql:mysql {}'.format(mysql_path),
'chmod -R 775 {}'.format(mysql_path),
'chown -R mysql:mysql {}'.format(data_path),
'chmod -R 775 {}'.format(data_path),
]
res = [
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('开始初始化MySQL..')
# 初始化
cmd = '{}/bin/mysqld --defaults-file={}/my.cnf --initialize-insecure --user=mysql'.format(
mysql_path, mysql_path)
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
self.log('{}：MySQL初始化完成'.format(self.node_info['ip']))
self.log(
'请使用MySQL用户(默认密码mysqld)启动MySQL数据库：{}/bin/mysqld_safe --defaults-file={}/my.cnf --user=mysql &'
.format(mysql_path, mysql_path))
def oracle_dbca(self):
self.log('开始进行dbca建库..')
oracle_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(oracle_params).connection()
self.log('开始生成dbca建库响应文件..')
LinuxBase(oracle_params).sftp_upload_file(
'{}dbca_onenode.rsp'.format(os.getcwd() +
'/utils/oracle_onenode_install/'),
'/tmp/dbca.rsp')
# 修改响应文件
cmd_list = [
"sed -i 's/NODE_DBNAME/{}/g' /tmp/dbca.rsp".format(
self.node_info['dbname']),
"sed -i 's/NODE_PDBNAME/{}/g' /tmp/dbca.rsp".format(
self.node_info['pdbname'])
]
res = [
LinuxBase(oracle_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('dbca建库响应文件生成完成！')
self.log(
'dbca配置成功，请在节点{}上使用oracle用户执行dbca建库脚本：/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbca -silent -createDatabase -ignorePrereqFailure '
'-responseFile /tmp/dbca.rsp'
' 并根据提示执行后续脚本'.format(self.node_info['node_ip']))
self.log(
'DBCA建库成功后，创建数据库监听：netca -silent -responsefile /u01/app/oracle/product/19.0.0/dbhome_1/assistants/netca/netca.rsp'
)
def grid_execute_scripts(self):
for node in self.node_list:
cmd = '/u01/app/oraInventory/orainstRoot.sh'
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': node['password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
LinuxBase(linux_params).exec_command_res(cmd)
for node in self.node_list:
cmd = '/u01/app/19.0.0/grid/root.sh'
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': node['password']
}
linux_conn, _ = LinuxBase(linux_params).connection()
LinuxBase(linux_params).exec_command_res(cmd)
def linux_config(self,linux_conn,linux_params):
cmd_list = [
'systemctl stop firewalld',
'systemctl disable firewalld',
"sed -i 's/SELINUX=enabled/SELINUX=disabled/g' /etc/selinux/config ", #关闭防火墙，selinux
'move /etc/my.cnf /etc/my.cnfbak', #移除旧的MySQL参数文件
'yum search libaio',
'yum install libaio', #安装libaio包
'userdel -r mysql',
'groupadd mysql',
'useradd -g mysql -G mysql mysql',
"echo 'mysql:mysqld'|chpasswd", #创建MySQL用户
]
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
def install_default_database(linux_params, linux_conn, data_path, mysql_base,
port, mysql_home):
# 上传安装包，解压缩
soft_dir = os.getcwd() + '/mysql_install'
soft_name = 'mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz'
local_soft = '%s/%s' % (soft_dir, soft_name)
remote_loc = '%s/%s' % (mysql_base, soft_name)
LinuxBase(linux_params).sftp_upload_file(local_soft, remote_loc)
print('上传安装文件至%s！' % remote_loc)
cmd = 'tar -xzvf %s -C %s' % (remote_loc, mysql_base)
print(cmd)
LinuxBase(linux_params).exec_command(cmd, linux_conn)
print('解压缩完成！')
cmd = 'mv %s/mysql*/* %s' % (mysql_base, mysql_home)
print(cmd)
# LinuxBase(linux_params).exec_command(cmd,linux_conn)
# 建用户，授权
cmd = '/usr/bin/groupadd mysql'
# LinuxBase(linux_params).exec_command(cmd,linux_conn)
cmd = '/usr/bin/useradd -d /home/mysql -g mysql -m mysql'
# LinuxBase(linux_params).exec_command(cmd)
print('创建MySQL用户成功！')
cmd = 'chown -R mysql:mysql %s' % mysql_base
# LinuxBase(linux_params).exec_command(cmd)
cmd = 'chown -R mysql:mysql %s/my%s' % (data_path, port)
LinuxBase(linux_params).exec_command(cmd, linux_conn)
# 初始化MySQL
cmd = '%s/bin/mysqld --defaults-file=%s/my.cnf --initialize-insecure --user=mysql' % (
mysql_home, mysql_home)
# LinuxBase(linux_params).exec_command(cmd,linux_conn)
print('初始化MySQL数据库成功！')
# 启动MySQL数据库
cmd = '%s/bin/mysqld_safe --defaults-file=%s/my.cnf --user=mysql &' % (
mysql_home, mysql_home)
print("创建MySQL：%s:%s成功，初始密码为空，请运行%s启动MySQL数据库！" %
(linux_params['hostname'], port, cmd))
def upload_software(self, linux_params):
# 文件上传
LinuxBase(linux_params).sftp_upload_file('{}grid_profile'.format(self.local_path), '/tmp/grid_profile')
LinuxBase(linux_params).sftp_upload_file('{}oracle_profile'.format(self.local_path), '/tmp/oracle_profile')
LinuxBase(linux_params).sftp_upload_file('{}compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm'.format(self.local_path),
'/tmp/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm')
LinuxBase(linux_params).sftp_upload_file('{}cvuqdisk-1.0.10-1.rpm'.format(self.local_path),
'/tmp/cvuqdisk-1.0.10-1.rpm')
LinuxBase(linux_params).sftp_upload_file('{}97-oracle-database-sysctl.conf'.format(self.local_path),
'/tmp/97-oracle-database-sysctl.conf')
LinuxBase(linux_params).sftp_upload_file('{}oracle-database-preinstall-19c.conf'.format(self.local_path),
'/tmp/oracle-database-preinstall-19c.conf')
def create_mysql_dir(self, linux_conn, linux_params):
self.log('开始创建MySQL目录..')
mysql_path = self.node_info['mysql_path']
data_path = self.node_info['data_path']
mysql_run = '{}/run'.format(mysql_path)
mysql_tmp = '{}/tmp'.format(mysql_path)
mysql_undo = '{}/undo'.format(mysql_path)
dirs = (mysql_path, data_path, mysql_run, mysql_tmp, mysql_undo)
cmd_list = [
'rm -rf {}'.format(mysql_path), 'rm -rf {}'.format(data_path)
]
cmd_list.extend(['mkdir -p {}'.format(dir) for dir in dirs])
res = [
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('{}：创建目录完成'.format(self.node_info['ip']))
def oracle_install(self, node):
self.log('开始进行oracle软件安装..')
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(linux_params).connection()
self.log('{} {}：开始上传oracle安装包..'.format(node['ip'], node['hostname']))
LinuxBase(linux_params).sftp_upload_file('{}LINUX.X64_193000_db_home.zip'.format(self.local_path),'/tmp/LINUX.X64_193000_db_home.zip')
self.log('oracle安装包上传完成！')
self.log('开始解压缩oracle安装包..')
cmd = 'unzip -q -o /tmp/LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1/'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('oracle安装包解压缩完成！')
cmd = 'rm -f /tmp/LINUX.X64_193000_db_home.zip'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('oracle安装包清理完成！')
# 配置oracle用户ssh互信
self.log('开始节点间oracle用户ssh互信配置..')
cmd_list = [
'rm -f /tmp/hostlist.cfg',
'echo {} > /tmp/hostlist.cfg'.format(self.rac_info['hostname'] + '1'),
'echo {} >> /tmp/hostlist.cfg'.format(self.rac_info['hostname'] + '2'),
'sh /tmp/auto_ssh.sh oracle oracle /tmp/hostlist.cfg'
]
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
self.log('oracle用户ssh互信配置完成！')
self.log('开始生成oracle安装响应文件..')
LinuxBase(linux_params).sftp_upload_file('{}db_install.rsp'.format(self.local_path), '/tmp/db_install.rsp')
# 修改响应文件
cmd_list = [
"sed -i 's/NODE_HOSTNAME/{}/g' /tmp/db_install.rsp".format(self.rac_info['hostname']),
]
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
self.log('oracle安装响应文件生成完成！')
self.log('oracle软件安装前配置成功，请在1号节点{}上使用oracle用户执行静默安装脚本：'
'/u01/app/oracle/product/19.0.0/dbhome_1/runInstaller -silent -ignorePrereqFailure -responsefile /tmp/db_install.rsp '
' 并根据提示执行后续脚本'.format(node['ip']))
def grid_install(self, node):
self.log('开始进行grid集群软件安装..')
# 只在第一节点操作
linux_params = {
'hostname': node['ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(linux_params).connection()
# 上传grid安装包
self.log('{} {}：开始上传grid安装包..'.format(node['ip'], node['hostname']))
LinuxBase(linux_params).sftp_upload_file('{}LINUX.X64_193000_grid_home.zip'.format(self.local_path), '/tmp/LINUX.X64_193000_grid_home.zip')
self.log('grid安装包上传完成！')
self.log('{} {}：开始解压缩grid安装包！'.format(node['ip'], node['hostname']))
cmd = 'unzip -q -o /tmp/LINUX.X64_193000_grid_home.zip -d /u01/app/19.0.0/grid/'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('grid安装包解压缩完成！')
cmd = 'rm -f /tmp/LINUX.X64_193000_grid_home.zip'
LinuxBase(linux_params).exec_command_res(cmd)
self.log('grid安装包清理完成！')
self.log('开始节点间grid用户ssh互信配置..')
LinuxBase(linux_params).sftp_upload_file('{}auto_ssh.sh'.format(self.local_path), '/tmp/auto_ssh.sh')
self.log('ssh互信配置脚本上传完成！')
# 配置grid用户ssh互信
cmd_list = [
'rm -f /tmp/hostlist.cfg',
'echo {} > /tmp/hostlist.cfg' .format(self.rac_info['hostname']+'1'),
'echo {} >> /tmp/hostlist.cfg' .format(self.rac_info['hostname']+'2'),
'sh /tmp/auto_ssh.sh grid oracle /tmp/hostlist.cfg'
]
res = [LinuxBase(linux_params).exec_command_res(cmd) for cmd in cmd_list]
self.log('grid用户ssh互信配置完成！')
self.log('开始生成grid安装响应文件..')
LinuxBase(linux_params).sftp_upload_file('{}gridsetup.rsp'.format(self.local_path), '/tmp/gridsetup.rsp')
# 修改响应文件
cmd_list = [
"sed -i 's/NODE_HOSTNAME/{}/g' /tmp/gridsetup.rsp".format(self.rac_info['hostname']),
"sed -i 's/NODE_PUBLIC_INTERFACE/{}/g' /tmp/gridsetup.rsp".format(self.rac_info['public_interface']),
"sed -i 's/NODE_PUBLIC_SUBNET/{}/g' /tmp/gridsetup.rsp".format(self.rac_info['public_subnet']),
"sed -i 's/NODE_PRIVATE_INTERFACE/{}/g' /tmp/gridsetup.rsp".format(self.rac_info['private_interface']),
"sed -i 's/NODE_PRIVATE_SUBNET/{}/g' /tmp/gridsetup.rsp".format(self.rac_info['private_subnet']),
"sed -i 's#NODE_OCR_DISK#{}#g' /tmp/gridsetup.rsp".format(self.rac_info['ocr_disk']),
"sed -i 's#NODE_OCR_PATH#{}#g' /tmp/gridsetup.rsp".format(self.rac_info['disk_path'])
]
res = [LinuxBase(linux_params).exec_command_res(cmd, linux_conn) for cmd in cmd_list]
self.log('grid安装响应文件生成完成！')
# 先决条件检查
# $ORACLE_HOME/runcluvfy.sh stage -pre crsinst -responseFile gridsetup.rsp
self.log('grid集群配置成功，请在1号节点{}上使用grid用户执行先决条件检查：$ORACLE_HOME/runcluvfy.sh stage -pre crsinst -responseFile /tmp/gridsetup.rsp'.format(node['ip']))
self.log('在先决条件检查完成后，请在1号节点{}上执行静默安装：/u01/app/19.0.0/grid/gridSetup.sh -silent -ignorePrereqFailure -responseFile /tmp/gridsetup.rsp'
' 并根据提示执行root.sh脚本'.format(node['ip']))
# 执行静默安装
# self.log('开始执行集群软件静默安装..请关注grid安装日志 {}：/tmp/gridsetup.log'.format(node['ip']))
# cmd = '/u01/app/19.0.0/grid/gridSetup.sh -silent -ignorePrereqFailure -responseFile /tmp/gridsetup.rsp > /tmp/gridsetup.log'
# LinuxBase(linux_params).exec_command_res(cmd)
# self.log('grid集群件静默安装完成！')
# self.log('请根据/tmp/gridsetup.log中的提示执行脚本！')
# 执行静默安装脚本
# self.grid_execute_scripts()
# 执行配置工具脚本
cmd = '/u01/app/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /tmp/gridsetup.rsp -silent'
def get_diskfree(self):
ret = []
command = 'df -k'
res = super().exec_command(command, self.conn)
disk_list = [line.split() for line in res]
disk_list.pop(0)
# print(disk_list)
for each in disk_list:
if each[0] == 'Filesystem' or each[0] == 'none' or each[
0] == 'udev' or each[0] in "tmpfs":
continue
else:
ret.append(each)
# print(ret)
return ret
if __name__ == '__main__':
linux_params = {
'hostname': '192.168.48.60',
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(linux_params).connection()
linuxstat = LinuxStat(linux_params, linux_conn)
print(linuxstat.get_diskfree())
def check_linux(tags, linux_params):
check_time = now()
host = linux_params['hostname']
port = linux_params['port']
# create connection
linux_conn, _ = LinuxBase(linux_params).connection()
if linux_conn:
checklog.logger.info('{}:开始获取Linux主机监控信息'.format(tags))
# get linuxstat data
linuxstat = LinuxStat(linux_params, linux_conn).get_linux()
hostinfo = linuxstat['hostinfo']
cpuinfo = linuxstat['cpuinfo']
memtotal = linuxstat['Memtotal']
ipinfo = linuxstat['ipinfo']
load = linuxstat['load']
cpustat = linuxstat['cpu']
iostat = linuxstat['iostat']
memstat = linuxstat['mem']
vmstat = linuxstat['vmstat']
tcpstat = linuxstat['tcpstat']
netstat = linuxstat['net']
procstat = linuxstat['proc']
# total network in/out
recv_kbps = round(sum([d['recv'] for d in netstat]), 2)
send_kbps = round(sum([d['send'] for d in netstat]), 2)
# total io
read_mb = round(sum([d['rd_m_s'] for d in iostat]), 2)
write_mb = round(sum([d['wr_m_s'] for d in iostat]), 2)
iops = round(sum([d['io_s'] for d in iostat]), 2)
# cpu used percent
cpu_used = round(100 - cpustat['cpu_idle'], 2)
# memory used percent
mem_used = round((float(memstat['mem_used_mb']) /
(float(memtotal['memtotal']) / 1024)) * 100, 2)
insert_data_values = {
**locals(),
**hostinfo,
**cpuinfo,
**memtotal,
**ipinfo,
**load,
**cpustat,
**memstat,
**vmstat,
**tcpstat,
**procstat
}
print("cpu_speed:")
insert_data_sql = "insert into linux_stat(tags,host,port,hostname,ipinfo,linux_version,updays,kernel,frame,cpu_mode,cpu_cache,processor,cpu_speed," \
"recv_kbps,send_kbps,load1,load5,load15,cpu_sys,cpu_iowait,cpu_user,cpu_used,memtotal,mem_used,mem_cache,mem_buffer,mem_free,mem_used_mb," \
"swap_used,swap_free,swapin,swapout,pgin,pgout,pgfault,pgmjfault,tcp_close,tcp_timewait,tcp_connected,tcp_syn,tcp_listen,iops,read_mb,write_mb," \
"proc_new,proc_running,proc_block,intr,ctx,softirq,status,check_time) " \
"values ('{tags}','{host}',{port},'{hostname}','{ipinfo}','{linux_version}',{updays},'{kernel}','{frame}','{cpu_mode}','{cpu_cache}','{processor}','{cpu_speed}'," \
"{recv_kbps},{send_kbps},{load1},{load5},{load15},{cpu_sys},{cpu_iowait},{cpu_user},{cpu_used},{memtotal},{mem_used},{mem_cache},{mem_buffer},{mem_free},{mem_used_mb}," \
"{swap_used},{swap_free},{swapin},{swapout},{pgin},{pgout},{pgfault},{pgmjfault},{tcp_close},{tcp_timewait},{tcp_connected},{tcp_syn},{tcp_listen},{iops},{read_mb},{write_mb}," \
"{proc_new},{proc_running},{proc_block},{intr},{ctx},{softirq},0,'{check_time}')"
clear_table(tags, 'linux_stat')
insert_sql = insert_data_sql.format(**insert_data_values)
mysql_exec(insert_sql)
archive_table(tags, 'linux_stat')
# disk free
clear_table(tags, 'linux_disk')
diskfree_list = LinuxStat(linux_params, linux_conn).get_diskfree()
for each in diskfree_list:
dev, total_size, used_size, free_size, used_percent, mount_point = each
print("used_percent>>>")
used_percent = float(used_percent.replace('%', ''))
insert_data_sql = '''insert into linux_disk(tags,host,dev,total_size,used_size,free_size,used_percent,mount_point,check_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
values = (tags, host, dev, round(float(total_size) / 1024, 2),
round(float(used_size) / 1024,
2), round(float(free_size) / 1024,
2), used_percent, mount_point, now())
mysql_exec(insert_data_sql, values)
archive_table(tags, 'linux_disk')
# io stat
clear_table(tags, 'linux_io_stat')
for each in iostat:
insert_data_sql = "insert into linux_io_stat(tags,host,dev,rd_s,rd_avgkb,rd_m_s,rd_mrg_s,rd_cnc,rd_rt,wr_s,wr_avgkb,wr_m_s,wr_mrg_s,wr_cnc,wr_rt,busy,in_prg,io_s,qtime,stime,check_time)" \
" values ('{tags}','{host}','{dev}',{rd_s},{rd_avgkb},{rd_m_s},{rd_mrg_s},{rd_cnc},{rd_rt},{wr_s},{wr_avgkb},{wr_m_s},{wr_mrg_s},{wr_cnc},{wr_rt},{busy},{in_prg},{io_s},{qtime},{stime},'{check_time}')"
insert_data_values = {**locals(), **each}
insert_sql = insert_data_sql.format(**insert_data_values)
mysql_exec(insert_sql)
archive_table(tags, 'linux_io_stat')
else:
error_msg = "{}:linux主机连接失败".format(tags)
checklog.logger.error(error_msg)
checklog.logger.info('{}:写入linux_stat采集数据'.format(tags))
clear_table(tags, 'linux_stat')
sql = "insert into linux_stat(tags,host,port,status,check_time) values('{tags}','{host}',{port},1,'{check_time}')"
sql = sql.format(**locals())
mysql_exec(sql)
archive_table(tags, 'linux_stat')
def grid_install(self):
self.log('开始进行grid集群软件安装..')
# 只在第一节点操作
linux_params = {
'hostname': self.node_info['node_ip'],
'port': 22,
'username': '******',
'password': '******'
}
linux_conn, _ = LinuxBase(linux_params).connection()
# 上传grid安装包
self.log('{} {}：开始上传grid安装包..'.format(self.node_info['node_ip'],
self.node_info['hostname']))
LinuxBase(linux_params).sftp_upload_file(
'{}LINUX.X64_193000_grid_home.zip'.format(self.local_path),
'/tmp/LINUX.X64_193000_grid_home.zip')
self.log('grid安装包上传完成！')
self.log('{} {}：开始解压缩grid安装包！'.format(self.node_info['node_ip'],
self.node_info['hostname']))
cmd = 'unzip -q -o /tmp/LINUX.X64_193000_grid_home.zip -d /u01/app/19.0.0/grid/'
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
self.log('grid安装包解压缩完成！')
cmd = 'rm -f /tmp/LINUX.X64_193000_grid_home.zip'
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
self.log('grid安装包清理完成！')
self.log('开始生成grid安装响应文件..')
LinuxBase(linux_params).sftp_upload_file(
'{}gridsetup_onenode.rsp'.format(self.local_path),
'/tmp/gridsetup.rsp')
# 修改响应文件
cmd_list = [
"sed -i 's#NODE_OCR_DISK#{}#g' /tmp/gridsetup.rsp".format(
self.node_info['ocr_disk']),
"sed -i 's#NODE_OCR_PATH#{}#g' /tmp/gridsetup.rsp".format(
self.node_info['disk_path'])
]
res = [
LinuxBase(linux_params).exec_command_res(cmd, linux_conn)
for cmd in cmd_list
]
self.log('grid安装响应文件生成完成！')
# 静默安装
self.log(
'grid集群配置成功，请在节点{}上执行静默安装：/u01/app/19.0.0/grid/gridSetup.sh -silent -ignorePrereqFailure -responseFile /tmp/gridsetup.rsp'
' 并根据提示执行root.sh脚本'.format(self.node_info['node_ip']))
# 执行静默安装
# self.log('开始执行集群软件静默安装..请关注grid安装日志 {}：/tmp/gridsetup.log'.format(node['ip']))
# cmd = '/u01/app/19.0.0/grid/gridSetup.sh -silent -ignorePrereqFailure -responseFile /tmp/gridsetup.rsp > /tmp/gridsetup.log'
# LinuxBase(linux_params).exec_command_res(cmd)
# self.log('grid集群件静默安装完成！')
# self.log('请根据/tmp/gridsetup.log中的提示执行脚本！')
# 执行静默安装脚本
# self.grid_execute_scripts()
# 执行配置工具脚本
cmd = '/u01/app/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /tmp/gridsetup.rsp -silent'