本地两个 mysql数据库同步-瓯乐棋牌

使用mysqldump备份本地数据库，然后通过aws data pipeline或自定义脚本将数据导入到rds for mysql中。

本地两个mysql数据库同步和本地mysql迁移到rds for mysql是两个不同的问题，我将分别为您详细介绍。

本地两个mysql数据库同步

1、使用主从复制实现同步

主从复制是mysql中最常见的同步方式，通过在主库上记录数据变更并传输到从库，从而实现数据的同步。

步骤如下：

配置主库：编辑主库的my.cnf文件，添加以下内容：

[mysqld]
serverid=1
logbin=mysqlbin
binlogdodb=需要同步的数据库名

重启mysql服务。

创建用于同步的用户并授权：

create user '同步用户'@'%' identified by '密码';
grant replication slave on *.* to '同步用户'@'%';

获取主库的二进制日志文件名和位置：

show master status;

记下file和position的值。

配置从库：编辑从库的my.cnf文件，添加以下内容：

[mysqld]
serverid=2
relaylog=relaybin

重启mysql服务。

在从库上执行以下命令，配置从库连接主库：

change master to master_host='主库ip', master_user='同步用户', master_password='密码', master_log_file='主库上获取的文件名', master_log_pos=主库上获取的位置；

启动从库的同步进程：

start slave;

查看从库同步状态：

show slave statusg;

如果slave_io_running和slave_sql_running的值都是yes，则表示同步成功。

2、使用第三方工具实现同步（如percona xtrabackup）

percona xtrabackup是一个免费的开源备份工具，支持innodb和xtradb引擎的热备份，可以用于实现mysql数据库的同步。

步骤如下：

安装percona xtrabackup，以ubuntu为例：

sudo aptget install perconaxtrabackup24

备份主库：执行以下命令，将备份文件保存到指定目录：

pxb backup targetdir=/备份目录 /var/lib/mysql/数据库名 > backup.log 2>&1 &

恢复备份到从库：执行以下命令，将备份文件恢复到从库的数据目录下：

rm rf /从库数据目录/* && xbstream restore innodbonly parallel=4 targetdir=/从库数据目录 /备份目录/backup.xtrabackup >> restore.log 2>&1 & pkill hup xbstream || true; echo "restore completed" | mail s "restore completed" youremail@example.com && sleep 5 && tail f restore.log || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore failed" youremail@example.com && exit 1; exit 0; pkill 9 xbstream || true; echo "restore failed" | mail s "restore失败