MySQL semi-master copy

MySQL semi-master copy

NODE3:172.16.92.3/16 MARIADB Primary Server
NODE4:172.16.92.4/16 mariadb from the server
The above nodes are CentOS 7.1

Configuring the Environment
1. Configure the disc yum source
2. Turn off SELinux and iptables


NODE3:MARIADB Primary Server

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

###### #以下的内容为添加 ########
#二进制变更日志
Log-bin=mysql-bin
#二进制日志格式为混合模式
Binlog_format=mixed
#为主服务器node3的ID值
Server-id = 1
Port = 3306
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 4
Innodb_file_per_table = On
Skip_name_resolve = On
###############################

###### The following content is optional ########
[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 128M
Sort_buffer_size = 128M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout
#############################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d
############### End for My.cnf #################

[Email protected] ~]# RPM-QL Mariadb-server | grep ' Semisync '
/usr/lib64/mysql/plugin/semisync_master.so #用于Master服务器安装的半同步插件
/usr/lib64/mysql/plugin/semisync_slave.so #用于Slave服务器安装的半同步插件

[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(none)]> Install plugin rpl_semi_sync_master soname ' semisync_master.so '; #安装Master半同步插件
MariaDB [(None)]> set global rpl_semi_sync_master_enabled = 1; #开启Master半同步功能
MariaDB [(None)]> set global rpl_semi_sync_master_timeout = 2000; #超时时间毫秒级
MariaDB [(None)]> grant replication client,replication slave on * * to ' repluser ' @ ' 172.16.92.4 ' identified by ' Replpass ‘;
MariaDB [(None)]> flush privileges;
MariaDB [(None)]> Show Master Status\g
1. Row ***************************
file:mysql-bin.000003
position:497
binlog_do_db:
binlog_ignore_db:

##### write down mysql-bin.000003 and 497, and set the time to relay logs from the server



NODE4:MARIADB from the server

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

########## Add the following content ##########
Log-bin=mysql-bin
Binlog_format=mixed
Server-id = 2
Relay-log = Relay-bin
Log_slave_updates = 1
Read_Only = On

Port = 3306
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 4
Innodb_file_per_table = On
Skip_name_resolve = On
###################################

######### The following content is optional ############
[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 128M
Sort_buffer_size = 128M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout
####################################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d

############# End of My.cnf ###############

[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(None)]> show global variables like '%read_only% ' \g
1. Row ***************************
Variable_name:read_only
Value:on

MariaDB [(none)]> change master to master_host= ' 172.16.92.3 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=497,master_connect_retry=5,master_heartbeat_period=2;
MariaDB [(none)]> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so '; #安装Slave半同步插件
MariaDB [(None)]> set global rpl_semi_sync_slave_enabled = 1; #开启Slave半同步功能
MariaDB [(None)]> start slave; #重启IO线程生效

MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_running:yes
Slave_sql_running:yes
........ The rest of the information is slightly ...

MariaDB [(None)]> show Processlist\g
3. Row ***************************
State:slave have read all relay log; Waiting for the slave I/O thread to update it
........ The rest of the information is slightly ... Description: All relay logs have been received from the node

MariaDB [(None)]> show global status like '%semi% ';
| Rpl_semi_sync_slave_status | On | #半同步状态已经开启了;

MariaDB [(None)]> show global variables like '%rpl% ';
| rpl_semi_sync_slave_enabled | On | #从服务器节点的半同步已经开启;


Node3 on the master node to view this process
MariaDB [(None)]> show Processlist\g
2. Row ***************************
State:master have sent all binlog to slave; Waiting for Binlog to be updated
........ The rest of the information is slightly ...

MariaDB [(None)]> show global status like ' rpl_semi% ';
| rpl_semi_sync_master_clients |    1 | #可以看出有一个半同步客户端了;
........ The rest of the information is slightly ...

To create a database test on the master node is capable of master and slave semi-synchronous
MariaDB [(None)]> CREATE database TestDB;

From the node can see the TestDB database, the master-slave synchronization success!
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| TestDB |
+--------------------+

And look at the state of the node.
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.92.3
Master_user:repluser
master_port:3306
Connect_retry:5
master_log_file:mysql-bin.000003
read_master_log_pos:584 #497->584
relay_log_file:relay-bin.000002
relay_log_pos:616 #529->616
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes


############# MySQL semi-master copy end ##############

This article is from the "8317626" blog, please be sure to keep this source http://8327626.blog.51cto.com/8317626/1711198

MySQL semi-master copy