开发者

mysql搭建主从复制的实现步骤

目录
  • 1、准备主服务器
  • 2、准备从服务器
  • 3、主库配置
    • 3.1、创建mysql主服务器配置文件:
  • 4、从库配置
    • 5、搭建主从&测试
      • 5.1、使用命令行登录MySQL主服务器
      • 5.2、主机中查询master状态:
      • 5.3、从机中查询slave状态:
      • 5.4、主机中创建slave用户:
      • 5.5、在从机上配置主从关系:
      • 5.6、启动从库的io和sql线程:都启动成功主从才搭建成功
    • 6、在3306主机上创建mydb1
      • 7、在3306主机上创建myDB2
        • 8、在3306主机上创建mydb3
          • 9、在3306主机上创建mydb4

            主库更新,从库会同步更新。从库更新,主库一般是不会同步更新的,如果发生主库也同步更新,可能出现短暂bug,或者主从配置有问题。mysql集群:

            单台设备的负载压力:主从复制

            集群:分摊访问压力和存储压力

            需求:使用 3306 mysql当作主, 3316 mysql 当作从,在3306中对 mydb2/mydb3 数据库所有的操作,希望能够主从复制同步到3316,其他的数据库操作不同步。

            1、准备主服务器

            docker run -d \
            --name spzx-mysql \
            -p 3306:3306 \
            -v mysql_data:/var/lib/mysql \
            -v mysql_conf:/etc/mysql \
            --restart=always \
            --privileged=true \
            -e MYSQL_ROOT_PASSWORD=123456 \
            mysql:8
            
            [root@localhost ~]# docker ps
            CONTAINER ID   IMAGE                       COMMAND                   CREATED        STATUS      PORTS                                                                                  
            ab66508d9441   mysql:8                     "docker-entrypoint.s…"   8 months ago   Up 9 days   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp                                   spzx-mysql
            

            此时我已经有一个主服务器 spzx-mysql

            2、准备从服务器

            docker run -d \
            -p 3316:3306 \
            -v mysql-slave1-conf:/etc/mysql/conf.d \
            -v mysql-slave1-data:/var/lib/mysql \
            -e MYSQL_ROOT_PASSWORD=123456 \
            --name atguigu-mysql-slave1 \
            mysql:8
            
            [root@localhost ~]# docker ps
            CONTAINER ID   IMAGE                       COMMAND                   CREATED          STATUS         PORTS                                                                                  NAMES
            c236f876ae40   mysql:8                     "docker-entrypoint.s…"   10 seconds ago   Up 3 seconds   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp                                   atguigu-mysql-slave1
            ab66508d9441   mysql:8                     "docker-entrypoint.s…"   8 months ago     Up 9 days      0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp                                   spzx-mysql
            

            3、主库配置

            • 先在主mysql中配置 记录mydb2/mydb3库的操作日志到binlog日志文件中

              – 主库写操作会按照配置记录到二进制文件中(binlog)

              – 主库需要创建一个从账户并分配可以读取binlog日志的权限

            • 在从mysql中配置中继日志文件,用来保存读取到的mysql主的 binlog 日志

              – 从库可以开启主从复制,从指定的主库的binlog文件中加载日志缓存到自己的relaylog文件中,最后通过一个sql线程将relaylog文件中的日志replay到自己的库表中

              – 从库需要使用主库提供的账号和主库的binlog文件建立连接

            3.1、创建MySQL主服务器配置文件:

            [root@localhost ~]# docker inspect spzx-mysql 
            
                    "Mounts": [
                        {
                            "Type": "volume",
                            "Name": "mysql_conf",
                            "Source": "/var/lib/docker/volumes/mysql_conf/_data",
                            "Destination": "/etc/mysql",
                            "Driver": "local",
                            "Mode": "z",
                            "RW": true,
                            "Propagation": ""
                        },
                        {
                            "Type": "volume",
                            "Name": "mysql_data",
                            "Source": "/var/lib/docker/volumes/mysql_data/_data",
                            "Destination": "/var/lib/mysql",
                            "Driver": "local",
                            "Mode": "z",
                            "RW": true,
                            "Propagation": ""
                        }
                    ],
            
            [root@localhost _data]# cd /var/lib/docker/volumes/mysql_conf/_data
            [root@localhost _data]# ll
            总用量 8
            drwxrwxr-x. 2 root root   41 12月 26 2023 conf.d
            -rw-rw-r--. 1 root root 1080 12月 21 2021 my.cnf
            -rw-r--r--. 1 root root 1448 9月  28 2021 my.cnf.fallback
            
            [root@localhost _data]# vim my.cnf
            

            配置如下内容:

            [mysqld]
            # 服务器唯一id,默认值1
            server-id=1
            # 设置日志格式,默认值ROW。row(记录行数据)  statement(记录sql)  mixed(混合模式)
            binlog_format=STATEMENT
            # 二进制日志名,默认binlog
            # log-bin=binlog
            log-bin=spzxbinlog
            # 设置需要复制的数据库,默认复制全部数据库
            binlog-do-db=mydb2
            binlog-do-db=mydb3
            # 设置不需要复制的数据库
            binlog-ignore-db=mydb4
            #binlog-ignore-db=infomation_schema
            

            mysql搭建主从复制的实现步骤

            [root@localhost _data]# docker restart spzx-mysql
            spzx-mysql
            
            [root@localhost _data]# ll ../../mysql_data/_data/
            

            mysql搭建主从复制的实现步骤

            4、从库配置

            [root@localhost _data]# docker inspect atguigu-mysql-slave1 
            

            mysql搭建主从复制的实现步骤

            vim /var/lib/docker/volumes/mysql-slave1-conf/_data/my.cnf
            

            配置如下内容:

            [mysqld]
            # 服务器唯一id,每台服务器的id必须不同,如果配置其他从机,注意修改id
            server-id=2
            # 中继日志名,默认xxxxxxxxxxxx-relay-bin
            #relay-log=relay-bin
            

            mysql搭建主从复制的实现步骤

            [root@localhost _data]# docker restart atguigu-mysql-slave1 
            atguigu-mysql-slave1
            

            5、搭建主从&测试

            5.1、使用命令行登录MySQL主服务器

            [root@localhost _data]# docker exec -it spzx-mysql /bin/bash
            root@ab66508d9441:/# mysql -uroot -p123456
            mysql: [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 8
            Server version: 8.0.27 MySQL Community Server - GPL
            
            Copyright (c) 2000, 2021, oracle and/or its affiliates.
            
            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.
            
            

            5.2、主机中查询master状态:

            mysql> show master status;
            +-------------------+----------+--------------+------------------+-------------------+
            | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +-------------------+----------+--------------+------------------+-------------------+
            | spzxbinlog.000001 |      156 | mydb2,mydb3  | mydb4            |                   |
            +-------------------+----------+--------------+------------------+-------------------+
            1 row in set (0.01 sec)
            

            5.3、从机中查询slave状态:

            [root@localhost ~]# docker exec -it atguigu-mysql-slave1 /bin/bash
            ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
            root@c236f876ae40:/# mysql -uroot -p123456
            mysql: [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 9
            Server version: 8.0.27 MySQL Community Server - GPL
            
            Copyright (c) 2000, 2021, Oracle and/or its affiliates.
            
            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> 
            
            mysql> show slave status;
            Empty set, 1 warning (0.02 sec)
            

            从库必须和主库主动建立连接 开启自己的sql和io线程

            5.4、主机中创建slave用户:

            -- 创建slave用户
            CREATE USER 'atguigu_slave'@'%';
            -- 设置密码
            ALTER USER 'atguigu_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
            -- 授予复制权限
            GRANT REPLICATION SLAVE ON *.* TO 'atguigu_slave'@'%';
            -- 刷新权限
            FLUSH PRIVILEGES;
            
            mysql> show master status;
            +-------------------+----------+--------------+------------------+-------------------+
            | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +-------------------+----------+--------------+------------------+-------------------+
            | spzxbinlog.000001 |     1074 | mydb2,mydb3  | mydb4            |                   |
            +-------------------+----------+--------------+------------------+-------------------+
            1 row in set (0.00 sec)
            

            5.5、在从机上配置主从关系:

            CHANGE androidMASTER TO MASTER_HOST='192.168.74.148', 
            MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
            MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074; 
            
            mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.148', 
                -> MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
                -> MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074; 
            Query OK, 0 rows affected, 9 warnings (0.05 sec)
            
            mysql> show slave status;
            +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            | Slave_IO_State | Master_Host    | Master_User   | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Masterjavascript_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
            +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            |                | 192.168.74.148 | atguigu_slave |        3306 |            60 | spzxbinlog.000001 |                1074 | c236f876ae40-relay-bin.000001 |             4 | spzxbinlog.000001     | No               | No                |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                1074 |             156 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL |                         |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
            +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            1 row in set, 1 warning (0.00 sec)
            

            mysql搭建主从复制的实现步骤

            5.6、启动从库的io和sql线程:都启动成功主从才搭建成功

            mysql> start slave;
            Query OK, 0 rows affected, 1 warning (0.03 sec)
            
            mysql> show slave status;
            +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            | Slave_IO_State                   | Master_Host    | Master_User   | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                  | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
            +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+----------------------php---+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            | Waiting for source to send event | 192.168.74.148 | atguigu_slave |        3306 |            60 | spzxbinlog.000001 |                1074 | c236f876ae40-relay-bin.000002 |           325 | spzxbinlog.000001     | Yes              | Yes               |                 |                     |                    |          javascript              |                         |                             |          0 |            |            0 |                1074 |             541 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | af98f4d4-a3ca-11ee-b194-0242ac110002 | mysql.slave_master_info |         0 |                NULL | Replica has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
            +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-----------------------------http://www.devze.com--+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
            1 row in set, 1 warning (0.01 sec)
            

            mysql搭建主从复制的实现步骤

            6、在3306主机上创建mydb1

            mysql搭建主从复制的实现步骤

            mysql搭建主从复制的实现步骤

            此时刷新3316从数据库,发现没有mydb1

            7、在3306主机上创建mydb2

            mysql搭建主从复制的实现步骤

            此时刷新3316从数据库,发现从机复制了主机中的mydb2数据库到从机中

            8、在3306主机上创建mydb3

            mysql搭建主从复制的实现步骤

            9、在3306主机上创建mydb4

            mysql搭建主从复制的实现步骤

            到此这篇关于mysql搭建主从复制的实现步骤的文章就介绍到这了,更多相关mysql搭建主从复制内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)! 

            0

            上一篇:

            下一篇:

            精彩评论

            暂无评论...
            验证码 换一张
            取 消

            最新数据库

            数据库排行榜