环境如下：
Master：Ubuntu14.04-192.168.31.61、mysql5.5.50
Slave： Ubuntu14.04-192.168.31.66、mysql5.5.49
数据库：mifi
数据库表：mf_group_members
因mysql本身的主从复制已相当的完善，所以这里就不多描述，直接来。
修改Master的my.cnf文件如下：
vim /etc/mysql/my.cnf
# The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 61#为了方便这里建议使用主机IP地址 log_bin = /var/log/mysql/mysql-bin.log binlog-do-db=mifi#需要同步的数据库 binlog-ignore-db=mysql#忽略的数据库 binlog-ignore-db=information-schema#忽略的数据库 #为了使用事务的InnoDB在复制中最大的持久性和一致性，你应该指定innodb_flush_log_at_trx_comm#it=1,sync_binlog=1选项 innodb_flush_log_at_trx_commit=1 sync_binlog=1 expire_logs_days = 10 max_binlog_size = 100M
wq!保存退出后
sudo service mysql restart
重启mysql服务。
在主机Master上创建一个有复制权限的用户
mysql> grant replication slave on *.* to repl_user@'192.168.31.66' identified by '123456';
锁住主机，记录二进制日志的位置(后面设置从机的时候会用到) mysql>flush tables with read lock; mysql> show master status; +------------------+----------+--------------+--------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+--------------------------+ | mysql-bin.000001 | 1114 | mifi | mysql,information-schema | +------------------+----------+--------------+--------------------------+ 1 row in set (0.00 sec)
接着导出此时的数据到/tmp/mifi.sql
$ mysqldump -uroot -p mifi > /tmp/mifi.sql $ scp administrator@192.168.31.61:/tmp/mifi.sql ./ #拷贝主数据库sql文件。
重新打开主机Master写操作功能
mysql>unlock tables;
=》修改从机Slave配置文件(/etc/mysql/my.cnf)
# The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 66 #log_bin = /var/log/mysql/mysql-bin.log relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin replicate-do-db=mifi replicate-ignore-db=mysql replicate-ignore-db=information-schema expire_logs_days = 10 max_binlog_size = 100M
从机Slave初始化复制
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_host='192.168.31.61', -> master_user='repl_user', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=1114; Query OK, 0 rows affected (0.00 sec) #开启复制 mysql> start slave; Query OK, 0 rows affected (0.00 sec) 查看复制状态 mysql>show slave status\G
至此已完成mysql的主从复制的配置工作。
测试如下：
主192.168.31.61
mysql> update mf_group_members set member_id=290 where id = 121; mysql> select * from mf_group_members where id = 121; +-----+----------+-----------+-------------+ | id | group_id | member_id | update_time | +-----+----------+-----------+-------------+ | 121 | 35 | 290 | 1472005745 | +-----+----------+-----------+-------------+ 1 row in set (0.00 sec)
从192.168.31.66
mysql> select * from mf_group_members where id = 121; +-----+----------+-----------+-------------+ | id | group_id | member_id | update_time | +-----+----------+-----------+-------------+ | 121 | 35 | 290 | 1472005745 | +-----+----------+-----------+-------------+ 1 row in set (0.00 sec)
提醒下中间可能会碰到的问题：
160825 9:41:11 [ERROR] Slave I/O: error connecting to master 'repl_user@192.168.31.61:3306' - retry-time: 60 retries: 86400, Error_code: 2003
然后我去从服务器直接连接主的数据库发现问题：
$ mysql -urepl_user -p -h192.168.31.61 Enter password: ERROR 2003 (HY000):Can`t connect to MySQL server on '192.168.31.61'(111)
解决方法：
Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1//把这句绑定IP地址给注销掉 #
然后就可以了。
