原理
(1) 主从复制基于binlog来实现的
(2) 主库发生新的操作,都会记录binlog
(3) 从库取得主库的binlog进行回放
(4) 主从复制的过程是异步
修改配置文件
vim /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log_bin=qishi-logbin
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
重新主的mysql服务
systemctl restart mysqld
可以看到 /var/lib/mysql/ 这个文件多出来
qishi-logbin.000001
qishi-logbin.index
创建数据库
create database zabbix character set utf8 collate utf8_bin;
flush privileges;
创建用户
create user 'zabbix'@'%' identified by 'zabbix';
给从库账号授权,说明给zabbix从库复制的权限
grant replication slave on *.* to zabbix@'%' identified by "zabbix";
grant all privileges on zabbix.* to zabbix@"%" identified by "zabbix";
flush privileges;
show grants for zabbix@'%';
#导出数据
mysqldump -uroot -p123456 --all-databases >/opt/qishimaster.sql
#将数据传给从库
scp /opt/qishimaster.sql root@10.10.0.100:/opt/
show master status \G
File: qishi-logbin.000001
Position: 4113 #这个数值是slave配置master_log_pos的数值
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从库的操作
修改配置文件
vim /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server-id=3
readonly=true
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
配置复制的参数,Slave从库连接Master主库的配置
mysql -u root -p123456
source ~/qishimaster.sql
mysql > change master to
master_host=‘10.10.0.23’,
master_user=‘zabbix’,
master_password=‘zabbix’,
master_log_file=‘qishi-logbin.000003’,
master_log_pos=4113;
#启动从库的同步开关,测试主从复制的情况
start slave;
show slave status\G;