MySQL database multi-instance deployment

This article system: rhel5.8

ip:192.168.100.150

Database version: mysql-5.6.15

1, create the deployment MySQL service account:

[[email protected] ~]# useradd-d/opt/mysql mysql[[email protected] ~]# echo "MySQL" |passwd--stdin mysqlchanging passwo Rd for User Mysql.passwd:all authentication tokens updated successfully.

2. Unpack without a package: Configure environment variables

[[Email protected] ~]$ tar zxvf mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz[[email protected] ~]$ MV mysql-5.6.15-linux-glibc2.5-x86_64 mysql-5.6.15[[email protected] mysql-5.6.15]$ vim ~/.bash_profilemysql_home=/opt /mysql/mysql-5.6.15export mysql_homepath= $MYSQL _home/bin: $PATHexport PATH

3. Configure multi-instance database configuration files

[Email protected] ~]# mkdir/data_for_mysql[[email protected] ~]# chown mysql.mysql-r/data_for_mysql/[[email protected ] ~]# Su-mysql[[email protected] ~]$ mkdir-p/data_for_mysql/3301/data/data_for_mysql/3301/logs[[email protected] ~]$ Touch/data_for_mysql/3301/my.cnf[[email protected] ~]$ mkdir-p/data_for_mysql/3302/data/data_for_mysql/3302/logs[ [Email protected] ~]$ TOUCH/DATA_FOR_MYSQL/3302/MY.CNF

Directory structure:

[Email protected] data_for_mysql]$ pwd/data_for_mysql[[email protected] data_for_mysql]$ tree.|   --3301|   |--data|   |--logs| '--my.cnf '--3302 |--data |--logs '--My.cnf6 directories, 2 files[[email protected] data_for_mysql]$

Configure MY.CNF ##### #本配置文件只供参考

[[email protected] 3301]$ vim /data_for_mysql/3301/my.cnf[client]port =  3301socket = /data_for_mysql/3301/data/mysql.sock[mysqld]user=msqlport=3301bind-address= 192.168.100.150socket = /data_for_mysql/3301/data/mysql.sock.3301pid-file = /data_for_ mysql/3301/data/mysql.pidbasedir = /opt/mysql/mysql-5.6.15datadir = /data_for_mysql/3301/ dataserver-id=1log-bin=mysql-binlog-bin-index= mysql-bin.index# logginglog_error=/data_for_mysql/ 3301/logs/mysql-error.logslow_query_log_file= /data_for_mysql/3301/logs/mysql-slow.logslow_query_log= 1character-sets-dir = /opt/mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections  = 1000connect-timeout = 60wait-timeout = 28800net_buffer_length =  16384max_allowed_packet = 64mthread_stack = 192kthread_cache_size = 20thread_ concurrency = 128query_cache_size  = 256mquery_cache_limit = 2mquery_cache_min_res_unit = 2default-time-zone =  systemcharacter-set-server = utf8default-storage-engine = InnoDBtmp_table_size  = 512mmax_heap_table_size = 512mmax_binlog_size = 1gmax_relay_log_size = 1g [Mysql]disable-auto-rehashdefault-character-set = utf8[[email protected] 3301]$ vim  my.cnf[[email protected] 3301]$[[email protected] 3301]$ vim my.cnf[[email  protected] 3301]$[[email protected] 3301]$ cat my.cnf[client]port =  3301socket = /data_for_mysql/3301/data/mysql.sock[mysqld]user=mysqlport=3301bind-address= 192.168.100.150socket = /data_for_mysql/3301/data/mysql.sock.3301pid-file = /data_for_ mysql/3301/data/mysql.pidbasedir = /opt/mysql/mysql-5.6.15datadir = /data_for_mysql/3301/ Dataserver-id=1log-bin=mysql-binlOg-bin-index= mysql-bin.index# logginglog_error=/data_for_mysql/3301/logs/mysql-error.logslow_query_ log_file= /data_for_mysql/3301/logs/mysql-slow.logslow_query_log=1character-sets-dir = /opt/ Mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections = 1000connect-timeout  = 60wait-timeout = 28800net_buffer_length = 16384max_allowed_packet =  64mthread_stack = 192kthread_cache_size = 20thread_concurrency = 128query_ cache_size = 256mquery_cache_limit = 2mquery_cache_min_res_unit =  2default-time-zone = systemcharacter-set-server = utf8default-storage-engine =  Innodbtmp_table_size = 512mmax_heap_table_size = 512mmax_binlog_size = 1gmax_ Relay_log_size = 1g[mysql]disable-auto-rehashdefault-character-set = utf8

4. Initialize the database

[Email protected] scripts]$ Pwd/opt/mysql/mysql-5.6.15/scripts[[email protected] scripts]$./mysql_install_db-- DEFAULTS-FILE=/DATA_FOR_MYSQL/3301/MY.CNF--user=mysql--basedir=/opt/mysql/mysql-5.6.15--datadir=/Data_for_ mysql/3301/data/

5, Start 3301 instance database # # #启动数据库必须在mysql目录;

[Email protected] mysql-5.6.15]$ pwd/opt/mysql/mysql-5.6.15[[email protected] mysql-5.6.15]$ Mysqld_safe-- DEFAULTS-FILE=/DATA_FOR_MYSQL/3301/MY.CNF--user=mysql--basedir=/opt/mysql/mysql-5.6.15--datadir=/Data_for_        mysql/3301/data/&[[email protected] mysql-5.6.15]$ netstat-nat |grep 3301tcp 0 0 192.168.100.150:3301 0.0.0.0:* LISTEN

ps:3302 instance operation as above, just change the port and the corresponding directory;

3302 Configuration files:

[[Email protected] 3302]$ cat my.cnf[client]port = 3302socket = /data_ For_mysql/3302/data/mysql.sock[mysqld]user=mysqlport=3302bind-address=192.168.100.150socket = /data_ for_mysql/3302/data/mysql.sock.3302pid-file = /data_for_mysql/3302/data/mysql.pidbasedir =  /opt/mysql/mysql-5.6.15datadir = /data_for_mysql/3302/dataserver-id=1log-bin= mysql-binlog-bin-index= mysql-bin.index# logginglog_error=/data_for_mysql/3302/logs/ mysql-error.logslow_query_log_file= /data_for_mysql/3302/logs/mysql-slow.logslow_query_log= 1character-sets-dir = /opt/mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections  = 1000connect-timeout = 60wait-timeout = 28800net_buffer_length =  16384max_allowed_packet = 64mthread_stack = 192kthread_cache_size = 20thread_ Concurrency = 128query_cache_size = 256mquery_cache_limit = 2mquery_cache_min_res_unit = 2default-time-zone =  systemcharacter-set-server = utf8default-storage-engine = innodbtmp_table_size =  512mmax_heap_table_size = 512mmax_binlog_size = 1gmax_relay_log_size = 1g[ Mysql]disable-auto-rehashdefault-character-set = utf8

3302 initialization of the instance;

[Email protected] scripts]$ Pwd/opt/mysql/mysql-5.6.15/scripts[[email protected] scripts]$./mysql_install_db-- DEFAULTS-FILE=/DATA_FOR_MYSQL/3302/MY.CNF--user=mysql--basedir=/opt/mysql/mysql-5.6.15--datadir=/Data_for_ mysql/3302/data/

3302 start the instance:

[Email protected] mysql-5.6.15]$ mysqld_safe--defaults-file=/data_for_mysql/3302/my.cnf--user=mysql--basedir=/ opt/mysql/mysql-5.6.15--datadir=/data_for_mysql/3302/data/&[[email protected] mysql-5.6.15]$ Netstat-nat |grep 330tcp 0 0 192.168.100.150:3301 0.0.0.0:* listentcp 0 0 192.168.100.150:3 302 0.0.0.0:* LISTEN

6, create the root password, to distinguish between instances

[Email protected] mysql-5.6.15]$ mysqladmin-uroot password ' root123 '-s/data_for_mysql/3301/data/mysql.sock.3301

6.1, close MySQL, the difference instance

[Email protected] mysql-5.6.15]$ mysqladmin-uroot-proot123-s/data_for_mysql/3301/data/mysql.sock.3301 Shutdownwarning:using a password on the command line interface can is insecure.150402 15:44:52 mysqld_safe mysqld from pi D file/data_for_mysql/3301/data/mysql.pid ended[1]-done Mysqld_safe--defaults-file=/data_for_mysql/ 3301/MY.CNF--user=mysql--basedir=/opt/mysql/mysql-5.6.15--datadir=/data_for_mysql/3301/data/

This article is from the "Big Wind" blog, please be sure to keep this source http://lansgg.blog.51cto.com/5675165/1627717

MySQL database multi-instance deployment