How to use scripts to quickly deploy MySQL and deploy one machine and multiple instances

How to use scripts to quickly deploy MySQL and deploy one machine and multiple instances

MySQL has three versions: binary, source code package, and RPM. The following describes how to install a binary package.

:

Http://dev.mysql.com/downloads/mysql/

Select Linux-Generic

Here I choose mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz.

After decompression, there is an INSTALL-BINARY file, which actually shows the deployment process of the BINARY Package.

Shell> groupadd mysql
Shell> useradd-r-g mysql-s/bin/false mysql
Shell> cd/usr/local
Shell> tar zxvf/path/to/mysql-VERSION-OS.tar.gz
Shell> ln-s full-path-to-mysql-VERSION-OS mysql
Shell> cd mysql
Shell> chown-R mysql.
Shell> chgrp-R mysql.
Shell> scripts/mysql_install_db -- user = mysql
Shell> chown-R root.
Shell> chown-R mysql data
Shell> bin/mysqld_safe -- user = mysql &
# Next command is optional
Shell> cp support-files/mysql. server/etc/init. d/mysql. server

Compared with the deployment in the actual production environment, the above step is missing during database initialization-that is, the configuration file is specified. If the configuration file is confirmed, the data directory and log directory are all confirmed, the deployment of MySQL binary is quite easy.

The following is a script based on the configuration file provided later. The execution format is as follows:

Sh 4.sh/root/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz/mysql3306 3306

Among them, 4. sh is the script,/root/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz is the absolute path of the Binary Package,/mysql3306 is basedir, 3306 is the port to be set,

With this script, you only need to pre-define the configuration file to quickly deploy the MySQL database and deploy multiple instances on one server.

#! /Bin/bash
# You need to input three parameters, the first is the mysql binary compressed package path (absolute path), such as/root/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz,
# The second is the basedir of mysql, that is, the directory in which the database is to be created, and the third is the set port number.
Filename = $1
Basedir = $2
Port = $3

Groupadd mysql
Useradd-r-g mysql-s/bin/false mysql
Cd/usr/local
Tar zxvf $ filename
Variable fileis The Name Of The mysqlbinary package, for example, mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz
# Dir is the path of the mysql compressed package, excluding the package name itself, such as/root, because the subsequent configuration file my. cnf is also placed under this path
File = 'basename $ filename'
Dir = 'dirname $ filename'

# Get the name after decompression, that is, mysql-5.6.28-linux-glibc2.5-x86_64
After_tar_file =$ {file: 0:-7}
# Change the Binary Package name to mysql + port number, which facilitates further differentiation
Mv $ after_tar_file mysql "$ port"
Cd mysql "$ port"

# Put the original configuration file (which must be in the same-layer directory as the mysql package, in this example/root/my. cnf) copy to the unzipped and renamed mysql binary directory and change it to my + port number. cnf
Cp $ dir/my. cnf./my "$ port". cnf
User_cnf = my "$ port". cnf
# The following describes how to change the path in the original configuration file to a self-defined path, that is, the second parameter passed in.
# The whole challenge is that the input path contains "/", which may cause problems when sed is replaced. All the clever ideas are used, that is, replacing "/" with "| ", replace sed, and then modify "|" back "/" in the file "/"
Basedir_new =$ {basedir /\// |}
Sed-I "s/\/project \/class2/$ basedir_new/g" $ user_cnf
Sed-I "s/| ///g" $ user_cnf

# Set server_id to get the current second value
Server_id = 'date + % s'
Sed-I/^ server_id/s/. */server_id = "$ server_id"/$ user_cnf
# Set the port number
Sed-I/^ port/s/. */port = "$ port"/$ user_cnf

# Create necessary directories and modify permissions
Mkdir-p "$ basedir"/mysql/run/& mkdir-p "$ basedir"/mysql/data & mkdir-p "$ basedir"/mysql/share && \
Mkdir-p "$ basedir"/mysql/log & mkdir-p "$ basedir"/mysql/tmp

Chown-R mysql $ basedir
Chgrp-R mysql $ basedir

# The following is not necessary. For details, refer to the following summary.
Cp share/english/errmsg. sys "$ basedir"/mysql/share/

# -- Force is not necessary during initialization. For details, refer to the following summary.
Scripts/mysql_install_db -- user = mysql -- defaults-file = "$ user_cnf" -- force
Bin/mysqld_safe -- defaults-file = "$ user_cnf" -- user = mysql &

The following provides a reference for the configuration file. You can modify the file based on your actual situation.

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

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

[Client]
Port = 3306
Socket =/project/class2/mysql/run/mysql. sock

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

# Innodb
Innodb_data_home_dir =/project/class2/mysql/data
Innodb_log_group_home_dir =/project/class2/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 = 2
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 = 1000
Innodb_thread_concurrency = 16
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 = inserts
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 = 5
Innodb_rollback_on_timeout = 0
Innodb_purge_threads = 1
Innodb_strict_mode = 1
Transaction-isolation = READ-COMMITTED

# Myisam
Key_buffer = 64 M
Myisam_sort_buffer_size = 64 M
Concurrent_insert = 2
Delayed_insert_timeout = 300

# Replication
Master-info-file =/project/class2/mysql/log/master.info
Relay-log =/project/class2/mysql/log/relaylog
Relay_log_info_file =/project/class2/mysql/log/relay-log.info
Relay-log-index =/project/class2/mysql/log/mysqld-relay-bin.index
Slave_load_tmpdir =/project/class2/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 =/project/class2/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 = utf8
Lower_case_table_names = 1
Skip-external-locking
Open_files_limit = 65536
Safe-user-create
Local-infile = 1
# Sqlmod = "STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE"

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

Summary:

If the following error is reported during initialization:

Fatal error: Neither host 'keepalived02 'nor 'localhost' could be looked up
/Mysql3306/mysql/bin/resolveip
Please configure the 'hostname' command to return a correct
Hostname.
If you want to solve this at a later stage, restart this script
With the -- force option

However, executing the hostname command on the bash terminal does return a value. You can add the -- force parameter as follows:

Scripts/mysql_install_db -- user = mysql -- defaults-file = "$ user_cnf" -- force

If the following error is reported:

[ERROR] Can't find messagefile '/mysql3306/mysql/share/errmsg. sys'

You can COPY the share/english/errmsg. sys file in the binary version to/mysql3306/mysql/share.

This article permanently updates the link address: