服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql实现主从配置和多主多从配置

Mysql实现主从配置和多主多从配置

2021-08-10 18:11_微风轻起 Mysql

大型网站为了软解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。必然会想到假设服务器群，来分担主数据库的压力。今天总结一下利用MySQL主从配置，减轻数据库压力

我们现在模拟的是主从(1台主机、一台从机)，其主从同步的原理，就是对bin-log二进制文件的同步，将这个文件的内容从主机同步到从机。

一、配置文件的修改

1、主机配置文件修改配置

我们首先需要mysql主机(192.168.254.130)的/etc/my.cnf配置文件，添加如下配置:

?
1
2
3
4
5
6
7
8
9
10
11
#主机唯一ID
server-id=1
#二进制日志
log-bin=mysql-bin
#不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#同步的数据库名称
binlog-do-db=mycat
#二进制的格式
binlog_format=STATEMENT

我们看下目前整个my.cnf文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[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]#

修改主机的配置文件后，我们需要通过命令重启下服务:

?
1
2
3
4
5
[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

然后我们修改下从机(192.168.254.131)的配置文件。

2、从机的配置

从机的配置修改比较简单：

?
1
2
3
4
#从机机器唯一ID
server-id=2
#中继日志
relay-log=mysql-relay

同样修改配置后，我们重启下从机

二、mysql客户端命令操作

下面我们可以通过命令连接到mysql的命令端：

?
1
2
3
4
[root@localhost bin]#
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# ./mysql -uroot -p

1、主机操作

1）、创建同步用户

首先我们可以在主机创建一个专门用于主从同步用户，通过命令：

?
1
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';

2）、查看同步文件状态

然后我们通过show master status;查看主机的同步内容状态：

?
1
2
3
4
5
6
7
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、从机操作

1）、设置从机的主机

执行如下命令，这里设置了我们与主机建立同步的相关信息

?
1
2
3
4
5
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;

这里如果提示已经设置了主机配置，可以通过stop slave&reset master进行重置。

2）、启动同步

下面我们再通过start slave开启同步：

​ 就可以看到：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
*************************** 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这里有错误，我们就可以去看下日志报的什么问题了，目前我这个是因为同步用户写错了才不能同步，按上面说的先停止同步重置，修改后同步命令，再操作一遍就可以了。

三、主从同步测试

1、主机创建库

我们先在主机创建我们前面设置的要同步的数据库mycat：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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、从机查看库

然后我们就能在从机看到这个库了

?
1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mycat              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3、表数据的初始

下面我们进行表数据的测试

1）、主机
首先我们再主机建立表并插入数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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）、从机
下面我们在从机查看看有没有成功同步：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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>

可以看到我们的主从配置已经成功了。

四、多主多从

我们还可以多主多从，例如我们一个主从序列是编号1位主机、编号2为从机，然后编号3为主机、编号4为从机，同时编号1主机与编号3主机相互为主从，这样就是其中主机一台有问题，整个mysql集群还是能正常工作。

​ 由于目前只有3台机，只使用三台来写demo(一台windows，两台linux)。

1、编号1主机(192.168.254.30)

1）、修改配置
我们首先需要修改其原来的etc/my.cnf文件，添加：

?
1
2
3
4
5
6
# 作为从机也修改其bin-log日志
log-slave-updates
#自增长的幅度
auto-increment-increment=2
#自增长的开始位置
auto-increment-offset=1

整个文件的信息

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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]#

修改这个文件后我们需要重启机器

2、编号2从机（192.168.254.31）

这台原来已经配置其连接30机器了，所以这次不用修改

3、编号3主机(192.168.254.1)

1）、修改配置文件
由于这台机器是windows，所以我们需要修改其的my.ini文件，在其最后面添加

?
1
2
3
4
5
6
7
8
9
10
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，同时也改了其的增长开始点auto-increment-offset=2。同时再重启服务。

2）、创建同步用户
首先我们可以在主机创建一个专门用于主从同步用户，通过命令：

​

?
1
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'%' IDENTIFIED BY '123456';

3）、查看状态

?
1
2
3
4
5
6
7
8
9
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）、设置同步的状态

下面我们运行其连接的主机(30)信息

?
1
2
3
4
5
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）、编号1主机同步(192.168.254.30)
我们需要设置其去同步编号3主机(192.168.254.1)，即我们前面查看的编号3的(master status)：

?
1
2
3
4
5
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;

然后我们在编号1主机执行同步start slave;，也在编号3主机执行同步start slave;。

4、测试查看

1）、可能的问题(可略过)

现在我们测试，然后分别查看这两台的master状态show master status;。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
........
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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。这里可能有问题，我们需要自己解决，例如我在编号1机器修改配置，然后在查看其的状态，

?
1
2
3
4
5
6
7
8
9
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>

我如果以这个消息去让编号3机器同步编号1，就会报(因为我又运行了一条新的插入语句)，但建表语句是在日志mysql-bin.000001，而这里我因为重启了，其有有新的mysql-bin.000002，所以有修改回了原来编号2的同步信息。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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）、在编号3插入数据
下面我们再编号3插入数据，看编号1、2能不能看到

在编号3操作：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>

在编号1查看

?
1
2
3
4
5
6
7
8
9
10
11
mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | petty |
|  2 | TOm   |
|  3 | kitt  |
+----+-------+
3 rows in set (0.00 sec)
 
mysql>

在编号2查看

?
1
2
3
4
5
6
7
8
9
10
11
mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | petty |
|  2 | TOm   |
|  3 | kitt  |
+----+-------+
3 rows in set (0.00 sec)
 
mysql>

可以看到目前我们已经同步成功了，在编号1中能查看到主机编号3的插入信息。

3）、编号1处理数据

下面我们在编号1操作查看

编号1：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>

编号3：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | petty |
|  2 | TOm   |
|  3 | kitt  |
|  4 | lisa  |
+----+-------+
4 rows in set (0.00 sec)
 
mysql>

可以看到其是相互同步的。

到此这篇关于Mysql实现主从配置和多主多从配置的文章就介绍到这了,更多相关Mysql 主从配置和多主多从配置内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/qq_25179481/article/details/116430663

  • mysql
  • 主从配置
  • 多主多从配置

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 1054 　 2006 　 1175 　 1615 　 1215 　 10048 　 1130 　 1366 　 2003 　 28000 　 1135 　 1577 　 1067 　 1786 　 1093 　 1665 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图