mysql半主从,mysql半主从复制

mysql半主从复制

node3: 172.16.92.3/16 mariadb主服务器

node4: 172.16.92.4/16 mariadb从服务器

以上节点均为CentOS 7.1

配置环境

1. 配置好光盘yum源

2. 关闭selinux和iptables

node3: mariadb主服务器

[iyunv@node3 ~]# yum -y install mariadb-server

[iyunv@node3 ~]# 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 are ignored when systemd is 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

###############################

###### 以下的内容可选 ########

[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 #################

[iyunv@node3 ~]# rpm -ql mariadb-server | grep 'semisync'

/usr/lib64/mysql/plugin/semisync_master.so    #用于Master服务器安装的半同步插件

/usr/lib64/mysql/plugin/semisync_slave.so        #用于Slave服务器安装的半同步插件

[iyunv@node3 ~]# systemctl start mariadb

[iyunv@node3 ~]# 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:

##### 记下mysql-bin.000003 和 497 , 设置从服务器中继日志时有用 ####

node4: mariadb从服务器

[iyunv@node4 ~]# yum -y install mariadb-server

[iyunv@node4 ~]# 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 are ignored when systemd is 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

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

###################################

######### 以下内容可选 ############

[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 ###############

[iyunv@node4 ~]# systemctl start mariadb

[iyunv@node4 ~]# 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

........ 其余信息略 ........

MariaDB [(none)]> show processlist\G

*************************** 3. row ***************************

State: Slave has read all relay log; waiting for the slave I/O thread to update it

........ 其余信息略 ........说明: 从节点已经接收到所有的中继日志

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 主节点上可查看到此进程

MariaDB [(none)]> show processlist\G

*************************** 2. row ***************************

State: Master has sent all binlog to slave; waiting for binlog to be updated

........ 其余信息略 ........

MariaDB [(none)]> show global status like 'rpl_semi%';

| Rpl_semi_sync_master_clients | 1 |    #可以看出有一个半同步客户端了；

........ 其余信息略 ........

在主节点上创建数据库测试是否能主从半同步

MariaDB [(none)]> create database testdb;

在从节点上可看到testdb数据库, 说明主从同步成功!

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| testdb             |

+--------------------+

再来看一下 从节点 的状态

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半主从复制结束 ##############