ubuntu16 MySQL5.7主从配置

2019-11-06 本文已影响0人二棉酷

1.准备两台虚拟机(192.168.0.197[M],192.168.0.96[S])

2.两台mysql服务器安装同一版本mysql数据库

sudo apt-get install mysql-client mysql-server

连接Navicat工具时可能会出现连接不上的情况:

//登录MySQL后执行
mysql> grant all privileges on *.* to root@"%" identified by "root";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> exit
//执行 grep -R address *
root@dfyt:/etc/mysql# grep -R address *
mysql.conf.d/mysqld.cnf:bind-address= 127.0.0.1
//打开配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
//将下面的注释,可能是出现ip绑定的情况
#bind-address           = 127.0.0.1
//执行 grep -R address *查看
root@dfyt:/etc/mysql# grep -R address *
mysql.conf.d/mysqld.cnf:#bind-address= 127.0.0.1

重启OK

sudo /etc/init.d/mysql restart

3.分别在两个数据库中创建同样的库和表例如 test

首先配置主服务器**197

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加配置

server-id = 197      //id唯一,用服务器ip后缀方可
log-bin=mysql-bin    //必须开启二进制
binlog_do_db = test  //要同步的数据库

5.重启主数据库

sudo /etc/init.d/mysql restart

6.登录主mysql服务器, 并在mysql控制台下输入授权主从模式的命令(此处使用的是root用户, 授权192.168.0.196使用test库, 凭用户密码登录, 可以授权’%’)

mysql> grant replication slave, replication client on *.* to 'root'@'192.168.0.196' identified by 'root';
mysql> grant all on test.* to root@'192.168.0.196' identified by 'root';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | test | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从服务器***196

7.修改从mysql配置文件, 支持主从模式, 主要配置如下几个参数(binlog_do_db记录了需要做主从同步的数据库名称):

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加配置

server-id = 196
log-bin=mysql-bin
binlog_do_db = test

8.登录从mysql服务器, 并在mysql控制台下输入匹配主从模式的命令(此处使用的是root用户, 将当前数据库作为从库匹配到主数据库192.168.0.197的mysql服务上)

mysql> change master to master_host='192.168.0.197', master_user='root', master_password='root', master_log_file='mysql-bin.000004', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.197
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: dfyt-virtual-machine-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 542
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 197
                  Master_UUID: 6a92eedc-ffa0-11e9-8c17-000c29d78b03
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Slave_IO_Running及 Slave_SQL_Running状态必须为YES。

执行时可能会遇见类似错误
change master to master_host='192.168.0.197', master_user='root', master_password='root', master_log_file='mysql-bin.000004', master_log_pos=154;

ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

解决方案
1.进入slave服务器mysql执行命令：STOP slave;
2.在执行上面CHANGE MASTER命令
3.成功后再执行命令：START slave;

最后重启服务器

[Mysql主从原理]

1.1 mysql主从同步

1、mysql主从同步（复制）概念

将Mysql某一台主机数据复制到其它主机（slaves）上，并重新执行一遍来实现的。
复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。
当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。
从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

·binlog：**是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)

2、Mysql支持哪些复制

1. 基于语句的复制: 在主服务器执行SQL语句，在从服务器执行同样语句。

注：MySQL默认采用基于语句的复制，效率较高。一旦发现没法精确复制时, 会自动选基于行的复制。

2. 基于行的复制: 把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

3、Mysql主从复制原理

master服务器将数据的改变都记录到二进制binlog日志中，只要master上的数据发生改变，则将其改变写入二进制日志；
salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件
同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中
从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致
最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒.

需要理解：

1）从库会生成两个线程,一个I/O线程,一个SQL线程;

2）I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;*

3）主库会生成一个log dump线程,用来给从库I/O线程传binlog;

4）SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

4、Mysql复制流程图

master将操作语句记录到binlog日志中
salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变
salave开启两个线程：IO线程和SQL线程

1）IO线程：负责读取master的binlog内容到中继日志relay log里；

2）SQL线程：负责从relay log日志里读出binlog内容，并更新到slave的数据库里（保证数据一致）
image

1.2 MySQL同步延迟问题

1、造成mysql同步延迟常见原因

1）网络：如主机或者从机的带宽打满、主从之间网络延迟很大，导致主上的binlog没有全量传输到从机，造成延迟。

2）机器性能：从机使用了烂机器？比如主机使用SSD而从机还是使用的SATA

3）从机高负载：有很多业务会在从机上做统计，把从机服务器搞成高负载，从而造成从机延迟很大的情况

4）大事务：比如在RBR模式下，执行带有大量的delete操作，这种通过查看processlist相关信息以及使用mysqlbinlog查看binlog中的SQL就能快速进行确认

5）锁: 锁冲突问题也可能导致从机的SQL线程执行慢，比如从机上有一些select .... for update的SQL，或者使用了MyISAM引擎等。

2、硬件方面（优化）

1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。

2.存储用ssd或者盘阵或者san，提升随机写的性能。

3.主从间保证处在同一个交换机下面，并且是万兆环境。

总结**：硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

3、mysql主从同步加速

1）sync_binlog在slave端设置为0

当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘

而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

2）slave端 innodb_flush_log_at_trx_commit = 2

每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。

该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

3）–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

4）直接禁用slave端的binlog

ubuntu16 MySQL5.7主从配置

1.准备两台虚拟机(192.168.0.197[M],192.168.0.96[S])

2.两台mysql服务器安装同一版本mysql数据库

3.分别在两个数据库中创建同样的库和表例如 test

首先配置主服务器**197

5.重启主数据库

6.登录主mysql服务器, 并在mysql控制台下输入授权主从模式的命令(此处使用的是root用户, 授权192.168.0.196使用test库, 凭用户密码登录, 可以授权’%’)

配置从服务器***196

7.修改从mysql配置文件, 支持主从模式, 主要配置如下几个参数(binlog_do_db记录了需要做主从同步的数据库名称):

8.登录从mysql服务器, 并在mysql控制台下输入匹配主从模式的命令(此处使用的是root用户, 将当前数据库作为从库匹配到主数据库192.168.0.197的mysql服务上)

Slave_IO_Running及 Slave_SQL_Running状态必须为YES。

最后重启服务器

[Mysql主从原理]

1.1 mysql主从同步

1、mysql主从同步（复制）概念

2、Mysql支持哪些复制

3、Mysql主从复制原理

4、Mysql复制流程图

1.2 MySQL同步延迟问题

1、造成mysql同步延迟常见原因

2、硬件方面（优化）

3、mysql主从同步加速

猜你喜欢

热点阅读

ubuntu16 MySQL5.7主从配置

1.准备两台虚拟机(192.168.0.197[M],192.168.0.96[S])

2.两台mysql服务器安装同一版本mysql数据库

3.分别在两个数据库中创建同样的库和表 例如 test

首先配置主服务器**197

5.重启主数据库

6.登录主mysql服务器, 并在mysql控制台下输入授权主从模式的命令(此处使用的是root用户, 授权192.168.0.196使用test库, 凭用户密码登录, 可以授权’%’)

配置从服务器***196

7.修改从mysql配置文件, 支持主从模式, 主要配置如下几个参数(binlog_do_db记录了需要做主从同步的数据库名称):

8.登录从mysql服务器, 并在mysql控制台下输入匹配主从模式的命令(此处使用的是root用户, 将当前数据库作为从库匹配到主数据库192.168.0.197的mysql服务上)

Slave_IO_Running及 Slave_SQL_Running状态必须为YES。

最后重启服务器

[Mysql主从原理]

1.1 mysql主从同步

1、mysql主从同步（复制）概念

2、Mysql支持哪些复制

3、Mysql主从复制原理

4、Mysql复制流程图

1.2 MySQL同步延迟问题

1、造成mysql同步延迟常见原因

2、硬件方面（优化）

3、mysql主从同步加速

猜你喜欢

热点阅读

3.分别在两个数据库中创建同样的库和表例如 test