How to automatically install and deploy MySQL

Anyone who has been using MySQL O & M should be clear that online MySQL usually uses source code compilation, because this allows you to select the function to be compiled based on your own needs, although the MySQL source code compilation is quite simple, just think about it. If you have hundreds of servers and want to install MySQL at the same time, do you still need to manually compile and compile configuration files? This is obviously too inefficient. This article discusses the automated installation and deployment of MySQL.

1. Create an RPM package that meets your needs

We need to compile the RPM package that meets the enterprise's needs based on the MySQL source code. The command for getting the source code is as follows:

Wget http://downloads.mysql.com/archives/get/file/mysql-5.6.16.tar.gz
Tar-zxvf mysql-5.6.16.tar.gz
Cd mysql-5.6.16
Mkdir rpm
Cd rpm

We obtained the source code above and created the rpm directory under the main directory of the source code. Then we created the mysql. spec file under the directory:

Name: mysql
Version: 5.6.16
Release: guahao
License: GPL
URL: http://downloads.mysql.com/archives/get/file/mysql-5.6.16.tar.gz
Group: applications/database
BuildRoot: % {_ tmppath}/% {name}-% {version}-% {release}-root
BuildRequires: cmake
Packager: zhuxj@guahao.com
Autoreq: no
Prefix:/opt/mysql
Summary: MySQL 5.6.16

% Description
The MySQL (TM) software delivers a very fast, multi-threaded, multi-user,
And robust SQL (Structured Query Language) database server. MySQL Server
Is intended for mission-critical, heavy-load production systems as well
As for embedding into mass-deployed software.

% Define MYSQL_USER mysql
% Define MYSQL_GROUP mysql
% Define _ OS _install_post % {nil}

% Build
Cd $ OLDPWD /../
CFLAGS = "-O3-g-fno-exceptions-static-libgcc-fno-omit-frame-pointer-fno-strict-aliasing"
CXX = g ++
CXXFLAGS = "-O3-g-fno-exceptions-fno-rtti-static-libgcc-fno-omit-frame-pointer-fno-strict-aliasing"
Export CFLAGS CXX CXXFLAGS

Cmake .\
-DSYSCONFDIR: PATH =%{ prefix }\
-DCMAKE_INSTALL_PREFIX: PATH =%{ prefix }\
-DCMAKE_BUILD_TYPE: STRING = Release \
-DENABLE_PROFILING: BOOL = ON \
-DWITH_DEBUG: BOOL = OFF \
-DWITH_VALGRIND: BOOL = OFF \
-DENABLE_DEBUG_SYNC: BOOL = OFF \
-DWITH_EXTRA_CHARSETS: STRING = all \
-DWITH_SSL: STRING = bundled \
-DWITH_UNIT_TESTS: BOOL = OFF \
-DWITH_ZLIB: STRING = bundled \
-DWITH_PARTITION_STORAGE_ENGINE: BOOL = ON \
-DWITH_INNOBASE_STORAGE_ENGINE: BOOL = ON \
-DWITH_ARCHIVE_STORAGE_ENGINE: BOOL = ON \
-DWITH_BLACKHOLE_STORAGE_ENGINE: BOOL = ON \
-DWITH_PERFSCHEMA_STORAGE_ENGINE: BOOL = ON \
-DDEFAULT_CHARSET = utf8 \
-DDEFAULT_COLLATION = utf8_general_ci \
-DWITH_EXTRA_CHARSETS = all \
-DENABLED_LOCAL_INFILE: BOOL = ON \
-DWITH_EMBEDDED_SERVER = 0 \
-DINSTALL_LAYOUT: STRING = STANDALONE \
-DCOMMUNITY_BUILD: BOOL = ON \
-DMYSQL_SERVER_SUFFIX = '-r5436 ';

Make-j 'cat/proc/cpuinfo | grep processor | wc-l'

% Install
Cd $ OLDPWD /../
Make DESTDIR = $ RPM_BUILD_ROOT install

% Clean
Rm-rf $ RPM_BUILD_ROOT

% Files
% Defattr (-, % {MYSQL_USER}, % {MYSQL_GROUP })
% Attr (755, % {MYSQL_USER}, % {MYSQL_GROUP}) % {prefix }/*

% Pre

% Post
Ln-s % {prefix}/lib % {prefix}/lib64

% Preun

% Changelog
With this spec file, you can execute the following command to generate your own RPM package:
Rpmbuild-bb./mysql. spec

2. Compile the my. cnf Template

The my. cnf template is as follows:

[Mysqld_safe]
Pid-file =/opt/mysql/run/mysqld. pid

[Mysql]
Prompt =\\ u \\ d \\ r :\\ m :\\ s>
Default-character-set = gbk
No-auto-rehash

[Client]
Socket =/opt/mysql/run/mysql. sock

[Mysqld]
# Dir
Basedir =/opt/mysql
Datadir =/data/mysql/data
Tmpdir =/data/mysql/tmp
Log-error =/data/mysql/log/alert. log
Slow_query_log_file =/data/mysql/log/slow. log
General_log_file =/data/mysql/log/general. log
Socket =/opt/mysql/run/mysql. sock

# Innodb
Innodb_data_home_dir =/data/mysql/data
Innodb_log_group_home_dir =/data/mysql/data
Innodb_data_file_path = ibdata1: 2G; ibdata2: 16 M: autoextend
Innodb_buffer_pool_size = 10G
Innodb_buffer_pool_instances = 4
Innodb_log_files_in_group = 4
Innodb_log_file_size = 1G
Innodb_log_buffer_size = 200 M
Innodb_flush_log_at_trx_commit = 1
Innodb_additional_mem_pool_size = 20 M
Innodb_max_dirty_pages_pct = 60
Innodb_io_capacity = 200
Innodb_thread_concurrency = 32
Innodb_read_io_threads = 8
Innodb_write_io_threads = 8
Innodb_open_files = 60000
Innodb_file_format = Barracuda
Innodb_file_per_table = 1
Innodb_flush_method = O_DIRECT
Innodb_change_buffering = all
Innodb_adaptive_flushing = 1
Innodb_old_blocks_time = 1000
Innodb_stats_on_metadata = 0
Innodb_read_ahead = 0
Innodb_use_native_aio = 0
Innodb_lock_wait_timeout = 50
Innodb_rollback_on_timeout = 0
Innodb_purge_threads = 1
Innodb_strict_mode = 1
Transaction-isolation = READ-COMMITTED

# Myisam
Key_buffer_size = 100 M
Myisam_sort_buffer_size = 64 M
Concurrent_insert = 2
Delayed_insert_timeout = 300

# Replication
Master-info-file =/data/mysql/log/master.info
Relay-log =/data/mysql/log/mysql-relay
Relay_log_info_file =/data/mysql/log/mysql-relay.info
Relay-log-index =/data/mysql/log/mysql-relay.index
Slave_load_tmpdir =/data/mysql/tmp
Slave_type_conversions = "ALL_NON_LOSSY"
Slave_net_timeout = 4
Skip-slave-start
Sync_master_info = 1000
Sync_relay_log_info = 1000

# Binlog
Log-bin =/data/mysql/log/mysql-bin
Server_id = 2552763370
Binlog_cache_size = 32 K
Max_binlog_cache_size = 2G
Max_binlog_size = 500 M
Binlog_format = ROW
Sync_binlog = 1000
Log-slave-updates = 1
Expire_logs_days = 0

# Server
Default-storage-engine = INNODB
Character-set-server = gbk
Lower_case_table_names = 1
Skip-external-locking
Open_files_limit = 65536
Safe-user-create
Local-infile = 1
Performance_schema = 0

Log_slow_admin_statements = 1
Log_warnings = 1
Long_query_time = 1
Slow_query_log = 1
General_log = 0

Query_cache_type = 0
Query_cache_limit = 1 M
Query_cache_min_res_unit = 1 K

Table_definition_cached = 65536

Thread_stack = 512 K
Thread _ cache_size = 256
Read_rnd_buffer_size = 128 K
Sort_buffer_size = 256 K
Join_buffer_size = 128 K
Read_buffer_size = 128 K

Port = 3306
Skip-name-resolve
Skip-ssl
Max_connections = 4500
Max_user_connection= 4000
Max_connect_errorrs = 65536
Max_allowed_packet = 128 M
Connect_timeout = 8
Net_read_timeout = 30
Net_write_timeout = 60
Back_log = 1024

# Server id

Careful readers should pay attention to the following. the end of cnf is left blank on the server id, and the shell script will be added dynamically. This is because the server IDs of all MySQL instances in an enterprise must maintain global consistency, this will not cause confusion during master-slave replication.
In fact, if you want to write this script more commonly, you can leave more parameters to white, such as port, datadir, and memory-related parameters. Here I just take the server id as an example to introduce them.

3. Prepare the MySQL data directory Template

You have to prepare a MySQL instance in advance. You can store all the common items (such as accounts) as needed. Below is a simple data directory structure of MySQL that has been installed:

[Root @ lx25 mysql] # ls-l
Total 12
Drwxr-xr-x 5 mysql 4096 Jul 2 data
Drwxr-xr-x 2 mysql 4096 Jul 1 log
Drwxr-xr-x 2 mysql 4096 Jul 2 tmp
[Root @ lx25 mysql] # cd data
[Root @ lx25 data] # ls-l
Total 6314044
Drwx ------ 2 mysql 4096 Jul 1 mysql
Drwx ------ 2 mysql 4096 Jul 1 performance_schema
Drwx ------ 2 mysql 4096 Jul 1 test

Use the Tartar package (named data.tar) as the Directory and decompress it to the data directory of the newly installed MySQL instance.
4. Compile an automated installation and deployment script

Before running this script, we must copy the rpm packages, my. the cnf template and data directory template are placed in a fixed place. In this example, they are placed on the internal ftp of the enterprise.

The MySQL automatic installation and deployment script (named mysql_install.sh) is as follows:

#! /Bin/sh

# Step 1: Prepare
Yum install cmake gcc g ++ bison ncurses-devel zlib

Groupadd mysql
Useradd-g mysql

# Step 2: Get Source
Ftp-n <EOF
Open 10.10.100.254
User zhuxianjie zxj321
Binary
Cd mysql
Prompt
Mget *
EOF

# Step 3: Install
Unique_id = 'date "+ % Y % m % d % M % S "'
Echo 'server _ id = '$ unique_id> my. cnf
Rpm-ivh mysql-5.6.16-guahao.x86_64.rpm
Cp my. cnf/opt/mysql
Chown-R mysql: mysql/opt/mysql

Tar xvf data.tar-C/data
Chown-R mysql: mysql/data/mysql

# Step 4: Start MySQL
Cp/opt/mysql/support-files/mysql. server/etc/rc. d/init. d/mysqld
Chmod 755/etc/init. d/mysqld
Chkconfig mysqld on

/Etc/init. d/mysqld start