centos6.5 mysql5.6.24 单机多实例安装

单实例安装请参照该文档，本文在此基础上进行安装

多实例部署优势：

• 充分利用系统资源
  
  • mysql为单进程多线程的模型，它对多核的利用不是很好，无法充分利用系统资源。所以服务器上可以考虑多实例部署
• 资源隔离
  
  • 如果不同的业务，部署在一个实例里，那么连接数、缓存等资源都是共享的。如果某个业务压力很大的话，很可能影响另一个业务的正常运行
• 业务、模块隔离
  
  • 例如，A业务需要支持移动端，那么就需要升级数据库以支持utf8mb4字符集。而B业务则不需要。如果A、B是部署在一起的话，升级数据库必然会对B的业务造成影响

安装过程

复制已有的配置文件

# pwd/etc/mysql# cp -p mysql3306.cnf mysql3307.cnf # lltotal 8-rw-r--r--. 1 mysql mysql 2368 Jul 19 06:59 mysql3306.cnf-rw-r--r--. 1 mysql mysql 2368 Jul 19 06:59 mysql3307.cnf

修改配置文件

# sed -i '1,$s/3306/3307/g' mysql3307.cnf # more mysql3307.cnf [mysqld]# GENERAL #user = mysqlport = 3307socket = /data1/db3307/my3307.sockpid_file = /data1/db3307/mysql.piddatadir = /data1/db3307/tmpdir = /data1/tmplog_bin = /data1/db3307/3307-mysql-binrelay-log = /data1/db3307/3307-relay-binlog_error = /data1/db3307/error.logslow_query_log_file = /data1/db3307/slow-queries.loglong_query_time=1sync_binlog = 0expire_logs_days = 7back_log=1024skip-name-resolveskip-slave-startskip-external-lockingskip-character-set-client-handshakeexplicit_defaults_for_timestamp=truedefault_storage_engine = InnoDBbind-address=0.0.0.0#lower_case_table_names  = 0myisam_recover = FORCE,BACKUPtransaction-isolation = READ-COMMITTEDtable_definition_cache = 4096table_open_cache = 4096# connection #max_connections = 1100max_user_connections = 1000max_connect_errors = 1000# timeout #wait_timeout = 100interactive_timeout = 100lock_wait_timeout = 3connect_timeout = 20slave-net-timeout = 30# character # character-set-server=utf8init-connect='SET NAMES utf8'# disabled query cache #query_cache_type = 0query_cache_size = 0# replication #server_id=71493307gtid_mode=ONenforce-gtid-consistencylog-slave-updatesbinlog-format=rowslave-parallel-workers=6master-info-repository=TABLErelay-log-info-repository=TABLEsync_master_info = 10000slave_sql_verify_checksum=1skip-slave-start# session #key_buffer_size = 128Mtmp_table_size = 32Mmax_heap_table_size = 32Mmax_allowed_packet = 32Mbulk_insert_buffer_size = 32Msort_buffer_size = 128Kread_buffer_size = 1Mread_rnd_buffer_size = 1Mjoin_buffer_size = 128Kmyisam_sort_buffer_size = 32Mtmp_table_size = 32Mmax_heap_table_size = 64Mthread_cache_size = 64#thread_concurrency = 32thread_stack = 192K# INNODB #innodb_flush_method = O_DIRECTinnodb_data_home_dir = /data1/db3307/innodb_data_file_path = ibdata1:10M:autoextend#redo loginnodb_log_group_home_dir=/data1/db3307/innodb_log_files_in_group = 3innodb_log_file_size = 1G#innodb performanceinnodb_flush_log_at_trx_commit = 0innodb_file_per_table = 1innodb_buffer_pool_instances = 8innodb_io_capacity = 2000innodb_lock_wait_timeout = 30binlog_error_action = ABORT_SERVERinnodb_buffer_pool_size = 256Minnodb_max_dirty_pages_pct=90innodb_file_format=Barracuda innodb_support_xa = 0innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_load_at_startup = 1

创建相关目录，修改目录拥有者

# mkdir -p /data1/db3307# chown -R mysql:mysql /data1/db3307

实例初始化

# ./mysql_install_db --basedir=/usr/local/mysql --defaults-file=/etc/mysql/mysql3307.cnf Installing MySQL system tables...2016-07-19 08:49:04 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 5438 ...OKFilling help tables...2016-07-19 08:50:33 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 5484 ...OK......# pwd/data1/db3307# lltotal 3222684-rw-rw----. 1 mysql mysql      65420 Jul 19 08:50 3307-mysql-bin.000001-rw-rw----. 1 mysql mysql    1099687 Jul 19 08:50 3307-mysql-bin.000002-rw-rw----. 1 mysql mysql         72 Jul 19 08:50 3307-mysql-bin.index-rw-rw----. 1 mysql mysql       5077 Jul 19 08:50 error.log-rw-rw----. 1 mysql mysql        962 Jul 19 08:50 ib_buffer_pool-rw-rw----. 1 mysql mysql   77594624 Jul 19 08:50 ibdata1-rw-rw----. 1 mysql mysql 1073741824 Jul 19 08:50 ib_logfile0-rw-rw----. 1 mysql mysql 1073741824 Jul 19 08:50 ib_logfile1-rw-rw----. 1 mysql mysql 1073741824 Jul 19 08:50 ib_logfile2drwx------. 2 mysql mysql       4096 Jul 19 08:50 mysqldrwx------. 2 mysql mysql       4096 Jul 19 08:50 performance_schemadrwx------. 2 mysql mysql       4096 Jul 19 08:49 test

启动实例

# mysqld_safe --defaults-file=/etc/mysql/mysql3307.cnf  &

查看相关进程

# ps -ef |grep mysqlroot       3551   2818  0 07:39 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/mysql3306.cnfmysql      4604   3551  0 07:39 pts/1    00:00:04 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/mysql3306.cnf --basedir=/usr/local/mysql --datadir=/data1/db3306/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data1/db3306/error.log --pid-file=/data1/db3306/mysql.pid --socket=/data1/db3306/my3306.sock --port=3306root       4630   2818  0 07:40 pts/1    00:00:00 mysql -uroot --socket=/data1/db3306/my3306.sockroot       5513   5341  0 08:53 pts/3    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/mysql3307.cnfmysql      6566   5513 15 08:53 pts/3    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/mysql3307.cnf --basedir=/usr/local/mysql --datadir=/data1/db3307/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data1/db3307/error.log --pid-file=/data1/db3307/mysql.pid --socket=/data1/db3307/my3307.sock --port=3307

账号安全设置

mysql> select user,host,password from mysql.user;+------+-----------+----------+| user | host      | password |+------+-----------+----------+| root | localhost |          || root | miles     |          || root | 127.0.0.1 |          || root | ::1       |          ||      | localhost |          ||      | miles     |          |+------+-----------+----------+6 rows in set (0.01 sec)mysql> delete from mysql.user where user='';Query OK, 2 rows affected (0.01 sec)mysql> delete from mysql.user where host <>'localhost';Query OK, 3 rows affected (0.00 sec)mysql> select user,host,password from mysql.user;+------+-----------+----------+| user | host      | password |+------+-----------+----------+| root | localhost |          |+------+-----------+----------+1 row in set (0.00 sec)mysql> set password for root@'localhost' = password('XXXXXX');Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) 可以看到test数据库存在安全隐患，任何用户都可以在test进行操作mysql> select * from mysql.db \G*************************** 1. row ***************************                 Host: %                   Db: test                 User:           Select_priv: Y          Insert_priv: Y          Update_priv: Y          Delete_priv: Y          Create_priv: Y            Drop_priv: Y           Grant_priv: N      References_priv: Y           Index_priv: Y           Alter_priv: YCreate_tmp_table_priv: Y     Lock_tables_priv: Y     Create_view_priv: Y       Show_view_priv: Y  Create_routine_priv: Y   Alter_routine_priv: N         Execute_priv: N           Event_priv: Y         Trigger_priv: Y*************************** 2. row ***************************                 Host: %                   Db: test\_%                 User:           Select_priv: Y          Insert_priv: Y          Update_priv: Y          Delete_priv: Y          Create_priv: Y            Drop_priv: Y           Grant_priv: N      References_priv: Y           Index_priv: Y           Alter_priv: YCreate_tmp_table_priv: Y     Lock_tables_priv: Y     Create_view_priv: Y       Show_view_priv: Y  Create_routine_priv: Y   Alter_routine_priv: N         Execute_priv: N           Event_priv: Y         Trigger_priv: Y2 rows in set (0.00 sec)mysql> delete from mysql.db;Query OK, 2 rows affected (0.00 sec)mysql> drop database test;Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)