MySQL的主从复制!!!
1、主从复制
在第一台创建库,表,插入数据
create database mytest;
create table test(id int primary key auto_increment,name varchar(30));
insert into test(name) values("zhang"),("li");
把之前的所有数据导入到一个位置:mysqldump -uroot -p123 -A > /tmp/all.sql
然后复制到第二台服务器上:scp /tmp/all.sql 192.168.179.139:/tmp
第二台服务器: mysql -uroot -p123 < /tmp/all.sql
数据同步完成之后做主从复制:
先开启二进制日志: show varibles like '%log%'; vim /etc/my.cnf
log_bin=mysql-bin
server-id=1
重启服务器
授权:show privileges;
grant replication slave on *.* to zhao@'192.168.179.%' identified by '123'; (主从复制的权限)
开启从服务器的中继日志和server-id
vim /etc/my.cnf
server-id=2
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index 重启服务器
确定从哪个位置读取二进制日志 show master status;
设置从服务器:change master to master_host='192.168.179.134',master_user='zhao',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1265;
开启主服务器的防火墙端口:firewall-cmd --add-port=3306/tcp
firewall-cmd --add-port=3306/tcp --permanent
修改或者删除uuid: rm -rf /usr/local/mysql/data/auto.cnf (克隆的虚拟机会出现)重启
从服务器启动slave: start slave; 查看一下show slave status\G
停止主从复制:stop slave;
2、基于ssl的方式做主从复制
在主服务器生成证书:
mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
添加读的权限:chmod +r server-key.pem
重启
查看:show variable like '%ssl%';
给权限:grant replication slave on *.* to zhao@'192.168.179.%' identified by '123' require ssl;
开启二进制日志:(上个主从复制已经做过)
生成的证书传到从服务器:scp ca.pem client-cert.pem client-key.pem 192.168.179.139:/usr/local/mysql/data
chmod +r server-key.pem
修改从主配文件:vim /etc/my.cnf
ssl-ca=/usr/local/mysql/data/ca.pem
ssl-cert=/usr/local/mysql/data/client-cert.pem
ssl-key=/usr/local/mysql/data/client-key.pem
重启
先测试:mysql -uzhao -p123 -h192.168.179.134 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
设置从服务器:change master to master_host='192.168.179.134',master_user='zhao',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=154,master_ssl=1,master_ssl_ca='/usr/local/mysql/data/ca.pem',master_ssl_cert='/usr/local/mysql/data/client-cert.pem',master_ssl_key='/usr/local/mysql/data/client-key.pem';
启动:start slave;
主服务器创建数据,从服务器查看