Shulou(Shulou.com)05/31 Report--

This article is about how MySQL automates installation and deployment. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Make RPM packages that meet your needs.

We need to compile the RPM package that meets the needs of the enterprise according to the source code of MySQL. The command to obtain 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

Above we get the source code and create a rpm directory under the source code home directory, and then we create a mysql.spec file under this 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 in place, we can generate 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

Careful readers should notice that the server id is left blank at the end of the my.cnf and will be dynamically added in the subsequent shell script, this is because the server id of all MySQL within an enterprise must be globally consistent so that it will not cause confusion in the active and standby replication.

In fact, if you want to make this script more generic, you can leave more parameters blank, such as port, datadir, memory-related parameters, and so on. Here I just take server id as an example.

3. Prepare the MySQL data catalog template

You need to prepare a MySQL in advance, and you can put general-purpose things (such as accounts, etc.) on it according to your own needs. Here is the simplest installed MySQL data directory structure:

[root@lx25 mysql] # ls-l

Total 12

Drwxr-xr-x 5 mysql mysql 4096 Jul 2 09:26 data

Drwxr-xr-x 2 mysql mysql 4096 Jul 1 18:21 log

Drwxr-xr-x 2 mysql mysql 4096 Jul 2 09:26 tmp

[root@lx25 mysql] # cd data

[root@lx25 data] # ls-l

Total 6314044

Drwx- 2 mysql mysql 4096 Jul 1 17:17 mysql

Drwx- 2 mysql mysql 4096 Jul 1 17:17 performance_schema

Drwx- 2 mysql mysql 4096 Jul 1 17:17 test

Package the directory with tar (named data.tar), and then use this as a template to decompress it to the data directory of the newly installed MySQL instance.

4. Write automated installation and deployment scripts

Before running this script, we have to put the rpm packages, my.cnf templates, and data catalog templates made in the previous installments in a fixed place, in this case on the ftp within the enterprise.

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

#! / bin/sh

# Step 1: Prepare

Yum install cmake gcc gathers + bison ncurses-devel zlib

Groupadd mysql

Useradd-g mysql mysql

# Step 2: Get Source

Ftp-n 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

Attachment 1: (MySQL compilation parameters)

CMAKE_BUILD_TYPE

Compiled version types: RelWithDebInfo and Debug, the difference is that RelWithDebInfo is optimized.

CMAKE_INSTALL_PREFIX

Specify the destination path for the make install installation.

SYSCONFDIR

Specifies the default path to the configuration file.

MYSQL_DATADIR

Specifies the default path to the data directory.

WITH_DEBUG

Specify whether there is debugging information, which is generally used for source code debugging, when WITH_DEBUG is opened and the production environment is closed.

ENABLED_PROFILING

Specifies whether show profile can be used to display the details of the operation.

DEFAULT_CHARSET

Specifies the default character set, which can be specified in the startup configuration file.

DEFAULT_COLLATION

Specifies the rules for default character comparison and sorting.

WITH_EXTRA_CHARSETS

Specify other character sets that may be used.

WITH_SSL

Specify the type of SSL, the default bundled type starting at 5.6.6, and you can also specify the path address of the SSL library.

WITH_ZLIB

Specifies the type of zlib for the compression feature.

WITH_storage_STORAGE_ENGINE

Specifies the storage engine supported by compilation, and the MyISAM,MERGE,MEMORY,CSV storage engine is supported by default.

ENABLED_LOCAL_INFILE

Specifies whether the load data infile feature is allowed.

WITH_EMBEDDED_SERVER

Specifies whether to compile the libmysqld embedded library.

INSTALL_LAYOUT

Specifies the type of layout to install.

Thank you for reading! This is the end of the article on "how to automate the installation and deployment of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.