主从数据库搭建步骤

主从数据库搭建步骤

1.添加hosts文件

vi /etc/hosts

2.创建挂载文件夹，挂载镜像

mkdir /opt/centos
mount CentOS-7-x86_64-DVD-1511.iso  /opt/centos/

3.编写本地源文件

vi /etc/yum.repos.d/local.repo
[centos]
name=centos
baseurl=file:///opt/centos
gpgcheck=0
enabled=1

3.安装mariadb、mariadb-server,启动并设置开机自启

yum install -y mariadb mariadb-server
systemctl start mariadb mariadb-server
systemctl enable mariadb mariadb-server

4.初始化数据库

mysql_secure_installations

5.配置主节点

vi /etc/my.cnf
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=11

6.重启数据库并赋予权限

systemctl restart mariadb
grant all privileges on *.* to root@'%' identified by '000000';
grant replication slave on *.* to 'user'@'xserver2' identified by '000000';

7.配置从数据库

vi /etc/my.cnf
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=12

8.重启数据并且连接主数据库

systemctl restart mariadb
change master to master_host='xserver1',master_user='root'.master_password='000000';

9.启动从节点服务并且查看

start slave;
show slave status\G

10.问题解决Slave_SQL_Running: No

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

​