• mysql多实例的配置和管理

    多实例mysql的安装和管理

    mysql的多实例有两种方式可以实现，两种方式各有利弊。
    第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。
    第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。

    下面就分别来实战这两种多实例的安装和管理

    先来学习第一种使用多个配置文件启动多个不同进程的情况：

    环境介绍：

    mysql 版本：5.1.50

    操作系统：SUSE 11

    mysql实例数：3个

    实例占用端口分别为：3306、3307、3308

    创建mysql用户

    点击(此处)折叠或打开

    1. /usr/sbin/groupadd mysql
    2. /usr/sbin/useradd -g mysql mysql
    编译安装mysql

    点击(此处)折叠或打开

    1. tar xzvf mysql-5.1.50.tar.gz
    2. cd mysql-5.1.50
    3. ./configure '--prefix=/usr/local/mysql' '--with-charset=utf8' '--with-extra-charsets=complex' '--with-pthread' '--enable-thread-safe-client' '--with-ssl' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' '--enable-shared' '--enable-assembler'
    4. make
    5. make install
    初始化数据库

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306 --user=mysql
    2. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307 --user=mysql
    3. /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308 --user=mysql
    创建配置文件
    vim /data/dbdata_3306/my.cnf
    3306的配置文件如下：

    点击(此处)折叠或打开

    1. [client]
    2. port = 3306
    3. socket = /data/dbdata_3306/mysql.sock
    4. [mysqld]
    5. datadir=/data/dbdata_3306/
    6. skip-name-resolve
    7. lower_case_table_names=1
    8. innodb_file_per_table=1
    9. port = 3306
    10. socket = /data/dbdata_3306/mysql.sock
    11. back_log = 50
    12. max_connections = 300
    13. max_connect_errors = 1000
    14. table_open_cache = 2048
    15. max_allowed_packet = 16M
    16. binlog_cache_size = 2M
    17. max_heap_table_size = 64M
    18. sort_buffer_size = 2M
    19. join_buffer_size = 2M
    20. thread_cache_size = 64
    21. thread_concurrency = 8
    22. query_cache_size = 64M
    23. query_cache_limit = 2M
    24. ft_min_word_len = 4
    25. default-storage-engine = innodb
    26. thread_stack = 192K
    27. transaction_isolation = REPEATABLE-READ
    28. tmp_table_size = 64M
    29. log-bin=mysql-bin
    30. binlog_format=mixed
    31. slow_query_log
    32. long_query_time = 1
    33. server-id = 1
    34. key_buffer_size = 8M
    35. read_buffer_size = 2M
    36. read_rnd_buffer_size = 2M
    37. bulk_insert_buffer_size = 64M
    38. myisam_sort_buffer_size = 128M
    39. myisam_max_sort_file_size = 10G
    40. myisam_repair_threads = 1
    41. myisam_recover
    42. innodb_additional_mem_pool_size = 16M
    43. innodb_buffer_pool_size = 200M
    44. innodb_data_file_path = ibdata1:10M:autoextend
    45. innodb_file_io_threads = 8
    46. innodb_thread_concurrency = 16
    47. innodb_flush_log_at_trx_commit = 1
    48. innodb_log_buffer_size = 16M
    49. innodb_log_file_size = 512M
    50. innodb_log_files_in_group = 3
    51. innodb_max_dirty_pages_pct = 60
    52. innodb_lock_wait_timeout = 120
    53. [mysqldump]
    54. quick
    55. max_allowed_packet = 256M
    56. [mysql]
    57. no-auto-rehash
    58. prompt=\\u@\\d \\R:\\m>
    59. [myisamchk]
    60. key_buffer_size = 512M
    61. sort_buffer_size = 512M
    62. read_buffer = 8M
    63. write_buffer = 8M
    64. [mysqlhotcopy]
    65. interactive-timeout
    66. [mysqld_safe]
    67. open-files-limit = 8192
    vim /data/dbdata_3307/my.cnf
    3307的配置文件如下：

    点击(此处)折叠或打开

    1. [client]
    2. port = 3307
    3. socket = /data/dbdata_3307/mysql.sock
    4. [mysqld]
    5. datadir=/data/dbdata_3307/
    6. skip-name-resolve
    7. lower_case_table_names=1
    8. innodb_file_per_table=1
    9. port = 3307
    10. socket = /data/dbdata_3307/mysql.sock
    11. back_log = 50
    12. max_connections = 300
    13. max_connect_errors = 1000
    14. table_open_cache = 2048
    15. max_allowed_packet = 16M
    16. binlog_cache_size = 2M
    17. max_heap_table_size = 64M
    18. sort_buffer_size = 2M
    19. join_buffer_size = 2M
    20. thread_cache_size = 64
    21. thread_concurrency = 8
    22. query_cache_size = 64M
    23. query_cache_limit = 2M
    24. ft_min_word_len = 4
    25. default-storage-engine = innodb
    26. thread_stack = 192K
    27. transaction_isolation = REPEATABLE-READ
    28. tmp_table_size = 64M
    29. log-bin=mysql-bin
    30. binlog_format=mixed
    31. slow_query_log
    32. long_query_time = 1
    33. server-id = 1
    34. key_buffer_size = 8M
    35. read_buffer_size = 2M
    36. read_rnd_buffer_size = 2M
    37. bulk_insert_buffer_size = 64M
    38. myisam_sort_buffer_size = 128M
    39. myisam_max_sort_file_size = 10G
    40. myisam_repair_threads = 1
    41. myisam_recover
    42. innodb_additional_mem_pool_size = 16M
    43. innodb_buffer_pool_size = 200M
    44. innodb_data_file_path = ibdata1:10M:autoextend
    45. innodb_file_io_threads = 8
    46. innodb_thread_concurrency = 16
    47. innodb_flush_log_at_trx_commit = 1
    48. innodb_log_buffer_size = 16M
    49. innodb_log_file_size = 512M
    50. innodb_log_files_in_group = 3
    51. innodb_max_dirty_pages_pct = 60
    52. innodb_lock_wait_timeout = 120
    53. [mysqldump]
    54. quick
    55. max_allowed_packet = 256M
    56. [mysql]
    57. no-auto-rehash
    58. prompt=\\u@\\d \\R:\\m>
    59. [myisamchk]
    60. key_buffer_size = 512M
    61. sort_buffer_size = 512M
    62. read_buffer = 8M
    63. write_buffer = 8M
    64. [mysqlhotcopy]
    65. interactive-timeout
    66. [mysqld_safe]
    67. open-files-limit = 8192
    vim /data/dbdata_3308/my.cnf
    3308的配置文件如下：

    点击(此处)折叠或打开

    1. [client]
    2. port = 3308
    3. socket = /data/dbdata_3308/mysql.sock
    4. [mysqld]
    5. datadir=/data/dbdata_3308/
    6. skip-name-resolve
    7. lower_case_table_names=1
    8. innodb_file_per_table=1
    9. port = 3308
    10. socket = /data/dbdata_3308/mysql.sock
    11. back_log = 50
    12. max_connections = 300
    13. max_connect_errors = 1000
    14. table_open_cache = 2048
    15. max_allowed_packet = 16M
    16. binlog_cache_size = 2M
    17. max_heap_table_size = 64M
    18. sort_buffer_size = 2M
    19. join_buffer_size = 2M
    20. thread_cache_size = 64
    21. thread_concurrency = 8
    22. query_cache_size = 64M
    23. query_cache_limit = 2M
    24. ft_min_word_len = 4
    25. default-storage-engine = innodb
    26. thread_stack = 192K
    27. transaction_isolation = REPEATABLE-READ
    28. tmp_table_size = 64M
    29. log-bin=mysql-bin
    30. binlog_format=mixed
    31. slow_query_log
    32. long_query_time = 1
    33. server-id = 1
    34. key_buffer_size = 8M
    35. read_buffer_size = 2M
    36. read_rnd_buffer_size = 2M
    37. bulk_insert_buffer_size = 64M
    38. myisam_sort_buffer_size = 128M
    39. myisam_max_sort_file_size = 10G
    40. myisam_repair_threads = 1
    41. myisam_recover
    42. innodb_additional_mem_pool_size = 16M
    43. innodb_buffer_pool_size = 200M
    44. innodb_data_file_path = ibdata1:10M:autoextend
    45. innodb_file_io_threads = 8
    46. innodb_thread_concurrency = 16
    47. innodb_flush_log_at_trx_commit = 1
    48. innodb_log_buffer_size = 16M
    49. innodb_log_file_size = 512M
    50. innodb_log_files_in_group = 3
    51. innodb_max_dirty_pages_pct = 60
    52. innodb_lock_wait_timeout = 120
    53. [mysqldump]
    54. quick
    55. max_allowed_packet = 256M
    56. [mysql]
    57. no-auto-rehash
    58. prompt=\\u@\\d \\R:\\m>
    59. [myisamchk]
    60. key_buffer_size = 512M
    61. sort_buffer_size = 512M
    62. read_buffer = 8M
    63. write_buffer = 8M
    64. [mysqlhotcopy]
    65. interactive-timeout
    66. [mysqld_safe]
    67. open-files-limit = 8192

    创建自动启动文件
    vim /data/dbdata_3306/mysqld
    3306的启动文件如下：

    点击(此处)折叠或打开

    1. #!/bin/bash
    2. mysql_port=3306
    3. mysql_username="admin"
    4. mysql_password="password"
    6. function_start_mysql()
    7. {
    8. printf "Starting MySQL...\n"
    9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
    10. }
    12. function_stop_mysql()
    13. {
    14. printf "Stoping MySQL...\n"
    15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
    16. }
    18. function_restart_mysql()
    19. {
    20. printf "Restarting MySQL...\n"
    21. function_stop_mysql
    22. function_start_mysql
    23. }
    25. function_kill_mysql()
    26. {
    27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
    29. }
    31. case $1 in
    32. start)
    33. function_start_mysql;;
    34. stop)
    35. function_stop_mysql;;
    36. kill)
    37. function_kill_mysql;;
    38. restart)
    39. function_stop_mysql
    40. function_start_mysql;;
    41. *)
    42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
    43. esac

    vim /data/dbdata_3307/mysqld
    3307的启动文件如下：

    点击(此处)折叠或打开

    1. #!/bin/bash
    2. mysql_port=3307
    3. mysql_username="admin"
    4. mysql_password="password"
    6. function_start_mysql()
    7. {
    8. printf "Starting MySQL...\n"
    9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
    10. }
    12. function_stop_mysql()
    13. {
    14. printf "Stoping MySQL...\n"
    15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
    16. }
    18. function_restart_mysql()
    19. {
    20. printf "Restarting MySQL...\n"
    21. function_stop_mysql
    22. function_start_mysql
    23. }
    25. function_kill_mysql()
    26. {
    27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
    29. }
    31. case $1 in
    32. start)
    33. function_start_mysql;;
    34. stop)
    35. function_stop_mysql;;
    36. kill)
    37. function_kill_mysql;;
    38. restart)
    39. function_stop_mysql
    40. function_start_mysql;;
    41. *)
    42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
    43. esac

    vim /data/dbdata_3308/mysqld
    3308的启动文件如下：

    点击(此处)折叠或打开

    1. #!/bin/bash
    2. mysql_port=3308
    3. mysql_username="admin"
    4. mysql_password="password"
    6. function_start_mysql()
    7. {
    8. printf "Starting MySQL...\n"
    9. /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
    10. }
    12. function_stop_mysql()
    13. {
    14. printf "Stoping MySQL...\n"
    15. /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
    16. }
    18. function_restart_mysql()
    19. {
    20. printf "Restarting MySQL...\n"
    21. function_stop_mysql
    22. function_start_mysql
    23. }
    25. function_kill_mysql()
    26. {
    27. kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
    29. }
    31. case $1 in
    32. start)
    33. function_start_mysql;;
    34. stop)
    35. function_stop_mysql;;
    36. kill)
    37. function_kill_mysql;;
    38. restart)
    39. function_stop_mysql
    40. function_start_mysql;;
    41. *)
    42. echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
    43. esac
    启动3306、3307、3308的mysql

    点击(此处)折叠或打开

    1. /data/dbdata_3306/mysqld start
    2. /data/dbdata_3307/mysqld start
    3. /data/dbdata_3308/mysqld start

    更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock
    2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock
    3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
    登录测试并创建关闭mysql的帐号权限，mysqld脚本要用到！

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    2. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    3. flush privileges;
    4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    5. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    6. flush privileges;
    7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    8. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    9. flush privileges;
    创建了admin帐号以后脚本的stop功能和restart功能就正常了！
    更改环境变量

    点击(此处)折叠或打开

    1. vim /etc/profile 添加下面一行内容
    2. PATH=${PATH}:/usr/local/mysql/bin/
    3. source /etc/profile
    添加到自动启动

    点击(此处)折叠或打开

    1. vim /etc/init.d/boot.local
    2. /data/dbdata_3306/mysqld start
    3. /data/dbdata_3307/mysqld start
    4. /data/dbdata_3308/mysqld start
    如果是rhel或者centos系统的话自启动文件/etc/rc.local
     
    管理的话，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！
     
     
     
    再来看第二种通过官方自带的mysqld_multi来实现多实例实战：
    这里的mysql安装以及数据库的初始化和前面的步骤一样，就不再赘述。

    mysqld_multi的配置
    vim /etc/my.cnf

    点击(此处)折叠或打开

    1. [mysqld_multi]
    2. mysqld = /usr/local/mysql/bin/mysqld_safe
    3. mysqladmin = /usr/local/mysql/bin/mysqladmin
    4. user = admin
    5. password = password
    7. [mysqld1]
    8. socket = /data/dbdata_3306/mysql.sock
    9. port = 3306
    10. pid-file = /data/dbdata_3306/3306.pid
    11. datadir = /data/dbdata_3306
    12. user = mysql
    13. skip-name-resolve
    14. lower_case_table_names=1
    15. innodb_file_per_table=1
    16. back_log = 50
    17. max_connections = 300
    18. max_connect_errors = 1000
    19. table_open_cache = 2048
    20. max_allowed_packet = 16M
    21. binlog_cache_size = 2M
    22. max_heap_table_size = 64M
    23. sort_buffer_size = 2M
    24. join_buffer_size = 2M
    25. thread_cache_size = 64
    26. thread_concurrency = 8
    27. query_cache_size = 64M
    28. query_cache_limit = 2M
    29. ft_min_word_len = 4
    30. default-storage-engine = innodb
    31. thread_stack = 192K
    32. transaction_isolation = REPEATABLE-READ
    33. tmp_table_size = 64M
    34. log-bin=mysql-bin
    35. binlog_format=mixed
    36. slow_query_log
    37. long_query_time = 1
    38. server-id = 1
    39. key_buffer_size = 8M
    40. read_buffer_size = 2M
    41. read_rnd_buffer_size = 2M
    42. bulk_insert_buffer_size = 64M
    43. myisam_sort_buffer_size = 128M
    44. myisam_max_sort_file_size = 10G
    45. myisam_repair_threads = 1
    46. myisam_recover
    47. innodb_additional_mem_pool_size = 16M
    48. innodb_buffer_pool_size = 200M
    49. innodb_data_file_path = ibdata1:10M:autoextend
    50. innodb_file_io_threads = 8
    51. innodb_thread_concurrency = 16
    52. innodb_flush_log_at_trx_commit = 1
    53. innodb_log_buffer_size = 16M
    54. innodb_log_file_size = 512M
    55. innodb_log_files_in_group = 3
    56. innodb_max_dirty_pages_pct = 60
    57. innodb_lock_wait_timeout = 120
    60. [mysqld2]
    61. socket = /data/dbdata_3307/mysql.sock
    62. port = 3307
    63. pid-file = /data/dbdata_3307/3307.pid
    64. datadir = /data/dbdata_3307
    65. user = mysql
    66. skip-name-resolve
    67. lower_case_table_names=1
    68. innodb_file_per_table=1
    69. back_log = 50
    70. max_connections = 300
    71. max_connect_errors = 1000
    72. table_open_cache = 2048
    73. max_allowed_packet = 16M
    74. binlog_cache_size = 2M
    75. max_heap_table_size = 64M
    76. sort_buffer_size = 2M
    77. join_buffer_size = 2M
    78. thread_cache_size = 64
    79. thread_concurrency = 8
    80. query_cache_size = 64M
    81. query_cache_limit = 2M
    82. ft_min_word_len = 4
    83. default-storage-engine = innodb
    84. thread_stack = 192K
    85. transaction_isolation = REPEATABLE-READ
    86. tmp_table_size = 64M
    87. log-bin=mysql-bin
    88. binlog_format=mixed
    89. slow_query_log
    90. long_query_time = 1
    91. server-id = 1
    92. key_buffer_size = 8M
    93. read_buffer_size = 2M
    94. read_rnd_buffer_size = 2M
    95. bulk_insert_buffer_size = 64M
    96. myisam_sort_buffer_size = 128M
    97. myisam_max_sort_file_size = 10G
    98. myisam_repair_threads = 1
    99. myisam_recover
    100. innodb_additional_mem_pool_size = 16M
    101. innodb_buffer_pool_size = 200M
    102. innodb_data_file_path = ibdata1:10M:autoextend
    103. innodb_file_io_threads = 8
    104. innodb_thread_concurrency = 16
    105. innodb_flush_log_at_trx_commit = 1
    106. innodb_log_buffer_size = 16M
    107. innodb_log_file_size = 512M
    108. innodb_log_files_in_group = 3
    109. innodb_max_dirty_pages_pct = 60
    110. innodb_lock_wait_timeout = 120
    113. [mysqld3]
    114. socket = /data/dbdata_3308/mysql.sock
    115. port = 3308
    116. pid-file = /data/dbdata_3308/3308.pid
    117. datadir = /data/dbdata_3308
    118. user = mysql
    119. skip-name-resolve
    120. lower_case_table_names=1
    121. innodb_file_per_table=1
    122. back_log = 50
    123. max_connections = 300
    124. max_connect_errors = 1000
    125. table_open_cache = 2048
    126. max_allowed_packet = 16M
    127. binlog_cache_size = 2M
    128. max_heap_table_size = 64M
    129. sort_buffer_size = 2M
    130. join_buffer_size = 2M
    131. thread_cache_size = 64
    132. thread_concurrency = 8
    133. query_cache_size = 64M
    134. query_cache_limit = 2M
    135. ft_min_word_len = 4
    136. default-storage-engine = innodb
    137. thread_stack = 192K
    138. transaction_isolation = REPEATABLE-READ
    139. tmp_table_size = 64M
    140. log-bin=mysql-bin
    141. binlog_format=mixed
    142. slow_query_log
    143. long_query_time = 1
    144. server-id = 1
    145. key_buffer_size = 8M
    146. read_buffer_size = 2M
    147. read_rnd_buffer_size = 2M
    148. bulk_insert_buffer_size = 64M
    149. myisam_sort_buffer_size = 128M
    150. myisam_max_sort_file_size = 10G
    151. myisam_repair_threads = 1
    152. myisam_recover
    153. innodb_additional_mem_pool_size = 16M
    154. innodb_buffer_pool_size = 200M
    155. innodb_data_file_path = ibdata1:10M:autoextend
    156. innodb_file_io_threads = 8
    157. innodb_thread_concurrency = 16
    158. innodb_flush_log_at_trx_commit = 1
    159. innodb_log_buffer_size = 16M
    160. innodb_log_file_size = 512M
    161. innodb_log_files_in_group = 3
    162. innodb_max_dirty_pages_pct = 60
    163. innodb_lock_wait_timeout = 120
    166. [mysqldump]
    167. quick
    168. max_allowed_packet = 256M
    169. [mysql]
    170. no-auto-rehash
    171. prompt=\\u@\\d \\R:\\m>
    172. [myisamchk]
    173. key_buffer_size = 512M
    174. sort_buffer_size = 512M
    175. read_buffer = 8M
    176. write_buffer = 8M
    177. [mysqlhotcopy]
    178. interactive-timeout
    179. [mysqld_safe]
    180. open-files-limit = 8192
    mysqld_multi启动

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysqld_multi start 1
    2. /usr/local/mysql/bin/mysqld_multi start 2
    3. /usr/local/mysql/bin/mysqld_multi start 3
    或者采用一条命令的形式：

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysqld_multi start 1-3
    更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock
    2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock
    3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
     
    登录测试并创建admin密码(停止mysql的时候需要使用到)

    点击(此处)折叠或打开

    1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    2. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    3. flush privileges;
    4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    5. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    6. flush privileges;
    7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock
    8. GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
    9. flush privileges;
    更改环境变量

    点击(此处)折叠或打开

    1. vim /etc/profile
    2. PATH=${PATH}:/usr/local/mysql/bin/
    3. source /etc/profile
    添加到自动启动

    点击(此处)折叠或打开

    1. vim /etc/init.d/boot.local
    2. /usr/local/mysql/bin/mysqld_multi start 1-3
    如果是rhel或者centos系统的话自启动文件/etc/rc.local
     
    管理的话，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！
    大家在管理多实例的话可以使用mysqlmanager实例管理器，管理器来会比较方面，限于篇幅，这里就不在多做介绍！
  • 相关阅读:
    AS3邮件
    JavaScript中this关键字使用方法详解
    AS3嵌入字体
    xp双击打不开jar包解决方案
    查询在表1表2中都存在，在表3中不存在的SQL(前提：表结构相同)
    这是否为复制Bug？求解！
    批处理添加允许弹出临时窗口站点
    SQL Server 合并IP
    C#学习笔记一(变量、属性、方法，构造函数)
    SQLServer事务的隔离级别
  • 原文地址：https://www.cnblogs.com/feihongwuhen/p/7169755.html
Copyright © 2020-2023  润新知