mysql-主从搭建ms

主从原理简述
1 master记录二进制日志.
1.1 在每个事务更新数据之前,master在二进制记录这些改变,mysql将这些事务串行的写入二进制日志,即使这些事务中的语句是交叉的,当日志写完之后,master会通知存储引擎提交事务,完成之后,数据变化就会写入的二进制日志中.
2 slaver将master的binlog拷贝到自己的中继日志
2.1 slaver开启工作线程io线程,再master上打开读链接.开始拷贝bingo日志.io线程写到relaylog,
2.2 sql线程从中继日志中读取事件,并存放事件,而且更新slaver数据,使得slaver和master保持一致
中继日志通常会再操作系统的缓存中.中继日志开销比较小.

在开始搭建主从之前,确保你的配置文件已经配置号.本文最后贴出我的最简my.cnf配置.没做优化,只是保证主从成功而已.具体优化策略看大家的环境而定.重点需要关注的是basedir,datadir,server_id,及从数据库其他参数文件.具体可参阅的我另一篇文章.

建立复制账号并赋权
grant replication slave,reload,super on *.* to repl@‘192.168.213.100’ identified by ‘repl’

数据备份及导入slave库
xtrabackup
mysqldump
我采用的是xtrabackup的方式,记得备份完要查看备份文件和备份点,在从库配置master的时候需要.

[root@localhost mysql]# more /tmp/20151120/xtrabackup_binlog_info 
mysql-bin.000004        685

设置主从链接(在从库上执行)

change master to master_host=‘192.168.213.100’,master_user=‘repl’,master_password=‘repl’,master_log_file=‘mysql-bin. 000004’,master_log_pos=‘685’;

启动slaver
开启slave之前要确认master的防火墙策略是可以通过的,否则slaver一直连不上.我是直接service iptables stop直接关闭的,因为我没懒得研究iptables策略了.
start slave

查看slaver状态
show slave status\G

查看关键项
Slave_IO_Running: Yes 这个是读取master的binlog
Slave_SQL_Running: Yes 这个是执行读取过来的日志中的sql
Seconds_Behind_Master: 0这个是落后master多少未执行

查看主库状态
show full processlist; 查看repl这个账号的状态.
| repl | 192.168.213.110:40911 | NULL | Binlog Dump | 788 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL

重置从库(慎操作)
reset slave 操作前需要知道主库的恢复文件及恢复点,否则将无法建立主从

master my.cnf
[root@localhost ~]# more /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock

[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/mysqld.pid
basedir = /usr/local/mysql
datadir = /home/data/mysql/data
user = mysql

server_id = 213100

log-bin = /home/data/mysql/binlog/mysql-bin.log

innodb_buffer_pool_size = 128M
character-set-server=utf8

slow_query_log
slow_query_log_file=/home/data/mysql/slowlog/mysql-slow.log
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql_safe]
open-files-limit = 8192
log-error = /usr/local/mysql/mysqld.log

slaver my.cnf
[root@localhost ~]# more /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock

[mysqld]
port = 3306
socket = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/mysqld.pid
log-error = /usr/local/mysql/mysqld.log
basedir = /usr/local/mysql
datadir = /home/data/mysql/data
user = mysql

server_id = 213110

重点在下面这些参数

log-slave-updates=1
replicate-same-server-id=0
log-bin = /home/data/mysql/binlog/mysql-bin.log
relay-log = mysql-relay-bin
master-info-repository=table
relay-log-info-repository=table
#忽略同步的表.相比binlog-ignore-db或binlog-do-db这种方式的好处可以参考网上资料

replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%


innodb_buffer_pool_size = 128M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql_safe]
open-files-limit = 8192
log-error = /usr/local/mysql/mysqld.log