必赢手机app下载 / Blog / 必赢手机app下载 / 主从复制,6复制本事
必赢手机app下载 14

主从复制,6复制本事

当前环境规划

前言

前篇说了作为运维在数据库块最起码要会两大技能,今天来说说第二技能–主从复制

随着业务的增长,一台数据库服务器以满足不了需求了,负载过重,这时候就需要减压,实现负载均衡读写分离,一主一从或一主多从

主服务器只管写,从服务器管读,从而提高效率减轻压力。

主从复制分类:

主从同步:当用户写数据主服务器必须和从服务器同步一致了才告诉用户写入成功,等待时间太长

主从异步:只要用户访问写数据主服务器写入立马返回给用户成功

主从半步同步:当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功

备注:通常都是使用的主从异步,根据环境需求来选择,想要数据更安全选择半步同步

主机名称 

主从复制注意事项

注意:selinux策略、防火墙

1、开启二进制日志

log_bin

2、设置二进制记录格式为ROW(推荐)

3、设置唯一server-id

server_id=#

4、设置datadir中日志名称(可选)

log-basename=master

5、创建有复制权限的用户账号

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

6、如果要启用级联复制,需要在从服务器启用

log_bin
log_slave_updates

7、限制从服务器为只读

read_only=ON

8、禁止主机名解析

skip_name_resolve = on

9、高可用从服务器要加上

relay_log_purge=0 #不清除中继日志

10、如何保证主从复制的事务安全(根据需求添加)

1)在master节点启用参数:

sync_binlog=1
# 每次写后立即同步二进制日志到磁盘,性能差

2)如果用到的为InnoDB存储引擎:

innodb_flush_log_at_trx_commit=1 
# 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 
# 默认值,分布式事务MariaDB10.3.0废除
sync_master_info=# 
# #次事件后master.info同步到磁盘

3)在slave节点启用参数:

sync_relay_log=# 
# #次写后同步relay log到磁盘
sync_relay_log_info=#
# #次事务后同步relay-log.info到磁盘

ec2t-pgtest-01

主从复制原理

如图

必赢手机app下载 1

备注:

主从同步有延迟,为什么?因为它时是单线程传送日志

ec2t-pgtest-02

实战-实现主从复制+高可用

IP地址

准备工作

准备4台主机67、17、37、57,分别充当角色为管理主机、主服务器、2个从服务器

安装包:和主服务器同版本的mysql数据库包、和高可用安装包

mha4mysql-manager

mha4mysql-node

比如主服务器已经运行了1年了,发现满足不了需求了,需要搭建从服务器,我们先从搭建从服务器开始

备注:首先确保主服务器开启了二进制日志

确认二进制文件已启动

MariaDB [(none)]> show variables like ‘%log_bin%’

确认server-id

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

10.189.102.118

主服务器设置

1、创建可用于复制的账号

grant replication slave on *.* to repluser@'192.168.43.%' identified by 'centos';

2、完全备份数据(用于在从服务器上来还原)

mysqldump -pcentos -A -F --single-transaction --master-data=1 >/data/all-`date +%F`.sql

备注:备份时的某表的状态

必赢手机app下载 2

为了下面的实验测试在这里我们备份玩再增加一条记录

insert hellodb.students (name,age)values('gaoda001',20);

必赢手机app下载 3

3、传送到从服务器37、57上

scp…

10.189.100.195

37、57(从)服务器设置

角色

1、配置文件
vim /etc/my.cnf

1)开启二进制日志

log-bin=mysql-bin

2)设置server-id(要和主服务id区分开)

server-id=2

备注:57设置为3 只要他们不相同就可

3)从服务器设置为只读

read_only=on

4)禁止主机名解析

skip_name_resolve = on

5)数据和索引分开存放

innodb_file_per_table = on 
# 10版本以上默认开启

6)重启或启动服务

备注:以防万一可以查看下最重要的两项开启了没有

mysql -e "show variables like 'log%'"
mysql -e "show variables like 'server%'"

master      

2、实现复制

1)进入数据库查找同步代码

MariaDB [(none)]> help change
MariaDB [(none)]> help change master to 

必赢手机app下载 4

2)根据情况编辑上图上的信息

注:起始位置可以在完全备份文件里查看

两种方式:

① 先还有完全备份数据再拿以上信息编辑下,在数据库中执行

② 直接把上面的信息添加到完全备份文件里

下面用第二种方式来操作如图

必赢手机app下载 5

3)37主机还原并查看

mysql < all-2018-08-08.sql

必赢手机app下载 6

备注:以还原到备份时的状态

          
主服务器备份完有用户新加数据对吧,下面开启主从复制看看能不能复制过来最新的数据

4)查看复制状态

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.43.17
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005  
          Read_Master_Log_Pos: 385       #从主服务器读取到的位置
               Relay_Log_File: centos7_05-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No       #表示还没开启
            Slave_SQL_Running: No       #表示还没开启
              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: 385
              Relay_Log_Space: 256
              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: NULL   ##注意这个 表示同步时间差
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: 0
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
1 row in set (0.03 sec)

5)开启主从复制并查看状态

start slave;

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.17
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 608
               Relay_Log_File: centos7_05-relay-bin.000003
                Relay_Log_Pos: 778
        Relay_Master_Log_File: mysql-bin.000005
             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: 608
              Relay_Log_Space: 1092
              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_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    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
1 row in set (0.00 sec)

6)查看表确认有没有从主服务器同步最新的数据

必赢手机app下载 7

7)57(从服务器同样的设置)

备注:目前现实了异步同步复制,下面来看半同步设置

slave    

半同步设置

系统版本  

主服务器设置

1、首先在主服务器上安装插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

备注:

show plugins; ##查看当前系统中的插件列表

UNINSTALL PLUGIN rpl_semi_sync_master;##卸载插件

2、查看插件状态

SHOW GLOBAL VARIABLES LIKE '%semi%';

必赢手机app下载 8

3、开启

set global rpl_semi_sync_master_enabled=on;

备注:最好写在配置文件中

必赢手机app下载 9

4、查看插件变量状态

SHOW GLOBAL STATUS LIKE '%semi%';

必赢手机app下载 10

备注:这里记录有几个半同步主机

CentOS release 6.8

从服务器设置

1、安装插件

INSTALL PLUGIN rpl_semi_sync_slave SONAME  'semisync_slave.so';

2、启动插件

set global rpl_semi_sync_slave_enabled=on;

备注:同样建议写在配置文件中

必赢手机app下载 11

3、从服务器查看改插件是否开始工作

SHOW GLOBAL STATUS LIKE '%semi%';

必赢手机app下载 12

为什么没有开始工作?

因为是先开启的主从复制再安装的次插件

所以这种情况下,先停止从服务器的主从复制功能

1)停止:

stop slave;

2)再次开启主从复制

start slave;

3)再去查看

必赢手机app下载 13

备注:现在就启用了半同步功能,下面开始搭建高可用,实现主服务器宕机自动提升从服务器当主

数据版本  

MHA高性能

备注:四台主机之间必须是ssh基于key验证登陆,所以要先实现ssh互相链接

步骤:

ssh-keygen

必赢手机app下载,cd .ssh

ssh-copy-id 192.168.43.67

把.ssh目录考到其他主机

1、在主服务器上创建管理者账号

grant all on *.* to mhauser@'192.168.43.%' identified by 'centos';

2、在67管理主机上安装以下包

mha4mysql-manager 
mha4mysql-node

3、在17、37、57安装如下包

mha4mysql-node

4、在管理主机创建管理节点

mkdir /etc/mastermha/
cd /etc/mastermha/
vim app1.cnf

根据上面创建的信息填写

[server default]
user=mhauser
password=centos
manager_workdir=/app/mastermha/app1/
manager_log=/app/mastermha/app1/manager.log
remote_workdir=/app/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
##以上是全局设置

##以下是针对某一组集群设置
[server1]
hostname=192.168.43.17
candidate_master=1
[server2]
hostname=192.168.43.37
candidate_master=1
[server3]
hostname=192.168.43.57
candidate_master=1

备注:

candidate_master=1:是说主服务器宕机了带有这项的主机有机会当主

5、测试

1)ssh协议

/usr/bin/masterha_check_ssh --conf=/etc/mastermha/app1.cnf

2、检查复制

masterha_check_repl --conf=/etc/mastermha/app1.cnf

3、启动(默认前台执行)

masterha_manager --conf=/etc/mastermha/app1.cnf

必赢手机app下载 14

备注:如果你是编译安装在/etc/mastermha/app1.cnf配置文件中指定日志路径,最好主从服务器数据库设置的位置都是一致的。

必赢手机app下载 15

MySQL 5.6.23

一. MySQL数据库安装

1. 创建MySQL用户和组

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

2. 配置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

3. 下载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/

4. 配置MySQL数据库(主备操作)

  4.1 创建MySQL配置文件/etc/my.cnf

# 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

  4.2 初始化MySQL数据库

$ 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

  4.3 启动MySQL数据库,并为root用户设置密码,删除多余用户

$ /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)

  4.4 安装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                                 |
+-----------------------------------------------------+

  4.5 设置MySQL服务相关

# 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

 二. MySQL主从复制部署

1. 同步主备库时间,可在主库搭建NTP SERVER,备库作为NTP client

2. 添加hosts文件解析确保主备可以互相解析host主机名

3. 主库做以下操作

  3.1 启用二进制日志

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

  3.2 选择一个唯一的server-id

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

   3.3 创建具有复制权限的用户

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)

4. 备库做以下操作

  4.1 启用中继日志

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

  4.2 选择一个唯一的server-id**

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

 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)

** 4.4 从服务器开启二进制日志(可选,做级联复制的时候需要)


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

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

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

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

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

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

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

6. 在从库连接主库

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 statusG
*************************** 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)

 

 
注意:如果此时主库已经运行一段时间,并且有大量数据存在,需要使用mysqldump命令将主库导出(要指定记录导出时的二进制文件和position)。然后把导出来的数据再次导入进从服务器,此时,再去连接主服务器就需要指定从主服务器的哪个二进制文件和position开始复制数据了。而怎么知道从哪个二进制文件和position开始复制数据呢?就是从mysqldump导出数据文件中记录了导出数据时的二进制文件和position,可以试用一下命令将主库导出

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

7. 在主库查看从库信息

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)

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

 半同步机制

a. 当Master上开启半同步复制的功能时,至少应该有一个Slave开启其功能。此时,一个线程在Master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件,或等待超时。

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

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

d.  如果等待超时,也就是Master没被告知已收到,此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了,Master与其Slave自动转换为半同步复制的机制。

e.  半同步复制的功能要在Master,Slave都开启,半同步复制才会起作用;否则,只开启一边,它依然为异步复制。

1. 在主库安装半同步插件

  1.1 查看主库未开启半同步时的状态

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

  1.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

  1.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)

 
安装后启动和定制主从连接错误的超时时间默认是10s可改为5s,一旦有一次超时自动降级为异步。(以上内容要想永久有效需要写到配置文件中)

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

  1.4 查看主库安装好半同步插件的状态

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

2. 在从库安装半同步插件

  2.1 查看从库未开启半同步时的状态

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 查看从库的半同步插件

$ 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

  2.3 安装从库的半同步插件

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)

  2.4 查看从库安装好半同步插件的状态**

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复制线程**

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

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

**2.6 再次查看半同步状态


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

2.7 将半同步配置写到配置文件

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

发表评论

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

相关文章

网站地图xml地图