mysql主从复制(mariadb)

主机:

master:192.168.199.231

slave:192.168.199.231

maste和slave都安装mariadb

yum install mariadb mariadb-server
systemctl start mariadb #启动mariadb
systemctl enable mariadb #设置开机自启动
mysql_secure_installation #设置root密码等相关
mysql -uroot -p #测试登录

修改master主配/etc/my.cnf

[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
#设置server-id,建议使用ip最后3位
server-id=231
[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

 修改slave的主配/etc/my.cnf

[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
#开启中继日志
relay-log=mysql-relay
#设置server-id,建议使用ip最后3位
server-id=232
[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

重启master和slave

systemctl restart MariaDB

master操作

登录mysql

mysql -uroot -p 

授权账户

create user ‘slave‘@‘192.168.199.%‘ identified by ‘123456‘;

grant replication slave on *.* to ‘slave‘@‘192.168.199.%‘;

记录master状态,主要为File和Position

show master status;

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      910 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 slave操作

添加要复制的主机信息

CHANGE MASTER TO master_host = ‘192.168.199.231‘,
 master_user = ‘slave‘,
 master_password = ‘123456‘,
 master_log_file = ‘mysql-bin.000001‘,
 master_log_pos = 910;

开始复制

start slave;

以前有过slave复制会开启失败,解决方法如下:

stop slave;

reset slave;

然后再重复上面的操作就可以了

新增数据库信息,查看时候主从复制成功,注意开启主从复制功能之后,slave只会复制master开启之后的数据库变化状态