mariadb主从同步
默认情况下属于异步复制,无需维持长连接
通过配置,可以复制所有的库或者几个库,甚至库中的一些表
-
-
Backup Server 备份服务,无法对SQL语句执行产生的故障恢复,有限的备份
-
-
-
slave将master的binary log events拷贝到它的中继日志(relay log);
-
mariadb主从复制中:
-
-
slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经执行完master产生的所有文件,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志
-
IP | 系统/mariadb版本 | 角色 | |
db01 | 172.16.1.51 | CentOS7.6/mariadb-5.5 | Master |
db02 | 172.16.1.52 | CentOS7.6/mariadb-5.5 |
1.下载安装软件程序 yum install mariadb-server mariadb -y 2.启动软件服务程序 systemctl start mariadb.service systemctl enable mariadb.service 3.数据库创建密码登录 mysqladmin -uroot password "123456" mysql -uroot -p123456
2. 创建需要同步的数据库
MariaDB [(none)]> create database HA; MariaDB [(none)]> use HA; MariaDB [HA]> create table test(id int,name varchar(20)); ---建表 MariaDB [HA]> grant replication slave on *.* to slave@172.16.1.52 identified by "123456"; ---创建账号 MariaDB [(none)]> flush privileges; ---刷新权限表 MariaDB [(none)]> set sql_log_bin=on; ---同步开启 MariaDB [(none)]> set sql_log_bin=off; ---不开启 MariaDB [(none)]> show master status; ---查看状态
3. 导出数据库
mysqldump -uroot -poldboy123 -B HA>ha.sql 拷贝到db02主机 scp ha.sql 172.16.1.52:/root
4. 修改db01数据库配置文件
vim /etc/mysql.conf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd log-bin=mysql-bin-master server-id=1 binlog-do-db=HA binlog-ignore-db=mysql [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid
[root@db01 ~]# ll /var/lib/mysql/ #查看同步信息 -rw-rw---- 1 mysql mysql 320 Nov 23 13:25 mysql-bin-master.000001 systemctl restart mariadb
server-id=1:本机数据库ID 标示
binlog-do-db=HA:可以被从服务器复制的库, 二进制需要同步的数据库名
mysql -uroot -p123456 <ha.sql mysql -uroot -p123456 -e "show databases" +--------------------+ | Database | +--------------------+ | information_schema | | HA | | mysql | | performance_schema | | test | +--------------------+
2. 修改db02数据库配置文件
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd server-id=2 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
server-id = 2:从服务器ID号,不要和主ID相同 ,如果设置多个从服务器,每个从服务器必须有一 个唯一的server-id值,必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址:这些ID值能唯一识别复制服务器群集中的每个服务器实例
MariaDB [HA]> stop slave; ---停止slave MariaDB [HA]> change master to master_host='172.16.1.51',master_user='slave',master_password='123456'; ---告诉从服务器,主服务器是谁。 账号是:slave 密码是:123456 MariaDB [HA]> start slave; ---开启同步 MariaDB [HA]> show slave status\G ---查看状态
3. 验证是否同步
db01数据库上
MariaDB [HA]> select * from test; ---查看表内容 MariaDB [HA]> insert into test values (1,'man'); ---插入man 内容 MariaDB [HA]> select * from test; +------+------+ | id | name | +------+------+ | 1 | man | +------+------+
---去db02上面查看 MariaDB [HA]> select * from test; ok!