主从同步的定义
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表.
主从同步的作用
- 实时备份
- 读写分离
- 备份
主从同步原理
注意: 从库需要两个线程 1.I/O线程 2.SQL线程
步骤：
- 主库开启Binlog日志，主库的增删改信息记录到binlog日志中
- 从库通过i/o线程去请求主库的binlog日志的内容。主库验证从库正常后，发送指定pos值的日志给从库，从库将日志写入到从库服务器中的relay log（中继日志）
- 最后从库的sql线程读取relay log中的日志并replay（回放）日志中的内容以达到主从同步
主从服务的配置
- 确保从数据库与主数据库数据一致
- 在主数据库创建一个同步账号授权给从数据库使用
- 配置主数据库配置文件
- 配置从数据库配置文件
配置流程
环境模拟如下：
| 数据库名称 | ip | 是否有初始数据 |
|---|---|---|
| 主数据库 | 192.168.197.135 | 有初始数据 |
| 从数据库 | 192.168.197.137 | 无初始化数据 |
- 新增主库read Lock,保证主从的同步内容保持一致
mysql> select * from student; #现主库中有student表
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | qianliu |
| 5 | sunqi |
+----+----------+
5 rows in set (0.00 sec)
## 重开一个终端，增加read lock，保证表内数据不再有变动
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
# 在进行增删改数据时，会卡在命令行。无法成功执行命令
mysql> update student set name = 'zhangshan' where id = 1;
- 将主库进行全备
root@localhost scripts]# mysqldump -uroot -p --all-databases > ~/all_packup10291527
Enter password:
[root@localhost scripts]# ls ~/all
all_packup10291527
## 将备份文件复制到从库上并恢复数据
[root@localhost scripts]# scp ~/all_packup10291527 root@192.168.197.137:/root/
The authenticity of host '192.168.197.137 (192.168.197.137)' can't be established.
ECDSA key fingerprint is SHA256:tBYHKkTGSpfv4f6M89LZCx1lndW9bmW9KKiAOedfLc0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.197.137' (ECDSA) to the list of known hosts.
root@192.168.197.137's password:
all_packup10291527 100% 783KB 17.4MB/s 00:00
## 在从库上进行恢复数据
[root@localhost ~]# mysql < all_packup10291527
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sawyer |
| sys |
+--------------------+
5 rows in set (0.00 sec)
## 已恢复主库上的所有库及表
mysql> select * from sawyer.student;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | qianliu |
| 5 | sunqi |
+----+----------+
5 rows in set (0.00 sec)
- 主库上新增同步账号并授权给从库使用
mysql> create user 'test@192.168.197.137';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'test'@'192.168.197.137';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges； # 刷新权限
- 配置主库的配置文件
[root@localhost scripts]# vim /etc/my.cnf
[mysqld]
[mysqld]
basedir = /usr/local/mysql
datadir = /var/mysql_data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /var/mysql_data/3306/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin #开启binlog日志，以便于主从同步
server-id=10
[client]
user = root
password = 123456
- 重启服务并查看主库状态
[root@localhost scripts]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 配置从数据库的配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /var/mysql_data/3306
socket = /tmp/mysql.sock
port = 3306
pid-file = /var/mysql_data/3306/mysql.pid
user = mysql
skip-name-resolve
server-id = 20
relay-log = mysql-relay-bin # 开启从库的relay-log日志 保证主库传过来的Bin log有文件进行存放
[client]
user = root
password = 123456
- 重启从库的服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
- 配置并启动主从同步
mysql> change master to
-> master_host='192.168.197.135',
-> master_user='test',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
## 查看从库的状态
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.197.133
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1225
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
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: 1225
Relay_Log_Space: 520
Until_Condition: None
......