快速搭建 mysql_快速搭建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)