快速搭建MySQL复制集

快速搭建MySQL复制集

主从复制架构

1 环境说明

  MySQL版本 5.6
  basedir    :/u01/my3306   #MySQL软件目录
  数据目录    :/u01/mysql/[实例名]/data
  日志目录    :/u01/mysql/my3308/log/iblog
  binlog日志 :/u01/mysql/my3308/log/binlog
  其他       :/u01/mysql/my3308/run
              /u01/mysql/my3308/tmp
  master端口(已存在) :3306
  slaved端口(带创建)：3308

2.msater已存在,创建slave(3308)实例

2.1 创建相关目录(MySQL用户)

mkdir -p /u01/mysql/my3308/log/iblog
mkdir -p /u01/mysql/my3308/log/binlog
mkdir -p /u01/mysql/my3308/run
mkdir -p /u01/mysql/my3308/tmp

2.2 创建my.cnf

cp /u01/mysql/my3306/my.cnf  /u01/mysql/my3308/my.cnf 
vim /u01/mysql/my3308/my.cnf 
#替换3306 为3307  并且修改server_id

#修改后的my.cnf:
[mysql@localhost data]$ cat /u01/mysql/my3308/my.cnf
[client]
port=3308
socket=/u01/mysql/my3308/mysql.sock

[mysql]
pid_file=/u01/mysql/my3308/run/mysqld.pid

[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# system
basedir=/u01/my3306
datadir=/u01/mysql/my3308/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/u01/mysql/my3308/run/mysqld.pid
port=3308
server_id=103
skip_name_resolve=ON
socket=/u01/mysql/my3308/run/mysql.sock
tmpdir=/u01/mysql/my3308/tmp

#binlog
log_bin=/u01/mysql/my3308/log/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/u01/mysql/my3308/log/error.log
slow_query_log_file=/u01/mysql/my3308/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/u01/mysql/my3308/log/relaylog
relay_log_index=/u01/mysql/my3308/log/relay.index
relay_log_info_file=/u01/mysql/my3308/log/relay-log.info

#slave
slave_load_tmpdir=/u01/mysql/my3308/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/u01/mysql/my3308/log/iblog
innodb_log_group_home_dir=/u01/mysql/my3308/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld_safe]
datadir=/u01/mysql/my3308/data

2.3 初始化数据库

 /u01/my3306/scripts/mysql_install_db   --datadir=/u01/mysql/my3308/data --user=mysql

2.4 启动数据库实例

 /u01/my3306/bin/mysqld_safe --defaults-file=/u01/mysql/my3308/my.cnf --user=mysql &

2.5 验证连接

 [mysql@localhost data]$ mysql --socket=/u01/mysql/my3308/run/mysql.sock --port 3308
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 1
 Server version: 5.6.36-log Source distribution

 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.

3.搭建主从复制

3.1设置提示符 以区别主从

 #主库上
 mysql> prompt my3306->
 PROMPT set to 'my3306->'
 my3306->
 #从库上
 mysql> prompt my3308->
 PROMPT set to 'my3308->'
 my3308->

3.2创建复制用户(主从上都要执行)

grant replication slave,replication client on *.* to 'rep'@'%' identified by 'rep';

3.3确认主从的server_id

my3306->show variables like 'server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 101       |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

my3308->show variables like 'server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 103       |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

3.4创建需要同步的数据库

create database replTestDB;
use replTestDB;
create table t1(id int,name varchar(10));
insert into t1 values(1,'AAAAA');
commit;

3.5mysqldump全备

[mysql@localhost ~]$ mysqldump  --socket=/u01/mysql/my3306/run/mysql.sock --port=3306 --single-transaction --master-data=2 replTestDB >/tmp/replTestDB20170731.sql   

[mysql@localhost ~]$ cat /tmp/replTestDB20170731.sql
-- MySQL dump 10.13  Distrib 5.6.36, for Linux (x86_64)
--
-- Host: localhostDatabase: replTestDB
-- ------------------------------------------------------
-- Server version   5.6.36-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000012', MASTER_LOG_POS=96488758;

--
-- Table structure for table `t1`
.........................................................

#备份完毕后,再插入一条数据
my3306-> insert into t1 values(2,'BBBBB');
Query OK, 1 row affected (0.02 sec)

3.6 从库上还原数据

my3308->use replTestDB
Database changed
my3308->source /tmp/replTestDB20170731.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


my3308->show tables;
+----------------------+
| Tables_in_replTestDB |
+----------------------+
| t1                   |
+----------------------+
1 row in set (0.00 sec)

3.7 从库上配置主从复制

 change master to
 master_host='127.0.0.1',
 master_port=3306,
 master_user='rep',
 master_password='rep',
 master_log_file='binlog.000012',
 master_log_pos=96488758;

 my3308->start slave;
 Query OK, 0 rows affected (0.02 sec)

4 Semi-sync复制

实现半同步复制的功能很简单，只需在mysql的主服务器和从服务器上安装个google提供的插件即可实现。
安装步骤如下:

cd /u01/mysql-5.6.36/plugin/semisync  //插件目录

master上操作:

my3306->install plugin rpl_semi_sync_master SOname 'semisync_master.so'；  #安装模块
Query OK, 0 rows affected (0.03 sec)

my3306->SET GLOBAL rpl_semi_sync_master_enabled = 1;    # 启动半同步
my3306->set global rpl_semi_sync_master_timeout =2000; # 设置超时时间

my3306->show variables like '%rpl_semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 2000  |   #单位毫秒
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

slave上操作:

my3308->install plugin rpl_semi_sync_slave SOname 'semisync_slave.so'；  #安装模块
Query OK, 0 rows affected (0.03 sec)

my3308->show variables like '%rpl_semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+

my3306->SET GLOBAL rpl_semi_sync_slave_enabled = 1;    # 启动半同步

my3308->STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;  #从库上重启进程使其模块生效：

上面的设置时在mysql进程内动态设定了，会立即生效但是重启服务以后就会失效，为了保证永久有效，
需要把相关配置写到主、从服务器的配置文件my.cnf内：

# On Master  
[mysqld]  
rpl_semi_sync_master_enabled=1  
rpl_semi_sync_master_timeout=1000   #此单位是毫秒  
# On Slave  
[mysqld]  
rpl_semi_sync_slave_enabled=1

确认半同步功能已经启用，通过下面的操作进行查看

master:

my3306->show global status like "rpl%";
+--------------------------------------------+---------+
| Variable_name                              | Value   |
+--------------------------------------------+---------+
| Rpl_semi_sync_master_clients               | 1       |
| Rpl_semi_sync_master_net_avg_wait_time     | 1017    |
| Rpl_semi_sync_master_net_wait_time         | 16276   |
| Rpl_semi_sync_master_net_waits             | 16      |
| Rpl_semi_sync_master_no_times              | 1       |
| Rpl_semi_sync_master_no_tx                 | 36298   |
| Rpl_semi_sync_master_status                | ON      |
| Rpl_semi_sync_master_timefunc_failures     | 0       |
| Rpl_semi_sync_master_tx_avg_wait_time      | 312835  |
| Rpl_semi_sync_master_tx_wait_time          | 5005370 |
| Rpl_semi_sync_master_tx_waits              | 16      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0       |
| Rpl_semi_sync_master_wait_sessions         | 0       |
| Rpl_semi_sync_master_yes_tx                | 15      |
+--------------------------------------------+---------+
14 rows in set (0.00 sec)

Rpl_semi_sync_master_clients = 1 表示半同步已经开启

验证一下
** slave 上停止同步:**

my3308->STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

** master 上把超时设大一些:**

my3306->set global rpl_semi_sync_master_timeout=60000;
Query OK, 0 rows affected (0.00 sec)

** master 插入一条数据:**

my3306->INSERT into  t8 values (4,'eeeee');
Query OK, 1 row affected (58.10 sec)

my3306->INSERT into  t8 values (5,'eee');
Query OK, 1 row affected (0.01 sec)

可以看到: 关闭从服务以后,insert数据会出现等待,一直到超时时间过了才能创建
一旦超时,半同步将自动将为异步，可以看到第二个insert语句不再等待了！！

** slave 上启动同步，数据马上就同步了！😗*

my3308->START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
my3308->select * from t8;
+------+-------+
| id   | name  |
+------+-------+
|    1 | eee   |
|    2 | eee   |
|    3 | eee   |
|    4 | eee   |
|    4 | eeeee |
|    5 | eee   |
+------+-------+
6 rows in set (0.00 sec)

5 出现的问题以及解决方案

问题:

   Last_Errno: 1146
   Last_Error: Error executing row event: 'Table 'zabbix.history' doesn't exist'

原因：之前搭建了zabbix监控,slave没有还原这个库 导致 复制的sql进程报错

解决: 从库端过滤复制的数据库

#关闭从实例  
[mysql@localhost data]$ mysqladmin  shutdown -S /u01/mysql/my3308/run/mysql.sock

#编辑cnf文件 新增 replicate-do-db项目
vi /u01/mysql/my3308/my.cnf
replicate-do-db=replTestDB # 告诉slave只做 db=replTestDB  数据库的更新

#启动从实例
mysqld_safe --defaults-file=/u01/mysql/my3308/my.cnf --user=mysql &

#启动slave 
mysql> prompt my3308->
PROMPT set to 'my3308->'
my3308->start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#验证数据是否同步 
my3308->use replTestDB
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
my3308->select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|1 | AAAAA |
|2 | BBBBB |
+------+-------+
2 rows in set (0.01 sec)