MySQL主从复制流程
原理图
流程如下:
master服务器将数据改变记录二进制日志,slave服务器会在一定时间间隔内对master二进制日志检查,若发生改变,则开始一个I/O thread 请求master二进制事件,同时主节点为每个I/O启动一个线程,用于向 其发送二进制事件,并保存至从节点本地中继日志中,从节点启动SQL 线程从日志中读取二进制日志,在本地重放,使得主从节点数据一致,最 后I/O thread和SQL thread将进入睡眠状态,等待下一次被唤醒。
安装数据库
环境
两台Redhat7.1server1和server2,server1作为主库,server2作为从库
防火墙firewalld关闭
selinux关闭
server1和server2能够互相ping通
主从数据库版本保持一致
安装MySQL,这里采用yum安装
server1和server2上安装MySQL
[root@server1 local]# yum install mariadb mariadb-server -y
[root@server2 local]# yum install mariadb mariadb-server -y
[root@server2 local]# rpm -qa |grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
mariadb-5.5.64-1.el7.x86_64
mariadb-server-5.5.64-1.el7.x86_64
启动MySQL
[root@server1 local]# systemctl start mariadb
[root@server2 local]# systemctl start mariadb
为root用户设置密码
[root@server1 local]# mysqladmin -u root password "redhat"
[root@server2 local]# mysqladmin -u root password "redhat"
配置主从复制
主库配置
server1配置master
[root@server1 local]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #启用二进制日志,启用后才可通过I/O写到Slave的relay-log,是进行replication的前提.
server-id=1 # ID值唯一的标识了群集中的主从服务器,master_id必须为1到232–1之间的一个正整数值,slave_id值必须为2到232–1之间的一个正整数值.
max_binlog_size = 1G # 设置binlog文件的最大值,当达到这个值会自动生成一个新的binlog文件
binlog-ignore-db = zabbix # 对指定数据库的变动不写入二进制文件binlog,如果有多个数据库可用逗号分隔
# replicate-ignore-db = # 指定不需要复制同步的数据库,如果有多个数据库可用逗号分隔
重启MySQL服务
[root@server1 local]# systemctl restart mariadb
创建并授权复制用户
[root@server1 local]# mysql -uroot -p
Enter password:
MariaDB [(none)]> grant replication slave on *.* to 'mysql'@"192.168.56.%" identified by 'redhat';
Query OK, 0 rows affected (0.15 sec)
刷新权限表使授权生效
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)
查看master binlog文件名与position
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | zabbix |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从库配置
修改从库/etc/my.cnf文件
[root@server2 local]# vim /etc/my.cnf
[mysqld]
server_id=2 # slave id不能和master一样
重启从库
[root@server2 local]# systemctl restart mariadb
配置从库同步主库
[root@server2 local]# mysql -uroot -p
Enter password:
MariaDB [(none)]> change master to master_host='192.168.56.135',master_user='mysql',master_password='redhat',master_log_file='mysql-bin.000002',master_log_pos=245;
Query OK, 0 rows affected (0.01 sec)
设置从库只读
#如果有用户向从库中写数据,然后从库在从主库同步数据库时,会造成数据错乱,从而造成数据损坏,所以需要把从库设置成只读
[root@server2 local]# mysql -uroot -p
Enter password:
MariaDB [(none)]> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show global variables like 'read%';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_only | ON |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)
read-only = OFF/ON,全局变量,只有管理员具有修改权限;
read-only = ON,此功能只对非管理员组用户有效;
通过命令设置的read-only在服务重启后消失,可以在my.cnf文件中设置永久生效
启动从库slave进程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
查看从库同步状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.135
Master_User: mysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-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: 245
Relay_Log_Space: 825
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
1 row in set (0.00 sec)
Slave_IO_Running: Yes Slave_SQL_Running: Yes 都为yes,说明已经成功
验证主从同步
在主库上创建新的数据库和表
[root@server1 local]# mysql -uroot -p
Enter password:
MariaDB [(none)]> create database mysqltest;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> use mysqltest;
Database changed
MariaDB [mysqltest]> create table tabletest(id int);
Query OK, 0 rows affected (0.15 sec)
MariaDB [mysqltest]> insert into tabletest() values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
从库查看是否同步主库数据库和表
[root@server2 local]# mysql -uroot -p
Enter password:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqltest |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.02 sec)
MariaDB [(none)]> select * from mysqltest.tabletest;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
从库上同步了主库的mysqltest库和tabletest表
MySQL主从同步成功!