mysq多实例安装和管理［二］ -- 官方自带的mysqld_multi方式安装管理

最新推荐文章于 2024-02-09 20:02:04 发布

翻译最新推荐文章于 2024-02-09 20:02:04 发布 · 897 阅读

文章标签：

#mysql

Linux 专栏收录该内容

10 篇文章

订阅专栏

本文深入解析了如何利用MySQL自带的mysqld_multi工具实现多实例部署，并提供了具体的配置示例。包括多实例的启动、管理、密码设置及环境变量的调整，确保了在多台服务器上高效运行多个MySQL实例。

本文转自 http://blog.chinaunix.net/uid-20639775-id-3438560.html 向作者致敬，剖析的非常清晰

第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。

管理的话，在本地都是采用 -S /data/dbdata_3306/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！

大家在管理多实例的话可以使用mysqlmanager实例管理器，管理器来会比较方面，限于篇幅，这里就不在多做介绍！

再来看第二种通过官方自带的mysqld_multi来实现多实例实战：

这里的mysql安装以及数据库的初始化和前面的步骤一样，就不再赘述。

mysqld_multi的配置
[root@test data]# vim /etc/my.cnf

    [mysqld_multi]
    mysqld = /data/mysql/bin/mysqld_safe
    mysqladmin = /data/mysql/bin/mysqladmin
    user = admin
    password = password
    [mysqld1]
    socket = /data/dbdata_3306/mysql.sock
    port = 3306
    pid-file = /data/dbdata_3306/3306.pid
    datadir = /data/dbdata_3306
    user = mysql
    skip-name-resolve
    lower_case_table_names=1
    innodb_file_per_table=1
    back_log = 50
    max_connections = 300
    max_connect_errors = 1000
    table_open_cache = 2048
    max_allowed_packet = 16M
    binlog_cache_size = 2M
    max_heap_table_size = 64M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    thread_cache_size = 64
    thread_concurrency = 8
    query_cache_size = 64M
    query_cache_limit = 2M
    ft_min_word_len = 4
    default-storage-engine = innodb
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    log-bin=mysql-bin
    binlog_format=mixed
    slow_query_log
    long_query_time = 1
    server-id = 1
    key_buffer_size = 8M
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover
    innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 200M
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_file_io_threads = 8
    innodb_thread_concurrency = 16
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 512M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 60
    innodb_lock_wait_timeout = 120

    [mysqld2]
    socket = /data/dbdata_3307/mysql.sock
    port = 3307
    pid-file = /data/dbdata_3307/3307.pid
    datadir = /data/dbdata_3307
    user = mysql
    skip-name-resolve
    lower_case_table_names=1
    innodb_file_per_table=1
    back_log = 50
    max_connections = 300
    max_connect_errors = 1000
    table_open_cache = 2048
    max_allowed_packet = 16M
    binlog_cache_size = 2M
    max_heap_table_size = 64M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    thread_cache_size = 64
    thread_concurrency = 8
    query_cache_size = 64M
    query_cache_limit = 2M
    ft_min_word_len = 4
    default-storage-engine = innodb
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    log-bin=mysql-bin
    binlog_format=mixed
    slow_query_log
    long_query_time = 1
    server-id = 1
    key_buffer_size = 8M
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover
    innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 200M
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_file_io_threads = 8
    innodb_thread_concurrency = 16
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 512M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 60
    innodb_lock_wait_timeout = 120
    
    [mysqld3]
    socket = /data/dbdata_3308/mysql.sock
    port = 3308
    pid-file = /data/dbdata_3308/3308.pid
    datadir = /data/dbdata_3308
    user = mysql
    skip-name-resolve
    lower_case_table_names=1
    innodb_file_per_table=1
    back_log = 50
    max_connections = 300
    max_connect_errors = 1000
    table_open_cache = 2048
    max_allowed_packet = 16M
    binlog_cache_size = 2M
    max_heap_table_size = 64M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    thread_cache_size = 64
    thread_concurrency = 8
    query_cache_size = 64M
    query_cache_limit = 2M
    ft_min_word_len = 4
    default-storage-engine = innodb
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    log-bin=mysql-bin
    binlog_format=mixed
    slow_query_log
    long_query_time = 1
    server-id = 1
    key_buffer_size = 8M
    read_buffer_size = 2M
    read_rnd_buffer_size = 2M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover
    innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 200M
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_file_io_threads = 8
    innodb_thread_concurrency = 16
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 512M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 60
    innodb_lock_wait_timeout = 120
    [mysqldump]
    quick
    max_allowed_packet = 256M
    [mysql]
    no-auto-rehash
    prompt=\\u@\\d \\R:\\m>
    [myisamchk]
    key_buffer_size = 512M
    sort_buffer_size = 512M
    read_buffer = 8M
    write_buffer = 8M
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    open-files-limit = 8192

mysqld_multi启动

    [root@test data]# /data/mysql/bin/mysqld_multi start 1
    [root@test data]# /data/mysql/bin/mysqld_multi start 2
    [root@test data]# /data/mysql/bin/mysqld_multi start 3

或者采用一条命令的形式：

    [root@test data]# /data/mysql/bin/mysqld_multi start 1-3

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

    [root@test data]# /data/mysql/bin/mysqladmin -uroot password 123456 -S /data/dbdata_3306/mysql.sock
    [root@test data]# /data/mysql/bin/mysqladmin -uroot password 123456 -S /data/dbdata_3307/mysql.sock
    [root@test data]# /data/mysql/bin/mysqladmin -uroot password 123456 -S /data/dbdata_3308/mysql.sock

登录测试并创建admin密码(停止mysql的时候需要使用到)---注意机器的域名

    [root@test data]# 
    /data/mysql/bin/mysql -uroot -p123456 -S /data/dbdata_3306/mysql.sock
    GRANT SHUTDOWN ON *.* TO 'admin'@'test' IDENTIFIED BY 123456;
    flush privileges;
    /data/mysql/bin/mysql -uroot -p123456 -S /data/dbdata_3307/mysql.sock
    GRANT SHUTDOWN ON *.* TO 'admin'@'test' IDENTIFIED BY 123456;
    flush privileges;
    /data/mysql/bin/mysql -uroot -p123456 -S /data/dbdata_3308/mysql.sock
    GRANT SHUTDOWN ON *.* TO 'admin'@'test' IDENTIFIED BY 123456;
    flush privileges;

更改环境变量

    vim /etc/profile
    PATH=${PATH}:/data/mysql/bin/
    source /etc/profile

添加到自动启动

    vim /etc/rc.local
    /data/mysql/bin/mysqld_multi start 1-3

mysq多实例安装和管理 ［二］ -- 官方自带的mysqld_multi方式安装管理

mysq多实例安装和管理［二］ -- 官方自带的mysqld_multi方式安装管理