mysql主从同步

非常感谢大家昨晚来到我们的公开课课堂，一起探讨了关于数据库的架构，最后详细探讨学习了mysql主从同步的搭建过程。

今天分享一下昨晚的mysql数据库主从搭建的课堂笔记，让更多的人因为这次课程受益，另外我们每隔一段时间都会有相应的互动课程，大家可以前往https://ke.qq.com/course/254420#tuin=b66303d9 腾讯课堂链接报名参加。

以下是本次课程的课堂笔记，主题聊聊数据库架构那些事

首先分享了同方，从14年到17年的数据库架构，接下来是关于数据库主从的搭建

原理图如下：

mysql主从复制中：

第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。 第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。 第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。 此外，在master中也有一个工作线程和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

详细过程：

模式： C/S 模式

端口：

3306

x-63 主mysql服务器配置

创建要同步的数据库：

mysql> create database bawei;

mysql> use bawei;

mysql> create table test1 (id int);

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| bawei |

| mysql |

| test |

停止

#service mysqld stop

编辑

#vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0 #在原配置文件中，添加以下内容：

log-bin=mysqllog #启用二进制日志，默认存在/var/lib/mysql 下面

server-id=1 #本机数据库ID 标示。其中master_id必须为1到232之间的一个正整数值

binlog-do-db=bawei #可以被从服务器复制的库。二进制需要同步的数据库名

# binlog-ignore-db=bawei2 不可以被从服务器复制的库

重新启动

#service mysqld restart

授权

mysql> grant replication slave on *.* to slave@192.168.1.64 identified by "123456";

查看状态，信息

mysql> show master status;

+-----------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-----------------+----------+--------------+------------------+

| mysqllog.000001 | 242 | bawei | |

+-----------------+----------+--------------+------------------+

1 row in set (0.00 sec)

查看二进制日志位置：

[root@x-63 ~]# ls /var/lib/mysql/

ibdata1 ib_logfile1 mysql mysqllog.index passwd

ib_logfile0 bawei mysqllog.000001 mysql.sock test

mysql>show binlog events \G

复制前保证两个数据库数据一致：

mysqldump -u root -p123456 -A >all.sql

Enter password:

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

把数据传给从：

scp all.sql 192.168.1.64:/root

配置从服务器:

mysql从服务器: x-64

[root@x-64 opt]# yum install mysql-server -y

[root@x-64 opt]# service mysqld start

mysql>show variables like '%version%';查看版本

+-------------------------+---------------------+

| Variable_name | Value |

+-------------------------+---------------------+

| protocol_version | 10 |

| version | 5.1.52 |

| version_comment | Source distribution |

| version_compile_machine | x86_64 |

| version_compile_os | redhat-linux-gnu |

+-------------------------+---------------------+

5 rows in set (0.03 sec)

测试连接到主服务器是否成功

[root@x-64 opt]# mysql -u slave -h 192.168.1.63 -p123456

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

+--------------------+

看不到bawei数据库

导入数据库，和主服务器保持一致

[root@x-64 ~]# mysql -u root -p < all.sql

从服务器没必要开bin-log日志注。

修改从服务器配置文件：

[root@x-64 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#在配置文件中写入以下内容

server-id=2 #从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

master-host=192.168.1.63 #指定主服务器IP地址

master-user=slave #制定在主服务器上可以进行同步的用户名

master-password=123456 #密码

####以下可以不写

master-port = 3306 #同步所用的端口

master-connect-retry=60 #断点重新连接时间

保存，重启

#service mysqld restart

如果只做为备库，就只设置server-ID， 如果他也作为一个分发主库，开启bin-log和中继日志

测试：

主服务器上查看：

mysql> show master status;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: bawei1

+-----------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-----------------+----------+--------------+------------------+

| mysqllog.000004 | 106 | bawei1 | |

+-----------------+----------+--------------+------------------+

从服务器上查看：

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.63

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqllog.000001

Read_Master_Log_Pos: 315

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 459

Relay_Master_Log_File: mysqllog.000001

Slave_IO_Running: Yes #可以看到这两个Yes，说明从服务器安装成功。

Slave_SQL_Running: Yes

Slave_IO_Running ：一个负责与主机的io通信

Slave_SQL_Running：负责自己的slave mysql进程

mysql> START SLAVE

测试：数据同步

x-63写数据：

mysql> use bawei;

Database changed

mysql> show tables;

+--------------+

| Tables_in_bawei |

+--------------+

| test1 |

+--------------+

1 row in set (0.00 sec)

mysql> insert into test1 values(1);

x-64读数据：

mysql> use bawei;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test1;

+------+

| id |

+------+

| 1 |

+------+

到现在为止mysql主从同步就搭建完成了，因为篇幅限制，后面会出现的问题解决方案暂时就不在这里阐述了，如果有兴趣的可以到公开课上一起探讨。