MySQL学习笔记 主从复制

主机IP:192.168.233.130
从机IP:192.168.233.132

一、主机操作

1.主机配置

cd /usr/local/mysql
vi my.cnf

[mysqld]
#bin-log
log-bin=mysql-bin
server-id=1
#保证网络连接
skip_networking=0

保存退出后重启服务
service mysqld restart

##创建测试数据库并备份

mysql -uroot -p123
create database db_test_rep;
use db_test_rep;
create table tb_test_rep 
(
t_id int not null primary key auto_increment,
t_s_id char(10) not null,
t_data varchar(50) not null
);

insert into tb_test_rep (t_s_id,t_data)
values (20200118,'hello world'),
(20121221,'say hello world');

select * from tb_test_rep;

2.创建用于复制的用户

create user 'repl'@'192.168.233.%';
grant replication slave on *.* to 'repl'@'192.168.233.%' identified by '123456';
flush privileges;
quit;

mkdir -p /backup/mysql
cd /backup/mysql
mysqldump --master-data --single-transaction --all-databases -uroot -p123 > master_all_databases_`date +%Y%m%d`.sql

#获取bin-log file 和 pos 信息
head -n 22  master_all_databases_20200118.sql	

#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;

scp master_[tab] root@192.168.233.132:/backup/mysql/	#从机创建目录 mkdir -p /backup/mysql

3.记录rep信息：

master_host=‘192.168.233.130’,
master_user=‘repl’,
master_password=‘123456’,
master_log_file=‘mysql-bin.000002’,
master_log_pos=154;

二、从机操作

1.从机配置

[mysqld]
#relay-log
relay_log=mysql-relay

server_id=2

保存退出后重启服务

2.从机操作

cd /backup/mysql
mysql -uroot -p123 < master_[tab]

mysql -uroot -p123

mysql>change master to
    master_host='192.168.233.130',
    master_user='repl',
    master_password='123456',
    master_log_file='mysql-bin.000002',
    master_log_pos=154;

mysql> start slave;
mysql>show slave status\G