MySQL主从同步
主从同步概述
MySQL主从同步
- 主从同步介绍
- 实现数据自动同步的服务结构
- 主服务器：接受客户端访问连接
- 从服务器：自动同步主服务器的数据
主从同步的工作原理
主库有1个线程叫dump线程，从库有2个线程（io+sql）,当主库有数据发生改变的时候，那么主库会通过自己的dump线程去给从库发个信号，然后从库再去请求主库的binlog日志文件并保存在本机的中继日志文件里面，然后sql线程执行中继日志文件里面的SQL信息，完成同步。
- Master
- 启用binlog日志
- Slave
- Slave_IO：复制master主机binlog日志文件里的SQL命令到本机的relay-log文件里
- Slave_SQL：执行本机的relay-log文件里的SQL语句，实现与Master数据一致
构建主从同步
构建思路
- 配置主服务器
- 启用binlog日志、授权用户、查看binlog日志
- 配置从服务器
- 设置server_id
- 确保与主服务器数据一致
- 指定主库信息
- 启动slave程序
- 查看状态信息
拓扑结构图
构建主服务器
- 修改主配置文件
启动binlog日志，并重启服务
[root@host51 ~]# vim /etc/my.cnf
4 [mysqld]
5 server_id=51 #指定服务器id
6 log_bin=master51 #启用binlog日志
[root@host51 ~]# systemctl restart mysqld
- 授权用户
用户权限replication slave
用户名自定义
客户端地址允许从服务器连接
密码自定义，但要符合密码策略
[root@host51 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
mysql> select user ,host from mysql.user where user="repluser";
+----------+------+
| user | host |
+----------+------+
| repluser | % |
+----------+------+
1 row in set (0.00 sec)
mysql> show master status; #查看日志信息
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从服务器
- 修改主配置文件my.cnf
server_id不允许与主服务器server_id相同
[root@host52 ~]# vim /etc/my.cnf
4 [mysqld]
5 server_id=52 #指定服务器id
[root@host52 ~]# systemctl restart mysqld
- 确保与主服务器数据一致
1. 在主服务器上备份数据，将备份文件拷贝给从服务器
2. 从服务器使用备份文件恢复数据
3. 在从服务器上查看备份文件记录的binlog日志
[root@host51 ~]# mysqldump -uroot -p123456 --master-data db1 > /allbak.sql #主库备份数据，--master-data记录当前备份数据对应的日志信息
[root@host51 ~]# ls /allbak.sql
/allbak.sql
[root@host51 ~]# scp /allbak.sql root@192.168.4.52:/root
[root@host52 ~]# ls
allbak.sql
[root@host52 ~]# mysql -uroot -p123456 db1 < /root/allbak.sql #一定要先在从服务器上创建与主服务器同名的数据库
[root@host52 ~]# grep "master" /root/allbak.sql
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001', MASTER_LOG_POS=441; #查看binlog日志以及偏移量
- 指定主服务器信息
[root@host52 ~]# mysql -uroot -p123456
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to
-> master_host="192.168.4.51" , #主服务器IP地址
-> master_user="repluser" , #主服务器授权用户
-> master_password="123qqq...A" , #授权密码
-> master_log_file="master51.000001" , #主服务器binlog日志
-> master_log_pos=441 ; #日志偏移量
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave; #启动slave进程
Query OK, 0 rows affected (0.00 sec)
1. Master信息会自动保存到/var/lib/mysql/master.info文件
2. 若更改主服务器信息时，应先执行stop slave;修改后在执行start slave;
- 查看Slave状态
确认IO线程，SQL线程都是Yes状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51 #主服务器IP地址
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 441
Relay_Log_File: host52-relay-bin.000004
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes #IO线程已经运行
Slave_SQL_Running: Yes #SQL线程已经运行
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: 441
Relay_Log_Space: 692
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: 51
Master_UUID: 6d7b632c-4e3f-11ea-8a8d-000c29875030
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)
#要确保IO以及SQL都是yes状态，才可以与主服务器保持数据一致
# Last_IO_Error：slave_IO_Running的报错信息
# Last_SQL_Error：slave_SQL_Running的报错信息
如果报错检查Last_IO_Error以及Last_SQL_Error，然后根据错误进行修改，stop slave ，再次重新写change master，然后重新start slave，查看信息状态
change master to master_user="用户名"
change master to master_host="主服务器地址"
change master to master_password="主服务器设置的密码"
change master to master_log_file="主服务器的binlog日志名"
change master to master_log_pos=主服务器的偏移量 ;
（ps：当如果报错号码为2003，此时检查防火墙与selinux的状态，确保防火墙关闭，selinux为permissive状态）
将从服务器恢复成独立的数据库
- 存放在数据库目录下的相关文件
- 将以下文件全部删除，重启服务，即可恢复成独立的数据库
| 文件名 | 说明 |
|---|---|
| master.info | 主库信息 |
| relay-log.info | 中继日志信息 |
| 主机名-relay-bin.xxxxxx | 中继日志 |
| 主机名-relay-bin.index | 索引文件 |
[root@host52 ~]# cd /var/lib/mysql
[root@host52 mysql]# ls
auto.cnf ibdata1 mysql sys
host52-relay-bin.000003 ib_logfile0 mysql.sock xtrabackup_binlog_pos_innodb
host52-relay-bin.000004 ib_logfile1 mysql.sock.lock xtrabackup_info
host52-relay-bin.index ibtmp1 performance_schema
ib_buffer_pool master.info relay-log.info
[root@host52 mysql]# cat master.info #此处是主库信息
25
master51.000001
441
192.168.4.51
repluser
123qqq...A
3306
60
0
0
30.000
0
6d7b632c-4e3f-11ea-8a8d-000c29875030
86400
0
[root@host52 mysql]#rm -rf master.info
[root@host52 mysql]#rm -rf relay-log.info
[root@host52 mysql]#rm -rf host52-replay-bin.*
[root@host52 mysql]#systemctl restart mysqld
[root@host52 ~]# mysql -uroot -p123456
mysql>show slave status;
mysql> change master to
-> master_host="192.168.4.51" ,
-> master_user="repluser" ,
-> master_password="123qqq...A" ,
-> master_log_file="master51.000001" ,
-> master_log_pos=441 ;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql>start slave;
mysql>show slave status;
测试：
在主服务器上新写入数据，在从服务器上面查看
[root@host51 ~]# mysql -uroot -p123456
mysql> create database db5;
Query OK, 1 row affected (0.00 sec)
mysql> create table db5.user(name char(12));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into db5.user values("haha");
Query OK, 1 row affected (0.02 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 1024 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在host52上面查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db5 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.05 sec)
mysql> select * from db5.user;
+------+
| name |
+------+
| haha |
+------+
1 row in set (0.00 sec)
构建主从同步结构模式
结构类型
- 基本应用
- 单向复制：一主 <-- 一从
- 扩展应用
- 一主多从：从 <-- 主 --> 从
- 链式复制：主 <-- 从 <-- 从
- 互为主从：主 < – > 主
配置一主多从结构
- 拓扑图如下
- 配置从服务器53
1. 修改配置文件
2. 确保与主服务器数据一致
3. 指定主库信息
4. 启动slave进程
[root@host53 ~]# vim /etc/my.cnf
4 [mysqld]
5 server_id=53
[root@host51 ~]# mysqldump -uroot -p123456 db5 > /root/db5.sql
[root@host51 ~]# scp -r /root/db5.sql root@192.168.4.53:/root
[root@host51 ~]# mysql -uroot -p123456
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 1024 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@host53 ~]# mysql -uroot -p123456
mysql> create database db5;
[root@host53 ~]# mysql -uroot -p123456 db5 < /root/db5.sql
[root@host53 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.51" ,
-> master_user="repluser" ,
-> master_password="123qqq...A" ,
-> master_log_file="master51.000001" ,
-> master_log_pos=1024 ;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 1024
Relay_Log_File: host53-relay-bin.000003
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.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: 1024
Relay_Log_Space: 527
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: 51
Master_UUID: 6d7b632c-4e3f-11ea-8a8d-000c29875030
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)
UUID问题：如果报错UUID冲突，可能是由于之前做过的实验将所有数据备份过来导致uid也是相同的，解决办法如下：进到/var/lib/mysql数据库目录，修改/var/lib/mysql/auto.cnf, 改一个新的值，但与原来的长度一定要相同，重启数据库服务即可，或者也可以进到数据库select uid();获取新的uid复制到/var/lib/mysql/auto.cnf中
[root@host51 ~]# mysql -uroot -p123456
mysql> select * from db5.user;
+---------+
| name |
+---------+
| haha |
| heihei |
| lalalla |
| jkfjf |
| uuuuu |
| sss |
| ggg |
+---------+
7 rows in set (0.00 sec)
[root@host52 ~]# mysql -uroot -p123456
mysql> select * from db5.user;
+---------+
| name |
+---------+
| haha |
| heihei |
| lalalla |
| jkfjf |
| uuuuu |
| sss |
| ggg |
+---------+
7 rows in set (0.01 sec)
[root@host53 ~]# mysql -uroot -p123456
mysql> select * from db5.user;
+---------+
| name |
+---------+
| haha |
| heihei |
| lalalla |
| jkfjf |
| uuuuu |
| sss |
| ggg |
+---------+
7 rows in set (0.00 sec)
[root@host53 mysql]# ls
auto.cnf host53-relay-bin.000003 master.info relay-log.info
ca-key.pem host53-relay-bin.index mysql server-cert.pem
ca.pem ib_buffer_pool mysql.sock server-key.pem
client-cert.pem ibdata1 mysql.sock.lock sys
client-key.pem ib_logfile0 performance_schema
db5 ib_logfile1 private_key.pem
host53-relay-bin.000002 ibtmp1 public_key.pem
[root@host53 ~]# cat /var/lib/mysql/master.info
25
master51.000001
2547
192.168.4.51
repluser
123qqq...A
3306
60
0
0
30.000
0
6d7b632c-4e3f-11ea-8a8d-000c29875030
86400
0
配置主从从模式
- 拓扑图如下
为了在启用binlog日志以及通不值钱保持主、从库的一致性，主从同步未配置之前，要保证从库上要有主库上的数据，禁用selinux，关闭防火墙服务，保证物理连接正常。
- 配置主服务器
1.将53主机恢复成独立的数据库
2. 启用binlog日志
3. 重启数据库服务
4. 用户授权
[root@host53 ~]# mysql -uroot -p123456 -e "show slave status \G" | grep 192.168.4.51 #首先查看host53当前是否为其他主机的从服务器，显示当前为51主机的从服务器
mysql: [Warning] Using a password on the command line interface can be insecure.
Master_Host: 192.168.4.51
Last_IO_Error: error connecting to master 'repluser@192.168.4.51:3306' - retry-time: 60 retries: 14
[root@host53 ~]# systemctl stop mysqld #关闭服务
cd [root@host53 ~]# cd /var/lib/mysql
[root@host53 mysql]# ls
auto.cnf db5 ib_logfile0 private_key.pem sys
ca-key.pem host53-relay-bin.000004 ib_logfile1 public_key.pem
ca.pem host53-relay-bin.index master.info relay-log.info
client-cert.pem ib_buffer_pool mysql server-cert.pem
client-key.pem ibdata1 performance_schema server-key.pem
[root@host53 mysql]# rm -rf master.info #删除主库的信息
[root@host53 mysql]# rm -rf relay-log.info #删除中继日志信息
[root@host53 mysql]# rm -rf host53-relay-bin.* #删除所有
[root@host53 mysql]# vim /etc/my.cnf
5 #server_id=53
[root@host53 mysql]# systemctl restart mysqld
[root@host53 mysql]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db5 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.10 sec)
mysql> drop database db5;
Query OK, 1 row affected (0.12 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
[root@host53 mysql]# vim /etc/my.cnf
[mysqld]
server_id=53
log_bin=host53
[root@host53 mysql]# systemctl restart mysqld
[root@host53 mysql]# mysql -uroot -p123456
用户授权
mysql> grant replication slave on *.* to tian@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看日志信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| host53.000001 | 441 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 配置服务器54（既做主服务器又做从服务器）
级联复制：指在主服务器写的数据，会同步到从服务器的从服务器中
1. 修改配置文件
2. 用户授权
3. 指定主库信息
4. 启动slave进程
[root@host54 ~]# vim /etc/my.cnf
[mysqld]
log_bin=host54
server_id=54
log_slave_updates #允许级联复制
[root@host54 ~]# systemctl restart mysqld
[root@host54 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to tian@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| host54.000003 | 437 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> change master to
-> master_host="192.168.4.53",
-> master_user="tian",
-> master_password="123qqq...A",
-> master_log_file="host53.000001",
-> master_log_pos=437;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.53
Master_User: tian
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: host53.000001
Read_Master_Log_Pos: 437
Relay_Log_File: host54-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: host53.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: 437
Relay_Log_Space: 525
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: 53
Master_UUID: 5240ae91-533a-11ea-a67b-000c296c0929
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)
*****************************************************************
- 配置从服务器55
1. 修改配置文件
2. 指定主库信息
3. 启动slave进程
[root@host55 ~]# vim /etc/my.cnf
5 server_id=55
[root@host55 ~]# systemctl restart mysqld
[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> show master status;
Empty set (0.00 sec)
mysql> change master to
-> master_host="192.168.4.54",
-> master_user="tian",
-> master_password="123qqq...A",
-> master_log_file="host54.000003",
-> master_log_pos=437;
Query OK, 0 rows affected, 2 warnings (0.08 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: 192.168.4.54
Master_User: tian
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: host54.000003
Read_Master_Log_Pos: 437
Relay_Log_File: host55-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: host54.000003
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: 437
Relay_Log_Space: 525
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: 54
Master_UUID: 8b4a89b4-531c-11ea-883c-000c290db2a4
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)
客户端测试，在主库上授权访问数据的连接用户，客户端连接主库执行权限匹配的SQL操作，授权用户连接第一台从库，可以看到主库的数据，授权用户连接第二台从库，可以看到主库的数据
在host53上授权用户登录
mysql> grant all on gamedb.* to adminuser@"192.168.4.50" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| host53.000001 | 751 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
用host50测试，只要下载mariadb即可，不需要mysql
[root@host50 ~]# which mysql
/usr/bin/which: no mysql in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
[root@host50 ~]# yum -y install mariadb
[root@host50 ~]# mysql -h192.168.4.53 -uadminuser -p123qqq...A
MySQL [(none)]> create database gamedb; #创建测试库
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> create table gamedb.user(name char(10)); #在测试库下创建user表
Query OK, 0 rows affected (0.07 sec)
MySQL [(none)]> insert into gamedb.user values("bob"); #在表中插入数据
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> quit
Bye
[root@host50 ~]# mysql -h192.168.4.54 -uadminuser -p123qqq...A -e "show databases" #验证54主机的状态
+--------------------+
| Database |
+--------------------+
| information_schema |
| gamedb |
+--------------------+
[root@host50 ~]# mysql -h192.168.4.54 -uadminuser -p123qqq...A -e "select * from gamedb.user" #查询插入的记录
+------+
| name |
+------+
| bob |
+------+
[root@host50 ~]# mysql -h192.168.4.55 -uadminuser -p123qqq...A -e "show databases" #验证55主机
+--------------------+
| Database |
+--------------------+
| information_schema |
| gamedb |
+--------------------+
[root@host50 ~]# mysql -h192.168.4.55 -uadminuser -p123qqq...A -e "select * from gamedb.user" #查询插入的表记录
+------+
| name |
+------+
| bob |
+------+
配置主主模式
将数据库服务器20、21配置为主主结构
20为主服务器 21为从服务器
20为从服务器 21为主服务器
在20、21上面都能看到启动了binlog日志，以及slave status
互相同步对方的数据
1.准备环境：在两台主机上都要安装mysql的软件包
20主机：
[root@20 ~]# ls
mysql-5.7.17.tar
[root@20 ~]# tar -xf mysql-5.7.17.tar
[root@20 ~]# ls
mysql-5.7.17.tar
mysql-community-client-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-test-5.7.17-1.el7.x86_64.rpm
[root@20 ~]# systemctl stop firewalld
[root@20 ~]# systemctl enable firewalld
[root@20 ~]# yum -y install mysql-community*.rpm
21主机：
[root@21 ~]# ls
mysql-5.7.17.tar
[root@21 ~]# tar -xf mysql-5.7.17.tar
[root@21 ~]# ls
mysql-5.7.17.tar
mysql-community-client-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-test-5.7.17-1.el7.x86_64.rpm
[root@21 ~]# systemctl stop firewalld
[root@21 ~]# systemctl enable firewalld
[root@21 ~]# yum -y install mysql-community*.rpm
2.起服务，修改密码（两台主机做一样的操作，以下以21为例）
[root@21 ~]# ls /var/lib/mysql
[root@21 ~]# systemctl start mysqld
[root@21 ~]# ls /var/lib/mysql
auto.cnf client-key.pem ib_logfile1 mysql.sock.lock server-cert.pem
ca-key.pem ib_buffer_pool ibtmp1 performance_schema server-key.pem
ca.pem ibdata1 mysql private_key.pem sys
client-cert.pem ib_logfile0 mysql.sock public_key.pem
[root@21 ~]# grep "password" /var/log/mysqld.log
2020-02-23T00:03:35.923408Z 1 [Note] A temporary password is generated for root@localhost: To5?l6Vphjee
[root@21 ~]# mysql -uroot -p'To5?l6Vphjee'
mysql> show variables like "%password%";
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+---------------------------------------+--------+
14 rows in set (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@"localhost" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
[root@21 ~]# vim /etc/my.cnf
[mysqld]
validate_password_length=6
validate_password_policy=0
[root@21 ~]# systemctl restart mysqld
3.将20主机设为主服务器，21主机设为从服务器
[root@20 ~]# vim /etc/my.cnf
[mysqld]
validate_password_length=6
validate_password_policy=0
server_id=20
log_bin=master20
[root@20 ~]# systemctl restart mysqld
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master20.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to tian@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master20.000001 | 437 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@21 ~]# vim /etc/my.cnf
[mysqld]
validate_password_length=6
validate_password_policy=0
server_id=21
[root@21 ~]# systemctl restart mysqld
[root@21 ~]# mysql -uroot -p123456
mysql> show master status;
Empty set (0.00 sec)
mysql> change master to
-> master_host="192.168.4.20",
-> master_user="tian" ,
-> master_password="123456" ,
-> master_log_file="master20.000001" ,
-> master_log_pos=437 ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.20
Master_User: tian
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master20.000001
Read_Master_Log_Pos: 437
Relay_Log_File: 21-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master20.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: 437
Relay_Log_Space: 523
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: 20
Master_UUID: fd553082-55cf-11ea-a325-000c29fd476f
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)
4.同样，将21作为主服务器，20做为从服务器
[root@21 ~]# vim /etc/my.cnf
[mysqld]
validate_password_length=6
validate_password_policy=0
server_id=21
log_bin=master21
[root@21 ~]# systemctl restart mysqld
[root@21 ~]# mysql -uroot -p123456
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master21.000002 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to tian@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master21.000002 | 437 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@20 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.21" ,
-> master_user="tian" ,
-> master_password="123456" ,
-> master_log_file="master21.000002" ,
-> master_log_pos=437 ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.21
Master_User: tian
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master21.000002
Read_Master_Log_Pos: 437
Relay_Log_File: 20-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master21.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: 437
Relay_Log_Space: 523
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: 21
Master_UUID: efdf9bdc-55cf-11ea-a2c4-000c293d6c33
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)
5.测试
在20主机上写入数据，在21上也能查看，在21主机上写入数据，在20主机上也能查看，且20与21主机同时都能查看到主服务器的binlog日志以及从服务器的信息
20主机：
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> create table aaa.z(name char(10));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into aaa.z values("qqq");
Query OK, 1 row affected (0.04 sec)
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
+------+
1 row in set (0.00 sec)
21主机：
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
+------+
1 row in set (0.00 sec)
mysql> insert into aaa.z values("www");
Query OK, 1 row affected (0.01 sec)
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
+------+
2 rows in set (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master21.000002 | 686 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@21 ~]# ls /var/lib/mysql #查看mysql的目录，发现出现了主库信息以及中继日志信息等
21-relay-bin.000001 client-cert.pem master21.000001 performance_schema
21-relay-bin.000002 client-key.pem master21.000002 private_key.pem
21-relay-bin.index ib_buffer_pool master21.index public_key.pem
aaa ibdata1 master.info relay-log.info
auto.cnf ib_logfile0 mysql server-cert.pem
ca-key.pem ib_logfile1 mysql.sock server-key.pem
ca.pem ibtmp1 mysql.sock.lock sys
再次回到20主机查看：
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
+------+
2 rows in set (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master20.000001 | 1012 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@20 ~]# ls /var/lib/mysql #20主机也有相应的主库信息和中继日志文件
20-relay-bin.000001 client-cert.pem master20.000001 private_key.pem
20-relay-bin.000002 client-key.pem master20.index public_key.pem
20-relay-bin.index ib_buffer_pool master.info relay-log.info
aaa ibdata1 mysql server-cert.pem
auto.cnf ib_logfile0 mysql.sock server-key.pem
ca-key.pem ib_logfile1 mysql.sock.lock sys
ca.pem ibtmp1 performance_schema
也可以用客户端进行测试：
在20与21主机上添加授权用户：
mysql> grant all on aaa.* to tian@"192.168.4.53" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.01 sec)
客户端登录，分别在20与21主机的服务器上写入数据：
[root@host53 ~]# mysql -h192.168.4.20 -utian -p123456
mysql> show grants;
+----------------------------------------------------------+
| Grants for tian@192.168.4.53 |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'192.168.4.53' |
| GRANT ALL PRIVILEGES ON `aaa`.* TO 'tian'@'192.168.4.53' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into aaa.z values("sss");
Query OK, 1 row affected (0.01 sec)
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
| sss |
+------+
3 rows in set (0.00 sec)
[root@host53 ~]# mysql -h192.168.4.21 -utian -p123456
mysql> insert into aaa.z values("eee");
Query OK, 1 row affected (0.01 sec)
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
| sss |
| eee |
+------+
4 rows in set (0.00 sec)
在20主机和21主机上分别查看：
20主机：
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
| sss |
| eee |
+------+
4 rows in set (0.01 sec)
21主机：
mysql> select * from aaa.z;
+------+
| name |
+------+
| qqq |
| www |
| sss |
| eee |
+------+
4 rows in set (0.00 sec)
复制模式
复制模式介绍
- 异步模式（Asynchronous replication）
- 主服务器执行完一次事务后，立即将结果返回给客户端，不关心从服务器是否已经同步
- 如果配置一主多从模式，默认为异步模式，当客户端写入数据时，主服务器直接返回给客户端告知数据已经存储成功，而不等从服务器进行数据同步
- 好处：用户的体验度高
- 坏处：当主服务器坏掉时，有可能一个服务器上都没有客户端写过的数据，造成数据丢失
- 半同步复制模式
- 介于异步复制和完全同步复制之间
- 主服务器在执行完一次事务后，等待至少一台从服务器同步数据完成，才将结果返回给客户端
- 当客户端存储数据时，只要有一台从服务器同步主服务器的数据时，主服务器就会直接回复客户端数据存储已经完成，客户端的体验度高，也可以保证至少有一台从服务器有同步数据
- 全同步复制模式
- 等所有的从服务器存储完数据后，主服务器才回复给客户端告知数据存储完成，客户端的体验度低，但每个从服务器中都有主服务器中的数据，当主服务器坏掉也可以正常使用
配置半同步模式
- 步骤：
1. 安装模块
2. 查看安装模块
3. 启用模块
命令行配置（马上生效，但重启服务会失效）
host53主
查看是否允许动态加载模块（默认允许）
mysql> show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.03 sec)
命令行安装模块
mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected (0.07 sec)
查看模块
mysql> use information_schema
mysql> desc PLUGINS;
+------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| PLUGIN_NAME | varchar(64) | NO | | | |
| PLUGIN_VERSION | varchar(20) | NO | | | |
| PLUGIN_STATUS | varchar(10) | NO | | | |
| PLUGIN_TYPE | varchar(80) | NO | | | |
| PLUGIN_TYPE_VERSION | varchar(20) | NO | | | |
| PLUGIN_LIBRARY | varchar(64) | YES | | NULL | |
| PLUGIN_LIBRARY_VERSION | varchar(20) | YES | | NULL | |
| PLUGIN_AUTHOR | varchar(64) | YES | | NULL | |
| PLUGIN_DESCRIPTION | longtext | YES | | NULL | |
| PLUGIN_LICENSE | varchar(80) | YES | | NULL | |
| LOAD_OPTION | varchar(64) | NO | | | |
+------------------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
查看模块是否安装成功
mysql> select PLUGIN_NAME , PLUGIN_STATUS from PLUGINS where PLUGIN_NAME like "%semi%";
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
启用master半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.01 sec)
查看半同步复制模式是否已启用
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
+------------------------------+-------+
1 row in set (0.24 sec)
host54主从
安装模块
主：
mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected (0.08 sec)
从：
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected (0.03 sec)
mysql> use information_schema
mysql> select plugin_name ,plugin_status from plugins where plugin_name like "%semi%" ;
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.10 sec)
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.10 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
2 rows in set (0.09 sec)
host55从
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected (0.06 sec)
mysql> use information_schema
mysql> select plugin_name ,plugin_status from plugins where plugin_name like "%semi%" ;
+---------------------+---------------+
| plugin_name | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "rpl_semi_sync_%_enabled";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
+-----------------------------+-------+
1 row in set (0.01 sec)
************************************************************
永久配置（/etc/my.cnf）
在host54主机上面操作，永久启用半同步复制模式
[root@host54 mysql]# vim /etc/my.cnf
[mysqld]
server_id=54
log_bin=host54
log_slave_updates
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
重启数据库服务，并查看状态信息
[root@host54 mysql]# mystemctl restart mysqld
[root@host54 mysql]# mysql -uroot -p123456
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE | //模块已加载
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | //模式已启用
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
2 rows in set (0.00 sec)
总结
- 主从复制的用途
- 实时备份，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
- 主从部署的必要条件？
- 主库开启binlog日志（设置binlog参数）
- 主从server_id不同
- 从库服务器要与主库数据相同，可以连通主库
- 主从复制存在的问题？
- 主库宕机后，数据可能会丢失
- 从库只有一个SQL Thread，主库写的压力大，复制可能会延时
- 解决办法：
- 半同步复制，可以解决数据丢失的问题
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
