当前环境规划
| 主机名称 | ec2t-pgtest-01 | ec2t-pgtest-02 |
| IP地址 | 10.189.102.118 | 10.189.100.195 |
| 角色 | master | slave |
| 系统版本 | CentOS release 6.8 | |
| 数据版本 | MySQL 5.6.23 | |
一. MySQL数据库安装
1. 创建MySQL用户和组
# groupadd -g dba
# useradd -u -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 programsPATH=/usr/local/mysql/bin:$PATH:$HOME/binexport 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.-linux-glibc2.-x86_64.tar.gz
$ mv mysql-5.6.-linux-glibc2.-x86_64/* /usr/local/mysql/
4. 配置MySQL数据库(主备操作)
4.1 创建MySQL配置文件/etc/my.cnf
# cat /etc/my.cnf
[client]
port =
socket = /usr/local/mysql/data/mysql.sock[mysqld]
port =
socket = /usr/local/mysql/data/mysql.sockskip-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=32Mtable_open_cache =
thread_cache_size =
wait_timeout =
interactive_timeout =
max_connections = # Try number of CPU's*2 for thread_concurrency
thread_concurrency = #isolation level and default engine
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTEDserver-id =
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/hostname.pid#open performance schema
log-warnings
sysdate-is-nowbinlog_format = MIXED
log_bin_trust_function_creators=
log-error = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =
#general_log_file = /usr/local/mysql/data/general_log.err
#slow_query_log=
#slow_query_log_file=/usr/local/mysql/data/slow_log.err#for replication slave
#log-slave-updates
#sync_binlog = #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 =
innodb_log_file_size = 200Minnodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 16Minnodb_lock_wait_timeout =
#innodb_thread_concurrency =
innodb_flush_log_at_trx_commit =
innodb_locks_unsafe_for_binlog=#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=
innodb-write-io-threads=
innodb-io-capacity=
#purge threads change default() to for purge
innodb_purge_threads=
innodb_use_native_aio=on#case-sensitive file names and separate tablespace
innodb_file_per_table =
lower_case_table_names=[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...-- :: [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
-- :: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OKFilling help tables...-- :: [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
-- :: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your systemPLEASE 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- password 'new-password'Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installationwhich 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.plPlease report any problems at http://bugs.mysql.com/The latest information about MySQL is available on the web at http://www.mysql.comSupport MySQL by buying support/licenses at http://shop.mysql.comWARNING: 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 &
[] 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 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 =
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 =
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 =
5. 查看主库当前binary log和postion
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)
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 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)
注意:如果此时主库已经运行一段时间,并且有大量数据存在,需要使用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--- mysqladmin dba 408K Jan /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 = ;
rpl_semi_sync_master_timeout = ;
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--- mysqladmin dba 245K Jan /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=
