美文网首页
实践:mysql单机多实例部署(mac)

实践:mysql单机多实例部署(mac)

作者: Alice_299f | 来源:发表于2020-08-24 14:07 被阅读0次

    背景:在自己电脑搭建或测试分布式服务框架时,经常会用多个数据库实例模拟多个环境的情况,因此我把搭建多实例mysql的过程记录下来,方便互相学习和沟通。

    1.搭建环境

    1) mac 电脑,版本 10.15.6

    2) mysql版本  8.0.20

    官网下载:https://downloads.mysql.com/archives/community/

    2.搭建过程如下

    2.1 步骤

    1) 官网下载mysql安装tar包,注意dmg不行

    mysql-8.0.20-macos10.15-x86_64.tar.gz

    2)解压及创建mysql包路径

    ```

    tar-xfv mysql-8.0.20-macos10.15-x86_64.tar.gz

     cp -rf mysql-8.0.20-macos10.15-x86_64/*  /usr/local/mysql8.0/

    ```

    3)创建mysql数据文件存放路径

    mkdir -p  /usr/local/mysql8.0/{3306,3307,3308,3309}/data

    ##授权用户组mysql的权限

    chown -R mysql:mysql  /usr/local/mysql8.0

    ##创建日志文件

    touch /usr/local/mysql8.0/mysqld_multi.log

    4)编辑脚本 my.cnf

    vim /etc/my.cnf

    ###文件内容如下

    [mysqld]

    user=mysql

    basedir =/usr/local/mysql8.0

    datadir=/usr/local/mysql8.0/data

    [mysqld_multi]

    mysqld=/usr/local/mysql8.0/bin/mysqld_safe

    mysqladmin=/usr/local/mysql8.0/bin/mysqladmin

    log=/usr/local/mysql8.0/mysqld_multi.log

    [mysqld3306]

    mysqld=mysqld

    mysqladmin=mysqladmin

    datadir=/usr/local/mysql8.0/3303/data

    port=3306

    user=mysql

    server_id=3306

    socket=/tmp/mysql_3306.sock

    log-error =/usr/local/mysql8.0/3306/error_3306.log

    [mysqld3307]

    mysqld=mysqld

    mysqladmin=mysqladmin

    datadir=/usr/local/mysql8.0/3307/data

    port=3307

    user=mysql

    server_id=3307

    socket=/tmp/mysql_3307.sock

    log-error =/usr/local/mysql8.0/3307/error_3307.log

    [mysqld3308]

    mysqld=mysqld

    mysqladmin=mysqladmin

    datadir=/usr/local/mysql8.0/3308/data

    port=3308

    user=mysql

    server_id=3308

    socket=/tmp/mysql_3308.sock

    log-error =/usr/local/mysql8.0/3308/error_3308.log

    [mysqld3309]

    mysqld=mysqld

    mysqladmin=mysqladmin

    datadir=/usr/local/mysql8.0/3309/data

    port=3309

    user=mysql

    server_id=3309

    socket=/tmp/mysql_3309.sock

    log-error =/usr/local/mysql8.0/3309/error_3309.log

    5)分别初始化3306、3307、3308、3309端口,注意,初始化实例的最后一行记录了root的初始密码

    /usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3307/data

    /usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3308/data

    /usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3309/data

    6)设置环境变量

    vim ~/.bash_profile

    ##添加如下配置

    export PATH=/usr/local/mysql/bin:$PATH

    ##生效配置

    source  ~/.bash_profile

    7) mysql命令创建别名,不然后面这三个命令识别不出来 会报错

    alias mysqld=/usr/local/mysql8.0/bin/mysqld

    alias mysqld_multi=/usr/local/mysql8.0/bin/mysqld_multi

    alias mysqladmin=/usr/local/mysql8/bin/mysqladmin

    8) 启动实例

    mysqld_multi --defaults-extra-file=/etc/my.cnf --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql start 3306

    mysqld_multi --defaults-extra-file=/etc/my.cnf  --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql  start 3307

    mysqld_multi --defaults-extra-file=/etc/my.cnf  --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql  start 3308

    mysqld_multi --defaults-extra-file=/etc/my.cnf  --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql  start 3309

    9)验证实例

    mysqld_multi --defaults-extra-file=/etc/my.cnf report

    2.2 修改密码

    详见:

    https://blog.csdn.net/lkr_lkr/article/details/105931513

    ##先登录

    mysql -u root -p -S /tmp/mysql_3307.sock

    ##修改密码

    ALTER USER USER() IDENTIFIED BY '123456';

    exit;

    ##验证

    附录图:

    2.3 客户端连接问题

    1.出现Authentication plugin 'caching_sha2_password' 错误时,是因登录密码级别设置的高了。

    2.解决方法:

    1)管理员权限运行命令提示符,登陆MySQL

    2)修改账户密码加密规则并更新用户密码

       ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;   #修改加密规则 

       ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';   #更新一下用户的密码 

    3)刷新权限并重置密码

       FLUSH PRIVILEGES;   #刷新权限 

      alter user 'root'@'localhost' identified by '123456'; #再重置下密码

    4)现在再次打开Navicat Premium 12连接MySQL问题数据库就会发现可以连接成功了

    详见:https://blog.csdn.net/u011182575/article/details/80821418

    相关文章

      网友评论

          本文标题:实践:mysql单机多实例部署(mac)

          本文链接:https://www.haomeiwen.com/subject/urxfnftx.html