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 = 3306socket = /usr/local/mysql/mysql.sock[mysqld]port = 3306socket = /usr/local/mysql/mysql.sockpid-file = /usr/local/mysql/mysqld.pidbasedir = /usr/local/mysqldatadir = /home/data/mysql/datauser = mysqlserver_id = 213100log-bin = /home/data/mysql/binlog/mysql-bin.loginnodb_buffer_pool_size = 128Mcharacter-set-server=utf8slow_query_logslow_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 = 8192log-error = /usr/local/mysql/mysqld.log

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

[client]port = 3306socket = /usr/local/mysql/mysql.sock[mysqld]port = 3306socket = /usr/local/mysql/mysql.sockpid-file = /usr/local/mysql/mysqld.pidlog-error = /usr/local/mysql/mysqld.logbasedir = /usr/local/mysqldatadir = /home/data/mysql/datauser = mysqlserver_id = 213110

重点在下面这些参数

log-slave-updates=1replicate-same-server-id=0log-bin = /home/data/mysql/binlog/mysql-bin.logrelay-log = mysql-relay-binmaster-info-repository=tablerelay-log-info-repository=table#忽略同步的表.相比binlog-ignore-db或binlog-do-db这种方式的好处可以参考网上资料replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%innodb_buffer_pool_size = 128Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysql_safe]open-files-limit = 8192log-error = /usr/local/mysql/mysqld.log