mysql搭建主從復(fù)制的實(shí)現(xiàn)步驟
更新時(shí)間：2024年11月07日 11:41:05 作者：小丁學(xué)Java
在MySQL集群中,主庫(kù)更新會(huì)同步到從庫(kù),但從庫(kù)更新不同步到主庫(kù),主從復(fù)制能分?jǐn)倝毫?本文就來(lái)介紹一下mysql搭建主從復(fù)制的實(shí)現(xiàn)步驟,感興趣的可以了解一下
主庫(kù)更新，從庫(kù)會(huì)同步更新。從庫(kù)更新，主庫(kù)一般是不會(huì)同步更新的，如果發(fā)生主庫(kù)也同步更新，可能出現(xiàn)短暫bug，或者主從配置有問題。mysql集群：
單臺(tái)設(shè)備的負(fù)載壓力：主從復(fù)制
集群：分?jǐn)傇L問壓力和存儲(chǔ)壓力
需求：使用 3306 mysql當(dāng)作主， 3316 mysql 當(dāng)作從，在3306中對(duì) mydb2/mydb3 數(shù)據(jù)庫(kù)所有的操作，希望能夠主從復(fù)制同步到3316，其他的數(shù)據(jù)庫(kù)操作不同步。
1、準(zhǔn)備主服務(wù)器
docker run -d \ --name spzx-mysql \ -p 3306:3306 \ -v mysql_data:/var/lib/mysql \ -v mysql_conf:/etc/mysql \ --restart=always \ --privileged=true \ -e MYSQL_ROOT_PASSWORD=123456 \ mysql:8
[root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS ab66508d9441 mysql:8 "docker-entrypoint.s…" 8 months ago Up 9 days 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp spzx-mysql
此時(shí)我已經(jīng)有一個(gè)主服務(wù)器 spzx-mysql
2、準(zhǔn)備從服務(wù)器
docker run -d \ -p 3316:3306 \ -v mysql-slave1-conf:/etc/mysql/conf.d \ -v mysql-slave1-data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=123456 \ --name atguigu-mysql-slave1 \ mysql:8
[root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES c236f876ae40 mysql:8 "docker-entrypoint.s…" 10 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp atguigu-mysql-slave1 ab66508d9441 mysql:8 "docker-entrypoint.s…" 8 months ago Up 9 days 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp spzx-mysql
3、主庫(kù)配置
- 先在主mysql中配置 記錄mydb2/mydb3庫(kù)的操作日志到binlog日志文件中
– 主庫(kù)寫操作會(huì)按照配置記錄到二進(jìn)制文件中（binlog）
– 主庫(kù)需要?jiǎng)?chuàng)建一個(gè)從賬戶并分配可以讀取binlog日志的權(quán)限 - 在從mysql中配置中繼日志文件，用來(lái)保存讀取到的mysql主的 binlog 日志
– 從庫(kù)可以開啟主從復(fù)制，從指定的主庫(kù)的binlog文件中加載日志緩存到自己的relaylog文件中，最后通過一個(gè)sql線程將relaylog文件中的日志replay到自己的庫(kù)表中
– 從庫(kù)需要使用主庫(kù)提供的賬號(hào)和主庫(kù)的binlog文件建立連接
3.1、創(chuàng)建MySQL主服務(wù)器配置文件：
[root@localhost ~]# docker inspect spzx-mysql
"Mounts": [
{
"Type": "volume",
"Name": "mysql_conf",
"Source": "/var/lib/docker/volumes/mysql_conf/_data",
"Destination": "/etc/mysql",
"Driver": "local",
"Mode": "z",
"RW": true,
"Propagation": ""
},
{
"Type": "volume",
"Name": "mysql_data",
"Source": "/var/lib/docker/volumes/mysql_data/_data",
"Destination": "/var/lib/mysql",
"Driver": "local",
"Mode": "z",
"RW": true,
"Propagation": ""
}
],
[root@localhost _data]# cd /var/lib/docker/volumes/mysql_conf/_data [root@localhost _data]# ll 總用量 8 drwxrwxr-x. 2 root root 41 12月 26 2023 conf.d -rw-rw-r--. 1 root root 1080 12月 21 2021 my.cnf -rw-r--r--. 1 root root 1448 9月 28 2021 my.cnf.fallback
[root@localhost _data]# vim my.cnf
配置如下內(nèi)容：
[mysqld] # 服務(wù)器唯一id，默認(rèn)值1 server-id=1 # 設(shè)置日志格式，默認(rèn)值ROW。row（記錄行數(shù)據(jù)） statement（記錄sql） mixed（混合模式） binlog_format=STATEMENT # 二進(jìn)制日志名，默認(rèn)binlog # log-bin=binlog log-bin=spzxbinlog # 設(shè)置需要復(fù)制的數(shù)據(jù)庫(kù)，默認(rèn)復(fù)制全部數(shù)據(jù)庫(kù) binlog-do-db=mydb2 binlog-do-db=mydb3 # 設(shè)置不需要復(fù)制的數(shù)據(jù)庫(kù) binlog-ignore-db=mydb4 #binlog-ignore-db=infomation_schema
[root@localhost _data]# docker restart spzx-mysql spzx-mysql
[root@localhost _data]# ll ../../mysql_data/_data/
4、從庫(kù)配置
[root@localhost _data]# docker inspect atguigu-mysql-slave1
vim /var/lib/docker/volumes/mysql-slave1-conf/_data/my.cnf
配置如下內(nèi)容：
[mysqld] # 服務(wù)器唯一id，每臺(tái)服務(wù)器的id必須不同，如果配置其他從機(jī)，注意修改id server-id=2 # 中繼日志名，默認(rèn)xxxxxxxxxxxx-relay-bin #relay-log=relay-bin
[root@localhost _data]# docker restart atguigu-mysql-slave1 atguigu-mysql-slave1
5、搭建主從&測(cè)試
5.1、使用命令行登錄MySQL主服務(wù)器
[root@localhost _data]# docker exec -it spzx-mysql /bin/bash root@ab66508d9441:/# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
5.2、主機(jī)中查詢master狀態(tài)：
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | spzxbinlog.000001 | 156 | mydb2,mydb3 | mydb4 | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)
5.3、從機(jī)中查詢slave狀態(tài)：
[root@localhost ~]# docker exec -it atguigu-mysql-slave1 /bin/bash ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) root@c236f876ae40:/# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> show slave status; Empty set, 1 warning (0.02 sec)
從庫(kù)必須和主庫(kù)主動(dòng)建立連接 開啟自己的sql和io線程
5.4、主機(jī)中創(chuàng)建slave用戶：
-- 創(chuàng)建slave用戶 CREATE USER 'atguigu_slave'@'%'; -- 設(shè)置密碼 ALTER USER 'atguigu_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; -- 授予復(fù)制權(quán)限 GRANT REPLICATION SLAVE ON *.* TO 'atguigu_slave'@'%'; -- 刷新權(quán)限 FLUSH PRIVILEGES;
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | spzxbinlog.000001 | 1074 | mydb2,mydb3 | mydb4 | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
5.5、在從機(jī)上配置主從關(guān)系：
CHANGE MASTER TO MASTER_HOST='192.168.74.148', MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.148',
-> MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
-> MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074;
Query OK, 0 rows affected, 9 warnings (0.05 sec)
mysql> show slave status; +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace | +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ | | 192.168.74.148 | atguigu_slave | 3306 | 60 | spzxbinlog.000001 | 1074 | c236f876ae40-relay-bin.000001 | 4 | spzxbinlog.000001 | No | No | | | | | | | 0 | | 0 | 1074 | 156 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | | 0 | | mysql.slave_master_info | 0 | NULL | | 86400 | | | | | | | | 0 | | | | | 0 | | +----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ 1 row in set, 1 warning (0.00 sec)
5.6、啟動(dòng)從庫(kù)的io和sql線程：都啟動(dòng)成功主從才搭建成功
mysql> start slave; Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status; +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace | +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ | Waiting for source to send event | 192.168.74.148 | atguigu_slave | 3306 | 60 | spzxbinlog.000001 | 1074 | c236f876ae40-relay-bin.000002 | 325 | spzxbinlog.000001 | Yes | Yes | | | | | | | 0 | | 0 | 1074 | 541 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | af98f4d4-a3ca-11ee-b194-0242ac110002 | mysql.slave_master_info | 0 | NULL | Replica has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | | | 0 | | +----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+ 1 row in set, 1 warning (0.01 sec)
6、在3306主機(jī)上創(chuàng)建mydb1
此時(shí)刷新3316從數(shù)據(jù)庫(kù)，發(fā)現(xiàn)沒有mydb1
7、在3306主機(jī)上創(chuàng)建mydb2
此時(shí)刷新3316從數(shù)據(jù)庫(kù)，發(fā)現(xiàn)從機(jī)復(fù)制了主機(jī)中的mydb2數(shù)據(jù)庫(kù)到從機(jī)中
8、在3306主機(jī)上創(chuàng)建mydb3
9、在3306主機(jī)上創(chuàng)建mydb4
到此這篇關(guān)于mysql搭建主從復(fù)制的實(shí)現(xiàn)步驟的文章就介紹到這了,更多相關(guān)mysql搭建主從復(fù)制內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
MySQL數(shù)據(jù)庫(kù)為列提供了一種自增屬性,本文主要介紹了MySQL自增列解析,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
2024-09-09 
MYSQL的REPLACE和ON DUPLICATE KEY UPDATE語(yǔ)句介紹解決問題實(shí)例
這篇文章主要介紹了MYSQL的REPLACE和ON DUPLICATE KEY UPDATE語(yǔ)句介紹解決問題實(shí)例,需要的朋友可以參考下
2014-04-04 
在Mysql上創(chuàng)建數(shù)據(jù)表實(shí)例代碼
這篇文章主要介紹了如何在Mysql上創(chuàng)建數(shù)據(jù)表,需要的朋友可以參考下
2014-03-03 
mysql中數(shù)據(jù)庫(kù)覆蓋導(dǎo)入的幾種方式總結(jié)
這篇文章主要介紹了mysql中數(shù)據(jù)庫(kù)覆蓋導(dǎo)入的幾種方式總結(jié)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
2022-03-03 
ubuntu 16.04下mysql5.7.17開放遠(yuǎn)程3306端口
這篇文章主要介紹了ubuntu 16.04下mysql5.7.17開放遠(yuǎn)程3306端口的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
2017-01-01 
最近生產(chǎn)爆出一條慢sql，原因是用了or和!=，導(dǎo)致索引失效。于是，總結(jié)了索引失效的十大雜癥，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
2021-09-09 
這篇文章主要為大家詳細(xì)介紹了MySQL 5.7.18 免安裝版配置教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
2017-05-05 
這篇文章主要介紹了MySQL出現(xiàn)莫名其妙的斷開連接以及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
2023-11-11 
這篇文章主要介紹了MySQL備份與恢復(fù)之冷備，冷備一般需要定制計(jì)劃，比如什么時(shí)候做備份，每次對(duì)哪些數(shù)據(jù)進(jìn)行備份等等，對(duì)冷備感興趣的小伙伴們可以參考一下
2015-08-08