admin · 7月7日 · 2020年
1.准备
- 主服务器:172.16.1.100
- 从服务器:172.16.1.101
- 2台服务器分别设置时间同步、安装mysql
yum install ntp -y
yum install mysql-5.2.0 -y
systemctl restart mysqld.service
2.主服务器
#配置/etc/my.cnf
[mysqld]
#开启二进制日志
log-bin=mysql-bin
#设置server-id,建议使用ip最后3位
server-id=100
cat >> /etc/my.cnf <<EOF
log-bin=mysql-bin
server-id=100
EOF
systemctl restart mysqld.service
3.从服务器
#开启中继日志
relay-log=mysql-relay
#设置server-id,建议使用ip最后3位
server-id=101
cat >> /etc/my.cnf <<EOF
relay-log=mysql-relay
server-id=101
EOF
systemctl restart mysqld.service
4.在主机上建立账户并授权slave
mysql>grant replication slave on *.* to 'slave'@'172.16.1.100' identified by '12345678';
mysql>flush privileges;
#锁表
mysql>flush tables with read lock;
#同步完成后,解除锁表
mysql>unlock tables;
#查询master的状态
mysql>show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1603
Binlog_Do_DB:
Binlog_Ignore_DB:
记录上面结果中File和Position的值
5.告知从服务器二进制文件名与位置
mysql> change master to master_host = '', #master的ip
->master_user = '', #在master中创建的slave账户
->master_password = '', #在master中创建的slave密码
->master_log_file = 'mysql-bin.000001', #填写master中的数据
->master_log_pos = ; #填写master中的数据
#开启slave模式
mysql>start slave;
#查看主从复制是否配置成功
mysql>show slave status\G
*********************以下为正常状态*************************
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.在master上测试是否同步
mysql>create datebase test;
*********************常用语句*************************
show master status: 查看master的状态,尤其是当前的日志及位置
show slave status 查看slave的状态
reset slave 重置slave状态
start slave 启动slave状态
stop slave 暂停slave状态
#跳过错误
stop slave;
set global sql_slave_skip_counter =1;
start slave;
7.脚本
cat >> /etc/my.cnf <<EOF
log-bin=mysql-bin
server-id=100
EOF
cat >> /etc/my.cnf <<EOF
relay-log=mysql-relay
server-id=101
EOF
set global validate_password_policy=0;
flush privileges;
grant replication slave on *.* to 'slave'@'%' identified by '12345678';
flush tables with read lock;
show master status\G
unlock tables;
change master to master_host = '172.16.1.100',
master_user = 'slave',
master_password = '12345678',
master_log_file = 'mysql-bin.000001',
master_log_pos = ;