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

本文转自 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