mysql主从搭建

生产库已经使用了一段时间，现在需要搭建一个从库，主库一开始还没有开启二进制日志，操作步骤

1、将主库备份，或者将主库的物理文件拷贝给从库，物理文件拷贝的方式需要数据库版本一致

这样此时数据库master 和 slave的数据库数据同步了

物理文件压缩命令：

tar  --warning=no-file-changed  -czf  data.tgz   mysql_data_dir

 

2、主库配置新增二进制日志和配置server-id

expire_logs_days=30
log-bin=/var/lib/mysql/mysql-bin
max_binlog_size=1G
binlog_format=row
binlog_row_image=full
server-id=66

 

3、主库授权

grant replication slave on *.*  to repluser@'192.168.0.10' identified by 'centos';

　　

4、从库配置新增只读和server-id

server-id=77
read-only

 

5、查看当前master节点的二进制日志位置：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000034 |    98182 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

6、从库和master库进行关联

mysql> CHANGE MASTER TO   MASTER_HOST='mysql.test-his',   MASTER_USER='repluser',   MASTER_PASSWORD='centos',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000034',   MASTER_LOG_POS=98182,   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

 

7、从库开启线程

mysql> start  slave;
Query OK, 0 rows affected (0.01 sec)

 

8、查看SQL和IO线程状态

mysql> show status slave\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

9、出现的状况

mysql> show status slave\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
       ......(省略)
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.sq_jmfwdj; Can't find record in 'sq_jmfwdj', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000034, end_log_pos 12196

当时我查看二进制日志位置之后，由于没有将master禁止写入，导致二进制日志位置变化，SQL线程不能创建

 

 

mysql> show status slave\G
             Slave_IO_Running: connecting
            Slave_SQL_Running: Yes

 

IO线程connecting的可能原因有：

1.网络不通
2.账户密码错误
3.防火墙
4.mysql配置文件问题
5.连接服务器时语法
6.主服务器mysql权限

此处我这里主要是授权的问题。。。

 

10、主从同步原理

①主从同步是基于二进制日志实现的，首先主库上记录二进制日志，在每次准备提交事务完成数据更新之前，主库将数据更新的事件记录到二进制日志中，其中mysql会按照提交事务的顺序而不是每条语句的执行顺序来记录二进制日志，在记录二进制日志之后，主库会告诉存储引擎可以提交事务了。

②从库将主库的二进制日志复制到本地的中继日志中，

　　a.从库会启动一个工作线程(IO线程)，IO线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储(binhg dump线程)，这个二进制转储线程会读取主库的二进制日志事件，它不会对事件进行轮询，该线程追上主库，就进入睡眠状态，直到主库发送信号量，通知其有新的事件产生时才会被唤醒，从库IO线程会将接收到的事件记录到中继日志中。

　　b.从库的SQL线程从中继日志读取事务并在从库执行，从而实现从库的更新。