心得笔记网站首页 心得笔记

mysql5.5主从复制的筛选复制与mysql5.6的主从复制

发布时间：2019-06-09 19:17:17编辑：雪饮阅读（）

上次讲到了mysql5.5的主从复制，这次讲下基于mysql5.5主从复制时仅复制指定库以及mysql5.6的主从复制

仅复制指定库

比如我们只想复制master的discuz库，则在slave服务器上面配置如下

replicate_do_db = discuz

此时即便master若没有discuz库，而master新增其它库不会被同步到slave，但是master若新增一个库名为discuz的库，则会被同步到slave中去。

mysql5.6的主从复制搭建

这次基于mysql5.6的gtid搭建主从复制，通过gtid可以查看不同从服务器对于master的同步情况，以便进行将某个slave升级为master时候进行补差，那么基于此，我们就不用像mysql5.5通过定位到具体某个二进制文件以及该二进制文件中的position这么繁琐的实现主从了。

系统环境rhel6.4x32

安装mysql5.6

[root@localhost src]# tar -zxvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz

[root@localhost src]# ln -sv /usr/local/src/mysql-5.6.10-linux-glibc2.5-i686 /usr/local/mysql

`/usr/local/mysql' -> `/usr/local/src/mysql-5.6.10-linux-glibc2.5-i686'

[root@localhost src]# mkdir -pv /mydata/data

mkdir: created directory `/mydata'

mkdir: created directory `/mydata/data'

[root@localhost src]# useradd -r mysql

[root@localhost src]# chown -R mysql.mysql /mydata/data

[root@localhost src]# cd /usr/local/mysql/

[root@localhost mysql]# chown -R root.mysql ./*

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@localhost mysql]# chkconfig --add mysqld

[root@localhost mysql]# cat /etc/profile.d/mysql.sh

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

[root@localhost mysql]# ls

bin COPYING data docs include INSTALL-BINARY lib man my.cnf mysql-test README scripts share sql-bench support-files

这次不用复制my.cnf，该文件就在你执行初始化脚本时所在的目录生成了

mysql客户端环境变量导入

本步骤非必须，就是为了方便而已

[root@localhost mysql]# . /etc/profile.d/mysql.sh

master配置

root@localhost mysql]# cat my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

datadir = /mydata/data

innodb_file_per_table = ON

server-id = 1

socket=/tmp/mysql.sock

log-bin=master-bin

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

report-port=3306

port=3306

report-host=192.168.2.155

其中report-host设定为master自己的ip地址

master创建用于主从复制的用户

mysql> grant replication slave on *.* to 'repluser'@'192.168.2.%' identified by 'replpass';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;