建站
咨詢
售后
建站咨詢
新聞中心
本篇內(nèi)容介紹了“MySQL5.7主從添加新從庫的方法”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
創(chuàng)新互聯(lián)一直通過網(wǎng)站建設(shè)和網(wǎng)站營銷幫助企業(yè)獲得更多客戶資源。 以"深度挖掘，量身打造，注重實(shí)效"的一站式服務(wù)，以成都做網(wǎng)站、網(wǎng)站建設(shè)、移動(dòng)互聯(lián)產(chǎn)品、全網(wǎng)營銷推廣服務(wù)為核心業(yè)務(wù)。十余年網(wǎng)站制作的經(jīng)驗(yàn)，使用新網(wǎng)站建設(shè)技術(shù)，全新開發(fā)出的標(biāo)準(zhǔn)網(wǎng)站，不但價(jià)格便宜而且實(shí)用、靈活，特別適合中小公司網(wǎng)站制作。網(wǎng)站管理系統(tǒng)簡(jiǎn)單易用，維護(hù)方便，您可以完全操作網(wǎng)站資料，是中小公司快速網(wǎng)站建設(shè)的選擇。
MySQL 主從復(fù)制，不停機(jī)添加新從節(jié)點(diǎn)：
1、主庫創(chuàng)建賬號(hào)：
修改主庫repl密碼：
show master status;
alter user repl@'%' identified by '123456';
grant replication slave,replication client on *.* to 'repl'@'%';
flush privilegs;
2、從庫配置（創(chuàng)建從庫數(shù)據(jù)庫過程簡(jiǎn)略）：
開啟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 risks
symbolic-links=0
basedir=/usr/local/mysql
datadir=/data1/data
socket=/tmp/mysql.sock
port=3306
server-id =60182
replicate-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-bin
binlog_format = MIXED
skip-slave-start = 1
expire_logs_days=3
#validate_password_policy=0
#validate_password_length=3
relay-log-index=/data1/log/mysql-relay
relay-log=/data1/log/mysql-relay
log-bin=/data1/log/mysql-bin
#log-error=log.err
explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error=/data1/log/mysql.err
pid-file=/data1/tmp/mysqld.pid
初始化數(shù)據(jù)庫：
正常初始化：
[root@centos_TP bin]# ./mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
2020-01-14T08:48:27.965207Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-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
#開啟數(shù)據(jù)庫
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
#登錄數(shù)據(jù)庫，修改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遠(yuǎn)程登錄用戶：
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;
#創(chuàng)建slave賬號(hào)
mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by '123456';
#在slave節(jié)點(diǎn)上執(zhí)行
mysql> set global read_only=1;
#由于從庫隨時(shí)會(huì)提升成主庫，不能寫在配置文件里
3、備份主庫：
[root@localhost dbdata]# mysqldump -uroot -p --routines --single_transaction --master-data=2 -B cat qc_bh > all.sql
參數(shù)說明：
--routines：導(dǎo)出存儲(chǔ)過程和函數(shù)
--single_transaction：導(dǎo)出開始時(shí)設(shè)置事務(wù)隔離狀態(tài)，并使用一致性快照開始事務(wù)，然后unlock tables;而lock-tables是鎖住一張表不能寫操作，直到dump完畢。
--master-data：默認(rèn)等于1，將dump起始（change master to）binlog點(diǎn)和pos值寫到結(jié)果中，等于2是將change master to寫到結(jié)果中并注釋。
4、從庫創(chuàng)建數(shù)據(jù)庫，并導(dǎo)入數(shù)據(jù)
將dump的數(shù)據(jù)拷貝到從庫后開始導(dǎo)數(shù)據(jù)
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、啟動(dòng)從庫
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 status\G;
*************************** 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: 6606
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: 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 specified
顯示：
看到IO和SQL線程均為YES，說明主從配置成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Read_Master_Log_Pos: 677960018表示一直在追binlog日志。
“MySQL5.7主從添加新從庫的方法”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
分享文章：MySQL5.7主從添加新從庫的方法
網(wǎng)頁鏈接：http://www.dlmjj.cn/article/gisjdh.html
