mysql主从复制实践

重要的前戏条件

1. 主库必须开启binlog日志
2. 主从库的server-id必须不同
3. 主从库必须保证网络畅通可访问

配置开始

1. 主库配置，修改主库配置文件，重载配置，重启mysql

//主要更改如下，主库server-id 最好更改，这样不用修改重库配置

server-id = 300			#必须唯一
log_bin = mysql-bin 	#开启及设置二进制日志文件名称
binlog_format = MIXED
expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
binlog-do-db = test3 	#要同步的数据库
binlog-ignore-db = mysql 	#不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

2. 创建授权用户

//执行sql 设置从库名为：slave  密码：123456
grant replication slave on *.* to 'slave'@'%' identified by '123456';

3. 查看主库 log-bin是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

4. 查看主库 master状态，日志Position

mysql> show master status \G;
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 4170
    Binlog_Do_DB: test3,test3
Binlog_Ignore_DB: mysql,mysql
1 row in set (0.00 sec)

5. 从库操作-

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.10.212',   //主库IP
MASTER_PORT=4300,                       //主库端口
MASTER_USER='slave',                     //访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456',      //登录密码
MASTER_LOG_FILE='mysql-bin.000003',  //主库二进制日志
MASTER_LOG_POS=438;   //主库二进制日志position

6. 查看配置成功

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 111.230.128.127
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1305
               Relay_Log_File: izwz9fgwaldgwiyc3d37ztz-relay-bin.000002
                Relay_Log_Pos: 150
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000001' at 1305, the last event read from './mysql-bin.000001' at 1305, the last byte read from './mysql-bin.000001' at 1324.'

注意：：：
//当Slave_IO_Running 和 Slave_SQL_Running 都为 yes 时，配置成功
Slave_IO_Running为no 在下面 Last_IO_Error 显示了上次连接错误原因，可进行更改后。
进行 stop slave;   CHANGE MASTER TO ......  ; start slave;  即可成功