主库已有数据配置MySQL8主从同步

2022-05-05 23:17:18   MySQL

主库已有数据配置MySQL8主从同步，我的从库是使用了docker搭建的，已有数据需要先锁表停止写入，再把主库数据导入从库，最后再开启同步

话不多说，开搞

第一步 写主库和从库的MySQL配置

主库配置

通常位置/etc/mysql/mysql.conf.d/mysqld.cnf，先简单介绍一下几个配置项：

[mysqld]
server-id               = 1 # 服务ID，注意主库从库一定要不同的ID
log_bin                 = /var/log/mysql/mysql-bin.log  # binlog存储位置
binlog_format = mixed # 模式
#expire_logs_days = 10 #日志过期时间
max_binlog_size   = 100M #日志最大容量，默认100M
# binlog_do_db = dbname  # 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可，这里我们选择注释掉，也就是全部数据库
# binlog_ignore_db = dbname # 指定忽略（不记录）二进制日志的数据库，如果某个或某几个不需要同步，可以在这里指定，这里我们全量同步，也注释掉这个了

再提供一个无注释版方便你复制：

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format = mixed
max_binlog_size   = 100M

配置后记得重启MySQL

service mysql restart

从库配置

与主库基本一致，重复项不过多介绍了

[mysqld]
server-id = 2
log-bin = /var/log/mysql/mysql-bin
binlog_format = mixed
max_binlog_size   = 100M

#指定relay_log日志的存放路径和文件前缀 ，不指定的话默认以主机名作为前缀
relay_log = /var/log/mysql/relay-bin  

read_only = on
skip_slave_start = on

#下面两个参数是把主从复制信息存储到innodb表中，默认情况下主从复制信息是存储到文件系统中的，如果从服务器宕机，很容易出现文件记录和实际同步信息不同的情况，存储到表中则可以通过innodb的崩溃恢复机制来保证数据记录的一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

同样提供一个纯享版方便复制：

[mysqld]
server-id = 2
log-bin = /var/log/mysql/mysql-bin
binlog_format = mixed
max_binlog_size   = 100M
relay_log = /var/log/mysql/relay-bin  
read_only = on
skip_slave_start = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

配置后记得重启MySQL

service mysql restart

第二步 在主库为从库创建账号并授权

以下语句在主库的MySQL命令行下执行，其中username和password为从库连接主库的用户名和密码，需要自行替换，ipaddress为从库的ip地址，毕竟出于安全性考虑，不宜让太多ip拥有访问权限，所以这里指定ip而非用*。

CREATE USER 'username'@'ipaddress' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'username'@'ipaddress';
FLUSH PRIVILEGES;

配置后，在从库主机中尝试用刚刚创建的用户和密码去连接主库，测试是否可以连接成功，如果连接失败，需要找找原因，下面讲几个注意的点，或许有助于你找到问题。

📢 需要注意的几个点：

  1. 需要将主库配置文件中bind-address = 127.0.0.1注释掉，并重启MySQL使其生效
  2. 如果是两台同区域主机，并且处于同一个安全组下，是可以用内网ip访问的，所以记得要将两台主机放在同一个安全组中，并且创建用户的时候记得用内网ip（私有ip）。如果非同安全组，或者需要公网访问，请将主库主机安全组中配置开放3306端口（如果数据库用的其他端口，请配置其开放），安全起见，只开放给从库的公网ip。
  3. 如果是用docker等虚拟技术，确保端口映射没有问题。如果是docker容器想要访问宿主机所在内网其他主机，请记得创建网桥，并使得该容器使用该网桥，具体操作方法，请查看docker文档，或者直接Google：docker容器如何访问宿主机内网（或局域网）？

第三步 复制主库数据到从库

此步骤主要针对运行一段时间的主库，需要将历史数据导入到从库，保证主从强一致性。

主库锁表停止写操作

在主库MySQL命令行中执行

flush tables with read lock;

主库数据导出

将主库所在主机命令行下使用mysqldump命令导出所有数据

mysqldump -uroot -p --all-databases > all_db.sql

数据导入到从库

将导出的文件传输到从库所在主机，并且执行导入。注意文件路径不要搞错

mysqldump -uroot -p --all-databases < all_db.sql

第四步 配置和启动从库同步

先查看主库日志状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000046 |    36660 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql-bin.000046 和 36660 就是我们下面配置从库要用的，当然，这两个值你要以你自己的为准。

在从库MySQL命令行下执行：

CHANGE MASTER TO MASTER_HOST='主库ip地址', MASTER_USER='之前创建的账号', MASTER_PASSWORD='之前创建的账号的密码', MASTER_LOG_FILE='上面刚刚的第一个值', MASTER_LOG_POS=上面的第二个值;

例如（以前文为例，假设主库ip为172.25.1.1）：

CHANGE MASTER TO MASTER_HOST='172.25.1.1', MASTER_USER='username', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000046', MASTER_LOG_POS=36660;

启动从库同步，在从库MySQL命令行下执行：

start salve;

恢复主库写入，在主库MySQL命令行下执行：

unlock tables;

第五步 查看状态检查是否成功

在从库MySQL命令行下执行：

show slave status\G;

幸运的话你会看到下面的结果。重点关注Slave_IO_Running和Slave_SQL_Running这两个，正常为Yes，如果Slave_IO_Running为connecting说明未能连接成功，你需要重新审视一下上面的第二步，看一下第二步的注意事项。并且你会注意到Slave_SQL_Running_State也会展示一些状态信息，如果有执行未成功，可以通过查看这个参数进一步了解问题出在哪里。

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.25.1.1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000046
          Read_Master_Log_Pos: 36190
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 15072
        Relay_Master_Log_File: mysql-bin.000046
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 36190
              Relay_Log_Space: 15275
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a11b83a5-3477-11ec-a8d0-00163e2606f8
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

至此，恭喜你已经完成了主从同步

viencoding.com版权所有，允许转载，但转载请注明出处和原文链接: https://viencoding.com/article/313
欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

猜你喜欢


评论

There are no comments yet.
未登录

登录后即可发表评论

登录或注册

GitHub VienBlog
最新搬瓦工优惠码 Vultr优惠购买教程 免费开源博客 PayPal免费提现到支付宝
点击图片注册vultr, 送100美元免费体验海外云服务器.(Click the banner to sing up, you will get $100 for free try.
最新搬瓦工优惠吗，搬瓦工优惠码每天更新.
laravel5.8 markdown blog
热门文章
大家都在看

标签

AdSense Anaconda Android apache API apt Auth AWS B-tree Bandwagon Blog bower brew bytes Caffe Catalina certbot Charles cloudcone Composer conda CoreML CPU crontab CSS csv Cuda cv2 datetime Digitalocean DNS Docker Docker-Compose Eloquent Excel export Flask FTP GET Git GitHub GitLab Gmail GoDaddy Google GTM hash Homebrew Homestead HTML http HTTPS IDEA image imagemagick imagick imgick import InnoDB ios iou iPhone ISO8601 iTerm2 Java JavaScript JPG JS Keras Laravel Laravel-Admin lazyload Linux list Livewire lnmp load logs Lravel Mac Markdown matplotlib md5 mix MobileNet Mojave mongo MongoDB MySQL Namesilo Nginx Node npm numpy Nvidia Nvidia-Docker onevps OpenCV Openpose openpyxl oss Outline parse PayPal PHP php-fpm PhpStorm PHP扩展 PIL Pillow pip PNG POST Protobuf PyCharm pyenv pymongo Python Python,人工智能,机器学习,VOC,xml Queue Redis requests RGB Sanctum save selenium SEO Shadowsock Shadowsocks ShadowsocksR simplemde Spring Boot SQLServer ssd SSH ssl SSL证书 SSR str Sublime sudo swap Swift Tensorflow TensorflowLite Terminal Terminator timestamp Ubuntu urllib UTC v2ray Valet Validation Validator VienBlog virtualenvs VPN VPS Vultr Web Windows WordPress Xcode xlsx yaml YAPI YUV zip zmq zsh 上网 下载图片 主从同步 云主机 云存储 云开发 云服务器 人工智能 代码管理 优化 优惠码 伪原创 作弊与反作弊 免费ss账号 免费提现 切片 前端 加密 协议 博客 友链 双击事件 后台运行 后端 命令 国内镜像源 图标 图片操作 图片转换 域名 多身份认证 大小写转换 姿态检测 安卓模拟器 安装 定时任务 定时执行 密码 密钥 导出导入 小程序码 延迟加载 异常 微信 微信小程序 快捷方式 慢查询 懒加载 提现 搜索引擎 搬瓦工 搭梯子 教程 数据库 数据重复 文件上传 无法登录 日志 日期 时区 时间 时间戳 服务器 机器学习 权限 梯子 模拟浏览器 港版支付宝 漏洞 爬虫 生活服务 用户管理 病毒 登录 目标检测 科学上网 系统升级 索引 组件 组件开发 编辑器 自动付款 自定义组件 英文伪原创 计划任务 计算机视觉 订阅通知 认证 语法 读写分离 远程仓库 远程连接 配置文件 重定向 错误异常 错误提示 队列 阿里云 香港 香港手机号
亲情非友情链接