• MySQL 多实例管理


    根据配置文件创建目录

    [root@localhost redis]# mkdir -pv /data/mysql/mysql_3307/{data,logs,tmp}
    mkdir: created directory `/data/mysql/mysql_3307'
    mkdir: created directory `/data/mysql/mysql_3307/data'
    mkdir: created directory `/data/mysql/mysql_3307/logs'
    mkdir: created directory `/data/mysql/mysql_3307/tmp'
    [root@localhost redis]# mkdir -pv /data/mysql/mysql_3308/{data,logs,tmp}
    mkdir: created directory `/data/mysql/mysql_3308'
    mkdir: created directory `/data/mysql/mysql_3308/data'
    mkdir: created directory `/data/mysql/mysql_3308/logs'
    mkdir: created directory `/data/mysql/mysql_3308/tmp'

    修改配置文件(注意sock datadir  server_id  pid port的修改)

    [root@localhost redis]# cp /etc/my.cnf  /data/mysql/mysql_3307/
    [root@localhost redis]# cp /etc/my.cnf  /data/mysql/mysql_3308/ 
    [root@localhost redis]# sed -i 's/3376/3307/g' /data/mysql/mysql_3307/my.cnf 
    [root@localhost redis]# sed -i 's/3376/3308/g' /data/mysql/mysql_3308/my.cnf 

    创建多实例

    # ./scripts/mysql_install_db  --user=mysql --defaults-file=/data/mysql/mysql_3307/my.cnf --datadir=/data/mysql/mysql_3307/data/

    # ./scripts/mysql_install_db  --user=mysql --defaults-file=/data/mysql/mysql_3308/my.cnf --datadir=/data/mysql/mysql_3308/data/

    多实例启动方式

    多实例用一个配置文件可以用mysqld_multi启动

    # mysqld_multi  start 3307

    # mysqld_multi  start 3308

    mysqld --defaults-file=/data/mysql/mysql_3307/my.cnf &

    mysqld --defaults-file=/data/mysql/mysql_3308/my.cnf &

    mysqld_safe  --defaults-file=/data/mysql/mysql_3307/my.cnf  &

    mysqld_safe  --defaults-file=/data/mysql/mysql_3308/my.cnf  &

    连续启动

    #mysqld_mulit start 1-3

    #mysqld_mulit start 1,3,4,6

    # mysql -S /tmp/mysql3307.sock

    # mysql -S /tmp/mysql3308.sock

    多实例关闭

     

    [root@localhost data]# mysqladmin  --defaults-file=/data/mysql/mysql_3307/my.cnf  shutdown
    [1]-  Done                    mysqld --defaults-file=/data/mysql/mysql_3307/my.cnf
    [root@localhost data]# netstat -nalp | grep 3307 

    多实例共用一个配置文件可以用下面方法关闭

    #mysqladmin  -S /tmp/mysql3308.sock  shutdown
    #mysqladmin  -S /tmp/mysql3307.sock  shutdown

    多实例使用一个配置文件

    [mysqld_multi]
    mysqld = /usr/local/mysql/bin/mysqld_safe
    mysqladmin = /usr/local/mysql/bin/mysqladmin
    user       = mt_user
    password   = abcd123
                 
    [mysqld3307]
    #tee=/data/mysql/mysql_3307/data/query.log
    datadir = /data/mysql/mysql_3307/data
    socket = /tmp/mysql3307.sock
    tmpdir=/data/mysql/mysql_3307/tmp
    server-id = 123307
    log-bin = /data/mysql/mysql_3307/logs/mysql-bin
    innodb_data_home_dir = /data/mysql/mysql_3307/data
    innodb_log_group_home_dir = /data/mysql/mysql_3307/logs
        
    [mysqld3308]
    port            = 3308
    #tee=/data/mysql/mysql_3308/data/query.log
    datadir = /data/mysql/mysql_3308/data
    socket = /tmp/mysql3308.sock
    tmpdir=/data/mysql/mysql_3308/tmp
    server-id = 123308
    log-bin = /data/mysql/mysql_3308/logs/mysql-bin
    innodb_data_home_dir = /data/mysql/mysql_3308/data
    innodb_log_group_home_dir = /data/mysql/mysql_3308/logs
                                                              
    [root@localhost mysql_3307]# pkill mysql
    [root@localhost mysql_3307]# mysqld_multi  start 3307,3308
    [root@localhost mysql_3307]# netstat -nalp | grep mysql
    tcp        0      0 :::3308                     :::*                        LISTEN      9247/mysqld         
    tcp        0      0 :::3376                     :::*                        LISTEN      9231/mysqld         
    unix  2      [ ACC ]     STREAM     LISTENING     21545  9231/mysqld         /tmp/mysql3307.sock
    unix  2      [ ACC ]     STREAM     LISTENING     21554  9247/mysqld         /tmp/mysql3308.sock

     创建关闭数据库用户

    (product)root@localhost [(none)]> create user 'mt_user'@'localhost' identified by 'abc123';
    Query OK, 0 rows affected (0.00 sec)
    
    (product)root@localhost [(none)]> GRANT SHUTDOWN ON *.* TO 'mt_user'@'localhost'; 
    Query OK, 0 rows affected (0.00 sec)
    [root@localhost mysql_3307]# /usr/local/mysql/bin/mysqladmin -h localhost -S /tmp/mysql3307.sock -umt_user -pabc123 shutdown 
    Warning: Using a password on the command line interface can be insecure.
    [root@localhost mysql_3307]# netstat -nalp | grep 3307
    [root@localhost mysql_3307]# /usr/local/mysql/bin/mysqladmin -h localhost -S /tmp/mysql3308.sock -umt_user -pabc123 shutdown 
    Warning: Using a password on the command line interface can be insecure.
    [root@localhost mysql_3307]# 

     

  • 相关阅读:
    python 小兵(4)之文件操作 小问题
    python 小兵(4)之文件操作
    排序
    Java的数据结构
    基本数据操作
    部署tomcat到Linux
    找工作的一些知识积累
    BootStrap
    操作系统
    做Global Admin
  • 原文地址:https://www.cnblogs.com/weiwenbo/p/6640765.html
Copyright © 2020-2023  润新知