Sharing Mysql's automated installation deployment method _mysql

Has the MySQL operation Dimension person should be clear, the line MySQL generally uses the source code compiles, because this can according to the enterprise each need to choose to compile the function, although the MySQL source code compiles quite simple, but imagine, if you have hundreds of servers simultaneously to install the MySQL, Do you still have a table to manually compile and write configuration files? This is obviously too inefficient, this article discusses the automated installation of MySQL deployment.

1, the production of the RPM package to meet their needs

We want to according to MySQL source code compiled in line with the needs of the RPM package, the source code to obtain the following command:

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

In the above we get the source code, and in the main source directory to create the RPM directory, and then we create the Mysql.spec file in 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
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
Once you have this spec file, you can build our own RPM package by executing the following command:
Rpmbuild-bb./mysql.spec

2, write 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:16m: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=200m
Innodb_flush_log_at_trx_commit=1
innodb_additional_mem_pool_size=20m
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=100m
myisam_sort_buffer_size=64m
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=32k
max_binlog_cache_size=2g
max_binlog_size=500m
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=1m
query_cache_min_res_unit=1k

table_definition_cache=65536

thread_stack=512k
thread_cache_size=256
read_rnd_buffer_size=128k
sort_buffer_size=256k
join_buffer_size=128k
read_buffer_size=128k

port=3306
Skip-name-resolve
Skip-ssl
max_connections=4500
max_user_connections=4000
max_connect_errors=65536
max_allowed_packet=128m
Connect_timeout=8
Net_read_timeout=30
Net_write_timeout=60
back_log=1024

#server ID

Attentive readers should note that there is a blank space on the server ID at the end of the my.cnf, and that the subsequent shell script is dynamically added because all MySQL server IDs within an enterprise must be globally consistent so that it does not cause confusion when primary copy is replicated.
In fact, if you want to write this script more general, you can leave more parameters to white, such as Port, DataDir, memory-related parameters, here I just take the server ID as an example.

3. Prepare MySQL Data Catalog template

You have to prepare a MySQL, you can according to their own needs, the commonality of things on the above (such as accounts, etc.), the following is a simple installed MySQL data directory structure:

[Root@lx25 mysql]# Ls-l
Total 12
Drwxr-xr-x 5 mysql mysql 4096 June 2 09:26 data
Drwxr-xr-x 2 MySQL mysql 4096 June 1 18:21 Log
Drwxr-xr-x 2 mysql mysql 4096 June 2 09:26 tmp
[root@lx25 mysql]# CD data
[Root@lx25 data]# Ls-l
Total 6314044
drwx------2 MySQL mysql 4096 June 1 17:17 MySQL
drwx------2 MySQL mysql 4096 June 1 17:17 Performance_schema
drwx------2 MySQL mysql 4096 June 1 17:17 test

Pack the catalog in tar (named Data.tar), and then extract the template into a new MySQL instance data directory.
4. Write an automated installation deployment script

Before we run this script, we have to put the RPM packages, my.cnf templates, and data Catalog templates that were made in the previous sections in a fixed place, in this case, on FTP inside the enterprise.

The MySQL Automation installation 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 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