MySQL主从配置实现主从读写分离

Mysql主从简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步；主要作用有：①实时灾备，用于故障切换，②读写分离，提供查询服务，③备份，避免影响业务。

基本原理

mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取该日志文件来报错和从服务器的数据一致。
在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收该日志的一个副本，从服务器可以指定执行该日志的哪一类事件（例如只插入数据或者只更新数据），默认会执行日志中的所有语句
每一个从服务器会记录关于二进制的日志的信息：文件名和已处理过的语句，这就意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接
主服务器和每一个从服务器都必须配置一个唯一的id号（在my.cnf文件的 mysql模块下有一个server-id配置项），另外，每一个从服务器都必须通过CHANGE MASTER TO 语句来配置它连接的主服务器ip地址，日志文件名称和该日志文件的位置（这些信息都存储在主服务器的数据库里面）

主从同步的基本步骤

①在主服务器上，开启二进制日志机制和配置一个独立的ID
②在每一个从服务器上，配置一个唯一的id，并创建一个专门复制主服务器数据的账号
③开始复制之前，在主服务器上记录二进制文件的位置信息
④复制之前，创建数据库快照
⑤配置从服务器链接主服务的ip地址和登陆授权，二进制日志文件名和位置

详细的配置方法

①准备工作

搭建两台MYSQL服务器（版本一致），一台作为主服务器（本文在虚拟机 ubuntu中 ip 192.168.142.129 ），一台作为从服务器（windows中 ip 192.168.250.167）
在主服务器中创建一些测试数据：

--在主服务器上创建测试数据
create database test charset utf8;
create table  my_user(id int not null, name varchar(10) not null);
insert into my_user values(1,"li");
--备份主服务器上所有数据
 mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/back.sql;
--从服务器上还原所有数据
mysql -uroot -pmysql < back.sql

②配置主服务器的master

在朱服务器 ubuntu中的 设置mysql的配置文件 设置log_bin 和server-id

sudo vim  /etc/mysql/mysql.conf.d/mysqld.cnf

打开配置文件后 把83 行和 84行前面的#注释去掉即可 如下图：

完成后重启服务

sudo service mysql restart

在主服务器上创建同步权限账户（只有同步权限）：

grant replication slave on *.* to 'slave'@'%' identified by 'slave';
flush privileges;

查看主服务器状态：

show master status;

③配置从服务器

在从服务器中先进入mysql 然后 执行：

change master to master_host='192.168.142.129', master_user='slave', master_password='slave', master_log_file='mysql-bin.000003', master_log_pos=154;

④启动slave

start slave;
show slave status\G    #注意，没有分号

输出如下，显示两个都为yes即成功，可以测试一下：

mysql> show slave status;
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.142.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 593
               Relay_Log_File: mysql-relay-log.000004
                Relay_Log_Pos: 441
        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: 52360
              Relay_Log_Space: 597
              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

⑤去主服务器开启表只读锁

unlock tables;

文章参考：https://www.cnblogs.com/hellxz/p/8378601.html