• mysql-多实例


    一、MySQL 多实例

    1、简介

    什么使多实例呢?

    多实例就是在一台机器上开启多个不同的服务端口(如:3306,3307);

    运行多个MySQL服务进程,这些进程通过不同的socket监听不同的服务端口来提供各自的服务。

    MySQL多实例共用一个 MySQL 的安装程序,使用不同(也可相同)的 my.cnf 配置文件,启动程序和数据文件。

    在提供服务时,多实例 MySQL 在逻辑上是各自独立的,多个实例的本身是根据配置文件对应的设定值,来获得服务器的相关硬件资源多少。

    2、安装配置前操作

    安装配置前的一些配置少不了

    文件的目录最好也规矩,这样才专业

    /data/ops/app/mysql-5.6.23/     ###mysql的安装路径
    mkdir /data/ops/app/mysql-5.6.23/{3306,3307}
    vim /data/ops/app/mysql-5.6.23/etc/3306.cnf
    
    [client]
    character-set-server = utf8
    
    [mysql]
    character-set-server = utf8
    
    [mysqld]
    #bind-address=127.0.0.1
    character-set-server = utf8
    datadir=/data/ops/app/mysql-5.6.23/3306
    socket=/data/ops/app/mysql-5.6.23/3306/3306.sock
    user=mysql
    skip-grant-tables
    
    symbolic-links=0
    skip-name-resolve
    
    #init-connect='insert into accesslog.accesslog(id, time, localname, matchname) values(connection_id(), now(), user(),current_user());'
    
    slow_query_log = 1
    long_query_time = 4
    #slow-query-log-file = /data/ops/app/mysql-5.6.23/logs/3306/3306_slow.log
    log-queries-not-using-indexes = false
    lower-case-table-names = 1
    
    server-id = 1
    max_connections =5000
    max_allowed_packet = 16M
    connect_timeout = 60
    log-slave-updates
    log-bin = 3306-bin
    expire_logs_days = 7
    slave-skip-errors = 1062
    #binlog_do_db =
    back_log = 200
    max_connect_errors = 10
    table_open_cache = 10240
    
    binlog_cache_size = 1M
    max_heap_table_size = 64M
    sort_buffer_size = 4M
    join_buffer_size = 4M
    thread_cache_size = 8
    thread_concurrency = 8
    query_cache_size = 512M
    query_cache_limit = 2M
    ft_min_word_len = 4
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    binlog_format = mixed
    log-short-format
    key_buffer_size = 512M
    read_buffer_size = 16M
    read_rnd_buffer_size = 16M
    bulk_insert_buffer_size = 64M
    innodb_file_per_table
    innodb_additional_mem_pool_size = 100M
    innodb_buffer_pool_size = 1G
    innodb_data_file_path = ibdata1:12M;ibdata2:12M:autoextend
    innodb_file_io_threads = 4
    innodb_thread_concurrency = 8
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 256M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    slave-skip-errors = 1062
    
    [mysqld_safe]
    log-error=/data/ops/app/mysql-5.6.23/logs/3306/3306.log
    pid-file=/data/ops/app/mysql-5.6.23/3306/3306.pid
    
    vim /data/ops/app/mysql-5.6.23/etc/3307.cnf  ### 配置文件
    
    [client]
    character-set-server = utf8
    
    [mysql]
    character-set-server = utf8
    
    [mysqld]
    #bind-address=127.0.0.1 
    character-set-server = utf8
    datadir=/data/ops/app/mysql-5.6.23/3307
    socket=/data/ops/app/mysql-5.6.23/3307/3307.sock
    user=mysql
    skip-grant-tables
    
    symbolic-links=0
    skip-name-resolve
    
    #init-connect='insert into accesslog.accesslog(id, time, localname, matchname) values(connection_id(), now(), user(),current_user());'
    
    slow_query_log = 1
    long_query_time = 4
    slow-query-log-file = /data/ops/app/mysql-5.6.23/logs/3307/3307_slow.log
    log-queries-not-using-indexes = false
    lower-case-table-names = 1
    
    server-id = 1
    max_connections =5000
    max_allowed_packet = 16M
    connect_timeout = 60
    log-slave-updates
    log-bin = 3307-bin
    expire_logs_days = 7
    slave-skip-errors = 1062
    #binlog_do_db =
    back_log = 200
    max_connect_errors = 10
    table_open_cache = 10240
    
    binlog_cache_size = 1M
    max_heap_table_size = 64M
    sort_buffer_size = 4M
    join_buffer_size = 4M
    thread_cache_size = 8
    thread_concurrency = 8
    query_cache_size = 512M
    query_cache_limit = 2M
    ft_min_word_len = 4
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    binlog_format = mixed
    log-short-format
    key_buffer_size = 512M
    read_buffer_size = 16M
    read_rnd_buffer_size = 16M
    bulk_insert_buffer_size = 64M
    innodb_file_per_table
    innodb_additional_mem_pool_size = 100M
    innodb_buffer_pool_size = 1G
    innodb_data_file_path = ibdata1:12M;ibdata2:12M:autoextend
    innodb_file_io_threads = 4
    innodb_thread_concurrency = 8
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 256M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    slave-skip-errors = 1062
    
    [mysqld_safe]
    log-error=/data/ops/app/mysql-5.6.23/logs/3307/3307.log
    pid-file=/data/ops/app/mysql-5.6.23/3307/3307.pid
    

    ###创建初始化脚本以及启动文件

    vim /data/ops/app/mysql-5.6.23/scripts/init/3306.sh
    
    #/bin/bash
    # 3306.sh can create a 3306 instance
    # create directories
    if [ ! -d '/data/ops/app/mysql-5.6.23/3306' ];then
      mkdir -p '/data/ops/app/mysql-5.6.23/3306';
    fi
    if [ ! -d '/data/ops/app/mysql-5.6.23/logs/3306' ];then
      mkdir -p '/data/ops/app/mysql-5.6.23/logs/3306';
    fi
    
    # set env
    conf="/data/ops/app/mysql-5.6.23/etc/3306.cnf"
    basedir="/data/ops/app/mysql-5.6.23"
    datadir="/data/ops/app/mysql-5.6.23/3306"
    pid="/data/ops/app/mysql-5.6.23/3306/3306.pid"
    sock="/data/ops/app/mysql-5.6.23/3306/3306.sock"
    port='3000'
    user='mysql'
    
    # create instance
    #/data/ops/app/mysql-5.6.23/scripts/mysql_install_db --user=$user --basedir=$basedir --datadir=$datadir
    
    # boot instance
    /data/ops/app/mysql-5.6.23/bin/mysqld_safe --defaults-file=$conf --user=$user --basedir=$basedir --datadir=$datadir --port=$port --pid-file=$pid --socket=$sock &
    
    vim /data/ops/app/mysql-5.6.23/scripts/init/3307.sh
    
    #/bin/bash
    # 3307.sh can create a 3307 instance
    # create directories
    if [ ! -d '/data/ops/app/mysql-5.6.23/3307' ];then
      mkdir -p '/data/ops/app/mysql-5.6.23/3307';
    fi
    if [ ! -d '/data/ops/app/mysql-5.6.23/logs/3307' ];then
      mkdir -p '/data/ops/app/mysql-5.6.23/logs/3307';
    fi
    
    # set env
    conf="/data/ops/app/mysql-5.6.23/etc/3307.cnf"
    basedir="/data/ops/app/mysql-5.6.23"
    datadir="/data/ops/app/mysql-5.6.23/3307"
    pid="/data/ops/app/mysql-5.6.23/3307/3307.pid"
    sock="/data/ops/app/mysql-5.6.23/3307/3307.sock"
    port='3002'
    user='mysql'
    
    # create instance
    #/data/ops/app/mysql-5.6.23/scripts/mysql_install_db --user=$user --basedir=$basedir --datadir=$datadir
    
    # boot instance
    /data/ops/app/mysql-5.6.23/bin/mysqld_safe --defaults-file=$conf --user=$user --basedir=$basedir --datadir=$datadir --port=$port --pid-file=$pid --socket=$sock &
    

    #####注意初始化脚本完毕以后mysql才能启动

    su --shell=/bin/bash mysql -c "/data/ops/app/mysql-5.6.23/scripts/init/3306.sh"                               ###启动脚本 可以放在/etc/rc.local下 开机自启

    su --shell=/bin/bash mysql -c "/data/ops/app/mysql-5.6.23/scripts/init/3307.sh"

    ####mysql关闭

    killall mysqld  不建议这样操作

    mysqladmin -u mysql -P 3002 -S /data/ops/app/mysql-5.6.23/3307/3307.sock shutdown            ###建议这样关闭数据库

    增加一个mysql实例

    问题又来了,要增加一个实例怎么弄呢?

    其实不难,只需按上面步骤,创建目录,在修改相应配置文件,初始化就ok

  • 相关阅读:
    帮助智力障碍的可怜儿童
    PE556
    ZJOIDay2T1 BB题解
    CTSC2016游记
    再次被老爸钦点退役
    欧拉筛
    考据
    51nod 1020 逆序排列(动态规划)
    51nod 1186 质数检测(Miller-Rabin算法)
    大整数类模板
  • 原文地址:https://www.cnblogs.com/zhaobin-diray/p/9354075.html
Copyright © 2020-2023  润新知