本篇内容介绍了“MySQL5.7主从添加新从库的方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！MySQL 主从复制，不停机添加新从节点：1、主库创建账号：修改主库repl密码：show master status;alter user repl@’%’ identified by ‘123456’;grant replication slave,replication client on *.* to ‘repl’@’%’;flush privilegs;2、从库配置（创建从库数据库过程简略）：开启binlog[root@centos_TP data1]# cat /etc/my.cnf[mysqld]#datadir=/var/lib/mysql#socket=/var/lib/mysql/mysql.sock#user=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0basedir=/usr/local/mysqldatadir=/data1/datasocket=/tmp/mysql.sockport=3306server-id =60182replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=sys.%log-bin = /data1/log/mysql-binbinlog_format = MIXEDskip-slave-start = 1expire_logs_days=3#validate_password_policy=0#validate_password_length=3relay-log-index=/data1/log/mysql-relayrelay-log=/data1/log/mysql-relaylog-bin=/data1/log/mysql-bin#log-error=log.errexplicit_defaults_for_timestamp=true[mysqld_safe]log-error=/data1/log/mysql.errpid-file=/data1/tmp/mysqld.pid初始化数据库：正常初始化：[root@centos_TP bin]# ./mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql2020-01-14T08:48:27.965207Z 0 [Warning] InnoDB: New log files created, LSN=457902020-01-14T08:48:28.175008Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2020-01-14T08:48:28.270192Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a2408f8d-36aa-11ea-a1c6-00505695cefc.2020-01-14T08:48:28.273709Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.2020-01-14T08:48:28.278708Z 1 [Note] A temporary password is generated for root@localhost: (,%E6LnwWrrq指定初始化配置文件：/usr/local/mysql/bin/mysqld–defaults-file=/etc/my.cnf–initialize–user=mysql#开启数据库/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &#登录数据库，修改root密码mysql -p之前初始化的密码set sql_log_bin=0;mysql> alter user root@’localhost’ identified by ‘123456’;mysql>flush privileges;set sql_log_bin=1;增加root远程登录用户：mysql> create user root@’%’ identified by ‘123456’;Query OK, 0 rows affected (0.01 sec)mysql> grant all privileges on *.* to root@’%’;mysql> flush privileges;#创建slave账号mysql> grant replication slave,replication client on *.* to ‘repl’@’%’ identified by ‘123456’;#在slave节点上执行mysql> set global read_only=1;#由于从库随时会提升成主库，不能写在配置文件里3、备份主库：[root@localhost dbdata]# mysqldump -uroot -p –routines –single_transaction –master-data=2 -B cat qc_bh > all.sql参数说明：–routines：导出存储过程和函数–single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。–master-data：默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。4、从库创建数据库，并导入数据将dump的数据拷贝到从库后开始导数据mysql>create database cat;create database qc_bh;mysql> source /data1/all.sql…Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)5、查看备份文件的binlog 和 pos值[root@centos_TP data1]# head -25 all.sql– MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)—- Host: localhost Database: cat– ——————————————————– Server version 5.7.20-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE=’+00:00′ */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;—- Position to start replication or point-in-time recovery from—- CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000037′, MASTER_LOG_POS=621697642;—- Current Database: `cat`可以看到 MASTER_LOG_FILE=’mysql-bin.000037′, MASTER_LOG_POS=621697642;6、启动从库mysql> change master to -> master_host=’192.168.60.181′, -> master_user=’repl’, -> master_password=’123456′, -> master_log_file=’mysql-bin.000037′, -> master_log_pos=621697642;mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave statusG;*************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.60.181 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000037 Read_Master_Log_Pos: 677960018 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 24887 Relay_Master_Log_File: mysql-bin.000037 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: mysql.%,performance_schema.%,information_schema.%,sys.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 621722209 Relay_Log_Space: 56262899 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: 6606Master_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: 60181 Master_UUID: a524c954-c8a8-11e9-8082-00505697e9db Master_Info_File: /data1/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log 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.01 sec)ERROR:No query spe开发云主机域名cified显示：看到IO和SQL线程均为YES，说明主从配置成功。Slave_IO_Running: YesSlave_SQL_Running: YesRead_Master_Log_Pos: 677960018表示一直在追binlog日志。“MySQL5.7主从添加新从库的方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注开发云网站，小编将为大家输出更多高质量的实用文章！
1： 检查是否安装了MySQL组件。 如上所示，说明安装了MySQL 5.6.23这个版本的client、server、devel三个组件。 2： 卸载前关闭MySQL服务 2.1 方法1 2.2 方法2 3. 收集MySQL对应的文件夹信息 [root@DB…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
