MySQL主从复制：
注：不等于备份
实时同步
机械故障
远程灾备
用于备份
高可用HA
负载均衡
读写分离
分布式数据库
复制技术原理：
1、主==>开启二进制日志（将DDL DML DCL记录）
2、备==>IO线程读取主二进制日志，复制到中继日志
3、备==>SQL线程读取中继日志，将其重放到数据库上
1.png
主库：
# 开启binlog并设置server-id
vim /etc/my.cnf
[mysqld]
log-bin=
server-id=1
说明：log-bin 不指定,则以《主机名-bin》,存放在/var/lib/mysql/ 下
# 重启数据库
systemctl restart mysqld
扩展：
# 查看mysql日志相关的
mysql> show variables like 'log_%';
# 创建复制账号并授权：
mysql> grant REPLICATION SLAVE, REPLICATION CLIENT on *.*
-> to rep@'192.168.174.%' identified by 'ZShj26@1200';
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
创建测试数据：
# 创建数据库
mysql> create database student;
Query OK, 1 row affected (0.00 sec)
# 创建表
mysql> create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) not null default '0',
-> dept varchar(16) default null,
-> primary key(id),
-> KEY index_name(name)
-> );
Query OK, 0 rows affected (0.16 sec)
#查看表
mysql>
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
# 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 插入数据:
mysql> insert into student(id,name,age,dept) values
(1,'zhangsan','23','book'),
(2,'zhangsan1','24','book1'),
(3,'zhangsan2','25','book2'),
(4,'zhangsan3','26','book3');
# 查看数据
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zhangsan | 23 | book |
| 2 | zhangsan1 | 24 | book1 |
| 3 | zhangsan2 | 25 | book2 |
| 4 | zhangsan3 | 26 | book3 |
+----+-----------+-----+-------+
4 rows in set (0.00 sec)
数据备份：
[root@mysql_node1 ~]# mysqldump -p'ZShj26@1200' --all-databases --single-transaction --master-data=1 --flush-logs >`date +%F`-mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql_node1 ~]# sed -n '22p' 2019-06-08-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql_node1-bin.000002', MASTER_LOG_POS=154;
将数据拷贝到从库：
[root@mysql_node1 ~]# scp 2019-06-08-mysql-all.sql mysql_node2:/
root@mysql_node2's password:
2019-06-08-mysql-all.sql 100% 776KB 33.0MB/s 00:00
从库：
# 设置server-id
[mysqld]
server-id=2
在从服务器上，可以选择不开启binlog。当开启了binlog后，如果想把重放的时间同样也记录到binlog中，可将log_slave_updates参数设置为1。
# 重启数据库:
systemctl restart mysqld
# 测试复制账号是否可以远程连接上：（如果连接不上请关闭主库的防火墙）
[root@mysql_node1 ~]# mysql -hmysql_node1 -urep -p'ZShj26@1200';
# 查看授权:
mysql> show grants;
+-----------------------------------------------------------------------------+
| Grants for rep@192.168.174.% |
+-----------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'192.168.174.%' |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 导入数据
不建议：（这种方式导入，开启复制的时候需要手动指定binlog和postion）
[root@mysql_node2 ~]# mysql -p'ZShj26@1200' < /2019-06-08-mysql-all.sql
建议：（这种方式就可以省略指定binlog和postion）
# 不记录bin-log日志
mysql> set sql_log_bin=0;
# 数据导入
mysql> source /2019-06-08-mysql-all.sql
主库模拟增加数据：
mysql> insert into student(id,name,age,dept) values
(5,'zhangsan4','23','book4'),
(6,'zhangsan5','24','book5'),
(7,'zhangsan6','25','book6'),
(8,'zhangsan7','26','book7');
从库：
# 开启复制
mysql> CHANGE MASTER TO
MASTER_HOST='mysql_node1',
MASTER_USER='rep',
MASTER_PASSWORD='ZShj26@1200';
# 如果不是source的方式导入,就需要手动指定：
MASTER_LOG_FILE='mysql_node1-bin.000002',
MASTER_LOG_POS=recorded_log_position;
怎么获取到这两个值呢？（备份的时候加了参数 --master-data=1 （不注释））
sed -n 22p 2019-06-08-mysql-all.sql
# 开启复制功能（启动slave角色）
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看状态是否正常（重点关注以下两个变量，如果为YES，则代表复制搭建成功）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql_node1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_node1-bin.000002
Read_Master_Log_Pos: 1372
Relay_Log_File: mysql_node2-relay-bin.000004
Relay_Log_Pos: 1597
Relay_Master_Log_File: mysql_node1-bin.000002
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: 1372
Relay_Log_Space: 1863
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: 5715e8f1-89ae-11e9-a2c2-000c29a6956b
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: Yes
Slave_SQL_Running: Yes
Master_Host: mysql_node1 #master主机
Master_User: rep #master用户
Master_Port: 3306 #数据库端口
Read_Master_Log_Pos: 1372 #读取到主服务器的position位置
Relay_Log_Pos: 1597 #从服务器SQL线程已经执行和处理过的中继日志位置
Seconds_Behind_Master: 0 #这个等于0的话就代表主从同步正常并且没有延迟
mysql> SHOW GLOBAL VARIABLeS LIKE '%log%'; =======》log_bin | ON （启用二进制日志）
=======》 relay_log_purge | ON （启用中继日志）
mysql> SHOW GLOBAL VARIABLES LIKE '%server%'; =======》server_id | 1 （为配置文件设置的数值）
# 查看数据（可以看出不在备份文件的5、6、7、8条数据都同步过来）
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zhangsan | 23 | book |
| 2 | zhangsan1 | 24 | book1 |
| 3 | zhangsan2 | 25 | book2 |
| 4 | zhangsan3 | 26 | book3 |
| 5 | zhangsan4 | 23 | book4 |
| 6 | zhangsan5 | 24 | book5 |
| 7 | zhangsan6 | 25 | book6 |
| 8 | zhangsan7 | 26 | book7 |
+----+-----------+-----+-------+
8 rows in set (0.00 sec)
主从复制（M-S）GTID
主库：跟着上面的步骤配置
# 修改配置文件
vim /etc/my.cnf
log-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1
# 重启mysql
systemctl restart mysqld
从库：
# 跟着上面的步骤配置，将从库的数据rm（慎用！慎用！慎用！）
[root@mysql_node2 ~]# rm -rf /var/lib/mysql
# 修改配置文件
server-id=2
log-bin
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# 不写上面的参数，就是以文件的方式保存，加上上面的参数就是以数据库的方式存放
查看方式：（里面记录复制账号、密码、主机、端口、uuid）
mysql> select * from mysql.slave_master_info\G
# 重启数据库
[root@mysql_node2 ~]# systemctl restart mysqld
主库：
# 备份数据（--master-data=2 是1还是2都没多大关闭，我们就选2注释）
mysqldump -p'ZShj26@1200' --all-databases --single-transaction --master-data=2 --flush-logs >`date +%F`-mysql-all.sql
# 拷贝到mysql_node2
scp 2019-06-09-mysql-all.sql mysql_node2:/
# 创建复制账号并授权：
mysql> grant REPLICATION SLAVE, REPLICATION CLIENT on *.*
-> to rep@'192.168.174.%' identified by 'ZShj26@1200';
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
从库：
# 测试复制账号是否可以远程连接上：（如果连接不上请关闭主库的防火墙）
[root@mysql_node2 ~]# mysql -hmysql_node1 -urep -p'ZShj26@1200';
# 查看授权:
mysql> show grants;
+-----------------------------------------------------------------------------+
| Grants for rep@192.168.174.% |
+-----------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'192.168.174.%' |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 备份数据导入
GTID模式不需要手动指定binlog和postion，下面2种方式导入都可以
1、命令行
mysql -p'ZShj26@1200' < /2019-06-08-mysql-all.sql
2、mysql里面
mysql> set sql_log_bin=0;
mysql> source /2019-06-09-mysql-all.sql
# 开启复制
mysql> change master to
master_host='mysql_node1',
master_user='rep',
master_password='ZShj26@1200',
master_auto_position=1;
# 开启复制功能（启动slave角色）
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
问题1：
Slave_IO_Running: Connecting
开启复制的时候，master_user=‘rep’ 写错了
问题2：
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决:
[root@mysql_node2 ~]# systemctl stop mysqld.service
[root@mysql_node2 ~]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
[root@mysql_node2 ~]# systemctl start mysqld
[root@mysql_node2 ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=ad0bd229-8ab3-11e9-8c9c-000c2991b69e
网友评论