mysql多实例（mysqld_multi方式）

以下内容为在学习MYSQL 过程中的实验步骤，

mysql多实例（mysqld_multi方式）

1.建立各个实例的目录

    [root@mdw ~]# mkdir /mnt/data/mysql/3306 /mnt/data/mysql/3307 /mnt/data/mysql/3308 -p
    [root@mdw ~]# chown -R mysql:mysql /mnt/data/mysql/3306 /mnt/data/mysql/3307 /mnt/data/mysql/3308
    [root@mdw ~]#

2.初始化各个实例

    /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mnt/data/mysql/3306 --user=mysql
    /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mnt/data/mysql/3307 --user=mysql
    /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mnt/data/mysql/3308 --user=mysql

3.修改配置，my.cnf 配置文件修改后如下：

    [root@mdw ~]# cat /etc/my.cnf
    [mysqld_multi]
    mysqld = /usr/local/mysql/bin/mysqld_safe
    mysqladmin = /usr/local/mysql/bin/mysqladmin

    user = root
    password = sa123

    [mysqld3306]
    datadir = /mnt/data/mysql/3306
    port = 3306
    socket = /tmp/mysql3306.sock

    log-bin = /mnt/data/mysql/3306/mysql-bin
    pid-file = /mnt/data/mysql/3306/mysql_3306.pid
    log-error=/var/log/3306.log


    [mysqld3307]
    datadir = /mnt/data/mysql/3307
    port = 3307
    socket = /tmp/mysql3307.sock

    log-bin = /mnt/data/mysql/3307/mysql-bin
    pid-file = /mnt/data/mysql/3307/mysql_3307.pid
    log-error=/var/log/3307.log


    [mysqld3308]
    datadir = /mnt/data/mysql/3308
    port = 3308
    socket = /tmp/mysql3308.sock

    log-bin = /mnt/data/mysql/3308/mysql-bin
    pid-file = /mnt/data/mysql/3308/mysql_3308.pid
    log-error=/var/log/3308.log



    #[mysqld]
    #datadir=/usr/local/mysql/data
    #socket=/usr/local/mysql/data/mysql.sock
    #user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    #symbolic-links=0

    #[mysqld_safe]
    #log-error=/var/log/mysqld.log
    #pid-file=/var/run/mysqld/mysqld.pid

    #[client]
    #socket=/usr/local/mysql/data/mysql.sock
    [root@mdw ~]#

4.启动 3306,3307,3308 各端口的mysql 实例

    [root@mdw ~]# /usr/local/mysql/bin/mysqld_multi start 3306
    [root@mdw ~]# /usr/local/mysql/bin/mysqld_multi start 3307
    [root@mdw ~]# /usr/local/mysql/bin/mysqld_multi start 3308


    #error 一个错误处理
    [root@mdw ~]# /usr/local/mysql/bin/mysqld_multi start 3306
    WARNING: my_print_defaults command not found.
    Please make sure you have this command available and
    in your path. The command is available from the latest
    MySQL distribution.
    ABORT: Can't find command 'my_print_defaults'.
    This command is available from the latest MySQL
    distribution. Please make sure you have the command
    in your PATH.

    在.bash_profile 添加路径：

        MYSQL_PATH=/usr/local/mysql/bin;
        PATH=$PATH:$HOME/bin:$MYSQL_PATH
        
        export PATH

    或者直接命令行输入：export PATH=/usr/local/mysql/bin:$PATH

/usr/local/mysql/bin/mysqladmin -u root -h mdw password 'new-password'

5.查看端口状态，看是否正常运行

    [root@mdw ~]# netstat -anp | grep 3308
    tcp        0      0 :::3308                     :::*                        LISTEN      31535/mysqld        
    unix  2      [ ACC ]     STREAM     LISTENING     496621 31535/mysqld        /tmp/mysql3308.sock
    [root@mdw ~]# netstat -anp | grep 3307
    tcp        0      0 :::3307                     :::*                        LISTEN      31372/mysqld        
    unix  2      [ ACC ]     STREAM     LISTENING     496593 31372/mysqld        /tmp/mysql3307.sock
    [root@mdw ~]# netstat -anp | grep 3306
    tcp        0      0 :::3306                     :::*                        LISTEN      31211/mysqld        
    unix  2      [ ACC ]     STREAM     LISTENING     496460 31211/mysqld        /tmp/mysql3306.sock
    [root@mdw ~]#

6.初始化密码

    6.1 实例：3306,
    /usr/local/mysql/bin/mysqladmin -u root password "sa123" -S /tmp/mysql3306.sock


    /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3306.sock
    grant all privileges on  *.* to 'root'@'127.0.0.1' identified by 'sa123' with grant option;
    flush privileges;

    /usr/local/mysql/bin/mysqladmin -u root password "saPsd" -S /tmp/mysql3306.sock


    实例：3307
    /usr/local/mysql/bin/mysqladmin -u root password "sa123" -S /tmp/mysql3307.sock
    /usr/local/mysql/bin/mysql -uroot -psa123 -S  /tmp/mysql3307.sock
    grant all privileges on  *.* to 'root'@'127.0.0.1' identified by 'sa123' with grant option;
    flush privileges;

    实例：3308
    /usr/local/mysql/bin/mysqladmin -u root password "sa123" -S /tmp/mysql3308.sock
    /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3308.sock
    grant all privileges on  *.* to 'root'@'127.0.0.1' identified by 'sa123' with grant option;
    flush privileges;


    操作明细：
    [root@mdw ~]# /usr/local/mysql/bin/mysqladmin -u root password "sa123" -S /tmp/mysql3306.sock
    Warning: Using a password on the command line interface can be insecure.

    [root@mdw ~]# /usr/local/mysql/bin/mysql -uroot -psa123 -S  /tmp/mysql3306.sock
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.6.35-log Source distribution

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> grant all privileges on  *.* to 'root'@'127.0.0.1' identified by 'sa123' with grant option;
    Query OK, 0 rows affected (0.02 sec)

    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)

    mysql>




参考：

http://blog.csdn.net/clevercode/article/details/47659457