ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > Mysql 5.6 multi instances configuration on redhat 6.2

Mysql 5.6 multi instances configuration on redhat 6.2

原创 MySQL 作者：lovehewenyu 时间：2016-06-23 16:46:15 0 删除 编辑

Mysql 5.6 multi instances configuration on redhat 6.2

1.create mysql user
/usr/sbin/groupadd  -g 601 mysql
/usr/sbin/useradd -u 701 -g mysql mysql

2.create data directory and permissions and attributes
mkdir -p /mysql/data/3307/{data,binlog,tmp}
mkdir -p /mysql/data/3308/{data,binlog,tmp}
mkdir -p /mysql/data/3309/{data,binlog,tmp}
chmod 755 /mysql/data/
chown -R mysql:mysql /mysql/data/

[root@dbdou01 ~]# ls -l /mysql/data/
total 12
drwxr-xr-x. 5 mysql mysql 4096 Jun 23 10:48 3307
drwxr-xr-x. 5 mysql mysql 4096 Jun 23 10:48 3308
drwxr-xr-x. 5 mysql mysql 4096 Jun 23 10:48 3309


3.create {3307,3308,3309} my.cnf
vi /mysql/data/3307/my.cnf

点击( 此处 )折叠或打开

  1. [ client ]
  2. #password = mysql123
  3. port = 3307
  4. socket = / mysql / data / 3307 / mysql . sock
  5. default - character - set = utf8
  6. # Here follows entries for some specific programs

  8. # The MySQL server
  9. [ mysqld ]
  10. ignore - db - dir = lost + found
  11. federated
  12. character - set - server = utf8
  13. collation - server = utf8_bin
  14. port = 3307
  15. socket = / mysql / data / 3307 / mysql . sock
  16. datadir = / mysql / data / 3307
  17. skip - external - locking
  18. lower - case - table - names = 1
  19. #MYISAM
  20. max_connections = 2048
  21. key_buffer_size = 512M
  22. max_allowed_packet = 16M
  23. table_open_cache = 2048
  24. sort_buffer_size = 8M
  25. net_buffer_length = 512K
  26. read_buffer_size = 8M
  27. read_rnd_buffer_size = 16M
  28. myisam_sort_buffer_size = 256M
  29. query_cache_size = 64M
  30. join_buffer_size = 8M
  31. thread_concurrency = 24
  32. thread_cache_size = 8
  33. query_cache_size = 0
  34. query_cache_type = 0
  35. slow_query_log = 1
  36. long - query - time = 1
  37. transaction_isolation = REPEATABLE - READ
  38. bulk_insert_buffer_size = 64M
  39. myisam_max_sort_file_size = 10G
  40. skip - name - resolve
  41. event_scheduler = ON
  42. transaction - isolation = READ - COMMITTED
  43. log_bin_trust_function_creators = 1
  44. #skip - name - resolve

  46. # binary logging is required for replication
  47. log - bin = mysql - bin
  48. expire_logs_days = 1
  49. #binlog_format = ROW
  50. binlog_cache_size = 64M

  52. server - id = 3307

  54. innodb_data_home_dir = / mysql / data / 3307
  55. innodb_data_file_path = ibdata1 : 10M : autoextend
  56. innodb_log_group_home_dir = / mysql / data / 3307
  57. innodb_buffer_pool_size = 200m
  58. innodb_log_file_size = 500M
  59. innodb_log_buffer_size = 16M
  60. innodb_flush_log_at_trx_commit = 1
  61. innodb_lock_wait_timeout = 20
  62. innodb_io_capacity = 2000
  63. innodb_buffer_pool_instances = 3
  64. innodb_purge_threads = 1
  65. innodb_stats_on_metadata = off
  66. innodb_flush_method = O_DIRECT
  67. innodb_file_per_table = true
  68. innodb_print_all_deadlocks = 1

  70. [ mysqldump ]
  71. quick
  72. max_allowed_packet = 16M

  74. [ mysql ]
  75. no - auto - rehash
  76. # Remove the next comment character if you are not familiar with SQL
  77. #safe - updates

  79. [ myisamchk ]
  80. key_buffer_size = 20M
  81. sort_buffer_size = 20M
  82. read_buffer = 2M
  83. write_buffer = 2M
vi /mysql/data/3308/my.cnf

点击( 此处 )折叠或打开

  1. [ client ]
  2. #password = mysql123
  3. port = 3308
  4. socket = / mysql / data / 3308 / mysql . sock
  5. default - character - set = utf8
  6. # Here follows entries for some specific programs

  8. # The MySQL server
  9. [ mysqld ]
  10. ignore - db - dir = lost + found
  11. federated
  12. character - set - server = utf8
  13. collation - server = utf8_bin
  14. port = 3308
  15. socket = / mysql / data / 3308 / mysql . sock
  16. datadir = / mysql / data / 3308
  17. skip - external - locking
  18. lower - case - table - names = 1
  19. #MYISAM
  20. max_connections = 2048
  21. key_buffer_size = 512M
  22. max_allowed_packet = 16M
  23. table_open_cache = 2048
  24. sort_buffer_size = 8M
  25. net_buffer_length = 512K
  26. read_buffer_size = 8M
  27. read_rnd_buffer_size = 16M
  28. myisam_sort_buffer_size = 256M
  29. query_cache_size = 64M
  30. join_buffer_size = 8M
  31. thread_concurrency = 24
  32. thread_cache_size = 8
  33. query_cache_size = 0
  34. query_cache_type = 0
  35. slow_query_log = 1
  36. long - query - time = 1
  37. transaction_isolation = REPEATABLE - READ
  38. bulk_insert_buffer_size = 64M
  39. myisam_max_sort_file_size = 10G
  40. skip - name - resolve
  41. event_scheduler = ON
  42. transaction - isolation = READ - COMMITTED
  43. log_bin_trust_function_creators = 1
  44. #skip - name - resolve

  46. # binary logging is required for replication
  47. log - bin = mysql - bin
  48. expire_logs_days = 1
  49. #binlog_format = ROW
  50. binlog_cache_size = 64M

  52. server - id = 3308

  54. innodb_data_home_dir = / mysql / data / 3308
  55. innodb_data_file_path = ibdata1 : 10M : autoextend
  56. innodb_log_group_home_dir = / mysql / data / 3308
  57. innodb_buffer_pool_size = 200m
  58. innodb_log_file_size = 500M
  59. innodb_log_buffer_size = 16M
  60. innodb_flush_log_at_trx_commit = 1
  61. innodb_lock_wait_timeout = 20
  62. innodb_io_capacity = 2000
  63. innodb_buffer_pool_instances = 3
  64. innodb_purge_threads = 1
  65. innodb_stats_on_metadata = off
  66. innodb_flush_method = O_DIRECT
  67. innodb_file_per_table = true
  68. innodb_print_all_deadlocks = 1

  70. [ mysqldump ]
  71. quick
  72. max_allowed_packet = 16M

  74. [ mysql ]
  75. no - auto - rehash
  76. # Remove the next comment character if you are not familiar with SQL
  77. #safe - updates

  79. [ myisamchk ]
  80. key_buffer_size = 20M
  81. sort_buffer_size = 20M
  82. read_buffer = 2M
  83. write_buffer = 2M
vi /mysql/data/3309/my.cnf

点击( 此处 )折叠或打开

  1. [ client ]
  2. #password = mysql123
  3. port = 3309
  4. socket = / mysql / data / 3309 / mysql . sock
  5. default - character - set = utf8
  6. # Here follows entries for some specific programs

  8. # The MySQL server
  9. [ mysqld ]
  10. ignore - db - dir = lost + found
  11. federated
  12. character - set - server = utf8
  13. collation - server = utf8_bin
  14. port = 3309
  15. socket = / mysql / data / 3309 / mysql . sock
  16. datadir = / mysql / data / 3309
  17. skip - external - locking
  18. lower - case - table - names = 1
  19. #MYISAM
  20. max_connections = 2048
  21. key_buffer_size = 512M
  22. max_allowed_packet = 16M
  23. table_open_cache = 2048
  24. sort_buffer_size = 8M
  25. net_buffer_length = 512K
  26. read_buffer_size = 8M
  27. read_rnd_buffer_size = 16M
  28. myisam_sort_buffer_size = 256M
  29. query_cache_size = 64M
  30. join_buffer_size = 8M
  31. thread_concurrency = 24
  32. thread_cache_size = 8
  33. query_cache_size = 0
  34. query_cache_type = 0
  35. slow_query_log = 1
  36. long - query - time = 1
  37. transaction_isolation = REPEATABLE - READ
  38. bulk_insert_buffer_size = 64M
  39. myisam_max_sort_file_size = 10G
  40. skip - name - resolve
  41. event_scheduler = ON
  42. transaction - isolation = READ - COMMITTED
  43. log_bin_trust_function_creators = 1
  44. #skip - name - resolve

  46. # binary logging is required for replication
  47. log - bin = mysql - bin
  48. expire_logs_days = 1
  49. #binlog_format = ROW
  50. binlog_cache_size = 64M

  52. server - id = 3309

  54. innodb_data_home_dir = / mysql / data / 3309
  55. innodb_data_file_path = ibdata1 : 10M : autoextend
  56. innodb_log_group_home_dir = / mysql / data / 3309
  57. innodb_buffer_pool_size = 200m
  58. innodb_log_file_size = 500M
  59. innodb_log_buffer_size = 16M
  60. innodb_flush_log_at_trx_commit = 1
  61. innodb_lock_wait_timeout = 20
  62. innodb_io_capacity = 2000
  63. innodb_buffer_pool_instances = 3
  64. innodb_purge_threads = 1
  65. innodb_stats_on_metadata = off
  66. innodb_flush_method = O_DIRECT
  67. innodb_file_per_table = true
  68. innodb_print_all_deadlocks = 1

  70. [ mysqldump ]
  71. quick
  72. max_allowed_packet = 16M

  74. [ mysql ]
  75. no - auto - rehash
  76. # Remove the next comment character if you are not familiar with SQL
  77. #safe - updates

  79. [ myisamchk ]
  80. key_buffer_size = 20M
  81. sort_buffer_size = 20M
  82. read_buffer = 2M
  83. write_buffer = 2M

4.initialzation instance
/usr/bin/mysql_install_db --defaults-file=/mysql/data/3307/my.cnf --user=mysql --datadir=/mysql/data/3307
/usr/bin/mysql_install_db --defaults-file=/mysql/data/3308/my.cnf --user=mysql --datadir=/mysql/data/3308
/usr/bin/mysql_install_db --defaults-file=/mysql/data/3309/my.cnf --user=mysql --datadir=/mysql/data/3309


initialzation instance 3307 log

点击( 此处 )折叠或打开

  1. [ root@dbdou01 mysql ] # / usr / bin / mysql_install_db - - defaults - file = / mysql / data / 3307 / my . cnf - - user = mysql - - datadir = / mysql / data / 3307
  2. Installing MySQL system tables . . . 2016 - 06 - 23 11 : 17 : 11 0 [ Warning ] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release .
  3. 2016 - 06 - 23 11 : 17 : 11 0 [ Warning ] TIMESTAMP with implicit DEFAULT value is deprecated . Please use - - explicit_defaults_for_timestamp server option ( see documentation for more details ) .
  4. 2016 - 06 - 23 11 : 17 : 11 0 [ Note ] / usr / sbin / mysqld ( mysqld 5 . 6 . 29 - log ) starting as process 26999 . . .
  5. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Using atomics to ref count buffer pool pages
  6. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : The InnoDB memory heap is disabled
  7. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Mutexes and rw_locks use GCC atomic builtins
  8. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Memory barrier is not used
  9. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Compressed tables use zlib 1 . 2 . 3
  10. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Using Linux native AIO
  11. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Using CPU crc32 instructions
  12. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Initializing buffer pool , size = 200 . 0M
  13. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Completed initialization of buffer pool
  14. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : The first specified data file / mysql / data / 3307 / ibdata1 did not exist : a new database to be
  15. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Setting file / mysql / data / 3307 / ibdata1 size to 10 MB
  16. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Database physically writes the file full : wait . . .
  17. 2016 - 06 - 23 11 : 17 : 11 26999 [ Note ] InnoDB : Setting log file / mysql / data / 3307 / ib_logfile101 size to 500 MB
  18. InnoDB : Progress in MB : 100 200 300 400 500
  19. 2016 - 06 - 23 11 : 17 : 17 26999 [ Note ] InnoDB : Setting log file / mysql / data / 3307 / ib_logfile1 size to 500 MB
  20. InnoDB : Progress in MB : 100 200 300 400 500
  21. 2016 - 06 - 23 11 : 17 : 23 26999 [ Note ] InnoDB : Renaming log file / mysql / data / 3307 / ib_logfile101 to / mysql / data / 3307 / ib_logfile0
  22. 2016 - 06 - 23 11 : 17 : 23 26999 [ Warning ] InnoDB : New log files created , LSN = 45781
  23. 2016 - 06 - 23 11 : 17 : 23 26999 [ Note ] InnoDB : Doublewrite buffer not found : creating new
  24. 2016 - 06 - 23 11 : 17 : 23 26999 [ Note ] InnoDB : Doublewrite buffer created
  25. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : 128 rollback segment ( s ) are active .
  26. 2016 - 06 - 23 11 : 17 : 24 26999 [ Warning ] InnoDB : Creating foreign key constraint system tables .
  27. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : Foreign key constraint system tables created
  28. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : Creating tablespace and datafile system tables .
  29. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : Tablespace and datafile system tables created .
  30. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : Waiting for purge to start
  31. 2016 - 06 - 23 11 : 17 : 24 26999 [ Note ] InnoDB : 5 . 6 . 29 started ; log sequence number 0
  32. 2016 - 06 - 23 11 : 17 : 25 26999 [ Note ] Binlog end
  33. 2016 - 06 - 23 11 : 17 : 25 26999 [ Note ] InnoDB : FTS optimize thread exiting .
  34. 2016 - 06 - 23 11 : 17 : 25 26999 [ Note ] InnoDB : Starting shutdown . . .
  35. 2016 - 06 - 23 11 : 17 : 26 26999 [ Note ] InnoDB : Shutdown completed ; log sequence number 1625984
  36. OK

  38. Filling help tables . . . 2016 - 06 - 23 11 : 17 : 26 0 [ Warning ] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release .
  39. 2016 - 06 - 23 11 : 17 : 26 0 [ Warning ] TIMESTAMP with implicit DEFAULT value is deprecated . Please use - - explicit_defaults_for_timestamp server option ( see documentation for more details ) .
  40. 2016 - 06 - 23 11 : 17 : 26 0 [ Note ] / usr / sbin / mysqld ( mysqld 5 . 6 . 29 - log ) starting as process 27022 . . .
  41. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Using atomics to ref count buffer pool pages
  42. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : The InnoDB memory heap is disabled
  43. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Mutexes and rw_locks use GCC atomic builtins
  44. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Memory barrier is not used
  45. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Compressed tables use zlib 1 . 2 . 3
  46. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Using Linux native AIO
  47. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Using CPU crc32 instructions
  48. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Initializing buffer pool , size = 200 . 0M
  49. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Completed initialization of buffer pool
  50. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Highest supported file format is Barracuda .
  51. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : 128 rollback segment ( s ) are active .
  52. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : Waiting for purge to start
  53. 2016 - 06 - 23 11 : 17 : 26 27022 [ Note ] InnoDB : 5 . 6 . 29 started ; log sequence number 1625984
  54. 2016 - 06 - 23 11 : 17 : 27 27022 [ Note ] Binlog end
  55. 2016 - 06 - 23 11 : 17 : 27 27022 [ Note ] InnoDB : FTS optimize thread exiting .
  56. 2016 - 06 - 23 11 : 17 : 27 27022 [ Note ] InnoDB : Starting shutdown . . .
  57. 2016 - 06 - 23 11 : 17 : 28 27022 [ Note ] InnoDB : Shutdown completed ; log sequence number 1625994
  58. OK

  60. To start mysqld at boot time you have to copy
  61. support - files / mysql . server to the right place for your system

  63. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
  64. To do so , start the server , then issue the following commands :

  66.    / usr / bin / mysqladmin - u root password 'new-password'
  67.    / usr / bin / mysqladmin - u root - h 10 . 124 . 110 . 113 password 'new-password'
  68. ## [ root@dbdou01 mysql ] # / usr / bin / mysqladmin - S / mysql / data / 3307 / mysql . sock - u root password 'mysql123!'
  69. ##Warning : Using a password on the command line interface can be insecure .

  71. Alternatively you can run :

  73.    / usr / bin / mysql_secure_installation

  75. which will also give you the option of removing the test
  76. databases and anonymous user created by default . This is
  77. strongly recommended for production servers .

  79. See the manual for more instructions .

  81. You can start the MySQL daemon with :

  83.   cd / usr ; / usr / bin / mysqld_safe &
  84. ##mysqld_safe - - defaults - file = / mysql / data / 3307 / my . cnf &

  86. You can test the MySQL daemon with mysql - test - run . pl

  88.   cd mysql - test ; perl mysql - test - run . pl

  90. Please report any problems at http : / / bugs . mysql . com /

  92. The latest information about MySQL is available on the web at

  94.   http : / / www . mysql . com

  96. Support MySQL by buying support / licenses at http : / / shop . mysql . com

  98. WARNING : Found existing config file / usr / my . cnf on the system .
  99. Because this file might be in use , it was not replaced ,
  100. but was used in bootstrap ( unless you used - - defaults - file )
  101. and when you later start the server .
  102. The new default config file was created as / usr / my - new . cnf ,
  103. please compare it with your file and take the changes you need .

mysqld_safe --defaults-file=/mysql/data/3308/my.cnf &
/usr/bin/mysqladmin -S /mysql/data/3308/mysql.sock -u root password 'mysql123!'
/usr/bin/mysqladmin -S /mysql/data/3308/mysql.sock -u root -pmysql123! shutdown

mysqld_safe --defaults-file=/mysql/data/3309/my.cnf &
/usr/bin/mysqladmin -S /mysql/data/3309/mysql.sock -u root password 'mysql123!'
/usr/bin/mysqladmin -S /mysql/data/3309/mysql.sock -u root -pmysql123! shutdown

5. mysqld_multi start mysql and create user admin manage mysqld start or stop

vi /etc/my.cnf

点击( 此处 )折叠或打开

  1. [ mysqld_multi ]
  2. mysqld = / usr / bin / mysqld_safe
  3. mysqladmin = / usr / bin / mysqladmin
  4. user = admin
  5. password = password

  7. [ mysqld3307 ]
  8. ignore - db - dir = lost + found
  9. federated
  10. character - set - server = utf8
  11. collation - server = utf8_bin
  12. port = 3307
  13. socket = / mysql / data / 3307 / mysql . sock
  14. datadir = / mysql / data / 3307
  15. skip - external - locking
  16. lower - case - table - names = 1
  17. #MYISAM
  18. max_connections = 2048
  19. key_buffer_size = 512M
  20. max_allowed_packet = 16M
  21. table_open_cache = 2048
  22. sort_buffer_size = 8M
  23. net_buffer_length = 512K
  24. read_buffer_size = 8M
  25. read_rnd_buffer_size = 16M
  26. myisam_sort_buffer_size = 256M
  27. query_cache_size = 64M
  28. join_buffer_size = 8M
  29. thread_concurrency = 24
  30. thread_cache_size = 8
  31. query_cache_size = 0
  32. query_cache_type = 0
  33. slow_query_log = 1
  34. long - query - time = 1
  35. transaction_isolation = REPEATABLE - READ
  36. bulk_insert_buffer_size = 64M
  37. myisam_max_sort_file_size = 10G
  38. skip - name - resolve
  39. event_scheduler = ON
  40. transaction - isolation = READ - COMMITTED
  41. log_bin_trust_function_creators = 1
  42. #skip - name - resolve

  44. # binary logging is required for replication
  45. log - bin = mysql - bin
  46. expire_logs_days = 1
  47. #binlog_format = ROW
  48. binlog_cache_size = 64M

  50. server - id = 3307

  52. innodb_data_home_dir = / mysql / data / 3307
  53. innodb_data_file_path = ibdata1 : 10M : autoextend
  54. innodb_log_group_home_dir = / mysql / data / 3307
  55. innodb_buffer_pool_size = 200m
  56. innodb_log_file_size = 500M
  57. innodb_log_buffer_size = 16M
  58. innodb_flush_log_at_trx_commit = 1
  59. innodb_lock_wait_timeout = 20
  60. innodb_io_capacity = 2000
  61. innodb_buffer_pool_instances = 3
  62. innodb_purge_threads = 1
  63. innodb_stats_on_metadata = off
  64. innodb_flush_method = O_DIRECT
  65. innodb_file_per_table = true
  66. innodb_print_all_deadlocks = 1

  68. [ mysqldump ]
  69. quick
  70. max_allowed_packet = 16M

  72. [ mysql ]
  73. no - auto - rehash
  74. # Remove the next comment character if you are not familiar with SQL
  75. #safe - updates

  77. [ myisamchk ]
  78. key_buffer_size = 20M
  79. sort_buffer_size = 20M
  80. read_buffer = 2M
  81. write_buffer = 2M


  84. [ mysqld3308 ]
  85. ignore - db - dir = lost + found
  86. federated
  87. character - set - server = utf8
  88. collation - server = utf8_bin
  89. port = 3308
  90. socket = / mysql / data / 3308 / mysql . sock
  91. datadir = / mysql / data / 3308
  92. skip - external - locking
  93. lower - case - table - names = 1
  94. #MYISAM
  95. max_connections = 2048
  96. key_buffer_size = 512M
  97. max_allowed_packet = 16M
  98. table_open_cache = 2048
  99. sort_buffer_size = 8M
  100. net_buffer_length = 512K
  101. read_buffer_size = 8M
  102. read_rnd_buffer_size = 16M
  103. myisam_sort_buffer_size = 256M
  104. query_cache_size = 64M
  105. join_buffer_size = 8M
  106. thread_concurrency = 24
  107. thread_cache_size = 8
  108. query_cache_size = 0
  109. query_cache_type = 0
  110. slow_query_log = 1
  111. long - query - time = 1
  112. transaction_isolation = REPEATABLE - READ
  113. bulk_insert_buffer_size = 64M
  114. myisam_max_sort_file_size = 10G
  115. skip - name - resolve
  116. event_scheduler = ON
  117. transaction - isolation = READ - COMMITTED
  118. log_bin_trust_function_creators = 1
  119. #skip - name - resolve

  121. # binary logging is required for replication
  122. log - bin = mysql - bin
  123. expire_logs_days = 1
  124. #binlog_format = ROW
  125. binlog_cache_size = 64M

  127. server - id = 3308

  129. innodb_data_home_dir = / mysql / data / 3308
  130. innodb_data_file_path = ibdata1 : 10M : autoextend
  131. innodb_log_group_home_dir = / mysql / data / 3308
  132. innodb_buffer_pool_size = 200m
  133. innodb_log_file_size = 500M
  134. innodb_log_buffer_size = 16M
  135. innodb_flush_log_at_trx_commit = 1
  136. innodb_lock_wait_timeout = 20
  137. innodb_io_capacity = 2000
  138. innodb_buffer_pool_instances = 3
  139. innodb_purge_threads = 1
  140. innodb_stats_on_metadata = off
  141. innodb_flush_method = O_DIRECT
  142. innodb_file_per_table = true
  143. innodb_print_all_deadlocks = 1

  145. [ mysqldump ]
  146. quick
  147. max_allowed_packet = 16M

  149. [ mysql ]
  150. no - auto - rehash
  151. # Remove the next comment character if you are not familiar with SQL
  152. #safe - updates

  154. [ myisamchk ]
  155. key_buffer_size = 20M
  156. sort_buffer_size = 20M
  157. read_buffer = 2M
  158. write_buffer = 2M


  161. [ mysqld3309 ]
  162. ignore - db - dir = lost + found
  163. federated
  164. character - set - server = utf8
  165. collation - server = utf8_bin
  166. port = 3309
  167. socket = / mysql / data / 3309 / mysql . sock
  168. datadir = / mysql / data / 3309
  169. skip - external - locking
  170. lower - case - table - names = 1
  171. #MYISAM
  172. max_connections = 2048
  173. key_buffer_size = 512M
  174. max_allowed_packet = 16M
  175. table_open_cache = 2048
  176. sort_buffer_size = 8M
  177. net_buffer_length = 512K
  178. read_buffer_size = 8M
  179. read_rnd_buffer_size = 16M
  180. myisam_sort_buffer_size = 256M
  181. query_cache_size = 64M
  182. join_buffer_size = 8M
  183. thread_concurrency = 24
  184. thread_cache_size = 8
  185. query_cache_size = 0
  186. query_cache_type = 0
  187. slow_query_log = 1
  188. long - query - time = 1
  189. transaction_isolation = REPEATABLE - READ
  190. bulk_insert_buffer_size = 64M
  191. myisam_max_sort_file_size = 10G
  192. skip - name - resolve
  193. event_scheduler = ON
  194. transaction - isolation = READ - COMMITTED
  195. log_bin_trust_function_creators = 1
  196. #skip - name - resolve

  198. # binary logging is required for replication
  199. log - bin = mysql - bin
  200. expire_logs_days = 1
  201. #binlog_format = ROW
  202. binlog_cache_size = 64M

  204. server - id = 3309

  206. innodb_data_home_dir = / mysql / data / 3309
  207. innodb_data_file_path = ibdata1 : 10M : autoextend
  208. innodb_log_group_home_dir = / mysql / data / 3309
  209. innodb_buffer_pool_size = 200m
  210. innodb_log_file_size = 500M
  211. innodb_log_buffer_size = 16M
  212. innodb_flush_log_at_trx_commit = 1
  213. innodb_lock_wait_timeout = 20
  214. innodb_io_capacity = 2000
  215. innodb_buffer_pool_instances = 3
  216. innodb_purge_threads = 1
  217. innodb_stats_on_metadata = off
  218. innodb_flush_method = O_DIRECT
  219. innodb_file_per_table = true
  220. innodb_print_all_deadlocks = 1

  222. [ mysqldump ]
  223. quick
  224. max_allowed_packet = 16M

  226. [ mysql ]
  227. no - auto - rehash
  228. # Remove the next comment character if you are not familiar with SQL
  229. #safe - updates

  231. [ myisamchk ]
  232. key_buffer_size = 20M
  233. sort_buffer_size = 20M
  234. read_buffer = 2M
  235. write_buffer = 2M

  237. [ mysqldump ]
  238. quick
  239. max_allowed_packet = 256M
  240. [ mysql ]
  241. no - auto - rehash
  242. prompt = \ \ u @ \ \ d \\R : \ \ m >
  243. [ myisamchk ]
  244. key_buffer_size = 512M
  245. sort_buffer_size = 512M
  246. read_buffer = 8M
  247. write_buffer = 8M
  248. [ mysqlhotcopy ]
  249. interactive - timeout
  250. [ mysqld_safe ]
  251. open - files - limit = 8192
##create user admin
[mysql@dbdou01 ~]$ mysql -uroot -pmysql123! -S /mysql/data/3307/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.29-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none ) 16:00>GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@(none ) 16:00>flush privileges;
Query OK, 0 rows affected (0.00 sec)

##mysqld_multi start
mysqld_multi start 3307-3309
## check mysql port
[root@dbdou01 ~]# netstat -ntlp  | grep mysql
tcp        0      0 :::3307                     :::*                        LISTEN      8935/mysqld        
tcp        0      0 :::3308                     :::*                        LISTEN      8941/mysqld        
tcp        0      0 :::3309                     :::*                        LISTEN      8934/mysqld

6.Boot from the start
vi /etc/init.d/mysql_boot.local
/usr/bin/mysqld_multi start 3307-3309


参考：
http://www.dbdream.com.cn/2016/03/24/%E4%BD%BF%E7%94%A8mysql_install_db%E8%84%9A%E6%9C%AC%E5%88%9D%E5%A7%8B%E5%8C%96mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AE%9E%E4%BE%8B/
http://blog.chinaunix.net/uid-25760152-id-3472603.html
http://blog.csdn.net/leshami/article/details/40339167


########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928 E-mail： dbadoudou@163.com
本文链接： http://blog.itpub.net/26442936/viewspace-2120848/
########################################################################################


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26442936/viewspace-2120848/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 优化之存储过程方法（DBMS_HPROF）
下一篇： Oracle 优化之SQL（清晰的逻辑重构业务SQL）
请登录后发表评论 登录
全部评论
lovehewenyu
专家姓名：何文玉
10年金融行业老鸟。Oracle，Mysql，PostgreSQL，EMC 存储，NBU备份。微信／电话 18211103995

注册时间：2012-02-03

  • 博文量
    307
  • 访问量
    1528536

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593