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);