mysql搭建主从同步

主从同步的原理

1.环境配置(首先得使主从库单独都 好使了 这里不再赘述 可参考网上文档)

windows本机装好mysql（5.6.12） 虚机装好mysql(5.5.43)

windows本机ip 192.168.109.203(为主 A)  虚机ip 192.168.109.159(为从 B)

在A上修改配置文件

#确保有一下两行，并开启

log_bin = mysql-bin

server_id = 1

如果是5.6以上版本还应该加上

binlog_checksum=none

否则的话会出现Last_IO_Error: Got fatal error 1236 from master when reading data from >> binary log: 'Slave can not handle replication events with the checksum that >> master is configured to log; the first event 'mysql-bin.000001'这样的错误

然后重启master(/etc/init.d/mysqld restart)

在A上执行赋权语句

GRANT REPLICATION SLAVE,FILE ON *.* TO 'replication'@'192.168.109.159' IDENTIFIED BY '123456';(replication/123456 为从 从库上连接主库的账号配置从库时会用到)

ok  主库配置完了 可以执行

SHOW MASTER STATUS;来查看主库的运行状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1453 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

这里的mysql-bin.000004和1453在配置从库的时候会用到

开始配置从库

确保有以下几行，并去掉#

server-id               = 2//不能喝主库的id一样，否则会报错
relay_log=/var/log/mysql/mysql-relay-bin//中继日志
read_only = 1
log_slave_updates = 1
log_bin = /var/log/mysql/mysql-bin.log (如果这么写有问题 可以改成log_bin = mysql-bin  默认在/usr/local/mysql/data中)

执行连接语句

1.CHANGE MASTER TO MASTER_HOST='192.168.109.203', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=685;

PS：MASTER_LOG_FILE和MASTER_LOG_POS是上面show master status语句执行的结果，master每重启一次这个值就会相应的变一次，所以在重启master后都要相应的执行下show master status 然后再执行下CHANGE语句。另外，如果是第二次执行得先执行slave stop;再执行reset slave;再执行CHANGE语句，否则会报错。

        另外执行这句可能会报错ERROR 29 (HY000): File '/var/log/mysql/mysql-relay-bin.index' not found (Errcode: 13 - Permission denied)这个直接创建这个目录和文件就好了

2.START SLAVE;//启动丛库进程

3.mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: station192.example.com

Master_User: tom

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 175

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 175

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes //必须为yes

Slave_SQL_Running: Yes //必须为yes

PS:如果主库中已经存在一个数据库，并且该库中有一张表，相应丛库中并没有该库或该表，如果此时更新该表中的数据，此时会到时SQL进程停掉报错为：

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. 原因就是因为丛库中没有相应的库或表

4.SHOW PROCESSLIST\G

*************************** 2. row ***************************
     Id: 51
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2632
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 52
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2428
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

在windows本机查看binlog方式为

mysqlbinlog --start-position=120 C:\wamp\bin\mysql\mysql5.6.12\data/mysql-bin.000015
mysqlbinlog C:\wamp\bin\mysql\mysql5.6.12\data/mysql-bin.000015 > C:\wamp\bin\mysql\mysql5.6.12\data/test.txt