Mysql實(shí)現(xiàn)主從配置和多主多從配置
更新時(shí)間：2021年06月02日 15:45:14 作者：_微風(fēng)輕起
大型網(wǎng)站為了軟解大量的并發(fā)訪問(wèn)，除了在網(wǎng)站實(shí)現(xiàn)分布式負(fù)載均衡，遠(yuǎn)遠(yuǎn)不夠。必然會(huì)想到假設(shè)服務(wù)器群，來(lái)分擔(dān)主數(shù)據(jù)庫(kù)的壓力。今天總結(jié)一下利用MySQL主從配置，減輕數(shù)據(jù)庫(kù)壓力
我們現(xiàn)在模擬的是主從(1臺(tái)主機(jī)、一臺(tái)從機(jī))，其主從同步的原理，就是對(duì)bin-log二進(jìn)制文件的同步，將這個(gè)文件的內(nèi)容從主機(jī)同步到從機(jī)。
一、配置文件的修改
1、主機(jī)配置文件修改配置
我們首先需要mysql主機(jī)(192.168.254.130)的/etc/my.cnf配置文件，添加如下配置:
#主機(jī)唯一ID server-id=1 #二進(jìn)制日志 log-bin=mysql-bin #不需要同步的數(shù)據(jù)庫(kù) binlog-ignore-db=mysql binlog-ignore-db=information_schema #同步的數(shù)據(jù)庫(kù)名稱 binlog-do-db=mycat #二進(jìn)制的格式 binlog_format=STATEMENT
我們看下目前整個(gè)my.cnf文件
[root@localhost Desktop]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/usr/local/mysql/data/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=1 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-do-db=mycat binlog_format=STATEMENT [mysqld_safe] log-error=/usr/local/mysql/data/mysqld.log pid-file=/usr/local/mysql/data/mysqld/mysqld.pid [root@localhost Desktop]#
修改主機(jī)的配置文件后，我們需要通過(guò)命令重啟下服務(wù):
[root@localhost support-files]# ls magic mysqld_multi.server mysql-log-rotate mysql.server [root@localhost support-files]# pwd /usr/local/mysql/support-files [root@localhost support-files]# ./mysql.server restart
然后我們修改下從機(jī)(192.168.254.131)的配置文件。
2、從機(jī)的配置
從機(jī)的配置修改比較簡(jiǎn)單：
#從機(jī)機(jī)器唯一ID server-id=2 #中繼日志 relay-log=mysql-relay
同樣修改配置后，我們重啟下從機(jī)
二、mysql客戶端命令操作
下面我們可以通過(guò)命令連接到mysql的命令端：
[root@localhost bin]# [root@localhost bin]# pwd /usr/local/mysql/bin [root@localhost bin]# ./mysql -uroot -p
1、主機(jī)操作
1）、創(chuàng)建同步用戶
首先我們可以在主機(jī)創(chuàng)建一個(gè)專門用于主從同步用戶，通過(guò)命令：
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';
2）、查看同步文件狀態(tài)
然后我們通過(guò)show master status;查看主機(jī)的同步內(nèi)容狀態(tài)：
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mycat | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
2、從機(jī)操作
1）、設(shè)置從機(jī)的主機(jī)
執(zhí)行如下命令，這里設(shè)置了我們與主機(jī)建立同步的相關(guān)信息
CHANGE MASTER TO MASTER_HOST='192.168.254.130', MASTER_USER='SLAVE', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=430;
這里如果提示已經(jīng)設(shè)置了主機(jī)配置，可以通過(guò)stop slave&reset master進(jìn)行重置。
2）、啟動(dòng)同步
下面我們?cè)偻ㄟ^(guò)start slave開(kāi)啟同步：
就可以看到：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.254.130
Master_User: SLAVE
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 592
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 482
Relay_Master_Log_File: mysql-bin.000001
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: 592
Relay_Log_Space: 685
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: 74397a99-accf-11eb-ae0d-000c2912d302
Master_Info_File: /usr/local/mysql/data/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)
mysql>
這里我們可以看到Slave_IO_Running、Slave_SQL_Running都為YES，則成功了，如果是下面這種：
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.254.130
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
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: 430
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 'slave@192.168.254.130:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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: 210505 00:18:08
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)
我們可以看到Last_IO_Error這里有錯(cuò)誤，我們就可以去看下日志報(bào)的什么問(wèn)題了，目前我這個(gè)是因?yàn)橥接脩魧?xiě)錯(cuò)了才不能同步，按上面說(shuō)的先停止同步重置，修改后同步命令，再操作一遍就可以了。
三、主從同步測(cè)試
1、主機(jī)創(chuàng)建庫(kù)
我們先在主機(jī)創(chuàng)建我們前面設(shè)置的要同步的數(shù)據(jù)庫(kù)mycat：
mysql> create database mycat; Query OK, 1 row affected (0.00 sec) mysql> use mycat; Database changed mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mycat | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
2、從機(jī)查看庫(kù)
然后我們就能在從機(jī)看到這個(gè)庫(kù)了
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mycat | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
3、表數(shù)據(jù)的初始
下面我們進(jìn)行表數(shù)據(jù)的測(cè)試
1）、主機(jī)
首先我們?cè)僦鳈C(jī)建立表并插入數(shù)據(jù)
mysql> use mycat;
Database changed
mysql>
mysql> create table `test1`(
-> id int auto_increment not null primary key,
-> name varchar(10) default null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test1(`id`,`name`) value(1,"petty");
Query OK, 1 row affected (0.16 sec)
mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | petty |
+----+-------+
1 row in set (0.00 sec)
mysql>
2）、從機(jī)
下面我們?cè)趶臋C(jī)查看看有沒(méi)有成功同步：
mysql> use mycat; 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_mycat | +-----------------+ | test1 | +-----------------+ 1 row in set (0.00 sec) mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | +----+-------+ 1 row in set (0.00 sec) mysql>
可以看到我們的主從配置已經(jīng)成功了。
四、多主多從
我們還可以多主多從，例如我們一個(gè)主從序列是編號(hào)1位主機(jī)、編號(hào)2為從機(jī)，然后編號(hào)3為主機(jī)、編號(hào)4為從機(jī)，同時(shí)編號(hào)1主機(jī)與編號(hào)3主機(jī)相互為主從，這樣就是其中主機(jī)一臺(tái)有問(wèn)題，整個(gè)mysql集群還是能正常工作。
由于目前只有3臺(tái)機(jī)，只使用三臺(tái)來(lái)寫(xiě)demo(一臺(tái)windows，兩臺(tái)linux)。
1、編號(hào)1主機(jī)(192.168.254.30)
1）、修改配置
我們首先需要修改其原來(lái)的etc/my.cnf文件，添加：
# 作為從機(jī)也修改其bin-log日志 log-slave-updates #自增長(zhǎng)的幅度 auto-increment-increment=2 #自增長(zhǎng)的開(kāi)始位置 auto-increment-offset=1
整個(gè)文件的信息
[root@localhost Desktop]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data basedir=/usr/local/mysql socket=/usr/local/mysql/data/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=1 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-do-db=mycat binlog_format=STATEMENT log-slave-updates auto-increment-increment=2 auto-increment-offset=1 [mysqld_safe] log-error=/usr/local/mysql/data/mysqld.log pid-file=/usr/local/mysql/data/mysqld/mysqld.pid [root@localhost Desktop]#
修改這個(gè)文件后我們需要重啟機(jī)器
2、編號(hào)2從機(jī)（192.168.254.31）
這臺(tái)原來(lái)已經(jīng)配置其連接30機(jī)器了，所以這次不用修改
3、編號(hào)3主機(jī)(192.168.254.1)
1）、修改配置文件
由于這臺(tái)機(jī)器是windows，所以我們需要修改其的my.ini文件，在其最后面添加
server-id=3 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-do-db=mycat binlog_format=STATEMENT log-slave-updates auto-increment-increment=2 auto-increment-offset=2
注意我們上面改了server-id，同時(shí)也改了其的增長(zhǎng)開(kāi)始點(diǎn)auto-increment-offset=2。同時(shí)再重啟服務(wù)。
2）、創(chuàng)建同步用戶
首先我們可以在主機(jī)創(chuàng)建一個(gè)專門用于主從同步用戶，通過(guò)命令：
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';
3）、查看狀態(tài)
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mycat | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec) mysql>
4）、設(shè)置同步的狀態(tài)
下面我們運(yùn)行其連接的主機(jī)(30)信息
CHANGE MASTER TO MASTER_HOST='192.168.254.130', MASTER_USER='SLAVE', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=430;
5）、編號(hào)1主機(jī)同步(192.168.254.30)
我們需要設(shè)置其去同步編號(hào)3主機(jī)(192.168.254.1)，即我們前面查看的編號(hào)3的(master status)：
CHANGE MASTER TO MASTER_HOST='192.168.254.1', MASTER_USER='SLAVE', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
然后我們?cè)诰幪?hào)1主機(jī)執(zhí)行同步start slave;，也在編號(hào)3主機(jī)執(zhí)行同步start slave;。
4、測(cè)試查看
1）、可能的問(wèn)題(可略過(guò))
現(xiàn)在我們測(cè)試，然后分別查看這兩臺(tái)的master狀態(tài)show master status;。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.254.1
Master_User: SLAVE
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.254.130
Master_User: SLAVE
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 462
Relay_Log_File: LAPTOP-QR83QEC0-relay-bin.000003
Relay_Log_Pos: 675
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
.........
可以看到它們的同步都是yes。這里可能有問(wèn)題，我們需要自己解決，例如我在編號(hào)1機(jī)器修改配置，然后在查看其的狀態(tài)，
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000002 | 462 | mycat | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec) mysql>
我如果以這個(gè)消息去讓編號(hào)3機(jī)器同步編號(hào)1，就會(huì)報(bào)(因?yàn)槲矣诌\(yùn)行了一條新的插入語(yǔ)句)，但建表語(yǔ)句是在日志mysql-bin.000001，而這里我因?yàn)橹貑⒘?，其有有新的mysql-bin.000002，所以有修改回了原來(lái)編號(hào)2的同步信息。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.254.130
Master_User: SLAVE
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 462
Relay_Log_File: LAPTOP-QR83QEC0-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table 'mycat.test1' doesn't exist' on query. Default database: 'mycat'. Query: 'insert into test1(`id`,`name`) value(2,"TOm")'
2）、在編號(hào)3插入數(shù)據(jù)
下面我們?cè)倬幪?hào)3插入數(shù)據(jù)，看編號(hào)1、2能不能看到
在編號(hào)3操作：
mysql> insert into test1(`id`,`name`) value(3,"kitt"); Query OK, 1 row affected (0.01 sec) mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | | 2 | TOm | | 3 | kitt | +----+-------+ 3 rows in set (0.00 sec) mysql>
在編號(hào)1查看
mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | | 2 | TOm | | 3 | kitt | +----+-------+ 3 rows in set (0.00 sec) mysql>
在編號(hào)2查看
mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | | 2 | TOm | | 3 | kitt | +----+-------+ 3 rows in set (0.00 sec) mysql>
可以看到目前我們已經(jīng)同步成功了，在編號(hào)1中能查看到主機(jī)編號(hào)3的插入信息。
3）、編號(hào)1處理數(shù)據(jù)
下面我們?cè)诰幪?hào)1操作查看
編號(hào)1：
mysql> insert into test1(`id`,`name`) value(4,"lisa"); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | | 2 | TOm | | 3 | kitt | | 4 | lisa | +----+-------+ 4 rows in set (0.00 sec) mysql>
編號(hào)3：
mysql> select * from test1; +----+-------+ | id | name | +----+-------+ | 1 | petty | | 2 | TOm | | 3 | kitt | | 4 | lisa | +----+-------+ 4 rows in set (0.00 sec) mysql>
可以看到其是相互同步的。
到此這篇關(guān)于Mysql實(shí)現(xiàn)主從配置和多主多從配置的文章就介紹到這了,更多相關(guān)Mysql 主從配置和多主多從配置內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL數(shù)據(jù)庫(kù)遷移到Oracle數(shù)據(jù)庫(kù)的完整步驟記錄
在研發(fā)過(guò)程中可能會(huì)用到將表數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)及數(shù)據(jù)遷移到另外一種數(shù)據(jù)庫(kù)中,比如說(shuō)從mysql中遷移到oracle中,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)遷移到Oracle數(shù)據(jù)庫(kù)的完整步驟,需要的朋友可以參考下2024-06-06
本文主要介紹了MySQL添加注釋(comment)的方法步驟,字段或列的注釋是用屬性comment來(lái)添加,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05
解決mysql連接錯(cuò)誤errorCode 0,state 08S01
這篇文章主要介紹了解決mysql連接錯(cuò)誤errorCode 0,state 08S01,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-05-05
這篇文章主要給大家介紹了關(guān)于MySQL兩個(gè)字段模糊匹配操作的相關(guān)資料,兩張MySQL表進(jìn)行模糊匹配可以使用like語(yǔ)法來(lái)實(shí)現(xiàn),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-09-09
Windows環(huán)境下MySQL 8.0 的安裝、配置與卸載
這篇文章主要介紹了Windows環(huán)境下MySQL 8.0 的安裝、配置與卸載步驟，本文分步驟給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-09-09
這篇文章主要介紹了MySQL SQL語(yǔ)句分析查詢優(yōu)化,需要的朋友可以參考下2018-03-03
這篇文章主要給大家介紹了關(guān)于MySQL可重復(fù)讀級(jí)別能否解決幻讀的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
mysql insert 存在即不插入語(yǔ)法說(shuō)明
這篇文章主要介紹了mysql insert 存在即不插入語(yǔ)法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-03-03
分析mysql中一條SQL查詢語(yǔ)句是如何執(zhí)行的
為了充分發(fā)揮MySQL的性能并順利地使用，就必須正確理解其設(shè)計(jì)思想，因此，了解MySQL的邏輯架構(gòu)是必要的。本文將通過(guò)一條SQL查詢語(yǔ)句的具體執(zhí)行過(guò)程來(lái)詳細(xì)介紹MySQL架構(gòu)中的各個(gè)組件2021-06-06
這篇文章主要介紹了MySQL（InnoDB）是如何處理死鎖的，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04