简单复制
#环境说明
#两台linux虚拟机
#Linux版本 CentOS7，mysql版本5.7
192.168.3.11 主节点
192.168.3.105 从节点
#准备工作，主节点和从节点安装好MySQL，并且都按下面设置
~]# vim /etc/selinux/config #关闭selinux
SELINUX=disabled
~]# systemctl stop firewalld.service #停止firewall
~]# systemctl disable firewalld.service #禁止firewall开机启动
#主从节点mysql的环境配置
[root@n5 mysql]# vim /usr/local/mysql/etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/usr/local/mysql/logs/error.log
pid-file=/var/run/mysql/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d
#操作主节点
~]# systemctl stop mysql #停止mysql服务
~]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf #主节点环境配置
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=1
log-bin=master-log
~]# systemctl start mysql #开启mysql服务
mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit';
#查看自动事物是否开启
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.3.%' IDENTIFIED BY 'replpass';
#创建拥有复制权限的账号
mysql> FLUSH PRIVILEGES; #刷新系统权限表
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000005 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
#查看让从节点从二进制哪个位置开始向后进行复制，文件05的154处
~]# mysqldump -uroot -p112233 --all-databases -R -E --triggers -x --master-data=2 > alldb.sql
#主节点全备数据
~]# scp alldb.sql 192.168.3.105:/root #主节点备份数据库发送到从节点
#操作从节点
~]# systemctl stop mysql
~]# rm -rf /data/mysql/*
~]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
#因清空了整个mysql数据文件，所以重新配置生成一些默认文件
~]# systemctl start mysql
~]# mysql < alldb.sql #从节点恢复主节点的全备数据
~]# systemctl stop mysql
~]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=11
relay_log=relay-log
[root@n5 ~]# systemctl start mysql
[root@n5 ~]# ss -tnl #查看3306端口是否开启
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.11',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE='master-log.000005',MASTER_LOG_POS=154;
#设置从节点连接主节点的参数
mysql> START SLAVE; #启动io线程和sql线程
mysql> SHOW SLAVE STATUS\G #查看主从状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000006
Read_Master_Log_Pos: 618
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 321
Relay_Master_Log_File: master-log.000006
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: 618
Relay_Log_Space: 1201
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 #服务器ID为1
Master_UUID: 35bdbcf6-3b93-11e8-9d64-000c295d0e95
Master_Info_File: /data/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)
#测试
mysql> CREATE DATABASE mydb1;
#主节点上创建数据库mydb1
-------------------------分割线-------------------------
mysql> SHOW DATABASES; #在从服务器上查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 | #从节点成功自动把新建数据库复制过来
| mysql |
| performance_schema |
| sys |
+--------------------+
双主复制
#环境说明
#两台linux虚拟主机
#Linux版本 CentOS7，mysql版本5.7
192.168.3.105 节点1
192.168.3.106 节点2
#两台节点的mysql环境配置
[root@n5 mysql]# vim //usr/local/mysql/etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/usr/local/mysql/logs/error.log
pid-file=/var/run/mysql/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d
#准备工作，节点1和节点2各安装好MySQL，并且按下面设置
~]# vim /etc/selinux/config #关闭selinux
SELINUX=disabled
~]# systemctl stop firewalld.service #停止firewall
~]# systemctl disable firewalld.service #禁止firewall开机启动
~]# systemctl stop mysql #停止mysql服务
~]# rm -rf /data/mysql/* #删除mysql数据
~]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
#重新配置环境
#配置节点1
~]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=11 #服务器id号
log-bin=master-log #开启二进制日志
relay_log=relay-log #开启中继日志
auto_increment_offset=1 #只存储奇数行
auto_increment_increment=2 #存储行数步进为2
]# systemctl start mysql
]# mysql
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.152.%' IDENTIFIED BY 'replpass';
#创建拥有复制权限的账号
mysql> FLUSH PRIVILEGES; #刷新系统权限表
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000001 | 635 | | | |
+-------------------+----------+--------------+------------------+-------------------+
#查看节点1为主节点时，节点2当从节点开始的二进制位置
#配置节点2
~]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=12 #服务器id号
log-bin=master-log #开启二进制日志
relay_log=relay-log #开启中继日志
auto_increment_offset=2 #只存储偶数行
auto_increment_increment=2 #存储行数步进为2
]# systemctl start mysql
]# mysql
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.3.%' IDENTIFIED BY 'replpass';
mysql> FLUSH PRIVILEGES; #刷新系统权限表
#创建拥有复制权限的账号
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000001 | 635 | | | |
+-------------------+----------+--------------+------------------+-------------------+
#查看节点2为主节点时，节点1当从节点开始的二进制位置
双主复制时，两个节点都要写入数据并同步，为解决两个节点把数据写入相同ID，节点1只写奇数行，节点2只写偶数行。
#配置节点1
mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.106',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE=''master-log.000001',MASTER_LOG_POS=635;
#节点1当作从节点时，向主节点节点2连接时的配置
#配置节点2
mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.105',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_PORT=3306,MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=635;
#当节点2当作从节点时，向主节点节点1连接时的配置
#启动节点1
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.106
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000002
Read_Master_Log_Pos: 633
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 848
Relay_Master_Log_File: master-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.............
#启动节点2
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.105
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000002
Read_Master_Log_Pos: 633
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 848
Relay_Master_Log_File: master-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
双主复制测试
#在节点1添加数据
mysql> CREATE DATABASE mydb;
mysql> use mydb;
mysql> CREATE TABLE tbl1 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(30));
mysql> INSERT INTO tbl1 (Name) VALUES ('stu1'),('stu2');
mysql> SELECT * FROM tbl1; #查看节点1中的数据，插入数据为奇数行
+----+------+
| id | Name |
+----+------+
| 1 | stu1 |
| 3 | stu2 |
+----+------+
#在节点2添加数据
mysql> use mydb #连接节点1创建的数据库
mysql> INSERT INTO tbl1 (Name) VALUES ('stu3'),('stu4');
#继续在表中插入数据
mysql> SELECT * FROM tbl1;
+----+------+
| id | Name |
+----+------+
| 1 | stu1 |
| 3 | stu2 |
| 4 | stu3 |
| 6 | stu4 |
+----+------+
#在节点2中插入的数据为偶数行，是接着ID3后面追加插入的
#来到节点1继续查看数据
mysql> SELECT * FROM tbl1;
+----+------+
| id | Name |
+----+------+
| 1 | stu1 |
| 3 | stu2 |
| 4 | stu3 |
| 6 | stu4 |
+----+------+
#刚刚节点2插入的数据已经同步过来，双主复制架设成功。
半同步复制
何为半同步复制模式呢？先了解异步复制模式，异步复制是主节点把二进制日志发给从节点，然后不管从节点有没有收到，继续主节点的下一步操作。这里暴露一个问题，当从节点发生故障了，那么会导致主从节点数据库中数据不一致。
开启了半同步复制模式，就解决了上面提到的问题。半同步复制是从节点接受完主节点发送过来的二进制日志并写入自己的中继日志之后，把自己成功拷贝完成的信息反馈给主节点，这时主节点等到从节点发送的反馈信息后，才继续往下处理其他操作。
#环境说明
#两台linux虚拟机
#Linux版本 CentOS7，mysql版本5.7
192.168.3.11 主节点
192.168.3.105 从节点
首先按照前面的简单复制流程配置好mysql主从节点
#主节点
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#主节点加载semisync_master.so插件
mysql> SET @@GLOBAL.rpl_semi_sync_master_enabled=ON;
#把插件开启
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';#查看状态
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |#插件已经开启
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
#从节点
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#从节点加载semisync_slave.so插件
mysql> SET @@global.rpl_semi_sync_slave_enabled=ON;
#把插件开启
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';#查看状态
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |#插件已经开启
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;#从节点重启IO线程
#查看主节点半同步复制状态
mysql> SHOW GLOBAL STATUS LIKE 'rpl%';#主节点查看状态
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |#数字1代表已连接1台从节点
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
#至此mysql的半同步复制架设成功
网友评论