mysql主从复制与基于GTID主从复制

最新推荐文章于 2024-06-28 16:47:53 发布

love_sunshine_999

最新推荐文章于 2024-06-28 16:47:53 发布

阅读量2.2k

点赞数 1

本文链接：https://blog.csdn.net/love_sunshine_999/article/details/81570987

版权

mysql的主从复制与基于GTID主从复制

概述：
主从复制用途以及条件：

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从复制的必要条件：

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连同主库

主从复制原理：

从库生成两个线程，一个i/o线程，一个SQL线程；

i/o线程去请求主库的binlog，并且得到的binlog日志写道relay log(中继日志)文件中，

主库会生成一个log dump线程，用来给从库的i/o线程传binlog；

SQL线程，会读取中继日志文件，并解析成具体的操作执行，这样主从的操作就一致了，而最终的数据也就一直了。

主从复制存在的问题以及解决办法
问题：

主库宕机之后，数据可能会丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

半同步复制解决--解决数据丢失的问题
并行复制--解决从库复制延时的问题

1.数据库同步是怎样进行的?
master用户写入数据，生成event记到binary log中.
slave接收master上传来的binlog，然后按顺序应用，重现master上的用户操作。
2.数据库是靠什么同步的?
主从复制,默认是通过pos复制(postion),就是说在日志文档里,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号,我们在配置主从复制时从节点时,要输入master的log_pos值就是这个原因,要求它从哪个pos开始同步数据库里的数据,这也是传统复制技术,
MySQL5.6增加了GTID复制,GTID就是类似于pos的一个作用,不过它是整个mysql复制架构全局通用的,就是说在这整个mysql冗余架构中,它们的日志文件里事件的GTID值是一致的.
3.从节点怎么知道要从哪块进行同步?
上面也说了,一开始是自己设置的从节点从主节点的日志文件里的pos开始复制,以后就自己去读取上一次同步到哪一块,接着同步.
4:pos与GTID有什么区别?
两者都是日志文件里事件的一个标志,如果将整个mysql集群看作一个整体,pos就是局部的,GTID就是全局的.

安装包：

  mysql-community-client-5.7.17-1.el6.x86_64.rpm    
  mysql-community-common-5.7.17-1.el6.x86_64.rpm      
  mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm 
  mysql-community-server-5.7.17-1.el6.x86_64.rpm

实验环境：

主数据库 ：  server1                
从数据库 ：  server2

1.Mysql主从复制

概述：

mysql的主从配置又叫replication，AB复制，基于binlog二进制日志，主数据库必须开启binlog二进制日志才能进行复制。 
主从复制大致有3个步骤： 
1，主数据库将更改操作记录到binlog二进制日志（主数据库有log dump线程和从数据库的I/O线程传递binlog）。 
2.从数据库含有两个线程，I/O线程将主数据库的binlog同步到本机并记录在relaylog日志 
3.sql线程将relaylog日志记录进行操作落地 
作为异步复制，其主库将事件写入binlog二进制文件，dump线程将binlog文件发送出去，不保证其他从节点是否会收到binlog二进制文件。

实验：
在主数据库中（server1）：

[root@server1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@server1 ~]# yum install * -y
##打开数据库 
[root@server1 ~]# /etc/init.d/mysqld start
Initializing MySQL database:  
                                                            [  OK  ]
Installing validate password plugin:                        [  OK  ]
Starting mysqld:                                            [  OK  ]
 ##查看密码
[root@server1 ~]# grep password /var/log/mysqld.log

这里写图片描述

注意：如果数据库的启动过于缓慢则执行：
[root@server1 ~]# ps
  PID TTY          TIME CMD
 1199 pts/0    00:00:00 bash
 1246 pts/0    00:00:00 mysqld
 1356 pts/0    00:00:00 mysqld
 1386 pts/0    00:00:00 sleep
 1387 pts/0    00:00:00 ps
 ##强制杀死mysqld进程
[root@server1 ~]# kill -9 1246 
[1]-  Killed                  /etc/init.d/mysqld start
[root@server1 ~]# kill -9 1356
[root@server1 ~]# ps
  PID TTY          TIME CMD
 1199 pts/0    00:00:00 bash
 1388 pts/0    00:00:00 ps
[2]+  Killed                  /etc/init.d/mysqld stop
[root@server1 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]

##修改密码
注意：密码需要有数字 大小写字母 特殊字符 三者构成 缺一不可！ 密码位数大于8
（1）直接修改密码：
[root@server1 ~]#  mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> ALTER USER root@localhost identified by 'HYM*hyf5532';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
（2）初始化修改密码：
[root@server4 ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 
 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
##编写配置文件
[root@server1 ~]# vim /etc/my.cnf
#################
##30和31 行  可写可不写
在3个节点中设置的server-id不可以相同 否则最后出现IO错误

这里写图片描述

[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##创建用户并授权
mysql> grant replication slave on *.* to hostname@'172.25.254.%' identified by 'HYM*hyf5532';
Query OK, 0 rows affected, 1 warning (0.00 sec)

##查看二进制日志是否打开
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |
| log_bin_basename                       | /var/lib/mysql/mysql-bin       |
| log_bin_index                          | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysqld.log            |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | OFF                            |
| log_slave_updates                      | OFF                            |
| log_slow_admin_statements              | OFF                            |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)
##查看主库的状态
mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      625 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

参数解释： 
replication       ###表示授权复制的权限
 *.*              ###表示所有数据库可以进行同步
 hostname         ###表示授权名，可以随意填写
'172.25.254.%'    ###表示授权172.25.254.0/24的网段所有服务器可以同步， %表示任意，

注意：在server1中创建用户并授权后需要在server2中检测：

##能够登陆视为授权成功
[root@server2 ~]# mysql -u hostname -p -h 172.25.254.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> quit
Bye

在从数据库中（server2）：

[root@server2 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
##安装数据库
[root@server2 ~]# yum install * -y
##打开数据库 
[root@server2 ~]# /etc/init.d/mysqld start
Initializing MySQL database:  
                                                        [  OK  ]
Installing validate password plugin:                    [  OK  ]
Starting mysqld:                                        [  OK  ]
##查看密码
[root@server2 ~]# grep password /var/log/mysqld.log

这里写图片描述

##修改密码 为了方便将server1和server2的密码设为一样
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##设置密码
mysql> ALTER USER root@localhost identified by 'HYM*hyf5532';
Query OK, 0 rows affected (0.15 sec)

mysql> quit
Bye
##编写配置文件
[root@server2 ~]# vim /etc/my.cnf

这里写图片描述

[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##注意：参数要与主数据库的相对应
mysql> change master to master_host='172.25.254.1',master_user='hostname',master_password='HYM*hyf5532',master_log_file='mysql-bin.000002',master_log_pos=625;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

##如果添加错了 想要重新添加 必须先执行：stop slave 再重新添加

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

##注意 \G后边不要加;
mysql> show slave status\G

##当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes，
##则表示slave库已经正常运行了

这里写图片描述
测试：

在server1中：
##创建test数据库 并在test数据库中创建userlist表 
并在该表中插入数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test ;
Query OK, 1 row affected (0.35 sec)

mysql> use test;
Database changed
mysql> create table userlist (
    -> username varchar(20) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (1.07 sec)

mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into userlist values ('user1','111');
Query OK, 1 row affected (0.07 sec)

mysql> insert into userlist values ('user2','222');
Query OK, 1 row affected (0.09 sec)

在server2中检测：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
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 userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

在server1中创建的数据 同步到了server2 即实现了数据库的主从复制

2.基于GDIT主从复制

概述：

mysql数据库从5.6.5开始新增一种基于GDIT的复制方式。通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性，故障恢复以及容错能力。 
GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。
TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

实验：

在server1中：
[root@server1 ~]# vim /etc/my.cnf

这里写图片描述

[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
## Position 一直在变更
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在server2中：

[root@server1 ~]# vim /etc/my.cnf

这里写图片描述

[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
##由于pos号一直在变更 故设定MASTER_AUTO_POSITION=1
mysql> change master to master_host='172.25.254.1',master_user='hostname',master_password='HYM*hyf5532',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.48 sec)

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

mysql> show slave status\G

这里写图片描述

测试：

在server1中：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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  userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)
##删除用户
mysql> delete from userlist where username='user1';
Query OK, 1 row affected (0.07 sec)
在server2中：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
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 userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 222      |
+----------+----------+

love_sunshine_999

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
mysql主从复制与基于GTID主从复制

安装包： mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el...
复制链接

扫一扫