实验环境:
主:172.25.31.1 server1 master
从:172.25.31.2 server2 slave
mysql的主从复制
复制原理:
Mysql 中有一种日志叫做 bin 日志(二进制日志)。这个日志会记录下所有修改了数据库的 SQL
语句(insert,update,delete,ALTER TABLE,grant 等等)。
主从复制的原理其实就是把主服务器上的BIN日志复制到从服务器上执行一遍,这样从服务器
上的数据就和主服务器上的数据相同了。
复制过程:
❖主节点必须启用二进制日志,记录任何修改数据库数据的事件。 ❖从节点开启一个线程(I/O Thread)把自己扮演成 mysql
的客户端,通过 mysql 协议,请 求主节点的二进制日志文件中的事件 ❖主节点启动一个线程(dump
Thread),检查自己二进制日志中的事件,跟对方请求的位 置对比,如果不带请求位置参数,则主节点就会从第一个日志文件中的第一个事件一个一
个发送给从节点。 ❖从节点接收到主节点发送过来的数据把它放置到中继日志(Relay log)文件中。并记录该
次请求到主节点的具哪个二进制日志文件的哪个位置。 ❖从节点启动另外一个线程(sql Thread ),把 replaylog
中的事件读取出来,并在本地 再执行一次。
安装 mysql
主从都安装
修改配置文件
主:
vim /etc/my.cnf
添加
log-bin= mysql-bin #启用二进制日志
server-id=1 #mysql 的同步的数据中是包含 server-id 的,用于标识该语句最初是从哪个server 写入的,所以 server-id 一定要有的,一般就写, ip 地址的最后一位,比如 172.25.31.1,就写 1
从:
vim /etc/my.cnf
添加
server-id=2
在主从节点都启动数据库
/etc/init.d/mysqld start
查看数据库密码
cat /var/log/mysqld.log | grep password
2018-08-08T08:42:02.027811Z 1 [Note] A temporary password is generated for root@localhost: zo&kc2Cu?DNI
2018-08-08T08:42:37.333590Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.DInZEo.sql' started.
2018-08-08T08:42:37.547752Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.DInZEo.sql' ended.
2018-08-08T08:42:39.515391Z 0 [Note] Shutting down plugin 'sha256_password'
2018-08-08T08:42:39.515399Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-08-08T08:42:43.099069Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
进行安全配置向导
mysql_secure_installation
主：
创建帐号（slave来复制时用的身份）
[root@server1 ~]# mysql -uroot -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 'repl'@'%' identified by 'Cqmyg+666';
Query OK, 0 rows affected, 1 warning (0.31 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1492 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从：
登录操作
[root@server2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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> CHANGE MASTER TO MASTER_HOST='172.25.31.1', MASTER_USER='backup',master_password='redhat', master_log_file='mysql-bin.000007',master_log_pos=1492;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.31.1
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1492
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000007
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: 1492
Relay_Log_Space: 529
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: 1
Master_UUID: 15f762eb-9a3f-11e8-aa03-5254000e8178
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)
ERROR:
No query specified
测试
主：
[root@server1 ~]# mysql -uroot -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> create database wyh;
Query OK, 1 row affected (0.05 sec)
mysql> use wyh;
Database changed
mysql> create table wyh(id int(3),name char(10));
Query OK, 0 rows affected (0.90 sec)
mysql> select * from wyh;
Empty set (0.00 sec)
mysql> insert into wyh values(001,"user1");
Query OK, 1 row affected (0.09 sec)
mysql> select * from wyh;
+------+-------+
| id | name |
+------+-------+
| 1 | user1 |
+------+-------+
1 row in set (0.00 sec)
从：
直接查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wyh |
+--------------------+
5 rows in set (0.00 sec)
mysql> use wyh;
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> how tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'how tables' at line 1
mysql> show tables;
+---------------+
| Tables_in_wyh |
+---------------+
| wyh |
+---------------+
1 row in set (0.00 sec)
mysql> select * from wyh;
+------+-------+
| id | name |
+------+-------+
| 1 | user1 |
+------+-------+
1 row in set (0.00 sec)
Gtid
GTID(Global Transaction
ID)，可以在集群全局范围标识事务，用于取代过去通过binlog文件偏移量定位复制位置的传统方式。借助GTID，在发生主备切换的情况下，MySQL的其它Slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务,减少了数据发生不一致的风险。
主、从：
编辑配置文件
Vim /etc/my.cnf
添加如下两行
gtid_mode = ON
enforce_gtid_consistency = true
重点内容从：
[root@server2 mysql5.7]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
mysql> change master to master_host='172.25.31.1', master_user='repl', masmysql> change master to master_host='172.25.31.1', master_user='repl', master_password='Cqmyg+666', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.96 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.31.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
在主库中删除userlist表中的user2
[root@server1 mysql5.7]# mysql -p
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> delete from userlist where username='user2';
Query OK, 1 row affected (0.61 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 | 123 |
+----------+----------+
1 row in set (0.00 sec)
MySQL半同步复制
❖ 异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。❖ 全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。❖ 半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay
log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
半同步复制的安装部署
加载插件、查看插件是否加载成功
主：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 rows in set (0.00 sec)
从：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
1 rows in set (0.00 sec)
启动半同步复制，查看半同步是否在运行
在安装完插件后，半同步复制默认是关闭的，这时需设置参数来开启半同步
主：
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.51 sec)
从：
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.05 sec)
这两个变量常用来监控主从是否运行在半同步复制模式下。
重启从上的IO线程，如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色。
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.43 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_semi_sync%'; #查看变量的值
事实上，半同步复制并不是严格意义上的半同步复制
当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master
dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。
测试：
主：
mysql> create database wyh;
Query OK, 1 row affected (0.11 sec)
mysql> create table wyh.wyh(id int);
Query OK, 0 rows affected (0.64 sec)
mysql> insert into wyh.wyh values(1);
Query OK, 1 row affected (0.16 sec)
在Slave执行stop slave之前，主的insert操作很快就能返回。
在从中执行stop slave
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
在主中写入数据
mysql> insert into wyh.wyh values(2);
Query OK, 1 row affected (10.09 sec)
在Slave执行stop slave后，主的insert操作需要10.09s才返回，而这与rpl_semi_sync_master_timeout参数的时间相吻合。
这时，查看两个状态的值，均为“OFF”了。
mysql> show status like 'Rpl_semi_sync_master_status';#主
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';#从
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
在从中执行start slave；
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
在主中写入数据
mysql> insert into wyh.wyh values(3);
Query OK, 1 row affected (0.08 sec)
mysql> show status like 'Rpl_semi_sync_master_status';#主
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';#从
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
在Slave执行start slave后，主的insert操作很快就能返回，此时，两个状态的值也变为“ON”了。
相关文件作用:
mysql-bin.index: 服务器一旦开启二进制日志,会产生一个与二日志文件同名,但是以.index 结尾
的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL 用它来定位二进制日志文件。 mysqld-relay-bin.index:
该文件的功能与 mysql-bin.index 类似,但是它是针对中继日志,而不是 二进制日志。 master.info :保存
master 的相关信息。不要删除它,否则,slave 重启后不能连接 master。 relay-log.info :包含 slave
中当前二进制日志和中继日志的信息。
如果写操作较少,而读操作很多时,可以采取这种结构。你可以将读操作分布到其它的 slave,从而减小master 的压力。但是,当 slave 增加到一定数量时,slave 对 master 的负载以及网络带宽都会成为一个严重的问题。这种结构虽然简单,但是,它却非常灵活,足够满足大多数应用需求。
当设置 log_slave_updates 时,你可以让 slave 扮演其它 slave 的 master。此时,slave 把 SQL 线程执行的事件写进行自己的二进制日志(binary log),然后,其它的 slave 可以获取这些事件并执行它,从而有效缓解master 的压力。如下:
log_slave_updates
添加server3:172.25.31.3为 slave2:
如果master 上已经有数据,而新加的 slave2 没有,必须在配置复制前同步数据
在 slave1 上加入以下设置
vim /etc/my.cnf
log_slave_updates
在 slave1 上创建同步帐户,并给予权限
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'Cqmyg+666;
在 slave2 上添加配置
server-id=3
开启gtid 半同步复制
在 slave2 上执行以下命令
mysql> change master to master_host='172.25.31.2', master_user='repl',
master_password='Cqmyg+666', MASTER_AUTO_POSITION=1;
mysql> slave start;
mysql> show slave status\G;
并行复制
在配置文件中添加如下几行
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
重启数据库
查看有哪些线程正在执行
mysql> show processlist;
扫一扫
4216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
