centos7搭建mysql主从复制

mysql主从复制

• 修改mysql master的配置文件

  • vim /etc/my.cnf

  #设置服务id
  server-id=1
  #启动binlog日志
  log-bin=mysql-bin
  binlog-ignore-db=mysql
  binlog-do-db=msb
  #binlog的格式
  binlog_format=statement
  
  [client]
  default-character-set=utf8
  

• 修改mysql salve的配置文件

  #设置服务id
  server-id=1
  relay-log=mysql-relay
  

  systemctl restart mysqld 重启master salve

• 给salve复制数据的权限

  grant replication slave on *.* to 'root'@'%' identified by '111111'
  如果出现 "Your password does not satisfy the current policy requirements"代表密码安全性不够级别，执行
  set global validate_password_policy=0;
  set global validate_password_length=0;
  

• 在slave上配置需要复制的主机

  • 查看master状态

    show master status;
    

    

#连接主服务器 
change master to master_host='192.168.10.130',master_user='root',master_password='111111',master_port=3306,master_log_file='mysql-bin.000005',master_log_pos=437;#master_log_file必须跟master的File一致,master_log_pos跟master的position对应
#如果报 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.则执行下面语句
SET PASSWORD PASSWORD('新密码')
#启动Slave
start slave;
#查看Slave状态
show slave status\G

如果显示上图所示，代表一主一从已经配置好了。

• 测试

  -- 在master上执行
  create table test_tb(id int primary key,name varchar(20));
  insert into test_db(id,name) values(1,'Jack');
  -- 在slave上执行
  select * FROM test_db;//数据已经存在
  insert into test_db(id,name) values(2,'Mary');
  -- 在master上执行
  select * FROM test_db;//id为2的那条数据不存在
  

  可以知道,master可以进行读写操作,而slave可以执行写操作，但是数据不会同步到master.

  -- master插入一条id为2的数据
  insert into test_db(id,name) values(2,"Lee");
  select * FROM test_db;//发现id=2，name='Lee'的数据不存在
  -- 查看日志
  tail -100 mysqld.log //发现slave thread状态已经是aborted了
  

这时再master中插入数据不再同步任何数据，所以必须要

drop table xxx（所有表）
stop slave;
-- 重新连接主服务器 master_log_file、position必须要填写最初始的file
change master to master_host='192.168.10.130',master_user='root',master_password='111111',master_port=3306,master_log_file='mysql-bin.000005',master_log_pos=437; 
reset slave;
start slave;

此时虽然数据同步了,但是全量同步非常的耗性能,而且每当出错 都需要重新全量同步一次。这在数据量大的时候是无法忍受的,而造成此问题的根本原因是slave也可以进行读的原因，因此为了解决这个问题，必须对数据库进行读写分离。

2.Mycat读写分离

mycat安装可以参考 https://blog.csdn.net/lihongtai/article/details/83824365

#编辑mycat配置文件schema.xml
vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="msb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                # writeHost配置master的ip
                <writeHost host="hostM1" url="192.168.10.130:3306" user="root"
                                   password="111111">
                                   #readHost配置slave的ip
                        <readHost host="hostS1" url="192.168.10.131:3306" user="root" password="111111"></readHost>
                </writeHost>
        </dataHost>

</mycat:schema>

#启动mycat 显示success表示成功
mycat console
#连接mycat
mysql -uroot -p111111 -h 192.168.10.130 -P 8066

显示上图代表mycat连接成功

查看数据库

use TESTDB;
show tables;
select * FROM mytbl;
-- 插入hostname区分查询的是那台数据库
insert into mytbl(2,@@hostname);