部署mysql主從同步

配置mysql主從

分為主數(shù)據(jù)庫角色（master）、從數(shù)據(jù)庫服務(wù)器角色（slave）

網(wǎng)站服務(wù)器連接后存儲數(shù)據(jù)的服務(wù)器作為主服務(wù)器

自動同步主服務(wù)器上的數(shù)據(jù)

192.168.88.53 做master

啟用binlog日志文件?指定server_id 重啟服務(wù)

[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
......
log-bin=mysql53    # 自定義binlog日志文件
server-id=53    # 主從配置 server id 不能一樣
[root@mysql53 ~]# systemctl restart mysqld
[root@mysql53 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
[root@mysql53 ~]# ls /var/lib/mysql/mysql53.*
/var/lib/mysql/mysql53.000001  /var/lib/mysql/mysql53.index

用戶授權(quán)

mysql> create user repluser@"%" identified by "123qqq...A";
mysql> grant replication slave on *.* to repluser@"%";

# 驗(yàn)證登錄
[root@mysql54 ~]# mysql -h192.168.88.53 -urepluser -p123qqq...A
......
mysql> 

查看正在使用的binlog日志文件

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 |      667 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

192.168.88.54 做slave

指定server_id 并重啟數(shù)據(jù)庫服務(wù)

[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
......
server-id=54
[root@mysql54 ~]# systemctl restart mysqld

# 用一個已安裝mysql的虛擬機(jī)克隆出來的主從機(jī)器，在從機(jī)器上執(zhí)行此操作
[root@mysql54 ~]# rm -rf /var/lib/mysql/auto.cnf 
[root@mysql54 ~]# systemctl restart mysqld

指定主服務(wù)器信息

mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123qqq...A",master_log_file="mysql53.000001",master_log_pos=667;
mysql> show slave status \G
......
Slave_IO_Running: No
Slave_SQL_Running: No
......

啟動slave進(jìn)程

mysql> start slave;

查看狀態(tài)信息

mysql> show slave status \G

mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

主從驗(yàn)證

在master上添加數(shù)據(jù)

# -e 命令行下執(zhí)行數(shù)據(jù)庫命令
[root@mysql53 ~]# mysql -e 'create database bbsdb'
[root@mysql53 ~]# mysql -e 'create table bbsdb.a(id int)'
[root@mysql53 ~]# mysql -e 'insert into bbsdb.a values(110)'

在slave上查看驗(yàn)證

[root@mysql54 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| bbsdb              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mysql54 ~]# mysql -e 'desc bbsdb.a'
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
[root@mysql54 ~]# mysql -e 'select * from bbsdb.a'
+------+
| id   |
+------+
|  110 |
+------+

主從同步的工作原理

Master：啟用binlog日志，記錄所有的數(shù)據(jù)庫更新和修改操作

Slave：

Slave_IO：復(fù)制master主機(jī) binlog日志文件里的SQL命令到本機(jī)的relay-log(中繼日志文件)文件里

Slave_SQL：執(zhí)行本機(jī)relay-log文件里的SQL語句，實(shí)現(xiàn)與Master數(shù)據(jù)一致

排錯方法

Last_IO_Error:? ? ? ? IO線程報(bào)錯信息

報(bào)錯的原因：通常是 change master to 的配置項(xiàng)不正確

解決辦法：重新指定主服務(wù)器信息

stop slave;

change master to 服務(wù)器ip 用戶 密碼 日志文件名 偏移量;

start slave;

Last_SQL_Error:? ? ? ? SQL線程報(bào)錯信息

通常是執(zhí)行relay-log文件里的sql命令時失敗導(dǎo)致的

配置 一主多從 結(jié)構(gòu)

給已有的master服務(wù)器53添加第二臺slave角色的服務(wù)器55

指定server_id 并重啟mysql服務(wù)

[root@mysql55 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
......
server-id=55
[root@mysql55 ~]# systemctl restart mysqld

要手動同步主服務(wù)器比自己多的

在master服務(wù)器執(zhí)行完全備份 把備份文件拷貝給slave主機(jī)

mysql> show master status;    # 查看binlog日志文件 偏移量
mysql> show databases;        # 查看比slave多的庫
[root@mysql53 ~]# mysqldump -B bbsdb > /root/bbsdb.sql    # 完全備份
[root@mysql53 ~]# scp /root/bbsdb.sql root@192.168.88.55:/root

在slave服務(wù)器執(zhí)行 備份文件 恢復(fù)數(shù)據(jù)

[root@mysql55 ~]# mysql < /root/bbsdb.sql 
[root@mysql55 ~]# mysql
mysql> show databases;

指定主服務(wù)器信息

change master to 服務(wù)器的ip 用戶 密碼 日志文件名 偏移量

mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123qqq...A",master_log_file="mysql53.000002",master_log_pos=156;
mysql> show slave status \G
......
Slave_IO_Running: No
Slave_SQL_Running: No
......

注意:日志名和偏移量 要寫 在mysql53主機(jī)執(zhí)行完全備份之前查看到的日志名和偏移量

啟動slave

mysql> start slave;

查看狀態(tài)信息

mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

主從驗(yàn)證

在master上添加數(shù)據(jù)

mysql> insert into bbsdb.a values(114),(119),(122);
mysql> select * from bbsdb.a;
+------+
| id   |
+------+
|  110 |
|  114 |
|  119 |
|  122 |
+------+
4 rows in set (0.00 sec)

在slave上查看驗(yàn)證

# 55
mysql> select * from bbsdb.a;
+------+
| id   |
+------+
|  110 |
|  114 |
|  119 |
|  122 |
+------+
4 rows in set (0.00 sec)


# 54
mysql> select * from bbsdb.a;
+------+
| id   |
+------+
|  110 |
|  114 |
|  119 |
|  122 |
+------+
4 rows in set (0.00 sec)

部署mysql數(shù)據(jù)讀寫分離結(jié)構(gòu)

56? ? ? ? master數(shù)據(jù)庫服務(wù)器

57? ? ? ? slave數(shù)據(jù)庫服務(wù)器

58? ? ? ? 讀寫分離服務(wù)器

相關(guān)理論

把客戶端訪問數(shù)據(jù)庫服務(wù)的查詢訪問select、寫訪問insert 分別給不同的數(shù)據(jù)庫服務(wù)器處理

功能：減輕單臺數(shù)據(jù)庫服務(wù)器的并發(fā)訪問壓力

如何實(shí)現(xiàn)數(shù)據(jù)的讀寫分離

人肉分離? ? ? ? select-------->57

? ? ? ? ? ? ? ? ? ? ? ?insert-------->56

搭建讀寫分離服務(wù)實(shí)現(xiàn)

能夠?qū)崿F(xiàn)數(shù)據(jù)讀寫分離服務(wù)的軟件（中間件）有哪些

mysql-proxy mycat和mycat2

環(huán)境準(zhǔn)備

配置mysql主從結(jié)構(gòu)

56? ? ? ? master數(shù)據(jù)庫服務(wù)器

57? ? ? ? slave數(shù)據(jù)庫服務(wù)器

配置器mycat服務(wù)器

58

準(zhǔn)備軟件

安裝軟件

# 安裝jdk
[root@myscat58 ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@myscat58 ~]# java -version
# 安裝解壓命令
[root@myscat58 ~]# which unzip || yum -y install unzip
# 安裝mycat
[root@myscat58 ~]# unzip mycat2-install-template-1.21.zip 
[root@myscat58 ~]# mv mycat /usr/local/
# 安裝依賴
[root@myscat58 ~]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
# 修改權(quán)限
[root@myscat58 ~]# chmod -R 777 /usr/local/mycat/

修改配置

設(shè)置客戶端連接mycat服務(wù)時使用的用戶名和密碼

[root@myscat58 ~]# vim /usr/local/mycat/conf/users/root.user.json
{^M
        "dialect":"mysql",^M
        "ip":null,^M
        "password":"654321",^M
        "transactionType":"proxy",^M
        "username":"mycat"^M
}

設(shè)置mycat服務(wù)啟動時連接的數(shù)據(jù)庫服務(wù)器

[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json 
{^M
        "dbType":"mysql",^M
        "idleTimeout":60000,^M
        "initSqls":[],^M
        "initSqlsGetConnection":true,^M
        "instanceType":"READ_WRITE",^M
        "maxCon":1000,^M
        "maxConnectTimeout":3000,^M
        "maxRetryCount":5,^M
        "minCon":1,^M
        "name":"prototypeDs",^M
        "password":"123456",^M
        "type":"JDBC",^M
        "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",^M
        "user":"hmy",^M
        "weight":0^M
}

根據(jù)配置在本機(jī)運(yùn)行mysql服務(wù)并創(chuàng)建hmy用戶

[root@myscat58 ~]# yum -y install mysql-server mysql
[root@myscat58 ~]# systemctl start mysqld
[root@myscat58 ~]# mysql
mysql> create user hmy@"localhost" identified by '123456';
mysql> grant all on *.* to hmy@"localhost";
[root@myscat58 ~]# mysql -hlocalhost -uhmy -p123456

啟動mycat服務(wù)

[root@myscat58 ~]# ls /usr/local/mycat/logs/
[root@myscat58 ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
[root@myscat58 ~]# ls /usr/local/mycat/logs/
mycat.pid  wrapper.log
[root@myscat58 ~]# ss -ntulp | grep 8066
tcp   LISTEN 0      128                     *:8066             *:*    users:(("java",pid=22649,fd=71))  
[root@myscat58 ~]#

連接mycat服務(wù)

[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql> show databases;
+--------------------+
| `Database`         |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.16 sec)

連接mycat服務(wù)配置讀寫分離

MySQL主從結(jié)構(gòu) + MyCat2

主從結(jié)構(gòu) + mycat 軟件實(shí)現(xiàn) 數(shù)據(jù)的讀寫分離

添加數(shù)據(jù)源（定義數(shù)據(jù)庫服務(wù)器的ip地址）

# 連接mycat服務(wù)
[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
# 添加mysql56數(shù)據(jù)庫服務(wù)器
mysql> /*+ mycat:createdatasource{
    -> "name":"whost56", "url":"jdbc:mysql://192.168.88.56:3306","user":"hmy","password":"123456"}*/;
Query OK, 0 rows affected (0.30 sec)
# 添加mysql57數(shù)據(jù)庫服務(wù)器
mysql> /*+ mycat:createdatasource{
    -> "name":"whost57", "url":"jdbc:mysql://192.168.88.57:3306","user":"hmy","password":"123456"}*/;
Query OK, 0 rows affected (0.07 sec)
# 查看數(shù)據(jù)源
/*+mycat:showDataSources{}*/ \G
# 添加的數(shù)據(jù)源以文件的形式保存在安裝目錄下
[root@myscat58 ~]# ls /usr/local/mycat/conf/datasources/
prototypeDs.datasource.json  whost56.datasource.json  whost57.datasource.json

在數(shù)據(jù)庫服務(wù)器上添加hmy用戶

[root@mysql56 ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";

# 測試
[root@myscat58 ~]# mysql -h192.168.88.56 -uhmy -p123456
mysql>

[root@myscat58 ~]# mysql -h192.168.88.57 -uhmy -p123456
mysql>

創(chuàng)建集群 把56和57組成工作組

[root@myscat58 ~]#mysql -h127.0.0.1 -P8066 -umycat -p654321
# 創(chuàng)建集群
mysql> /*!mycat:createcluster{ "name":"rwcluster", "masters":["whost56"], "replicas":["whost57"] }*/;
# 查看集群信息
mysql> /*+ mycat:showClusters{}*/ \G*************************** 1. row ***************************
             NAME: rwcluster
      SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000
             TYPE: BALANCE_ALL
         WRITE_DS: whost56
          READ_DS: whost56,whost57
          WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
           READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
        AVAILABLE: true
*************************** 2. row ***************************
             NAME: prototype
      SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200
             TYPE: BALANCE_ALL
         WRITE_DS: prototypeDs
          READ_DS: prototypeDs
          WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
           READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
        AVAILABLE: true
2 rows in set (0.00 sec)

# 創(chuàng)建的集群以文件的形式保存在目錄下
[root@myscat58 ~]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json  rwcluster.cluster.json

指定數(shù)據(jù)庫服務(wù)器的角色

master角色機(jī)器負(fù)責(zé)寫

slave角色機(jī)器負(fù)責(zé)訪問

# 修改master角色主機(jī)僅負(fù)責(zé)寫訪問
[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"WRITE",    # 僅負(fù)責(zé)寫訪問
        "logAbandoned":true,
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"whost56",
        "password":"123456",
        "queryTimeout":0,
        "removeAbandoned":false,
        "removeAbandonedTimeoutSecond":180,
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"hmy",
        "weight":0
}
# 修改slave角色主機(jī)僅負(fù)責(zé)讀訪問
[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/whost57.datasource.json 
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ",    # 僅負(fù)責(zé)讀訪問
        "logAbandoned":true,
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"whost57",
        "password":"123456",
        "queryTimeout":0,
        "removeAbandoned":false,
        "removeAbandonedTimeoutSecond":180,
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.88.57:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"hmy",
        "weight":0
}

定義集群出來讀訪問的策略

集群處理查詢訪問的方式

[root@myscat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json 
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "whost56"
        ],
        "maxCon":2000,
        "name":"rwcluster",
        "readBalanceType":"BALANCE_ALL_READ",
        "replicas":[
                "whost57"
        ],
        "switchType":"SWITCH"
}

創(chuàng)建網(wǎng)站存儲數(shù)據(jù)使用的庫 并定義 庫存儲數(shù)據(jù)使用rwcluster

[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql> create database testdb;
# 指定testdb庫存儲數(shù)據(jù)使用的集群
[root@myscat58 ~]# vim /usr/local/mycat/conf/schemas/testdb.schema.json 
{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"testdb",
        "targetName":"rwcluster",    # 添加此行,之前創(chuàng)建的集群名rwcluster
        "shardingTables":{},
        "views":{}
}
[root@myscat58 ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@myscat58 ~]# ss -ntulp | grep 8066
tcp   LISTEN 0      128                     *:8066             *:*    users:(("java",pid=23325,fd=72))  
[root@myscat58 ~]# 

測試配置文章來源地址http://www.zghlxwxcb.cn/news/detail-804161.html

# 連接mycat服務(wù)建表插入記錄
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321
mysql> create table user(name char(10),passwd char(6));
mysql> insert into user values("A","123");
mysql> select * from user;
+------+--------+
| name | passwd |
+------+--------+
| A    | 123    |
+------+--------+
1 row in set (0.01 sec)

[root@mysql56 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A    | 123    |
+------+--------+

[root@mysql57 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A    | 123    |
+------+--------+

# 測試讀寫分離
# 在從服務(wù)器本機(jī)插入記錄，數(shù)據(jù)僅在從服務(wù)器有，主服務(wù)器沒有
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'insert into testdb.user values("B","678")'
[root@mysql57 ~]# mysql -e 'insert into testdb.user values("mysql57","57")'
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'select * from testdb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------+--------+
| name    | passwd |
+---------+--------+
| A       | 123    |
| B       | 678    |
| mysql57 | 57     |
+---------+--------+
# 主服務(wù)器數(shù)據(jù)不變，日志偏移量不不變
[root@mysql56 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A    | 123    |
| B    | 678    |
+------+--------+
[root@mysql56 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 |     2538 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

# 客戶端連接mycat服務(wù)讀/寫數(shù)據(jù)
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'insert into testdb.user values("bbb","222")'
# 在主服務(wù)器查看數(shù)據(jù)和日志偏移量
[root@mysql56 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 |     2828 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

到了這里，關(guān)于MySQL一主一從、配置一主多從結(jié)構(gòu)、數(shù)據(jù)讀寫分離的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！