MySQL 主从复制（内外网）

发表于 | 分类于 | 阅读次数

file

  • master服务器将数据的改变记录二进制日志，当master上的数据发生改变时，则将其改变写入二进制日志中.
  • salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件，同时master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至slave节点本地的中继日志中.
  • slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和master节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

master 库操作

注意： master与slave库的版本需要一致

master 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ vim /etc/my.cnf
[mysqld]
## 设置server_id，一般设置为IP,注意要唯一
server_id=146
### 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
### 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=mysql-bin
### 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
### 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
### 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
### 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

master启动服务

1
$ /etc/init.d/mysqld restart

锁表导数据

1
mysql> flush  tables  with  read  lock;

导出数据拷贝

1
2
$ mysqldump -uuser -ppassword dbname > mysql_bak.sql
$ scp mysql_bak.sql root@server_ip:/data/

记录pos并解锁

1
2
3
4
5
6
7
8
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 29880250 |              | mysql            |
+------------------+----------+--------------+------------------+
mysql> unlock tables;

授权

1
2
mysql> grant replication slave on *.* to 'rep'@'%' identified by '123456';
mysql> flush privileges;

slave库操作

安装

1
$ yum install mysql-server -y

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ vim /etc/my.cnf
## 设置server_id，一般设置为IP,注意要唯一
server_id=202
### 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
### 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-bin
### 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
### 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
### 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
### 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
### relay_log配置中继日志
relay_log=mysql-relay-bin
### log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
### 防止改变数据(除了特殊的线程)
#read_only=1

启动服务

1
$ /etc/init.d/mysqld start

创建密码并导入备份数据

1
2
$ mysqladmin -uroot -p password '123456'
$ mysql -uroot -p123456 < mysql_bak.sql

连主库

1
2
3
mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP',MASTER_USER='rep',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=29880250;
mysql> start slave;

查看salve状态

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
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 118.194.48.246
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 41113925
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 11233926
        Relay_Master_Log_File: mysql-bin.000003
             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: 41113925
              Relay_Log_Space: 12941554
              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:
1 row in set (0.00 sec)

注意：Slave_IO_Running: Yes, Slave_SQL_Running: Yes这两个参数为Yes表示正常。

========

问题1:

问题描述

由于master库在外网，slave库在内网，当master日志发生变化时，master与slave的连接有时会断开，导致外网的master的binlog无法发送给内网的slave.只有在slave发起连接时才能建立连接，保持日志传输。

master库状态

此时正在连接中
file

解决办法

所以我在slave库上写了个脚本，判断master库的pos与slave库的pos是否一致，如果不一致就重启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
#!/bin/bash
#主数据库配置
master_ip='118.xx.xxx.xxx'
master_mysql_port=3306
master_mysql_name='test1'
master_mysql_user='root'
master_mysql_password='123456'
#从数据库配置
slave_ip='192.168.150.202'
slave_mysql_user='root'
slave_mysql_password='123456'
slave_mysql_name='test2'
log='/var/log/slave_restart.log'
dat=`date +%F-%H:%M:%S`
#连接主库获取master pos
mysql -u$master_mysql_user -p$master_mysql_password -h $master_ip -P $master_mysql_port -e "use $master_mysql_name; show master status" > master_pos.txt
if [ $? -eq 0 ]
then
        master_pos=`awk '/^mysql-bin/{print $2}' master_pos.txt`
        mysql -u$slave_mysql_user -p$slave_mysql_password -h $slave_ip -e "use $slave_mysql_name; show slave status\G" > slave_pos.txt
        if [ $? -eq 0 ]
        then
                slave_pos=`awk '/Read_Master_Log_Pos/{print $2}' slave_pos.txt`
                if [ $master_pos != $slave_pos ]
                then
                        mysql -u$slave_mysql_user -p$slave_mysql_password -h $slave_ip -e "stop slave;start slave" && echo "$dat--->从库重启成功" >> $log || echo "$dat--->从库重启失败" >> $log
                fi
        fi
fi

放入计划任务

1
2
3
$ crontab -e
#每五分钟检测从库的pos是否与主库一致，如不一致重启slave
*/5 * * * * /bin/bash /data/scripts/mysql_replication_slave_restart.sh

查看日志

1
2
3
4
5
6
7
8
9
10
11
$ tailf /var/log/slave_restart.log
2017-06-29-17:20:01--->从库重启成功
2017-06-29-17:30:01--->从库重启成功
2017-06-29-17:35:01--->从库重启成功
2017-06-29-17:50:01--->从库重启成功
2017-06-29-17:55:01--->从库重启成功
2017-06-29-18:00:01--->从库重启成功
2017-06-29-18:05:01--->从库重启成功
2017-06-29-18:10:01--->从库重启成功
2017-06-29-18:15:01--->从库重启成功
2017-06-29-18:20:01--->从库重启成功

查看master,slave的pos是否一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 56780950 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 118.xx.xxx.xxx
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 56780950
               Relay_Log_File: mysql-relay-bin.000016
                Relay_Log_Pos: 441
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
坚持原创技术分享，您的支持将鼓励我继续创作！