【python脚本】一台服务器安装多个mysql

说明：该脚本是一台服务器安装多个mysql
1、执行脚本：python3 install_mysql.py 端口号
2、mysql路径：/usr/local/mysql5.7
3、mysql数据路径：/mysql/$PORT/

　mysql版本：mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

 

#!/usr/bin/env python3.6
# -*- coding:utf-8 -*-
# autor: wuxun

import subprocess
import os
import sys
import re

mysql_port = sys.argv[1]
base_dir =  '/usr/local/mysql5.7'

def run_command(command):
    p = subprocess.Popen(command,shell=True,close_fds=True,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
    stdout,stderr = p.communicate()
    return (p.returncode,stdout.strip().decode('utf-8'),stderr.strip().decode('utf-8'))

def check_mysql_rpm():
    '''
    检查环境中是否存在mysql和mariadb插件
    如果存在，卸载该插件
    '''
    print('检查环境中是否存在mysql rpm包！')
    command= 'rpm -qa | grep -i "mysql"'
    for i in run_command(command)[1].split('\n'):
        rpm_e_command = 'rpm -e --nodeps '+ i
        run_command(rpm_e_command)

    if run_command(command)[0] == 1 :
        print('已无mysql/mariadb依赖包。')
    else:
        print('卸载mysql rpm包失败1！！手动检查！')
        sys.exit()

def check_user():
    '''
    检查是否存在mysql用户
    :return:
    '''
    print('检查是否存在mysql用户')
    if run_command('id mysql')[0] == 0:
        print('存在mysql用户！')
        print(run_command(' id mysql')[1])
    else:
        print('不存在mysql用户，创建mysql用户！')
        run_command('groupadd mysql')
        run_command('useradd -g mysql -M -s /sbin/login mysql')

def mkdir_basedis(port):
    '''
    创建mysql目录
    :return:
    '''
    print('创建mysql根目录！')
    if os.path.isfile(base_dir+'/bin/mysqld'):
        print('存在{}目录'.format(base_dir))
        pass
    else:
        run_command('rm -rf {}'.format(base_dir))
        run_command('mkdir -p '+base_dir)
        print('解压mysql！')
        run_command('mkdir /tmp/mysql')
        tar_cmd = 'tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /tmp/mysql '
        if run_command(tar_cmd)[0] == 0:
            run_command('mv /tmp/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/* '+base_dir)
            run_command('chown -R mysql:mysql ' + base_dir)
            run_command('rm -rf /tmp/mysql')
        else:
            print('uncompress wrong!')
            sys.exit()
    print('创建mysql数据目录.')
    if os.path.isdir('/mysql/'):
        if os.path.isdir('/mysql/'+port):
            run_command('rm -rf /mysql/'+port)
            run_command('mkdir -p /mysql/' + port + '/{data,logs,tmp}')
        else:
            run_command('mkdir -p /mysql/' + port + '/{data,logs,tmp}')
    else:
        run_command('mkdir -p /mysql/' + port + '/{data,logs,tmp}')
    run_command('chown -R mysql:mysql /mysql')

def alter_file(file,old_info,new_info):
    '''
    替换文件内容
    :param file: 源文件
    :param old_info: 需要替换的内容
    :param new_info: 替换后的内容
    :return:
    '''
    with open(file,'r',encoding="utf-8") as old , open("{}.bak".format(file),'w',encoding="utf-8") as new:
        for line in old:
            new.write(re.sub(old_info,new_info,line))
    os.remove(file)
    os.rename("{}.bak".format(file),file)

def add_config(port):
    print('配置mysql配置文件！')
    if os.path.isfile('/etc/my{}.cnf'.format(port)):
        run_command('mv /etc/my{}.cnf /etc/my{}.cnf.bak.`date +%Y%m%d`'.format(port,port))
        run_command('cp $PWD/my.cnf /etc/my{}.cnf'.format(port))
    else:
        run_command('cp $PWD/my.cnf /etc/my'+port+'.cnf')
    config_path = '/etc/my'+port+'.cnf'
    alter_file(config_path,'99999999',port)

def add_envpath():
    print('检查环境变量！')
    env_path = 'export PATH={}/bin:$PATH'.format(base_dir)
    with open('/etc/profile', 'rt') as profile:
        if  env_path in profile.readlines():
            print('存在环境变量！')
        else:
            print('不存在环境变量，插入环境变量！')
            with open('/etc/profile', 'a+') as profile_path:
                profile_path.write('\n'+env_path)
    run_command('source /etc/profile')

def init_mysql(port):
    '''
    1、初始化mysql
    2、启动mysql
    3、修改mysql root密码为Admin123
    '''
    # mysql_init = '{}/bin/mysqld --defaults-file=/etc/my{}.cnf --initialize-insecure --user=mysql --basedir={} --datadir=/mysql/{}/data'.format(base_dir,port,base_dir,port)
    mysql_init = '{}/bin/mysqld --defaults-file=/etc/my{}.cnf --initialize --user=mysql --basedir={} --datadir=/mysql/{}/data'.format(base_dir, port, base_dir, port)
    try:
        mysql_init_result = run_command(mysql_init)
        print('初始化mysqld！')
        if mysql_init_result[0] == 0:
            with open('/mysql/'+port+'/logs/mysql-error.log') as mysql_error:
                for line in mysql_error:
                    if 'root@localhost:' in line:
                        old_password = line.split(': ')[1].split('\n')[0]
                        print(old_password)
            print('初始化mysql成功，开始启动mysql。')
    except Exception as e:
        raise e
    start_cmd = '{}/bin/mysqld --defaults-file=/etc/my{}.cnf --user=mysql &'.format(base_dir, port)
    try:
        if run_command(start_cmd)[0] == 0:
            print('启动成功！修改root密码为Admin123')
            change_password_cmd = "mysql --connect-expired-password -uroot -p'{}' -S /mysql/{}/tmp/mysql_{}.sock -e ".format(old_password,port,port)+'"'+"set password="+"'"+"Admin123"+"'"+'"'
            if run_command(change_password_cmd)==0:
                print('密码修改成功！\n 修改后root密码：Admin123')
            else:
                print('密码修改失败！密码为{}'.format(old_password))
            print('添加mysql启动模式：')
            run_command('cp $PWD/my /bin/')
            print("mysql启动/停止/查看状态：my  start/stop/status 端口号;\n"
                  "启动/停止/查看所有的mysql：my all start/stop/status;\n"
                  "mysql快捷进入方式（相当于：mysql -uroot -p）：my 端口号\n")
    except Exception as e:
        raise e

def main():
    check_mysql_rpm()
    check_user()
    mkdir_basedis(mysql_port)
    add_config(mysql_port)
    add_envpath()
    init_mysql(mysql_port)

if __name__ == '__main__':
    main()

 

#!/usr/bin/env bash
case $1 in
start)
  if [[ $2 =~ ^[0-9]+$ ]] && [[ -z $3 ]] ;then
    for port in $(ls /mysql)
    do
      if [ $port -eq $2 ];then
        /usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my$2.cnf &
      fi
    done
  else
    echo -e  "\n输入错误，请输入正确格式："
    echo  -e "mysql启动/停止/查看状态：my  start/stop/status 端口号;\n启动/停止/查看所有的mysql：my all start/stop/status;\nmysql特殊进入方式（相当于：mysql -uroot -p）：my 端口号"
    exit
  fi
;;
stop)
  if [[ $2 =~ ^[0-9]+$ ]] && [[ -z $3 ]] ;then
    for port in $(ls /mysql)
    do
      if [ $port -eq $2 ];then
        mysqladmin -uroot -pAdmin123 -S /mysql/$2/tmp/mysql_$2.sock shutdown
      fi
    done
  else
    echo  -e "\n输入错误，请输入正确格式："
    echo  -e  "mysql启动/停止/查看状态：my  start/stop/status 端口号;\n启动/停止/查看所有的mysql：my all start/stop/status;\nmysql特殊进入方式（相当于：mysql -uroot -p）：my 端口号"
    exit
  fi

;;
status)
  if [[ $2 =~ ^[0-9]+$ ]] && [[ -z $3 ]] ;then
    for port in $(ls /mysql)
    do
      if [ $port -eq $2 ];then
        mysqladmin -uroot -pAdmin123 -S /mysql/$2/tmp/mysql_$2.sock ping
      fi
    done
  else
    echo -e  "\n输入错误，请输入正确格式："
    echo -e  "mysql启动/停止/查看状态：my  start/stop/status 端口号;\n启动/停止/查看所有的mysql：my all start/stop/status;\nmysql特殊进入方式（相当于：mysql -uroot -p）：my 端口号"
    exit
  fi

;;
all)
  case $2 in
  start)
    for port in $(ls /mysql)
    do
      /usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my$port.cnf &
    done
  ;;
  stop)
    for port in $(ls /mysql)
    do
      mysqladmin -uroot -pAdmin123 -S /mysql/$port/tmp/mysql_$port.sock shutdown
    done
  ;;
  status)
    for port in $(ls /mysql)
    do
      mysqladmin -uroot -pAdmin123 -S /mysql/$port/tmp/mysql_$port.sock ping
    done

  ;;
  *)
    echo -e  "\n输入错误，请输入正确格式："
    echo -e  "mysql启动/停止/查看状态：my  start/stop/status 端口号;\n启动/停止/查看所有的mysql：my all start/stop/status;\nmysql特殊进入方式（相当于：mysql -uroot -p）：my 端口号"
  ;;
  esac
;;
*)
  if [[ $1 =~ ^[0-9]+$ ]] && [[ -z $2 ]] ;then
    mysql -S /mysql/$1/tmp/mysql_$1.sock -uroot -pAdmin123
  else
    echo  -e "\n输入错误，请输入正确格式："
    echo -e  "mysql启动/停止/查看状态：my  start/stop/status 端口号;\n启动/停止/查看所有的mysql：my all start/stop/status;\nmysql特殊进入方式（相当于：mysql -uroot -p）：my 端口号"
    exit
  fi
;;
esac

 

[mysqld]
user = mysql
basedir = /usr/local/mysql/
datadir = /mysql/99999999/data
tmpdir = /mysql/99999999/tmp
port = 99999999
socket = /mysql/99999999/tmp/mysql_99999999.sock
event_scheduler = 0
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
skip-name-resolve = 1
#lower_case_table_names=1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /mysql/99999999/logs/slow.log
log-error = /mysql/99999999/logs/mysql-error.log
log_warnings = 2
pid-file = /mysql/99999999/tmp/mysql_99999999.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = mixed
server-id = 2022
log-bin = mybinlog
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
#innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

[mysql]
socket=/mysql/99999999/tmp/mysql_99999999.sock

[client]
socket=/mysql/99999999/tmp/mysql_99999999.sock