mysql 主从配置

一、 MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的，MySQL主从基于binlog，主上须开启binlog才能进行主从。

 

MySQL主从过程大致有3个步骤

1）主将更改操作记录到binlog里

2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

3）从根据relaylog里面的sql语句按顺序执行

 

MySQL主从有3个线程：

主上有一个log dump线程，用来和从的I/O线程传递binlog

从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

主从复制原理图：

 

mysql主从使用场景：

1）数据备份，主机器宕机，从机器还能随时对web提供服务

2）作为一个从库，读的库，减轻主库的压力，数据备份且可以分担主机器被调用数据时的压力，mysql主从，是有方向性的，写数据，必须从主机器开始；如果不依照原理会导致数据紊乱

 

二、准备工作

做实验需要两台机器，且都需要mysql；

• 主机1 ying01 192.168.112.136；

• 主机2 ying02 192.168.112.138 ;

第一步：先查看ying02是否安装mysql

[root@ying02 ~]# ls /usr/local/mysql

ls: 无法访问/usr/local/mysql: 没有那个文件或目录

[root@ying02 ~]# ps aux |grep mysql

root 1270 0.0 0.0 112676 980 pts/0 S+ 11:50 0:00 grep --color=auto mysql

[root@ying02 ~]# cd /usr/local/src/

[root@ying02 src]# ls

第二步;把ying01上的mysql 5.6的安装包，复制到ying02机器上；

第三步：此时，解压，创建用户，初始化等（见LNMP 笔记）

[root@ying02 src]# ls

mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

[root@ying02 src]# tar zxf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

[root@ying02 src]# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql

[root@ying02 src]# ls /usr/local/mysql/

bin COPYING data docs include lib man mysql-test README scripts share sql-bench support-files

[root@ying02 src]# cd /usr/local/mysql/

[root@ying02 mysql]# useradd mysql

 

[root@ying02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

 

初始化的时候出现错误，需要安装 perl perl-devel库

[root@ying02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

-bash: ./scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录

[root@ying02 mysql]# yum -y install perl perl-devel

 

继续安装 perl-Data-Dumper

[root@ying02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:

Data::Dumper

[root@ying02 mysql]# yum install -y perl-Data-Dumper

 

继续按提示安装：libaio

[root@ying02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

[root@ying02 mysql]# yum install -y libaio

 

此时，初始化成功，用echo $? 检测，为0正确

[root@ying02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

.... 此处省略

[root@ying02 mysql]# echo $?0

第四步：按下面，配置my.cnf文件

[root@ying02 mysql]# vi /etc/my.cnf

[mysqld]

datadir=/data/mysql //指定数据目录

socket=/tmp/mysql.sock //指定sock位置

 

第五步：把mysql脚本，复制到/etc/init.d下，并改名为mysqld；并配置脚本文件；

[root@ying02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@ying02 mysql]# vi /etc/init.d/mysqld //脚本文件，按下面配置

basedir=/usr/local/mysql

datadir=/data/mysql

 

第六步：启动mysql脚本服务；

[root@ying02 mysql]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/data/mysql/ying02.err'.

. SUCCESS!

三、配置主

现在把ying01作为主(master),把ying02作为从(slave)

按下面提示，编辑主配置文件；

[root@ying01 src]# vim /etc/my.cnf

[mysqld]datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=136 //需要添加，id任意数字

log_bin=ying01 //前缀

重启mysql服务；查看数据目录有 ying01.000001和ying.index两个文件；

[root@ying01 src]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

`

[root@ying01 src]# cd /data/mysql/ //切换到mysql数据目录下

[root@ying01 mysql]# ls -l

总用量 176308

-rwxr-xr-x 1 mysql mysql 16384 6月 27 09:58 aria_log.00000001-rwxr-xr-x 1 mysql mysql 52 6月 27 09:58 aria_log_control-rwxr-xr-x 1 mysql mysql 56 6月 24 08:45 auto.cnf-rwxr-xr-x 1 mysql mysql 2747 6月 27 09:58 ib_buffer_pool-rwxr-xr-x 1 mysql mysql 79691776 7月 19 13:26 ibdata1-rw-rw---- 1 mysql mysql 50331648 7月 19 13:26 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 7月 19 13:22 ib_logfile1-rwxr-xr-x 1 mysql mysql 0 6月 24 17:37 multi-master.info

drwxr-xr-x 2 mysql mysql 4096 7月 11 22:48 mysql

drwx------ 2 mysql mysql 4096 7月 11 23:28 mysql2

drwxr-xr-x 2 mysql mysql 4096 6月 24 07:45 performance_schema

drwxr-xr-x 2 mysql mysql 6 6月 24 07:45 test

-rw-rw---- 1 mysql mysql 120 7月 19 13:26 ying01.000001 //新生成的-rw-rw---- 1 mysql mysql 89233 7月 19 13:26 ying01.err -rw-rw---- 1 mysql mysql 16 7月 19 13:26 ying01.index //新生成的索引页-rw-rw---- 1 mysql mysql 5 7月 19 13:26 ying01.pid

drwx------ 2 mysql mysql 324 7月 16 23:59 zrlog

先把zrlog数据库做备份，并改名为 yingying数据库；

 

[root@ying01 mysql]# mysqldump -uroot -pyinglinux zrlog > /tmp/zrlog.sql //先备份

Warning: Using a password on the command line interface can be insecure.

[root@ying01 mysql]# du -sh /tmp/zrlog.sql12K /tmp/zrlog.sql

[root@ying01 mysql]# mysql -uroot -pyinglinux -e "create database yingying" //创建yingying数据库

Warning: Using a password on the command line interface can be insecure.

[root@ying01 mysql]# mysql -uroot -pyinglinux yingying < /tmp/zrlog.sql //再把备份的写入到yingying

Warning: Using a password on the command line interface can be insecure.

[root@ying01 mysql]# ls -lt

总用量 176276

-rw-rw---- 1 mysql mysql 50331648 7月 19 13:30 ib_logfile0

-rwxr-xr-x 1 mysql mysql 79691776 7月 19 13:30 ibdata1

-rw-rw---- 1 mysql mysql 10999 7月 19 13:30 ying01.000001

drwx------ 2 mysql mysql 324 7月 19 13:30 yingying //备份的数据库，yingying

-rw-rw---- 1 mysql mysql 89233 7月 19 13:26 ying01.err

-rw-rw---- 1 mysql mysql 5 7月 19 13:26 ying01.pid

-rw-rw---- 1 mysql mysql 16 7月 19 13:26 ying01.index

-rw-rw---- 1 mysql mysql 50331648 7月 19 13:22 ib_logfile1

drwx------ 2 mysql mysql 324 7月 16 23:59 zrlog

drwx------ 2 mysql mysql 4096 7月 11 23:28 mysql2

drwxr-xr-x 2 mysql mysql 4096 7月 11 22:48 mysql

-rwxr-xr-x 1 mysql mysql 16384 6月 27 09:58 aria_log.00000001

-rwxr-xr-x 1 mysql mysql 52 6月 27 09:58 aria_log_control

-rwxr-xr-x 1 mysql mysql 2747 6月 27 09:58 ib_buffer_pool

-rwxr-xr-x 1 mysql mysql 0 6月 24 17:37 multi-master.info

-rwxr-xr-x 1 mysql mysql 56 6月 24 08:45 auto.cnf

drwxr-xr-x 2 mysql mysql 4096 6月 24 07:45 performance_schema

drwxr-xr-x 2 mysql mysql 6 6月 24 07:45 test

[root@ying01 mysql]#

 

进入mysql；

[root@ying01 mysql]# mysql -uroot -pyinglinux

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.36-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

授予repl用户（所属IP）从复制权限，针对所有的数据库及表，以及密码

mysql> grant replication slave on *.* to 'repl'@'192.168.112.138' identified by 'yinglinux111';

Query OK, 0 rows affected (0.00 sec)

 

grant 权限 on 数据库对象 to 用户

名词	解释
replication slave	从复制权限
*.*	数据库.表
repl	主从复制的用户
IP	slave的IP

 

先锁定数据库以防止写入数据；

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

 

查看master状态

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| ying01.000001 | 11211 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

mysql> quit

Bye

 

把需要主从复制的数据库都备份

[root@ying01 mysql]# ls /tmp/1212.txt php_errors.log-20180704 test.com.log

hsperfdata_root php-fcgi.sock user.sql

mysql_all.sql systemd-private-0b22f6f6d29c4dd98b413eb07065989f-chronyd.service-k7zu0o ying.sock

mysqlbak.sql systemd-private-0b22f6f6d29c4dd98b413eb07065989f-vgauthd.service-2DBZkO zrlog.sql

mysql.sock systemd-private-0b22f6f6d29c4dd98b413eb07065989f-vmtoolsd.service-25Cn8e

[root@ying01 mysql]# mysqldump -uroot -pyinglinux mysql2 > /tmp/my2.sql

Warning: Using a password on the command line interface can be insecure.

 

四、配置从

现在在从端配置: ying02 192.168.112.138

编辑mysql配置文件；

[root@ying02 ~]# vim /etc/my.cnf

 

[mysqld]datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=138 //id只要和主端不同即可

 

重启mysql服务；并利用scp命令，复制ying01上备份文件到ying02上/tmp下；

[root@ying02 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@ying02 ~]# ls /data/mysql/

auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test ying02.err ying02.pid

[root@ying02 ~]# scp 192.168.112.136:/tmp/my2.sql /tmp/

my2.sql 100% 641KB 18.6MB/s 00:00

[root@ying02 ~]# scp 192.168.112.136:/tmp/zrlog.sql /tmp/

zrlog.sql 100% 10KB 2.1MB/s 00:00

[root@ying02 ~]# ls /tmp/1212.txt systemd-private-a229089a97fe4894a0c6a4e777a7688a-chronyd.service-Jlqqo0 zrlog.sql

my2.sql systemd-private-a229089a97fe4894a0c6a4e777a7688a-vgauthd.service-lVk5CW

mysql.sock systemd-private-a229089a97fe4894a0c6a4e777a7688a-vmtoolsd.service-NuLEq4

 

由于ying02没有给mysql命令指定环境变量；因此此时需要设置别名，方便使用；

[root@ying02 ~]# mysql -uroot //没有设置环境变量

-bash: mysql: 未找到命令

[root@ying02 ~]# alias 'mysql=/usr/local/mysql/bin/mysql' //别名

[root@ying02 ~]# alias 'mysqldump=/usr/local/mysql/bin/mysqldump' //别名

[root@ying02 ~]# mysql -uroot //现在可以使用

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

 

创建与ying01上相同的数据库，并把/tmp下相应的备份数据库，拷贝到刚创建的数据库，需要一一对应；

mysql> create database yingying;

Query OK, 1 row affected (0.00 sec)

 

mysql> create database zrlog;

Query OK, 1 row affected (0.00 sec)

 

mysql> create database mysql2;

Query OK, 1 row affected (0.20 sec)

 

mysql> quit

Bye

[root@ying02 ~]# mysql -uroot yingying < /tmp/zrlog.sql

[root@ying02 ~]# mysql -uroot zrlog < /tmp/zrlog.sql

[root@ying02 ~]# mysql -uroot mysql < /tmp/my2.sql

ERROR 1556 (HY000) at line 180: You can't use locks with log tables.

[root@ying02 ~]# mysql -uroot mysql2 < /tmp/my2.sql

[root@ying02 ~]# ls /data/mysql/

auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql2 performance_schema test ying02.err ying02.pid yingying zrlog

[root@ying02 ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.36 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

 

若slave io及sql线程已经启动，需要先 stop slave

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

change master命令设置slave从机与master主机进行通信；

mysql> change master to master_host='192.168.112.136',master_user='repl',master_password='yinglinux111',master_log_file='ying01.000001',master_log_pos=11211;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

语句释义：

• master_host 与 master_port：分别代表master主机名（或IP地址）及mysql实例端口号。端口默认为3306,则可以省略；

• master_user 与 master_password：连接到master主机复制账户所对应的用户名及密码。

• master_log_file 与 master_log_pos：确定slave的io线程下次开始执行时从master开始读取的位置坐标，如果二者都没有指定，slave使用上次slave sql线程保存的位置。

注意：change master to可以不带参数，如：只改变了用于复制的用户密码，那么change master to只需针对master_password选项作出修改即可。

开启从机上slave io及sql线程；

mysql> start slave;

Query OK, 0 rows affected (0.15 sec)

 

mysql> show slave status\G

 

Slave_IO_Running: Connecting //有问题

Slave_SQL_Running: Yes

出现 Slave_IO_Running: Connecting 问题

有问题 ，先停止slave；

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

在排查用户名，密码等配置信息后，发现ying01端的防火墙没有关闭；

[root@ying01 mysql]# systemctl stop firewalld

[root@ying01 mysql]# iptables -nvL

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)

pkts bytes target prot opt in out source destination

 

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)

pkts bytes target prot opt in out source destination

 

Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)

pkts bytes target prot opt in out source destination

回到从端（ying02），重新开启start slave

mysql> change master to master_host='192.168.112.136', master_user='repl', master_password='yinglinux111', master_log_file='ying01.000001', master_log_pos=112211;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

 

mysql> start slave; //开启

Query OK, 0 rows affected (0.01 sec)

 

mysql> show slave status\G

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.112.136 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ying01.000001 Read_Master_Log_Pos: 11211 Relay_Log_File: ying02-relay-bin.000002 Relay_Log_Pos: 280 Relay_Master_Log_File: ying01.000001 Slave_IO_Running: Yes //配置成功 Slave_SQL_Running: Yes //配置成功

配置成功后，然后到ying01上，解锁数据表；

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

五、测试主从同步

数据库及表同步也可以指定，主从上命令不同：

• 主服务器：

  • binglog-do-db=1,2 //仅同步指定的库1和2

  • bing-ignore-db=3 //忽略指定的库3

• 从服务器：

  • replicate-do-db=

  • replicate-ignore-db=

  • replicate-do-table

  • replicate-ignore-table=

  • replicate-wild-do-table= // 常用

  • replicate-wild-ignore-table= // 常用

现在分别在ying01和ying02机器上进行主从测试：

在主（ying01）上，进入yingying数据库，查看其数据表情况；

mysql>

mysql> use yingying

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_yingying |

+--------------------+

| comment || link || log || lognav || plugin || tag || type || user || website |

+--------------------+

9 rows in set (0.00 sec)

 

mysql> select count(*) from website; //查看websites表的行数

+----------+

| count(*) |

+----------+

| 10 |

+----------+

1 row in set (0.00 sec)

 

mysql> truncate table website; //清空ebsites表内容

Query OK, 0 rows affected (0.11 sec)

 

mysql> select count(*) from website; //此时此表内容为0

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

 

mysql> drop table website; //删除 website表

Query OK, 0 rows affected (0.05 sec)

 

mysql>

从端（ying02）测试

mysql> use yingying //进入yingying数据库

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> select count(*) from website; //查看 website的行数，与主端一致

+----------+

| count(*) |

+----------+

| 10 |

+----------+

1 row in set (0.00 sec)

 

mysql> select count(*) from website; //在主端清空 website表的内容，此时与主端一致

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

 

mysql> select * from website; //在主端删除 website表，此时也与主端一致，此表已经不存在

ERROR 1146 (42S02): Table 'yingying.website' doesn't exist

mysql> select count(*) from website;

ERROR 1146 (42S02): Table 'yingying.website' doesn't exist

mysql>