1, create a multi-instance directory: Mkdir-p/data/mysql/mysql_3307/{data,tmp,logs}mkdir-p/data/mysql/mysql_3308/{data,tmp,logs} 2, Modify the MY.CNF configuration file vi/data/mysql/mysql_3306/my3306.cnf #my. Cnf[client]port = 3306socket =/data/mysql/mysql_3306/tmp/mysql_3306.sock[mysql]prompt=[3306]> #tee =/data/mysql/mysql_3306/data/query.logno-auto-rehash [mysqld]user = MySQLBasedir =/usr/local/mysqlDataDir =/data/mysql/mysql_3306/dataPort = 3306socket =/data/mysql/mysql_3306/tmp/mysql_3306.sockEvent_scheduler = 0 #timeoutinteractive_timeout = 300wait_timeout = #character Setcharacter-set-server = UTF8 #open_fi Les_limit = 65535max_connections = 100max_connect_errors = 100000 Skip-name-resolve = 1#logslog-output=fileslow_query_ Log = 1slow_query_log_file = Slow.loglog-error =/data/mysql/mysql_3306/data/error.loglog_warnings = 2pid-file = Mysql.pidlong_query_time = 1#log-slow-admin-statements = 1#log-queries-not-using-indexes = 1log-slow-slave-statements = 1 #binlogbinlog_format = Mixedserver-id = 203306Log-bin =/data/mysql/mysql_3306/logs/mybinlogBinlog_cache_size = 4mmax_binlog_size = 1gmax_binlog_cache_size = 2gsync_binlog = 0expire_logs_days = Ten #relay Logskip_sl Ave_start = 1max_relay_log_size = 1grelay_log_purge = 1relay_log_recovery = 1log_slave_updates#slave-skip-errors= 1032,1053,1062 explicit_defaults_for_timestamp=1#buffers & cachetable_open_cache = 2048table_definition_cache = 2048table_open_cache = 2048max_heap_table_size = 96msort_buffer_size = 2mjoin_buffer_size = 2Mthread_cache_size = 256query_cache_size = 0query_cache_type = 0query_cache_limit = 256kquery_cache_min_res_unit = 512thread_stack = 192Ktmp_ Table_size = 96mkey_buffer_size = 8mread_buffer_size = 2mread_rnd_buffer_size = 16mbulk_insert_buffer_size = 32M #myisamm Yisam_sort_buffer_size = 128mmyisam_max_sort_file_size = 10gmyisam_repair_threads = 1 #innodbinnodb_buffer_pool_size = 100minnodb_buffer_pool_instances = 1Innodb_data_file_path = Ibdata1:1g:autoextendInnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 64Minnodb_log_file_size = 256MInnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 50innodb_file_per_table = 1innodb_rollback_on_ Timeoutinnodb_status_file = 1innodb_io_capacity = 2000transaction_isolation = Read-committedinnodb_flush_method = O_ Direct [mysqld_safe]open_file_limit = 65535 3, copying the configuration file to a different instance of the directory: Cp/data/mysql/mysql_ 3306/my3306.cnf/data/mysql/mysql_3307/my3307.cnfcp/data/mysql/mysql_3306/my3306.cnf/data/mysql/mysql_3308/ My3308.cnf 4, modifying the directory path of other instances Vi/data/mysql/mysql_3307/my3307.cnf:%s/3306/3307/g vi/data/mysql /mysql_3308/my3308.cnf:%s/3306/3308/g 5, granting permissions: Chown-r mysql:mysql/usr/local/mysqlchown-r mysql: Mysql/datachown-r mysql:mysql/opt/mysql 6, multi-instance initialization: CD /usr/local/mysql./scripts/mysql_ install_db--user=mysql--datadir=/data/mysql/mysql_3306/data--defaults-file=/data/mysql/mysql_3306/my3306.cnf./ scripts/mysql_install_db--user=mysql--datadir=/data/mysql/mysql_3307/data--defaults-file=/data/mysql/mysql_3307/my3307.cnf./scripts/mysql_install_db--user=mysql--datadir=/data/mysql/mysql_3308/data--defaults-file=/data /mysql/mysql_3308/my3308.cnf 7, boot process:/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql/ Mysql_3306/my3306.cnf &/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql/mysql_3307/ My3307.cnf &/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/mysql/mysql_3308/my3308.cnf & 8, to see if the specified file directory exists: (see each instance before it starts) ll/data/mysql/mysql_3306/tmpsrwxrwxrwx 1 mysql mysql 0 Dec 18 07:08 mysql_3306.sock ll/data/mysql/mysql_3307/tmpsrwxrwxrwx 1 mysql mysql 0 Dec 07:10 mysql_3307.sock ll/data/ mysql/mysql_3308/tmpsrwxrwxrwx 1 mysql mysql 0 Dec 07:22 mysql_3308.sock 9, multi-instance landing mode: MySQL--protocol=tcp- H 127.0.0.1-uroot-p3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sockmysql--protocol=tcp-h 127.0.0.1- uroot-p3307 --socket=/data/mysql/mysql_3307/tmp/mysql_3307.sockmysql--protocol=tcp-h 127.0.0.1-uroot-p3308 --socket=/data/mysql/mysql_3308/tmp/mysql_3308.sock 10, Close Mysql:mysqladmin-s /data/mysql/mysql_3308/tmp/mysql_3306.sock shutdownmysqladmin-s /data/mysql/mysql_3308/tmp/mysql_3307.sock shutdownmysqladmin-s /data/mysql/mysql_3308/ tmp/mysql_3308.sock shutdown =============================================== = = Problems in the test process: 1, would like to create multiple instances in a configuration file, but at the time of initialization found error-log,bin-log,socket these files can not be created in the specified directory. 2, the configuration parameters are as follows: You can also do multi-instance configuration under/ETC/MY.CNF, add the following at the bottom of the configuration file: #端口号为3306的实例特殊配置 [Mysqld3306]port =3306server-id=2003306# Specify the Basedir and datadirbasedir= of the corresponding version of this instance/usr/local/mysqldatadir =/data/mysql/mysql_3306/datalog-bin =/data/mysql/mysql_ 3306/logs/mybinloglog-error =/data/mysql/mysql_3306/data/error.logsocket =/data/mysql/mysql_3306/tmp/mysql_3306. Sockpid-file = /data/mysql/mysql_3306/tmp/mysql3306.pid# Reconfigure these options, not as the global configuration, directly overrides the global settings above Innodb_buffer_pool_ Size = 100mtransaction_isolation = Repeatable-read [mysqld3307]port=3307server-id=2003307#binlog-do-db=db01basedir=/usr/local/mysqldatadir =/data/mysql/ Mysql_3307/datalog-bin =/data/mysql/mysql_3307/logs/mybinloglog-error =/data/mysql/mysql_3307/data/ Error.logsocket =/data/mysql/mysql_3307/tmp/mysql_3307.sockpid-file = /data/mysql/mysql_3307/tmp/ mysql3307.pid# reconfigure these options, not the same as the global configuration, will directly overwrite the above global settings innodb_buffer_pool_size = 100minnodb_flush_log_at_trx_commit = 2sync_ Binlog = 0 [mysqld3308]port=3308server-id=2003308#binlog-do-db=db01basedir=/usr/local/mysqldatadir =/data/ Mysql/mysql_3308/datalog-bin =/data/mysql/mysql_3308/logs/mybinloglog-error =/data/mysql/mysql_3308/data/ Error.logsocket =/data/mysql/mysql_3308/tmp/mysql_3308.sockpid-file = /data/mysql/mysql_3308/tmp/ mysql3308.pid# reconfigure these options, not the same as the global configuration, will directly overwrite the above global settings innodb_buffer_pool_size = 100minnodb_flush_log_at_trx_commit = 2sync_ Binlog = 0 3, the great God passing can be pointed out.
MySQL Multi-instance configuration
