mysql系列之多实例3----基于my

发布时间:2019-09-16 07:40:47编辑:auto阅读(1734)

        上一篇博文mysql系列之多实例2----基于多配置文件介绍了,如何部署基于多配置文件的mysql多实例,本篇博文将介绍基于mysql自带的mysqld_multi工具来如何实现mysql多实例的部署和管理!


    环境:
    CentOS 6.5 x86_64位 采用最小化安装,系统经过了基本优化
    selinux 为关闭状态,iptables 为无限制模式
    mysql版本:mysql-5.5.38
    源码包存放位置:/usr/local/src
    源码包编译安装位置:/usr/local/mysql
    数据库存放位置:/mydata


    本方案仅以同一台服务器上跑2个实例为例,演示基于多配置文件的mysql多实例

    一、安装mysql程序

    1、准备软件环境

    [
    root@nolinux ~]# yum install wget make cmake gcc gcc-c++ ncurses ncurses-devel perl -y


    2、准备mysql源码包

    获取源码包的方式有很多,你可以去http://dev.mysql.com/downloads/mysql/自行下载

    [root@nolinux ~]# cd /usr/local/src/ 
    [root@nolinux src]# ll 
    total 21232 
    -rw-r--r--. 1 root root 21739681 Jun 3 20:39 mysql-5.5.38.tar.gz
    [root@nolinux src]# tar zxf mysql-5.5.38.tar.gz 
    [root@nolinux src]# ll 
    total 21236 
    drwxr-xr-x. 31 7161 wheel 4096 May 12 00:39 mysql-5.5.38 
    -rw-r--r--. 1 root root 21739681 Jun 3 20:39 mysql-5.5.38.tar.gz


    3、建立mysql用户

    [root@nolinux ~]# useradd -r -u 306 mysql


    4、mysql安装

    [root@nolinux src]# cd mysql-5.5.38 [root@nolinux mysql-5.5.38]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.38 \
    -DMYSQL_DATADIR=/usr/local/mysql-5.5.38/data \
    -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.38/tmp/mysql.sock \
    -DDEFAULT_CHARSET=gbk \
    -DDEFAULT_COLLATION=gbk_chinese_ci \
    -DENABLED_LOCAL_INFILE=ON \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
    -DWITHOUT_PARTITION_STORAGE_ENGINE=1
    [root@nolinux mysql-5.5.38]# make
    [root@nolinux mysql-5.5.38]# make install


    5、为mysql安装目录制作软链接

    编译安装完成之后,我们需要为mysql安装目录做一个link

    [root@nolinux ~]# cd
    [root@nolinux ~]# ln -s /usr/local/mysql-5.5.38 /usr/local/mysql


    6、配置 mysql 环境变量

    [root@nolinux ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile 
    [root@nolinux ~]# tail -1 /etc/profile 
    export PATH=/usr/local/mysql/bin:$PATH 
    [root@nolinux ~]# source /etc/profile 
    [root@nolinux ~]# echo $PATH 
    /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin


    二、mysql 多实例部署

    1、建立各个实例的对应目录

    [root@nolinux ~]# mkdir /mydata/{3306,3307}/data -p
    [root@nolinux ~]# chown -R mysql.mysql /mydata/
    [root@nolinux ~]# tree /mydata/ 
    /mydata/ 
    ├── 3306        # 3306端口的mysql实例目录
    │   └── data   # 3307端口的mysql数据目录
    └── 3307        # 3307端口的mysql实例目录
         └── data   # 3307端口的mysql数据目录
    
    4 directories, 0 files


    2、单一配置文件部署

    a、查看默认模板配置文件

    [root@nolinux ~]# ls /usr/local/mysql/support-files/my-*/usr/local/mysql/support-files/my-huge.cnf 
    /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf 
    /usr/local/mysql/support-files/my-large.cnf 
    /usr/local/mysql/support-files/my-medium.cnf 
    /usr/local/mysql/support-files/my-small.cnf

    b、为每个实例选择配置文件

    这里我们在以上模板文件中选择一个

    [root@nolinux ~]# cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf

    c、修改默认配置文件,修改结果如下

    [root@nolinux ~]# cat /etc/my.cnf
    [mysqld_multi]
    mysqld = /usr/local/mysql/bin/mysqld_safe
    mysqladmin = /usr/local/mysql/bin/mysqladmin
    user = mysql   # 如果该地方不是root用户,下面则需要在数据库为该用户添加shutdown权限,不然mysqld_multi将无法关闭实例
    password = sunsky
    
    [mysqld1]
    port = 3306
    server-id = 1
    socket = /mydata/3306/mysql.sock
    pid-file = /mydata/3306/mysql.pid
    datadir = /mydata/3306/data
    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
    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]
    port = 3307
    server-id = 2
    socket = /mydata/3307/mysql.sock
    pid-file = /mydata/3307/mysql.pid
    datadir = /mydata/3307/data
    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
    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


    3、初始化各个mysql实例的数据文件

    [root@nolinux ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/3306/data --user=mysql 
    WARNING: The host 'nolinux' could not be looked up with resolveip. 
    This probably means that your libc libraries are not 100 % compatible 
    with this binary MySQL version. The MySQL daemon, mysqld, should work 
    normally with the exception that host name resolving will not work. 
    This means that you should use IP addresses instead of hostnames 
    when specifying MySQL privileges ! 
    Installing MySQL system tables... 
    OK 
    Filling help tables... 
    OK 
    
    To start mysqld at boot time you have to copy 
    support-files/mysql.server to the right place for your system 
    
    PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 
    To do so, start the server, then issue the following commands: 
    
    /usr/local/mysql/bin/mysqladmin -u root password 'new-password' 
    /usr/local/mysql/bin/mysqladmin -u root -h nolinux password 'new-password' 
    
    Alternatively you can run: 
    /usr/local/mysql/bin/mysql_secure_installation 
    
    which will also give you the option of removing the test 
    databases and anonymous user created by default. This is 
    strongly recommended for production servers. 
    
    See the manual for more instructions. 
    
    You can start the MySQL daemon with: 
    cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe & 
    
    You can test the MySQL daemon with mysql-test-run.pl 
    cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl 
    
    Please report any problems at http://bugs.mysql.com/ 
    
    [root@nolinux ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/3307/data --user=mysql 
    WARNING: The host 'nolinux' could not be looked up with resolveip. 
    This probably means that your libc libraries are not 100 % compatible 
    with this binary MySQL version. The MySQL daemon, mysqld, should work 
    normally with the exception that host name resolving will not work. 
    This means that you should use IP addresses instead of hostnames 
    when specifying MySQL privileges ! 
    Installing MySQL system tables... 
    OK 
    Filling help tables... 
    OK 
    
    To start mysqld at boot time you have to copy 
    support-files/mysql.server to the right place for your system 
    
    PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 
    To do so, start the server, then issue the following commands: 
    
    /usr/local/mysql/bin/mysqladmin -u root password 'new-password' 
    /usr/local/mysql/bin/mysqladmin -u root -h nolinux password 'new-password' 
    
    Alternatively you can run: 
    /usr/local/mysql/bin/mysql_secure_installation 
    
    which will also give you the option of removing the test 
    databases and anonymous user created by default. This is 
    strongly recommended for production servers. 
    
    See the manual for more instructions. 
    
    You can start the MySQL daemon with: 
    cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe & 
    
    You can test the MySQL daemon with mysql-test-run.pl 
    cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl 
    
    Please report any problems at http://bugs.mysql.com/


    4、多实例的启动测试

    [root@nolinux ~]# /usr/local/mysql/bin/mysqld_multi start   # 默认start后面不跟数字,就表示全部实例都启动
    [root@nolinux ~]# lsof -i tcp:3306 
    COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME 
    mysqld 31208 mysql 14u IPv4 81308 0t0 TCP *:mysql (LISTEN) 
    [root@nolinux ~]# lsof -i tcp:3306 
    COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME 
    mysqld 31208 mysql 14u IPv4 81308 0t0 TCP *:mysql (LISTEN) 
    补充:
    仅启动某一个
    [root@nolinux ~]# /usr/local/mysql/bin/mysqld_multi start  1  # 这里的1是我们在/etc/my.cnf中[mysqld1]标题中的数字,这个数字可以随便起,但是最好保证,数字是越来越大的!


    5、多实例的关闭

    [root@nolinux ~]# /usr/local/mysql/bin/mysql -S /mydata/3306/mysql.sock -e "grant shutdown on *.* to 'mysql'@'localhost' identified by 'sunsky'
    [root@nolinux ~]# /usr/local/mysql/bin/mysql
     
    -S /mydata/3307/mysql.sock -e "grant shutdown on *.* to 'mysql'@'localhost' identified by 'sunsky'
    [root@nolinux ~]# /usr/local/mysql/bin/mysqld_multi stop 
    [root@nolinux ~]# netstat -lntup | grep 330


    6、配置多实例的开机自启动

    通过将 mysql 多实例的命令与对应的参数放入/etc/rc.local文件,来实现mysql多实例的开机自启动

    [root@nolinux ~]# echo '# To start the mysql instance boot since 3306' >> /etc/rc.local 
    [root@nolinux ~]# echo '/usr/local/mysql/bin/mysqld_multi start 1' >> /etc/rc.local 
    [root@nolinux ~]# echo '# To start the mysql instance boot since 3307' >> /etc/rc.local 
    [root@nolinux ~]# echo '/usr/local/mysql/bin/mysqld_multi start 2' >> /etc/rc.local 
    [root@nolinux ~]# tail -4 /etc/rc.local
    # To start the mysql instance boot since 3306 
    /usr/local/mysql/bin/mysqld_multi start 1
    # To start the mysql instance boot since 3307 
    /usr/local/mysql/bin/mysqld_multi start 2


    7、mysql实例部署之后的安全优化

    实例刚刚部署完之后的安全优化,主要有两方面:

    1、为root设置密码,由于我们在前面已经设置过了,这里就不再设置了

    2、我们要查看mysql默认的用户,并将多余账户删除掉

    这里仅仅以,端口为3306的mysql实例安全优化为例,做演示:

    [root@nolinux ~]# mysqladmin -uroot password 'sunsky' -S /mydata/3308/mysql.sock
    [root@nolinux ~]# mysql -uroot -psunsky -S /mydata/3306/mysql.sock << EOF 
    > drop database test; 
    > show databases; 
    > delete from mysql.user where user='root' and host='::1'; 
    > delete from mysql.user where user='' and host='localhost'; 
    > delete from mysql.user where user='' and host='nolinux'; 
    > delete from mysql.user where user='root' and host='nolinux'; 
    > select user,host from mysql.user; 
    > EOF 
    Database 
    information_schema 
    mysql 
    performance_schema 
    user	host 
    root	127.0.0.1 
    mysql	localhost 
    root	localhost

    以上就是基于mysqld_multi方案的mysql多实例部署的整个完整操作了!希望能对大家有所帮助!下面补充一个,在现有基于mysqld_multi的mysql多配置文件环境下,增加实例的操作!


    增加mysql实例案例

    增加一个mysql实例

    1、建立新实例对应的目录并授权

    [root@nolinux ~]# mkdir -p /mydata/3308/data
    [root@nolinux ~]# cp /mydata/3306/my.cnf /mydata/3308/
    [root@nolinux ~]# ll -d /mydata/3308 
    drwxr-xr-x. 3 mysql mysql 4096 Jun 4 00:35 /mydata/3308

    2、更改my.cnf文件为3308专用

    [root@nolinux ~]# cat >> /etc/my.cnf << EOF
    [mysqld3]
    port = 3308
    server-id = 3
    socket = /mydata/3308/mysql.sock
    pid-file = /mydata/3308/mysql.pid
    datadir = /mydata/3308/data
    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
    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
    EOF


    3、初始化端口为3308的mysql实例的数据文件

    [root@nolinux ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/3308/data --user=mysql 
    WARNING: The host 'nolinux' could not be looked up with resolveip. 
    This probably means that your libc libraries are not 100 % compatible 
    with this binary MySQL version. The MySQL daemon, mysqld, should work 
    normally with the exception that host name resolving will not work. 
    This means that you should use IP addresses instead of hostnames 
    when specifying MySQL privileges ! 
    Installing MySQL system tables... 
    OK 
    Filling help tables... 
    OK 
    
    To start mysqld at boot time you have to copy 
    support-files/mysql.server to the right place for your system 
    
    PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 
    To do so, start the server, then issue the following commands: 
    
    /usr/local/mysql/bin/mysqladmin -u root password 'new-password' 
    /usr/local/mysql/bin/mysqladmin -u root -h nolinux password 'new-password' 
    
    Alternatively you can run: 
    /usr/local/mysql/bin/mysql_secure_installation 
    
    which will also give you the option of removing the test 
    databases and anonymous user created by default. This is 
    strongly recommended for production servers. 
    
    See the manual for more instructions. 
    
    You can start the MySQL daemon with: 
    cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe & 
    
    You can test the MySQL daemon with mysql-test-run.pl 
    cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl 
    
    Please report any problems at http://bugs.mysql.com/


    4、启动测试

    [root@nolinux ~]# /usr/local/mysql/bin/mysqld_multi start 3
    [root@nolinux ~]# lsof -i tcp:3308 
    COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
    mysqld 38438 mysql 14u IPv4 86554 0t0 TCP *:mysql (LISTEN)
    [root@nolinux ~]# netstat -lntup |grep 3308 
    tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 38438/mysqld


    5、关闭测试

    [root@nolinux ~]# /usr/local/mysql/bin/mysql
     
    -S /mydata/3308/mysql.sock -e "grant shutdown on *.* to 'mysql'@'localhost' identified by 'sunsky'
    [root@nolinux ~]# /usr/local/mysql/bin/mysqld_multi stop 3
    [root@nolinux ~]# netstat -lntup | grep 3308


    6、配置多实例的开机自启动

    [root@nolinux ~]# echo '# To start the mysql instance boot since 3308' >> /etc/rc.local 
    [root@nolinux ~]# echo '/usr/local/mysql/bin/mysqld_multi start 3' >> /etc/rc.local 
    [root@nolinux ~]# tail -2 /etc/rc.local
    # To start the mysql instance boot since 3308 
    /usr/local/mysql/bin/mysqld_multi start 3


    7、安全优化

    [root@nolinux ~]# mysqladmin -uroot password 'sunsky' -S /mydata/3308/mysql.sock
    [root@nolinux ~]# mysql -uroot -psunsky -S /mydata/3308/mysql.sock << EOF 
    > drop database test; 
    > show databases; 
    > delete from mysql.user where user='root' and host='::1'; 
    > delete from mysql.user where user='' and host='localhost'; 
    > delete from mysql.user where user='' and host='nolinux'; 
    > delete from mysql.user where user='root' and host='nolinux'; 
    > select user,host from mysql.user; 
    > EOF 
    Database 
    information_schema 
    mysql 
    performance_schema 
    user	host 
    root	127.0.0.1 
    mysql	localhost 
    root	localhost


    OK!

    上面就是在现有基于mysqld_multi的mysql多配置文件环境下,增加实例的操作!


关键字