mysql8.0 主从备份(ubutu22.04到centos7.9)

文章来源：原创   作者：临窗旋墨   发布时间：2022-09-02   阅读：1424   标签：我的linux,运维 分类：mysql 专题：我的物理linux

缘由

我的主机：

  • 阿里云主机centos7.9， 固定ip， xuqiduong.cn域名解析到此主机

  • 家用小主机ubutu22.04， ip会动态改变， 通过ddns解析到xuqiudong.us.to

  • 项目和数据库等都放在家用主机上面。阿里云通过ngixn反向代理到家用主机。

  • 由于家用主机ip会变化，ddns会有一定的延迟，加上nginx反向代理域名会缓存ip(虽然在ngixn上我通过resolver解决缓存（参见我上一篇博文临窗旋墨-反向代理域名缓存引起的504 Gateway Time-out (xuqiudong.cn)），但是考虑到效率，我依然设置了一定时间的缓存)。

  • 有鉴于此，当家用主机ip变化的时候，可能存在一定的时间无法访问xuqiudong.cn的情况。故在阿里云主机上同步部署了xuqiudong.cn项目并安装了数据库。
  • nginx配置的负载策略(backup)，当家用主机宕机的时候，访问阿里云本机项目。
  • 所以需要从家用主机ubuntu上同步数据库到阿里centos上。

数据库版本

阿里云centos7.9 安装的是 8.0.30；

由于CentOS 7默认安装的数据库是Mariadb,所以使用YUM命令是无法安装MySQL的，只会更新Mariadb。下面这篇博文有完整的安装过程，

centos7系统安装mysql8.0完整步骤 -

家用主机ubuntu22.04安装的是8.0.30-0ubuntu0.22.04.1

通过apt安装，非常快。

  1. apt-search mysql-server
  2. sudo apt install mysql-server-8.0
  4. # 看看默认账号和密码
  5. sudo cat /etc/mysql/debian.cnf
  6. # 登录mysql创建root 和授权
  7. create user 'root'@'%' identified by 'password';
  8. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  9. FLUSH PRIVILEGES;
  10. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxx';

取消本地地址绑定：

/etc/mysql/mysql.conf.d/mysqld.cnf中注释掉 bind-address = 127.0.0.1

mysql服务管理：

  1. sudo service mysql status # 查看服务状态
  2. sudo service mysql start # 启动服务
  3. sudo service mysql stop # 停止服务
  4. sudo service mysql restart # 重启服务

卸载mysql

  1. sudo apt purge mysql-*
  2. sudo rm -rf /etc/mysql/ /var/lib/mysql
  3. sudo apt autoremove
  4. sudo apt autoclean

主从备份

1 ubuntu主库配置

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

主要配置这几行就可以了, 配置完之后重启mysql， 重启玩看一下相关配置 SHOW GLOBAL VARIABLES LIKE '%...%' 是否生效

  1. #[必须]主服务器唯一ID
  2. server-id=1
  3. #[必须]启用二进制日志,无后缀的文件名。也可以是本地的路径/log/bin-log
  4. log-bin=bin-log
  5. #[可选]设置需要复制的数据库名称,默认全部记录。
  6. binlog-do-db=qiudong

copy from file:

  1. #
  2. # The MySQL database server configuration file.
  3. #
  4. # One can use all long options that the program supports.
  5. # Run program with --help to get a list of available options and with
  6. # --print-defaults to see which it would actually understand and use.
  7. #
  8. # For explanations see
  9. # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
  11. # Here is entries for some specific programs
  12. # The following values assume you have at least 32M ram
  14. [mysqld]
  15. #
  16. # * Basic Settings
  17. #
  18. user            = mysql
  19. # pid-file      = /var/run/mysqld/mysqld.pid
  20. # socket        = /var/run/mysqld/mysqld.sock
  21. # port          = 3306
  22. # datadir       = /var/lib/mysql
  25. # If MySQL is running as a replication slave, this should be
  26. # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
  27. # tmpdir                = /tmp
  28. #
  29. # Instead of skip-networking the default is now to listen only on
  30. # localhost which is more compatible and is not less secure.
  31. # 20220613  注释掉绑定本地地址
  32. #bind-address           = 127.0.0.1
  33. mysqlx-bind-address     = 127.0.0.1
  34. #
  35. # * Fine Tuning
  36. #
  37. key_buffer_size         = 16M
  38. # max_allowed_packet    = 64M
  39. # thread_stack          = 256K
  41. # thread_cache_size       = -1
  43. # This replaces the startup script and checks MyISAM tables if needed
  44. # the first time they are touched
  45. myisam-recover-options  = BACKUP
  47. # max_connections        = 151
  49. # table_open_cache       = 4000
  51. #
  52. # * Logging and Replication
  53. #
  54. # Both location gets rotated by the cronjob.
  55. #
  56. # Log all queries
  57. # Be aware that this log type is a performance killer.
  58. # general_log_file        = /var/log/mysql/query.log
  59. # general_log             = 1
  60. #
  61. # Error log - should be very few entries.
  62. #
  63. log_error = /var/log/mysql/error.log
  64. #
  65. # Here you can see queries with especially long duration
  66. # slow_query_log                = 1
  67. # slow_query_log_file   = /var/log/mysql/mysql-slow.log
  68. # long_query_time = 2
  69. # log-queries-not-using-indexes
  70. #
  71. # The following can be used as easy to replay backup logs or for replication.
  72. # note: if you are setting up a replication slave, see README.Debian about
  73. #       other settings you may need to change.
  74. # server-id             = 1
  75. # log_bin                       = /var/log/mysql/mysql-bin.log
  76. # binlog_expire_logs_seconds    = 2592000
  77. max_binlog_size   = 100M
  78. # binlog_do_db          = include_database_name
  79. # binlog_ignore_db      = include_database_name
  80. #
  81. #start master config 20220902
  82. #[必须]主服务器唯一ID
  83. server-id=1
  84. # [可选]设置需要复制的数据库名称,默认全部记录。
  85. binlog-do-db=qiudong
  86. # [可选] 0(默认)表示读写(主机),1表示只读(从机)
  87. read-only=0
  88. # 设置日志文件保留的时长,单位是秒 2592000 = 30天
  89. #
  90. binlog_expire_logs_seconds=2592000
  91. # [可选]设置binlog格式(STATEMENT是基于sql语句的复制，ROW是基于行的复制，MIXED是混合模式
  92. binlog_format=MIXED

2 centos从库配置

/etc/my.conf

  1. #[必须]从服务器唯一ID
  2. server-id=2
  3. #[可选]启用中继日志
  4. relay-log=mysql-relay
  5. #[可选] 0(默认)表示读写(主机),1表示只读(从机)
  6. read-only=1

配置完成重启。

3 从库连接主库

3.1 查看主库日志 show master status;，
  • 记录下file和position
3.2 从库连接主库,执行如下命令
  1. CHANGE MASTER TO
  2. MASTER_HOST='主机的IP地址',
  3. MASTER_USER='主机用户名',
  4. MASTER_PASSWORD='主机用户名的密码',
  5. MASTER_LOG_FILE='bin-log.具体数字',
  6. MASTER_LOG_POS=Position的具体值;
  8. -- 不过CHANGE MASTER 已经过期了，可以改为CHANGE REPLICATION SOURCE：
  9. SOURCE_HOST ='',
  10. SOURCE_USER ='',
  11. SOURCE_PASSWORD ='',
  12. SOURCE_LOG_FILE ='binlog.000091',
  13. SOURCE_LOG_POS =157;

一些简单命令：

  1. # 查看slave状态
  2. SHOW  REPLICA STATUS ;
  3. #重置 删除SLAVE数据库的relaylog日志文件,并重新启用新的relaylog文件
  4. RESET  REPLICA ALL;
  5. # 停止
  6. STOP REPLICA ;
  7. # 启动
  8. START REPLICA ;

测试：

修改主库数据，从库可以实时同步。

2022-09-02

参考：ubuntu22 mysql8.0如何搭建主从复制？

发表评论

临窗旋墨发表于 2022-09-02 16:12

qq也能登陆啊

目录