mysql主从复制

原理：
用户向主数据库写入数据，主数据库数据更新，写入二进制文件
从数据库开启I/O线程，向主数据库请求数据同步以获取二进制日志
主数据库为从服务器授权
主数据库开启I/O线程回应并发送二进制日志到从数据库
从数据库得到主数据库的二进制日志写入中继日志
从数据库开启SQL线程读取中级日志内容并执行，实现数据同步

实例：
主数据库：192.168.200.111
[root@localhost ~]# setenforce 0
[root@localhost ~]# iptables -F
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# yum -y install ntp
[root@localhost ~]# cp -p /etc/ntp.conf /etc/ntp.conf.origin
[root@localhost ~]# vim /etc/ntp.conf
添加以下两行：
22 server 127.127.1.0
23 fudge 127.127.1.0 startum 8
[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# chkconfig ntpd on
[root@localhost ~]# vim /etc/my.cnf
在[mysqld]下添加三行：
log-bin=mysql-bin
log-slave-updates=true
server-id=11
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -p111111
MariaDB [(none)]> grant replication slave on *.* to 'myslave'@'192.168.200.%' identified by '111111';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 1264 | | |
+------------------+----------+--------------+------------------+

从数据库：192.168.200.112
[root@localhost ~]# setenforce 0
[root@localhost ~]# iptables -F
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.200.111
[root@localhost ~]# vim /etc/my.cnf
添加三行：
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
server-id=12
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql

MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='192.168.200.111',master_user='myslave',
master_password='111111',master_log_file='mysql-bin.000004',master_log_pos=1264;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave statusG
出现以下两行即成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

从数据库：192.168.200.113
[root@localhost ~]# setenforce 0
[root@localhost ~]# iptables -F
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.200.111
[root@localhost ~]# vim /etc/my.cnf
添加三行：
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
server-id=13
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql

MariaDB [(none)]> stop slave
MariaDB [(none)]> change master to master_host='192.168.200.111',master_user='myslave',
master_password='111111',master_log_file='mysql-bin.000004',master_log_pos=1264;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave statusG
出现以下两行即成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证：
然后在主数据库中创建数据库，在从服务器中查看是否有创建的数据库。