mysql主从模式配置

1.配置主节点

#测试环境主节点192.168.1.101，从节点192.168.1.102

#mysql主节点配置

vi /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

log-error = /var/log/mysql/error.log

bind-address = 192.168.1.101

mysqlx-bind-address = 127.0.0.1

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = tax_release

mysql_native_password = ON

#重启服务

systemctl restart mysql

#在主节点创建备份用户并赋予相应权限

CREATE USER 'rp'@'192.168.1.102' IDENTIFIED with mysql_native_password BY 'abc@brt123.com';

GRANT REPLICATION SLAVE ON *.* TO 'rp'@'192.168.1.102';

FLUSH PRIVILEGES;

2.数据同步

#主节点锁表（无写操作可省略）
FLUSH TABLES WITH READ LOCK;
#查看断点(show master status或show source status或如下命令）
SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      851 | dbname       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
# 记录上面的file和position字段值
#主节点上导出数据
mysqldump -u root mysql > mysql.sql
scp mysql.sql tcenter@192.168.1.102:/tmp/
#在从节点上导入数据
CREATE DATABASE dbname;
mysql mysql < /tmp/mysql.sql
#主节点表解锁
UNLOCK TABLES;

#重启从节点mysql服务
systemctl restart mysql

3.配置从节点

#修改从节点配置文件
vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
datadir        = /var/lib/mysql
log-error    = /var/log/mysql/error.log
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = mysql
relay-log               = /var/log/mysql/mysql-relay-bin.log
mysql_native_password    = ON
#在从节点上执行sql启动副本同步，source_log_file和source_log_pos为之前主节点上执行SHOW BINARY LOG STATUS;的结果
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.101',
SOURCE_USER='rp',
SOURCE_PASSWORD='abc@brt123.com',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=851;
START REPLICA;
SHOW REPLICA STATUS\G;