mysql主从复制（mariadb）

 

 

主机：

master：192.168.199.231

slave：192.168.199.231

 

maste和slave都安装mariadb

 

?

1 2 3 4 5

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

 

修改master主配/etc/my.cnf

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[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

?

1	systemctl restart MariaDB

 

master操作

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

登录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操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

添加要复制的主机信息   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开启之后的数据库变化状态