半联机复制,玛丽亚DB主从复制搭建

By admin in 美高梅手机版4858 on 2019年5月4日

我的github

————–Mysql兑现数据库主从复制架构—————-

脚下蒙受设计

一主一从

安装MySQL服务器

  • 设置数据库

yum install -y mariadb-server
  • 开始化数据库

mysql_secure_installation   #MySql初始化脚本,以下为每一项的翻译
    是否设置root密码
    输入密码
    确认密码
    是否设置匿名用户
    是否允许root远程登录
    删除test数据库
    现在是否生效
    添加PATH变量
  • 开端数据库

systemctl start mariadb

壹、景况妄想:

长机名称 

壹.1 景况希图:

  centos系统服务器二台、 一台用户做Mysql主服务器,
1台用来做Mysql从服务器, 配置好yum源、 防火墙关闭、
各节点石英钟服务一起、 各节点之间能够透过主机名相互通讯

主服务器基础搭建

  • 设定主机名,在此时此刻bash生效

hostnamectl set-hostname mysql-master
exec bash
  • 始建数据库

MariaDB [(none)]> create database shuaiguoxia ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuaiguoxia        |
| test               |
+--------------------+
5 rows in set (0.00 sec)
  • 创建表

use shuaiguoxia;
create table blog (name varchar(20),age int,sex varchar(10));
  • 查看表结构

MariaDB [shuaiguoxia]> desc blog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
  • 安顿测试数据

insert into blog (name,age,sex) values ("guo",27,"nan");
insert into blog (name,age,sex) values ("wu",33,"nan");
insert into blog (name,age,sex) values ("cai",31,"nv");
insert into blog (name,age,sex) values ("li",19,"nan");
insert into blog (name,age,sex) values ("zhao",18,"nan");
insert into blog (name,age,sex) values ("qian",25,"nv");

centos系统服务器2台、一台用户做Mysql主服务器,一台用来做Mysql从服务器,配置好yum源、防火墙关闭、各节点时钟服务协同、各节点之间可以经过主机名相互通讯

ec2t-pgtest-01

1.2 筹划步骤:

1)iptables -F && setenforce 清空防火墙计策,关闭selinux
2)拿两台服务器都使用yum方式安装Mysql服务, 须求版本一样
三)分别运维两台服务器mysql服务, 确认保障服务平常
  架构图:
美高梅手机版4858 1

设定主服务器

  • 修改配置文件

vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=10
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysq.%
  • 给blog表上锁

mysql -u root -p

use shuaiguoxia;

MariaDB [shuaiguoxia]> lock tables blog read;
Query OK, 0 rows affected (0.00 sec)
  • 导出主库中曾经有的数据

mysqldump -u root -p -h 127.0.0.1 --database shuaiguoxia  > /bak.sql
Enter password: 
# 导出时一定要--dabatase指定数据库
  • 将数据复制到从节点

scp /bak.sql 192.168.1.175:/
  • 从节点导入从主节点复制的数目

mysql -u root -p < /bak.sql
  • 给从节点创造授权用户

grant replication slave on *.* to 'slave_user'@'192.168.1.%' identified  by '1234'

192.168.41.145   master

ec2t-pgtest-02

一.3 落成步骤:

从节点配置

  • 设定主机名,在时下bash生效

hostnamectl set-hostname slave-master
exec bash
  • 设定配置文件

[root@mysql-slave ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=21
log-bin=mysql-bin
replicate-wild-ignore-table=mysql.%
  • 查阅主节点的master状态

# 查看主节点的master状态,要在主节点执行
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     2900 |              |                  |
+------------------+----------+--------------+------------------+
  • 从节点导入从主节点复制的数量

# 前面导入过就不同导入了,怕你忘了再提一句
mysql -u root -p < /bak.sql
  • 设定自动复制

# 命令中的master_log_file和master_log_pos为主节点查询的结果

MariaDB [(none)]> change master to \
master_host='192.168.1.46',
master_user='slave_user',
master_password='1234',
master_log_file='mysql-bin.000003',
master_log_pos=2900;
  • 起步复制

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
  • 翻看复制状态

# 正常状态下Slave_IO_Running和 Slave_SQL_Running都为yes

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.1.46
                 Master_User: slave_user
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000003
         Read_Master_Log_Pos: 3057
              Relay_Log_File: mariadb-relay-bin.000002
               Relay_Log_Pos: 686
       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: mysql.%
                  Last_Errno: 0
                  Last_Error: 
                Skip_Counter: 0
         Exec_Master_Log_Pos: 3057
             Relay_Log_Space: 982
             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: 
 Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 10
1 row in set (0.00 sec)

192.168.41.137  slave

IP地址

一.叁.一 配置master主服务器

  对master进行陈设,包罗张开2进制日志,钦赐唯壹的servr
ID。比方,在布局文件参加如下值

    server-id=1      //配置server-id,让主服务器有唯一ID号
    log-bin=mysql-bin  //打开Mysql日志,日志格式为二进制
    skip-name-resolve  //关闭名称解析,(非必须)

美高梅手机版4858 2

基本失败常见原因

  • 网络不通:排查互联网、端口
  • 用户密码不对:检查在主节点创设的用户名密码
  • 用户权限不对:MySQL中对1个用户的标记为IP@username,
  • pos不对:检查从节点设定的启幕pos是还是不是为主节点正在开展的pos
  • 开端二进制文件不对:检查从节点早先的2进制文件是不是为主节点整个在开始展览的2进制位内按
  • 防火墙限制:关闭防火墙

二、计划步骤:

10.189.102.118

一.三.二 创制复制帐号

  在 Master 的数据库中国建工业总群集团立八个备份帐户:每一种 slave 使用专门的学问的
MySQL 用户名和密码连接 master 。进行复制操作的用户会授予 REPLICATION
SLAVE 权限。

    MariaDB [(none)]> grant replication slave,replication client on *.* to slave@'192.168.%.%' identified by 'keer';

从节点导入数据不见效

  • 主节点导出多少时要增多–databases参数

mysqldump -u root -p -h 127.0.0.1 --database shuaiguoxia  > /bak.sql
Enter password: 
# 导出时一定要--dabatase指定数据库
  • 从节点导入数据库是要选取mysql命令

mysql -u root -p < /bak.sql

1、iptables -F && setenforce 清空防火墙战术,关闭selinux

10.189.100.195

半联机复制,玛丽亚DB主从复制搭建。一.叁.三 查看主服务器状态

  在 Master 的数据库试行
show master status,查看主服务器二进制日志状态及地点号。
美高梅手机版4858 3

2、①vim /etc/hosts

角色

1.3.四 配置slave从服务器

  对 slave 进行布署,展开中继日志,钦命唯壹的 servr
ID,设置只读权限。在安顿文件参预如下值:

    server-id=2                 //配置server-id,让从服务器有唯一ID号
    relay_log = mysql-relay-bin //打开Mysql日志,日志格式为二进制
    read_only = 1               //设置只读权限
    log_bin = mysql-bin         //开启从服务器二进制日志
    log_slave_updates = 1       //使得更新的数据写进二进制日志中

美高梅手机版4858 4

192.168.41.145   master     #各节点之间能够由此主机名相互通信

master      

1.三.伍 运营从服务器复制线程

  让 slave 连接 master ,并开端重做 master 二进制日志中的事件。

MariaDB [(none)]> change master to master_host='192.168.37.111', 
   >master_user='slave', 
   >master_password='keer', 
   >master_log_file='mysql-bin.000001', 
   >master_log_pos=413;

  执行start slave;,来运行复制线程。
  

192.168.41.137  slave

slave    

一.3.陆 查看从服务器状态

  可使用SHOW SLAVE STATUS\G;翻开从服务器状态,如下所示,也可用show processlist \G;查阅当前复制状态:

    Slave_IO_Running: Yes       //IO线程正常运行
    Slave_SQL_Running: Yes      //SQL线程正常运行

美高梅手机版4858 5

② ntpdate 172.17.0.1  #各节点挂钟服务同步

系统版本  

1.3.7 测试

一)测试中央同步
  大家在 master
服务器上制造3个数据库,再利用该数据库创制二个表,增加一条记下,来看壹看
slave 服务器有未有一齐成功。
  首先,大家先来查看一下多个服务器上有何数据库:
  master:
美高梅手机版4858 6
  slave:
美高梅手机版4858 7
  将来,大家在主服务器上创造3个数据库“keer”:
美高梅手机版4858 8
  然后,大家来看望从服务器是或不是曾经更新:
美高梅手机版4858 9
  大家能够观察,数据库已经一齐,接下去,大家就来成立表,并在表中插入一条记下:
美高梅手机版4858 10
  然后,大家来看望从服务器是还是不是更新:
美高梅手机版4858 11

2)测试只读属性  
  大家在从服务器上安装了只读,所以大家来试试看能或不可能插入数据:
美高梅手机版4858 12
  那就很为难了,为何大家设置只读依然得以插入数据吧?那要怎么消除呢?
  因为大家登陆的时候使用的是root一流用户,那些大变态是不受只读设置影响的,所以,大家仍旧得以往里面去插入数据,当然,假如大家换来其它的普通用户就不会产出这么的难题了。
  解决办法也相当粗略,大家只须要把表锁起来就能够了:

MariaDB [keer]> flush tables with read lock;

  实行锁表操作之后,大家再来插入一条数据:
美高梅手机版4858 13
  那样的话,固然大家是一流用户登6进来的,也不可能再插入数据了。那样会安全诸多。不过,有利就有弊,那样的话,大家的中坚就无法实时同步了,因为大家的骨干同步是通过把主上的
sql 语句放在从上再实施壹回来落到实处的,然则大家锁表的话,sql
语句就没办法推行了。想要同步方法也异常粗略,直接把表解锁就能够了。
  大家在主上插入一条数据:
美高梅手机版4858 14
  此时,大家的从上将在化痰未来才具促成同台:
美高梅手机版4858 15

三、拿两台服务器都使用yum方式安装Mysql服务,供给版本相同

CentOS release 6.8

一.4 扩充——落成一主多从

四、分别运行两台服务器mysql服务,确定保障服务不奇怪.

数据版本  

1.4.1 要求分析

  当我们的服务器运营一段时间后,流量变得进一步多,那时,壹主壹从能够完结的高可用性和负载均衡无法满意大家的急需,大家将要挑选再增添壹台从服务器。
  然而未来大家的 master 已经运维很久了,大家也须求对新装置的 slave
实行多少同步,乃至它从未 master 的多寡。
  此时,有两种形式能够使 slave 从另三个服务起头,比方,从 master
拷贝数据,从另贰个 slave 克隆,从日前的备份开端一个 slave 。为了加快slave 与 master 同步,可用以下措施先实行多少同步:

(一)master的有个别时刻的数额快速照相;
(2)数据库的备份数据;
(三)master的贰进制日志文件。

  架构图:
美高梅手机版4858 16

  接下去,大家就来落成一下这一个进度:

叁、完成步骤:

MySQL 5.6.23

一.四.贰 具体贯彻进程

一)对 master 举行完全备份

[root@master ~]# mkdir /backup
[root@master ~]# mysqldump --all-database > /backup/mysql-backup-`date +%F-%T`-all.sql

因循守旧查看一下主的二进制日志状态及地点号。
美高梅手机版4858 17

2)把一心备份的数目发到新扩张的从上

[root@master ~]# scp /backup/mysql-backup-2017-11-21-10\:28\:29-all.sql @192.168.37.133:~
root@192.168.37.133's password: 
mysql-backup-2017-11-21-10:28:29-all.sql                                                           100%  504KB 503.9KB/s   00:00 

3)把那几个完全备份导入从的数据库:
美高梅手机版4858 18

4)查看从是不是恢复生机成功:
美高梅手机版4858 19
美高梅手机版4858 20
  大家来查看一下从的二进制日志状态及地方号:
美高梅手机版4858 21
  大家的多寡已经回复成功

伍)设置基本
接下去就是依据上述手续,对该从服务器举行设置就好:

[root@slave2 ~]# vim /etc/my.cnf
    [mysqld]
    server-id = 3
    relay-log = mysql-relay-bin
    read-only = 1
    log-bin = mysql-bin
    log-slave-updates = 1

  
  然后,我们就来安装从服务器:

[root@slave2 ~]# mysql -uroot -p
Enter password: 
MariaDB [(none)]> change master to master_host='192.168.37.111', 
   >master_user='slave', 
   >master_password='keer', 
   >master_log_file='mysql-bin.000001', 
   >master_log_pos=330;
MariaDB [(none)]> start slave;

  然后我们来查阅一下从服务器的场合:
美高梅手机版4858 22

6)测试
  设置完成,大家来测试一下,当主上有操作时,从上是或不是更新:
美高梅手机版4858 23
美高梅手机版4858 24
  至此,大家就完事了1主两从。尽管有要求,大家还足以延续累加从服务器。

1、配置master主服务器

壹. MySQL数据库安装

一.5 扩张——达成半一齐复制

对master进行布置,包含打开2进制日志,内定唯一的servr
ID。举例,在配备文件参预如下值。

1. 开立MySQL用户和组

一.5.壹 二种复制格局

一)同步复制
  指当主库实行完二个作业,装有的从库都实行了该业务才重回给客户端。因为要求拭目以俟全体从库施行完该业务才具回来,所以全同步复制的属性必然会接收严重的熏陶。需求有逾期时间。
2)异步复制
  MySQL私下认可的复制正是异步的,主库在试行完客户端提交的专门的学业后会立刻将结果返给给客户端,并不关怀从库是或不是早已抽取并拍卖,这么就能有3个题目,主尽管crash掉了,此时主上已经交给的政工恐怕并未传来从上,假诺这时候,强行将从升高为主,大概导致新主上的多少不完全。
三)半同步复制
  介于异步复制和全同台复制之间,主库在执行完客户端提交的事体后不是当下回到给客户端,而是等待至少一个从库接收到并写到relay
log中才回去给客户端。
冲突于异步复制,半协同复制巩固了数码的安全性,同时它也招致了自然程度的延迟,本条延迟最少是一个TCP/IP往返的时光。所以,半齐声复制最佳在低延时的互联网中应用。

vim /etc/my.cnf

# groupadd -g 101 dba
# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin

一.伍.贰 完成半联袂复制

  半同步复制是依照特定的模块来贯彻。不过那多少个 nice 的少数正是,在我们的
mariadb伍.5 以上的本子暗许是带有那个模块的。
  借使我们的 centos七 上设置了 mariadb ,我们就能够进来目录去查看一下:

[root@master ~]# cd /usr/lib64/mysql/plugin/

美高梅手机版4858 25
  找到大家的模块后,我们就能够透过命令来安装,然后开始展览简要布署就可以使用了。下边便是具体方法:
master 服务器上:

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;

美高梅手机版4858 26

slave 服务器上:

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = ON ;
MariaDB [(none)]> stop slave;   
MariaDB [(none)]> start slave;

美高梅手机版4858 27
美高梅手机版4858 28
  未来,我们的半联合具名复制就敞开了,大家得以来查看一下日记:

[root@slave ~]# tail -200 /var/log/mariadb/mariadb.log 

  从日记里也足以看来我们的半共同复制已经展开。
美高梅手机版4858 29


  以上,实验一完毕。

server-id=1 #布局server-id,让主服务器有唯1ID号

二. 配置MySQL用户景况变量

互为着力(双主)

  还有一种数据库的架构为双主方式,也正是互为宗旨,那种设定在集团中虽不常用,然而大家照旧需求调整的。
  Master-Master复制的两台服务器,既是master,又是另1台服务器的slave。那样,任何壹方所做的退换,都会透过复制利用到别的壹方的数据库中。
  架构图如下:
美高梅手机版4858 30

log-bin=mysql-bin #开垦Mysql日志,日志格式为二进制

$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

PATH=/usr/local/mysql/bin:$PATH:$HOME/bin

export PATH

贰.壹 碰着策画:

  centos系统服务器二台、 两台用户完成Mysql互为核心, 配置好yum源、
防火墙关闭、 各节点石英钟服务联合、 各节点之间可以因此主机名相互通信

skip-name-resolve#闭馆名称解析,(非必须)

3. 下载MySQL二进制包并安装

二.贰 图谋步骤:

一)iptables -F && setenforce 清空防火墙计策,关闭selinux
二)拿两台服务器都使用yum形式安装Mysql服务, 要求版本一样
三)分别运维两台服务器mysql服务, 确定保证服务符合规律

二.成立复制帐号

$ mkdir /usr/local/mysql/{data,arch}
$ wget https://downloads.mysql.com/archives/get/file/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
$ tar -zxf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
$ mv mysql-5.6.23-linux-glibc2.5-x86_64/* /usr/local/mysql/

二.三 达成步骤:

在Master的数据库中创立2个备份帐户:每一个slave使用正式的MySQL用户名和密码连master
。举行复制操作的用户会授予REPLICATION SLAVE权限。

肆. 陈设MySQL数据库(主备操作)

二.三.1 配置第一台 master 主服务器

    server-id = 1      //配置server-id,让主服务器有唯一ID号
    log-bin = mysql-bin  //打开Mysql日志,日志格式为二进制
    skip-name-resolve    //关闭名称解析,(非必须)
    relay_log = mysql-relay-bin //打开Mysql日志,日志格式为二进制
    read_only = 1               //设置只读权限
    log_slave_updates = 1       //使得更新的数据写进二进制日志中
    auto_increment_offset = 1    //表示自增长字段从那个数开始
    auto_increment_increment = 2  //表示自增长字段每次递增的量

美高梅手机版4858 31

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
slave@’192.168.%.%’ IDENTIFIED BY
‘ke’;

  4.一 创立MySQL配置文件/etc/my.cnf

二.③.二 配置第叁台 master 主服务器

    server-id = 2      //配置server-id,让主服务器有唯一ID号
    log-bin = mysql-bin  //打开Mysql日志,日志格式为二进制
    skip-name-resolve    //关闭名称解析,(非必须)
    relay_log = mysql-relay-bin //打开Mysql日志,日志格式为二进制
    read_only = 1               //设置只读权限
    log_slave_updates = 1       //使得更新的数据写进二进制日志中
    auto_increment_offset = 2    //表示自增长字段从那个数开始
    auto_increment_increment = 2  //表示自增长字段每次递增的量

美高梅手机版4858 32

三.查看主服务器状态

# cat /etc/my.cnf 
[client]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock

[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock

skip-external-locking
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
query_cache_size= 32M
max_allowed_packet = 16M
myisam_sort_buffer_size=128M
tmp_table_size=32M

table_open_cache = 512
thread_cache_size = 8
wait_timeout = 86400
interactive_timeout = 86400
max_connections = 600

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 32

#isolation level and default engine 
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTED

server-id  = 1
basedir     = /usr/local/mysql
datadir     = /usr/local/mysql/data
pid-file     = /usr/local/mysql/data/hostname.pid

#open performance schema
log-warnings
sysdate-is-now

binlog_format = MIXED
log_bin_trust_function_creators=1
log-error  = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =1
#general_log_file  = /usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err

#for replication slave
#log-slave-updates 
#sync_binlog = 1

#for innodb options 
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/arch
innodb_log_files_in_group = 2
innodb_log_file_size = 200M

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 16M

innodb_lock_wait_timeout = 100
#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_locks_unsafe_for_binlog=1

#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on

#case-sensitive file names and separate tablespace
innodb_file_per_table = 1
lower_case_table_names=1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

二.三.三 创制复制账号

  因为我们得以达成的是双主方式,所以大家每一台机器上都要创制复制账号:
美高梅手机版4858 33
美高梅手机版4858 34

在Master的数据库实践show master status,查看主服务器二进制日志状态

  4.2 初始化MySQL数据库

二.三.肆 查看服务器状态

  我们分别查看两台服务器的情事,从而为大家下一步的设置奠下基础:
美高梅手机版4858 35
美高梅手机版4858 36

美高梅手机版4858 37美高梅手机版4858 38

$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

Installing MySQL system tables...2017-07-12 02:46:46 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-07-12 02:46:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

Filling help tables...2017-07-12 02:47:40 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-07-12 02:47:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h ec2t-userdata-01 password 'new-password'

Alternatively you can run:

  /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/local/mysql/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql/my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

二.3.5 运行复制线程

  大家来分别对两台机械进行的装置:
  首先,先对第一台 master 机器进行安装:

MariaDB [(none)]> change master to master_host='192.168.37.222',master_user='slave',master_password='keer',master_log_file='mysql-bin.000003',master_log_pos=245;
MariaDB [(none)]> start slave;

  接着,大家来查阅一下她的景况:
美高梅手机版4858 39

  未来,大家来对第叁台 master 实行设置:

MariaDB [(none)]> change master to master_host='192.168.37.111',master_user='slave',master_password='keer',master_log_file='mysql-bin.000002',master_log_pos=245;
MariaDB [(none)]> start slave;

  我们来查看一下她的状态:
美高梅手机版4858 40   
  能够看出,大家的七个机械都设置结束,接下去,大家就能够来测试一下了。

4、配置slave从服务器

  肆.三 运营MySQL数据库,并为root用户设置密码,删除多余用户

2.3.5 测试

  在我们的布局文件中,大家设置了自增加字段的开端值和拉长值,将来我们去创建三个自增加的表:

MariaDB [keer]> create table test(id int PRIMARY KEY AUTO_INCREMENT,name char(20) not null);

  因为大家设置了宗旨,所以在任一机器使用该命令就能够。
  成立好了表以往,大家就能够插入数据开始展览测试了,大家3个安排多条数据来探视效果:
首先个服务器:

MariaDB [keer]> insert into test(name) value('zhao'),('qian'),('sun'),('li');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

其次个服务器:

MariaDB [keer]> insert into test(name) value('zhou'),('wu'),('zheng'),('wang');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

  数据大家都插入了,大家来探望效果如何:
美高梅手机版4858 41
  能够看来,四个表插入的多少是不会再也的。
  不过以往也不是咱们想像的连天的 id
,那是因为,大家两台机器都是同时插入四条数据,而且不是同台插入的,那样就能够有3个先后顺序,第三台机器上的
sql 语句实践完结之后才施行第1台机器上的。所以会现出如此的情景。
  不过,那样已经不会插入重复的数据了。大家的目标也是高达了。


  以上,实验十分之二功。

对slave举行布局,展开中继日志,钦命唯壹的servr
ID,设置只读权限。在布署文件参加如下值

$ /usr/local/mysql/bin/mysqld_safe &
[1] 2531
$ 170714 03:34:41 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
170714 03:34:41 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

$ mysqladmin -u root password "mypna123"
$ mysql -u root -pmypna123
mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+------------------+
| user | password                                  | host             |
+------+-------------------------------------------+------------------+
| root | *FDC33561AE905A01A945F356C99B76E1F0707B3B | localhost        |
| root |                                           | ec2t-pgtest-01   |
| root |                                           | 127.0.0.1        |
| root |                                           | ::1              |
|      |                                           | localhost        |
|      |                                           | ec2t-pgtest-01   |
+------+-------------------------------------------+------------------+
6 rows in set (0.00 sec)

mysql> delete from mysql.user where user='' or password='';
Query OK, 2 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root | *FDC33561AE905A01A945F356C99B76E1F0707B3B | localhost |
+------+-------------------------------------------+-----------+
1 row in set (0.00 sec)

高可用架构MHA的兑现

  具体的落实进程见小编的下一篇博客mysql达成高可用架构之MHA

  如有不足之处还请大家多多指教喏٩(๑>◡<๑)۶

server-id=2 #配置server-id,让从服务器有唯一ID号

  4.4 安装MySQL时区

relay_log = mysql-relay-bin #展开Mysql日志,日志格式为二进制

$ mysql -u root -pmypna123 -e "SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');"
Warning: Using a password on the command line interface can be insecure.
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+


$ /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -pmypna123 mysql
Warning: Using a password on the command line interface can be insecure.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

$ mysql -u root -pmypna123 -e "SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');"
Warning: Using a password on the command line interface can be insecure.
+-----------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+-----------------------------------------------+
| 2004-01-01 13:00:00                           |
+-----------------------------------------------+

$ mysql -u root -pmypna123 -e "SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');"
Warning: Using a password on the command line interface can be insecure.
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2004-01-01 22:00:00                                 |
+-----------------------------------------------------+

read_only = 1 #设置只读权限

  4.五 设置MySQL服务相关

log_bin = mysql-bin #拉开从服务器二进制日志

# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
# chmod +x /etc/rc.d/init.d/mysql
# chown mysqladmin:dba /etc/rc.d/init.d/mysql
# echo 'su - mysqladmin -c "/etc/init.d/mysql start --federated"' >> /etc/rc.local
# chkconfig --add mysql
# chkconfig mysql --level 2345 on

log_slave_updates = 1 #使得立异的数码写进贰进制日志中

 二. MySQL主从复制安插

伍.起始从服务器复制线程

一. 联合签字主备库时间,可在主库搭建NTP SE奥德赛VE福睿斯,备库作为NTP client

①stop  slave;

2. 加多hosts文件分析确认保障主备能够并行解析host主机名

②reset slave;

3. 主库做以下操作

3让slave连接master,并开始重做master二进制日志中的事件。

  3.1 启用二进制日志

CHANGE MASTER TO

$ grep "log-bin" /etc/my.cnf 
log-bin=/usr/local/mysql/arch/mysql-bin

MASTER_HOST=’192.168.41.145′,

  三.二 选拔一个唯一的server-id

MASTER_USER=’slave’,

$ grep "server-id" /etc/my.cnf 
server-id  = 1

MASTER_PASSWOCR-VD=’ke’,
【主从密码要依然故我】

   三.叁 成立具备复制权限的用户

MASTER_LOG_FILE=’mysql-bin.000001′,

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MASTER_LOG_POS=278;

四. 备库做以下操作

※注意

  四.一 启用中继日志

IP为master的IP;

$ grep "relay-log" /etc/my.cnf
relay-log = /usr/local/mysql/arch/relay-bin
relay-log-index  = /usr/local/mysql/arch/relay-log-index

密码和master设置同样;

  4.2 分选二个唯壹的server-id**

POS设置查看master拿到:show master status

$ grep "server-id" /etc/my.cnf
server-id  = 2

④start slave;# 运营复制线程。

 4.3 创立具有复制权限的用户(可选,switchover为主库的时候需求)**

六、查看从服务器状态

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

可使用SHOW SLAVE STATUS\G查看从服务器状态,如下所示,也可用show
processlist \G查看当前复制状态:

** 4.四 从服务器开启贰进制日志(可选,做级联复制的时候供给)

Slave_IO_Running: Yes #IO线程平常运营


Slave_SQL_Running: Yes #SQL线程通常运行

$ grep "log-bin" /etc/my.cnf
log-bin=/usr/local/mysql/arch/mysql-bin

【两者必须同时YES,才算运营成功】

**  四.5从服务器更新操作记入二进制日志(可选,做级联复制的时候必要)**

附:主从同步出现转手谬误:

$ grep "log-slave-updates" /etc/my.cnf
log-slave-updates = true

Slave_IO_Running: Connecting

  肆.陆 锁定从服务器为只读(可选,安全起见,备库设为只读)

Slave_SQL_Running: Yes

$ grep "read-only" /etc/my.cnf
read-only = 1

消除方法:

5. 查看主库当前binary log和postion

导致lave_IO_Running 为connecting 的案由主要有以下 叁 个地点:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 397
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

  壹、互连网不通

陆. 在从库连接主库

2、密码不对

mysql> CHANGE MASTER TO MASTER_HOST='ec2t-pgtest-01',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=397;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ec2t-pgtest-01
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             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: 120
              Relay_Log_Space: 613
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 342fbbf4-6896-11e7-822f-0ad588ebcbcc
             Master_Info_File: /data/01/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

3、pos不对

 

四、验证

 
注意:若是那时主库已经运营一段时间,并且有大气数目存在,需求接纳mysqldump命令将主库导出(要钦命记录导出时的二进制文件和position)。然后把导出来的多少再度导入进从服务器,此时,再去连接主服务器就须求内定从主服务器的哪些二进制文件和position发轫复制数据了。而怎么知道从哪些2进制文件和position开首复制数据吧?正是从mysqldump导出数据文件中记录了导出数据时的2进制文件和position,能够试用一下命令将主库导出

在master上插入数据,查看slave上是或不是能够一并

$ mysqldump -u root -pmypna123 --flush-privileges --single-transaction --master-data=2 --all-databases > all_database.sql

create database liuke;#建数据库

七. 在主库查看从库消息

use liuke;

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | b88d1498-68f5-11e7-849e-0a0ce639fa30 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

create table mini_tb(id int(3),name char(10));  #建表

 贰. MySQL主从半同步复制部署

insert into mini_tb values(001,’candy’); #插入数据

 半同步机制

select*from mini_tb;     #查看表内容

a. 当Master上开启半联合复制的成效时,至少应该有三个Slave开启其职能。此时,三个线程在Master上交给业务将深受阻塞,直到得知2个已开启半同步复制效能的Slave已吸收此职业的装有事件,或等候超时。

美高梅手机版4858 42美高梅手机版4858 43

b. 当Slave主机连接到Master时,能够查阅其是不是处于半同台复制的机制。

在slave上查看,能够共同则成功。

c. 当多少个事务的事件都已写入其relay-log中且已刷新到磁盘上,Slave才会告知已接受。

五、添加新slave服务器

d.  尽管等待超时,约等于Master没被报告已接到,此时Master会自动转变为异步复制的体制。当至少1个半同步的Slave超过了,Master与其Slave自动调换为半1块复制的建制。

若是master已经运营很久了,想对新安装的slave实行多少同步,以致它并未有master的数额。

e.  半同步复制的成效要在Master,Slave都打开,半一起复制才会起功用;不然,只开启1边,它还是为异步复制。

此刻,有三种办法能够使slave从另3个服务开始,比如,从master拷贝数据,从另贰个slave克隆,从近来的备份初始二个slave。为了加速Slave与master同步,可

一. 在主库安装半手拉手插件

用以下办法先实行数量同步:

  1.一 查看主库未开启半同步时的动静

(壹)master的有个别时刻的数据快照;

mysql> show status like '%semi%';
Empty set (0.00 sec)

mysql> show variables like '%semi%';
Empty set (0.00 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+

(2)数据库的备份数据

  一.2 查看主库的半合伙插件

(三)master的2进制日志文件。

$ ls -lh /usr/local/mysql/lib/plugin/semisync_master.so 
-rwxr-x--- 1 mysqladmin dba 408K Jan 19  2015 /usr/local/mysql/lib/plugin/semisync_master.so

————–**mysql完结数据库主主复制—————-**

  壹.3 安装主库的半手拉手插件

一、原理

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)

mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_master_timeout = 5000;
Query OK, 0 rows affected (0.00 sec)

主主复制即在两台MySQL主机内都得以改造数据,而且别的1台主机也会做出相应的改观。

 
安装后开发银行和定制中央连接错误的超时时间默许是10s可改为伍s,一旦有叁遍超时自动降级为异步。(以上内容要想永世有效须求写到配置文件中)

怎么兑现:正是将多少个主从复制有机统一同来。

$ grep rpl_semi_sync /etc/my.cnf 
rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_master_timeout = 2000;

配备的时候要求注意的难题:主键重复,server-id不能够重复。

  1.四 查看主库安装好半联机插件的事态

贰、实验步骤

mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.02 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

master:192.168.41.145   slave:192.168.41.137

二. 在从库安装半一起插件

一、配置文件

  贰.壹 查看从库未开启半同步时的景色

定义master使用奇数id

mysql> show status like '%semi%';
Empty set (0.00 sec)

mysql> show variables like '%semi%';
Empty set (0.00 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+

vim   /etc/my.cnf

  二.二 查看从库的半同步插件

auto_increment_offset=1  
 #美高梅手机版4858,起初值。一般填第n台主MySQL。此时为第贰台主MySQL

$ ls -lh /usr/local/mysql/lib/plugin/semisync_slave.so
-rwxr-x--- 1 mysqladmin dba 245K Jan 19  2015 /usr/local/mysql/lib/plugin/semisync_slave.so

auto_increment_increment=2  
 #步进值auto_imcrement。一般有n台主MySQL就填n

  二.三 安装从库的半联名插件

slave 使用偶数id

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

mysql> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

auto_increment_offset=2

  2.4 查看从库安装好半共同插件的景象**

auto_increment_increment=2

mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.02 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+-------------------+---------+
| Name                       | Status   | Type               | Library           | License |
+----------------------------+----------+--------------------+-------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |
+----------------------------+----------+--------------------+-------------------+---------+

贰、  
因为主主复制是三个主从复制组合一同,所以就接着上面主从复制接着布置。

**2.5 重启slave复制线程**

(1)

mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

壹在slave上创办贰个192.168.四一.14伍主机能够登录的MySQL用户。

**2.陆 再度翻开半联袂状态

用户:bubu   密码:123


②创建:create user ‘bubu’@’192.168.41.145’ IDENTIFIED BY “123”;

mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

授权:grant replication slave on *.* to ‘bubu’@’192.168.41.145’
IDENTIFIED BY “123”;

贰.柒 将半联名布置写到配置文件

mysql>FLUSH PRIVILEGES;

$ grep rpl_semi_sync /etc/my.cnf
rpl_semi_sync_slave_enabled=1

叁在1九二.168.4一.一三七上查看2进制名和职责:show master status;

美高梅手机版4858 44美高梅手机版4858 45

美高梅手机版4858 46

(三) 告知2进制文件名与职责

在192.168.41.145中执行:

change master to  master_host=’192.168.41.137′,

master_user=’bubu’,

master_password=’123′,

master_log_file=’mysql-bin.000004′,

master_log_pos=601;

主主复制配置完结。

三、测试

(1)

两个主机分别运营slave:start slave;

个别查看slave状态:

192.168.41.145     【replicate-do-db=aa #要一起的数据库,暗中同意全体库】

美高梅手机版4858 47美高梅手机版4858 48

192.168.41.137

美高梅手机版4858 49美高梅手机版4858 50

当看到Slave_IO_Running:
YES、Slave_SQL_Running: YES才表明状态日常

(2)

①192.168.41.145

MariaDB [mysql]> use jesper;

create table tab1(id int);

insert into tab1  values(’66’);

select*from tab1;

②192.168.41.145

MariaDB [mysql]> use jesper;

insert into tab1  values(’99’);

select*from tab1;

翻看数据: 八个主机数据结果一律!

美高梅手机版4858 51美高梅手机版4858 52

※注意:

一、主主复制配置文件中auto_increment_increment和auto_increment_offset只好保险主键不另行,却不可能保险主键有序。

2、当配置完结Slave_IO_Running、Slave_SQL_Running不全为YES时,show
slave status\G新闻中有不当提醒,可依据错误提示进行考订。

广阔出错点:

1、两台数据库都设有db数据库,而首先台MySQL db中有tab1,第3台MySQL
db中未有tab1,那自然不能够得逞。

2、已经得到了数码的贰进制日志名和岗位,又张开了数额操作,导致POS爆发转移。在配置CHANGE
MASTE途达时还是使用在此以前的POS。

三、stop slave后,数据变动,再start slave。出错。

终极校勘法:重新执行一次CHANGE MASTE锐界就好了

————–**MySQL完毕数据库半1块复制**————-**

半联手复制情势必须在主服务器和从服务器同时中拉开,不然将会默以为异步复制格局。

一、情形计划

一急需设置方可使用:

mysql> INSTALL PLUGIN plugin_name
SONAME ‘shared_library_name’;

半一起复制:

semisync_master.so

semisync_slave.so

2反省是或不是有全自动加载功用

MariaDB [(none)]> show variables
like ‘have_dynamic_loading’;

美高梅手机版4858 53美高梅手机版4858 54

叁主从复制已经安顿好,并且1度工作。

二、实验步骤

1、主节点设置:

①安装:INSTALL PLUGIN rpl_semi_sync_master SONAME
‘semisync_master.so’;

②查看:MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE
‘rpl_semi%’;#查看半联袂复制文件

+————————————+——-+

| Variable_name | Value |

+————————————+——-+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

+————————————+——-+

③MariaDB [mydb]> SET GLOBAL
rpl_semi_sync_master_enabled=ON;【0:关闭;1:开启】

#开启半手拉手复制,暗中认可是停业的。

2、从节点设置:

①INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

②MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE
‘rpl_semi%’;#翻看是不是加载成功

+———————————+——-+

| Variable_name | Value |

+———————————+——-+

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+———————————+——-+

叁重启从服务器IO线程,手动将异步情势换来半一齐形式

MariaDB [mydb]> STOP SLAVE IO_THREAD;

MariaDB [mydb]> SET GLOBAL  rpl_semi_sync_slave_enabled = ON ;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;

MariaDB [mydb]> START SLAVE IO_THREAD;

※可查看从库错误日志观看是不是见效

叁、配置文件修改

宗旨配置文件都丰盛:

vim /etc/my.cnf

rpl_semi_sync_master_enabled=1
#表示现在运行MySQL将会活动开启半联袂复制

三、测试

摸拟slave挂掉,master等待十s仍没接到到上报时域信号,则转为异步复制形式,继续实施。

第三起步创设数据库aa

一、slave施行stop slave;关闭主从复制

二、master在aa数据库中创建表aihao,没接受到上报功率信号,等待10秒后(揽胜极光pl_semi_sync_master_timeout=壹仟等候超时),继续施行

 master:

美高梅手机版4858 55美高梅手机版4858 56

slave:

美高梅手机版4858 57美高梅手机版4858 58

三、master在数据库中再次创下立tab二,无需等待反馈,直接施行

【当汇报逾期时,master将切换成异步复制形式。此时是异步方式,无需拭目以俟】

美高梅手机版4858 59美高梅手机版4858 60

四、slave实施start
slave,数据起头联合,建构aihao、tab二,反馈给master,并切换为半共同复制

美高梅手机版4858 61美高梅手机版4858 62

 伍、slave试行stop slave;关闭主从复制

 陆、master在数据库中开创表tab叁,此时内需等待拾s,接收slave反馈实信号;等待超时,切换为异步复制方式,继续施行【步骤肆时,数据同步已经上报给master,此时master已经是半同步复制方式】

美高梅手机版4858 63美高梅手机版4858 64

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 美高梅手机版4858 版权所有