Mysql 5.6 multi instances configuration on redhat 6.2

最新推荐文章于 2024-08-26 16:32:13 发布
最新推荐文章于 2024-08-26 16:32:13 发布
阅读量771 收藏
点赞数
文章标签： 数据库 网络
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博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-2120848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-2120848/

cqrw65623
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
[MySQL 5.6] 5.6新参数 （完全版）
帅的数说
02-09 1185
http://mysqllover.com/?p=575 以下列出了MySQL 5.6的一些新参数 || 老参数新功能，有一些的说明只是参照的文档，还没有从代码求证。 这些参数的分类也显示了5.6在不同层面的改进(or regression?) 不定期更新Ing… ///////////////////////////////////////// Serv
MySQL学习系列(2) -- MySQL数据库生产环境的安装和升级
胖熊猫的窝
07-13 5994
=================================================================================================== 0. summary       1. 生产环境如何安装mysql     .  1.1 innodb_log_file_size说明     .  1.2 使用编辑的my.cnf安装5.6
26. 连接器
蜗牛的彩色生活专栏
09-05 6192
26.1. MySQL Connector/ODBC 26.1.1. MyODBC介绍26.1.2. 关于ODBC和MyODBC的一般信息26.1.3. 如何安装MyODBC26.1.4. 在Windows平台上从二进制版本安装MyODBC26.1.5. I在U
sqoop使用指南
01-14 1401
Sqoop User Guide (v1.4.6) 1.Introduction Sqoop is a tool designed to transfer data between Hadoop and relational databases or mainframes. You can use Sqoop to import data fr...
【安装篇】- 基于 VMWARE Oracle Linux7.9 安装 Oracle19c RAC 详细配置方案
热门推荐
JiekeXu的博客
08-11 1万+
作者 | yanwei来源 | 墨天轮 https://www.modb.pro/db/95684大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Linux7.9...
MySQL 5.6 & 5.7最优配置文件模板(my.ini)
09-10
MySQL的配置文件(my.ini)是数据库性能调优的关键部分，对于MySQL 5.6和5.7这两个版本，优化配置尤为重要。配置文件模板提供了一种基础设置，可以根据服务器的具体情况进行微调，以达到最佳性能。以下是一些重要的...
mysql 释放内存_Mysql5.6启动内存占用过高解决方案
weixin_36125467的博客
01-18 1030
最近把MySQL升级到了5.6版本，无意中在任务管理器发现MySQL内存占用达到400+M，这是怎么回事呢？以前的版本可没有遇到过这种情况啊。开始的时候还以为是缓存了某个数据量较大的表的数据。于是重启MySQL服务，但结果出乎意料，MySQL服务一起启动，内存就又飙升到400+M。心想，这肯定不是缓存的那个表数据的问题了。接下来就是寻找解决MySQL 5.6 内存占用过高这个问题的解决方案了。 经...
docker 安装 mysql5.6.51 （MySQL 5.7 同理）使用笔记
zyklbr的博客
11-30 415
拉取镜像（image） docker pull mysql:5.6.51 root@ecs-fxbzf:~# docker pull mysql:5.6.51 5.6.51: Pulling from library/mysql 89166b5eeae4: Pull complete 73631a7ee13b: Pull complete f287d5f3290c: Pull complete 0a24ae08d840: Pull complete 4b749bd83c0e: Pull complete
MySQL 5.6中备份及还原performance_schema配置
08-02
在MySQL 5.6版本中，`performance_schema`是一个重要的系统表集合，它提供了数据库性能监控和分析的能力。这个表空间记录了各种数据库操作的详细信息，如查询、锁、线程和内存使用等，帮助管理员优化数据库性能。这...
修复数据库中的 “Access Denied: SUPER Privilege Required” 错误
最新发布
xiaochong0302的博客
08-26 240
当您使用数据库时，您可能会看到错误消息：“Access denied; you need (at least one of) the SUPER privilege(s) for this operation”。当您的数据库用户没有足够的权限来执行某些操作时，就会发生这种情况。
数据库查询大量数据避免内存溢出的方法
huan1213858的博客
08-23 228
每次查询一定数量数据之后记录id，进行数据处理之后再继续查询继续处理，
汉服文化平台网站
heye0910032的博客
08-26 270
本文主要探讨了使用Java语言开发汉服文化平台网站的全过程。系统采用B/S架构，严格遵循软件开发流程，从分析、设计到实现，确保了项目的系统性和科学性。系统主要面向管理员和用户两大类使用者，提供了包括首页、个人中心、汉服知识管理、服装展示管理、用户相册管理、论坛交流、系统管理、订单管理等功能。本系统的应用，旨在实现汉服文化信息管理的信息化，提高管理效率，方便用户访问和交流。通过本毕业设计，我深入学习并实践了Java语言和SSM框架在Web开发中的应用。
Android架构组件：MVVM模式的实战应用
m0_66995023的博客
08-22 404
在 Android 开发中，MVVM（Model-View-ViewModel）模式是一种非常流行的架构模式，它可以帮助开发者更好地组织代码，使得应用程序更加模块化、易于维护。用途：在 ViewModel 中使用，当数据发生变化时通知 View 更新。职责：作为 Model 和 View 之间的桥梁，处理 UI 相关的逻辑。实例：持有数据和 UI 相关的状态，处理用户输入，更新 UI 等。用途：实现 View 和 ViewModel 之间的双向绑定。特点：简化 UI 更新逻辑，减少手动更新 UI 的代码。
ABAP 程序间传递数据
weixin_45879627的博客
08-21 975
ABAP 程序间传递数据
linux上datax 安装以及使用
寂夜了无痕的博客
08-24 965
linux上datax 安装以及使用
2019 下午 CLI举例：通过ISAKMP方式建立GRE over IPsec隧道
qq_25467441的博客
08-23 243
http://127.0.0.1:51299/icslite/hdx.do?docid=HDXAZN01177_03_zh#topicid=ZH-CN_TASK_0000001555595308
谷粒商城实战笔记-236~238-商城业务-购物车-环境搭建
epitomizelu的专栏
08-22 915
这一节的内容主要分析购物的业务逻辑和设计存储购物车的选型及数据结构。①把整个购物车封装为一个对象CartVo。
【C#】【EXCEL】Bumblebee/Classes/ExShape.cs
hmywillstronger的博客
08-25 788
这些方法和属性共同构成了一个强大的接口,用于在C#中操作Excel形状,特别是SmartArt对象。它们展示了如何与Excel的COM对象交互,修改形状的各种属性,如颜色、线条粗细和布局。这个类的设计考虑到了易用性和灵活性,允许开发者精确控制Excel中的图形元素。这个方法重写了Object类的ToString方法,提供了ExShape对象的自定义字符串表示。当然,我会为这段代码添加详细的中文注释,以便于您的学习和博客发表。这个方法设置形状轮廓的颜色。它同时设置线条的前景色和背景色,以确保一致的颜色效果。
MySQL高并发性能优化：MDL锁与metadata_locks_hash_instances解析
- **表缓存优化**: 通过调整`table_open_cache_instances`参数来增加缓存大小，可以减少表打开的开销，提高系统响应速度。 3. **性能模式（Performance Schema）**： - **性能模式作用**: 提供数据库性能监控和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值