MySQL主从架构

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。它基于binlog，主设备上须开启binlog才能进行主从。

主从过程大致有3个步骤：

1）主设备将更改操作记录到binlog里；

2）从将主设备的binlog事件(sql语句)同步到从本机上并记录在relaylog里；

3）从根据relaylog里面的sql语句按顺序执行。

主设备上有一个log dump线程，用来和从的I/O线程传递binlog。从设备上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。

准备工作

1、准备两台装有mysql的服务器，并启动mysql服务。

2、分配角色，确定设备主从。

配置主设备

1、编辑配置文件

<code>[root@plinuxos ~]</code><code># vim /etc/my.cnf</code>

<code>[mysqld]</code>

<code>server-</code><code>id</code><code>=88</code>

2、重启mysql

<code>[root@plinuxos ~]</code><code># /etc/init.d/mysqld restart</code>

<code>Shutting down MySQL.... SUCCESS! </code>

<code>Starting MySQL. SUCCESS!</code>

3、检查log_bin

<code>[root@plinuxos ~]</code><code># ls /data/mysql/bin01.*</code>

<code>/data/mysql/bin01</code><code>.000001 </code><code>/data/mysql/bin01</code><code>.index</code>

4、创建数据库

<code>[root@plinuxos mysql]</code><code># cd /data/mysql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot -e 'create database db01'</code>

5、增加测试数据

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot zrlog >/tmp/zrlog.sql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot db01 </tmp/zrlog.sql</code>

6、备份所有数据库

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot db01 > /tmp/db01.sql</code>

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysqldump -uroot zrlog > /tmp/zrlog.sql</code>

7、创建用户

<code>[root@plinuxos mysql]</code><code># /usr/local/mysql/bin/mysql -uroot</code>

<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>

<code>Your MySQL connection </code><code>id</code> <code>is 414</code>

<code>Server version: 5.6.35-log MySQL Community Server (GPL)</code>

<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</code>

<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>

<code>mysql> grant replication slave on *.* to </code><code>'repl'</code><code>@</code><code>'122.112.197.192'</code> <code>identified by </code><code>'123456'</code><code>;</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

8、锁表并查看状态

<code>mysql> flush tables with </code><code>read</code> <code>lock; </code><code>##锁表，防止写入</code>

<code>mysql> show master status; </code><code>##下方两个参数需要在从设备上配置</code>

配置从设备

<code>[root@ecs-89c1 mysql]</code><code># vi /etc/my.cnf</code>

<code>server-</code><code>id</code><code>=192</code>

<code>[root@ecs-89c1 mysql]</code><code># /etc/init.d/mysqld restart</code>

<code>Shutting down MySQL.. SUCCESS! </code>

3、复制主设备数据库备份文件

<code>[root@ecs-89c1 mysql]</code><code># scp 122.112.253.88:/tmp/*.sql /tmp/</code>

<code>The authenticity of host </code><code>'122.112.253.88 (122.112.253.88)'</code> <code>can't be established.</code>

<code>ECDSA key fingerprint is 2e:6e:90:32:87:05:9e:63:63:d6:2d:44:a5:5f:be:51.</code>

<code>Are you sure you want to </code><code>continue</code> <code>connecting (</code><code>yes</code><code>/no</code><code>)? </code><code>yes</code>

<code>Warning: Permanently added </code><code>'122.112.253.88'</code> <code>(ECDSA) to the list of known hosts.</code>

<code>[email protected]'s password: </code>

<code>zrlog.sql 100% 9878 9.7KB</code><code>/s</code> <code>00:00</code>

4、创建对应数据库

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot</code>

<code>Your MySQL connection </code><code>id</code> <code>is 1</code>

<code>Server version: 5.6.35 MySQL Community Server (GPL)</code>

<code>mysql> create database db01;</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> create database zrlog;</code>

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot db01 </tmp/db01.sql</code>

<code>[root@ecs-89c1 mysql]</code><code># /usr/local/mysql/bin/mysql -uroot zrlog </tmp/zrlog.sql</code>

5、配置主备同步

<code>Your MySQL connection </code><code>id</code> <code>is 4</code>

<code>mysql> stop slave;</code>

<code>Query OK, 0 rows affected, 1 warning (0.00 sec)</code>

<code>mysql> change master to master_host=</code><code>'122.112.253.88'</code><code>,master_user=</code><code>'repl'</code><code>,master_password=</code><code>'123456'</code><code>,master_log_file=</code><code>'bin01.000001'</code><code>,master_log_pos=10472;</code>

<code>Query OK, 0 rows affected, 2 warnings (0.03 sec)</code>

<code>mysql> start slave;</code>

6、查看主从状态

<code>mysql> show slave status\G; </code><code>##如果出现错误，可能是云主机策略没放过3306端口</code>

<code> </code><code>Slave_IO_State: Waiting </code><code>for</code> <code>master to send event</code>

<code> </code><code>Master_Host: 122.112.253.88</code>

<code> </code><code>Master_User: repl</code>

<code> </code><code>Master_Port: 3306</code>

<code> </code><code>Connect_Retry: 60</code>

<code> </code><code>Master_Log_File: bin01.000001</code>

<code> </code><code>Read_Master_Log_Pos: 10708</code>

<code> </code><code>Relay_Log_File: ecs-89c1-relay-bin.000003</code>

<code> </code><code>Relay_Log_Pos: 515</code>

<code> </code><code>Relay_Master_Log_File: bin01.000001</code>

<code> </code><code>Slave_IO_Running: Yes</code>

<code> </code><code>Slave_SQL_Running: Yes</code>

<code> </code><code>Replicate_Do_DB: </code>

<code> </code><code>Replicate_Ignore_DB: </code>

<code> </code><code>Replicate_Do_Table: </code>

<code> </code><code>Replicate_Ignore_Table: </code>

<code> </code><code>Replicate_Wild_Do_Table: </code>

<code> </code><code>Replicate_Wild_Ignore_Table: </code>

<code> </code><code>Last_Errno: 0</code>

<code> </code><code>Last_Error: </code>

<code> </code><code>Skip_Counter: 0</code>

<code> </code><code>Exec_Master_Log_Pos: 10708</code>

<code> </code><code>Relay_Log_Space: 691</code>

<code> </code><code>Until_Condition: None</code>

<code> </code><code>Until_Log_File: </code>

<code> </code><code>Until_Log_Pos: 0</code>

<code> </code><code>Master_SSL_Allowed: No</code>

<code> </code><code>Master_SSL_CA_File: </code>

<code> </code><code>Master_SSL_CA_Path: </code>

<code> </code><code>Master_SSL_Cert: </code>

<code> </code><code>Master_SSL_Cipher: </code>

<code> </code><code>Master_SSL_Key: </code>

<code> </code><code>Seconds_Behind_Master: 0</code>

<code>Master_SSL_Verify_Server_Cert: No</code>

<code> </code><code>Last_IO_Errno: 0</code>

<code> </code><code>Last_IO_Error: </code>

<code> </code><code>Last_SQL_Errno: 0</code>

<code> </code><code>Last_SQL_Error: </code>

<code> </code><code>Replicate_Ignore_Server_Ids: </code>

<code> </code><code>Master_Server_Id: 88</code>

<code> </code><code>Master_UUID: 79b66469-8cc8-11e7-ae36-fa163eb51d35</code>

<code> </code><code>Master_Info_File: </code><code>/data/mysql/master</code><code>.info</code>

<code> </code><code>SQL_Delay: 0</code>

<code> </code><code>SQL_Remaining_Delay: NULL</code>

<code> </code><code>Slave_SQL_Running_State: Slave has </code><code>read</code> <code>all relay log; waiting </code><code>for</code> <code>the slave I</code><code>/O</code> <code>thread to update it</code>

<code> </code><code>Master_Retry_Count: 86400</code>

<code> </code><code>Master_Bind: </code>

<code> </code><code>Last_IO_Error_Timestamp: </code>

<code> </code><code>Last_SQL_Error_Timestamp: </code>

<code> </code><code>Master_SSL_Crl: </code>

<code> </code><code>Master_SSL_Crlpath: </code>

<code> </code><code>Retrieved_Gtid_Set: </code>

<code> </code><code>Executed_Gtid_Set: </code>

<code> </code><code>Auto_Position: 0</code>

<code>ERROR: </code>

<code>No query specified</code>

7、主设备解锁

<code>Your MySQL connection </code><code>id</code> <code>is 875</code>

<code>mysql> unlock tables;</code>

<code>Query OK, 0 rows affected (0.00 sec)</code>

测试主从同步

1、在主设备上删除db01数据库的表；

<code>mysql> show tables;</code>

<code>| Tables_in_db01 |</code>

<code>| comment |</code>

<code>| lognav |</code>

<code>| plugin |</code>

<code>mysql> drop table tag;</code>

2、在从设备查看对应的表也已经不存在了。

扩展学习

▎配置参数

1. 主服务器上：

binlog-do-db= //仅同步指定的库(其他库不同步)

binlog-ignore-db= //忽略指定库(其他库都同步)

2. 从服务器上：

replicate_do_db= //(不常用)

replicate_ignore_db= //(不常用)

replicate_do_table= //(不常用)

replicate_ignore_table= //(不常用)

replicate_wild_do_table= //如aming.%, (支持通配符%)

replicate_wild_ignore_table=

本文转自Grodd51CTO博客，原文链接：http://blog.51cto.com/juispan/1961233，如需转载请自行联系原作者

MySQL主从架构

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述