Mysql 5.6 multi instances configuration on redhat 6.2

最新推荐文章于 2025-08-15 10:38:51 发布
最新推荐文章于 2025-08-15 10:38:51 发布
阅读量808 收藏
点赞数
文章标签： 数据库 网络
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.youkuaiyun.com/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
关注
MySQL学习系列(2) -- MySQL数据库生产环境的安装和升级
胖熊猫的窝
07-13 6080
=================================================================================================== 0. summary       1. 生产环境如何安装mysql     .  1.1 innodb_log_file_size说明     .  1.2 使用编辑的my.cnf安装5.6
【MySQL】windows安装MySQL 5.6
tissar的博客
09-01 943
【MySQL】windows安装MySQL 5.6一、下载MySQL二、安装MySQL三、添加环境变量四、检查文件夹五、配置文件配置文件优先级六、安装MySQL服务查看mysqld使用说明安装Windows服务（管理员权限）启动服务（管理员权限）测试连接七、修改root密码mysqladmin使用说明mysqladmin修改密码 如果直接安装官网的安装包（MySQL Community Server），会连带一些用不上的软件。 一、下载MySQL http://mirrors.sohu.com：mysq
26. 连接器
蜗牛的彩色生活专栏
09-05 7858
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 1601
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...
sqoop-使用文档
扛着锄头的码农
08-12 1913
该项目已退役。详情请参阅其 阁楼页面。 文档主页 Sqoop 用户指南 (v1.4.7) Sqoop 用户指南 (v1.4.7) 目录 一、简介 2. 支持的版本 3. Sqoop 发布 4. 先决条件 5. 基本用法 6. Sqoop 工具 6.1. 使用命令别名 6.2. 控制 Hadoop 安装 6.3. 使用通用参数和特定参数 6.4. 使用选项文件传递参数 6.5. 使用工具 7. sqoop-import 7.1. 目的 7.2. 句法 7.2.1. 连接到数据库服务器 7.2.2. 选择要导入
【安装篇】- 基于 VMWARE Oracle Linux7.9 安装 Oracle19c RAC 详细配置方案
热门推荐
JiekeXu的博客
08-11 1万+
作者 | yanwei来源 | 墨天轮 https://www.modb.pro/db/95684大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Linux7.9...
LVS-HOWTO
fei的专栏
09-19 1751
原文链接请点击 LVS-HOWTO Joseph Mack jmack (at) wm7d (dot) net  Copyright © 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012 Joseph Mack v
Sqoop User Guide (v1.4.6)
Success relies on perseverance
12-24 1745
Sqoop User Guide (v1.4.6) Table of Contents 1. Introduction2. Supported Releases3. Sqoop Releases4. Prerequisites5. Basic Usage6. Sqoop Tools 6.1. Using Command Aliases6.2. Controlling the Hado
Docker的诱惑
weixin_50062453的博客
10-20 205
文章目录1、Docker简介1.1、Docker为什么会出现？1.2、Docker理念1.3、是什么？2、Docker能干什么？2.1、容器虚拟化2.2、 一次构建、随处运行3、Docker安装3.1、前提说明3.2、Docker的基本组成3.3、安装步骤3.4、永远的HelloWorld3.5、阿里云镜像加速3.6、底层原理3.7、为什么比VM快4、Docker常用命令4.1、帮助命令4.2、镜像命令4.3、容器命令4.4、学习方法5、容器数据卷5.1、是什么？5.2、能干什么？5.3、数据卷5.4、数据
OpenStack Queens版搭建详解
CodeStar`
01-16 3870
目录 OpenStack Queens版搭建详解 1.基础环境配置 1.2 节点网络规划 1.3 关闭防火墙 1.4 配置yum源 1.5 配置节点IP 1.6 配置主机名 1.7 配置主机名解析(hosts) 1.8 配置NTP服务 2.安装基础软件包 2.1 安装OpenStack软件包 2.2 安装mariadb数据库 2.3 安装RabbitMQ消息队列 ...
MySQL 5.6 & 5.7最优配置文件模板(my.ini)
09-10
MySQL的配置文件(my.ini)是数据库性能调优的关键部分，对于MySQL 5.6和5.7这两个版本，优化配置尤为重要。配置文件模板提供了一种基础设置，可以根据服务器的具体情况进行微调，以达到最佳性能。以下是一些重要的...
MySQL 5.6中备份及还原performance_schema配置
08-02
在MySQL 5.6版本中，`performance_schema`是一个重要的系统表集合，它提供了数据库性能监控和分析的能力。这个表空间记录了各种数据库操作的详细信息，如查询、锁、线程和内存使用等，帮助管理员优化数据库性能。这...
docker 安装 mysql5.6.51 （MySQL 5.7 同理）使用笔记
zyklbr的博客
11-30 508
拉取镜像（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
docker 中mysql 5.6 内存占用过大，优化配置
心飞路漫的博客
08-25 4301
主要参考 https://blog.csdn.net/nagisazz/article/details/86540992 https://v2ex.com/t/269589 https://blog.csdn.net/kangle0228/article/details/81334555 经过多次尝试后，我的mysql的内存占用从500M上下，减少到了80M上下 最后我自己的使用的配置如下 [...
MoonBit Perals Vol.05: 函数式里的依赖注入：Reader Monad
m0_74743788的博客
08-13 1166
普通的函数就像一个流水线，你丢进去一袋面粉，然后直接跑到生产线末端，等着方便面出来。但这条流水线需要自动处理中间的所有复杂情况：没放面粉/“没有下单，期待发货”（null）面团含水量不够把压面机干卡了（抛出异常）配料机需要读取今天的生产配方，比如是红烧牛肉味还是香菇炖鸡味（读取外部配置）流水线末端的打包机需要记录今天打包了多少包（更新计数器）Monad 就是专门管理这条复杂流水线的“总控制系统”。它把你的数据和处理流程的上下文一起打包，确保整个流程能顺畅、安全地进行下去。
如何在 Linux 上安装 SQL Server 2022 和 Azure Data Studio
csdn_aspnet的专栏，请点击博客主页右上角三个点中的私信联系
08-15 600
本文介绍了如何在Linux系统（以Ubuntu 20.04为例）上安装SQL Server 2022及Azure Data Studio。SQL Server自2017版开始支持Linux平台，通过平台抽象层实现跨平台兼容性。文章详细说明了安装步骤：导入GPG密钥、注册存储库、安装软件包并配置管理员密码。同时介绍了SQL Server在Linux上的不同版本（企业版、标准版等）和主要功能差异。安装完成后，用户可通过Azure Data Studio等工具进行连接管理，文中提供了连接参数设置指南。整个过程简便
MySQL 常用命令速查表
最新发布
DeppBing的博客，全栈修炼记｜记录一个开发者的进化轨迹
08-15 186
本文总结了MySQL常用SQL命令，涵盖数据库操作、表操作、数据操作、数据查询、权限控制、事务控制等核心功能。主要内容包括：数据库的创建/删除/修改（CREATE/DROP/ALTER DATABASE）；表结构的创建/修改/删除（CREATE/ALTER/DROP TABLE）；数据增删改查（INSERT/DELETE/UPDATE/SELECT）；索引管理（CREATE/DROP INDEX）；用户权限控制（GRANT/REVOKE）；事务管理（START/COMMIT/ROLLBACK）等。每个命令都
cat本地部署
Chaser______的博客
08-15 311
CAT监控系统部署指南 环境准备：JDK1.8、Tomcat、MySQL 获取CAT包：通过Git克隆源码或下载官网war包 部署步骤： 创建/data目录结构并设置权限 配置client.xml、datasources.xml和server.xml 导入SQL文件创建数据库 将cat.war部署到Tomcat的webapps目录 启动服务：启动Tomcat后访问http://ip:8080/cat 注意事项：将配置文件中的127.0.0.1替换为实际IP地址 （98字）
SQL进阶-学习路线梳理（一）
xfzldxfz的博客
08-13 769
本文档是MySQL 8.0+的SQL学习指南，内容涵盖从基础到进阶的完整知识体系。主要包含SQL基础概念、数据类型与约束、CRUD操作、函数、表连接、分组聚合、子查询、窗口函数等核心语法，以及索引优化、事务锁机制、存储过程、触发器、视图等高级特性。特别介绍了MySQL 8.0的新功能如CTE、JSON数据处理等，并提供了数据库设计、性能优化、安全管理、备份恢复等实战经验。文档强调生产环境验证的重要性，建议根据实际业务场景调整SQL语句，并定期关注MySQL官方更新。
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、付费专栏及课程。

余额充值