mysql主从复制&读写分离

当存在用户并发时，不仅应用层有压力，数据库也会有压力，这时候需要用到mysql 的主从复制功能，主从复制可以用到mysql集群部署和读写分离。如下图，我们可以用一台服务器作为主服务器，进行增删改操作，多台服务站作为从服务器，由于查询远远大于修改的量，所以主服务器的数量可以少于从服务器。接下来进行配置。

预先准备两台实验机器，为了方便，本次使用docker容器

docker run -it  --name mysql57 -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7

docker run -it  --name mysql57-2 -e MYSQL_ROOT_PASSWORD=123456 -p 3307:3306 -d mysql:5.7

172.17.0.2    主

172.17.0.3    从

首先我们要弄清楚其中原理，主服务器进行操作时会生成binlog，从服务通过读取主服务器的log进行同样操作，实现主从服务。

vi /etc/mysql/my.conf.d/mysqld.conf

   [master]服务器配置：

    #主服务器唯一ID

    server-id=1

    #启用二进制日志

    log-bin=mysql-bin

    # 设置不要复制的数据库(可设置多个)

    binlog-ignore-db=mysql

    binlog-ignore-db=information_schema

    #设置需要复制的数据库

    binlog-do-db=studentdb

    #设置logbin格式

    binlog_format=STATEMENT

    

   [slave]服务器配置：

     

    #从服务器唯一ID

    server-id=2

    #启用中继日志

    relay-log=mysql-relay

   #在主机MySQL里执行授权命令

   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

   #查询master的状态,File为日志文件名

   show master status;

mysql>  show master status;

+------------------+----------+--------------+--------------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------+-------------------+

| mysql-bin.000001 |     1009 | studentdb    | mysql,information_schema |                   |

+------------------+----------+--------------+--------------------------+-------------------+

1 row in set (0.00 sec)

   [在从机上配置需要复制的主机]

    #复制主机的命令

    CHANGE MASTER TO MASTER_HOST='主机ip',

    MASTER_USER='主服务开启的账号名',

    MASTER_PASSWORD='密码',

    MASTER_LOG_FILE=‘日志文件名',MASTER_LOG_POS=行数，从哪一行开始复制;

    

    #启动从服务器复制功能

    start slave;

    #查看从服务器状态

    show slave status\G;

mysql>     show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.17.0.2

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 1009

               Relay_Log_File: mysql-relay.000002

                Relay_Log_Pos: 1175

        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: 1009

              Relay_Log_Space: 1378

              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: 1

                  Master_UUID: be8259a9-15c7-11ec-8f76-0242ac110005

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           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

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

这时候已经配置成功了，马上进行测试，在主服务器上执行

mysql> show create database studentdb;

+-----------+--------------------------------------------------------------------+

| Database  | Create Database                                                    |

+-----------+--------------------------------------------------------------------+

| studentdb | CREATE DATABASE `studentdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+-----------+--------------------------------------------------------------------+

1 row in set (0.00 sec)

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                                                                                                                    |

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student | CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> insert into student(name) value('andy')

    -> ;

Query OK, 1 row affected (0.01 sec)

然后查看从库，发现数据已生成，测试成功！！！