1. 主库服务器配置

主服务器MySQ配置参数，参数主要依据《高性能MySQL 第三版》

#replication safe for innodb engine
innodb_flush_logs_at_commit
innodb_support_xa=1


#replication
server-id=100
log-bin=mysql-bin
log-error=mysql-bin.err
expire_logs_days=30
sync_binlog=1

2. 添加复制用户

只需要赋两个全局的权限: REPLICATION SLAVE, REPLICATION CLIENT

CREATE USER 'repl'@'192.168.10.132' IDENTIFIED BY '***';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'repl'@'192.168.10.132' IDENTIFIED BY '***';

3. 备份主库，备份中加入binlog及位置

全面备份主库，备份前需要已经启用二进制日志。

mysqldump -hlocalhost --opt --master-data=1  --all-databases --max_allowed_packet=8M --net_buffer_length=128K -uroot -pyour-password >all.sql

为了获得一致性的备份，考虑加入参数--single-transaction 或 --lock-all-tables，分别针对事务型引擎及非事务型引擎，但它们不能同时使用。

注意其中--master-data=1参数，它将binlog及位置信息生成到备份文件中，大概如下一行

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=8747725;

4.iptables放行备库连入

针对从服务器ip到本地3306端口的连接

iptables -I INPUT 7 -s 192.168.10.132/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

5. 配置从服务器

server-id=201
log-bin=mysql-bin
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
expire_logs_days=30
read_only=1

6. 还原到从库服务器上

SOURCE /path/to/bakup.sql;

6. 配置从库，连接到主库

事实上下面的log_file,log_pos可以不用再指定，因为备份里已经有该信息并还原到这里了。当然再指定一次也没负作用。

change master to master_host='192.168.10.100', 
master_user='repl',
master_password='repl-password', 
master_log_file='mysql-bin.000004', 
master_log_pos=8747725;

7. 启动slave

start slave

顺利的话即完成。

如报 Failed to open the relay log 的错误，先停止slave的mysql服务，删除 /var/lib/mysql/ 下的中继日志相关文件，主要是  *-relay-bin.* 及 relay-log.info，再启动重新start slave

x. 如果异常，复制失败时，或许需要重配置slave才可以解决问题

 

stop slave;
RESET SLAVE;
SOURCE /path/to/bakup.sql;
RESET SLAVE;
change master to ...
show slave status; #检查状态，确认主库及连接账号等
START SLAVE;

附记注意事项：

• 关于binlog_format 参数。在mysql 5.5(win32)版下，该参数默认值为STATEMENT。于是，至少有以下问题insert into tablename select ... limit n这样的语句，执行时会报warning错误，原因是limit结果在一致性上是非安全的。

end

延伸阅读

• MySQL进程(process)中出现大量locked，问题分析与解决
• mysqld 配置优化，请一定要加 skip-name-resolve，否则将被show processlist 里的 unauthenticated user 搞死
• windows下安装zip压缩版的mysql服务器端v5.7.16（另MariaDB 10.4.10）
• mssql(microsoft sql server2005)数据导入mysql方法
• windows下mysql自动定期备份并压缩/以discuz论坛为例
• 把mysql中的表数据导到csv文件
• mysql join查询时，参与join的字段字符集编码不同，对性能影响是巨大的
• ms sql server下unix时间戳函数unix_timestamp, from_unixtime（与mysql兼容）
• MySQL函数 GROUP_CONCAT，相当有用
• mysql对大表执行update速度慢时，试试改用insert可能会有意想不到的发现
• 通过网络ntp自动同步linux主机的日期时间
• linux下日期时间自动同步设置（rdate,ntpdate两种方法）
• VIM复制命令详解

coded by nessus