Shulou(Shulou.com)06/01 Report--

Centos6.6 installs and configures MYSQL database with single machine and multiple instances

The environment for installing stand-alone and multi-instance MYSQL database is as follows:

System platform environment: Centos6.6

Mysql package: Mysql-5.5.32.tar.gz

One: install the dependent packages and compiling software required by MYSQL

# yum install ncurses-devel libaio-devel-y

Second: install the software needed to compile MYSQL (later versions of mysql5.2 will need to use cmake to compile and install MYSQL)

# rpm-ivh cmake-2.8.12.2-4.el6.x86_64 (it will be included on CD-ROM, or you can compile and install it by yourself)

If you are self-compiling and installing cmake, you can # which cmake view the full path so that you can compile and install MYSQL later.

Three: start installing the MYSQL database

1: create a mysql user account

# useradd-s / sbin/nologin-M mysql & & id mysql

2: get the source code package of MYSQL (if the connection fails, you can find the corresponding link by yourself)

# wget http://dev.mysql.com/downloads/mysql

3: install MYSQL by compiling

# tar xf mysql-5.5.32.tar.gz

# cd mysql-5.5.32

# cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32

-DMYSQL_DATADIR=/application/mysql-5.5.32/data

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii

-DENABLED_LOCAL_INFILE=ON-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_FAST_MUTEXES=1

-DWITH_ZLIB=bundled-DENABLED_LOCAL_INFILE=1

-DWITH_READLINE=1

-DWITH_EMBEDDED_SERVER=1

-DWITH_DEBUG=0

# make & & make install

# ln-s / application/mysql-5.5.32/ / application/mysql

At this point, the MYSQL-5.5.32 source code package is installed in cmake even if it is successful!

Since we are configuring and installing a stand-alone multi-instance database, the next thing we need to do is to create the necessary data files and configuration files for each instance. And startup files.

Four: instance creation of the necessary data files, configuration files. And startup files.

1: create a data directory for each instance

# mkdir-p / data/ {3306 recorder 3307} / data

2: create a configuration file for each instance (multiple profile templates provided by default in ls-l / application/mysql/suport-files/my*.cnf database, which can be selected according to the server hardware configuration)

# vim / data/3306/my.cnf (configuration example is as follows: # is a comment line)

[client] port = 3306 socket = / data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] port = 3306 user = mysql socket = / data/3306/mysql.sock basedir = / application/mysql datadir = / data/3306/data back_log = 100max_connections = 800max_connect_errors = 100table_open_cache = 2048 external-locking = FALSE binlog_cache_size = 1m max_heap_table_size = 2m read_buffer_size = 2m read_rnd_buffer_size = 2m sort_buffer_size = 2m join_buffer_size = 1m Thread_cache_size = 100thread_concurrency = 8 query_cache_size = 2m query_cache_limit = 2m # ft_min_word_len = 4 # default-storage-engine = InnoDB thread_stack = 19K # transaction_isolation = REPEATABLE-READ tmp_table_size = 2m # binlog_format=mixed # slow_query_log long_query_time = 2 Pid-file = / data/3306/mysqld.pid relay-log = / data/3306/relay-bin relay-log-info-file = / data/3306/relay-log.info max_binlog_cache_size = 1m max_binlog_size = 2m key_buffer_size = 16m read_buffer_size = 1m read_rnd_buffer_size = 1m lower_case_table_names = 1 Skip-name-resolve slave-skip-errors = 1032 replicate-ignore-db = mysql bulk_insert_buffer_size = 1m server-id = 1 # log-bin = / data/3306/logbin/mysql-bin # log-bin-index = / data/3306/logbin/log-bin.index # myisam_sort_buffer_size = 128m # myisam_max_sort_file_size = 10G # myisam_ Repair_threads = 1 # myisam_recover # skip-innodb innodb_additional_mem_pool_size = 16m innodb_buffer_pool_size = 32m innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 8 # innodb_read_io_threads = 8 # innodb_force_recovery=1 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2m innodb_log_file_size = 24m innodb_log_files_in_group = 3 # innodb_log_group_home_dir innodb_max_dirty_pages_pct = 90 # innodb_flush_method=O_DSYNC innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max _ allowed_packet = 5m [mysqld_safe] log-error=/data/3306/mysql_niutianwen3306.err pid-file=/data/3306/mysqld.pid

# cp / data/3306/my.cnf / data/3307/my.cnf

# sed's Universe 3306 / data/3307/my.cnf

3: create a startup file for multiple instances of MYSQL

# vim / data/3306/mysqld (configuration example of startup script is shown below for reference)

#! / bin/bash # this scripts is created by niutianwen at 2016-10-20 # site: www.niutianwen.com # # port=3306 mysql_user= "root" mysql_pwd= "niutianwen" # CmdPath= "/ application/mysql/bin" mysql_sock= "/ data/$ {port} / mysql.sock "txtfile=" / data/$ {port} / txt "declare-I value # startup function function_start_mysql () {if [!-e" $mysql_sock "] Then printf "Starting MYSQL...\ n" / bin/sh ${CmdPath} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null & echo > ${txtfile} sleep 10 value= `netstat-tnlp | grep ": $port" | awk'{print $4}'| awk-favored print'{print $NF}'`# echo $value If ["${value}"-eq "${port}"] Then printf "MYSQL is up.!\ n" else printf "MYSQL is not startup.!\ n" fi else printf "MYSQL is runing...\ n" exit fi} # stop function function_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MYSQL is stopped...\ n" exit else printf "Stoping MYSQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdown & & echo "MYSQL is down.!" Fi} # restart function function_restart_mysql () {printf "Restarting NYSQL...\ n" function_stop_mysql sleep 2 function_start_mysql} case $1 in start) function_start_mysql;; stop) function_stop_mysql;; restart) function_restart_mysql *) printf "Usage:/data/$ {port} / mysql {start | stop | restart}\ n" esac

# cp / data/3306/mysqld / data/3307/mysqld

# sed's Universe 3306 / data/3307/mysqld

So far, the data files, configuration files, and startup files of the two instances have all been configured.

Fifth: configure file permissions for multiple instances of MYSQL

1: authorize mysql users to manage the root directory / data of the entire multi-instance

# chown-R mysql.mysql / data

2: because the password information of the database is in the startup file, 700 permissions should be set for the best.

# find / data-name mysqld | xargs chmod 700

Six: add the Mysql command to the global path for ease of use.

# echo 'export PATH=/application/mysql/bin:$PATH' > > / etc/profile

# source / etc/profile (effective directly)

# echo $PATH (check whether the configuration is successful)

Initialize the database file of multiple instances of MYSQL (the essence of initializing the database is to create the library file of the basic database)

# cd / application/mysql/scripts

#. / mysql_install_db-basedir=/application/mysql-datadir=/data/3306/data-user=mysql

#. / mysql_install_db-basedir=/application/mysql-datadir=/data/3307/data-user=mysql

Eight: start MYSQL multi-instance database

# / data/3306/mysqld start (stop | restart)

Nine: configure MYSQL database to boot automatically

# echo "# mysql multi Instances" > > / etc/rc.local

# echo "/ data/3306/mysql start" > > / etc/rc.local

# echo "/ data/3307/mysql start" > > / etc/rc.local

Ten: login test and security configuration (set password for mysql login)

# mysql-S / data/3306/mysql.sock

# mysqladmin-uroot-S / data/3306/mysql.sock password 'niutianwen'

At this point, the configuration of dual instances of stand-alone MYSQL has been completed! To add more MYSQL instances, just repeat the above process! Thank you. Focus on the road to linux so that everyone is not alone!

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.