MySQL数据库主从同步

原理图

一、环境

linux-node1.example.com:  master  192.168.1.14
linux-node2.example.com:  slave   192.168.1.15

二、 slave导入master的全备份数据

1、备份master的数据，并复制给slave机

[[e-mail]-node1 ~]# mysqldump --all-databases -uroot -p123 --single-transaction --flush-logs --master-data=2 > /tmp/mysql.all.sql 
[[e-mail]-node1 ~]# scp /tmp/mysql.all.sql [e-mail].168.1.15:/tmp 
mysql.all.sql                                 100%  504KB 503.9KB/s   00:00 

2、从机slave安装数据库并启动服务

[root@linux-node2 ~]# yum install -y mariadb mariadb-server 
[root@linux-node2 ~]# systemctl start mariadb

3、将备份文件导入到slave的数据库

[root@linux-node2 ~]# mysql < /tmp/mysql.all.sql 
[root@linux-node1 ~]# systemctl restart mariadb

三、修改master和slave配置文件

1、修改主服务器master的配置文件

[[e-mail] ~]# cat /etc/my.cnf 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
symbolic-links=0 
log-bin=/var/lib/mysql-log/master   #设置二进制日志的路径，在master上必须设置
server-id=1                #server-id的值必须唯一，并且master的值一定大于slave的值
[mysqld_safe] 
log-error=/var/log/mariadb/mariadb.log 
pid-file=/var/run/mariadb/mariadb.pid  
!includedir /etc/my.cnf.d 
[[e-mail] ~]# systemctl restart mariadb

2、修改从机slave主配置文件

[[e-mail] ~]# cat /etc/my.cnf 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
symbolic-links=0 
server-id=2       #slave的server-id必须小于master的
[mysqld_safe] 
log-error=/var/log/mariadb/mariadb.log 
pid-file=/var/run/mariadb/mariadb.pid 
!includedir /etc/my.cnf.d
[[e-mail] ~]# systemctl restart mariadb

四、master添加授权用户允许从机来读日志

1、主服务器master添加授权

[[e-mail] ~]# mysql -uroot -p123 
#让slave用户(用户名字可以自己取的)可以从192.168.1.15即linux-node2.example.com，通过密码123登陆master的数据库，并拥有replication slave的权限，即可以读取二进制日志
MariaDB [(none)]> grant replication slave on *.* to [e-mail]"192.168.1.15" identified by "123"; 
MariaDB [(none)]> grant replication slave on *.* to [e-mail]"linux-node2.example.com" identified by "123"; 
MariaDB [(none)]> flush privileges; 
MariaDB [(none)]> \q 

2、从备份中找到master_log_file和master_log_pos的值，在从机slave上要用到

[root@linux-node1 ~]# grep master /tmp/mysql.all.sql | head -1
-- CHANGE master TO master_LOG_FILE='master.000016', master_LOG_POS=245; 
#或者直接用sed截取第22行，相关信息就是在22行
[root@linux-node1 ~]# sed -n "22p" /tmp/mysql.all.sql 
-- CHANGE master TO master_LOG_FILE='master.000016', master_LOG_POS=245; 

五、slave定义master的ip user passwd 日志名称 日志position号

MariaDB [(none)]> change master to master_host="192.168.1.14",master_user="slave",master_password="123",master_log_file="master.000016",master_log_pos=245; 
MariaDB [(none)]> slave start; 
MariaDB [(none)]> show slave status\G; 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  master_Host: 192.168.1.14 
                  master_User: slave 
                  master_Port: 3306 
                Connect_Retry: 60 
              master_Log_File: master.000017 
          Read_master_Log_Pos: 475 
               Relay_Log_File: mariadb-relay-bin.000003 
                Relay_Log_Pos: 757 
        Relay_master_Log_File: master.000017 
             Slave_IO_Running: Yes     #必须是yes
            Slave_SQL_Running: Yes     #必须是yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0 
                   Last_Error: 
                 Skip_Counter: 0 
          Exec_master_Log_Pos: 475 
              Relay_Log_Space: 1335 
              Until_Condition: None 
               Until_Log_File: 
                Until_Log_Pos: 0 
           master_SSL_Allowed: No 
           master_SSL_CA_File: 
           master_SSL_CA_Path: 
              master_SSL_Cert: 
            master_SSL_Cipher: 
               master_SSL_Key: 
        Seconds_Behind_master: 0 
master_SSL_Verify_Server_Cert: No 
                Last_IO_Errno: 0   

六、测试

1、在主服务器的数据库操作

[root@linux-node1 ~]# mysql -uroot -p123 
MariaDB [(none)]> create database test11; 
MariaDB [(none)]> use test11; 
MariaDB [test11]> create table a ( id int primary key, 
    -> name varchar(20) not null, 
    -> age int, 
    -> school varchar(50) 
    -> ); 
MariaDB [test11]> insert into a values (1,"tom",27,"Beijing"); 
MariaDB [test11]> \q 

2、从机slave查看是否同步了主服务器master的数据

MariaDB [(none)]> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| test11             | 
+--------------------+ 
MariaDB [(none)]> use test11; 
MariaDB [test11]> show tables; 
+------------------+ 
| Tables_in_test11 | 
+------------------+ 
| a                | 
+------------------+ 
1 row in set (0.01 sec) 

MariaDB [test11]> select * from a; 
+----+------+------+---------+
| id | name | age  | school  |
+----+------+------+---------+
|  1 | tom  |   27 | Beijing |
+----+------+------+---------+
1 row in set (0.00 sec) 

MariaDB [test11]> \q