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

文章来源原创   作者:临窗旋墨   发布时间:2022-09-02   阅读:112   标签:

缘由

我的主机:

  • 阿里云主机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
  3. # 看看默认账号和密码
  4. sudo cat /etc/mysql/debian.cnf
  5. # 登录mysql创建root 和授权
  6. create user 'root'@'%' identified by 'password';
  7. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  8. FLUSH PRIVILEGES;
  9. 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
  10. # Here is entries for some specific programs
  11. # The following values assume you have at least 32M ram
  12. [mysqld]
  13. #
  14. # * Basic Settings
  15. #
  16. user = mysql
  17. # pid-file = /var/run/mysqld/mysqld.pid
  18. # socket = /var/run/mysqld/mysqld.sock
  19. # port = 3306
  20. # datadir = /var/lib/mysql
  21. # If MySQL is running as a replication slave, this should be
  22. # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
  23. # tmpdir = /tmp
  24. #
  25. # Instead of skip-networking the default is now to listen only on
  26. # localhost which is more compatible and is not less secure.
  27. # 20220613 注释掉绑定本地地址
  28. #bind-address = 127.0.0.1
  29. mysqlx-bind-address = 127.0.0.1
  30. #
  31. # * Fine Tuning
  32. #
  33. key_buffer_size = 16M
  34. # max_allowed_packet = 64M
  35. # thread_stack = 256K
  36. # thread_cache_size = -1
  37. # This replaces the startup script and checks MyISAM tables if needed
  38. # the first time they are touched
  39. myisam-recover-options = BACKUP
  40. # max_connections = 151
  41. # table_open_cache = 4000
  42. #
  43. # * Logging and Replication
  44. #
  45. # Both location gets rotated by the cronjob.
  46. #
  47. # Log all queries
  48. # Be aware that this log type is a performance killer.
  49. # general_log_file = /var/log/mysql/query.log
  50. # general_log = 1
  51. #
  52. # Error log - should be very few entries.
  53. #
  54. log_error = /var/log/mysql/error.log
  55. #
  56. # Here you can see queries with especially long duration
  57. # slow_query_log = 1
  58. # slow_query_log_file = /var/log/mysql/mysql-slow.log
  59. # long_query_time = 2
  60. # log-queries-not-using-indexes
  61. #
  62. # The following can be used as easy to replay backup logs or for replication.
  63. # note: if you are setting up a replication slave, see README.Debian about
  64. # other settings you may need to change.
  65. # server-id = 1
  66. # log_bin = /var/log/mysql/mysql-bin.log
  67. # binlog_expire_logs_seconds = 2592000
  68. max_binlog_size = 100M
  69. # binlog_do_db = include_database_name
  70. # binlog_ignore_db = include_database_name
  71. #
  72. #start master config 20220902
  73. #[必须]主服务器唯一ID
  74. server-id=1
  75. # [可选]设置需要复制的数据库名称,默认全部记录。
  76. binlog-do-db=qiudong
  77. # [可选] 0(默认)表示读写(主机),1表示只读(从机)
  78. read-only=0
  79. # 设置日志文件保留的时长,单位是秒 2592000 = 30天
  80. #
  81. binlog_expire_logs_seconds=2592000
  82. # [可选]设置binlog格式(STATEMENT是基于sql语句的复制,ROW是基于行的复制,MIXED是混合模式
  83. 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的具体值;
  7. -- 不过CHANGE MASTER 已经过期了,可以改为CHANGE REPLICATION SOURCE
  8. SOURCE_HOST ='',
  9. SOURCE_USER ='',
  10. SOURCE_PASSWORD ='',
  11. SOURCE_LOG_FILE ='binlog.000091',
  12. 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也能登陆啊

目录