mysql主从和双主

发表于   |   更新于 | 分类于 | | 阅读次数: | 总览

mysql 主从

修改 mysql-master1 的配置

1
2
3
4
5
6
7
8
9
10
skip-name-resolve
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
log-slave-updates = 1
binlog-ignore-db = mysql
binlog-ignore-db = test
expire_logs_days = 7

参数介绍

参数说明
skip-name-resolve选项可以禁用dns解析
server-id唯一标识主机，mysql主从每个mysql实例配置都不一样就行。这个值默认是0，如果是0，主服务器拒绝任何从服务器的连接。
log-bin开启二进制日志，该日志是在事务提交时写日志文件的。默认大小是1G，后面加001,002这样的后缀顺加。
sync_binlog表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，设置为1会影响mysql数据服务器的性能
binlog_checksum不进行校验
binlog_formatbinlog日志格式
log-slave-updates中继日志执行之后，这些变化是否需要计入自己的binarylog。 当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。 就是双主互相备份，或者多主循环备份
binlog-ignore-db指定不需要同步的数据库
expire_logs_daysbinlog日志保持天数

重启mysql

1
systemctl  restart mysqld

创建主从同步

登录master-1 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建同步账户
# grant replication slave,replication client on *.* to mysqlsave@'%' identified by '123456';
grant replication slave on *.* to xingxing@'172.21.%.%' identified by '123456';
flush privileges;

# 查看下log bin日志和pos值位置
flush logs;
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              | mysql,test       |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器同步数据

1
2
3
4
change master to master_host='172.21.17.52',master_user='xingxing',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=120; 

start slave;
show slave status\G;

关闭主从同步

1
2
3
4
5
6
7
8
# 停止主从
stop slave;

## 清除主从同步信息
# 清除binlog 文件名及位置
reset slave;  
# 清除slave 的连接配置信息   
reset slave all;

配置mysql 多主

前面做了mysql的主从同步，mysql多主就是在相互做同步，需要注意的是每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。在上述的主从配置中增加如下配置

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
# master-1 配置
skip-name-resolve
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
log-slave-updates = 1
binlog-ignore-db = mysql,test
expire_logs_days = 7
auto-increment-offset = 1
auto-increment-increment = 2

# master-2 配置
skip-name-resolve
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
log-slave-updates = 1
binlog-ignore-db = mysql,test
expire_logs_days = 7
auto-increment-offset = 2
auto-increment-increment = 2

# 重启
systemctl  restart mysqld

参数介绍

参数说明
auto-increment-offset表示这台服务器的序号，从该值开始
auto-increment-increment服务器的数量

修改或创建账户授权

1
2
# 所有mysql 服务器执行
grant replication slave,replication client on *.* to xxlaila@'172.21.%.%' identified by '123456';

查看下log bin日志和pos值位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# master-1
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      857 |              | mysql,test       |                   |
+------------------+----------+--------------+------------------+-------------------+

# master-2
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      351 |              | mysql,test       |                   |
+------------------+----------+--------------+------------------+-------------------+

配置同步

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
# mseter-1 同步操作
unlock tables;
stop slave;

## 同步master-2
change master to master_host='172.21.16.87',master_user='xxlaila',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=351;

start slave;
## 查看同步状态，如下出现两个“Yes”，表明同步成功！
show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.16.87
                  Master_User: xxlaila
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 351
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
        ……………………………………………………………………
        Seconds_Behind_Master: 0
        ……………………………………………………………………
#master1就和master2实现了主从同步，即master1同步master2的数据。

# master-2 同步操作
unlock tables;
stop slave;

## 同步master-1
change master to master_host='172.21.17.52',master_user='xxlaila',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=857;

start slave;
## 查看同步状态，如下出现两个“Yes”，表明同步成功！
show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.17.52
                  Master_User: xxlaila
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 857
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 279
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
        …………………………………………………………………………
        Seconds_Behind_Master: 0
        …………………………………………………………………………

# master2就和master1实现了主从同步，即master2也同步master1的数据。

测试同步

在mster-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
25
26
# 创建数据库
create database xxlaila DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 创建表
create table if not exists haha (
  id int(10) PRIMARY KEY AUTO_INCREMENT,
  name varchar(50) NOT NULL);

# 插入数据
insert into haha values(1,"张三"),(2,"李四");

# 测试自增id
insert into haha(name) values("小红");
insert into haha(name) values("小花");
# 查询数据
select * from haha;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 小红   |
|  5 | 小花   |
+----+--------+

# 登录master-2数据库，查询数据库是否有数据

master-2 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 创建数据库
create database xxlaila1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 对xxlaila数据库写入新的数据
insert into xxlaila.haha values(4,"王五"),(6,"张麻子");

# 测试自增id
insert into haha(name) values("小妹");
insert into haha(name) values("小第");

# 查询xxlaila数据库验证数据是否插入和同步
select * from haha;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 李四      |
|  3 | 小红      |
|  4 | 王五      |
|  5 | 小花      |
|  6 | 张麻子    |
| 10 | 小妹      |
| 12 | 小第      |
+----+-----------+

Keepalived 配置

Keepalived这里不进行阐述

mysql 高可用

利用mysql的多主，前方使用haproxy+Keepalived 来代理mysql，这里贴出haproxy里面mysql的配置，完成以后测试停掉一条mysql服务器。插入部分数据，然后在吧停掉的mysql启动，查看数据是否同步。停掉的同时验证是否连接可用。

1
2
3
4
5
6
7
8
9
listen mysql_cluster
    bind     0.0.0.0:3306
    mode     tcp
    balance  roundrobin
    option   tcplog
    option   tcpka
    fullconn 10240
    server mysql1 172.21.17.52:3306 check port 3306 maxconn 300
    server mysql2 172.21.16.87:3306 check port 3306 maxconn 300

验证

插入10000条数据，查看数据库同步状态。最后检查数据是否一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/bash
VIP=1.1.1.1
MysqlLogin="mysql -h${VIP} -uzxc -p123456 -P3306"

for ((i=1;i<=10000;i++))
do
{
    ${MysqlLogin} -e "insert into xxlaila.haha(name) values('小${i}');"
    echo "INSERT HELLO $i"
     i=$(($i+1))
    sleep 0.05
}&
done
wait

date
exit 0

注视:
mysql 同步参数binlog-ignore-db这个参数一般我不添加，如果在数据库创建了账号密码，账号密码不同步，在做读写分离，双主切换的时候。高可用的时候，就会显得很麻烦及蛋疼。

坚持原创技术分享，您的支持将鼓励我继续创作！
0%