MySQL - 主从同步

MySQL - 主从同步

目录

环境准备

MySQL核心配置

数据同步

 数据备份

---

环境准备

• 两台mysql

MySQL核心配置

1.【必须】主服务器唯一ID
server-id =1
2.【必须】启用二进制日志
log-bin=自己本地的路径/mysqlbin
3.【可选】启动错误日志
log-err=自己本地的路径/mysqlerr
4.【可选】根目录
basedir="自己本地路径"
5.【可选】临时目录
tmpdir="自己的本地路劲"
6.【可选】数据目录
datadir="自己本地路径/Data/"
7.read-only=0
主机，读写都可以，0或者false表示关闭，读写都支持；1或者true便是开启，只支持读取数据，但是只对普通用户生效
8.【可选】设置不要复制的数据库
binlog-lgnore-db=mysql
9.【可选】设置需要复制的数据
binlog-do-db=需要复制的主数据库名字

vim /etc/my.cnf

• 主节点配置
  • mysql之间数据备份用的是日志实现
  • 只需要在核心配置文件增加 log-bin 和 server-id 配置即可
  • server-id 全局唯一，多个节点不可重复

[client]
port=3306
socket=/tmp/mysql/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=error.log

transaction_isolation = READ-COMMITTED
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
lower_case_table_names = 1
#开启可以跳过认证，免密登录
#skip-grant-tables

default_authentication_plugin=mysql_native_password

log-bin=mysql_1
server-id=1

• 从节点配置 
  • 只需要配置 server-id 即可
  • server-id 全局唯一，多个节点不可重复

[client]
port=3306
socket=/tmp/mysql/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=error.log

transaction_isolation = READ-COMMITTED
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
lower_case_table_names = 1
#开启可以跳过认证，免密登录
#skip-grant-tables

default_authentication_plugin=mysql_native_password

server-id=2

数据同步（主节点操作）

• 创建数据同步账号，在主节点创建即可

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

• 重启MySQL服务 （主节点操作）

systemctl restart mysql.service

 数据备份（主节点操作）

• #锁表，暂时不接受任何写操作

FLUSH TABLES WITH READ LOCK;

• 查看当前 bin-log 文件 和数据偏移量
  • 会根据数据库写操作发生变化，锁表->备份数据->恢复锁，必须一气呵成

mysql > SHOW MASTER STATUS;

• dump主节点数据
  • 缺少dump指令，建立软连接即可
    • ln -s  /usr/local/mysql/bin/mysqldump  /usr/bin
    • /usr/local/mysql/bin/mysqldump 当前mysql安装路径

mysqldump --all-databases --master-data > dbdump.db -uroot -p

• 将文件发送只从节点

scp ./dbdump.db root@192.168.213.162:/root/

• 从节点dump同步数据

# 可以在系统中直接使用使用如下命令
mysql < /root/dbdump.db -uroot -p

# 也可以在mysql命令行中使用如下命令
#临时修改
mysql> show variables like 'sync_binlog';
mysql> set global sync_binlog = 2000;
mysql> set global innodb_flush_log_at_trx_commit = 2;

mysql> source /root/dbdump.db;

# 同步完成后恢复 global sync_binlog global innodb_flush_log_at_trx_commit 原有值

• 从节点连接主机
  • MASTER_HOST：主节点地址
  • MASTER_PORT：主节点端口号，默认端口可以省略
  • MASTER_USER：同步通讯用的账号
  • MASTER_PASSWORD：同步通讯用的账号的密码
  • MASTER_LOG_FILE：bin-log 文件
  • MASTER_LOG_POS：偏移量

mysql> 
CHANGE MASTER TO 
MASTER_HOST='192.168.213.156', 
MASTER_PORT=3306,
MASTER_USER='repl', 
MASTER_PASSWORD='repl', 			        
MASTER_LOG_FILE='mysql_1.000002',			   
MASTER_LOG_POS=155;

• 开始同步，标记当前为从节点

mysql> START SLAVE;

• 主节点释放锁
  • 在释放锁之前不可关闭mysql客户端，结束会话，当前锁时效

mysql> UNLOCK TABLES;

•  从节点查看从节点状态

show slave status;

 看到master-log-bin.000003 | Yes | Yes 就表示成功了

• 从节点操作

# 停止从节点工作
mysql> stop slave ;

# 启动从节点工作
mysql> start slave ;

• root账户不受(my.cnf)读写限制，处理业务建议使用非root账户，创建MySQL用户并授权