• 数据库主从+多实例


    数据库创建多实例+主从复制
    1.准备多个配置文件

    创建多个存放配置文件的目录

    [root@db02 ~]# mkdir /data/{13306,23306,33306} -p 
    
    [root@db02 ~]# tree /data/ 
    
    /data/ 
    
    ├── 13306 
    
    ├── 23306 
    
    └── 33306
    

    创建多个配置文件

    [root@db02 data]# cat 13306/my.cnf 
    [mysqld]
    basedir=/application/mysql
    datadir=/data/13306/data
    socket=/data/13306/data/mysql.sock
    port=13306
    log-bin=/data/13306/data/mysql-bin
    log_error=/data/13306/data/13306.err
    server_id=1
    pid_file=/data/13306/data/13306.pid
    
    --------------------------------------
    
    [root@db02 data]# cat 23306/my.cnf 
    [mysqld]
    basedir=/application/mysql
    datadir=/data/23306/data
    socket=/data/23306/data/mysql.sock
    port=23306
    log-bin=/data/23306/data/mysql-bin
    log_error=/data/23306/data/23306.err
    server_id=2
    pid_file=/data/23306/data/23306.pid
    
    ----------------------------------------
    
    
    
    
    
    初始化
    [root@db02 ~]# cd /application/mysql/scripts/ 
    
    #初始化13306的数据目录 
    
    [root@db02 scripts]#./mysql_install_db --defaults-file=/data/13306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/13306/data
    
    #23306
    
    [root@db02 scripts]# ./mysql_install_db --defaults-file=/data/23306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/23306/data
    #3306
    
    [root@db02 scripts]#. ./mysql_install_db --defaults-file=/data/33306/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3306/data
    
    
    
    
    [root@db02 scripts]# tree -L 2 /data 
    
    ├── 3307 
    
    │ ├── data 
    
    │ └── my.cnf 
    
    ├── 3308 
    
    │ ├── data 
    
    │ └── my.cnf 
    
    └── 3309 
    
    ├── data 
    
    └── my.cnf 
    
    
    启动
    [root@db02 scripts]#  --defaults-file=/data/3307/my.cnf & 
    
    [root@db02 scripts]#  --defaults-file=/data/3308/my.cnf & 
    
    [root@db02 scripts]#  --defaults-file=/data/3309/my.cnf & 
    
    检查端口
    [root@db01 data]# netstat -lntup|grep 330
    tcp6       0      0 :::3306                 :::*                    LISTEN      8480/mysqld         
    tcp6       0      0 :::23306                :::*                    LISTEN      8061/mysqld         
    tcp6       0      0 :::33306                :::*                    LISTEN      8399/mysqld         
    tcp6       0      0 :::13306                :::*                    LISTEN      7398/mysqld         
    
    设置密码
    [root@db02 scripts]# mysqladmin -uroot -p -S/data/13306/data/mysql.sock password '13306'
    [root@db02 scripts]# mysqladmin -uroot -p -S/data/23306/data/mysql.sock password '23306'
    [root@db01 scripts]# mysqladmin -uroot -p -S/data/33306/data/mysql.sock password '33306'
    
    
    连接MySQL
    [root@db02 scripts]# mysql -uroot -p13306 -S /data/13306/data/mysql.sock
    [root@db02 scripts]# mysql -uroot -p23306 -S /data/23306/data/mysql.sock
    [root@db02 scripts]# mysql -uroot -p33306 -S /data/33306/data/mysql.sock
    
    查看
    [root@db01 scripts]# mysql -uroot -p13306 -S /data/23306/data/mysql.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 3
    Server version: 5.6.44-log Source distribution
    
    Copyright (c) 2000, 2019, 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> show variables like 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 1     |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    

    测试成功了就可以做主从了

    在主的数据库设置以下

    [root@db01 data]# vim /etc/my.cnf
    开启日志:
     log_bin=mysql-bin
     打开端口
     设置server_id
     port = 3306
     server_id = 5
    
    打开主的数据打开
    #创建一个用户
    
    mysql> grant replication slave on *.* to rep@'10.0.0.51' identified by '123';
    Query OK, 0 rows affected (0.03 sec)
    #展示主数据库的状态
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      325 |              |                  |                   |
    +------------------+----------+--------------+------------------+----
    
    
    准备从数据库了
    打开从数据库
    #输入以下信息
    mysql> change master to
        ->  master_host='10.0.0.51',
        ->  master_user='rep',
        ->  master_password='123',
        ->  master_log_file='mysql-bin.000001',
        ->  master_log_pos=325,
        ->  master_port=3306;
    Query OK, 0 rows affected, 2 warnings (0.20 sec)
    #打开
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    #显示状态  看到两个yes 就可以了
    mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.51
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 325
                   Relay_Log_File: 13306-relay-bin.000002
                    Relay_Log_Pos: 283
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 325
                  Relay_Log_Space: 456
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 5
                      Master_UUID: 81b282ac-faf3-11e9-8745-000c29ce34f8
                 Master_Info_File: /data/13306/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
    1 row in set (0.00 sec)
    
    
    其他的从数据库也是一样的操作
    
    
    
    测试主从是否完成
    在主的的数据库创建一个用户,然后咋从的数据库上查看,就说明成功了
    
  • 相关阅读:
    测试开发工资为什么这么高?
    测试开发工程师技能图谱 V1.0 版 | 福利
    完成这 10+ 企业级项目实战,你也能进阶中高级测试开发
    我们准备了50000现金,给爱学习的你!手慢无~
    Git实战(四)| Git分支管理实操,搞定在线合并和本地合并
    公开课|互联网测试技术体系详解&职业发展规划
    测试面试 | 某互联网大厂测试面试真题,你能回答出多少?
    2020 中秋国庆,阖家快乐!
    测试面试 | 某BAT大厂测试开发面试真题与重点解析
    jsp_1
  • 原文地址:https://www.cnblogs.com/223zhp/p/11905895.html
Copyright © 2020-2023  润新知