MySQL 自动化部署脚本

一、环境说明

• 操作系统：CentOS
• 数据库版本：MySQL 5.7/8.0
• 参数：buffer pool 会根据系统内存指定、默认双一、GTID、SlowLog
• 脚本默认安装路径：/usr/local/mysql
• 脚本默认数据路径：/data/mysql*（根据安装包版本适应 比如 5.7 mysql57）

二、使用方法

1. 服务器上创建一个 /myinstall 临时文件夹

   mkdir /myinstall
   

2. 将本地的 MySQL 安装包 和 mysql_install.py 上传到服务器：

   [root@172-16-104-56 test01]# ll
   总用量 645732
   -rw-r--r--. 1 root root 661214270 1月  11 13:59 mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
   -rw-r--r--. 1 root root     10727 1月  12 18:34 mysql_install.py
   

3. 运行 mysql_install.py 即可执行

   python mysql_install.py mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz

   

4. 脚本执行完成后会将登入数据库的命令打印出来，直接登入即可

   

5. 接下来修改密码即可：

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'YouPassword';
   

6. 将 MySQL 添加到 .bash_profile 中：

   vi /root/.bash_profile
   -- 添加到文件中
   PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
   -- 保存后 source
   source /root/.bash_profile
   

7. 验证：

   [root@db01 /]# mysql
   mysql                       mysql_embedded
   mysqladmin                  mysqlimport
   mysqlbinlog                 mysql_install_db
   mysqlcheck                  mysql_plugin
   mysql_client_test_embedded  mysqlpump
   mysql_config                mysql_secure_installation
   mysql_config_editor         mysqlshow
   mysqld                      mysqlslap
   mysqld-debug                mysql_ssl_rsa_setup
   mysqld_multi                mysqltest_embedded
   mysqld_safe                 mysql_tzinfo_to_sql
   mysqldump                   mysql_upgrade
   mysqldumpslow               mysqlxtest
   [root@db01 /]#

脚本

 

import os
import sys
import time
from collections import OrderedDict

 

class MysqlInstall(object):
def __init__(self, mysql_zip_name):
self.start_instructions()
self.mysql_zip = mysql_zip_name
self.base_path = '/usr/local'
self.data_base = self.verify_package('/data')

 

def run(self):
"""
Program entrance
:return:
"""
print('loading....')
time.sleep(1)
self.create_sys_mysql_user()
self.unzip()
self.create_data_base()
self.authorization_set()
self.write_conf()
self.init_mysql()
self.start_mysql()

 

def unzip(self):
"""
unzip mysql
:return:
"""
print('Unpacking the installation package...')
dos_comm_unzip = 'tar -xvf {} -C {}'.format(self.mysql_zip, self.base_path)
os.popen(dos_comm_unzip).read()
temp_path = self.base_path + '/' + self.mysql_zip
now_base_path = temp_path[: temp_path.find('tar') - 1]
dos_comm_mv = 'mv {} {}'.format(now_base_path, self.base_path + '/mysql')
os.popen(dos_comm_mv).read()
print('Successful!')

 

def create_data_base(self):
"""
Creating a data path.
:return:
"""
print('Creating a data directory...')
dos_create_data_path = 'mkdir -p %s/{data,logs,tmp,run}' % self.data_base
dos_create_error_log = 'touch {}'.format(self.data_base + '/logs/error.log')
os.popen(dos_create_data_path).read()
os.popen(dos_create_error_log).read()
print('Successful!')

 

def authorization_set(self):
"""
Directory authorization.
:return:
"""
print('Permission setting in progress...')
dos_auth_base_set = 'chown -R mysql:mysql {}/mysql'.format(self.base_path)
dos_auth_data_set = 'chown -R mysql:mysql {}'.format(self.data_base)
os.popen(dos_auth_base_set)
os.popen(dos_auth_data_set)

 

def write_conf(self):
"""
Write configuration file.
:return:
"""
print('Writing my.conf ...')
client_conf_dict = {
'port': 3306,
'socket': self.data_base + '/data/mysql.sock'
}
mysql_conf_dict = {
'port': 3306,
'socket': self.data_base + '/data/mysql.sock'
}

 

with open('/etc/my.cnf', 'w') as r1:
r1.truncate()
# write client conf
r1.write('[client]\n')
for x, y in client_conf_dict.items():
temp_write = x + ' = ' + str(y) + '\n'
r1.write(temp_write)

 

# write mysql conf
r1.write('[mysql]\n')
for x, y in mysql_conf_dict.items():
temp_write = x + ' = ' + str(y) + '\n'
r1.write(temp_write)

 

# write mysqld conf
r1.write('[mysqld]\n')
for x, y in self.set_mysqld_conf().items():
temp_write = x + ' = ' + str(y) + '\n'
r1.write(temp_write)
print('Successful!')

 

def init_mysql(self):
"""
Initialize the MySQL.
:return:
"""
print('Initializing MySQL...')
dos_init_mysql = '{}/mysql/bin/mysqld --initialize --user=mysql --basedir={}/mysql/ --datadir={}/data'.format(
self.base_path, self.base_path, self.data_base)
os.popen(dos_init_mysql).read()

 

if len(os.listdir(self.data_base + '/data')) > 8:
print('Successful!')
else:
print('=' * 100)
print('Error: An exception occurred during initialization.')
print('=' * 100)
sys.exit(0)

 

def start_mysql(self):
"""
Start the database.
:return:
"""
print('Starting MySQL...')
dos_start_mysql = '/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &'
os.system(dos_start_mysql)
print('\n')
if self.check_mysql():
print('Successful! Please enter to exit the program.')
self.get_password()
else:
print('Error: Database startup failure.')
sys.exit(0)

 

@staticmethod
def create_sys_mysql_user():
"""
Create system users or user groups.
:return:
"""
print('Checking system permissions...')
# if os.popen('echo $LANG $LANGUAGE').read() != 'zh_CN.UTF-8':
# print('[Warning]: A system supported language other than zh_CN.UTF-8 may fail.')

 

dos_group = os.popen('cat /etc/group | grep mysql').read()
dos_user = os.popen('cat /etc/passwd | grep mysql').read()

 

if dos_group == '':
os.popen('groupadd mysql').read()
print('User group creation successful!')
else:
print('50% System Permission Detection!')

 

if dos_user == '':
os.popen('useradd -r -g mysql -s /bin/false mysql').read()
print('User created successfully')
else:
print('100% System Permission Detection!')

 

@staticmethod
def check_mysql():
"""
Verify that MySQL started successfully.
:return:
"""
response_dos = os.popen('ps -ef | grep mysql').read()
if response_dos.find('mysqld'):
return True
else:
return False

 

@staticmethod
def get_buffer_size():
"""
To calculate buffer pool size.
:return:
"""
sys_mem_size = 128
dos_response = os.popen('free -m |grep Mem').read()
for i in dos_response.split(' '):
if i.isdigit():
if int(i) > 1024:
# 3788 * 0.7 = int(2651 // 1024 = 2)
sys_mem_size = (int(int(i) * 0.7) // 1024) * 1024
break
return str(sys_mem_size) + 'M'

 

@staticmethod
def start_instructions():
print('*' * 66)
print('Developer email: wenruo@dtstack.com')
print('*' * 66)
time.sleep(2)

 

def get_password(self):
"""
Get the password from the error log.
:return:
"""
dos_grep_password = 'grep "password" {}/logs/error.log'.format(self.data_base)
response_dos = os.popen(dos_grep_password).read()
temp_password = response_dos[response_dos.rfind(' ') + 1:]
dos_login_mysql = "{}/mysql/bin/mysql -uroot -p'{}' -S {}/data/mysql.sock".format(self.base_path,
temp_password, self.data_base)
print('=' * 100)
print(dos_login_mysql.replace('\n', '').replace('\r', ''))
print('=' * 100)

 

def set_mysqld_conf(self):
"""
Set up the configuration file associated with mysqld
:return: mysqld_conf_odict obj
"""
mysqld_conf_odict = OrderedDict()

 

# base
mysqld_conf_odict['user'] = 'mysql'
mysqld_conf_odict['port'] = 3306
mysqld_conf_odict['basedir'] = self.base_path + '/mysql'
mysqld_conf_odict['datadir'] = self.data_base + '/data'
mysqld_conf_odict['socket'] = self.data_base + '/data/mysql.sock'
mysqld_conf_odict['tmpdir'] = self.data_base + '/tmp'

 

# server_id
mysqld_conf_odict['server-id'] = 1000

 

# binlog
mysqld_conf_odict['log-bin'] = self.data_base + '/logs/mysql-bin'
mysqld_conf_odict['sync_binlog'] = 1
mysqld_conf_odict['binlog_cache_size'] = '4M'
mysqld_conf_odict['max_binlog_cache_size'] = '2G'
mysqld_conf_odict['max_binlog_size'] = '1G'
mysqld_conf_odict['expire_logs_days'] = 7

 

# GTID
mysqld_conf_odict['gtid_mode'] = 'on'
mysqld_conf_odict['enforce_gtid_consistency'] = 1

 

# level
mysqld_conf_odict['transaction_isolation'] = 'READ-COMMITTED'

 

# innodb
mysqld_conf_odict['innodb_buffer_pool_size'] = self.get_buffer_size()
mysqld_conf_odict['innodb_buffer_pool_instances'] = 4
mysqld_conf_odict['innodb_buffer_pool_load_at_startup'] = 1
mysqld_conf_odict['innodb_buffer_pool_dump_at_shutdown'] = 1
mysqld_conf_odict['innodb_data_file_path'] = 'ibdata1:500M:autoextend'
mysqld_conf_odict['innodb_temp_data_file_path'] = 'ibtmp1:200M:autoextend'
mysqld_conf_odict['innodb_flush_log_at_trx_commit'] = 1
mysqld_conf_odict['innodb_log_buffer_size'] = '32M'
mysqld_conf_odict['innodb_log_file_size'] = '128MB'

 

# error_log
mysqld_conf_odict['log-error'] = self.data_base + '/logs/error.log'

 

# Slow log
mysqld_conf_odict['slow_query_log'] = 1
mysqld_conf_odict['slow_query_log_file'] = self.data_base + '/logs/error.log'
mysqld_conf_odict['long_query_time'] = 1
mysqld_conf_odict['log_queries_not_using_indexes'] = 1
mysqld_conf_odict['log_throttle_queries_not_using_indexes'] = 60
mysqld_conf_odict['min_examined_row_limit'] = 0
mysqld_conf_odict['log_slow_admin_statements'] = 1

 

# connections
mysqld_conf_odict['max_connections'] = 1000
mysqld_conf_odict['max_user_connections'] = 64

 

# other
mysqld_conf_odict['pid-file'] = 'mysql1.pid'
mysqld_conf_odict['innodb_doublewrite'] = 1
mysqld_conf_odict['character-set-server'] = 'utf8mb4'
mysqld_conf_odict['skip_name_resolve'] = 1
mysqld_conf_odict['open_files_limit'] = 65535
mysqld_conf_odict['autocommit'] = 1
mysqld_conf_odict['innodb_sort_buffer_size'] = 1048576
mysqld_conf_odict['explicit_defaults_for_timestamp'] = 1

 

# How do I need to add a custom configuration in the following code format
# mysqld_conf_odict[''] = 'xxx'

 

return mysqld_conf_odict

 

def verify_package(self, data_path):
print('Validating the installation package...')
dos_md5 = 'md5sum ' + self.mysql_zip
md5_response = os.popen(dos_md5).read()

 

if md5_response == '':
print(md5_response)
print('Error: Please specify the correct installation package.')
sys.exit(0)
else:
print(md5_response)

 

sp_name = self.mysql_zip.split('-')[1]
db_version = int(sp_name[0:1] + sp_name[2:3])
return data_path + '/mysql_' + str(db_version)

 

if __name__ == '__main__':
try:
zip_name = sys.argv[1]
except IndexError:
print('Please specify the MySQL installation package path.')
sys.exit(0)

 

install_obj = MysqlInstall(zip_name)
install_obj.run()

 摘自：https://github.com/DooBeDooBa/mysql_lib