环境
-
vmware workstation15
-
centos7.0
-
主IP:192.168.162.143
-
从IP:192.168.162.145
-
数据库: MariaDB-10.3.7
两台机器都要安装mariadb-server -
yum配置
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
- 安装数据库
yum -y install MariaDB-server
- 主:
- 修改配置文件
vim /etc/my.cnf.d/server.cnf
在[mysqld]
下加入以下两行
server-id=1
log-bin=mysql-bin
- 重启mysql服务
systemctl restart mysqld
- 创建主从连接账号与授权
CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;
show master status;
- 从:
- 修改配置文件
vim /etc/my.cnf.d/server.cnf
在[mysqld]
下加入以下两行
server-id=2 #必须和主不同
log-bin=mysql-bin
- 重启mysql服务
service mysqld restart
- 登录数据库
···
mysql -uroot
delete from user where user='';
- 建立主从连接
#MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=765;必须和主相同,使用show master status;查询
CHANGE MASTER TO MASTER_HOST='192.168.162.143', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=765;
start slave;
- 验证!!!
create database test01
use test01
create table students(id int(10), name char(20), age int(10))
- 查看mysql二进制log
mysqlbinlog /var/lib/mysql/mysql-bin.000001
- 如果出现不同步可以执行以下步骤
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
- 或者
stop slave;
mysql> change master to
master_host='master_ip',
master_user='user',
master_password='pwd',
master_port=3306,
master_log_file=localhost-bin.000094',
master_log_pos=33622483;