MySql配置主从同步

本次使用mysql5.7版本，两台服务器均在公网。其中一台是线上生产环境，每次全量备份sql文件和费时和重复。那我们把线上生产环境的数据库设置成主库，线下本地数据库设置成从库，那么就只需要利用mysql的主从同步来做增量更新了，然后本地数据库再利用mysqldump备份sql文件并压缩成tar.gz文件存储到磁盘或者nas上就效率很高了。

这里我们假设

主库为:

主机地址：29.22.33.44

mysql端口:3306

从库:

主机地址：29.22.33.45

mysql端口:3306

注意：必须要现在主库上开启binlong,然后创建一个从库访问的账号，最后主库需要锁数据库（禁止新的数据写入）！！！

一.在主库中配置步骤如下：

1.配置my.cnf配置文件，使其启用bin-log日志文件：

首先关闭mysql数据库

如果是CentOs系统：

systemctl stop mysqld

如果是Ubuntu系统：

service mysql stop

然后编辑mysql的配置文件my.cnf在mysqld中增加如下内容(CentOs默认在/etc/my.cn,Ubuntu默认在/etc/mysql/mysql.conf.d/mysqld.cnf）:

[mysqld]

log-bin=mysql-bin

server-id=1

2.配置用来给从库远程访问的账号：

注意这里要先启动mysql数据库：

如果是CentOs系统：

systemctl start mysqld

如果是Ubuntu系统：

service mysql start

登录mysql数据库（执行:mysql -uroot -p,然后输入密码）操作如下：

说明：如果从库的ip地址固定，可以把%修改成ip地址。

CREATE USER 'slave'@'%' IDENTIFIED BY 'newPass@2222#z2!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;

3.锁住数据库的写操作：

flush tables with read lock;

4.获取二进制日志的信息：

show master status;

5.把当前数据库中已有数据全部备份出来到一个压缩文件（方便下载到本地数据库导入）：

说明：/var/lib/mysql/mysql.sock这个请到my.cnf文件中查看具体路径，不同linux系统有不同。

注意：第4步的mysql登录会话不要关闭，我们这里新开一个会话来导出sql（不然前面的binlog position会失效）

此处的root为账号，-p后面单引号中的为对应账号密码。

备份文件将保存在：/home/abc/backup/文件夹下， 请提前用命令：mkdir /home/abc/backup/ 创建好。

 mysqldump -uroot -p'123456' -S /var/lib/mysql/mysql.sock --all-databases | gzip > /home/abc/backup/mysql_bak.$(date +%F).sql.gz

2.在从库中配置如下：

1.mysql的配置文件my.cnf在mysqld中增加如下内容(CentOs默认在/etc/my.cn,Ubuntu默认在/etc/mysql/mysql.conf.d/mysqld.cnf）:

[mysqld]

server-id=2

2.重启mysql数据库：

如果是CentOs系统：

systemctl restart mysqld

如果是Ubuntu系统：

service mysql restart

3.导入主库备份的sql脚本（从主库下载的备份sql文件是压缩的tar.gz压缩文件，需要先解压):

说明：/var/lib/mysql/mysql.sock这个请到my.cnf文件中查看具体路径，不同linux系统有不同。

mysql -uroot -p'123456' -S /var/lib/mysql/mysql.sock < /Users/zhangsan/Downloads/mysql_bak.2022-10-14.sql

4.配置主库连接信息：

使用mysql -uroot -p,然后输入密码登录数据库然后执行如下指令：

CHANGE MASTER TO
MASTER_HOST='29.22.33.44',
MASTER_USER='slave',
MASTER_PASSWORD='newPass@2222#z2!',
MASTER_LOG_FILE='mysql-bin.000010',
MASTER_LOG_POS=154; 

说明：MASTER_HOST 值是主库ip地址，MASTER_USER是我们在主库上给从库分配的远程访问账号,MASTER_PASSWORD是对应密码，MASTER_LOG_FILE就是主库上的二进制日志文件名，MASTER_LOG_POS是标记位置。

5.启动同步：

start slave;

6.检查同步状态：

show slave status \G;

3.一些细节说明

1.当从库显示同步成功后，需要登录主库执行解锁数据库写操作：

unlock tables;

2.因为是从主库全量同步的sql脚本，所以你导入到本地从库后，你原来本地从库设置的账号密码都会变成主库的账号密码。

3.如果因为意外情况终止了同步，可以登录从库执行停止同步：stop slave;然后再执行启动同步：start slave;。然后使用：show slave status \G;查看同步状态。