mysql主从复制实现

羽翰尘

修改于 2019-11-26 16:27:13

6560

修改于 2019-11-26 16:27:13

文章被收录于专栏：技术向技术向

本文由腾讯云+社区自动同步，原文地址 https://stackoverflow.club/article/mysql_copy_master_slave/

修改mysql配置文件

对于ubuntu16.04和mysql 5.6，配置文件的路径为：

/etc/mysql/mysql.conf.d/mysqld.cnf

master修改下列项

[mysqld]
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

slave修改下列项

[mysqld]
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin   
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin

重启两个mysql

将slave加入master

可以在master mysql 中运行show master status;查看master状态。

典型输出

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |  2849350 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

其中的File和Position为需要在slave加入master时指定的参数。

在slave中执行下述命令，将slave加入master

change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 2830, master_connect_retry=30;

参数说明：

master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒

如果是在容器中测试，可以用docker inspect --format='.NetworkSettings.IPAddress}}' 容器名称|容器id查看容器ip。

slave查看状态

slave节点运行下面命令，查看同步结果

show slave status\G

重点关注下面两项，两者都为Yes才是同步成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试

可以在mysql master中增加一个数据库，然后在mysql slave中查看结果。

创建数据库时指定编码为utf8

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

排错

在实际使用时，发现某些机器上用docker运行mysql master节点会发生崩溃，如下所示：

15:59:37 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68109 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
mysqld(my_print_stacktrace+0x2c)[0x55b3c2ffe88c]
mysqld(handle_fatal_signal+0x4b1)[0x55b3c2d9bea1]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110e0)[0x7f0aa093f0e0]
mysqld(_ZN9MYSQL_LOG17generate_new_nameEPcPKc+0x79)[0x55b3c2d89879]
mysqld(_ZN9MYSQL_LOG26init_and_set_log_file_nameEPKcS1_13enum_log_type10cache_type+0x57)[0x55b3c2d89937]
mysqld(_ZN13MYSQL_BIN_LOG11open_binlogEPKcS1_10cache_typembbbbP28Format_description_log_event+0x61)[0x55b3c2fa51a1]
mysqld(+0x3a63ba)[0x55b3c2ce23ba]
mysqld(_Z11mysqld_mainiPPc+0x41b)[0x55b3c2ce2c3b]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf1)[0x7f0a9f4c92e1]
mysqld(_start+0x2a)[0x55b3c2cd6afa]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

将mysql master binary log的目录挂到外部就可以了，

在mysqld.cnf中设置log-bin=/binlog/mysql-bin，在docker启动时加入一个外部卷，估计是docker内部存储不足？有待研究。