一、MySQL的主从复制
注意事项:
主节点必须开启二进制日志,在从节点上务必开启中继日志;
中继日志:从服务器上记录下来从主服务器的二进制日志文件同步过来的事件;
下面来做一个小的实验,两个空的数据库的主从复制
配置前注意事项:务必同步时间 ntpdate 172.18.0.1
如果服务开启先停止服务,清空数据
systemctl stop mariadb
rm -rf /var/lib/mysql/*
实验演示:
1、主节点上的配置文件中定义如下
[server] #在配置文件中找到[server]配置段定义
skip_name_resolve = on #为了方便跳过地址解析
innodb_file_per_table = on
max_connections = 20000
#上述这些配置可以不用一定配置,但是建议可以写上
log_bin = master-log #最主要的两项配置
server_id = 1
read_only=ON #默认只读是打开的,可以不用设置
2、从节点上的配置[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
relay_log = relay-log #启动中继日志
server_id = 2 #这里的节点就不能为1,这是从节点
3、主节点上授权一个用户可以做复制操作MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.18.77.%' identified by 'centos';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 495 | |
+-------------------+----------+--------------+------------------+
4、然后可以让从服务器来复制
设置从主服务器的哪个文件的哪个位置开始启动复制,要设置从服务器指的主服务器要使用下述的命令启动复制MariaDB [(none)]> change master to master_host='172.18.77.7',master_user='repluser',master_password='centos',master_log_file='master-log.000003',master_log_pos=495;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.18.77.7 ----------------->
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003 ----------------->
Read_Master_Log_Pos: 495
Relay_Log_File: relay-log.000001 ----------------->
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: No -----------------> 这里为no表示复制还没有开启
Slave_SQL_Running: No ----------------->
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: 495
Relay_Log_Space: 245
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: NULL ------------------>
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: 0
“---------------->” 表示需要注意的点
现在可以开启复制功能MariaDB [(none)]> start slave io_thread,sql_thread;
MariaDB [(none)]> show slave status\G;
上述已经完成了主从复制
5、测试:
MariaDB [(none)]> create database mydb; 在主节点上创建一个表
MariaDB [(none)]> show databases; 在从节点查看这个表是否已经复制过来
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
二、MySQL的主主复制
主主复制:
互为主从:两个节点各自都要开启binlog和relay log;
1、数据不一致;
2、自动增长id;
定义一个节点使用奇数id
auto_increment_offset=1
uto_increment_increment=2
另一个节点使用偶数id
auto_increment_offset=2 id递增使用偶数
auto_increment_increment=2 id增长步进为2
配置:
1、server_id必须要使用不同值;
2、均启用binlog和relay log;
3、存在自动增长id的表,为了使得id不相冲突,需要定义其自动增长方式;
服务启动后执行如下两步:
4、都授权有复制权限的用户账号;
5、各把对方指定为主节点;
复制时应该注意的问题:
1、从服务设定为“只读”;
在从服务器启动read_only,但仅对非SUPER权限的用户有效;
阻止所有用户:
mysql> FLUSH TABLES WITH READ LOCK;
2、尽量确保复制时的事务安全
在master节点启用参数:
sync_binlog = ON
如果用到的是InnoDB存储引擎:
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
3、从服务器意外中止时尽量避免自动启动复制线程
4、从节点:设置参数
sync_master_info=ON
sync_relay_log_info=ON
实验演示:
环境准备
两台主机 节点1: 172.18.77.7
节点2: 172.18.77.77
1、实验前先将mysql服务停止修改配置文件
在节点1上的配置如下[root@bixia ~]#vim /etc/my.cnf.d/server.cnf
[server]
log_bin = master-log
relay_log = relay-log
server_id = 1 -----------> 两台主机的server_id必须有一个为主
auto_increment_offset=1
auto_increment_increment=2
在节点2上的配置如下[root@huangshang ~]#vim /etc/my.cnf.d/server.cnf
[server]
log-bin = master-log
relay_log = relay-log
server_id = 2 ----------->
auto_increment_offset=2 ----------->
auto_increment_increment=2
2、查看两台主机各自处于二进制日志的哪个位置[root@bixia ~]#mysql -pcentos
MariaDB [(none)]> show master status; #节点1上的二进制日志所处的位置
+-------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 245 | | |
+-------------------+----------+--------------+------------------+[root@huangshang ~]#mysql
MariaDB [(none)]> show master status; #节点2上的二进制日志所处的位置
+-------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 245 | | |
+-------------------+----------+--------------+------------------+
3、都授权有复制权限的用户账号
两个节点都要授权[root@bixia ~]#mysql #有密码用-p加密码就可以 也可用-u指明用户
MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.18.77.%' identified by 'centos';
4、两个节点各把对方指定为主节点;并且指定从对方的哪个二进制日志文件的什么位置开始复制
第一个节点1change master to master_host='172.18.77.77',master_user='repluser',master_password='centos',master_log_file='master-log.000001',master_log_pos=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G; #检查是否已经复制开启
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
第二个节点2MariaDB [(none)]> change master to master_host='172.18.77.7',master_user='repluser',master_password='centos',master_log_file='master-log.000003',master_log_pos=245;
MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、测试:
(1) 先在172.18.77.7这个节点上删除一行数据,在172.18.77.77这个节点上查看是否已经同步
MariaDB [hidb]> delete from students where id=998;
另一个节点上172.18.77.77查看 (没有998这行数据已经证明数据同步)
(2) 反过来测试:先在172.18.77.77这个节点上修改一行数据,在172.18.77.7这个节点上查看是否已经同步MariaDB [hidb]> update students set major='tiantian' where id=995;
在172.18.77.7上查看
MariaDB [hidb]> select * from students;
.........
| 995 | stu995 | 39 | F | tiantian |
(3) 插入一些数据检查id是否的递增(172.18.77.7); 我们在配置文件中定义的是以奇数递增的id号MariaDB [hidb]> insert into students (name,age,gender,major) values ('longmu',45,'F','qilongpenhuo'),('xiao emo',40,'F','wenxue')
MariaDB [hidb]> select * from students;
.....
| 1003 | longmu | 45 | F | qilongpenhuo |
| 1005 | xiao emo | 40 | F | wenxue |
+------+----------+------+--------+----------------------------------+
在另一台节点上查看也是同步的
但是在另一台主机上(172.18.77.77)插入数据时显示如下MariaDB [hidb]> insert into students (name,age,gender,major) values ('zhoutou',76,'F','tangtou'),('yuqian',40,'M','hejiu');
MariaDB [hidb]> select * from students;
......
| 1003 | longmu | 45 | F | qilongpenhuo |
| 1005 | xiao emo | 40 | F | wenxue |
| 1006 | zhoutou | 76 v | F | tangtou |
| 1008 | yuqian | 40 | M | hejiu |
+------+----------+------+--------+----------------------------------------------+
实验证明id号的递增并不是从其中插入从1004开始的;这里希望可以注意一下