ubuntu下数据库主从配置

今天说一下MySQL的主从复制如何做到！

- 准备工作：

1.两个虚拟机：我这里用的是CentOS5.5，IP地址分别是192.168.1.101 和192.168.1.105；

　　　　　　　101做主服务器，105做从服务器（都已经安装相同版本的Mysql）；

2.本机环境：nginx+PHP+MySQL

好了，现在开始吧，来看看这听起来高大上的主从复制是怎么回事。

原理：mysql要做到主从复制，其实依靠的是二进制日志，即：假设主服务器叫A，从服务器叫B；主从复制就是

　　 B跟着A学，A做什么，B就做什么。那么B怎么同步A的动作呢？现在A有一个日志功能，把自己所做的增删改查的动作

　　 全都记录在日志中，B只需要拿到这份日志，照着日志上面的动作施加到自己身上就可以了。这样就实现了主从复制。

扩展：MYSQL还有一种日志叫做：慢日志

　　 可以设置一个时间，那么所有执行时间超过这个时间的SQL都会被记录下来。这样就可以通过慢日志快速的找到网站中SQL的瓶颈来进行优化。

     大家有时间可以研究一下，这里不多做介绍。

• 实现步骤：

1.首先修改mysql的配置文件，使其支持二进制日志功能。

打开主服务器的mysql配置文件：my.conf

代码：# vi /etc/my.cnf

加入如下三行代码：

vi /etc/my.cnf-----log-bin=mysql-binbinlog_format=mixedserver-id=101------

• 参数解释：

log-bin=mysql-bin //将mysql二进制日志取名为mysql-bin

　　　　 binlog_format=mixed //二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed

　　　　 server-id=101 //为服务器设置一个独一无二的id便于区分，这里使用ip地址的最后一位充当server-id

配置完成，:wq 保存，重启mysql

重启mysql命令：#

• service mysqld restart
• 从服务器的配置(有几台,配置几台)

同样的，进入从服务器，配置从服务器的my.cnf,重复步骤1即可，

唯一的区别是，server-id要改成从服务器的ip尾位(只要不一样就ok,但是业界喜欢将ip尾位作为id)，即server-id=105;其他两项是一样的，保存，并重启mySQL；

service mysqld restart

2.在主服务器上为从服务器分配一个账号，就像一把钥匙，从服务器拿着这个钥匙，才能到主服务器上来共享主服务器的日志文件。

进入主服务器的mysql界面，

命令： # mysql -u root -p 111111 //我这里mysql账号是root，密码是111111

在mysql操作界面下，输入下面一行命令：

GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '111111';

刷新

 FLUSH PRIVILEGES;

3.查看主服务器BIN日志的信息（执行完之后记录下这两值，然后在配置完从服务器之前不要对主服务器进行任何操作，因为每次操作数据库时这两值会发生改变）.

• 设置从服务器

进入从服务器mysql

命令： # mysql -u root -p111111

关闭slave（如果你以前配置过主从的话，一定要先关闭）

stop slave;

开始配置：

输入下面代码即可：

• 参数解释：

          MASTER_HOST  :  设置要连接的主服务器的ip地址

  　　　　 MASTER_USER : 设置要连接的主服务器的用户名

  　　　　 MASTER_PASSWORD : 设置要连接的主服务器的密码

  　　　　MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称，即第3步得到的信息

  　　　　MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置，即第3步得到的信息，（这里注意，最后一项不需要加引号。否则配置失败）

  示例:

CHANGE MASTER TOMASTER_HOST  ="10.0.0.128",MASTER_USER  ="slave",MASTER_PASSWORD  ="111111",MASTER_LOG_FILE  ="mysql-bin.000001",MASTER_LOG_POS  = 590;

先在从服务器配置完成，启动从服务器：

命令：

start slave;

5.查看是否配置成功：

命令： show slave status;

上面两项均为yes，说明配置成功，否则，请重复前面的步骤。(或者评论问我,我也是摸索了很久之后才知道的,可能写的不太详细)

• 优化

 主库  set global binlog_group_commit_sync_delay=10; 从库配置文件添加 slave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=4或者 在线添加 stop slave; set global slave_parallel_type='LOGICAL_CLOCK';  set global slave_parallel_workers=4;  start slave;   show variables like 'slave_parallel_%'; 从库配置文件添加 在 MySQL 5.7 中，推荐将 master-info-repository 和 relay-log-info-repository 设置为 TABLE ，来减小这部分的开销。 master-info-repository = tablerelay-log-info-repository = tablerelay-log-recovery = ON`

• 参数解释

  启用MySQL并行复制

MySQL 5.7的并行复制建立在组提交的基础上，所有在主库上能够完成 Prepared 的语句表示没有数据冲突，就可以在 Slave 节点并行复制。

关于 MySQL 5.7 的组提交，我们要看下以下的参数：

mysql> show global variables like '%group_commit%';+-----------------------------------------+-------+| Variable_name                           | Value |+-----------------------------------------+-------+| binlog_group_commit_sync_delay          | 0     || binlog_group_commit_sync_no_delay_count | 0     |+-----------------------------------------+-------+

2 rows in set (0.00 sec)
要开启 MySQL 5.7 并行复制需要以下二步，首先在主库设置 binlog_group_commit_sync_delay 的值大于0 。

mysql> set global binlog_group_commit_sync_delay=10;

这里简要说明下 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数的作用。

binlog_group_commit_sync_delay
全局动态变量，单位微妙，默认0，范围：0～1000000（1秒）。

表示 binlog 提交后等待延迟多少时间再同步到磁盘，默认0 ，不延迟。当设置为 0 以上的时候，就允许多个事务的日志同时一起提交，也就是我们说的组提交。组提交是并行复制的基础，我们设置这个值的大于 0 就代表打开了组提交的功能。

binlog_group_commit_sync_no_delay_count
全局动态变量，单位个数，默认0，范围：0～1000000。

表示等待延迟提交的最大事务数，如果上面参数的时间没到，但事务数到了，则直接同步到磁盘。若 binlog_group_commit_sync_delay 没有开启，则该参数也不会开启。

其次要在 Slave 主机上设置如下几个参数：

过多的线程会增加线程间同步的开销，建议4-8个Slave线程。

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
或者直接在线启用也是可以的：

mysql> stop slave;Query OK, 0 rows affected (0.07 sec)mysql> set global slave_parallel_type='LOGICAL_CLOCK';Query OK, 0 rows affected (0.00 sec)mysql> set global slave_parallel_workers=4;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.06 sec)mysql> show variables like 'slave_parallel_%';+------------------------+---------------+| Variable_name          | Value         |+------------------------+---------------+| slave_parallel_type    | LOGICAL_CLOCK || slave_parallel_workers | 4             |+------------------------+---------------+

2 rows in set (0.00 sec)
检查Worker线程的状态

当前的 Slave 的 SQL 线程为 Coordinator（协调器），执行 Relay log 日志的线程为 Worker(当前的 SQL 线程不仅起到协调器的作用，同时也可以重放 Relay log 中主库提交的事务)。

我们上面设置的线程数是 4 ，从库就能看到 4 个 Coordinator（协调器）进程。

并行复制配置与调优

开启 MTS 功能后，务必将参数 master-info-repository 设置为 TABLE ，这样性能可以有 50%~80% 的提升。这是因为并行复制开启后对于 master.info 这个文件的更新将会大幅提升，资源的竞争也会变大。

在 MySQL 5.7 中，推荐将 master-info-repository 和 relay-log-info-repository 设置为 TABLE ，来减小这部分的开销。

配置文件添加master-info-repository = tablerelay-log-info-repository = tablerelay-log-recovery = ON

并行复制监控

复制的监控依旧可以通过 SHOW SLAVE STATUS\G，但是 MySQL 5.7 在 performance_schema 架构下多了以下这些元数据表，用户可以更细力度的进行监控：

mysql> use performance_schema;mysql> show tables like 'replication%';+---------------------------------------------+| Tables_in_performance_schema (replication%) |+---------------------------------------------+| replication_applier_configuration           || replication_applier_status                  || replication_applier_status_by_coordinator   || replication_applier_status_by_worker        || replication_connection_configuration        || replication_connection_status               || replication_group_member_stats              || replication_group_members                   |+---------------------------------------------+8 rows in set (0.00 sec)

注意

• 如果是新机器,可以完全按照这个来
• 如果不是新机器,stop slave之后可能还需要 reset slave; 然后再开启slave