摘要:且根据不同的引擎类型与数据库集群配置方式,都有很多具体的复制方式和参数配置就我个人看,前期根据尽量考虑周全,以避免不必要的后期更新与容错。更多建议参考官方文档,或者高性能中复制章节。官方文档参考非官方文档参考
主备目的
高可用
可扩展
容灾
备份
数据仓库
主库把数据更改记录到二进制文件(binary log)
备库将主库的日志复制到自己的中继日志(relay log)
备库读取自己中继日志上的文件,重放到备份数据库上
观点区分:mysql集群和主备(也可以叫做从库)是两个不相同的概念
安装环境介绍ubuntu 版本:Ubuntu 16.04.2 LTS
mysql 版本 :mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
虚拟机:virtual box
主服务器ip:10.70.31.151(主库ip地址在从库连接主库同步的时候会用到)
从服务器ip:10.70.30.191
port :都为3306
1.在主上查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | 111 | | hive | | mysql | | performance_schema | | sonar | | sys | +--------------------+ 7 rows in set (0.00 sec)
需要同步的就是111库,注意字母的库名如果需要在命令行新建或者删除需要加上``
如drop database 111``
mysql> use 111; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_111 | +---------------+ | 360ky | | content | | product | +---------------+ 3 rows in set (0.00 sec)
2.在主上修改配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf #server-id 服务器唯一标识,与其集群内不能重复 server-id = 1 #log_bin 启动MySQL二进制日志,表示自定义binglog文件名称 log_bin = master-bin #管理所有bin log的索引文件 log_bin_index = master-bin.index #binlog_do_db 指定记录二进制日志的数据库 binlog_do_db = 111 #binlog_ignore_db 指定不记录二进制日志的数据库 binlog_ignore_db = mysql
设置后如下:
80 # The following can be used as easy to replay backup logs or for replication. 81 # note: if you are setting up a replication slave, see README.Debian about 82 # other settings you may need to change. 83 server-id = 1 84 log_bin = /var/log/mysql/mysql-bin.log 85 86 expire_logs_days = 10 87 max_binlog_size = 100M 88 binlog_do_db = 111 89 binlog_ignore_db = mysql
另外建议将bind_address 设置成非127.0.0.1 ,具体设置根据当前环境设定。
记得重启:
root@ubuntu:/home/tb# service mysql restart主库操作之验证状态
在主库上验证一下log bin状态:
mysql> show global variables like "%log_bin%"; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/log/mysql/mysql-bin | | log_bin_index | /var/log/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+--------------------------------+ 5 rows in set (0.00 sec)
在主库查看binlog日志文件列表(不同机器有差别)
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 154 | +------------------+-----------+
查看目前位置偏移,从库同步更新时需要用到以下参数值:File 、Position
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 1044 | 111 | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看当前正在写入的binlog日志
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | 111 | mysql | | +------------------+----------+--------------+------------------+-------------------+
查看data目录地址(也就是auto.conf的路径)
mysql> show global variables like "%datadir%"; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)主库操作之设置权限及导出
在主库设置从库复制权限:
GRANT REPLICATION SLAVE ON *.* TO "repl"@"%"IDENTIFIED BY "123456";
刷新权限
FLUSH PRIVILEGES;
刷新读写,生产环境可能会造成阻塞
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
在主库用mysqldump导出数据(由于采用了FTWRL锁表,所以数据会相对完整及过程安全)
mysqldump -u root -p --opt 111 > 111.sql
完成后主库解锁
ublock tables从库操作之导入数据
从库执行更新复制
create database `111`; mysql -u root -p 111 < /path/to/111.sql
(模拟环境用的命令行工具szrz工具同步两台机器之间的.sql文件)
从库操作之修改配置文件修改从库的mysql.cnf文件为
83 server-id = 2 84 relay-log =/var/log/mysql/mysql-relay-bin.log 85 log-bin =/var/log/mysql/mysql-bin.log 86 binlog_do_db =111 87 #log_bin = /var/log/mysql/mysql-bin.log 88 expire_logs_days = 10 89 max_binlog_size = 100M 90 #binlog_do_db = include_database_name 91 #binlog_ignore_db = include_database_name 92#重放的事件也记录到自己的二进制文件中 93log_salve_updates=1
然后重启从库的msyql
sudo service mysql restart从库操作之配置启动同步
mysql> CHANGE MASTER TO MASTER_HOST="10.70.31.151", MASTER_USER="repl", MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000003", MASTER_LOG_POS=1044;从库操作之可能出现问题
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
原因:如果采用虚拟机复制,会发生节点错误,建议在复制的虚机上删除后重新安装
sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.7 mysql-client-core-5.7 OR(sudo apt-get remove --purge mysql*) sudo rm -rf /etc/mysql /var/lib/mysql sudo apt-get autoremove sudo apt-get autoclean
然后需要重新安装一下
sudo apt-get install mysql-server mysql-client
可以用mysqlbinglog命令行(非mysql cli 环境下)查看binlog日志文件内容
root@ubuntu:/home/tb# cd /var/log/mysql/ root@ubuntu:/var/log/mysql# ls error.log mysql-bin.000001 mysql-bin.index root@ubuntu:/var/log/mysql# mysqlbinlog -v mysql-bin.000001 > mysql-bin.1.log root@ubuntu:/var/log/mysql# cat mysql-bin.1.log /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180102 14:16:56 server id 1 end_log_pos 123 CRC32 0x8ed37480 Start: binlog v 4, server v 5.7.17-0ubuntu0.16.04.1-log created 180102 14:16:56 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG " 2CNLWg8BAAAAdwAAAHsAAAABAAQANS43LjE3LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADYI0taEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AYB0044= "/*!*/; # at 123 #180102 14:16:56 server id 1 end_log_pos 154 CRC32 0x5dd6eb7e Previous-GTIDs # [empty] SET @@SESSION.GTID_NEXT= "AUTOMATIC" /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
开启同步:
mysql> mysql> start slave; Query OK, 0 rows affected (0.01 sec)查看从库状态
mysql> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.70.31.151 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1044 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Connecting 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: 1044 Relay_Log_Space: 154 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master "repl@10.70.31.151:3306" - retry-time: 60 retries: 6 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 180102 16:41:36 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: 1 row in set (0.00 sec)
发现密码不对,改一下重新开始
mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.01 sec) mysql> CHANGE MASTER TO MASTER_HOST="10.70.31.151", MASTER_USER="repl", MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000003", MASTER_LOG_POS=1044; Query OK, 0 rows affected, 2 warnings (0.01 sec)
从库运行关键指标:
如果再次查询状态仍然 发现Slave_IO_Running 或者Slave_SQL_Running 不同时为YES,尝试执行
mysql> stop slave; mysql> reset slave; mysql> START SLAVE;
最后之成功状态:
mysql> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.70.31.151 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 3023 Relay_Log_File: mysql-relay-bin.000006 Relay_Log_Pos: 3236 Relay_Master_Log_File: mysql-bin.000004 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: 3023 Relay_Log_Space: 4546 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: 16edfaa2-0970-11e7-994e-080027e11ac8 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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: 1 row in set (0.00 sec)写在最后
上述流程就是自己模拟,线上不同的业务场景与地域具体配置,远远复杂于以上虚拟机下的demo。
且根据不同的引擎类型与数据库集群配置方式,都有很多具体的复制方式和参数配置
就我个人看,前期根据尽量考虑周全,以避免不必要的后期更新与容错。
更多建议参考官方文档,或者高性能mysql中复制章节。
官方文档参考
非官方文档参考
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/47475.html
摘要:会保证安全并且禁止非法复制拓扑。事实上,拒绝任何非法操作,以免破坏系统。可以人为进行恢复。一旦集群经历了恢复,将在接下来的秒小时内阻止自动恢复。优势完成故障转移。 .................................................................................系统:centos7Mysql:5.7.22IP:192.16...
摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...
摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...
摘要:微软前些日子公布了下简称,当时还只有支持安装,作为普通用户的我就没有尝试。解决方案有待进一步发掘。按照刚刚发布的报告,微软已经是开源贡献第一大公司,拥抱之后我们也可以在下进行开发了。 微软前些日子公布了 Windows Subsystem Linux(下简称WSL),当时还只有 Insider Preview 支持安装,作为普通用户的我就没有尝试。这两天不知道伴随哪次更新,标准的专业版...
摘要:之前写过一篇文章主从同步的原理。相信看过这篇文章的童鞋,都摩拳擦掌,跃跃一试了吧今天我们就来一次主从同步实战环境说明下面的实战演练,都是基于上面的环境。创建主从同步的。 1、Introduction 之前写过一篇文章:Mysql主从同步的原理。相信看过这篇文章的童鞋,都摩拳擦掌,跃跃一试了吧?今天我们就来一次mysql主从同步实战! 2、环境说明 os:ubuntu16.04 mysq...
阅读 2814·2023-04-25 15:44
阅读 1737·2019-08-30 13:11
阅读 2688·2019-08-30 11:11
阅读 2796·2019-08-29 17:21
阅读 1170·2019-08-29 15:38
阅读 735·2019-08-29 12:49
阅读 1655·2019-08-28 18:19
阅读 3103·2019-08-26 14:01