mysq多实例安装和管理 ［一］ -- 多配置文件方式安装管理

本文转自 http://blog.chinaunix.net/uid-20639775-id-3438560.html         向作者致敬

本人重新梳理了作者的文章，分成两篇文章说明多实例的安装和管理。
第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。

环境介绍：

mysql 版本：5.5.10

操作系统：RedHat  Linux 6.4

mysql实例数：1个

实例占用端口分别为：3306

注意这个问题：datadir路径，初始化数据库和my.cnf中都有配置，一定要一致，否则你会被错误折磨死的。

创建mysql用户

    [root@test data]# /usr/sbin/groupadd mysql
    [root@test data]# /usr/sbin/useradd -g mysql mysql

编译安装mysql（时间比较长，得两根烟的功夫）

　　[root@test data]# tar xzvf mysql-5.1.50.tar.gz
　　[root@test data]# cd mysql-5.1.50
　　[root@test data]# cmake . -DCMAKE_INSTALL_PREFIX=/data/mysql/ -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all 
　　[root@test data]# make &&　make install

初始化数据库

    [root@test data]# /data/mysql/scripts/mysql_install_db --basedir=/data/mysql/ --user=mysql --datadir=/data/dbdata_3306/mysql_data/

创建配置文件

    [root@test data]# vim /data/dbdata_3306/my.cnf

3306的配置文件如下：

    [client]
    port = 3306
    socket = /data/dbdata_3306/mysql.sock
    [mysqld]
    datadir=/data/dbdata_3306/mysql_data/
    skip-name-resolve
    lower_case_table_names=1
    innodb_file_per_table=1
    port = 3306
    socket = /data/dbdata_3306/mysql.sock
    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

创建自动启动文件

    [root@test data]#vim /data/dbdata_3306/mysqld

3306的启动文件如下：

    #!/bin/bash
    mysql_port=3306
    mysql_username="admin"
    mysql_password="123456"
    function_start_mysql()
    {
    printf "Starting MySQL...\n"
    /bin/sh /data/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_${mysql_port}/my.cnf 2>&1 > /dev/null &
    }
    function_stop_mysql()
    {
    printf "Stoping MySQL...\n"
    /data/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown
    }
    function_restart_mysql()
    {
    printf "Restarting MySQL...\n"
    function_stop_mysql
    function_start_mysql
    }
    function_kill_mysql()
    {
    kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
    }
    case $1 in
    start)
    function_start_mysql;;
    stop)
    function_stop_mysql;;
    kill)
    function_kill_mysql;;
    restart)
    function_stop_mysql
    function_start_mysql;;
    *)
    echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
    esac

启动3306端口的mysql

    [root@test data]# /data/dbdata_3306/mysqld start

更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：（上面的配置生效，服务起来了之后再改密码）

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

登录测试并创建关闭mysql的帐号权限，mysqld脚本要用到！

 [root@test data]# /data/mysql/bin/mysql -uroot -p123456 -S /data/dbdata_3306/mysql.sock

     
     mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456';     ----使用这个处理root的账号

     mysql> flush privileges;

创建了admin帐号以后脚本的stop功能和restart功能就正常了！更改环境变量    

     [root@test data]# vim /etc/profile 添加下面一行内容
           PATH=${PATH}:/usr/local/mysql/bin/
           source /etc/profile

添加到自动启动

    [root@test data]#vim /etc/rc.local
            /data/dbdata_3306/mysqld start 

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